Indexen op een juiste manier aanbrengen

Nu we weten hoe indexen in MySQL werken, kunnen we kijken hoe we indexen op een goede manier aan kunnen brengen. Laten we eens kijken naar een stappenplan voor het juist ontwerpen van indexen.

Begin met een tabel met daarin alle kolommen die je nodig hebt om je data op te slaan. Breng nog geen indexen aan behalve degene die je nodig hebt om te voorkomen dat je corrupte gegevens in je database kunt krijgen (primary key en unique indexen). Kijk vervolgens naar de queries die uitgevoerd worden op de database. Zijn het voornamelijk bepaalde queries die herhaaldelijk op de database uitgevoerd worden of zijn het verschillende queries die incidenteel uitgevoerd worden?

Bedenk voordat je verder gaat, hoe groot de tabel zal worden en hoe vaak hij gebruikt zal worden. Het is namelijk totaal niet efficient om een indexen aan te brengen op een hele kleine tabel. Mocht de tabel wel groot worden, dan moet je de optimalisatie daar proberen toe te passen, waar die het meeste nut heeft.

Als je bijvoorbeeld een query hebt die een minuut duurt, eens per dag uitgevoerd wordt en waarvan jij weet dat je hem terug kunt brengen naar een query van een seconde, heb je in totaal 59 seconden bespaart. Als een andere query, die elke minuut uitgevoerd wordt, zonder optimalisatie 1 seconde duurt en met optimalisatie een paar miliseconden, bepaar je met optimalisatie per dag zo'n 1400 seconden. In dit geval begin je dus met optimalisatie van de tweede query!

Terug naar het ontwerpen van de indexen. Als je veel verschillende queries hebt die incidenteel uitgevoerd worden, zul je indexen moeten aanmaken die algemeen bruikbaar zijn. Als we kijken naar de werknemerstabel zoals die op pagina 2 staat, zal er waarschijnlijk geen index nodig zijn op de 'opmerkingen' kolom. Er zullen immers weinig queries uitgevoerd worden met de 'opmerkingen' kolom in de WHERE clausule. Het is logischer dat je een opmerking wilt bekijken dan dat je erop wilt selecteren. Aan de andere kant is het vrij waarschijnlijk dat we werknemers aan de hand van de hoogte van hun salaris willen selecteren. Een index op de kolom 'salaris' lijkt dus een goede keuze.

De tweede mogelijkheid is dat je veel dezelfde queries hebt die vaak uitgevoerd worden en die snel moeten zijn. In dat geval zul je goed geoptimaliseerde indexen aan moeten brengen. Stel dat we de volgende twee queries hebben die meerdere malen per seconde uitgevoerd worden:

De optimalisatie van deze queries is afhankelijk van de grootte van de tabel en de gebruikte storage engine.

Als we gebruik maken van de InnoDB engine is de eerste query, zoals we eerder al gezien hebben, al geoptimaliseerd. De 'salaris' index zal gebruikt worden en er zal verder niet meer naar de tabel zelf gekeken worden.

In het geval van MyISAM of een andere storage engine moeten we kijken hoe groot de tabel is en hoe groot een index op (salaris, opmerkingen) zal zijn. Als je te maken hebt met een hele grote tabel, bijvoorbeeld een tabel met heel veel kolommen, zal de index (salaris, opmerkingen) waarschijnlijk sneller zijn dan het uitlezen van alle rijen die gekoppeld zijn aan een index op alleen salaris.

Het optimaliseren van de tweede query is iets lastiger. Het hangt hier voor zowel InnoDB als MyISAM echt af van de grootte van de tabel. Bij een zeer grote tabel met veel kolommen is het, zoals ik eerder al zei, waarschijnlijk sneller om een index aan te maken op (salaris, opmerkingen). De enige manier om hierachter te komen is door het testen van de verschillende situaties en de uitvoertijden te vergelijken.

De strategie die je in het algemeen kunt toepassen is de volgende:
Voor InnoDB: maak een index aan met daarin als eerste de kolommen in de WHERE clausule en vervolgens de kolommen die genoemd worden in de SELECT clausule, tenzij de kolom voorkomt in de primary key index.
Voor MyISAM: maak een index aan met daarin als eerste de kolommen in de WHERE clausule en vervolgens de kolommen die genoemd worden in de SELECT clausule.
Waar moet je op letten bij het aanmaken van indexen?
Wees je ervan bewust dat indexen alleen nut hebben in SELECT queries op grote tabellen. Heb je dus weinig SELECT queries op een tabel of is de tabel relatief klein, gebruik dan geen indexes. Dit levert dan juist het tegenovergestelde resultaat.

Zorg ervoor dat je nooit dubbele indexen aanmaakt. Als je bijvoorbeeld al een index (achternaam, voornaam) hebt is een index (achternaam) volledig overbodig aangezien de eerste index daar ook voor gebruikt kan worden.

Zorg voor de goede volgorde van kolommen in een index op meerdere kolommen. Denk aan left-most prefixing en dat je een index (A, B, C) dus niet kunt gebruiken voor kolommen B, C.

« Lees de omschrijving en reacties