Tutorials
MySQL Indexes
Database optimalisatie met behulp van indexen in MySQL
Pagina 1
Inleiding
Het gebruik van indexen in een database is iets waar nog lang niet altijd bij stil gestaan wordt. Vaak wordt er onbewust al een index gebruikt, zoals de primary key, maar vooral bij grote databases is het verstandig om bij dit onderwerp stil te staan. Deze indexen kunnen namelijk, mits goed ingesteld, zorgen voor een grote snelheidswinst bij het uitvoeren van select queries.
In deze tutorial zal ik ingaan op het gebruik van indexes in een MySQL database. Aan de hand van voorbeelden zal ik proberen uit te leggen wat hier het nut van is en hoe je indexes op een goede manier kunt toepassen.
Benodigde voorkennis
[li]Basiskennis SQL[/li]
In deze tutorial zal ik ingaan op het gebruik van indexes in een MySQL database. Aan de hand van voorbeelden zal ik proberen uit te leggen wat hier het nut van is en hoe je indexes op een goede manier kunt toepassen.
Benodigde voorkennis
[li]Basiskennis SQL[/li]
Pagina 2
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:
[li]id - Geeft aan met welke SELECT we te maken hebben. Er kunnen meerdere SELECT's in een query staan[/li]
[li]select_type - Geeft aan wat voor type SELECT het is. Zie de MySQL handleiding voor een uitleg[/li]
[li]table - De tabel waarop de query uitgevoerd is[/li]
[li]type - Geeft aan hoe er in de tabel gezocht is. Zie de MySQL handleiding voor een uitleg[/li]
[li]possible_keys - Dit geeft aan uit welke indexen MySQL kan kiezen om rijen in de tabel te vinden[/li]
[li]key - Geeft de gebruikte index om in de tabel te zoeken[/li]
[li]key_len - Geeft de lengte van de waarde van de index aan[/li]
[li]ref - Geeft aan welke constanten of kollommen vergelijken zijn met de gebruikte index[/li]
[li]rows - Geeft aan matchende hoeveel rijen MySQL denkt te vinden in de tabel[/li]
[li]Extra - Geeft meer informatie over hoe MySQL de query uitvoert[/li]
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:
[li]Voor het snel vinden van rijen die voldoen aan een WHERE clausule[/li]
[li]Elimineren van rijen die er niet toe doen door bij meerdere mogelijk indexen degene met de minste rijen te gebruiken[/li]
[li]Om rijen uit andere tabellen te halen wanneer een JOIN uitgevoerd wordt[/li]
[li]Om minimale en maximale waarden uit een kolom met index te vinden[/li]
[li]Voor het sorteren van gevonden rijen[/li]
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 [img=Primary Key]http://phphulp.jorendewit.nl/images/primary_key.png[/img] 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.
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:
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:
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:
EXPLAIN SELECT geboortedatum
FROM werknemers
WHERE id = 205
Deze query levert het volgende resultaat:
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 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:
[li]id - Geeft aan met welke SELECT we te maken hebben. Er kunnen meerdere SELECT's in een query staan[/li]
[li]select_type - Geeft aan wat voor type SELECT het is. Zie de MySQL handleiding voor een uitleg[/li]
[li]table - De tabel waarop de query uitgevoerd is[/li]
[li]type - Geeft aan hoe er in de tabel gezocht is. Zie de MySQL handleiding voor een uitleg[/li]
[li]possible_keys - Dit geeft aan uit welke indexen MySQL kan kiezen om rijen in de tabel te vinden[/li]
[li]key - Geeft de gebruikte index om in de tabel te zoeken[/li]
[li]key_len - Geeft de lengte van de waarde van de index aan[/li]
[li]ref - Geeft aan welke constanten of kollommen vergelijken zijn met de gebruikte index[/li]
[li]rows - Geeft aan matchende hoeveel rijen MySQL denkt te vinden in de tabel[/li]
[li]Extra - Geeft meer informatie over hoe MySQL de query uitvoert[/li]
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:
[li]Voor het snel vinden van rijen die voldoen aan een WHERE clausule[/li]
[li]Elimineren van rijen die er niet toe doen door bij meerdere mogelijk indexen degene met de minste rijen te gebruiken[/li]
[li]Om rijen uit andere tabellen te halen wanneer een JOIN uitgevoerd wordt[/li]
[li]Om minimale en maximale waarden uit een kolom met index te vinden[/li]
[li]Voor het sorteren van gevonden rijen[/li]
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:
ALTER TABLE werknemers ADD PRIMARY KEY (id)
Ook kun je dit doen in PHPMyAdmin, door in het overzicht van de tabel op het [img=Primary Key]http://phphulp.jorendewit.nl/images/primary_key.png[/img] knopje achter de kolomnaam te klikken.
Het nogmaals uitvoeren van de EXPLAIN query, levert nu het volgende resultaat:
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 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.
Pagina 3
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:
[li]<[/li]
[li]<=[/li]
[li]=[/li]
[li]>[/li]
[li]>=[/li]
[li]BETWEEN[/li]
[li]IN[/li]
[li]LIKE[/li]
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.
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:
SELECT id FROM werknemers WHERE achternaam = 'de Vries'
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:
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | werknemers | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
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.
ALTER TABLE werknemers ADD INDEX achternaam (achternaam)
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:
+----+-------------+------------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | werknemers | ref | achternaam | achternaam | 102 | const | 2 | Using where; Using index |
+----+-------------+------------+------+---------------+------------+---------+-------+------+--------------------------+
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:
[li]<[/li]
[li]<=[/li]
[li]=[/li]
[li]>[/li]
[li]>=[/li]
[li]BETWEEN[/li]
[li]IN[/li]
[li]LIKE[/li]
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:
SELECT * FROM werknemers WHERE voornaam LIKE 'Kare%'
Maar in dit geval zal er nooit een index gebruikt worden:
SELECT * FROM werknemers WHERE voornaam LIKE '%arel'
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:
SELECT voornaam FROM werknemers WHERE achternaam = 'de Vries'
Als we kijken naar de EXPLAIN van deze query ziet dat er als volgt uit:
+----+-------------+------------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | werknemers | ref | achternaam | achternaam | 102 | const | 2 | Using where |
+----+-------------+------------+------+---------------+------------+---------+-------+------+-------------+
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:
ALTER TABLE werknemers ADD INDEX achternaam_voornaam (achternaam, voornaam)
De EXPLAIN van de select query ziet er nu als volgt uit:
+----+-------------+------------+------+--------------------------------+---------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+--------------------------------+---------------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | werknemers | ref | achternaam,achternaam_voornaam | achternaam_voornaam | 102 | const | 2 | Using where; Using index |
+----+-------------+------------+------+--------------------------------+---------------------+---------+-------+------+--------------------------+
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:
SELECT salaris FROM werknemers WHERE voornaam = 'Jan'
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:
SELECT voornaam, achternaam FROM werknemers WHERE salaris / 12 > 1500
Het resultaat van de EXPLAIN van deze query ziet er als volgt uit:
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | werknemers | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
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:
SELECT voornaam FROM werknemers WHERE salaris > 1500 * 12
De EXPLAIN van deze query ziet er wel goed uit:
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | werknemers | range | salaris | salaris | 4 | NULL | 2 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
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:
werknemers
------------
id
voornaam
achternaam
geboortedatum
salaris
opmerkingen
werkgever
werkgevers
--------------
id
werkgever
Stel dat we willen weten welke werknemers bij de rabobank werken. Dat zouden we met de volgende query kunnen bepalen:
SELECT
id
FROM
werknemers
INNER JOIN
werkgevers ON werkgevers.id = werknemers.werkgever
WHERE
werkgevers.werkgever = 'Rabobank'
De EXPLAIN van deze query laat zien dat er totaal geen indexen gebruikt worden:
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | werkgevers | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 1 | SIMPLE | werknemers | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
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:
+----+-------------+------------+------+---------------+-----------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-----------+---------+--------------------+------+-------------+
| 1 | SIMPLE | werkgevers | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 1 | SIMPLE | werknemers | ref | werkgever | werkgever | 4 | test.werkgevers.id | 2 | |
+----+-------------+------------+------+---------------+-----------+---------+--------------------+------+-------------+
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:
+----+-------------+------------+------+---------------+-----------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-----------+---------+--------------------+------+-------------+
| 1 | SIMPLE | werkgevers | ref | werkgever | werkgever | 102 | const | 1 | Using where |
| 1 | SIMPLE | werknemers | ref | werkgever | werkgever | 4 | test.werkgevers.id | 2 | Using index |
+----+-------------+------------+------+---------------+-----------+---------+--------------------+------+-------------+
Zoals we zien is hebben we met deze indexen een betere situatie dan voorheen.
Pagina 4
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:
[li]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.[/li]
[li]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.[/li]
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.
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:
SELECT id FROM werknemers WHERE salaris = ?;
SELECT opmerkingen FROM werknemers WHERE salaris = ?;
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:
[li]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.[/li]
[li]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.[/li]
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.
Pagina 5
Slotwoord en referenties
Tot zover deze tutorial over het gebruik van indexen in MySQL. Ik heb hier geprobeerd om te verduidelijken hoe indexen op een juiste manier in een database gebruikt kunnen worden. Tevens hoop ik dat duidelijk is geworden dat het gebruik van indexen een van de beste manieren is voor het versnellen van queries op een database.
Hoe meer je over indexen weet, hoe meer je je database kunt optimaliseren. Gebruik bij het aanbrengen van indexen de EXPLAIN query veelvuldig om te voorkomen dat je nutteloze indexen aanbrengt. Een goed aangebrachte index kan de performance van je database zeker doen verbeteren, maar het tegenovergestelde is helaas ook waar. Let daar dus goed op.
In deze tutorial ben ik ingegaan op de verschillen tussen de InnoDB en MyISAM storage engines. Doordat de manier van behandelen van indexen net iets anders is, vereisen deze engines ook een aparte aanpak voor het aanbrengen van indexen.
Een onderdeel dat ik niet behandeld heb is het gebruik van een fulltext index in de MyISAM storage engine. Dit is een onderwerp op zich en is te uitgebreid om op te nemen in deze tutorial. Voor meer informatie over de fulltext functie van MySQL verwijs ik naar de MySQL handleiding.
Tot slot wil ik nog wijzen op de MySQL Index Analyzer, een klein programmatje dat alle indexen in een database analyseert. Ik heb er zelf nog niet veel mee gewerkt, maar wat ik er van gezien heb is redelijk positief. Try it yourself!
Opermkingen en suggesties over deze tutorial zijn natuurlijk altijd welkom!
Deze tutorial is ook hier te vinden.
Bronvermelding
[li]MySQL Handleiding[/li]
[li]How to exploit MySQL index optimizations[/li]
[li]Optimizing MySQL: Queries and Indexes[/li]
Hoe meer je over indexen weet, hoe meer je je database kunt optimaliseren. Gebruik bij het aanbrengen van indexen de EXPLAIN query veelvuldig om te voorkomen dat je nutteloze indexen aanbrengt. Een goed aangebrachte index kan de performance van je database zeker doen verbeteren, maar het tegenovergestelde is helaas ook waar. Let daar dus goed op.
In deze tutorial ben ik ingegaan op de verschillen tussen de InnoDB en MyISAM storage engines. Doordat de manier van behandelen van indexen net iets anders is, vereisen deze engines ook een aparte aanpak voor het aanbrengen van indexen.
Een onderdeel dat ik niet behandeld heb is het gebruik van een fulltext index in de MyISAM storage engine. Dit is een onderwerp op zich en is te uitgebreid om op te nemen in deze tutorial. Voor meer informatie over de fulltext functie van MySQL verwijs ik naar de MySQL handleiding.
Tot slot wil ik nog wijzen op de MySQL Index Analyzer, een klein programmatje dat alle indexen in een database analyseert. Ik heb er zelf nog niet veel mee gewerkt, maar wat ik er van gezien heb is redelijk positief. Try it yourself!
Opermkingen en suggesties over deze tutorial zijn natuurlijk altijd welkom!
Deze tutorial is ook hier te vinden.
Bronvermelding
[li]MySQL Handleiding[/li]
[li]How to exploit MySQL index optimizations[/li]
[li]Optimizing MySQL: Queries and Indexes[/li]
Reacties
0