MySQL Indexes

  1. Inleiding
  2. Indexen in MySQL
  3. Hoe indexen werken
  4. Indexen op een juiste manier aanbrengen
  5. Slotwoord en referenties
  6. Reacties op deze tutorial

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:
Code
1
2
3
4
5
6
7
8
werknemers
------------
id
voornaam
achternaam
geboortedatum
salaris
opmerkingen

Als we nu de geboortedatum van de werknemer Jan de Vries met id 205 willen weten, kunnen we de volgende query gebruiken:
Code
1
2
3
SELECT geboortedatum
FROM werknemers
WHERE id = 205

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:
Code
1
2
3
EXPLAIN SELECT geboortedatum
FROM werknemers
WHERE id = 205

Deze query levert het volgende resultaat:
Code
1
2
3
4
5
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | werknemers | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

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:
Code
1
ALTER TABLE werknemers ADD PRIMARY KEY (id)

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

Het nogmaals uitvoeren van de EXPLAIN query, levert nu het volgende resultaat:
Code
1
2
3
4
5
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | werknemers | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+

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.

Vorige Volgende