Alla SQL Server Index skapas inte lika
Förord
Om du alls har någon erfarenhet av Index, så borde du vara familjär med skillnaden mellan grupperade och icke-grupperade Index. Den här artikeln kommer dock inte att diskutera dem skillnaderna, utan den kommer att behandla huruvida din SQL Server Query Optimizer kommer att använda sig utav dina noggrant konstruerade Index över huvud taget. Du kanske inte är medveten om det, men bara för att en kolumn har ett Index så betyder inte det att din Query Optimizer behöver eller kommer att använda sig utav det. Som du då kanske förstår då så är det bara slöseri med tid, och därmed också en försämring av applikationens prestanda, om du skapar ett Index som inte används. Låt oss titta på varför.
Innehåll
Alla SQL Server Index skapas inte lika
av Brad M. McGehee
Vi ska börja med att titta på ett enkelt exempel. Låt oss anta att vi har en arbetsdatabas, vari vi har en tabell som heter ”orders”. Bland andra kolumner i tabellen så är vi bara intresserade av två av dem: ”orderID” och ”employeeID”. Tabellen innefattar 150 000 poster med ett icke-grupperat Index på ”employeeID”. Låt oss nu säga att vi kör följande SQL-sats:
SELECT orderID FROM orders WHERE employeeID = 5
Det första du bör komma ihåg är att det finns ett icke-grupperat Index satt på ”employeeID” kolumnen i WHERE klausulen. I och med detta så räknar du med att när satsen körs genom Query Optimizer så använder sig Optimizern utav Indexet för att få fram de önskade resultaten.
Tyvärr så kan du inte alltid automatiskt räkna med att den gör det. Bara för att det finns ett Index att tillgå så behöver inte din Query Optimizer alltid använda sig utav det. Det beror på att din Query Analyzer alltid överväger innan Indexet används, huruvida Indexet är användbart eller inte. Om din Query Analyzer analyserar Indexet och inte finner det användbart, så kommer den att ignorera Indexet, och om det behövs så söker den igenom hela tabellen för att få fram de önskade resultaten.
Så vilket Index är användbart? För att kunna svara på den frågan så måste vi förstå att det huvudsakliga syftet med Query Optimizer är att reducera mängden I/O som krävs, samt reducera den medförande tid som krävs för att exekvera en sats. Med andra ord så tittar din Query Optimizer på flera olika sätt att köra satsen för att hitta det alternativ som den tror kommer att kräv minst I/O. Det som kan verka överraskande är att bara för att det finns ett Index att tillgå, så innebär inte det alltid att man genom att använda det reducerar mängden I/O som krävs. I många fall, speciellt med icke-grupperade Index, så kräver det mindre I/O genom att söka igenom hela tabellen istället för att använda sig av ett Index.
Innan din Query Optimizer använder sig av ett Index så analyserar den Indexet först för att se om det är selektivt nog. Och vad innebär det? Selektivitet refererar till det procentuella antalet poster i en tabell som returneras då en SQL-sats körs. En sats anses vara högt selektivt om det returnerar ett väldigt begränsat antal poster. En sats anses följande vara lågt selektivt om det returnerar en hög procent av posterna från en tabell. Generellt sett; om en sats returnerar mer än 5 % av posterna från en tabell, så anses det var lågt selektivt och ett Index kommer högst troligt att användas. Om satsen returnerar 5-10 % av posterna så kan det ske att ett Index används, men det är inte säkert. Då satsen returnerar mer än 10 % av posterna så är det högst troligt att ett Index inte används. Om det då inte finns fler användbara Index att tillgå så kommer troligen hela tabellen att sökas igenom.
Låt oss gå tillbaka till vårt exempel:
SELECT orderID FROM orders WHERE employeeID = 5
Vi kan inte bara genom att titta på satsen avgöra ifall Indexet på employeeID kommer att användas eller inte. Låt oss säga att vi vet att av de 150 000 poster som finns i tabellen så passar villkoret ”employeeID = 5” in på 5 000 av dem. Om vi delar 5 000 på 150 000 så får vi fram 3,3 %. Eftersom 3,3 % är mindre än 5 % så kommer Indexet högst troligen användas. Men låt oss då säga att employeeID = 5” passar in på 25 000 av posterna. Delar vi 25 000 på 150 000 så får vi fram 16,6 %, vilket är större än både 5 % och 10 %. Så det troligaste i det här fallet är att den inte kommer att använda Indexet utan söka igenom hela tabellen istället.
Så hur kan en genomsökning av en tabell reducera mer I/O än när man använder ett Index, likt det icke-grupperade Indexet i vårt exempel? Ett icke-grupperat Index är perfekt då Indexet är högt selektivt, speciellt om du bara vill returnera en post. Men om det är många poster ska returneras och om Indexet inte är särskilt selektivt, så blir det väldigt kostsamt för I/O att överlämna posterna. Anledningen till det här är att din Query Optimizer först måste gå till Indexet för att leta upp rätt data (vilket kräver I/O) för att sedan gå till tabellen i fråga och hämta ut datan (ännu mer I/O). Vid någon punkt bestämmer sig Query Optimizer för att det skulle gå snabbare att söka igenom hela tabellen för de önskade posterna istället för att gå fram och tillbaka från Index till tabell.
Exemplet som ges ovan tillämpas ofta på icke-grupperade Index. Om det istället hade varit ett grupperat Index som användes, så skulle Indexet användas oavsett om det var hög eller låg selektivitet. Detta för att Indexet är tabellen och I/O operationer kan då vara väldigt effektiva.
Så hur vet Query Optimizern om ett åtkomligt Index är selektivt nog för att vara användbart? Den vet det genom att den skaffar sig en Index statistik på varje Index i varje tabell. Index statistiken fungerar som ett histogram av värden som är lagrade i sysindex tabellen. Statistikerna är ett exempel på antalet poster som träffas av Indexet, vilka talar om för Query Optimizer ungefär hur selektivt ett specifikt Index är.
Index statistiken skapas varje gång som ett Index skapas, byggs om, då ett UPDATE STATISTICS kommando körs, samt automatiskt varje gång som Query Optimizer behöver den. Index statistiken återhämtas inte i realtid, eftersom det skulle skapa för mycket arbetslast på servern. Men trots att inte Index statistiken körs i realtid, så kan de gå över tiden. Och ibland kan det hända att Query Optimizer gör ett dåligt val, då inte Index statistiken den använder inte längre är aktuell.
Även fast en Index statistik skulle vara aktuell, så behöver inte Query Optimizer använda sig utav det Index som finns att tillgå. Kom ihåg att Query Optimizer baserar sina val på selektiviteten på ett Index, och att den bara använder Index statistiken för att avgöra selektiviteten på ett Index.
Så om Query Optimizer kan avgöra om ett specifikt Index är användbart eller inte, kan inte vi göra samma sak? Som tur är så finns det ett kommando som låter oss analysera ett Index för att få reda på om det är selektivt nog till att använda.
Anledningen till att vi vill veta om ett specifikt Index är selektivt nog, är för att om det inte är det, så kommer det inte att användas. Och om inte Indexet används så finns det ingen anledning att ha det. Högst troligen kan du genom att ta bort onödiga Index höja applikationens prestanda, för som du kanske vet så saktar Index ned operationer som t ex INSERT, UPDATE och DELETE, eftersom Index kräver så hög arbetslast från Servern. Och om tabellen i fråga ofta utsätts för en mångfald ändringar i databasen, så kan användandet av Index vara orsaken till flaskhalsar. Så vårt mål är att försäkra oss om att selektiviteten av Index är så pass högt så att det är värt att använda, och därmed inte samla på oss Index som inte används.
Kommandot du använder för att mäta selektiviteten för ett Index är:
DBCC SHOW_STATISTICS (tabellnamn, indexnamn)
När du kör kommandot så kan du få ett resultat liknande det nedan, vilket är baserat på en av dem databaser jag använder.
Statistics for INDEX 'in_tran_idx'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- ------- ------------ ------ ------------ ------------------
Feb 24 2001 3:36AM 7380901 7163688 300 2.2528611E-5 0.0
(1 row(s) affected)
All density Columns
2.2528611E-5 in_tran_key
Steps
-----------
1
283
301
340
371
403
456
…
44510
(300 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Resultatet innefattar en hel del information, vilka ligger helt utanför den här artikelns gränser. Det vi dock är intresserade av är densitet värdet ” 2.2528611E-5” som finns under rubriken ”All density”.
Densitet refererar till det genomsnittliga procentuella antalet dubblettposter i ett Index. Om en Indexerad kolumn, så som ”employeeID”, innehåller mycket dubblettdata så säger man att Indexet har en hög densitet. Och om kolumnen innehåller många unika värden så säger man att Indexet har låg densitet.
Densitet har en motsatt relation till selektivitet. Om densitet t ex har ett högt värde, så är selektiviteten låg, vilket i sin tur innebär att Indexet inte kommer att användas. Om densiteten följaktligen har ett lågt värde, så är selektiviteten hög och Indexet kommer förmodligen att användas.
Exempelutskriften ovan visar på en densitet som ligger under 1 %. Det innebär att selektiviteten ligger på över 99 % och att Indexet då förmodligen är väldigt användbart för din Query Optimizer.
Om du är en sofistikerad DBA så har du kanske redan lagt märke till att jag har förenklat den här diskussionen en hel del. Trots det så är syftet med den här artikeln mycket viktig, och syftet är att inte alla Index är likadana. Bara för att det finns ett Index att tillgå så är det inte säkert att din Query Optimizer alltid kommer att använda sig av det.
För DBAs så innebär det här att du bör vara varsam med dina tabellers Index. I mån av tid bör du köra kommandot DBCC SHOW_STATISTICS ibland, för att se hur det egentligen ligger till med selektiviteten hos dina Index. Du kan komma att upptäcka att en del Index inte används. Om det skulle vara så bör du överväga alternativet att ta bort dem, för att på så sätt öka hastigheten på din applikation.
För nya DBAs så kan det verka motstridigt att man kan optimera databasens prestanda genom att ta bort vissa Index, hellre än att ha dem kvar. Men ju mer du lär dig om hur din SQL Server fungerar internt, desto bättre kommer du att förstå de begränsningar som finns då du vill använda Index för att optimera din applikations prestanda.
För att lära dig mer om det här ämnet på djupet så kan du läsa kapitel 15, ”The Query Processor”, i boken ”Inside Microsoft SQL Server 2000” av Kalen Delaney.
0 Kommentarer