SQL: toepassen indexen
Als je in je database gegevens wilt opzoeken, dan maakt SQL Server gebruik van indexen om deze data op te zoeken. Een index kan je vergelijken met een Gouden Gids waarin de adressen van personen staan, zodat je niet alle huizen langs hoeft om de gegevens van iemand te vinden. In een normale databaseomgeving staat er altijd wel minstens Ă©Ă©n index actief op een tabel, maar dat hoeft niet en die ene kan ontoereikend zijn voor de meeste use cases.
Laten we het voorbeeld van de Gouden Gids eens uitbreiden. Stel je voor we hebben een database met daarin gegevens van 2000 personen (hierin kunnen dubbele namen zitten). Voor dit voorbeeld heb ik 2000 fictieve namen en beroepen gegenereerd en deze in de tabel Personen geĂŻmporteerd:
Als we nu zoeken naar alle personen en ik wil alle gegevens zien, dan kan ik bijvoorbeeld doen:
SELECT * FROM Personen;
Om te zien hoe SQL Server deze Query interpreteert, klik je op âShow Actual Execution Planâ en voer je de query nogmaals uit.
Je ziet dat er Table Scan staat. In het Gouden Gids-voorbeeld komt het erop neer dat ik mijn collega van de afdeling Gegevens vraag alle gegevens van personen die wij hebben, mij te geven. Deze collega loopt naar de archiefkast met daarin persoonsgegevens en geeft mij alle informatie die erin staat.
Als ik mijn collega daarna vraag om alle gegevens van mensen die als beroep programmeur hebben, terug te geven, loopt de collega weer naar de archiefkast, controleert alle papieren of deze bij een programmeur hebben en zo ja, geeft die informatie aan mij terug:
SELECT * FROM Personen WHERE Beroep = 'Programmer';
Dit kan verbeterd worden. Allereerst door een CLUSTERED INDEX aan te maken via een PRIMARY KEY constraint. Een primary key mag elk uniek kenmerk zijn of unieke combinaties van kenmerken, maar gebruikelijk is een oplopende integer:
ALTER TABLE Persons ADD COLUMN PersonId int IDENTITY(1,1) NOT NULL CONSTRAINT Pk_Persons PRIMARY KEY.
Door het aanmaken van een primary key is er meteen ook een clustered index gemaakt (waarvan er maar Ă©Ă©n per tabel kan zijn). Deze clustered index is als het ware de Gouden Gids en wanneer ik mijn collega nu vraag om alle gegevens, dan hoeft zij alleen maar de Gouden Gids erbij te pakken.
Dit hoeft niet per se direct een voordeel in performance op te leveren. De primary key is vergelijkbaar met een adres. Adressen, die gesorteerd, bij wijze van spreken van 1 tot en met 2000, opgeschreven zijn, zodat ik meteen de juiste rij vindt als ik een adres heb.
Om de impact van je queries te zien, kan je statistieken aanzetten:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
Als ik vraag om alle gegevens van programmeurs, dan moet alsnog de hele gids (index) doorzocht worden, of ik nou een primary key heb toegevoegd. Dat zie je aan de statistieken, als ik zoek naar alle personen, doorzoekt de server 13 âpagesâ en als ik zoek naar alleen programmeurs ook:
SELECT * FROM Personen;
(2000 row(s) affected)
Table 'Personen'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT * FROM Personen WHERE Beroep = âProgrammerâ;
(46 row(s) affected)
Table 'Personen'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Als ik vaak naar beroepen zoek, dan is het zinvol om een NONCLUSTERED INDEX aan te maken. In dat geval maak ik een tweede gids aan, met daarin de kolommen waarop ik wil zoeken, beroep bijvoorbeeld, in een gesorteerde volgorde. Achter de kolom met beroep staat dan een kolom met het adres, de primary key. Zodra ik alle programmeurs zoek, dan kan ik A tot en met O overslaan, pak ik de eerste rij programmeur, schrijf het adres op en doe dat voor de volgende regels, net zolang ik geen programmeur meer tegenkom. Nu heb ik een aantal adressen en hoef in de grote gids waarin alle gegevens staan alleen maar te zoeken naar die adressen, die snel gevonden zijn omdat deze gesorteerd zijn ingevoerd.
CREATE NONCLUSTERED INDEX IX_Personen ON Personen(Beroep);
Als ik alleen voornamen en achternamen zou willen weten van de programmeurs, dan zou het handig zijn om die meteen beschikbaar te hebben in de nieuwe, aparte gids. In het voorbeeld wordt gebruik gemaakt van eenvoudige tabellen en data en bepaalt de server dat de clustered index voldoende is om de gegevens op te halen. In andere gevallen moet ik weer terug naar de Gouden Gids en per regel voor- en achternaam opzoeken. Dit kan ik doen door de kolommen Voornaam en Achternaam toe te voegen aan de gids, of:
CREATE NONCLUSTERED INDEX IX_Personen_Beroep ON Personen(Beroep) INCLUDE (Voornaam, Achternaam);
Voor het voorbeeld heb ik er nu 512000 regels van gemaakt en extra kolommen toegevoegd. Als ik nu zoek naar alle programmeurs, zonder extra index, dan krijg ik het volgende:
SELECT Voornaam, Achternaam From Personen WHERE Beroep = 'Programmer'
(11776 row(s) affected)
Table 'Personen'. Scan count 5, logical reads 15056, physical reads 3, read-ahead reads 13152, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Voeg ik de index toe, dan is het:
(11776 row(s) affected)
Table 'Personen'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Dat betekent dat er 15000 âpagesâ in het geheugen minder worden gelezen, dus een betere response en die pages kunnen wel weer gelezen worden door andere processen.
Bij het aanleggen van indexen dient wel gerealiseerd te worden dat het gepaard gaat met een aantal nadelen:
- Een index wordt geupdate zodra de geĂŻndexeerde gegevens in de tabel worden bijgewerkt. Dit is extra overhead. Als ik een aanpassing doe in de Gouden Gids, dan moet ik dat ook doen in de gids die ik gebruik om naar beroepen te zoeken.
- De index moet ergens worden opgeslagen, dat betekent dat er dus extra schijfruimte en geheugen zal worden gebruikt. De gids met beroepen is een extra boek dat ik ergens dien te bewaren, en als ik extra kolommen toevoeg (zij het als composite index, zij het het als included column), dan moet het boek iets breder worden.
Over het algemeen weegt bovenstaande niet op tegen de verbeterde performance, maar het is iets om in het achterhoofd te houden. Daarnaast is een index niet het enige antwoord en kunnen er nog steeds problemen zijn als queries niet optimaal zijn opgesteld of als er onnodig veel queries worden uitgevoerd terwijl er ook data gecached kan worden in de applicatielaag. Desalniettemin, door enkele vaak gebruikte queries te analyseren en de juiste indexen te plaatsen, kan je met minimale moeite ervoor zorgen dat de performance van je website of webshop verbetert.
