Keeping your MySQL database lean and mean!

In dit document gaan we in op het optimaliseren van een MySQL­-database. “Waarom?”, vraag je je wellicht af. Onze ervaring leert dat een opgeblazen en slecht onderhouden MySQL­ database één van de grootste oorzaken is van een trage website. Voor een vlot reagerende website is het belangrijk om aandacht te besteden aan het optimaliseren van de achterliggende database.

We proberen dit document zo toegankelijk mogelijk te houden. Gaat het misschien toch jouw pet te boven en heb je hierover vragen, schroom dan niet om contact op te nemen met onze klantenservice. We helpen je graag.

In dit document stippen we de volgende onderwerpen aan:

Op sommige onderwerpen gaan we wat dieper in dan op anderen. Het onderwerp “MySQL-database optimalisatie” is veel besproken op internet. Gebruik wat relevante zoekwoorden in Google om je hierin verder te verdiepen. Dat is belangrijk. Oók voor gebruikers van een CMS of blog, dat eigenlijk het hele databasegebeuren voor jou regelt, is MySQL optimalisatie belangrijk. Niet iedere ontwikkelaar van een CMS doet zijn werk even goed, en je kunt zelfs veel snelheidswinst behalen op de standaard MySQL-database zoals WordPress die aanmaakt.

Let op: het is helaas niet altijd mogelijk om goede, representatieve voorbeelden te gebruiken. We doen ons best om alles zo eenduidig mogelijk te houden, maar hier en daar zullen we wat reële waardes moeten verzinnen.

Beschouw dit document niet als een stap voor stap handleiding. Daarvoor is de materie te complex en uitgebreid. Zie dit document meer als een aantal handvatten, met onderwerpen en steekwoorden, waarmee je zelf verder kunt.

Wat is een MySQL-database?

Iets wat versimpeld: Een database is een gestructureerde collectie van data (informatie). Zie het als een archiefkast gevuld met documenten, een adres- of telefoonboek enz. In een archiefkast liggen documenten opgeslagen, gesorteerd op bijvoorbeeld de eerste letter van een achternaam, op datum, of genummerd. Dit is met een database niet anders.

MySQL is een relationeel database management systeem, of RDMS. Een relationele database slaat informatie (data, gegevens) op in aparte tabellen in plaats van in één lange waslijst van informatie. Het grote voordeel hiervan is snelheid en flexibiliteit; wetende in welke lade je moet zoeken kun je de informatie sneller vinden dan wanneer je een meterslange lijst regel voor regel moet bekijken.

Eenvoudig gezegd is een archiefkast met lades te zien als een tabel, met daarin de lades als kolommen met opgeslagen gegevens. De archiefkamer, met daarin de kasten, is de database.

De “SQL” in “MySQL” staat voor Structured Query Language en is de standaard taal om databases te benaderen. Met SQL kun je informatie opvragen uit een database, of juist bijwerken, informatie opslaan of verwijderen. De commando’s hiervoor zijn o.a SELECT, INSERT, UPDATE, CREATE en DROP. Van SELECT zegt men het is een “query” (vraag), de overigen zijn “statements” of opdrachten.

Gegevens opvragen uit een MySQL-database

Om gegevens uit een MySQL-database op te vragen gebruik je de opdracht SELECT. Zoals de naam al aangeeft, het selecteert informatie. Bijvoorbeeld SELECT * FROM `adressen` laat alle informatie in de tabel adressen zien. Je kunt zo ook eenvoudig opvragen wie er allemaal aan de Kerklaan woont. Hiervoor gebruiken we een clausule: SELECT * FROM `adressen` WHERE `straat` = 'Kerklaan'. De conditie WHERE laat ons filteren, in dit geval op de kolom straat.

Met een adressenbestand van een tiental of honderd adressen is dit nog prima te doen. Voor iedere SELECT opdracht begint MySQL bij de eerste rij (primary key 1) om te bekijken of dat een match is voor hetgeen wij opvragen. Daarna gaat MySQL naar rij 2, rij 3, 4, enzovoorts tot het eind.

In de loop der jaren verzamelen we er nogal wat adressen bij: familie, vrienden, bekenden, werknemers en ex-werknemers, andere zakelijke relaties, enz. Wil je die allemaal bijhouden? Dan is het toch belangrijk de database hierop voor te bereiden. Wat met 100 rijen nog snel gaat, gaat met 1.000, 10.000 of meer rijen niet meer zo snel.

Je bereidt de database voor op het doorzoeken van veel rijen door het plaatsen van een index (indices in meervoud). Door het plaatsen van een index op een kolom kan MySQL een rij extra snel vinden. Ook als je alleen de 9999e rij nodig hebt en zoekt. MySQL hoeft dan niet bij de eerste rij te beginnen, om naar twee, drie, vier, .. tot en met 9999 te gaan. Dit scheelt ontzettend veel tijd.

In theorie moet er een index zijn geplaatst op iedere kolom die wordt gebruikt in een WHERE clausule.

Vraag alleen dat op wat noodzakelijk is

Vaak worden gegevens opgevraagd met de hierboven genoemde query: SELECT * FROM `adressen`. Dit retourneert alle gegevens in de tabel adressen. Vaak ook overbodige informatie. Vraag daarom alléén op wat noodzakelijk is. Wil je aléén weten in welke straat de heer Janssen woont, gebruik dan (versimpeld voorbeeld):

SELECT `straat` FROM `adressen` WHERE `naam` = 'Janssen'

Want als je hier SELECT * FROM `adressen` WHERE `naam` = 'Janssen' gebruikt, dan geeft dat informatie terug die wij helemaal niet zoeken, zoals mogelijk zijn voornaam, telefoonnummer, geboortedatum en al het andere wat zich in die tabel bevindt.

Gegevens snéller opvragen uit een MySQL-database

We laten ons adressenbestand even voor wat het is. Om inzichtelijk te maken hoe je nóg sneller gegevens uit een MySQL-database kunt opvragen hebben we een “real life” voorbeeld nodig: WordPress.

WordPress slaat heel veel informatie op in een tabel wp_options. Een wp_options tabel met (tien-)duizenden rijen is vrij normaal als de WordPress website al een tijdje meedraait. Daarom is het verstandig als je weet hoe je dit gegeven kunt gebruiken voor het optimaliseren van jouw database. Of dat nou een WordPress database, of zelf ontwikkelde database is dat maakt niet uit.

Je kunt een MySQL SELECT query verklaren met de extra opdracht EXPLAIN. Als je EXPLAIN vóór SELECT plaatst, dan wordt de query niet uitgevoerd, maar laat MySQL zien wat het zou doen met welke gegevens.
Eén van de queries die WordPress altijd uitvoert is:

SELECT `option_name`, `option_value` FROM `wp_options` WHERE `autoload` = 'yes';

en hiermee onderzoekt WordPress welke opties – bijvoorbeeld van plugins – automatisch geladen moet worden.

Gebruik je hierbij EXPLAIN:

EXPLAIN SELECT `option_name`, `option_value` FROM `wp_options` WHERE `autoload` = 'yes';
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | wp_options | ALL  | NULL          | NULL | NULL    | NULL | 33927 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

dan val je soms bijna stijl achterover van verbazing. Je ziet in dit specifieke voorbeeld dat er 33927 rijen aanwezig zijn. Dat zijn er nogal wat, zelfs voor WordPress. De kolom key geeft helaas NULL aan, dat wil zeggen: er is geen index aanwezig die gebruikt kan worden met deze WHERE conditie. Oftewel, er is geen index geplaatst op “autoload”. Prima stukje database om te optimaliseren, niet?

Laten we eens zien hoeveel rijen er daadwerkelijk “yes” als autoload waarde hebben. Hiervoor gebruiken we SELECT in combinatie met COUNT(*) om de rijen te tellen.

SELECT COUNT(*) FROM `wp_options` WHERE `autoload` = 'yes';
+----------+
| COUNT(*) |
+----------+
|     1337 |
+----------+
1 row in set (0.00 sec)

Je ziet het, om slechts 1337 rijen terug te geven moet MySQL eerst 33927 rijen doorzoeken. Erg inefficiënt.

Dit kun je eenvoudig verbeteren met een index op “autoload”, waarvoor je de tabelstructuur aanpast met de opdracht ALTER TABLE.

ALTER TABLE `wp_options` ADD INDEX (`autoload`);

Als je de opdracht EXPLAIN SELECT nogmaals uitvoert zul je zien dat het veel beter geoptimaliseerd is:


EXPLAIN SELECT `option_name`, `option_value` FROM `wp_options` WHERE autoload = 'yes';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | wp_options | ref  | autoload      | autoload | 62      | const | 1508 | Using where |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Hierin zie je “autoload” vermeld staan als mogelijke index (possible_keys), en als gebruikte index (key). Omdat MySQL nu sneller te gevraagde informatie weet te lokaliseren hoeft het nog maar 1105 rijen te doorzoeken. Door slechts één index te plaatsen!

Gegevens in een MySQL-database structureren en normaliseren

Gegevens in een database komen bij voorkeur slechts één keer voor. Om weer even terug te komen op het adres-/telefoonboek uit het onderwerp Gegevens opvragen uit een MySQL-database: In een telefoonboek vind je de heer Janssen ook niet terug op zijn naam, zijn adres én op zijn telefoonnummer. Dat is namelijk zonde van het aantal pagina’s; de opslagruimte.

Voordat je begint met het opbouwen van jouw MySQL-database moet je dus nadenken over de structuur. “Welke gegevens sla ik waar op, en hoe?” Dit is het begin van “normaliseren“. Gegevens zoals voor- en achternaam, adres en telefoonnummer worden opgeslagen in afzonderlijke tabellen. Eén voor de voor- en achternaam, één voor het adres en één voor het telefoonnummer.

Hierdoor blijft de database klein, overzichtelijk én snel doorzoekbaar.

Voor de oplettende lezer: ja, de hierboven genoemde structuur is vaak nog verder te normaliseren door ook de postcode in een aparte tabel te plaatsen. De kans is namelijk reëel dat een postcode vaker dan één keer in de tabel zal voorkomen.

Idealiter heeft iedere tabel een unieke key, de zogenoemde primary key. Een primary key is altijd uniek. De onderlinge tabellen staan met elkaar in relatie, want de heer Janssen woont aan de Kerklaan, nummer 3 met postcode 1020 AA te Amsterdam. Die gegevens moeten aan elkaar gerelateerd worden, evenals zijn telefoonnummer 020-10000001. Hiervoor gebruik je zogenoemde Foreign keys. “Foreign” (extern), omdat het verwijst naar informatie in een andere, buitenstaande, tabel.

Je kunt gegevens uit verschillende tabellen opvragen door deze te combineren met een JOIN opdracht. Hiermee combineer je gegevens uit verschillende tabellen en koppel je aan elkaar met behulp van de foreign key.

MySQL Storage Engine, MyISAM versus InnoDB

Een tweede belangrijk aspect, waaraan je moet denken voordat je begint met het opbouwen van jouw database, is: “in welk formaat kunnen de gegevens het beste opgeslagen worden?”. Van welke storage engine maak je gebruik, of, wat voor soort tabellen maak je aan?

De twee meest gangbare soorten in MySQL zijn MyISAM en InnoDB tabellen. De storage engine bepaalt in wat voor structuur de informatie opgeslagen wordt en is uiterst belangrijk voor een goede performance van jouw database. In oudere versies van MySQL was MyISAM de standaard engine, en daarom zie je in veel – oudere – voorbeelden: engine=MyISAM bij CREATE TABLE codes. Echter, tegenwoordig is in nieuwere versies van MySQL InnoDB de standaard en MyISAM wordt niet meer doorontwikkeld.

Je vindt een vergelijking van verschillende database engines op Wikipedia: https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines.

Wij raden daarom zoveel mogelijk InnoDB aan. Let wel dat bepaalde zaken in InnoDB nog niet mogelijk zijn, zoals Full-Text indexes.

MyISAM tabellen omzetten naar InnoDB

Je kunt je bestaande MyISAM tabellen eenvoudig converteren (omzetten) naar InnoDB. Per tabel kun je hiervoor het volgende statement gebruiken:

ALTER TABLE tabelnaam ENGINE=InnoDB;

Ook kan dit eenvoudig via phpMyAdmin, en als je wat meer bekend bent met MySQL opdrachten kun je het volgende statement gebruiken in phpMyAdmin:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') 
FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema');

Dit statement zoekt alle MyISAM-tabellen op en genereert daarvoor alle benodigde ALTER TABLE statements in één keer:

ALTER TABLE db-name.table1 ENGINE=InnoDB;
ALTER TABLE db-name.table2 ENGINE=InnoDB;
ALTER TABLE db-name.table3 ENGINE=InnoDB;

Hierna moeten MySQL tabellen geoptimaliseerd worden, dit doen we met het volgende “prepared statement”:

SELECT CONCAT('OPTIMIZE TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') 
INTO @optimizecmd FROM information_schema.tables
WHERE table_schema=database();
PREPARE s1 FROM @optimizecmd; EXECUTE s1;
DEALLOCATE PREPARE s1;

Hiermee worden tabellen geoptimaliseerd, de database kleiner en is informatie dus sneller gevonden. Heb je vragen over (of met) deze twee statements, onze klantenservice helpt je graag.

MySQL-database onderhoud

Net als gegevens (data, bestanden) op jouw computers harde schijf, raakt data in een MySQL-database na verloop van tijd gefragmenteerd (verspreid). Door het verwijderen van data (informatie zoals nieuwsartikelen, reacties, forum-berichten en dergelijke) ontstaat er ruimte tussen de data in de database. Net als dat je de harde schijf regelmatig moet “defragmenteren”, moet je de MySQL-database tabellen “optimaliseren”. Hiermee worden gegevens weer netjes achter elkaar gezet en dus sneller gevonden.

Het optimaliseren van MySQL-tabellen kan heel eenvoudig via phpMyAdmin. Log in, klik door naar jouw database en selecteer alle tabellen. Onderaan de pagina vind je een drop-down menu met daarin de optie “optimaliseren”. De phpMyAdmin-applicatie regelt de rest. Je vindt onze phpMyAdmin-applicatie op https://mysql.myvevida.com. Je kunt uiteraard ook eerder genoemde prepared statement hiervoor gebruiken.

Wat vond je van dit antwoord?

Bedankt voor je feedback!

Er is een fout opgetreden. Probeer het later opnieuw.