Indexen in MySQL

Als je werkt met grote databases zul je vast wel eens tegen het probleem aangelopen zijn dat het uitvoeren van een query erg lang duurt. Dit kan bijvoorbeeld komen doordat MySQL duizenden records moet doorzoeken op zoek naar een bepaald gegeven of doordat MySQL alle records moet doorlopen om zo een minimale of maximale waarde te bepalen. Het gebruik van een index kan in zo'n geval een flinke snelheidswinst opleveren.

Een index in MySQL werkt eigenlijk precies hetzelfde als een index achterin een boek. Het wordt gebruikt om snel te weten te komen waar bepaalde gegevens staan. Stel dat we te maken hebben met een tabel met gegevens over werknemers die er als volgt uitziet:

Als we nu de geboortedatum van de werknemer Jan de Vries met id 205 willen weten, kunnen we de volgende query gebruiken:

MySQL heeft nu geen idee waar dit record zich in de tabel bevindt. MySQL weet zelfs niet of er wel zo'n record bestaat of dat er meerdere records bestaan die aan deze query voldoen. In dit geval zullen dus alle records uit de hele tabel doorlopen moeten worden op zoek naar Jan's gegevens.

Een index op de kolom id zou het resultaat van deze query aanzienlijk kunnen versnellen. Maar voordat ik uitleg hoe je een index aan kunt brengen op een kolom, kijken we eerst eens nader naar de query die we gebruiken.

Gebruik van EXPLAIN
Met EXPLAIN kun je zien hoe queries uitgevoerd worden. Door EXPLAIN voor een SELECT query te zetten kun je zien of je indexen wel juist gebruikt worden of hoe een JOIN uitgevoerd wordt.

Laten we eens kijken wat EXPLAIN over onze query te zeggen heeft:

Deze query levert het volgende resultaat:

Ik zal beginnen met uitleggen van de verschillende onderdelen die we in dit resultaat zien:
id - Geeft aan met welke SELECT we te maken hebben. Er kunnen meerdere SELECT's in een query staan
select_type - Geeft aan wat voor type SELECT het is. Zie de MySQL handleiding voor een uitleg
table - De tabel waarop de query uitgevoerd is
type - Geeft aan hoe er in de tabel gezocht is. Zie de MySQL handleiding voor een uitleg
possible_keys - Dit geeft aan uit welke indexen MySQL kan kiezen om rijen in de tabel te vinden
key - Geeft de gebruikte index om in de tabel te zoeken
key_len - Geeft de lengte van de waarde van de index aan
ref - Geeft aan welke constanten of kollommen vergelijken zijn met de gebruikte index
rows - Geeft aan matchende hoeveel rijen MySQL denkt te vinden in de tabel
Extra - Geeft meer informatie over hoe MySQL de query uitvoert

Het lijkt erop dat de query die wij gebruikt hebben een van de slechtste is die we maar kunnen hebben. Het type geeft aan dat er door de hele tabel gezocht wordt, possible_keys vertelt dat er geen enkele index beschikbaar is en rows geeft in dit geval alle rijen in de tabel aan (hier zijn het er 4 omdat ik maar 4 records heb staan in de tabel die ik als voorbeeld gebruik).

Later in deze tutorial, als we een index aangebracht hebben, zal ik wederom het resultaat van EXPLAIN laten zien. Die zal heel anders zijn.

Waar MySQL indexen voor gebruikt
MySQL zal in de volgende gevallen gebruik maken van een index als die beschikbaar is:
Voor het snel vinden van rijen die voldoen aan een WHERE clausule
Elimineren van rijen die er niet toe doen door bij meerdere mogelijk indexen degene met de minste rijen te gebruiken
Om rijen uit andere tabellen te halen wanneer een JOIN uitgevoerd wordt
Om minimale en maximale waarden uit een kolom met index te vinden
Voor het sorteren van gevonden rijen
In al deze gevallen is het van belang om de index goed aan te brengen. Hoe je dat doet, zal ik later in deze tutorial bespreken.

Index op een kolom aanbrengen
Zoals ik in de inleiding al suggereerde, zijn er verschillende indexen die je kunt gebruiken. De bekendste daarvan zijn de Primary Key, Unique Index, Index en Fulltext Index.

De Index is van deze vier de normale index. Hier zitten verder geen eisen aan vast. Anders is dat bij een Unique Index waarbij waarden in de kolom waarop deze index aangemaakt is, allemaal uniek moeten zijn. Een Primary Key index stelt dezelfde eisen, met als extra voorwaarde dat er maar 1 primary key per tabel kan zijn. De fulltext index zal ik in deze tutorial niet behandelen aangezien dit een onderwerp op zich is en te groot is om in deze tutorial op te nemen. (Meer informatie over de fulltext functie van MySQL is te vinden in de handleiding)

Stel nu dat we een Primary Key index aan willen brengen op de kolom 'id', dat kunnen we doen met de volgende query:

Ook kun je dit doen in PHPMyAdmin, door in het overzicht van de tabel op het knopje achter de kolomnaam te klikken.

Het nogmaals uitvoeren van de EXPLAIN query, levert nu het volgende resultaat:

We zien nu dat dit resultaat al sterk verbeterd is. We zien dat het type niet meer ALL aangeeft wat betekent dat er niet meer door alle rijen gezocht is. Ook zien we dat er een index gebruikt is, namelijk de Primary Key. Tenslotte zien we dat MySQL nu verwacht dat er maar 1 rij gevonden zal worden.

« Lees de omschrijving en reacties