Mijn MySQL-database optimaliseren, hoe doe ik dat? In dit artikel gaan we in op het optimaliseren van een MySQL-database en tabellen. Tegenwoordig is een goed geoptimaliseerde database erg belangrijk voor de snelheid van jouw website.

Het optimaliseren van een MySQL-database

MySQL-database optimalisatie is een erg belangrijk onderdeel als jouw website gebruik maakt van een MySQL-database. Daarbij maakt het niet uit of je een bestaand CMS als WordPress, Drupal of Joomla gebruikt, of dat je zelf een CMS en website hebt gemaakt.

Informatie in jouw MySQL-database moet snel doorzoekbaar zijn en gevonden worden, want een groot deel van de snelheid van jouw website is daarvan afhankelijk. Of zoals een klant van ons onlangs meldde:

… maar ik heb de enorme snelheidsverbetering door het gebruik van indexen onderschat.

In dit artikel, en latere artikelen, gaan we daarom nog verder in op MySQL-database optimalisatie. Zie bijvoorbeeld ook MySQL-database normaliseren.

MySQL-database en WordPress

In het eerste deel over WordPress optimaliseren heb ik al belangrijke informatie gegeven over wat je kunt doen om jouw database beter te laten presteren met WordPress, zoals berichtrevisies opruimen en het indexeren van autoload-waarden in de wp_options tabel.

Maar er is meer (meer, méér)!

Hoeveel je zelf kunt doen of waar je afhankelijk bent van jouw hostingprovider is soms afhankelijk van jouw eigen kunde, neem contact op met jouw hostingprovider als je twijfels hebt.

MySQL tabellen optimaliseren

Net als met de gegevens op de harde schijf van jouw computer, raken gegevens (data, informatie) verspreid, of gefragmenteerd. 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 je harde schijf regelmatig moet “defragmenteren”, moet je jouw MySQL-database tabellen “optimaliseren”.

MySQL tabellen optimaliseren via phpMyAdmin

De webapplicatie phpMyAdmin heb ik al genoemd in het artikel over het maken van een MySQL backup met phpMyAdmin. Als je op de online phpMyAdmin-omgeving bent ingelogd en jouw database hebt geselecteerd, dan zie je een overzicht van alle database tabellen. Dit kunnen er zo tien- of honderdtallen zijn.

Onderaan die overzichtspagina staat een optie om in één keer alle tabellen te selecteren. Geloof me, dat is vaak sneller dan elk selectievakje aanvinken 😛 . Zodra alle tabellen geselecteerd zijn, kies je in het drop-down menu Met geselecteerd voor de optie Optimaliseer tabel. PhpMyAdmin voert dan het OPTIMIZE TABLE-commando uit op al jouw database tabellen.

Als dit optimaliseren klaar is zul je zien dat de MySQL-database (iets) kleiner in omvang is. Alle data is gedefragmenteerd en staat weer netjes achterelkaar.

PhpMyAdmin’s SQL-tab om queries en statements uit te voeren

Het is hipper om het SQL-tab van phpMyAdmin te gebruiken. Daarmee kun je zelf queries uitvoeren op de database, en dus ook statements om deze OPTIMIZE TABLE opdracht te geven.

Bijvoorbeeld dit onderstaande:


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;

Het ziet er ingewikkeld uit, maar wat het doet is:

  1. gebruik de MySQL-functies SELECT, CONCAT en GROUP_CONCAT om alle tabelnamen op te vragen, uit de kolom tables van de -veelal verborgen- database information_schema, en plak daar ‘OPTIMIZE TABLE ‘ (inclusief spatie) voor. Het resultaat is bijvoorbeeld OPTIMIZE TABLE wp_options, wp_posts, wp_users.
  2. Die uitvoer wordt opgeslagen in een variabele optimizecmd, geprepareerd en uitgevoerd. That’s it eigenlijk, viel mee toch?

MySQL tabellen optimaliseren met PHP/MySQLi

Naast phpMyAdmin kun je natuurlijk ook zelf een PHP-script maken wat automatisch alle tabellen optimaliseert. Hiervoor kun je PHP/MySQLi multi_query() functie gebruiken, zie dit op Saotn.org gelinkte artikel.

Nu de tabellen in topvorm zijn, alle data gedefragmenteerd is en netjes achterelkaar staat, is het tijd om wat aan MySQL’s storage engine te doen.

MySQL tabellen optimaliseren in WordPress met een plugin

Voor het optimaliseren van MySQL tabellen via WordPress heb ik een kleine plugin ontwikkeld. Deze plugin haakt in op de WordPress Cron functionaliteit en voert zo één keer per dag of weekelijks een OPTIMIZE TABLE opdracht uit. Je vindt de code van deze plugin op Saotn.org, in mijn post Optimize WordPress MySQL tables through Cron, behind the scenes.

Test er eens mee 🙂

mysqlcheck
Werk je graag op de opdrachtprompt van Windows of Linux, dan kan je deze OPTIMIZE en ANALYZE taken vereenvoudigen, en automatiseren, met de opdracht mysqlcheck.

MySQL InnoDB storage engine versus MyISAM

InnoDB en MyISAM zijn storage engines in MySQL. In een storage engine wordt bepaald hoe de informatie opgeslagen is en geraadpleegd moet worden. Als jouw website en database al wat langer op internet bestaat, is de kans groot dat jouw databasetabellen nog van het type MyISAM zijn. Net als Windows XP, Windows 7, PHP 5.3, PHP 5.4 of ASP.NET 2.0/3.5: dat is oud. In tegenstelling tot InnoDB wordt MyISAM niet meer doorontwikkeld.

Omdat InnoDB wél doorontwikkeld wordt, en alle prestatieverbeteringen daarin beschikbaar worden gemaakt, is het beter jouw MySQL-databasetabellen om te zetten van MyISAM naar InnoDB. En gelukkig is dat niet al te moeilijk.

Je herkent MyISAM-tabellen voornamelijk aan alle oude code die je nog op internet terugvindt, waarin engine=MyISAM staat.

MyISAM tabellen omzetten naar InnoDB

Tot pak-em-beet een jaar geleden moest je nog oppassen met het omzetten/converteren van MySQL’s storage engine, want bijvoorbeeld een FULLTEXT index werd in InnoDB nog niet ondersteund. Tegenwoordig gelukkig wel, maar vraag eerst bij je hostingprovider na welke MySQL-, MariaDB- of Percona-versie er in gebruik is.

Met een PHP/MySQLi scriptje, of weer in het SQL-tab van phpMyAdmin kun je het volgende SELECT statement uitvoeren:


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

Met het OPTIMIZE TABLE gebeuren van hierboven kun je wel ongeveer nagaan wat hier gebeurt. Dit statement geneert een lijst van alle tabellen met daarvoor de opdracht ALTER TABLE, en ENGINE=InnoDB erachter. Simpel gezegd, met ALTER TABLE pas je een tabel aan, en in dit geval is dat het storage engine. De uitvoer is bijvoorbeeld:


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

Maar dit heeft nog niets gedaan! Het is de bedoeling dat je al deze regels in phpMyAdmin selecteert, kopieert en uitvoert via het SQL-tab.

Van MyISAM naar InnoDB in WordPress met een plugin

Gebruik je WordPress als CMS voor jouw website? Dan kan dit MyISAM converteren naar InnoDB ook met een plugin. Ik ben co-ontwikkelaar van de Vevida Optimizer plugin, en hierin zit deze functionaliteit ingebouwd.

MySQL-database normaliseren

Net zo belangrijk als het optimaliseren van tabellen en het omzetten van MyISAM naar InnoDB van tabellen, is het om tabellen te normaliseren. Data en informatie moet zo weinig mogelijk in de database staan om dubbele data te voorkomen.

Eén van de doelen van het normaliseren van een MySQL database is om dubbele gegevens te voorkomen. Gegevens worden verdeeld over meerdere, aan elkaar gerelateerde databasetabellen en komen bij voorkeur maar één keer voor. Jouw MySQL-database is hierdoor kleiner, duidelijker, eenvoudiger in het beheer én sneller toegankelijk.

Je leest hier alles over MySQL database normaliseren.

Conclusie

In dit artikel heb ik je een aantal mogelijkheden gegeven om jouw MySQL-database en tabellen te optimaliseren (defragementeren als het ware), en om jouw oude MyISAM-tabellen om te zetten naar InnoDB. In een volgend artikel gaan we in op het toevoegen van een index op een tabel kolom, waardoor MySQL informatie nog sneller kan vinden.

Op Saotn.org heb ik een hele categorie gewijd aan MySQL optimalisatie. Een aantal artikelen is juist bedoeld voor MySQL-database en serverbeheerders, dus wijs jouw hostingprovider daar eens op.


MCSA Windows Server 2016

Bereid je met dit boek voor op je MCSA Windows Server 2016 examens 70-740, 70-741 en 70-743, nu via bol.com! Dit complete studieboek is het enige boek dat je nodig hebt voor jouw MCSA 2016 certificering. Met deze aankoop steun je ITFAQ.nl, waarvoor dank!

Je vindt misschien ook interessant: 70-744 Securing Windows Server 2016 | 70-740 Installation, Storage and Compute with Windows Server 2016 | 70-741 Networking with Windows Server 2016 with Practice Test | 70-743 Upgrading Your Skills to MCSA | Kaspersky Internet Security 2019 (3 gebruikers, 1 jaar) - via bol.com

Psst: steun ITFAQ.nl en doneer een kop koffie via paypal.me/jreilink.