Introduktion om index i sql-server

Det är vanligt att efterhand som en applikation – oavsett om det är en webbsida eller en annan typ av program – ökar i användande så uppstår prestandaproblem. Det finns naturligtvis många flaskhalsar, men en av de största kan vara databasservern. En enkel – men oftast dyr, och i längden inte hållbar – åtgärd är att uppgradera servern med mer minne, fler processorer, fler hårddiskar i RAIDsystemet etc. Innan det görs bör man dock kontrollera hur frågorna i applikationen körs, samt hur tabellernas index ser ut.

Nedan listar vi några olika typer av index som finns samt exempel på när de är bra att använda, respektive mindre bra. Först skall vi gå igenom vad ett index egentligen är.

Vad är ett index?

När du ställer en fråga mot en tabell för att hämta data, exempelvis
Select username from users where username like ’john%’
så kommer databasmotorn att börja från början i tabellen och fortsätta tills den hittat alla förekomster där fältet username börjar på john. Om du istället har indexerat kolumnen username så kommer databasmotorn först leta i ett register. Vi kan säga att bokstäverna a-z finns lagrade där. Databasmotorn går direkt till bokstaven j (eftersom det är första bokstaven i john), och där under kan det finnas uppdelat på j till jane och från janet till joe, osv. Hur finmaskat ”registret” är beror på hur många unika poster för din indexerade kolumn som finns. När databasmotorn har slagit upp john i registret så hittar den direkt alla poster som börjar på john. Tidsskillnaden kan vara enormt stor på en stor databas med många poster. Istället för att gå igenom alla rader så behöver databasen endast göra tre-fyra uppslagningar för att hitta rätt, därmed minimeras läsningen från disk (I/O). Index i SQL-Server använder sig av b-tree för att lagra och lokalisera posterna. Varje sida i registret kan innehålla max en viss mängd data, och när en ny sida skapas så innehåller den ett visst tomrum (fillfactor). Detta för att slippa behöva skapa nya sidor vid varje ny post som skapas. Databasmotorn kan även använda sig av index vid update/delete. Det är dock inte gratis – ur prestandasynpunkt – att skapa index. När ett nytt index skapas så måste detta uppdateras vid varje insert/update/delete. Jämfört med att bara behöva uppdatera tabellen när man inte har några index, så måste databasen om man man har tre index på en tabell göra fyra uppdateringar, därför måste man vara försiktig när man skapar index, så att man – totalt sett – inte får sämre prestanda, även om just select blir snabbare.

Klustrat index

Ett klustrat index är det index som i de flesta fall är snabbast. Här lagras datan i tabellen i den ordning som det klustrade indexet. På grund av detta kan det endast finnas ett klustrat index per tabell, därför är det viktigt att vara mycket noggrann när man väljer sitt klustrade index. Det är också på grund av att data lagras i samma ordning som indexet som gör att det är det snabbaste indexet. Detta eftersom när sökningen når sitt mål (hittar rätt) så finns all data representerad direkt på indexet, jämför med icke klustrade index där man bara hittar en pekare till data.
En nackdel med en tabell som har ett klustrat index kan vara de siddelningar (page splits) som uppstår i samband med inserts. När du matar in en ny rad så kan det vara så att den aktuella sidan är full och då måste en ny sida skapas. Hälften av innehållet på den tidigare sidan flyttas då till den nya sidan, vilket naturligtvis tar en del tid. Om vi däremot inte har ett klustrat index på tabellen så kan raden sättas in på vilken sida som helst där det finns en ledig plats. Oftast när du väljer kolumn för klustrat index bör du välja en kolumn som är den som oftast förekommer i dina frågor. Bäst utnyttjat blir det också om du har ett unikt klustrat index. SQL-Server tillåter icke unika klustrade index men för bäst prestanda bör det vara unikt. Notera att primärnyckel inte är det samma som klustrat index. SQL-Server skapar per automatik ett klustrat index för den kolumn som du väljer som primärnyckel, men detta kan du alltså ändra på.

Klustrade index är bra för

– Frågor där du har ett brett spektra på din fråga, exempelvis då du använder between, , order by, join, group by, men även vid sum(), count() osv.
– Frågor där du behöver hämta en unik post (användarid exempelvis). Detta eftersom de data du hämtar ut direkt finns att tillgå.
– Frågor där du hämtar många rader. Det är av samma anledning som vi nämnt tidigare. Datan finns direkt åtkomlig från indexet och behöver inte hämtas genom en extra sökning.
– Ökande kolumner, såsom identity (int) och datum.

Klustrade index är mindre bra för

– Guid, eftersom en guid är ett slumpmässigt värde som gör att tabellen måste sorteras om vid varje ny post i tabellen.

Icke klustrat index

Ett icke klustrat index är ett index bestående av en kolumn. Här lagras på bladnivån i trädet endast en pekare till datan. Den kan då peka mot antingen tabellen direkt, eller mot det klustrade indexet, oavsett vilket så gör detta att en extra uppslagning kan krävas. Sätt inte ett klustrat index på en kolumn som inte är minst 95% unik, det är i de flesta fall onödigt. Exempelvis en kolumn med alternativen ’ja’, ’nej’ är inte 95% unik och därmed kommer troligtvis inte databasmotorn att använda sig av indexet. Det kan dock finnas tillfällen då det faktiskt är bra att indexera fält som inte är speciellt unika, jag skrev om det i mitt inlägg om strategier vid indexering av bitfält.

Icke klustrade index är bra

– För frågor där du hämtar få rader och där indexet är minst 95% unikt.
– Där kolumnen i både where och order by är den samma i frågan.
– För frågor där du använder join (klustrade index är dock ännu bättre)

Om vi ställer följande fråga select username, userid, sex from users where username = ‘john’ mot ett klustrat index bestående av (username, userid) kommer vi direkt att kunna hämta all information, även sex. Om vi däremot ställer samma fråga mot ett icke klustrat index (username, userid) så kommer det icke klustrade indexet att innehålla username och userid, men eftersom vår select innehåller även sex så måste vi gå ner på tabellnivå och hämta den informationen. Om det är många poster som returneras kan det givetvis bli kostsamt.

Composite index (sammansatt)

Ett sammansatt index är ett index bestående av flera kolumner. I vissa fall är ett sammansatt index även ett täckande index, mer om täckande index kan du läsa i nästa avsnitt. Normalt sätt är det bra att inte använda sammansatta index eftersom sammansatta index tenderar att bli breda (många kolumner) vilket i sin tur innebär att de tar mer diskutrymme, kräver mer diskläsning (I/O) vilket resulterar i sämre prestanda. Den vanligaste orsaken till att skapa ett sammansatt index är att öka unikheten för raden. Ett sammansatt index är oftast mest värdefullt där den första kolumnen används i ditt where-villkor. Om du exempelvis har indexet (username, city) och ställer frågan ”where username = ’john’ ” så kommer indexet troligtvis att bli använt. I frågan ”where city = ’gothenburg’ ” kommer det dock troligen inte att användas. Bäst använt blir indexet om du sorterar kolumnerna i ditt index med den som innehåller mest unika poster först och den med minst unika sist. Ibland kan det vara en fördel att dela upp det sammansatta indexet i flera enkolumnsindex eftersom SQL-Server per automatik kan använda sig av flera enkolumnsindex. Det vanliga när man skapar sammansatta index är att tabellen innehåller många kolumner som man vill ställa frågor mot men att det är omöjligt att skapa ett index för varje kolumn (dels eftersom det inte blir effektivt, och dels eftersom SQL-Server troligen inte kan använda alla de enskilda indexen när din fråga blir bred). Det är därför vanligt att man skapar några få men bredare index som passar för de allra flesta frågorna och låter de extremt ovanliga frågorna få sämre prestanda.

Covering index (täckande)

Ett täckande index är egentligen ett sammansatt index. Det är så att det sammansatta indexet blir ett täckande index när alla kolumner som ingår i din select, join och where ingår i indexet. Täckande index är i regel snabbare än sammansatta index, detta eftersom att de data som skall hämtas redan finns på indexet och uppslagning mot datatabellen inte behöver göras. Täckande index kan vara bra att använda där du vet att du kommer att utföra samma fråga om och om igen mot en viss tabell. Ett täckande index kan då innehålla kolumner som har låg unikitet, för att slippa göra extra uppslagningar.

Generella tips för index

  • Indexera en kolumn alltid max en gång. Det finns ingen anledning att skapa två likadana index. Det kan däremot finnas tillfällen vid användningen av sammansatta index då du exempelvis vill skapa ett index på både (username, city) och (city, username). Detta är dock inte speciellt vanligt, utan en bedömning får göras från fall till fall. Det bästa är att testa dina frågor. Tänk dock på att en ändring av ett index kan påverka den generella prestandan i databasen både negativt och positivt.
  • För att kunna genomföra tester för dina indexuppsättningar skall du använda dig av data från en produktionsmiljö. Detta kan vara svårt speciellt när du skapar en helt ny databas, men innan du testar dina index bör du i alla fall fylla din databas med den mängd data som du uppskattar att din databas i produktionsmiljön kommer att innehålla. Detta gäller även datumkolumner.
  • Du bör överväga att lägga index på alla kolumner som frekvent används av dina where, order by, group by, top, distinct frågor. Utan index kommer en table scan att genomföras vilket leder till sämre prestanda. Tänk dock på att ett index på en tabell som anpassas för en viss fråga kan påverka en annan fråga negativt. Du måste därför hela tiden göra bedömningar på för- och nackdelar för dina index. Om det verkligen är värt att skapa detta indexet. Ställ dig frågor som, hur ofta körs frågan, hur kostsamt är mitt index jämfört med att inte ha ett index
  • En tumregel är att alla tabeller skall ha ett klustrat index. Normalt kan denna kolumn vara en identitetskolumn (stegvis ökande) eller datum, men även detta är en bedömningsfråga.
  • Statiska tabeller (tabeller med få eller inga ändringar) kan indexeras mycket mer än tabeller med mycket ändringar. På statiska tabeller kan du även sätta FILLFACTOR och PAD_INDEX till 100, för att på det sättet maximera användandet av indexsidorna.
  • Ta bort alla index som inte används, annars tar de upp utrymme vilket belastar servern negativt.
  • Indexets innehåll bör vara minst 95% unikt.
  • Håll nere bredden (antalet kolumner) på dina index till så få som möjligt.
  • Försök att göra dina index unika. Detta eftersom SQL-Server oftast genomsöker ett unikt index snabbare än ett icke unikt index eftersom så snart den har hittar rätt så kan den avsluta sin sökning.
  • Om inga kolumner är tillräckligt unika för just din fråga, försök att bygga ett täckande index.
  • Frågor med flera ’or’ i sig kan skrivas om för att på det sättet utnyttja index bättre. Exempelvis select userid, username from users where username=’john’ or id=10 kan skrivas om till select userid, username from users where username=’john’ UNION ALL select userid, username from users where id = 10. Genom denna ändring kan du undvika att använda sammansatta index.

Kommentarer inaktiverade.

%d bloggare gillar detta: