--Az index egy olyan struktúra a lemezen, amely táblázathoz vagy nézethez van társítva, és felgyorsítja a sorok visszakeresését a táblázatból vagy nézetből. Az index egy vagy több oszlopból összeállított kulcsokat tartalmaz egy táblázatban vagy nézetben. Ezek a kulcsok egy kiegyensúlyozott fastruktúrában vannak tárolva, amely támogatja a sorok gyors keresését kulcsértékeik alapján az SQL Serverben.

-- A fürtözött indexek kulcsértékeik alapján rendezik és tárolják az adatsorokat táblázatokban vagy nézetekben. Ezek az értékek az indexdefinícióban szereplő oszlopok. Táblánként csak egy fürtözött index van, mivel az adatsorok csak egyetlen sorrendben rendezhetők.
--A tábla adatsorait a rendszer csak akkor tárolja rendezési sorrendben, ha a tábla fürtözött indexet tartalmaz. Ha egy táblának van fürtözött indexe, akkor a táblát fürtözöttnek nevezzük. Ha egy táblának nincs fürtözött indexe, akkor az adatsorokat a rendszer egy halomnak nevezett rendezetlen struktúrában tárolja.

--A nem klaszterezett index szerkezete pontosan megegyezik a fürtözött indexével, de két fontos különbség van:
--a nem csoportosított index nem változtatja meg a táblázat sorainak fizikai sorrendjét, és a nem fürtözött index levéloldalai indexkulcsokból és könyvjelzőkből állnak.

-- A fürtözött indexek gyorsabb adatlekérést biztosítanak, mint a nem fürtözött indexek. Általában frissítéskor is gyorsabbak, de nem akkor, ha sok frissítés történik ugyanazon a helyen a kapcsolat közepén.

-- Valamilyen oknál fogva a fürtözött indexek gyorsabban futnak, mint a nem fürtözött indexek. Amikor a rendszer fürtözött indexet vizsgál, nem kell elhagynia a B-fa struktúráját az adatlapok vizsgálatához, mert az ilyen oldalak már jelen vannak a fa levélszintjén.

--A nem fürtözött index több I/O műveletet igényel, mint a megfelelő fürtözött index.

-- A nem fürtözött indexnek be kell olvasnia az adatlapokat a B-fa beolvasása után, vagy ha van fürtözött index a tábla másik oszlopában, akkor a nem fürtözött indexnek be kell olvasnia a fürtözött index B-fa szerkezetét. .

--Tehát egy fürtözött index lényegesen gyorsabb lesz, mint egy táblázatvizsgálat, még akkor is, ha a szelektivitása meglehetősen gyenge (a lekérdezés sok sort ad vissza)

TÁBLÁZAT LÉTREHOZÁSA tsql.dbo.NI
ID int NEM NULL,
T char(8) NULL
);

TÁBLÁZAT LÉTREHOZÁSA tsql.dbo.NCI
ID int NEM NULL,
T char(8) NULL
);

-- Hozzon létre egy fürtözött indexet

IX_1. CSOMATOLT INDEX LÉTREHOZÁSA
ON tsql.dbo.NCI(ID);

-- Hozzon létre egy nem fürtözött indexet egy táblán

NEM KLUSTEREZETT INDEX LÉTREHOZÁSA IX_2
ON tsql.dbo.NCI(T);

-- Tesztadatok hozzáadása
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

WHILE @i > 0
KEZDŐDIK
beszúrás a tsql.dbo.NI-be értékek(@i, @t + CAST(@i AS char(6)));
beszúrás a tsql.dbo.NCI-be értékek(@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
VÉGE

-- Lekérdezések egy táblázatban indexekkel
SELECT ID, T FROM tsql.dbo.NCI
RENDELÉS IDŐVEL, T

SELECT ID, COUNT(*) AS C FROM tsql.dbo.NCI
CSOPORT AZONOSÍTÓ SZERINT, T

SELECT ID, T FROM tsql.dbo.NCI
WHERE ID > 4000 ÉS ID< 55000 AND T LIKE "T%"

-- Lekérdezés mindkét index használatával
tsql HASZNÁLATA;
SELECT CAST(dbo.NCI.ID AS VARCHAR)
A dbo.NCI-től
GROUP BY dbo.NCI.ID
UNION ALL
KIVÁLASZTÁSA dbo.NCI.T
A dbo.NCI-től
CSOPORT: dbo.NCI.T

-- Index információk
SELECT index_type_desc, index_depth, index_level,
oldalszám, rekord_szám
FROM sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL , "RÉSZLETES");

-- Indexek törlése
HA LÉTEZIK (VÁLASSZA A nevet A sys.indexes
WHERE név = N"IX_1")
A IX_1 INDEX DROP A tsql.dbo.NCI-re;

HA LÉTEZIK (VÁLASSZON nevet A sys.indexes
WHERE név = N"IX_2")
A IX_2 INDEX DROP A tsql.dbo.NCI-re;

Az előző cikkben bemutattuk a relációs adatbázisok optimalizálásának módjait, és megvitattuk, hogyan működnek a fürtözött és nem fürtözött indexek az adatbázis-lekérdezések végrehajtási idejének optimalizálásával összefüggésben. Most itt az ideje, hogy ezt a tudást a gyakorlatba is átültessük azáltal, hogy megtanuljuk, hogyan hozhatunk létre optimalizálási indexeket egy MS SQL adatbázishoz.

Hadd emlékeztesselek a Staffs táblázatséma meghatározására, amellyel dolgozni fogunk:

Személyzeti asztal

Tegyük fel, hogy létre kell hoznunk egy nem fürtözött indexet a Staffs táblához, amely optimalizálja a következő lekérdezést:

AZONOSÍTÓ, név, állás VÁLASZTÁSA AZOKBÓL, AHOL A BÉR > 1000 ÉS A FÉNY NEM NULL

Az indexkulcs a BÉR és a Fénykép oszlop lesz, mivel a kijelölést ezek a mezők szűrik. Az Id, Name és Job oszlopok pedig az indexben szereplő oszlopok lesznek.

Az általános parancs szintaxisa a következő:

HASZNÁLAT MEGY

NEM KLUSTERES INDEX LÉTREHOZÁSA TOVÁBB (ASC -- indexkulcs oszlopok)

TARTALMAZ ( -- tartalmazott oszlopok) GO

A mi esetünkben a kérés így fog kinézni:

(Bérezés, Fénykép) TARTALMAZZA (Id, Név, Munka) GO

Létrehoztunk egy nem fürtözött indexet. Vagy inkább egy nem klaszterezett fedőindex. Ez azt jelenti, hogy az index tartalmazza a lekérdezés végrehajtásához szükséges összes mezőt, és az SQL Server nem fog hozzáférni az alaptáblához a lekérdezés végrehajtásakor.

Ha a kódunk ilyen lenne:

CREATE NONCLUSTERED INDEX IDX_StaffsSearch ON Stuffs

(Bérezés, Fénykép) TARTALMAZ (Id) GO

Ebben az esetben az index megszűnik lefedő index lenni, mivel nem tartalmazza a lekérdezésben használt összes oszlopot. Az optimalizáló továbbra is ezt az indexet fogja használni a lekérdezés végrehajtásakor, de hatékonysága egy nagyságrenddel csökken, mivel hozzáférést igényel az alaptáblához.

A fürtözött index a következő paranccsal jön létre:

CLASZTERES INDEX LÉTREHOZÁSA IDX_Stsffsid ON Stuffs (Id)

Itt egy egyedi fürtözött index jött létre a tábla elsődleges kulcsa (Id oszlop) alapján.

Valódi példa

Most dolgozzunk ki egy forgatókönyvet, amelyben reálisan tudjuk értékelni a teljesítménynövekedés mértékét indexek használata esetén.

Hozzunk létre egy új adatbázist:

ADATBÁZIS LÉTREHOZÁSA TestDB;

És egyetlen Vevők táblázat, amely négy oszlopból áll majd:

TÁBLÁZAT LÉTREHOZÁSA .(

NEM NULL, NULL, NULL, NULL) GO

Most pedig töltsük fel a táblázatunkat véletlenszerű adatokkal. Az Id oszlopot ciklusban növeljük, és a táblázat fennmaradó három oszlopát véletlenszerű számokkal töltjük ki a véletlen függvény egy sajátos változatával:

DECLARE @i int = 0;

Miközben én< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(checksum(newid())), abs(checksum(newid())), abs(checksum(newid())) SET @i = @i + 1; VÉGE

Ez a szkript félmillió rekordot ad a táblázathoz, ezért légy türelmes, a szkript legalább 3 percig futni fog.

Minden készen áll a tesztre. Értékeljük a lekérdezés teljesítményjellemzőit. Mivel a lekérdezés végrehajtási ideje az adott géptől függhet, egy függetlenebb mutatót fogunk elemezni - a logikai olvasások számát.

A statisztikagyűjtési mód engedélyezéséhez futtassa a következő parancsot:

Mostantól minden kérés végrehajtása után az Üzenetek lapon hozzáférünk a kérés végrehajtására vonatkozó statisztikákhoz, az alábbiak szerint:

Minket csak a logikai olvasási paraméter értéke érdekel.

Tehát a táblázatunkban még nincsenek indexek. Futtassuk a következő három lekérdezést, és rögzítsük az egyes lekérdezések logikai olvasásainak számát az alábbi eredménytáblázatban:

1) SELECT Id, Num1, Num2 FROM ügyfelek WHERE Id = 2000

2) SELECT Id, Num1, Num2 FROM FROM ügyfelek WHERE Id >= 0 ÉS Id< 1000

3) SELECT Id, Num1, Num2 FROM FROM ügyfelek WHERE Id >= 0 ÉS Id< 5000

Ezek a lekérdezések 1 sort, 1000 sort és 5000 sort adnak vissza. Indexek nélkül a teljesítménymutató (logikai leolvasások száma) minden lekérdezésnél megegyezik és 1621. Vigyük be az adatokat az eredménytáblába:

Azt látjuk, hogy a második és harmadik lekérdezésnél, amikor meglehetősen nagy számú sort adunk vissza, az általunk létrehozott index nem javított a teljesítményen. Az egyetlen sort visszaadó lekérdezésnél azonban óriási volt a gyorsulás. Így arra a következtetésre juthatunk, hogy az egyetlen eredményt visszaadó lekérdezések optimalizálásakor érdemes nem lefedő indexeket létrehozni.

Most hozzunk létre egy fedőindexet, ezzel érjük el a maximális teljesítményt.

Először töröljük az előző indexet:

A TestDB GO DROP INDEX HASZNÁLATA Customers.TestIndex1

És hozzunk létre egy új indexet:

NEM CLUSTERED INDEX LÉTREHOZÁSA TesztIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

Most futtassuk le a lekérdezéseinket harmadszor, és írjuk az eredményeket egy táblázatba:

Nincsenek indexek
Nem fedő index
Borító index

Könnyen belátható, hogy a teljesítménynövekedés óriási volt. Így a lekérdezés végrehajtási sebességét tízszeresére növeltük. Több millió sort tároló adatbázis futtatásakor ez a teljesítménynövekedés nagyon észrevehető lesz.

Ebben a cikkben egy példát néztünk meg az adatbázisok indexek létrehozásával történő optimalizálására. Érdemes megjegyezni, hogy az indexek létrehozása minden kérés esetében tisztán egyedi folyamat. A lekérdezés teljesítményét valóban optimalizáló index létrehozásához alaposan elemeznie kell magát a lekérdezést és annak végrehajtási tervét.

A hatékony indexkészítés az egyik legjobb módszer az adatbázis-alkalmazások teljesítményének javítására. Indexek használata nélkül az SQL Server olyan, mint egy olvasó, aki úgy próbál szót találni a könyvben, hogy minden oldalt megnéz. Ha a könyvnek tárgymutatója (indexe) van, az olvasó sokkal gyorsabban tud keresni a szükséges információkat.

Index hiányában az SQL szerver, amikor adatokat kér le egy táblából, a teljes táblát átvizsgálja, és minden sort ellenőrzi, hogy a lekérdezési feltételek teljesülnek-e. Egy ilyen teljes vizsgálat katasztrofális lehet az egész rendszer teljesítményére nézve, különösen, ha sok adat van a táblázatokban.

Az adatbázisokkal végzett munka során az egyik legfontosabb feladat egy optimális index felépítése a rendszer teljesítményének javítása érdekében. A legtöbb nagyobb adatbázis tartalmaz eszközöket a lekérdezés végrehajtási tervének megtekintéséhez, valamint az indexek hangolásához és optimalizálásához. Ez a cikk néhány jó ökölszabályt emel ki, amelyek az adatbázisban lévő indexek létrehozásakor vagy módosításakor érvényesek. Először is nézzük meg azokat a helyzeteket, amikor az indexelés javítja a teljesítményt, és ahol az indexelés árthat.

Hasznos indexek

Tehát a táblaindexelés hasznos lehet, ha egy adott rekordot keres a táblában a Where utasítás használatával. Ilyen lekérdezések például olyan lekérdezések, amelyek értéktartományra keresnek, olyan lekérdezések, amelyek egy pontos értéket egy adott értékhez illesztenek, vagy olyan lekérdezések, amelyek két táblát egyesítenek.

Például a Northwind adatbázisra vonatkozó alábbi lekérdezések hatékonyabban futnak, ha indexet építenek az Egységár oszlopban.

Törlés azokból a termékekből, ahol UnitPrice=1
Válasszon *-ot a termékek közül, ahol az egységár 14 és 16 között van

Mivel az indexelemek rendezve vannak tárolva, az indexelés akkor is hasznos, ha a Rendezési záradék használatával lekérdezést készítünk. Index nélkül a rekordok betöltése és rendezése a lekérdezés futása közben történik. A UnitPrice-on alapuló index lehetővé teszi az index egyszerű átvizsgálását, és a sorok hivatkozással történő lekérését a következő kérés feldolgozásakor. Ha csökkenő sorrendben szeretné rendezni a sorokat, egyszerűen beolvassa az indexet fordított sorrendben.

Válassza a * Termékek közül rendelés UnitPrice ASC szerint

A Rekord csoportosítása a Group by utasítás használatával gyakran rendezést is igényel, így az Egységár oszlopban lévő index felépítése is hasznos lesz a következő lekérdezéshez, amely megszámolja egy termék darabszámát az egyes árakon.

Válassza ki a darabszámot (*), az egységárat a termékcsoportból egységár szerint

Az indexek hasznosak egy oszlop egyedi értékének fenntartásához, mivel a DBMS könnyen megnézheti az indexet, hogy megnézze, létezik-e már az érték. Emiatt az elsődleges kulcsok mindig indexelve vannak.

Az indexelés hátrányai

Az indexek rontják a rendszer teljesítményét a rekordok módosítása során. Minden alkalommal, amikor egy lekérdezést hajtanak végre egy tábla adatainak módosítására, az indexnek is változnia kell. Az indexek optimális számának kiválasztásához tesztelnie kell az adatbázist és figyelemmel kell kísérnie a teljesítményét. A statikus rendszerek, ahol az adatbázisokat elsősorban adatlekérésre, például jelentéskészítésre használják, több indexet is tartalmazhatnak a csak olvasható lekérdezések támogatására. A nagyszámú tranzakciót tartalmazó adatbázisoknak az adatok módosításához kis számú indexre lesz szükségük a nagyobb átviteli sebesség érdekében.

Az indexek további helyet foglalnak el a lemezen és a RAM-ban. A pontos méret a táblázatban lévő rekordok számától, valamint az index oszlopainak számától és méretétől függ. A legtöbb esetben ez nem jelentős probléma, mivel a lemezterületet ma már könnyű feláldozni a jobb teljesítmény érdekében.

Optimális index felépítése

Egyszerű index

Az egyszerű index olyan index, amely egy táblázat egyetlen mezőjének értékeit használja. Az egyszerű index használata két okból is előnyös. Először is, egy adatbázis futtatása nagy terhelést jelent a merevlemezen. A nagy indexkulcsok több I/O művelet végrehajtására kényszerítik az adatbázist, ami korlátozza a teljesítményt.

Másodszor, mivel az indexelemeket gyakran bevonják az összehasonlításokba, a kisebb indexeket könnyebb összehasonlítani. E két okból kifolyólag egyetlen egész oszlop jobb index, mert kicsi és könnyen összehasonlítható. A karakterláncok viszont karakterenkénti összehasonlítást igényelnek, és figyelmet igényelnek a paraméterek kezelésére.

Szelektív index

A leghatékonyabb indexek azok, amelyekben alacsony az ismétlődő értékek százaléka. Például egy olyan város telefonkönyve, amelyben szinte mindenki Smith vezetéknévvel rendelkezik, nem lesz olyan hasznos, ha a bejegyzések vezetéknév szerint vannak rendezve.

Az egyedi értékek nagy százalékával rendelkező indexet szelektív indexnek is nevezik. Nyilvánvalóan az egyedi index a legnagyobb szelektivitással rendelkezik, mivel nem tartalmaz duplikált értékeket. Sok DBMS képes nyomon követni az egyes indexekre vonatkozó statisztikákat, és felismeri, hogy az egyes indexek hány nem ismétlődő értéket tartalmaznak. Ezt a statisztikát a lekérdezés-végrehajtási terv generálásakor használják.

Lefedő indexek

Az indexek egy adatoszlopból állnak, amelyre maga az index épül, és egy mutatóból a megfelelő sorra. Olyan, mint egy könyv indexe: csak a kulcsszavakat és egy linket tartalmaz egy oldalra, ahol további információkért kereshet. A DBMS általában követi a mutatókat az index egy sorára, hogy összegyűjtse a lekérdezéshez szükséges összes információt. Ha azonban az index tartalmazza a lekérdezéshez szükséges összes oszlopot, akkor az információ a tábla elérése nélkül is lekérhető.

Tekintsünk egy indexet a UnitPrice oszlopban, amelyről fentebb már volt szó. A DBMS csak az indexelemeket használhatja a következő lekérdezés végrehajtásához.

Válassza ki a Szám(*), az egységárat a termékcsoportból egységár szerint

Az ilyen típusú lekérdezéseket lefedő lekérdezésnek nevezik, mivel az összes lekérdezett oszlop lekérhető egyetlen indexből. A legfontosabb lekérdezésekhez érdemes lehet egy lefedő index létrehozását a lehető legjobb teljesítmény érdekében. Az ilyen indexek valószínűleg összetettek (egynél több oszlopot használnak), ami az első elv ellentéte: egyszerű indexeket kell létrehozni. Nyilvánvaló, hogy egy indexben az oszlopok optimális számának megválasztása csak az adatbázis különböző helyzetekben történő teljesítményének tesztelésével és monitorozásával értékelhető.

Klaszter index

Sok adatbázisnak van egy speciális indexe egy táblán, ahol egy sor összes adata az indexben található. Az SQL Serverben az ilyen indexet fürtözött indexnek nevezik. A fürtözött index összehasonlítható egy telefonkönyvvel, mivel minden indexelem tartalmazza az összes szükséges információt, és nem tartalmaz hivatkozásokat további adatok megszerzéséhez.

Van egy általános szabály – minden nem triviális táblának fürtözött indexel kell rendelkeznie. Ha egy táblán csak egy indexet lehet létrehozni, tegye fürtözötté. Az SQL Serverben az elsődleges kulcs létrehozásakor automatikusan létrejön egy fürtözött index (ha még nem tartalmaz ilyet), az elsődleges kulcs oszlopot használja indexelési kulcsként. A fürtözött index a leghatékonyabb index (ha használjuk, lefedi a teljes lekérdezést), és sok DBMS-ben egy ilyen index segít a táblák tárolására igényelt terület hatékony kezelésében, mivel egyébként (fürtözött index építése nélkül) a tábla sorai egy rendezetlen szerkezet, amelyet kupacnak neveztek.

Legyen óvatos, amikor fürtözött index oszlopait választja ki. Ha módosít egy rekordot, és módosítja egy oszlop értékét egy fürtözött indexben, az adatbázis kénytelen lesz újraépíteni az indexelemeket (a rendezett sorrend megtartása érdekében). Ne feledje, hogy a fürtözött index indexelemei az összes oszlopértéket tartalmazzák, így egy oszlop értékének megváltoztatása hasonló a Delete utasítás végrehajtásához, amelyet egy Insert utasítás követ, ami nyilvánvalóan teljesítményproblémákat okoz, ha gyakran végzik el. Emiatt a fürtözött indexek gyakran egy elsődleges kulcsból és egy idegen kulcs oszlopból állnak. Ha a kulcsértékek változnak, akkor nagyon ritkán változnak.

Következtetés

Az adatbázisban használandó helyes indexek meghatározása a rendszer alapos elemzését és tesztelését igényli. Az ebben a cikkben bemutatott gyakorlatok jó szabályok az indexek létrehozásához. E módszerek alkalmazása után újra kell tesztelnie az adott alkalmazást az adott hardver-, memória- és működési feltételek mellett.

A magas termelékenység elérésének egyik legfontosabb módja SQL szerver 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 a könyvben található indexekhez, amelyek segítségével gyorsan megtalálhatja a szükséges információkat. Ebben a cikkben rövid áttekintést adok az indexekről SQL szerveré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 szerver 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 például 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 szerver.
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 lekérdez egy indexelt oszlopot, a lekérdezőmotor a gyökércsomópont tetejéről indul, és lefelé halad a közbenső csomópontokon, és minden közbenső réteg részletesebb információkat tartalmaz az 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. Ebben az esetben az első oldal 1-től 100-ig, a második pedig 101-től 200-ig terjedő értékre mutat, í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. A fürtözött index fontos jellemzője, hogy minden é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 ( kulcs), amely ezt az indexet határozza meg, és tartalmaz egy mutatót 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 ezek 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 vezeték- vagy utónév ismétlődése is lehetséges.
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 szerver 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 szerver 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 legjobb megközelítés a fürtözött index használata olyan oszlopokon, amelyek egyedi értékekkel rendelkeznek, és nem engedélyezik a NULL-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. Általában 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 FirstName = "Charlie") az első helyen kell lennie 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 Rendelés azonosítóÉ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 – ez általában sokkal jobb alternatíva a táblázatvizsgálatnak. 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 akárhogyan 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 egy másik kérdés. Előbb azonban tegyen néhány lépést hátra. Az index kitöltési tényezője határozza meg, hogy az oldalon mekkora területet kell tárolni az index alsó szintjén (a levél szintjén), mielőtt új oldalt töltene fel. 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 szerver 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. Te tudod használni 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 szerver. Most ellenőrizheti a beállított értéket az sp_configure futtatásával a megadott második argumentum 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";
Ebben a példában egy nem fürtözött indexet hoztunk létre egy 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á VAL 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 szerver 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. 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 szerver kétféle táblát támogat: 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 tele van adatokkal, az új oldalra kerül. A legtöbb esetben érdemes fürtözött indexet létrehozni egy táblán, hogy kihasználja a rendezhetőséget és a lekérdezési sebességet (próbáljon elképzelni egy telefonszámot egy rendezetlen 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 az 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 az nem tartalmazhat értékeket NULLA. Több korlátozást is létrehozhat egy tábla értékének egyediségére vonatkozóan, és mindegyiknek egyetlen rekordja lehet NULLA.
Az elsődleges kulcs létrehozásakor a tárolómotor egyedi fürtözött indexet is létrehoz, ha még nem hozott létre fürtözött indexet. 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 hozta létre.
Általánosságban elmondható, hogy az egyedi értékkényszer és az egyedi index 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 egy meghatározott hierarchiába vannak rendezve egy kiegyensúlyozott fának nevezett fastruktúrával. A legfelső szinten a gyökércsomópont, az alsó szinten a levél csomópontok találhatók, a felső és alsó szint között köztes csomópontokkal, ahogy 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 navigációt kezdeményez a hierarchikus struktúrán lefelé 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 szerver(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 szerver 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, vegye figyelembe nemcsak az alapul szolgáló lekérdezések költségeit, hanem a felhasznált lemezterület mennyiségét, a funkcionalitás és az indexek karbantartásának költségeit is, amelyek dominóhatáshoz vezethetnek 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?

Bármely oszlopon létrehozhat fürtözött indexet, amely megfelel a szükséges feltételeknek. 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 gyors keresést é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. Egyes esetekben a legjobb választás egy helyettesítő elsődleges kulcs, amely nemcsak egyedi, hanem kis méretű is, és amelynek értékei szekvenciálisan nőnek, hatékonyabbá téve 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 történő összekapcsolás, 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 mekkora. Ha a fürtözött index oszlopaiban lévő értékek megváltoznak, vagy az index nem a várt módon működik, akkor a táblázat összes többi indexe hatással 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 az alapul szolgáló 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 szerver 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 kulcsoszlopot 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 Rendelés azonosítóÉs Rendelés dátuma az asztalban Értékesítés:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
Létrehozhat egy összetett, nem fürtözött indexet mindkét oszlopban, de a Rendelés dátuma oszlop csak indexkarbantartási többletköltséget ad hozzá anélkül, hogy különösen hasznos kulcsoszlopként szolgálna. A legjobb megoldás az lenne, ha a kulcsoszlopon létrehoznánk egy fedőindexet Rendelés azonosítóé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ők a redundáns oszlopok indexelésének hátrányai, miközben a lekérdezések futtatásakor továbbra is fenntartják az adatok levelekben való tárolásának előnyeit. 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 sok John Doe értékkel és sok Doe értékkel rendelkezhet, de azt szeretné, hogy a lehető legkevesebb John Doe érték legyen, vagy lehetőleg csak egy John Doe érték legyen.
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. A hátránya természetesen 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 szerver 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 meg lehet nyitni egyszerű indexeket

kezdőknek

index

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ő, mestere a mestere, alapvető ismereteket ad az adatbázisokkal való munkavégzésről. Ha mindent elfelejtett, vagy soha nem tudott, mindenképp érdemes elolvasni.

ROWID indexek olyan adatbázis-objektumok, amelyek megjelenítik a táblázat oszlopában lévő összes értéket, valamint a táblázat összes olyan sorának ROWID-jét, amely az oszlop értékeit tartalmazza.

ROWID egy pszeudooszlop, amely egy táblázat egy sorának egyedi azonosítója, és valójában az adott sor pontos fizikai helyét írja le. Ezen információk alapján Jóslat ezt követően megtalálhatja a táblázat sorához tartozó adatokat. Minden alkalommal, amikor egy sort áthelyeznek, exportálnak, importálnak vagy bármilyen más műveletet, amely megváltoztatja a helyét, a ROWID vonalat, mert más fizikai pozíciót foglal el. Adattároláshoz ROWID 80 bit (10 bájt) szükséges. Azonosítók ROWID négy összetevőből áll: objektumszám (32 bit), relatív fájlszám (10 bit), blokkszám (22 bit) és sorszám (16 bit). Ezek az azonosítók 18 karakterből álló sorozatként jelennek meg, jelezve az adatok helyét az adatbázisban, és minden karakter 64-es formátumban jelenik meg, amely A-Z, a-z, 0-9, + és / karakterekből áll. Az első hat karakter az adatobjektum száma, a következő három a relatív fájl száma, a következő hat a blokkszám, az utolsó három pedig a sorszám.

Példa:

SELECT család, ROWID diáktól;

FAM ROWID

——————————————

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

Az adatbázisban Jóslat Az indexeket különböző célokra használják: az adatbázisban lévő értékek egyediségének biztosítására, a táblában lévő rekordok keresésének teljesítményének javítására stb. táblázatban szereplő adatokhoz. BAN BEN Jóslat indexek a LONG oszlopok kivételével bármely táblázatoszlopon létrehozhatók. Az indexek különbséget tesznek a sebességre nem érzékeny alkalmazások és a nagy teljesítményű alkalmazások között, különösen akkor, ha nagy táblákkal dolgozik. Mielőtt azonban az index létrehozása mellett döntene, mérlegelnie kell a rendszerteljesítmény előnyeit és hátrányait. A teljesítmény nem javul, ha egyszerűen megad egy indexet, és elfelejti.

Bár a legnagyobb teljesítménynövekedést az okozza, ha olyan indexet hoz létre egy oszlopon, amelyben minden érték egyedi, hasonló eredményeket kaphat az ismétlődő vagy NULL értékeket tartalmazó oszlopok esetében. Az index létrehozásához nem szükséges, hogy az oszlopértékek egyediek legyenek. Íme néhány javaslat, amelyek segítenek elérni a kívánt teljesítménynövekedést szabványos index használatakor, valamint megvizsgáljuk a teljesítmény és a lemezterület-felhasználás egyensúlyával kapcsolatos problémákat is az index létrehozásakor.

Ha indexeket használ a táblázatokban lévő információk keresésére, jelentős teljesítményjavulást érhet el az olyan táblákhoz képest, amelyek oszlopai nincsenek indexelve. A megfelelő index kiválasztása azonban egyáltalán nem egyszerű. Természetesen egy olyan oszlop, amelynek értékei mind egyediek, előnyösebb a B-fa indexszel való indexeléshez, de az az oszlop, amely nem felel meg ezeknek a követelményeknek, jó jelölt, amíg a sorok körülbelül 10%-a azonos értékeket tartalmaz. és nem több. A „Switch” vagy „flag” oszlopok, például azok, amelyek egy személy nemére vonatkozó információkat tárolnak, nem alkalmasak B-fa indexekhez. Azok az oszlopok, amelyek kis számú „megbízható érték” tárolására szolgálnak, valamint azok, amelyek tárolnak bizonyos értékek szintén nem megfelelőek, akkor a jelek, például a „megbízhatóság” vagy „megbízhatatlanság”, „aktivitás” vagy „inaktivitás”, „igen” vagy „nem” stb. stb. Végül a fordított kulcsú indexek általában ott használják, ahol fel van szerelve és ahol működik Jóslat Parallel Server, és a maximumra kell növelnie az adatbázis párhuzamosságának szintjét.