MySQL : index et performances

Comprendre pourquoi une requête est lente et la corriger, depuis la console. Les exemples utilisent les tables clients et commandes.

Les index

Un index accélère la recherche, au prix d’un peu d’espace et de lenteur à l’écriture. Sans index, MySQL parcourt toute la table.

CREATE INDEX idx_clients_ville ON clients (ville);
CREATE INDEX idx_commandes_statut_date ON commandes (statut, passee_le);   -- index composé
CREATE UNIQUE INDEX uq_clients_nom_ville ON clients (nom, ville);
SHOW INDEX FROM commandes;
DROP INDEX idx_clients_ville ON clients;

Les colonnes de WHERE, de jointure (JOIN ... ON) et de ORDER BY fréquents sont de bonnes candidates.
Dans un index composé, l’ordre des colonnes compte : l’index (statut, passee_le) sert aux filtres sur statut, ou sur statut et passee_le, mais pas sur passee_le seul.
Les clés primaires et uniques sont indexées automatiquement ; les clés étrangères aussi (InnoDB crée l’index nécessaire) : inutile d’indexer à nouveau client_id de commandes, et un index utilisé par une clé étrangère ne peut pas être supprimé (erreur 1553).
Éviter d’indexer toutes les colonnes : chaque index ralentit les écritures.

`EXPLAIN` : lire le plan d’exécution

EXPLAIN SELECT * FROM commandes WHERE statut = 'payee';
EXPLAIN ANALYZE SELECT * FROM commandes WHERE statut = 'payee';   -- exécute la requête et mesure

Colonnes principales de EXPLAIN :

Colonne	À regarder
`type`	`ALL` = parcours complet de la table (à éviter sur une grande table) ; `ref`, `range`, `const` = utilisation d’un index
`key`	Index réellement utilisé (`NULL` = aucun)
`rows`	Nombre de lignes estimées à examiner
`Extra`	`Using filesort` ou `Using temporary` signalent un tri ou une table temporaire coûteux

EXPLAIN ANALYZE affiche aussi les durées et le nombre réel de lignes.

Journal des requêtes lentes

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;               -- secondes
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';

sudo tail -f /var/lib/mysql/$(hostname)-slow.log
sudo mysqldumpslow -s t -t 10 /var/lib/mysql/$(hostname)-slow.log   # 10 requêtes les plus longues

Pour rendre ces réglages permanents, les ajouter dans /etc/mysql/mysql.conf.d/mysqld.cnf, ou utiliser SET PERSIST.

Voir ce qui s’exécute

SHOW FULL PROCESSLIST;
SELECT id, user, db, time, state, LEFT(info, 80) AS requete
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;
KILL QUERY 42;        -- interrompt la requête 42
KILL 42;              -- coupe la connexion 42

Configuration

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SET PERSIST max_connections = 300;      -- appliqué et conservé après redémarrage

Paramètre	Rôle
`innodb_buffer_pool_size`	Mémoire cache des données et index InnoDB : 50 à 70 % de la mémoire sur un serveur dédié
`max_connections`	Nombre maximal de connexions simultanées
`slow_query_log`, `long_query_time`	Journal des requêtes lentes

Maintenance

ANALYZE TABLE commandes;        -- met à jour les statistiques utilisées par l'optimiseur
OPTIMIZE TABLE commandes;       -- reconstruit la table (libère l'espace après de grosses suppressions)
CHECK TABLE commandes;          -- vérifie l'intégrité
SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 1) AS mo
FROM information_schema.tables
WHERE table_schema = 'boutique'
ORDER BY (data_length + index_length) DESC;

Réflexes pour une requête lente

EXPLAIN pour voir si un index est utilisé.
Ajouter l’index manquant sur les colonnes de filtre et de jointure.
Ne sélectionner que les colonnes nécessaires (pas de SELECT * sur de grosses tables).
Limiter les résultats (LIMIT) et paginer.
Éviter les fonctions sur une colonne indexée dans le WHERE (WHERE YEAR(passee_le) = 2026 empêche l’index ; préférer passee_le >= '2026-01-01' AND passee_le < '2027-01-01').
Mesurer à nouveau avec EXPLAIN ANALYZE.

Pour aller plus loin

Manuel : Optimization
Sauvegarde : Sauvegarde et restauration