Hoe indexen werken

Voordat we indexen op een juiste manier kunnen gebruiken is het van belang dat we weten hoe indexen in MySQL werken.

Allereerst wil ik aangeven dat het gebruik van indexen alleen nut heeft bij SELECT queries. Sterker nog, ze zorgen ervoor dat INSERT of UPDATE queries iets langer duren aangezien naast het invoegen of aanpassen van een record ook de index aangepast moet worden. Maar tenzij je systeem meer INSERT en UPDATE queries verwerkt dan SELECT queries en de snelheid van je INSERT en UPDATE queries echt van belang is, is het gebruik van indexen altijd aan te raden.

Het verschil tussen InnoDB en MyISAM
De manier waarom InnoDB omgaat met indexen is net iets anders dan bij andere storage engines zoals MyISAM.

InnoDB maakt uit zichzelf een geclusterde index aan voor elke tabel. Als een tabel een Primary Key heeft, wordt die gebruikt als geclusterde index. Zo niet, dan zal de InnoDB engine aan elke rij een uniek ID toekennen en dat als geclusterde index gebruiken. Het is dan ook verstandig om, bij het gebruik van de InnoDB storage engine, elke tabel een Primary Key te geven.

De 'bladwijzers' in de primary key index bevatten de werkelijke data van de kolom waarop de primary key is aangemaakt. De bladwijzers van alle andere indexen (secondary indexen) bevatten naast de gegevens uit de kolom waarop een index aangemaakt is, ook een verwijzing naar de overeenkomende bladwijzer uit de primary key index. Bij het gebruik van zo'n index heb je dus naast de waarde van de index zelf, ook beschikking over de bijbehorende waarde uit de primary key index. Dit klinkt nu misschien nog heel vaag maar dit zal straks allemaal duidelijker worden.

De MyISAM storage engine maakt geen gebruik van geclusterde indexen. De bladwijzers van zowel de primary key index als de andere indexen verwijzen direct naar de rijen uit de tabel. Er is dan ook geen wezenlijk verschil tussen primary key indexen en andere indexen. De primary key index is dan ook niets meer dan een simpele UNIQUE index met de naam PRIMARY.

Het gebruiken van indexen met de InnoDB engine
Aan het gebruik van indexen in combinatie met de InnoDB engine kleven zowel voordelen als nadelen.

Allereerst zit je natuurlijk met het feit dat de secondary indexen niet direct verwijzen naar rijen uit de tabel. Ze bevatten alleen een verwijzing naar de primary key index. Het gevolg hiervan is dat als er een secondary index gebruikt wordt, eerst de overeenkomende waarde uit de primary key index gezocht moet worden om vervolgens de rij uit de tabel uit te lezen.

Dit nadeel kan ook direct een voordeel zijn. Als een query gebruik maakt van een secondary index, weet het direct twee dingen: de waarden die gebruikt zijn om de index te doorzoeken en de primary key waarden van die rij uit de tabel.

Stel bijvoorbeeld dat we deze query gebruiken:

Op dit moment bevat onze tabel alleen een primary key index op de kolom 'id'. De EXPLAIN van deze query ziet er dus ook niet al te fraai uit:

Om deze query nu te optimaliseren, kunnen we een index aanbrengen op de kolom 'achternaam'. Dit is namelijk de kolom waarin MySQL een bepaalde waarde zal zoeken.

Met deze query breng je een index genaamd 'achternaam' aan op de kolom 'achternaam'. De EXPLAIN van de SELECT query ziet er nu als volgt uit:

We zien in de 'Extra' kolom aan de melding 'Using index' dat er daadwerkelijk een index gebruikt wordt. Maar wat is er nu eigenlijk precies gebeurt?

De query heeft de waarde 'de Vries' gebruikt om de index op de kolom 'achternaam' te doorzoeken. Als de query bij de betreffende bladwijzer aangekomen is, vindt het de bijbehorende primary key waarde. Deze waarde kan weer gebruikt worden om de primary key index te doorzoeken naar de betreffende rij uit de tabel.

In dit geval echter is dat helemaal niet nodig. Wij zijn op zoek naar het id, en dat is de primary key waarde uit de tabel. In dit geval gebruikt de query dus alleen maar waarden uit de secondary index op de 'achternaam' kolom.

Deze optimalisatie is eigenlijk heel handig. Het betekent namelijk dat elke secondary index beschouwd kan worden als een aparte tabel met daarin de waarden uit de index en die primary key waarden. Deze optimalisatie wordt ook wel don't leave the index genoemd.

Operators in de WHERE clausule
Het lijkt nu misschien alsof we een index aan zouden maken voor de kolommen die genoemd worden in een WHERE clausule, maar dit is niet helemaal waar. MySQL zal alleen gebruik maken van indexen voor de WHERE clausule als een van de volgende operators gebruikt worden:
<
<=
=
>
>=
BETWEEN
IN
LIKE
Voor de LIKE operator wordt alleen een index gebruikt als het eerste teken geen wildcard (% of _) is. In het volgende geval zal er, indien beschikbaar, een index gebruikt worden:

Maar in dit geval zal er nooit een index gebruikt worden:

Ten slotte zal er ook een index gebruikt worden als er IS NULL in de WHERE clausule gebruikt wordt.

Index op meerdere kolommen
MySQL biedt je ook de mogelijkheid om een index op meerdere kolommen aan te brengen. Waarom dit handig is, zal ik aantonen met het volgende voorbeeld.

Laten we uitgaan van de situatie met een primary key index en een index op de kolom 'achternaam'. Vervolgens bekijken we de volgende query:

Als we kijken naar de EXPLAIN van deze query ziet dat er als volgt uit:

We zien bijna hetzelfde resultaat als eerder, met als uitzondering dat 'Using index' in de 'Extra' kolom verdwenen is. Dit moet je niet interpreteren alsof de index helemaal niet gebruikt wordt, maar alsof de index slechts gedeeltelijk gebruikt wordt.

De query gebruikt nu de waarde 'de Vries' om wederom te zoeken in de achternaam index. Via de bladwijzer waarop 'de Vries' staat, wordt de waarde van de primary key achterhaald. Vervolgens wordt de primary key index gebruikt om de bijbehorende rij uit de tabel te bepalen en daaruit de voornaam te selecteren.

Je merkt al dat dit dus eigenlijk dubbel werk is. De oplossing hiervoor is het aanbrengen van een index op meerdere kolommen:

De EXPLAIN van de select query ziet er nu als volgt uit:

Hieruit blijkt dat de index die we zojuist aangemaakt hebben gebruikt wordt, en dat enkel die index gebruikt wordt. De waarde van de voornaam wordt nu dus direct uit de achternaam_voornaam index gelezen en de tabel zelf hoeft hier verder niet voor benaderd te worden.

Left-most prefixing
MySQL maakt gebruikt van het zogenaamde left-most prefixing. Dat houdt in dat een index op kolommen A, B, C (in deze volgorde) ook gebruikt kan worden voor zowel queries op kolommen A, B als alleen kolom A.

In bovenstaand voorbeeld is de index 'achternaam' dus ook overbodig geworden aangezien we een nieuwe index 'achternaam_voornaam' op kolommen 'achternaam, voornaam' aangemaakt hebben. Deze index is dus ook te gebruiken voor enkel de kolom achternaam.

In deze query zal de index achternaam_voornaam bijvoorbeeld niet gebruikt worden:

Mochten we deze query willen optimaliseren, dan zouden we een index op moeten nemen op in ieder geval de voornaam en voor volledige optimalisatie op zowel de kolommen voornaam als salaris.

Bewerkingen op kolommen met index
Dit is een belangrijk punt dat vaak fout gaat. Je moet te allen tijde voorkomen dat je een bewerking uitvoert op een kolom waarop je een index gemaakt hebt. Doe je dat wel, dan zal de index niet meer gebruikt wordt.

Stel bijvoorbeeld dat we willen weten van welke werknemers het maandelijkse salaris boven de 1500 euro ligt. We hebben daartoe een index aangemaakt op de kolom 'salaris', waarin het jaarsalaris staat, en gebruiken de volgende query:

Het resultaat van de EXPLAIN van deze query ziet er als volgt uit:

Niet erg positief dus. We zien dat de aangemaakte index op de kolom 'salaris' niet eens gebruikt wordt.

Dat de index niet gebruikt wordt heeft te maken met het feit dat we een bewerking op de salaris kolom uitvoeren. De index kan dus niet eens gebruikt worden omdat MySQL voor elke rij deze berekening uit moet voeren en dus alsnog de hele tabel moet doorlopen.

Dit is op te lossen door de query iets anders te schrijven:

De EXPLAIN van deze query ziet er wel goed uit:

MySQL is nu namelijk in staat om de berekening 1500 * 12 eenmalig uit te voeren en vervolgens deze constante op te zoeken in de salaris index.

Indexen en JOINS
Indexen kunnen ook gebruikt worden om queries met een JOIN te optimaliseren. Voor dit voorbeeld breiden we de eerder gebruikte gegevens uit met een tabel voor werkgevers:

Stel dat we willen weten welke werknemers bij de rabobank werken. Dat zouden we met de volgende query kunnen bepalen:

De EXPLAIN van deze query laat zien dat er totaal geen indexen gebruikt worden:

Om nu de juiste indexen aan te brengen zullen we moeten kijken welke gegevens bekend zijn bij MySQL en welke gegevens MySQL op moet zoeken.

We zien in de query dat de JOIN tussen de werkgevers en werknemers tabel plaatsvindt op werkgevers.id = werknemers.werkgever. De waarde van werkgevers.id is bekend en wordt gebruikt om de waarde van werknemers.werkgever te bepalen. Om dit proces te optimaliseren is het dus makkelijk als MySQL de beschikking heeft over een index op werknemers.werkgever.

Na aanbrengen van die index ziet de EXPLAIN er als volgt uit:

We zien dat deze index nu gebruikt wordt. Tevens zien we dat bij ref de kolom aangegeven wordt waar deze waarde naar refereert.

De huidige EXPLAIN laat ons zien dat de situatie voor de werkgevers tabel nog niet helemaal optimaal is. Dit komt door de WHERE clausule die we in de query hebben staan. Dit probleem is eenvoudig op te lossen door een index aan te brengen op de werkgever kolom uit de werkgevers tabel.

Via werkgevers.werkgever willen we namelijk werkgevers.id te weten komen om deze waarde vervolgens op te zoeken in de werknemers.werkgever index en zo uiteindelijk achter de voornaam te komen. EXPLAIN levert dan uiteindelijk:

Zoals we zien is hebben we met deze indexen een betere situatie dan voorheen.

« Lees de omschrijving en reacties