Clustered
Index er et af de mest værdifulde ”værktøjer” vi har, når der skal optimeres på en SQL Server. Det kan være rigtigt svært at forstå i starten, og ikke mindst forstå de forskellige typer af index. I denne lille serie vil jeg tage fat på clustered, nonclustered og filtered index, og hvorfor det egentlig er så vigtigt, at vi har styr på dem. Denne artikel er helt basic og kan forhåbentlig give dig et indblik i det basale typer af index og hvorfor index vedligehold skal være en del af dit daglige arbejde på SQL Serveren, og ikke mindst hvorfor index er så værdifulde for en god SQL performance.
I denne første del tager jeg fat på clustered IndexSQL_Index. For helt at forstå index og hvorfor de er så vigtige, skal vi lige forbi hvordan data gemmes (igen dette er helt basic og forsimplet).
SQL serverens filesystem
Data gemmes på SQL Serverens filesystem på pages (8kb), hver page har en header på 96 bytes. I headeren er der oplysninger om bl.a. page nummer samt et page nummer, der peger på den næste page samt forrige page hvor data er:
Der findes mange forskellige page typer. Her snakker jeg udelukkende om hvordan data gemmes, og ikke de pagetypes, der bl.a. er med til at organisere datapages.
Når vi opretter en tabel uden at angive et clustered index, så kalder man tabellen en ”heap table”. Det betyder, at der ikke er en bestemt orden på data, og så alligevel; for når der indsættes data på pagen, så kommer det ind lige efter hinanden.
Heap table
Når der indsættes data i en heap table går det hurtigt, det ryger bare ind på den sidste page, hvor der er plads. Hvis der ikke er plads, allokeres der nye pages til tabellen og data indsættes. Men! Når vi vil slette, opdatere eller finde data igen, skal SQL Serveren have fat i de pages, hvor data er. Da en heap tabel ikke har data sorteret efter en given orden, så er SQL Serveren nødt til at kigge alle pages igennem for at være sikker på, at alle rækker er fundet, som der passer til en given forespørgsel. Dette gælder både for opdateringer og sletninger, da den skal have fat i de rækker, der skal behandles. Det betyder, at tabellen skal scannes hver eneste gang data skal bruges, og på tabeller med 100.000 rækker kan det altså godt blive lidt af en opgave.
SQL Serveren SCANNER altså alle pages igennem for at finde de data vi skal bruge:
Clustered Index
Efter noget tid hvor hverdagen bare kører derudaf, har vi lavet mange sletninger/opdateringer og indsat data i vores tabel, vi putter hele tiden på i ”enden” af vores sidste page. Dette betyder, at vi har ”huller” i vores pages hvor vi har slettet data, da en page fylder 8 kb. Om der er data på den eller ej, så kan det give god mening at køre en shrink på databasen, for at frigive ”ubrugt” plads. Dette vil tvinge SQL Serveren til at omorganisere alle pages – og det er her det for alvor bliver noget rod. For højst sandsynligt vil pages ikke ligge i rækkefølge mere, data vil være ”kastet” ind på pages i en bunke uden nogen orden. Det betyder bl.a. at når der skal læses direkte fra en pages så skal SQL Serveren ned og ”lede” forskellige steder på disken for at finde den page, hvor data er på.
Vi har mulighed for at hjælpe SQL Serveren med hurtigt at finde data ved at oprette et clustered index. Det vil sige, at vi bestemmer i hvilken rækkefølge data skal ligge på pages, altså hvilken kolonne eller kolonner skal data sorteres efter. Data ligger rent fysisk sorteret efter cluster key. Det vil sige, at hvis vi gerne vil kunne hente data efter eksempelvis fornavn og vi har sorteret tabellen efter fornavn kolonnen, så er det utrolig hurtigt for SQL Serveren at finde data, da den ved at data ligger sorteret efter den kolonne. Når Serveren når en ny værdi behøver den ikke lede længere, for der kommer ikke flere herefter, den søger (seek) altså efter data i stedet for at scanne (scan) det hele.
Det kan vi godt tænke os til, at det giver rigtig god mening. Dog skal vi være opmærksomme på at sorteringsordnen også betyder, at data indsættes i samme rækkefølge og kan derfor godt tvinge at pages bliver delt, når der indsættes data. Dette kaldes også fragmentering.
Jeg hører jævnligt, at det at have et clustered index på en tabel ikke giver mening, da der indsættes så meget data, at det giver et ”overhead” for SQL Serveren at finde det rigtige sted at indsætte data. Det er langt fra rigtigt, det kan være rigtigt i få tilfælde, men den gevinst man får ved hurtigt at kunne finde data, er så meget højere end det, det koster at indsætte data. Husk når data skal opdateres eller slettes, så skal de pågældende rækker også findes.
En SQL forespørgsel, der kører på en tabel uden clustered index (en heap tabel) med 100.000 rækker vil give en masse ekstra læsninger, tage længere tid da alt data skal læses, tage plads i bufferen for andre SQL forespørgsler og blokere for andre kørsler på den samme tabel. Så det giver i de fleste tilfælde rigtig god mening at have et clustered index på en tabel.
For at opsummere:
En tabel uden et clustered index kaldes en heap, og en tabel med et clustered index kaldes clustered table, da et clustered index også bestemmer den fysiske sortering af data på en page. Derfor kan der kun være et clustered index pr tabel.
Et clustered index oprettes ved at skrive CREATE CLUSTERED INDEX <name> ON <table> (<key> [,<key>]…)
det kan angives at det er unikt. Dette har sine fordele bl.a. den måde, som SQL henter data på. Dette gør at den kan benytte en hurtig måde at joine tabelerne på, når den ved det er sorteret og unikt.
Et clusterd index kan have en eller flere key columns. Det er den rækkefølge data rent fysisk bliver sorteret og gemt på en page, nede på file systemet. Angivelsen af flere kolonner som key columns gøres typisk for at skabe en unik nøgle.
Lad os kigge på en heap og et clustered table nede på selve pagen, så er det lettere at forstå hvordan sorteringen virker.
Her opretter jeg en tabel for at vise hvad der sker nede på en page:
CREATE TABLE dbo.tabel(
letter CHAR(4)
)
GOINSERT INTO dbo.tabel VALUES(REPLICATE('A',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('B',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('C',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('D',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('C',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('A',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('B',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('F',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('G',4))
INSERT INTO dbo.tabel VALUES(REPLICATE('B',4))
GO
Jeg kører denne dbcc for at få page nummeret ud:
DBCC IND('Demo',tabel,-1)
Herefter kan jeg bede SQL Serveren om at vise hvordan en page ser ud. Traceon(3604) er nødvendig da den fortæller at resultatet skal printes til message vinduet, ellers ville man ikke kunne se noget resultat.
DBCC TRACEON(3604)
DBCC PAGE('Demo',1,8560,1)Her er resultatet (viser kun udsnit af data sektionen på en “page”), i DATA: sektionen vises der …AAAA… ….BBBB… osv. bogstaverne kommer i den rækkefølge vi har indsat dem rent fysisk på pagen.
DATA:
Slot 0, Offset 0x60, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008E9B7FA060
0000000000000000: 10000800 41414141 010000 ....AAAA...
Slot 1, Offset 0x6b, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008E9B7FA06B
0000000000000000: 10000800 42424242 010000 ....BBBB...
Slot 2, Offset 0x76, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008E9B7FA076
0000000000000000: 10000800 43434343 010000 ....CCCC...
Slot 3, Offset 0x81, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008E9B7FA081
0000000000000000: 10000800 44444444 010000 ....DDDD...
Slot 4, Offset 0x8c, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008E9B7FA08C
0000000000000000: 10000800 43434343 010000 ....CCCC...
Slot 5, Offset 0x97, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008E9B7FA097
0000000000000000: 10000800 41414141 010000 ....AAAA...Du kan efterprøve det ved at køre denne SQL, som først indsætter K og derefter viser data på den sidste del af pagen:
INSERT INTO dbo.tabel VALUES(REPLICATE('K',4))
DBCC TRACEON(3604)
DBCC PAGE('Demo',1,8560,1)Her ses K som sidste værdi på pagen (viser kun et udsnit):
0000000000000000: 10000800 42424242 010000 ....BBBB...
Slot 10, Offset 0xce, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA17FA0CE
0000000000000000: 10000800 4b4b4b4b 010000 ....KKKK...For at vise hvordan data rent fysisk gemmes i den sortering, som vi definerer med cluster key når vi opretter et clustered index, laver vi et på tabellen:
CREATE CLUSTERED INDEX idx_cluster ON dbo.Tabel (Letter)Herefter skal vi have fat i page nummer:
DBCC IND('Demo',tabel,-1)Som giver os page nummer 16664
Nu kan vi kigge på den page med følgende syntax:
DBCC TRACEON(3604)
DBCC PAGE('Demo',1,16664,1)Bemærk at nu er bogstaverne sorteret rent fysisk på pagen.
Hvis du undrer dig over at …AAAA… er i bunden, så er det altså fordi jeg på min database har dansk coalition hvilket får SQL Serveren til at smide AA i bunden (å).
(Viser kun data sektionen på page)
DATA:
Slot 0, Offset 0x60, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA060
0000000000000000: 10000800 42424242 020000 ....BBBB...
Slot 1, Offset 0x6b, Length 19, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x0000008EA13FA06B
0000000000000000: 30000800 42424242 02000001 00130001 000000 0...BBBB...........
Slot 2, Offset 0x7e, Length 19, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x0000008EA13FA07E
0000000000000000: 30000800 42424242 02000001 00130002 000000 0...BBBB...........
Slot 3, Offset 0x91, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA091
0000000000000000: 10000800 43434343 020000 ....CCCC...
Slot 4, Offset 0x9c, Length 19, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x0000008EA13FA09C
0000000000000000: 30000800 43434343 02000001 00130001 000000 0...CCCC...........
Slot 5, Offset 0xaf, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA0AF
0000000000000000: 10000800 44444444 020000 ....DDDD...
Slot 6, Offset 0xba, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA0BA
0000000000000000: 10000800 46464646 020000 ....FFFF...
Slot 7, Offset 0xc5, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA0C5
0000000000000000: 10000800 47474747 020000 ....GGGG...
Slot 8, Offset 0xd0, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA0D0
0000000000000000: 10000800 4b4b4b4b 020000 ....KKKK...
Slot 9, Offset 0xdb, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 11
Memory Dump @0x0000008EA13FA0DB
0000000000000000: 10000800 41414141 020000 ....AAAA...
Slot 10, Offset 0xe6, Length 19, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x0000008EA13FA0E6
0000000000000000: 30000800 41414141 02000001 00130001 000000 0...AAAA...........Slutteligt vil jeg gennemgå et par typiske spørgsmål, som jeg har fået omkring clustered index:
Skal et clustered index være unikt?
Nej, det behøver det ikke, hvis den samme værdi indsættes flere gange, sætter SQL Serveren en 4 byte ”uniquefier” på ”behind the scenes”
Kan en cluster key godt være sammensat af flere kolonner?
Ja, det kan den sagtens. F.eks. hvis formålet er at skabe en unik nøgle, ved at kombinere flere kolonner.
Kan man have flere clustered indexes på en tabel?
Nej, det kan man ikke, da et clustered index fortæller om den fysiske sortering af data ned på disken.
Kan et clustered index godt nøjes med at indeholde en delmængde af data?
Nej, da et clustered index fortæller om den fysiske sortering af vores data, så indeholder det altid alle vores rækker og alle vores kolonner.
Index kan være utrolig komplekse og et forkert oprettet index kan være til gene. Hvis du er usikker på hvordan du skal designe dine index, så tag fat i en rutineret SQL kyndig. Vi hjælper selvfølgelig også gerne i Unit IT, for vi kan rigtig godt lide at lære fra os.
I den næste artikel vil jeg tage fat på nonclustered og filtered index.
