Hoe optimaliseer je een MySQL database bij Vevida

In dit document gaan we in op het optimaliseren van een MariaDB-database (MySQL). “Waarom?”, vraag je je wellicht af. Onze ervaring leert dat een opgeblazen en slecht onderhouden MariaDB database (MySQL­) één van de grootste oorzaken is van een trage website. Voor een snel en goed 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.

Keeping your MySQL database lean and mean!

INHOUDSOPGAVE

1. Wat is een MySQL-database?
2. Gegevens opvragen uit een MySQL-database
3. Vraag alleen dat op wat noodzakelijk is
4. Gegevens snéller opvragen uit een MySQL-database
5. Gegevens in een MySQL-database structureren en normaliseren
6. MySQL Storage Engine, MyISAM versus InnoDB
7. MyISAM tabellen omzetten naar InnoDB
8. MySQL-database onderhoud

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'

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

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)

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

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

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)

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 InnoDB aan

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;

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');

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

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

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.vevida.com. Je kunt uiteraard ook eerder genoemde prepared statement hiervoor gebruiken.