Bonnes pratiques MySQL

Quelques bonnes pratiques suffisent à maintenir de bonnes performances dans une application web. Un modèle de données réfléchi et une indexation soignée restent les meilleurs garde-fous pour éviter les mauvaises surprises en production.

Les plus gros problèmes de performances en base de données viennent le plus souvent d’un mauvais modèle de données et d’index manquants. Les requêtes SELECT sont les plus fréquentes dans une application web, mais aussi celles qui coûtent le plus cher. Voici quelques points (non exhaustifs) pour limiter leur impact.

Choisir le bon moteur et le bon encodage

Avant toute optimisation de requête, deux choix structurants :

• Utilisez InnoDB, le moteur par défaut depuis MySQL 5.5. Il gère les transactions, le verrouillage au niveau ligne (et non table comme l’ancien MyISAM) et l’intégrité référentielle via les clés étrangères. MyISAM n’a plus de raison d’être sur une application moderne.
• Utilisez utf8mb4, jamais utf8. Dans MySQL, utf8 est en réalité utf8mb3 : il ne gère que 3 octets par caractère et casse sur les emojis et certains caractères. utf8mb4 est le vrai UTF-8 complet, à appliquer au niveau table et connexion.

Indexation

Chaque table doit avoir une clé primaire : sous InnoDB, elle définit l’index clusterisé qui structure physiquement les données sur le disque. Une clé primaire courte (un entier auto-incrémenté, par exemple) est donc préférable.

Quelques règles utiles :

• Indexez les clés étrangères et les colonnes servant aux jointures et aux clauses WHERE.
• Pour les requêtes filtrant sur plusieurs colonnes, pensez aux index composites, en respectant la règle du leftmost prefix : un index (a, b, c) sert les filtres sur a, a+b, a+b+c, mais pas un filtre portant uniquement sur b.
• Visez quand c’est possible un index couvrant (covering index) : si l’index contient toutes les colonnes nécessaires à la requête, MySQL répond sans lire la table (Using index dans l’EXPLAIN).
• Gardez vos requêtes sargables : une fonction appliquée sur une colonne indexée (WHERE YEAR(date) = 2026) ou un LIKE '%terme%' avec joker en tête empêchent l’usage de l’index.
• N’indexez pas tout : chaque index accélère les lectures mais ralentit les écritures et occupe de l’espace.

Écrire des requêtes efficaces

• Évitez SELECT * : ne récupérez que les colonnes réellement utilisées. C’est plus rapide, plus léger sur le réseau, et indispensable pour bénéficier des index couvrants.
• Méfiez-vous des requêtes N+1 côté applicatif (typiquement avec un ORM) : une boucle qui déclenche une requête par itération est un tueur de performances silencieux.
• Limitez les volumes retournés (LIMIT, pagination) plutôt que de filtrer côté application.

Limiter la taille des lignes

Une remarque héritée des vieux articles MySQL veut qu’on place les colonnes NULL « en fin de table pour gagner de la place ». Sous InnoDB, l’ordre des colonnes n’a en pratique aucun impact mesurable sur le stockage : cette optimisation est un mythe issu de l’ère MyISAM.

Ce qui reste vrai en revanche : isolez les grosses colonnes rarement lues (TEXT, BLOB, contenus volumineux) dans des tables séparées, récupérées par jointure au besoin. Cela garde les lignes principales compactes, donc plus de lignes par page et un buffer pool plus efficace.

Analyser les requêtes

Le slow query log

Le slow query log met en évidence les requêtes lentes du serveur. On l’active dans la configuration MySQL :

[mysqld]
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 1
log_queries_not_using_indexes = 1

Commencez avec un long_query_time à 5 secondes pour cibler les pires requêtes, puis descendez à 1 seconde une fois celles-ci traitées. Pour exploiter le fichier, des outils comme mysqldumpslow ou pt-query-digest (Percona Toolkit) agrègent et classent les requêtes par impact.

Le Performance Schema et le sys schema

Plus besoin de fouiller des fichiers de logs : MySQL 8 expose en temps réel les statistiques d’exécution via le Performance Schema, rendu lisible par le sys schema. Pour obtenir les requêtes les plus coûteuses, normalisées par empreinte :

SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

C’est aujourd’hui la façon la plus rapide d’identifier ce qui plombe une base, sans rien activer de plus.

EXPLAIN

La clause EXPLAIN devant un SELECT montre le plan d’exécution choisi par l’optimiseur. Note importante : la syntaxe EXPLAIN EXTENDED des anciennes versions a été supprimée dans MySQL 8.0 — son comportement est désormais celui d’EXPLAIN par défaut.

EXPLAIN
SELECT id, total FROM commandes WHERE client_id = 42;

Les colonnes les plus utiles à lire :

• type : la méthode d’accès. On vise const, eq_ref, ref ou range ; on se méfie de ALL (scan complet de la table).
• key : l’index réellement utilisé (NULL = aucun index, à investiguer).
• rows : estimation du nombre de lignes parcourues.
• Extra : Using index (index couvrant, idéal), Using filesort ou Using temporary (souvent à optimiser).

Pour aller plus loin, deux variantes modernes :

-- Plan détaillé au format JSON (coûts estimés par étape)
EXPLAIN FORMAT=JSON SELECT ...;

-- Exécute réellement la requête et donne les temps mesurés (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT id, total FROM commandes WHERE client_id = 42;

EXPLAIN ANALYZE est particulièrement précieux : il affiche le coût estimé à côté du temps réellement mesuré, ce qui permet de repérer les écarts entre ce que prévoit l’optimiseur et la réalité.

-> Index lookup on commandes using idx_client (client_id=42)
   (cost=2.45 rows=6) (actual time=0.042..0.061 rows=6 loops=1)

Régler le serveur : le buffer pool

Au-delà des requêtes, le levier de performance le plus important sur InnoDB est le buffer pool, le cache mémoire qui conserve données et index. Sur un serveur dédié à MySQL, on le dimensionne typiquement autour de 70-80 % de la RAM :

[mysqld]
innodb_buffer_pool_size = 8G

Un buffer pool bien dimensionné évite les allers-retours disque et change radicalement les temps de réponse sous charge. Cette variable (innodb_buffer_pool_size) est identique sous MariaDB.

Spécificités MariaDB

La grande majorité des conseils ci-dessus s’appliquent tels quels à MariaDB (InnoDB par défaut, utf8mb4, indexation, buffer pool). Voici les points où les deux moteurs divergent et qu’il faut connaître.

Analyse des requêtes. MariaDB n’utilise pas EXPLAIN ANALYZE : son équivalent est la commande ANALYZE (introduite dès MariaDB 10.1). ANALYZE SELECT ... exécute la requête et annote le plan avec les colonnes r_rows, r_filtered et r_total_time_ms, qui confrontent l’estimation de l’optimiseur à la réalité. ANALYZE FORMAT=JSON en donne le détail.

-- Équivalent MariaDB de EXPLAIN ANALYZE
ANALYZE SELECT id, total FROM commandes WHERE client_id = 42;

À noter : EXPLAIN EXTENDED, supprimé de MySQL 8, existe toujours sous MariaDB.

Optimiseur. MariaDB possède son propre optimiseur, qui a divergé de celui de MySQL et peut produire des plans d’exécution différents (mais corrects) pour une même requête. Conséquence pratique : profilez toujours sur le moteur que vous utilisez réellement en production, sans présumer que le plan sera identique.

Type JSON. Sous MariaDB, le type JSON est un alias de LONGTEXT : les données sont stockées en texte, là où MySQL utilise un format binaire natif. La sémantique de comparaison diffère (chaînes côté MariaDB, valeurs JSON côté MySQL), un point d’attention en cas de migration MySQL → MariaDB sur des colonnes JSON.

Moteurs de stockage. Au-delà d’InnoDB (le défaut commun), MariaDB mise sur une architecture multi-moteurs plus riche — Aria, ColumnStore (analytique), MyRocks, Spider — utile pour des charges spécifiques.

Concurrence et versions. MariaDB embarque un thread pool natif côté communauté (intéressant sur des charges très concurrentes en lecture), là où il est réservé à l’édition Enterprise chez MySQL. Côté versions enfin, visez une ligne LTS : 11.4 LTS pour MariaDB, comme 8.4 LTS pour MySQL.

Conclusion

L’optimisation d’une base MySQL commence dès la conception (modèle, moteur, encodage), se poursuit avec une indexation et des requêtes soignées, puis se mesure avec les bons outils : slow query log, Performance Schema et EXPLAIN ANALYZE. Le tout repose sur un serveur correctement réglé. Ces fondamentaux, appliqués tôt, évitent l’immense majorité des problèmes de performances en production.