Ms sql szerver indexek. Indexek SQL-ben. Mi van, ha indexel egy nézetet, akkor is nézet marad

6. Indexek és teljesítményoptimalizálás

Indexek az adatbázisokban: cél, hatás a teljesítményre, indexek létrehozásának elvei

6.1 Mire valók az indexek?

Az indexek olyan speciális struktúrák az adatbázisokban, amelyek lehetővé teszik a keresés és a rendezés felgyorsítását egy adott mező vagy mezőkészlet alapján egy táblázatban, valamint az adatok egyediségének biztosítására is szolgálnak. Az indexek összehasonlításának legegyszerűbb módja a könyvekben található indexekkel. Ha nincs index, akkor az egész könyvet át kell néznünk, hogy megtaláljuk a megfelelő helyet, de indexszel ugyanaz a művelet sokkal gyorsabban végrehajtható.

Általában minél több index, annál jobb az adatbázis-lekérdezések teljesítménye. Ha azonban az indexek száma túlzottan megnő, az adatmódosítási műveletek (beszúrás/módosítás/törlés) teljesítménye csökken, az adatbázis mérete pedig megnő, ezért az indexek hozzáadásával óvatosan kell bánni.

Néhány általános elv az indexek létrehozásához:

· indexeket kell létrehozni az összekapcsolásban használt oszlopokhoz, amelyeket gyakran használnak keresési és rendezési műveletekhez. Kérjük, vegye figyelembe, hogy az indexek mindig automatikusan jönnek létre azokhoz az oszlopokhoz, amelyekre az elsődleges kulcsra vonatkozó megkötés vonatkozik. Leggyakrabban idegen kulccsal rendelkező oszlopokhoz jönnek létre (az Accessben - automatikusan);

· Index kötelező automatikus üzemmód olyan oszlopokhoz készült, amelyek egyedi megszorítással rendelkeznek;

· A legjobb indexeket létrehozni azokhoz a mezőkhöz, amelyekben minimális számú ismétlődő érték van, és az adatok egyenletesen oszlanak el. Az Oracle speciális bitindexekkel rendelkezik az oszlopokon nagy számban duplikált értékek esetén az SQL Server és az Access nem biztosít ilyen típusú indexet;

· ha a keresést folyamatosan egy adott oszlopkészleten (egyidejűleg) hajtják végre, akkor ebben az esetben érdemes lehet összetett indexet létrehozni (csak SQL Serverben) - egy indexet egy oszlopcsoporthoz;

· Ha módosítja a táblákat, a táblára helyezett indexek automatikusan megváltoznak. Ennek eredményeként az index erősen töredezetté válhat, ami hatással van a teljesítményre. Rendszeresen ellenőrizze az index töredezettségének mértékét, és töredezettségmentesítse azokat. Nagy mennyiségű adat betöltésekor néha érdemes először törölni az összes indexet, és a művelet befejezése után újra létrehozni;

· indexek nem csak táblákhoz, hanem nézetekhez is létrehozhatók (csak SQL Serverben). Előnyök - a mezők kiszámításának lehetősége nem a kérés idején, hanem abban a pillanatban, amikor új értékek jelennek meg a táblázatokban.

1) Az index fogalma
Index biztosításának eszköze gyors hozzáférés táblázatsorokhoz egy vagy több oszlop értékei alapján.

Ebben az operátorban sok a változatosság, mivel nem szabványosított, mivel a szabványok nem foglalkoznak a teljesítményproblémákkal.

2) Indexek létrehozása
INDEX LÉTREHOZÁSA
ON()

3) Indexek módosítása és törlése
Az index tevékenységének szabályozásához az operátort használják:
ALTER INDEX
Az index eltávolításához használja az operátort:
DROP INDEX

a) Táblaválasztási szabályok
1. Célszerű azokat a táblázatokat indexelni, amelyekben a sorok legfeljebb 5%-a van kijelölve.
2. Indexelni kell azokat a táblákat, amelyek nem tartalmaznak ismétlődést a SELECT utasítás WHERE záradékában.
3. Nem célszerű a gyakran frissített táblázatokat indexelni.
4. Nem célszerű olyan táblázatokat indexelni, amelyek legfeljebb 2 oldalt foglalnak el (az Oracle esetében ez kevesebb, mint 300 sor), mivel a teljes vizsgálat nem tart tovább.

b) Oszlopkiválasztási szabályok
1. Elsődleges és idegen kulcsok – gyakran használják táblák összekapcsolására, adatok lekérésére és keresésre. Ezek mindig egyedi indexek, maximális hasznossággal
2. Hivatkozási integritási beállítások használatakor mindig szüksége van egy indexre az FK-n.
3. Oszlopok, amelyek alapján az adatokat gyakran rendezik és/vagy csoportosítják.
4. A SELECT utasítás WHERE záradékában gyakran keresett oszlopok.
5. Ne hozzon létre indexeket hosszú leíró oszlopokon.

c) Összetett indexek létrehozásának elvei
1. Az összetett indexek akkor jók, ha az egyes oszlopoknak kevés egyedi értéke van, de az összetett index több egyediséget biztosít.
2. Ha a SELECT utasítással kiválasztott összes érték egy összetett indexhez tartozik, akkor az értékeket az indexből választjuk ki.
3. Összetett indexet kell létrehozni, ha a WHERE záradék két vagy több értéket használ az ÉS operátorral kombinálva.

d) Nem ajánlott létrehozni
Nem ajánlott indexeket létrehozni olyan oszlopokon, beleértve az összetetteket is, amelyek:
1. Ritkán használják a lekérdezések eredményeinek keresésére, egyesítésére és rendezésére.
2. Tartalmazzon gyakran változó értékeket, ami megköveteli gyakori frissítések index lelassítja az adatbázis teljesítményét.
3. Tartalmazzon kis számú egyedi értéket (kevesebb, mint 10% m/f) vagy túlnyomó számú sort egy vagy két értékkel (a szállító székhelye Moszkva).
4. A WHERE záradékban függvények vagy kifejezések kerülnek rájuk, és az index nem működik.

e) Nem szabad elfelejtenünk
Törekedni kell az indexek számának csökkentésére, mivel a nagyszámú index csökkenti az adatfrissítés sebességét. Így az MS SQL Server táblánként legfeljebb 16 index létrehozását javasolja.
Az indexek általában lekérdezési célokra és a hivatkozási integritás fenntartására jönnek létre.
Ha az indexet nem használják lekérdezésekhez, akkor törölni kell, és triggerekkel biztosítani kell a hivatkozási integritást.

Az elérésének egyik legfontosabb módja nagy teljesítményű SQL Server az indexek használata. Az index felgyorsítja a lekérdezési folyamatot azáltal, hogy gyors hozzáférést biztosít egy táblázat adatsoraihoz, hasonlóan ahhoz, ahogyan a könyvben található index segít gyorsan megtalálni szükséges információkat. Ebben a cikkben adok rövid áttekintés indexek be SQL Serverés magyarázza el, hogyan vannak elrendezve az adatbázisban, és hogyan segítik felgyorsítani az adatbázis-lekérdezéseket.

Az indexek táblázat- és nézetoszlopokon jönnek létre. Az indexek lehetőséget biztosítanak az adatok gyors keresésére az oszlopok értékei alapján. Például, ha létrehoz egy indexet egy elsődleges kulcson, majd az elsődleges kulcs értékei alapján keres egy adatsort, akkor SQL Server először megkeresi az index értékét, majd az index segítségével gyorsan megkeresi a teljes adatsort. Index nélkül a rendszer a táblázat összes sorát teljes körűen megvizsgálja, ami jelentős hatással lehet a teljesítményre.
A táblázat vagy nézet legtöbb oszlopához létrehozhat indexet. A kivétel főleg a nagy objektumok tárolására szolgáló adattípusokat tartalmazó oszlopok ( FAJANKÓ), mint pl kép, szöveg vagy varchar (max). Indexeket is létrehozhat az adatok formátumban való tárolására szolgáló oszlopokon XML, de ezek az indexek kissé eltérnek a szabványos indexektől, és figyelembe vételük túlmutat e cikk keretein. Ezenkívül a cikk nem tárgyalja oszloptár indexek. Ehelyett az adatbázisokban leggyakrabban használt indexekre összpontosítok SQL Server.
Az index oldalak halmazából, indexcsomópontokból áll, amelyek egy fastruktúrába vannak rendezve - kiegyensúlyozott fa. Ez a szerkezet hierarchikus jellegű, és a hierarchia tetején lévő gyökércsomóponttal kezdődik, alul pedig a levelek csomópontjai, amint az az ábrán látható:

Ha egy indexelt oszlopot kérdez le, a lekérdezőmotor a gyökércsomópont tetején indul, és lefelé halad a közbülső csomópontokon, és minden közbenső réteg több mint részletes információkat adatokról. A lekérdezőmotor továbbra is mozog az index csomópontokon, amíg el nem éri az indexlevél alsó szintjét. Például, ha egy indexelt oszlopban a 123-as értéket keresi, a lekérdezőmotor először a gyökérszinten az első köztes szinten határozza meg az oldalt. IN ebben az esetben az első oldal 1 és 100 közötti értékre mutat, a második pedig 101 és 200 közötti értékre, így a lekérdezőmotor ennek a középszintnek a második oldalához fog hozzáférni. Ezután látni fogja, hogy a következő középszint harmadik oldalára kell lapoznia. Innentől a lekérdezési alrendszer alacsonyabb szinten fogja beolvasni magának az indexnek az értékét. Az indexlapok tartalmazhatják magukat a táblázatadatokat, vagy egyszerűen egy mutatót a táblázatban lévő adatokat tartalmazó sorokra, az index típusától függően: fürtözött index vagy nem fürtözött index.

Klaszteres index

A fürtözött index a tényleges adatsorokat az index leveleiben tárolja. Az előző példához visszatérve ez azt jelenti, hogy a 123-as kulcsértékhez tartozó adatsort magában az indexben tároljuk. Fontos jellemző A fürtözött index azt jelenti, hogy az összes érték meghatározott sorrendben van rendezve, akár növekvő, akár csökkenő sorrendben. Ezért egy táblának vagy nézetnek csak egy fürtözött indexe lehet. Ezenkívül meg kell jegyezni, hogy a táblákban lévő adatok csak akkor tárolódnak rendezett formában, ha ezen a táblán fürtözött indexet hoztak létre.
Azokat a táblákat, amelyeknek nincs fürtözött indexe, kupacnak nevezzük.

Nem klaszterezett index

A fürtözött indexekkel ellentétben a nem csoportosított index levelei csak azokat az oszlopokat tartalmazzák ( kulcsfontosságú), amely meghatározza ezt az indexet, és tartalmaz egy mutatót is a táblázat valós adatokat tartalmazó soraira. Ez azt jelenti, hogy az allekérdező rendszernek további műveletre van szüksége a szükséges adatok megkereséséhez és lekéréséhez. Az adatmutató tartalma az adatok tárolásának módjától függ: fürtözött tábla vagy kupac. Ha egy mutató egy fürtözött táblára mutat, akkor egy fürtözött indexre mutat, amely felhasználható a tényleges adatok megkeresésére. Ha egy mutató egy kupacra hivatkozik, akkor egy adott adatsor-azonosítóra mutat. A nem fürtözött indexek nem rendezhetők úgy, mint a fürtözött indexek, de egy táblán vagy nézeten egynél több nem fürtözött indexet is létrehozhat, legfeljebb 999-ig. Ez nem jelenti azt, hogy a lehető legtöbb indexet kell létrehoznia. Az indexek javíthatják vagy ronthatják a rendszer teljesítményét. Amellett, hogy több nem fürtözött indexet is létrehozhat, további oszlopokat is beilleszthet ( tartalmazza oszlop). Ez a megközelítés lehetővé teszi, hogy megkerülje az indexre vonatkozó bizonyos korlátozásokat. Például beilleszthet egy nem indexelhető oszlopot, vagy megkerülheti az indexhossz-korlátot (a legtöbb esetben 900 bájt).

Az indexek típusai

Amellett, hogy fürtözött vagy nem fürtözött, az index összetett indexként, egyedi indexként vagy lefedő indexként is konfigurálható.

Összetett index

Egy ilyen index egynél több oszlopot is tartalmazhat. Egy indexben legfeljebb 16 oszlop szerepelhet, de teljes hossza 900 bájt lehet. Mind a fürtözött, mind a nem fürtözött indexek lehetnek összetettek.

Egyedi index

Ez az index biztosítja, hogy az indexelt oszlop minden értéke egyedi legyen. Ha az index összetett, akkor az egyediség az index összes oszlopára vonatkozik, de nem minden egyes oszlopra. Például, ha egyedi indexet hoz létre az oszlopokon NÉVÉs VEZETÉKNÉV, akkor a teljes névnek egyedinek kell lennie, de a kereszt- vagy vezetéknévben is előfordulhatnak ismétlődések.
Egy egyedi index automatikusan létrejön, amikor megad egy oszlopkényszert: elsődleges kulcs vagy egyedi értékkényszer:

Elsődleges kulcs
Amikor egy vagy több oszlopban meghatároz egy elsődleges kulcs kényszert, akkor SQL Server automatikusan létrehoz egy egyedi fürtözött indexet, ha korábban nem hoztak létre fürtözött indexet (ebben az esetben egyedi, nem fürtözött index jön létre az elsődleges kulcson)
Az értékek egyedisége
Amikor korlátozza az értékek egyediségét, akkor SQL Server automatikusan létrehoz egy egyedi, nem fürtözött indexet. Megadhatja, hogy egyedi fürtözött index jöjjön létre, ha még nem jött létre fürtözött index a táblában

Borító index

Egy ilyen index lehetővé teszi, hogy egy adott lekérdezés azonnal megkapja az összes szükséges adatot az index leveleiből anélkül, hogy további hozzáférést kellene biztosítani magának a tábla rekordjaihoz.

Indexek tervezése

Bármennyire is hasznosak az indexek, gondosan kell megtervezni őket. Mivel az indexek jelentős lemezterületet foglalhatnak el, nem kíván a szükségesnél több indexet létrehozni. Ezenkívül az indexek automatikusan frissülnek, amikor magának az adatsornak a frissítése megtörténik, ami további erőforrás-ráfordításhoz és teljesítményromláshoz vezethet. Az indexek tervezésénél több szempontot is figyelembe kell venni az adatbázissal és az arra irányuló lekérdezésekkel kapcsolatban.

Adatbázis

Mint korábban említettük, az indexek javíthatják a rendszer teljesítményét, mert gyors adatkeresési módot biztosítanak a lekérdezőmotornak. Ugyanakkor azt is figyelembe kell vennie, hogy milyen gyakran kíván adatokat beszúrni, frissíteni vagy törölni. Amikor módosítja az adatokat, az indexeket is módosítani kell, hogy tükrözzék az adatokon végrehajtott megfelelő műveleteket, ami jelentősen csökkentheti a rendszer teljesítményét. Az indexelési stratégia tervezésekor vegye figyelembe a következő irányelveket:

A gyakran frissített táblákhoz használjon a lehető legkevesebb indexet.
Ha a táblázat nagy mennyiségű adatot tartalmaz, de a változtatások csekélyek, akkor használjon annyi indexet, amennyi szükséges a lekérdezések teljesítményének javításához. Azonban alaposan gondolja át, mielőtt indexeket használ kis táblákon, mert... Lehetséges, hogy az indexkeresés használata tovább tart, mint az összes sor egyszerű átvizsgálása.
A fürtözött indexeknél próbálja meg a mezőket a lehető legrövidebbre tartani. A lehető legjobb módon fürtözött indexet fog használni az egyedi értékekkel rendelkező oszlopokon, és nem teszi lehetővé a NULL használatát. Ez az oka annak, hogy az elsődleges kulcsot gyakran használják fürtözött indexként.
Az oszlopban lévő értékek egyedisége befolyásolja az index teljesítményét. IN általános eset Minél több ismétlődés van egy oszlopban, annál rosszabbul teljesít az index. Másrészt minél több egyedi érték van, annál jobb az index teljesítménye. Lehetőség szerint használjon egyedi indexet.
Összetett index esetén vegye figyelembe az index oszlopainak sorrendjét. Kifejezésekben használt oszlopok AHOL(például, WHERE Keresztnév = "Charlie") legyen az első az indexben. A következő oszlopokat értékük egyedisége alapján kell felsorolni (a legtöbb egyedi értéket tartalmazó oszlopok az elsők).
A számított oszlopokon indexet is megadhat, ha megfelelnek bizonyos követelményeknek. Például egy oszlop értékének meghatározásához használt kifejezéseknek determinisztikusnak kell lenniük (mindig ugyanazt az eredményt adják vissza egy adott bemeneti paraméterkészlethez).

Adatbázis lekérdezések

Egy másik szempont az indexek tervezésekor, hogy milyen lekérdezések futnak az adatbázisban. Mint korábban említettük, figyelembe kell vennie, hogy az adatok milyen gyakran változnak. Ezenkívül a következő elveket kell alkalmazni:

Próbálja meg a lehető legtöbb sort beszúrni vagy módosítani egy lekérdezésben, ahelyett, hogy több lekérdezésben tenné.
Hozzon létre egy nem fürtözött indexet azokon az oszlopokon, amelyeket gyakran használnak keresési kifejezésekként a lekérdezésekben. AHOLés csatlakozások benne CSATLAKOZIK.
Fontolja meg a sorkeresési lekérdezésekben használt oszlopok indexelését a pontos értékegyezések érdekében.

És most tulajdonképpen:

14 kérdés az SQL Server indexeivel kapcsolatban, amelyeket szégyellt feltenni

Miért nem lehet egy táblának két fürtözött indexe?

Rövid választ szeretne? A fürtözött index egy táblázat. Ha fürtözött indexet hoz létre egy táblán, a tárolómotor a tábla összes sorát növekvő vagy csökkenő sorrendbe rendezi az indexdefiníció szerint. A fürtözött index nem egy különálló entitás, mint más indexek, hanem egy mechanizmus az adatok táblában való rendezésére és az adatsorok gyors elérésére.
Képzeljük el, hogy van egy táblázata, amely az értékesítési tranzakciók előzményeit tartalmazza. Az Értékesítési táblázat olyan információkat tartalmaz, mint a rendelés azonosítója, a termék pozíciója a rendelésben, a termék száma, a termék mennyisége, a rendelés száma és dátuma stb. Csoportosított indexet hoz létre az oszlopokon OrderIDÉs LineID, növekvő sorrendbe rendezve az alábbiak szerint T-SQL kód:
EGYEDI KLUSTERES INDEX LÉTREHOZÁSA ix_oriderid_lineid ON dbo.Sales(Rendelésazonosító, Sorazonosító);
A szkript futtatásakor a tábla összes sora fizikailag először az OrderID oszlop, majd a LineID alapján lesz rendezve, de az adatok egyetlen logikai blokkban, a táblázatban maradnak. Emiatt nem hozhat létre két fürtözött indexet. Csak egy tábla lehet egy adattal, és ez a tábla csak egyszer rendezhető egy adott sorrendben.

Ha egy fürtözött tábla számos előnnyel jár, akkor miért használjunk kupacot?

igazad van. A fürtözött táblák nagyszerűek, és a legtöbb lekérdezés jobban teljesít a fürtözött indexszel rendelkező táblákon. De bizonyos esetekben érdemes meghagyni az asztalokat a természetes, érintetlen állapotukban, pl. kupac formájában, és csak nem fürtözött indexeket hozzon létre, hogy a lekérdezések futhassanak.
A kupac, mint emlékszik, véletlenszerű sorrendben tárolja az adatokat. A tárolási alrendszer általában a beillesztés sorrendjében ad hozzá adatokat a táblához, de a tárolási alrendszer is szereti a sorokat mozgatni a hatékonyabb tárolás érdekében. Ennek eredményeként nincs esélye megjósolni, hogy az adatok milyen sorrendben kerülnek tárolásra.
Ha a lekérdezőmotornak egy nem fürtözött index előnye nélkül kell adatokat találnia, akkor teljes körűen átvizsgálja a táblázatot, hogy megtalálja a szükséges sorokat. Nagyon kis asztalokon ez általában nem jelent problémát, de ahogy a kupac mérete nő, a teljesítmény gyorsan csökken. Természetesen egy nem fürtözött index is segíthet, ha egy mutatót használ arra a fájlra, oldalra és sorra, ahol a szükséges adatok tárolódnak - általában ez sokkal több legjobb alternatíva pásztázva az asztalt. Még így is nehéz összehasonlítani a fürtözött index előnyeit, ha figyelembe vesszük a lekérdezés teljesítményét.
A kupac azonban bizonyos helyzetekben segíthet a teljesítmény javításában. Vegyünk egy táblázatot sok beszúrással, de kevés frissítéssel vagy törléssel. Például egy naplót tároló táblázat elsősorban értékek beszúrására szolgál, amíg archiválásra nem kerül. A kupacban nem fog megjelenni a lapozás és az adatok töredezettsége, mint a fürtözött indexeknél, mivel a sorok egyszerűen hozzáadódnak a kupac végéhez. Az oldalak túlzott felosztása jelentős hatással lehet a teljesítményre, és nem a jó értelemben. Általánosságban elmondható, hogy a kupac lehetővé teszi az adatok viszonylag fájdalommentes beszúrását, és nem kell megküzdenie a tárolási és karbantartási többletköltségekkel, mint a fürtözött indexeknél.
De nem az adatok frissítésének és törlésének hiánya tekinthető az egyetlen oknak. Az adatok mintavételének módja is fontos tényező. Például ne használjon kupacot, ha gyakran kérdez le adattartományokat, vagy ha a lekérdezett adatokat gyakran rendezni vagy csoportosítani kell.
Mindez azt jelenti, hogy csak akkor érdemes fontolóra vennie a kupac használatát, ha nagyon kis táblákkal dolgozik, vagy a táblával való teljes interakciója adatok beszúrására korlátozódik, és a lekérdezések rendkívül egyszerűek (és nem fürtözött indexeket használ különben is). Ellenkező esetben maradjon egy jól megtervezett fürtözött indexnél, például egy egyszerű növekvő kulcsmezőben definiáltnál, például egy széles körben használt oszlopnál IDENTITÁS.

Hogyan módosíthatom az alapértelmezett indexkitöltési tényezőt?

Az alapértelmezett indexkitöltési tényező megváltoztatása egy dolog. Az alapértelmezett arány működésének megértése más kérdés. Előbb azonban tegyen néhány lépést hátra. Az index kitöltési tényezője meghatározza, hogy az oldalon mekkora területet kell tárolni az index alsó szintjén (a levél szintjén) a kitöltés megkezdése előtt új oldal. Például, ha az együttható 90-re van állítva, akkor az index növekedésével az oldal 90%-át elfoglalja, majd a következő oldalra lép.
Alapértelmezés szerint az indexkitöltési tényező értéke be van kapcsolva SQL Server 0, ami megegyezik a 100-zal. Ennek eredményeként minden új index automatikusan örökli ezt a beállítást, kivéve, ha a kódban kifejezetten a rendszer szabványos értékétől eltérő értéket ad meg, vagy nem módosítja az alapértelmezett viselkedést. Használhatod SQL Server Management Studio az alapértelmezett érték módosításához vagy egy rendszerben tárolt eljárás futtatásához sp_configure. Például a következő készlet T-SQL parancsok az együttható értékét 90-re állítja (először át kell váltani a speciális beállítások módba):
EXEC sp_configure "show speciális beállítások", 1; GO RECONFIGURE; GO EXEC sp_configure "kitöltési tényező", 90; GO RECONFIGURE; MEGY
Az indexkitöltési tényező értékének módosítása után újra kell indítania a szolgáltatást SQL Server. Most ellenőrizheti a beállított értéket az sp_configure futtatásával a második argumentum megadása nélkül:
EXEC sp_configure "kitöltési tényező" GO
Ennek a parancsnak 90 értéket kell visszaadnia. Ennek eredményeként az összes újonnan létrehozott index ezt az értéket fogja használni. Ezt úgy tesztelheti, hogy létrehoz egy indexet, és lekérdezi a kitöltési tényező értékét:
HASZNÁLATA AdventureWorks2012; -- az adatbázisod GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE objektum_azonosító = objektum_azonosító("Személy.Személy") AND name="ix_people_lastname";
IN ebben a példában létrehoztunk egy nem fürtözött indexet a táblán Személy az adatbázisban AdventureWorks2012. Az index létrehozása után a sys.indexes rendszertáblákból megkaphatjuk a kitöltési tényező értékét. A lekérdezésnek 90-et kell visszaadnia.
Képzeljük el azonban, hogy töröltük az indexet, és újra létrehoztuk, de most megadtunk egy konkrét kitöltési tényező értéket:
CREATE NONCLUSTERED INDEX ix_people_lastname ON Személy.Személy(Vezetéknév) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE objektum_azonosító = objektum_azonosító("Személy.Személy") AND name="ix_people_lastname";
Ezúttal utasításokat adtunk hozzá VELés opció fillfactor indexkészítési műveletünkhöz INDEX LÉTREHOZÁSAés megadta a 80. Operator értéket KIVÁLASZTÁS most a megfelelő értéket adja vissza.
Eddig minden nagyon egyszerű volt. Ebben az egész folyamatban igazán megéghet, ha létrehoz egy indexet, amely alapértelmezett együtthatóértéket használ, feltételezve, hogy ismeri ezt az értéket. Például valaki a szerver beállításain trükközik, és annyira makacs, hogy az indexkitöltési tényezőt 20-ra állítja. Eközben Ön folytatja az indexek létrehozását, feltételezve, hogy az alapértelmezett érték 0. Sajnos nincs mód a kitöltésre. tényezőt mindaddig, amíg nem hoz létre indexet, majd ellenőrzi az értéket, ahogy a példákban tettük. Ellenkező esetben meg kell várnia a pillanatot, amikor a lekérdezés teljesítménye annyira leesik, hogy gyanakodni kezd valamire.
Egy másik probléma, amellyel tisztában kell lennie, az indexek újraépítése. Az index létrehozásához hasonlóan az index kitöltési tényezőjének értékét is megadhatja az újraépítéskor. A create index paranccsal ellentétben azonban a rebuild nem használja a kiszolgáló alapértelmezett beállításait, annak ellenére, hogy milyennek tűnik. Még inkább, ha nem adja meg konkrétan az indexkitöltési tényező értékét, akkor SQL Server annak az együtthatónak az értékét fogja használni, amellyel ez az index a szerkezetátalakítás előtt létezett. Például a következő művelet ALTER INDEXújraépíti az imént létrehozott indexet:
ALTER INDEX ix_people_lastname ON Személy.Személy REBUILD; GO SELECT fill_factor FROM sys.indexes WHERE objektum_azonosító = objektum_azonosító("Személy.Személy") AND name="ix_people_lastname";
A kitöltési tényező értékének ellenőrzésekor 80-as értéket kapunk, mivel ezt az index utolsó létrehozásakor adtuk meg. Az alapértelmezett értéket figyelmen kívül hagyja.
Mint látható, az indexkitöltési tényező értékének megváltoztatása nem olyan nehéz. Sokkal nehezebb megismerni az aktuális értéket és megérteni, mikor alkalmazzák. Ha az indexek létrehozásakor és újraépítésekor mindig konkrétan megadja az együtthatót, akkor mindig tudja a konkrét eredményt. Hacsak nem kell attól tartanod, hogy valaki más ne csavarja el újra a szerver beállításait, ami miatt az összes index nevetségesen alacsony indexkitöltési tényezővel újjáépül.

Lehetséges-e fürtözött indexet létrehozni egy olyan oszlopon, amely ismétlődőket tartalmaz?

Igen és nem. Igen, létrehozhat fürtözött indexet egy olyan kulcsoszlopon, amely ismétlődő értékeket tartalmaz. Nem, a kulcsoszlop értéke nem maradhat nem egyedi állapotban. Hadd magyarázzam el. Ha nem egyedi fürtözött indexet hoz létre egy oszlopon, a tárolómotor egy egységesítőt ad az ismétlődő értékhez, hogy biztosítsa az egyediséget, és így azonosítani tudja a fürtözött tábla minden sorát.
Dönthet például úgy, hogy fürtözött indexet hoz létre egy ügyféladatokat tartalmazó oszlopon Vezetéknév a vezetéknév megtartása. Az oszlop a Franklin, Hancock, Washington és Smith értékeket tartalmazza. Ezután ismét beilleszti az Adams, Hancock, Smith és Smith értékeket. De a kulcsoszlop értékének egyedinek kell lennie, így a tárolómotor megváltoztatja a duplikátumok értékét, hogy azok valahogy így nézzenek ki: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 és Smith5678.
Első pillantásra ez a megközelítés jónak tűnik, de egy egész érték növeli a kulcs méretét, ami problémát jelenthet, ha nagy számú ismétlődés van, és ezek az értékek egy nem klaszterezett index vagy egy idegen index alapjává válnak. kulcs hivatkozás. Ezen okok miatt, amikor csak lehetséges, próbáljon meg egyedi fürtözött indexeket létrehozni. Ha ez nem lehetséges, akkor legalább próbáljon meg nagyon magas egyedi értékű oszlopokat használni.

Hogyan kerül tárolásra a tábla, ha nincs fürtözött index?

SQL Server kétféle táblát támogat: a fürtözött táblákat, amelyek fürtözött indexszel és kupactáblákkal vagy csak kupacokkal rendelkeznek. A fürtözött táblákkal ellentétben a kupacban lévő adatok semmilyen módon nincsenek rendezve. Lényegében ez egy halom (halom) adat. Ha hozzáad egy sort egy ilyen táblázathoz, a tárolómotor egyszerűen hozzáfűzi azt az oldal végéhez. Ha az oldal megtelt adatokkal, az új oldalra kerül. A legtöbb esetben érdemes egy fürtözött indexet létrehozni egy táblán, hogy kihasználhassa a rendezési lehetőségeket és a gyorsabb lekérdezéseket (próbálja meg a keresést telefonszám semmilyen elv szerint nem rendezett címjegyzékben). Ha azonban úgy dönt, hogy nem hoz létre fürtözött indexet, akkor is létrehozhat egy nem fürtözött indexet a kupacban. Ebben az esetben minden indexsornak egy kupacsorra mutató mutatója lesz. Az index tartalmazza a fájlazonosítót, az oldalszámot és az adatsorszámot.

Mi a kapcsolat az értékek egyediségi megszorításai és a táblaindexekkel rendelkező elsődleges kulcs között?

Az elsődleges kulcs és egy egyedi megszorítás biztosítja, hogy az oszlopban lévő értékek egyediek legyenek. Egy táblához csak egy elsődleges kulcs hozható létre, és nem tartalmazhat értékeket NULL. Több korlátozást is létrehozhat egy tábla értékének egyediségére vonatkozóan, és mindegyiknek egyetlen rekordja lehet NULL.
Az elsődleges kulcs létrehozásakor a tárolómotor egyedi fürtözött indexet is létrehoz, ha még nem jött létre fürtözött index. Azonban felülbírálhatja az alapértelmezett viselkedést, és létrejön egy nem fürtözött index. Ha az elsődleges kulcs létrehozásakor létezik fürtözött index, akkor egy egyedi, nem fürtözött index jön létre.
Egyedi megszorítás létrehozásakor a tárolómotor egyedi, nem fürtözött indexet hoz létre. Megadhatja azonban egy egyedi fürtözött index létrehozását, ha korábban még nem jött létre.
Általánosságban elmondható, hogy az egyedi érték megszorítása és az egyedi index egy és ugyanaz.

Miért nevezik a fürtözött és nem fürtözött indexeket B-tree-nek az SQL Serverben?

Az SQL Server alapvető indexei – fürtözött vagy nem fürtözött – az indexcsomópontoknak nevezett oldalkészletek között vannak elosztva. Ezek az oldalak meghatározott hierarchiába vannak rendezve egy fastruktúrával, amelyet kiegyensúlyozott fának neveznek. On felső szint van egy gyökércsomópont, alul, terminális levélcsomópontok, közbenső csomópontokkal a felső és alsó szint között, amint az az ábrán látható:

A gyökércsomópont biztosítja a fő belépési pontot azon lekérdezések számára, amelyek az indexen keresztül próbálnak lekérni adatokat. Ebből a csomópontból kiindulva a lekérdező motor lefelé navigációt kezdeményez a hierarchikus struktúrában az adatokat tartalmazó megfelelő levélcsomóponthoz.
Képzeljük el például, hogy érkezett egy kérés a 82-es kulcsértéket tartalmazó sorok kiválasztására. A lekérdezési alrendszer a gyökércsomóponttól kezdi meg a munkát, amely egy megfelelő köztes csomópontra, esetünkben 1-100-ra hivatkozik. Az 1-100 közbülső csomópontból átmenet van az 51-100 csomópontba, onnan pedig a 76-100 végső csomópontba. Ha ez egy fürtözött index, akkor a csomóponti levél a kulcshoz tartozó 82-es sor adatait tartalmazza. Ha ez egy nem fürtözött index, akkor az indexlevél tartalmaz egy mutatót a fürtözött táblára vagy egy adott sorra. a kupac.

Hogyan javíthatja egy index a lekérdezés teljesítményét, ha az összes indexcsomóponton be kell járnia?

Először is, az indexek nem mindig javítják a teljesítményt. A túl sok helytelenül létrehozott index ingoványba teszi a rendszert, és rontja a lekérdezések teljesítményét. Helyesebb azt mondani, hogy ha az indexeket körültekintően alkalmazzák, jelentős teljesítménynövekedést érhetnek el.
Gondolj egy hatalmas könyvre, amely a teljesítményhangolásról szól SQL Server(papír változat, nem elektronikus változat). Képzelje el, hogy információkat szeretne találni az erőforrás-irányító konfigurálásával kapcsolatban. Ujjával oldalanként végighúzhatja a teljes könyvet, vagy megnyithatja a tartalomjegyzéket, és megtudhatja a keresett információkkal együtt a pontos oldalszámot (feltéve, hogy a könyv megfelelően van indexelve, és a tartalom megfelelő indexekkel rendelkezik). Ezzel minden bizonnyal jelentős időt takaríthat meg, annak ellenére, hogy először egy teljesen más struktúrához (az indexhez) kell hozzáférnie, hogy az elsődleges struktúrából (a könyvből) megkapja a szükséges információkat.
Mint egy könyvmutató, egy tárgymutató SQL Server lehetővé teszi, hogy precíz lekérdezéseket futtasson a szükséges adatokon, ahelyett, hogy a táblázatban található összes adatot teljesen átvizsgálná. Kis táblák esetén a teljes vizsgálat általában nem jelent problémát, de a nagy táblák sok oldalnyi adatot foglalnak el, ami jelentős lekérdezés-végrehajtási időt eredményezhet, hacsak nincs olyan index, amely lehetővé teszi a lekérdezőmotor számára, hogy azonnal lekérje az adatok helyes helyét. Képzelje el, hogy térkép nélkül eltéved egy többszintű útkereszteződésben egy nagyobb metropolisz előtt, és megkapja az ötletet.

Ha az indexek olyan nagyszerűek, miért nem hoz létre egyet minden oszlopban?

Egyetlen jó cselekedet sem maradhat büntetlenül. Legalábbis az indexeknél ez a helyzet. Természetesen az indexek remekül működnek mindaddig, amíg operátorlekérési lekérdezéseket futtat KIVÁLASZTÁS, de amint elkezdődik az operátorok gyakori hívása BESZÁLLÍTÁS, FRISSÍTÉSÉs TÖRÖL, így a táj nagyon gyorsan változik.
Amikor az üzemeltetőtől adatigénylést kezdeményez KIVÁLASZTÁS, a lekérdezőmotor megkeresi az indexet, végighalad annak fastruktúráján, és felderíti a keresett adatokat. Mi lehetne egyszerűbb? De a dolgok megváltoznak, ha olyan változtatási nyilatkozatot kezdeményezel, mint pl FRISSÍTÉS. Igen, az utasítás első részében a lekérdezőmotor ismét használhatja az indexet a módosítandó sor megkeresésére – ez jó hír. És ha egy sorban egyszerű adatmódosítás történik, amely nem befolyásolja a kulcsoszlopok változásait, akkor a változtatási folyamat teljesen fájdalommentes lesz. De mi van akkor, ha a változtatás hatására az adatokat tartalmazó oldalak felosztásra kerülnek, vagy egy kulcsoszlop értéke megváltozik, aminek következtében az átkerül egy másik indexcsomópontba – ez azt eredményezi, hogy az indexet esetleg át kell szervezni, ami az összes kapcsolódó indexet és műveletet érinti. , ami a termelékenység széles körű csökkenését eredményezi.
Hasonló folyamatok fordulnak elő operátor hívásakor TÖRÖL. Az index segíthet a törölni kívánt adatok megtalálásában, de maga az adatok törlése az oldalak átrendezését eredményezheti. Az üzemeltetővel kapcsolatban BESZÁLLÍTÁS, minden index fő ellensége: elkezd nagy mennyiségű adatot hozzáadni, ami az indexek megváltozásához és átszervezéséhez vezet, és mindenki szenved.
Tehát vegye figyelembe az adatbázis lekérdezésének típusait, amikor meggondolja, hogy milyen típusú indexeket és hányat kell létrehozni. A több nem jelent jobbat. Mielőtt új indexet adna egy táblához, ne csak az alapvető lekérdezések költségét számolja ki, hanem az elfoglalt terület mennyiségét is lemezterület, az egészség és az indexek fenntartásának költsége, ami dominóhatáshoz vezethet más műveleteknél. Az indextervezési stratégia a megvalósítás egyik legfontosabb szempontja, és számos szempontot kell magában foglalnia, az index méretétől, az egyedi értékek számától kezdve az index által támogatott lekérdezések típusáig.

Szükséges-e fürtözött indexet létrehozni egy elsődleges kulccsal rendelkező oszlopon?

Létrehozhat fürtözött indexet bármely megfelelő oszlopban szükséges feltételeket. Igaz, hogy a fürtözött index és az elsődleges kulcs megszorítása egymásnak készült, és a mennyben egyezik, ezért értse meg azt a tényt, hogy amikor létrehoz egy elsődleges kulcsot, akkor automatikusan létrejön egy fürtözött index, ha még nem előtt létrejött. Azonban dönthet úgy, hogy egy fürtözött index máshol jobban teljesít, és döntése gyakran indokolt lesz.
A fürtözött index fő célja, hogy a táblázat összes sorát az index meghatározásakor megadott kulcsoszlop alapján rendezze. Ez biztosítja gyors keresésés könnyű hozzáférést biztosít a táblázat adataihoz.
A tábla elsődleges kulcsa jó választás lehet, mert egyedileg azonosítja a táblázatok minden sorát anélkül, hogy további adatokat kellene hozzáadnia. Bizonyos esetekben legjobb választás Lesz egy helyettesítő elsődleges kulcs, amely nem csak egyedi, de kis méretű is, és amelynek értékei szekvenciálisan nőnek, ami hatékonyabbá teszi az ezen az értéken alapuló, nem fürtözött indexeket. A lekérdezésoptimalizáló a fürtözött index és az elsődleges kulcs kombinációját is kedveli, mert a táblák összekapcsolása gyorsabb, mint más módon, amely nem használ elsődleges kulcsot és a hozzá tartozó fürtözött indexet. Mint mondtam, ez egy mennyben készült gyufa.
Végül azonban érdemes megjegyezni, hogy a fürtözött index létrehozásakor több szempontot is figyelembe kell venni: hány nem fürtözött index fog alapulni, milyen gyakran változik a kulcsindex oszlop értéke, és hogyan nagy. Ha a fürtözött index oszlopainak értékei megváltoznak, vagy az index nem a várt módon működik, akkor a táblázat összes többi indexe is érintett lehet. A fürtözött indexnek a legmaradandóbb oszlopon kell alapulnia, amelynek értékei meghatározott sorrendben nőnek, de nem véletlenszerűen változnak. Az indexnek támogatnia kell a tábla leggyakrabban elért adataira irányuló lekérdezéseket, így a lekérdezések teljes mértékben kihasználják azt a tényt, hogy az adatok a gyökércsomópontokon, az index levelein vannak rendezve és elérhetők. Ha az elsődleges kulcs megfelel ennek a forgatókönyvnek, akkor használja. Ha nem, válasszon másik oszlopkészletet.

Mi van, ha indexel egy nézetet, az továbbra is nézet?

A nézet egy virtuális tábla, amely egy vagy több táblából állít elő adatokat. Lényegében ez egy elnevezett lekérdezés, amely adatokat kér le a mögöttes táblákból, amikor lekérdezi az adott nézetet. Javíthatja a lekérdezés teljesítményét, ha ebben a nézetben fürtözött indexet és nem fürtözött indexeket hoz létre, hasonlóan ahhoz, ahogyan indexeket hoz létre egy táblán, de a fő figyelmeztetés az, hogy először fürtözött indexet kell létrehoznia, majd létrehozhat egy nem fürtözött indexet.
Ha indexelt nézetet (materializált nézetet) hoz létre, akkor maga a nézetdefiníció különálló entitás marad. Végül is ez csak egy keménykódolt operátor KIVÁLASZTÁS, az adatbázisban tárolva. De az index egy teljesen más történet. Ha fürtözött vagy nem fürtözött indexet hoz létre egy szolgáltatón, az adatokat a rendszer fizikailag a lemezre menti, akárcsak egy normál indexet. Ezenkívül, ha az alapul szolgáló táblákban megváltoznak az adatok, a nézet indexe automatikusan megváltozik (ez azt jelenti, hogy érdemes elkerülni a gyakran változó táblák nézeteinek indexelését). Mindenesetre a nézet nézet marad - a táblák nézete, de egy pillanatnyilag végrehajtott, a hozzá tartozó indexekkel.
Mielőtt indexet hozhatna létre egy nézeten, annak számos megkötésnek meg kell felelnie. Például egy nézet csak alaptáblákra hivatkozhat, más nézetekre nem, és ezeknek a tábláknak ugyanabban az adatbázisban kell lenniük. Valójában sok más korlátozás is létezik, ezért feltétlenül ellenőrizze a dokumentációt SQL Server minden piszkos részletért.

Miért használjunk lefedő indexet az összetett index helyett?

Először is győződjünk meg arról, hogy megértjük a kettő közötti különbséget. Az összetett index egyszerűen egy szabályos index, amely egynél több oszlopot tartalmaz. Több kulcsoszlop is használható annak biztosítására, hogy a táblázat minden sora egyedi legyen, vagy több oszlop is lehet az elsődleges kulcs egyediségének biztosítására, vagy megpróbálhatja optimalizálni a gyakran meghívott lekérdezések végrehajtását több oszlopon. Általában azonban minél több kulcs oszlopot tartalmaz egy index, annál kevésbé lesz hatékony az index, ami azt jelenti, hogy az összetett indexeket megfontoltan kell használni.
Amint már említettük, egy lekérdezés nagy hasznot hoz, ha az összes szükséges adat azonnal megtalálható az index lapjain, akárcsak maga az index. Ez nem jelent problémát egy fürtözött index esetében, mert minden adat már megvan (ezért olyan fontos, hogy alaposan átgondolja, amikor fürtözött indexet hoz létre). A leveleken található, nem fürtözött index azonban csak kulcsoszlopokat tartalmaz. Az összes többi adat eléréséhez a lekérdezésoptimalizálónak további lépésekre van szüksége, amelyek jelentős többletköltséget jelenthetnek a lekérdezések végrehajtásához.
Itt jön segítségül a fedezeti index. Ha nem fürtözött indexet definiál, további oszlopokat is megadhat a kulcsoszlopokhoz. Tegyük fel például, hogy az alkalmazás gyakran lekérdezi az oszlopadatokat OrderIDÉs Rendelés dátuma a táblázatban Értékesítés:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
Létrehozhat összetett, nem fürtözött indexet mindkét oszlopban, de a Rendelés dátuma oszlop csak az index karbantartási költségét adja hozzá, anélkül, hogy különösen hasznos kulcsoszlopként szolgálna. A legjobb megoldás egy lefedő index létrehozása a kulcsoszlopon OrderIDés ezenkívül tartalmazott oszlopot Rendelés dátuma:
CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(Rendelésazonosító) INCLUDE (Rendelés dátuma);
Ezzel elkerülhetőek a redundáns oszlopok indexelésének hátrányai, miközben a lekérdezések futtatásakor továbbra is megmaradnak az adatok levelekben való tárolásának előnyei. A benne foglalt oszlop nem része a kulcsnak, de az adatok a levél csomópontján, az indexlevélen tárolódnak. Ez további többletköltség nélkül javíthatja a lekérdezés teljesítményét. Ezenkívül a lefedő indexben szereplő oszlopokra kevesebb korlátozás vonatkozik, mint az index kulcsoszlopaira.

Számít-e az ismétlődések száma egy kulcsoszlopban?

Index létrehozásakor meg kell próbálnia csökkenteni a duplikációk számát a kulcsoszlopokban. Vagy pontosabban: próbálja meg a lehető legalacsonyabb szinten tartani az ismétlési gyakoriságot.
Ha összetett indexszel dolgozik, akkor a duplikáció az összes kulcsoszlop egészére vonatkozik. Egy oszlop sok ismétlődő értéket tartalmazhat, de az összes indexoszlop között minimálisnak kell lennie az ismétlődésnek. Például létrehozhat egy összetett, nem fürtözött indexet az oszlopokon KeresztnévÉs Vezetéknév, akkor rendelkezhet egy értékkészlettel, amely egyenlő Johnnal és egy Doe-val, de szeretne minél több értéket kevesebb érték John Doe, vagy jobb, ha csak egy jelentése John Doe.
A kulcsoszlop értékeinek egyediségi arányát indexszelektivitásnak nevezzük. Minél több egyedi érték van, annál nagyobb a szelektivitás: az egyedi index a lehető legnagyobb szelektivitással rendelkezik. A lekérdezőmotor nagyon szereti a magas szelektivitási értékű oszlopokat, különösen, ha ezek az oszlopok a leggyakrabban végrehajtott lekérdezések WHERE záradékaiban szerepelnek. Minél szelektívebb az index, a lekérdezőmotor annál gyorsabban tudja csökkenteni az eredményül kapott adatkészlet méretét. Hátránya A lényeg persze az, hogy a viszonylag kevés egyedi értékkel rendelkező oszlopok ritkán lesznek jó jelöltek az indexeléshez.

Létrehozható-e nem fürtözött index egy kulcsoszlop adatainak csak egy meghatározott részhalmazán?

Alapértelmezés szerint a nem fürtözött index a táblázat minden sorához egy sort tartalmaz. Természetesen ugyanezt elmondhatjuk a fürtözött indexről is, feltételezve, hogy egy ilyen index egy táblázat. De ha nem fürtözött indexről van szó, az egy az egyhez kapcsolat fontos fogalom, mert a verziótól kezdve SQL Server 2008, lehetősége van szűrhető index létrehozására, amely korlátozza a benne szereplő sorokat. A szűrt index javíthatja a lekérdezés teljesítményét, mert... kisebb méretű, és szűrt, pontosabb statisztikákat tartalmaz, mint az összes táblázatos - ez jobb végrehajtási tervek készítéséhez vezet. A szűrt index kevesebb tárhelyet és alacsonyabb karbantartási költségeket is igényel. Az index csak akkor frissül, ha a szűrőnek megfelelő adatok megváltoznak.
Ezenkívül könnyen létrehozható egy szűrhető index. Az operátorban INDEX LÉTREHOZÁSA csak jelezni kell AHOL szűrő állapota. Például kiszűrheti az összes NULL-t tartalmazó sort az indexből, ahogy az a kódban is látható:
CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber NEM NULL;
Valójában minden olyan adatot kiszűrhetünk, amely nem fontos a kritikus lekérdezésekben. De légy óvatos, mert... SQL Server számos korlátozást szab a szűrhető indexekre, például nem lehet szűrhető indexet létrehozni egy nézeten, ezért figyelmesen olvassa el a dokumentációt.
Az is előfordulhat, hogy hasonló eredményeket érhet el egy indexelt nézet létrehozásával. A szűrt indexnek azonban számos előnye van, például csökkentheti a karbantartási költségeket és javíthatja a végrehajtási tervek minőségét. A szűrt indexek online is újraépíthetők. Próbálja ki ezt indexelt nézetben.

És megint egy kicsit a fordítótól

Ennek a fordításnak a Habrahabr oldalain való megjelenésének célja az volt, hogy meséljen vagy emlékeztessen Önt a SimpleTalk blogra RedGate.
Sok szórakoztató és érdekes bejegyzést tesz közzé.
Nem állok kapcsolatban egyetlen cég termékével sem RedGate, sem az eladásukkal.

Ahogy ígértem, könyvek azoknak, akik többet szeretnének tudni
Három nagyon jó könyvet ajánlok magamtól (a linkek ide vezetnek meggyújt verziók a boltban amazon):

Elvileg megnyithat egyszerű indexeket Címkék hozzáadása

Microsoft SQL Server 2012 T-SQL Fundamentals (Fejlesztői referencia)
Szerző: Itzik Ben-Gan
Megjelenés dátuma: 2012. július 15
A szerző, mestersége mestere adja alapismeretek az adatbázisokkal való munkáról.
Ha mindent elfelejtett, vagy soha nem tudott, mindenképp érdemes elolvasni.

Az Oracle DBMS táblasoraihoz való gyors hozzáférés érdekében indexeket használnak. Az indexek gyors hozzáférést biztosítanak az adatokhoz olyan műveletek során, amelyek viszonylag kis számú táblázatsort választanak ki.

Bár az Oracle korlátlan számú indexet engedélyez egy táblán, az indexek csak akkor hasznosak, ha a lekérdezések felgyorsítására használják őket. Ellenkező esetben csak helyet foglalnak el, és csökkentik a szerver teljesítményét az indexelt oszlopok frissítésekor. Használnia kell az EXPLAIN PLAN (végrehajtási és statisztikai terv) szolgáltatást annak meghatározásához, hogy az indexeket hogyan használják fel a lekérdezésekben. Néha, ha az indexet alapértelmezés szerint nem használja, lekérdezési tippeket használhat az index használatához.

Indexek létrehozása a táblaadatok beszúrása után

Általában az indexek létrehozása előtt szúr be vagy tölt be adatokat egy táblába. Ellenkező esetben az indexek frissítésének többlete lelassítja a beszúrási vagy betöltési műveleteket. Az egyetlen kivétel ez alól a szabály alól a fürtkulcson lévő index. Csak üres fürthöz hozható létre.

Váltson ideiglenes táblaterületre, hogy elkerülje a szabad területtel kapcsolatos problémákat az indexek létrehozásakor

Ha olyan táblán hoz létre indexet, amely már tartalmaz adatokat, az Oracle további memóriát igényel a rendezéshez. Ez az index létrehozója számára lefoglalt rendezési memóriaterületet használja (az egyes felhasználók számára lefoglalt mennyiséget a SORT_AREA_SIZE inicializálási paraméter állítja be), és az Oracle szervernek az index létrehozása során lefoglalt ideiglenes szegmensekből is ki kell törölnie és ki kell cserélnie az információkat. Ha az index nagyon nagy, javasoljuk, hogy tegye a következőket:

Hozzon létre egy új ideiglenes táblaterületet a CREATE TABLESPACE utasítással.
Adja meg ezt az új ideiglenes területet az ALTER USER utasítás TEMPORARY TABLESPACE paraméterében.
Hozzon létre egy indexet a CREATE INDEX utasítással.
Dobja el ezt a táblaterületet a DROP TABLESPACE paranccsal. Ezután használja az ALTER USER utasítást az eredeti táblaterület ideiglenes visszaállításához.

Válassza ki a megfelelő táblázatokat és oszlopokat az indexeléshez

Az alábbi irányelvek alapján határozza meg, mikor kell indexet létrehozni.

Hozzon létre indexet, ha gyakran viszonylag kis számú (kevesebb, mint 15%) sort kér le egy nagy táblából. Ez a százalékos arány nagymértékben függ a táblázat relatív vizsgálati sebességétől és attól, hogy a soradatok milyen fürtözöttek az indexkulcsban. Minél nagyobb a böngészési sebesség, annál kisebb a százalékos arány, minél fürtözöttebbek a soradatok, annál nagyobb a százalékos arány.
Az összeillesztéseknél használt index oszlopok a több tábla összekapcsolásának teljesítményének javítása érdekében.
Az indexek automatikusan jönnek létre az elsődleges és egyedi kulcsok alapján.
A kis táblázatokat nem kell indexelni. Ha észreveszi, hogy a lekérdezés végrehajtási ideje jelentősen megnőtt, akkor valószínűleg nagy lett.

Egyes oszlopok egyértelmű jelöltek az indexeléshez. A következő jellemzőkkel rendelkeznek:

az oszlopban lévő értékek viszonylag egyediek;
nagy értéktartomány (alkalmas normál indexekhez);
kis értéktartomány (alkalmas bitindexekhez);
nagyon ritka oszlopok (sok definiálatlan, "üres" érték), de a lekérdezések többnyire értelmes sorokra vonatkoznak. Ebben az esetben előnyösebb olyan összehasonlítást végezni, amely megfelel az összes nem nulla értéknek:
WHERE COL_X > -9,99 *teljesítmény(10, 125) helyett
A WHERE COL_X NEM NULL Ennek az az oka, hogy az első eset a COL_X indexet használja (feltételezve, hogy a COL_X oszlop numerikus típusú).

Korlátozza a táblázatonkénti indexek számát

Minél több index, annál nagyobb a többletköltség a tábla módosításakor. Sorok hozzáadásakor vagy törlésekor a táblázat összes indexe frissül. Ha egy oszlopot frissítenek, akkor az összes indexet is frissíteni kell, amelyben részt vesz.

Indexek esetében mérlegelnie kell a lekérdezések teljesítménynövekedését, illetve a frissítések teljesítménybüntetését. Például, ha a tábla elsősorban csak olvasható, akkor széles körben használhatja az indexeket; de ha a táblázatot gyakran frissítik, célszerű minimalizálni az indexek használatát.

Válassza ki az oszlopok sorrendjét az összetett indexekben

Bár az oszlopok tetszőleges sorrendben megadhatók a CREATE INDEX utasításban, a CREATE INDEX utasításban lévő oszlopok sorrendje befolyásolhatja a lekérdezés teljesítményét. Általában a leggyakrabban használt oszlopok szerepelnek az indexben az első helyen. Létrehozhat egy összetett indexet (több oszlop használatával), amellyel lekérdezheti az indexben szereplő összes oszlopot, vagy csak néhányat.

Gyűjtsön statisztikákat az indexek megfelelő használatához

Az indexek hatékonyabban használhatók, ha az adatbázis statisztikákat gyűjt és karbantart a lekérdezésekben használt táblákról. Az index létrehozása során megadással gyűjthet statisztikákat kulcsszó COMPUTE STATISTICS a CREATE INDEX utasításban. Mivel az adatok folyamatosan frissülnek és az értékek eloszlása változik, a statisztikákat a DBMS_STATS.GATHER_TABLE_STATISTICS és a DBMS_STATS.GATHER_SCHEMA_STATISTICS eljárások segítségével rendszeresen frissíteni kell.

A szükségtelen indexek megsemmisítése

Az index törlése a következő esetekben történik:

ha egy index használata nem javítja a lekérdezés teljesítményét. Ez a helyzet akkor fordul elő, ha a tábla túl kicsi, vagy ha a táblának sok sora van, de ezek közül néhány indexbejegyzés;
ha ajánlatkérései nem használják az indexet;
ha az indexet is eldobják az újraépítés előtt.

Egy index eldobásakor az indexszegmens minden kiterjedése visszakerül abba a táblaterületbe, amelyhez tartozik, és elérhetővé válik a többi objektum számára a táblaterületen. Az indexek automatikusan törlődnek, amikor a tábla, amelyhez készültek, megsemmisül.

Ha sok rekord van a táblázatban, akkor nagyon nehéz lehet megtalálni a megfelelő rekordot. Az adatkeresés brute-force módszerrel történik, azaz minden táblarekordot átnézünk az első rekordtól az utolsó rekordig, ami sok időt vesz igénybe. A táblázatban található adatok könnyebb megtalálása érdekében indexeket használnak. Az index, amelyet néha mutatónak is neveznek, egy tábla rekordjának sorszáma. Az index egy mező értékei vagy több mező értékei alapján épül fel. Egy mező értékéből összeállított indexet egyszerűnek, a két vagy több mező értékein alapuló indexet pedig összetettnek nevezzük. Az index felépítése során a táblázat rekordjai a jövőbeli index mezőjének (vagy mezőinek) értékei szerint vannak rendezve. Ezután a táblázat első sorához az egyes indexszámot, a másodikhoz a kettes indexszámot rendeljük hozzá, és így tovább a táblázat végéig.

Mind az egyszerű, mind az összetett indexnek megvan a maga típusa (Ture). Az elsődleges (elsődleges) index (kulcs) egy mező vagy mezőcsoport, amely egyedileg azonosít egy rekordot, azaz az elsődleges index értékei egyediek (nem ismétlődnek). Egy relációs adatbázisban minden táblának csak egy elsődleges kulcsa lehet. Egy táblának sok idegen kulcsa lehet, és a következő típusok valamelyike lesz:

Jelölt – jelölt az elsődleges kulcsra vagy az alternatív kulcsra. Az elsődleges kulcs összes tulajdonságával rendelkezik.

Egyedi - lehetővé teszi az értékek megkettőzését abban a mezőben, amelyre épül, de csak az azonos indexmezőértékkel rendelkező rekordok csoportjának első rekordja jelenik meg.

Normál - nem ír elő semmilyen korlátozást az indexmező értékeire és a rekordok képernyőn történő megjelenítésére. Az index csak a rekordok megjelenítési sorrendjét szabályozza. Ez a legnépszerűbb indextípus.

A táblák közötti kapcsolat indexeken keresztül valósul meg, amelyeket kulcsoknak nevezünk.

Az összeállított indexet egy speciális indexfájl tárolja. Ha egy indexfájl csak egy indexet tárol, akkor azt egyindexnek nevezik, és a kiterjesztése .idx. A sok indexet tároló indexfájlokat többindexes fájloknak nevezzük, és .cdx kiterjesztéssel rendelkeznek. Minden indexet, amely többindexes fájlban tárol, címkének nevezzük. Minden címkének saját egyedi neve van.

A többindexes fájlok két típusa létezik: egyszerűen többindexes fájlok (lásd fent) és strukturális többindexes fájlok. A szerkezeti többindexes fájl neve megegyezik a táblával, amelyhez tartozik (az egyetlen különbség a fájl kiterjesztésében van), és a következő tulajdonságokkal rendelkezik:

Automatikusan nyílik saját asztallal;

Nem lehet bezárni, de nem lehet fő.

Egy táblában több indexfájl is lehet, egy- és többindexes is. A FoxPro régebbi verziói Multi-Index fájlokat használnak.

Index létrehozása

Kétféleképpen hozhat létre indexet.

A. A parancs használatával:

INDEX BE<индексное выражение>HOGY< idx-fájl> | CÍMKE<имя тега>

Az opciók célja:

<index kifejezés>- annak a mezőnek (vagy mezőknek) a neve, amelyek értékei alapján az indexet fel kell építeni. Összetett index összeállításakor a mezőnevek listája + (plusz) jellel van elválasztva. Ha egy összetett indexet a következőképpen épít fel:

A numerikus mezők esetében az index a mezőértékek összege alapján épül fel;

Karaktermezők esetén az index először az első mező értéke szerint épül fel, az első mező ismétlődő értékei esetén pedig a második mező értékei szerint; ha az első és a második mező értékei megismétlődnek - a harmadik mező értékeinek megfelelően stb.;

Mezők szerint különböző típusok, akkor először a mezőértékek egy típusra, általában karakteresre redukálódnak, majd felépül az index.

Az indexkifejezés hossza nem haladhatja meg a 254 karaktert.

HOGY< idx-fájl>- az egyindexes fájl neve szerepel.

CÍMKE<имя тега> - jelzi a címke nevét a többindexes fájlban. Az opció használata esetén a létrehozott tag a megadott többindexes fájlba kerül, ha pedig hiányzik a szükséges többindexes fájl, akkor egy strukturális többindexes fájl épül fel. Ha az opciót kihagyjuk, a létrehozott címke az aktuális többindexes fájlba kerül.

FOR<условие> - beállítja a megfelelő táblarekordok indexének kiválasztási módját<условию>.

KOMPAKT- vezérli egy kompakt egyindexes fájl létrehozását. A FoxPro régebbi verziói nem használatosak.

CSERE- az indexet csökkenő sorrendben építi fel. Alapértelmezés szerint az index növekvő sorrendben épül fel (NÖVEKEDŐ). Egyindexű fájlok esetén csak növekvő sorrendben hozhat létre indexet. Ha az INDEX ON... parancs használata előtt kiadja a SET COLLATE parancsot, akkor csökkenő sorrendben készíthet egy indexű fájlt.

EGYEDÜLÁLLÓ- egyedi indexet épít. Ha az indexmező(k) ismétlődő értékeket tartalmaznak, akkor csak az első rekord kerül be az indexbe, a többi rekord pedig nem lesz elérhető.

ADALÉKANYAG- az újonnan létrehozott indexfájl nem zárja be az adott időpontban már megnyitott indexfájlokat. Ha az opciót kihagyjuk, az újonnan létrehozott indexfájl bezárja az összes korábban megnyitott indexfájlt.

b. A főmenü használata:

Ebben az esetben az index vagy a tábla létrehozásakor, vagy a táblaszerkezet módosításakor jön létre. Ehhez a Table Designer párbeszédpanelen válassza az Index fület (3.1. ábra).

Minden index egy sorral van leírva a Table Designer párbeszédpanelen.

A Név oszlop a többindexes fájl címkenevét adja meg. Ha valamelyik többindexes fájlt korábban megnyitották, akkor az újonnan felépített index a megnyitott többindexes fájlba kerül. Ha az index egy táblafájl létrehozásával egyidejűleg épül fel, vagy a táblafájl nem tartalmaz többindexes fájlokat, akkor az újonnan felépített index egy automatikusan létrehozott strukturális többindexes fájlba kerül.

A legördülő listával ellátott Ture oszlopban az érvényes indextípusok egyike látható. Ha az index egy adatbázis részét képező táblához készült, akkor négy lehetséges érték van: Elsődleges, Jelölt, Egyedi és Rendszeres. Ha az index egy ingyenes táblához készült, akkor az Elsődleges érték nem szerepel a legördülő listában.

A Kifejezés oszlop felsorolja azoknak a mezőknek a nevét, amelyek értékeit index felépítéséhez kell használni. Ha összetett indexet építünk, kényelmesebb a kifejezéskészítő használata, amely a beviteli mezőtől jobbra található gombra kattintva indítható el.

A Szűrő oszlopban logikai feltételt állíthat be, és nem minden táblarekordhoz, hanem csak a szűrési feltételnek megfelelő rekordokhoz hozhat létre indexet. Ez a grafikon kifejezéskészítővel is fel van szerelve. Mindkét építő tartalma és megjelenése megegyezik (3.2. ábra).

ábrán. A 3.2. ábra egy összetett index felépítését mutatja be két karaktermezővel, az ush_step és az uch_zvan karaktermezőkkel (az uch címke nevét a kifejezéskészítő képernyőn történő meghívása előtt rendelték hozzá). Az összetett index felépítését jelző "+" jel a String legördülő listából származik.

A String legördülő listák érvényesek karakterlánc függvények. Hasonlóképpen, a Math, Logical és Date legördülő listák az érvényes matematikai, logikai és dátumfüggvényeket listázzák. Ezekből a legördülő listákból a kívánt funkciót a bal egérgombbal lehet kiválasztani. A mezőnevek (Mezők listája) és a változók nevei (Változólista) a bal egérgomb dupla kattintásával választhatók ki. Az eredményül kapott kifejezés a Kifejezés ablakba kerül.

A Táblázatból legördülő lista megadja annak a táblának a nevét, amelyből az index felépítéséhez szükséges mezőket átveszi. Ha szeretné, bármelyik táblát megrendelheti az aktuális adatbázisból, és bármelyik mezőt felhasználhatja az index felépítéséhez.