Wat is een index?

Je kent het principe van een index waarschijnlijk uit alledaagse situaties.

• In studieboeken staat vaak achteraan een alfabetisch geordende woordenlijst met paginanummers. Hiermee raak je veel sneller aan de gewenste informatie dan wanneer je een boek woord per woord leest vanaf het begin.

• In het Engels wordt het woord "index" soms gebruikt om een telefoongids aan te duiden. Hiermee kan je heel snel iemand terugvinden als je de juiste plaatsnaam kent en een stukje informatie over de gezochte persoon kent (vaak een achternaam of beroep).

Indexen kunnen ook gebruikt worden om heel snel een databasetabel te doorlopen. Net zoals je zelf tijd verliest door altijd vanaf de eerste pagina te beginnen lezen, verliest een database engine tijd door altijd elk databaserecord te controleren.

We maken database indexen aan om deze inefficiëntie te vermijden. Net als woordenlijsten of telefoonboeken zijn ze gespecialiseerd in slechts een stukje van de aanwezige informatie, maar maken ze het wel mogelijk sneller informatie terug te vinden. Door op een verstandige manier om te springen met indexen kunnen we onze database veel efficiënter gebruiken zonder te investeren in sterkere hardware.

In eerste instantie is een tabel geïndexeerd op basis van zijn primaire sleutel, maar we kunnen ook indexeren op andere kolommen of zelfs op een combinatie van kolommen.

Indexeren verhoogt de snelheid waarmee gegevens worden geselecteerd, maar vertraagt de werking bij het opslaan van gegevens. Wanneer gegevens worden toegevoegd, wordt niet alleen de tabel bijgewerkt, maar moet ook de index worden bijgewerkt. Vergelijk met de registratie van een nieuwe persoon in een gemeente. Als deze persoon zich aanmeldt op het stadhuis en er wordt een telefoonboek bijgehouden, moeten de gegevens van de personendienst en de gegevens van het telefoonboek worden aangepast. Dat is meer werk dan alleen het aanpassen van de persoonsgegevens.

Een index kun je op elk willekeurig moment creëren/wijzigen/verwijderen, zonder dat dit invloed heeft op de opgeslagen data in de bijhorende tabellen. Anders gesteld: het is niet omdat het telefoonboek niet meer wordt uitgegeven, dat je plots geen telefoonnummer meer hebt.

Toepassingen van indexen

Indexen kunnen nuttig zijn in vaak voorkomende scenario's:

• in queries met een WHERE-clause die de waarde van een geïndexeerd attribuut controleert kunnen veel rijen op voorhand worden uitgesloten (bijvoorbeeld WHERE Personen.Voornaam = 'Elias' sluit al veel mogelijkheden uit)

• om snel minima en maxima te bepalen (zoals hoe een woordenlijst vaak alfabetisch is, zijn ook indexen op kolommen met getalwaarden vaak gesorteerd)

• om JOIN-operaties op geïndexeerde attributen te versnellen (herinner je dat INNER JOIN ON in MySQL net hetzelfde doet als een CROSS JOIN WHERE)

• ...

Je vindt hier een officiële lijst terug van scenario's waarin MySQL zinvol gebruik kan maken van indexen. Let wel op: indexen hebben niet alleen voordelen. Anders zouden we op elk attribuut en op elke combinatie van attributen een index definiëren. Elke index neemt plaats in en moet onderhouden worden, wat betekent dat de schrijfperformantie van het systeem een beetje daalt.

Belangrijke types index in MySQL

Niet alle indexen zijn hetzelfde. Zoals eerder aangegeven kunnen indexen gebaseerd zijn op één kolom, of op meerdere kolommen. Een index met strengere voorwaarde zal typisch een grotere snelheidswinst opleveren dan een index met zwakkere eisen, omdat je sneller weet of je klaar bent met zoeken. Volgende indexen zijn voorzien in MySQL (en stemmen in sommige gevallen overeen met constraints op je data zoals UNIQUE):

• Primaire sleutels: deze heb je meestal sowieso al aangemaakt. Primaire sleutelkolommen worden vaak gebruikt voor zoekoperaties en WHERE-clauses, dus het is logisch om ze automatisch in een index op te nemen. MySQL houdt rijen intern bij in een gesorteerde volgorde op basis van de primaire sleutel en dankzij deze sortering kan er efficiënt gezocht worden.

• Unieke indexen: deze kan je definiëren op (verzamelingen van) kolommen met unieke waarden. De data kunnen niet fysiek opgeslagen worden in een volgorde bepaald door deze index (omdat de primaire sleutel de volgorde al bepaalt), maar er kan een extra datastructuur met de unieke waarden worden bijgehouden.

• Gewone indexen: deze kan je definiëren op kolommen met waarden die niet noodzakelijk uniek zijn. Ze mogen ook NULL bevatten.

• Fulltext indexen: deze dienen voor kolommen die een (lang) blok tekst bevatten. De tekst wordt opgesplitst in individuele woorden, die dan geïndexeerd worden. Zo kan je efficiënt blokken tekst zoeken die één specifiek woord bevatten.

Wanneer heb ik nu een index nodig?

Je zal waarschijnlijk pas indexen nodig hebben als je met grotere databases gaat werken. Langs de andere kant: eens je op dat punt zit, worden enkele zorgvuldig gekozen indexen snel onmisbaar. Het is wanneer je database stilaan onder het gewicht van bepaalde queries begint te kreunen, dat je een echt geïnformeerde beslissing kan nemen. Je doet dit door twee dingen op te meten:

• welke soorten queries vaak uitgevoerd worden

• hoe "duur" dergelijke queries zijn

Als je weet dat bepaalde queries vaak voorkomen en/of veel zoekwerk doorheen kolommen vereisen, is het zinvol de betrokken kolommen te indexeren op een manier die goed past bij hun typische gebruik. In de praktijk zal je ook de eventuele snelheidswinsten die je indexen opleveren opmeten. Hiervoor gebruik je het EXPLAIN-statement, dat een overzicht geeft van hoe een bepaald statement is uitgevoerd.

Standaardgebruik

Een index toevoegen doe je in het algemene geval met volgende syntax:

CREATE INDEX Column1Column2Index on TableName(Column1,Column2);

Dit maakt een index op de combinatie van de kolommen Column1 en Column2. Als er maar één kolom is, laat je de tweede gewoon achterwege. Meer dan twee kolommen kan ook.

De volgorde is hier van belang. Het telefoonboek is bijvoorbeeld een soort index op gemeente, familienaam en voornaam. Je kan het alleen maar efficiënt gebruiken als je de eerste kolom kent en het wordt efficiënter naarmate je meer kolommen kent.

Prefix index

Wanneer je een secundaire index voor een kolom maakt, dus een index die niet over de primaire sleutel gaat, slaat MySQL de waarden van de kolommen op in een afzonderlijke gegevensstructuur. Dit zijn structuren waarin je snel kan opzoeken.

In het geval dat de kolommen de tekenreekskolommen zijn (zoals VARCHAR), zal de index veel schijfruimte in beslag nemen en mogelijk de INSERT-bewerkingen vertragen.

Om dit probleem aan te pakken, kan je met MySQL een index maken voor het leidende deel van de kolomwaarden van de tekenreekskolommen met behulp van de volgende syntax:

column_name(length)

Je kan een index op twee manieren creëren.

1. bij creatie van de tabel zelf

-- mogen meerdere colum_names zijn, gescheiden door komma
-- length is optioneel
CREATE TABLE Table_name(
column_list,
INDEX(column_name(length))
);

2. nadien toevoegen aan een bestaande tabel

-- zelfde opmerking als boven
-- je mag een index op meerdere kolommen samen maken
CREATE INDEX index_name
ON table_name(column_name(length));

In bovenstaande voorbeelden is de lengte het aantal tekens voor de datatypes zoals VARCHAR (en het aantal bytes voor binaire datatypes).

Voorbeeld

Taken

Als we onderstaande query uitvoeren, dan zal heel de tabel worden doorlopen omdat er geen index is bepaald op het veld waarop het zoekcriterium werd ingesteld.

USE ApDB;
SELECT *
FROM Taken
WHERE Omschrijving LIKE 'aardappel%';

Als we deze query met het EXPLAIN statement uitvoeren, krijgen we volgende resultaat.

USE ApDB;
EXPLAIN SELECT *
FROM Taken
WHERE Omschrijving LIKE 'aardappel%';

Als je de tabel taken veel bevraagt terwijl je filtert op de omschrijving, is het zinvol om voor deze kolom een index te creëren waardoor de uitvoering een heel stuk sneller zal verlopen.

De grootte van de kolom Omschrijving is tijdens het ontwerp ingesteld op 50 karakters, maar dat wil niet zeggen dat we in de index 50 karakters per mogelijkheid moeten bijhouden. Voor de index moet je de lengte van het voorvoegsel bepalen. Soms wordt er gezegd dat je dit zo efficiënt mogelijk dient te doen door de prefixlengte zo kort mogelijk te houden. Hier schuilt wel een gevaar in: wanneer de tabel met nieuwe data wordt uitgebreid, is de index misschien niet meer zo uniek. Dat leidt niet tot fouten maar mogelijk wel tot performantieverlies.

Hoe zoek je nu de ideale lengte van de prefix op? Een vuistregel: zorg dat de index meteen naar een uniek resultaat leidt, maar dat hij niet groter is dan nodig om dit te bereiken.

Stap 1: zoek het aantal verschillende waarden in de kolom op

select count(distinct Omschrijving)
from Taken;

Stap 2: bepaal de maximale lengte van een waarde in de kolom

select max(length(Omschrijving)) from Taken;

Stap 3: zoek de minimale lengte waarbij je geen informatie verliest. Dit kan nooit meer zijn dan het resultaat van stap 2. Als de langste kolomwaarde bijvoorbeeld 21 is, start je door te kijken of 20 karakters al dan niet zorgen voor informatieverlies. Via LEFT krijg je alleen de eerste (aantal) tekens uit een string.

select count(distinct left(Omschrijving,20))
from Taken;

Zodra er minder resultaten zijn, heb je de prefix te kort gekozen. Het antwoord is dus één meer dan de lengte waarbij het aantal resultaten begint te krimpen.

Indien 20 de perfecte lengte van de prefix is, dan gaan we de index opbouwen.

USE ApDB;
CREATE INDEX OmschrijvingIdx
ON Taken(Omschrijving(20));

Binnen de schema navigation kan je nu de index zien. Als je nu bovenstaande select-query opnieuw uitvoert zal deze efficiënter en sneller verlopen.

De rol van kolomvolgorde

Als je een index op meerdere kolommen aanmaakt, is de volgorde van deze kolommen belangrijk. Vergelijk met het telefoonboek. Daar heb je een index op plaatsnaam en familienaam. Dit staat je toe snel iemand terug te vinden. Als je geen informatie kent over hoe de plaatsnamen geordend zijn (stel je voor dat alle gemeentes willekeurig in het telefoonboek stonden in plaats van alfabetisch), moet je toch elke pagina doorzoeken om zeker te zijn dat je de persoon in kwestie niet over het hoofd ziet.

Wat betekent dit in de praktijk? Je kan een index op meerdere kolommen (bijvoorbeeld A, B en C) gebruiken om snel opzoekingen te doen als je werkt met een doorlopende reeks van de eerste kolommen (bv. A of A en B of A, B en C). Maar je kan niet sneller zoeken met deze index als je bijvoorbeeld enkel B en C betrekt in je zoekopdracht.

Indexen belasten het systeem als deze niet nodig zijn, daarom is het raadzaam om indexen die niet (meer) nodig zijn te verwijderen. De reden van deze belasting kun je vinden in het feit dat indexen moeten worden bijgewerkt zodra de data in een tabel worden aangepast. Bovendien kun je indexen niet aanpassen of wijzigen, hiervoor moet je de index verwijderen en opnieuw aanmaken volgens de nieuwe ingevingen.

Drop index

Om een bestaande index te verwijderen van een tabel gebruik je het drop index statement.

DROP INDEX index_name ON table_name;

Drop primary key

De primary key is een index en door middel van dit commando kan je deze index verwijderen.

DROP INDEX `PRIMARY` ON table_name;

De primary key is een unieke index bij creatie. Er kan echter slechts één primary key per tabel worden gedefinieerd en indien je toch op een andere kolom ook een unieke index wil leggen kan je dit door een index te bepalen die uniek is. Je kan meerdere unieke indexen binnen één tabel hebben, maar slechts één primary key. De data zullen dan niet fysiek opgeslagen zijn in volgorde van deze unieke index, maar er zal wel een hulpstructuur bijgehouden worden die voor elke unieke waarde zegt waar je ze kan terugvinden.

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

Er zijn twee manieren om een unieke index te creëren, de eerste bij creatie van de tabel of in het tweede geval nadien.

Bij creatie van de tabel doe je dit als volgt.

CREATE TABLE table_name(
//...
UNIQUE KEY(index_column_1,index_column_2,...) 
);

Deze syntax is algemeen en zegt dat een combinatie van waarden uniek moet zijn. Als je index maar één kolom gebruikt, kan je dit doen met een UNIQUE constraint op die kolom, bijvoorbeeld:

CREATE TABLE table_name(
//...
ColName VARCHAR(100) UNIQUE
);

Voorbeeld

We creëren een tabel met een primary key en een unieke index.

CREATE TABLE IF NOT EXISTS People (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    mail VARCHAR(50) NOT NULL,
    UNIQUE KEY index_unique_mail (mail)
);

Met het commando SHOW INDEXES FROM table_namekan je zien dat de unieke index werd aangemaakt.

SHOW INDEXES FROM People;

Vervolgens voegen wat data toe aan de tabel People.

INSERT INTO People(first_name,last_name,mail)
VALUES
('John','Doe','john.doe@modernways.be'),
('Jane','Doe','jane.doe@modernways.be'),
('John','Roe','john.roe@modernways.be'),
('Jane','Roe','jane.roe@modernways.be')
;

Omdat we op mail een unieke index hebben gelegd is het niet meer mogelijk personen toe te voegen met opgave van een mailadres dat al bestaat. Concreet, wanneer we Jef Doe met als mailadres john.doe@modernways.be zouden willen toevoegen, zal dit resulteren in een fout, want het opgegeven mailadres komt reeds voor.

INSERT INTO People(first_name,last_name,mail)
VALUES ('Jef','Doe','john.doe@modernways.be');

Stap 1: bijhouden

Met de juiste instellingen kan je MySQL queries laten onthouden die langer duren dan een zekere drempelwaarde. Als het gaat om queries die regelmatig terugkomen, is het de moeite waard indexen aan te maken die deze queries kunnen versnellen.

Je ziet in MySQL Workbench hoe lang het duurt om een query uit te voeren:

Stap 2a: uitpluizen zonder MySQL Workbench

Je voert de queries in kwestie opnieuw uit, maar je laat ze voorafgaan door het sleutelwoordje EXPLAIN. Je krijgt dan één rij per doorzochte tabel in een uitgebreide versie van volgend formaat:

Als er voor een index NULL staat, betekent dat dat alle rijen van de eerste tot de laatste zijn beschouwd, wat typisch erg inefficiënt is (zeker als het aantal rijen groot is). Door een index toe te voegen aan een kolom met een gezochte waarde (bv. een kolom vermeld in een WHERE), kan je de NULL wegwerken en het aantal doorzochte rijen drastisch verlagen.

Stap 2b: uitpluizen met MySQL Workbench

MySQL Workbench heeft ook een grafische weergave van je uitvoeringsplan. Deze kan je bekijken na het uitvoeren van je statement via onderstaande knop:

Als je op deze knop klikt, krijg je een diagram waarin wordt uitgelegd welke stappen nodig zijn om je query uit te voeren. Een volledig overzicht van de betekenis van de elementen in zo'n diagram vind je hier. Je hoeft niet elk onderdeel te kennen, maar bekijk de pagina kort. Belangrijk is dat je rode boxes met daarin "full table scan" zo veel mogelijk vermijdt, zeker als het gaat om tabellen met veel data in.

Als je op een element van het diagram gaat staan met je muis, krijg je ook een technische uitleg over waarom dat onderdeel van je query waarschijnlijk niet efficiënt is. Let op! Het betreft hier nog steeds vuistregels. Een "full table scan" is bijvoorbeeld geen ramp als je tabel erg klein is. Dat wordt ook vermeld in de kleine lettertjes.

Stap 3: algoritme om te optimaliseren voor een query

Als je een bepaalde SELECT-query wil optimaliseren, kan je onderstaand stappenplan volgen:

1. Als er een WHERE voorkomt in je SELECT, voorzie dan één index op alle kolommen samen die via AND verbonden zijn en die rechtstreeks vergeleken worden met een constante waarde.

2. Voeg aan je index ook de eerste van de volgende mogelijkheden toe die je hieronder krijgt.

   a. Een kolom die niet rechtstreeks wordt vergeleken met een waarde, maar wel in een bereik moet liggen (via BETWEEN, >, <, LIKE waarbij het eerste karakter geen wildcard is)

   b. Alle kolommen die in een GROUP BY worden gebruikt, in de volgorde waarin ze in de GROUP BY worden vermeld

   c. Alle kolommen die in een ORDER BY worden gebruikt, in de volgorde waarin ze in de ORDER BY worden vermeld, maar enkel als er geen mix van ASC en DESC voorkomt in de ORDER BY

Bovenstaand stappenplan is afkomstig uit de documentatie van MariaDB. Dit is een zeer nauwe verwant van MySQL. Als je de pagina bekijkt, zie je dat het laatste woord over indexen nog niet gezegd is. Voor deze cursus volstaat het algoritme, maar als je ooit in je carrière geconfronteerd wordt met performantieproblemen in een database, raadpleeg dan deze uitleg voor je nieuwe (virtuele) hardware aankoopt!