Je wilt graag een snelle website én MySQL-database. MySQL-databasebeheer en onderhoud is hierbij erg belangrijk, maar ook optimalisatie. Daarom ga ik in dit artikel in op het optimaliseren van een MySQL-database en -tabellen. Je zult merken dat met een goed geoptimaliseerde database jouw website veel beter presteert.
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. Het optimaliseren van een MySQL-database is een gecompliceerd onderdeel van databasegebruik en -beheer. Je moet kennis te hebben van het gehele systeem, en je moet van te voren nagedacht te hebben over de structuur van niet alleen de website, maar ook van de achterliggende MySQL-database.
Zo kun je bijvoorbeeld de snelheid van een MySQL-database aanzienlijk versnellen door gebruik te maken van indices (een index). Maar te veel indices, of op verkeerde kolommen vertraagt de database. 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 ga ik daarom nog verder in op MySQL-database optimalisatie, en zo zijn er ook meer artikelen hierover op ITFAQ.nl. Zie bijvoorbeeld ook MySQL-database normaliseren.
WordPress en MySQL
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 :-P . 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 (of gewoon leuker) 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:
- gebruik de MySQL-functies
SELECT
,CONCAT
enGROUP_CONCAT
om alle tabelnamen op te vragen, uit de kolomtables
van de -veelal verborgen- databaseinformation_schema
, en plak daar ‘OPTIMIZE TABLE ‘ (inclusief spatie) voor. Het resultaat is bijvoorbeeldOPTIMIZE TABLE wp_options, wp_posts, wp_users
. - 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 voor alle informatie.
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.
Tip: 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, dat doe je door databasenormalisatie.
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.
MySQL-database normaliseren
PHP & MySQL boekentips
Wil je beginnen met PHP en MySQL? Of juist er méér mee leren doen? Maak een goede start met één van deze (ebook) boeken!
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.
Pingback: WordPress optimaliseren