MariaDB : index et performances

Index

Un index accélère les recherches au prix d’écritures un peu plus lentes et d’espace disque.

CREATE INDEX idx_commandes_client ON commandes (client_id);
CREATE UNIQUE INDEX idx_clients_email ON clients (email);
CREATE INDEX idx_commandes_statut_date ON commandes (statut, passee_le);
SHOW INDEX FROM commandes;
DROP INDEX idx_commandes_client ON commandes;

Règles utiles :

indexer les colonnes des WHERE, des JOIN et des ORDER BY fréquents ;
sur un index composé, l’ordre des colonnes compte (statut, passee_le sert aux recherches sur statut seul, pas sur passee_le seul) ;
les clés étrangères sont indexées automatiquement par InnoDB ;
éviter d’indexer les colonnes à très faible variété (booléen).

EXPLAIN

EXPLAIN SELECT * FROM commandes WHERE client_id = 42;
EXPLAIN FORMAT=JSON SELECT * FROM commandes WHERE client_id = 42\G
ANALYZE SELECT * FROM commandes WHERE client_id = 42;

Colonne	Lecture
`type`	`ALL` : parcours complet (à éviter sur grosse table) ; `ref`, `range`, `const` : index utilisé
`key`	Index choisi (`NULL` : aucun)
`rows`	Nombre estimé de lignes examinées
`Extra`	`Using filesort`, `Using temporary` : coûteux ; `Using index` : excellent

ANALYZE exécute la requête et compare estimation et réalité.

Journal des requêtes lentes

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_slow_query_file = '/var/log/mysql/mariadb-slow.log';

Pour le conserver après redémarrage, dans /etc/mysql/mariadb.conf.d/50-server.cnf :

[mysqld]
slow_query_log = 1
long_query_time = 1

Lire le journal : sudo tail -f /var/log/mysql/mariadb-slow.log (le dossier doit appartenir à l’utilisateur mysql).

Observer l’activité

SHOW FULL PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW ENGINE INNODB STATUS\G
KILL 123;                          -- arrête la requête de l'identifiant 123

Variables essentielles

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

Variable	Rôle
`innodb_buffer_pool_size`	Cache des données et index InnoDB : sur un serveur dédié, environ 50 à 70 % de la mémoire
`max_connections`	Connexions simultanées (chaque connexion consomme de la mémoire)
`innodb_log_file_size`	Taille du journal de transactions
`tmp_table_size`	Taille des tables temporaires en mémoire

[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 100

Puis sudo systemctl restart mariadb. Un serveur qui partage sa mémoire avec d’autres services (application, Redis) demande une valeur plus basse.

Statistiques et maintenance

ANALYZE TABLE commandes;           -- met à jour les statistiques
OPTIMIZE TABLE commandes;          -- reconstruit la table (verrou, durée)
CHECK TABLE commandes;

Bonnes pratiques

Éviter SELECT * : demander les colonnes utiles.
Paginer avec LIMIT (et un tri sur une colonne indexée).
Réduire les allers-retours : insertions par lots.
Mesurer avant d’optimiser : journal des requêtes lentes, EXPLAIN.
Surveiller l’espace disque de /var/lib/mysql.

Dépannage

Symptôme	Piste
`Too many connections`	Fuite de connexions côté application, ou `max_connections` trop bas
Requête soudain lente	`ANALYZE TABLE`, `EXPLAIN`, index manquant
Serveur qui consomme toute la mémoire	`innodb_buffer_pool_size` trop élevé
`Lock wait timeout exceeded`	Transaction longue qui bloque : `SHOW ENGINE INNODB STATUS\G`, terminer ou raccourcir la transaction
Journal lent vide	`slow_query_log` désactivé, ou seuil `long_query_time` trop haut

Ubuntu 24.04 et 26.04 : différences à connaître

Les outils de diagnostic sont les mêmes sur MariaDB 10.11 et 11.8. Les réglages de l’optimiseur évoluent d’une version à l’autre : comparer les plans EXPLAIN après une migration.

Pour aller plus loin

Optimisation des requêtes
Suite : Sauvegarde et restauration