Indexes et optimisation de requetes SQL

Introduction

Les index sont le levier le plus puissant pour ameliorer les performances de vos requetes SQL. Un index bien concu peut transformer une requete de plusieurs secondes en quelques millisecondes. A l’inverse, des index mal geres peuvent degrader significativement les performances d’ecriture.

Dans cet article, vous apprendrez :

• Comment fonctionnent les index en interne
• Les differents types d’index et leurs cas d’usage
• Comment analyser et optimiser vos requetes avec EXPLAIN
• Les strategies d’indexation pour differents patterns d’acces
• Le monitoring et la maintenance des index
• Les erreurs courantes a eviter

Comment fonctionnent les index

Analogie avec un index de livre

Un index de base de donnees fonctionne comme l’index a la fin d’un livre : au lieu de parcourir toutes les pages pour trouver un sujet, vous consultez l’index qui vous indique directement les pages concernees.

Sans index, MySQL doit effectuer un full table scan : parcourir chaque ligne de la table pour trouver les correspondances. Avec un index, il peut localiser directement les lignes pertinentes.

Structure B-Tree

La plupart des index MySQL utilisent une structure B-Tree (arbre equilibre) :

                    [50]
                   /    
            [25,35]      [75,90]
           /   |       /   |   
        [10] [30] [40] [60] [80] [100]
         |    |    |    |    |    |
        Ptr  Ptr  Ptr  Ptr  Ptr  Ptr
         |    |    |    |    |    |
      Donnees ou pointeurs vers les lignes

Caracteristiques du B-Tree :

Index clustered vs non-clustered

-- Index CLUSTERED (cle primaire InnoDB)
-- Les donnees de la table sont physiquement ordonnees selon cet index
CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,  -- Index clustered
    nom VARCHAR(255),
    prix DECIMAL(10,2)
) ENGINE=InnoDB;

-- Index NON-CLUSTERED (index secondaire)
-- Contient une copie des colonnes indexees + pointeur vers la cle primaire
CREATE INDEX idxprix ON produits(prix);

Index clustered (cle primaire InnoDB) :

Index secondaire :

Creation et gestion des index

Types d’index

-- 1. Index simple (B-Tree par defaut)
CREATE INDEX idxnom ON produits(nom);

-- 2. Index unique
CREATE UNIQUE INDEX idxemail ON clients(email);
-- Ou via contrainte
ALTER TABLE clients ADD CONSTRAINT ukemail UNIQUE (email);

-- 3. Index composite (multi-colonnes)
CREATE INDEX idxcategorieprix ON produits(categorieid, prix);

-- 4. Index avec ordre de tri (MySQL 8.0+)
CREATE INDEX idxdatedesc ON commandes(createdat DESC);

-- 5. Index prefixe (pour les colonnes longues)
CREATE INDEX idxdescription ON produits(description(100));

-- 6. Index FULLTEXT (recherche textuelle)
CREATE FULLTEXT INDEX idxfulltextnom ON produits(nom, description);

-- 7. Index SPATIAL (donnees geographiques)
CREATE SPATIAL INDEX idxlocation ON magasins(coordonnees);

-- 8. Index invisible (MySQL 8.0+) - pour tests
CREATE INDEX idxtest ON produits(stock) INVISIBLE;

Syntaxe de creation

-- Lors de la creation de table
CREATE TABLE exemple (
    id INT UNSIGNED AUTOINCREMENT,
    email VARCHAR(255) NOT NULL,
    nom VARCHAR(100),
    status ENUM('actif', 'inactif'),
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY ukemail (email),
    INDEX idxstatus (status),
    INDEX idxnomstatus (nom, status),
    INDEX idxdate (createdat DESC)
) ENGINE=InnoDB;

-- Ajouter un index a une table existante
ALTER TABLE produits ADD INDEX idxstock (stock);
-- ou
CREATE INDEX idxstock ON produits(stock);

-- Supprimer un index
DROP INDEX idxstock ON produits;
-- ou
ALTER TABLE produits DROP INDEX idxstock;

-- Voir les index d'une table
SHOW INDEX FROM produits;
SHOW CREATE TABLE produits;

Index composites : ordre des colonnes

L’ordre des colonnes dans un index composite est crucial :

-- Index composite
CREATE INDEX idxcatprixstock ON produits(categorieid, prix, stock);

-- Cet index peut etre utilise pour :
-- 1. Filtrer par categorieid seul
SELECT  FROM produits WHERE categorieid = 1;

-- 2. Filtrer par categorieid et prix
SELECT  FROM produits WHERE categorieid = 1 AND prix > 100;

-- 3. Filtrer par les trois colonnes
SELECT  FROM produits WHERE categorieid = 1 AND prix > 100 AND stock > 0;

-- Cet index NE PEUT PAS etre utilise efficacement pour :
-- Filtrer par prix seul (la premiere colonne n'est pas utilisee)
SELECT  FROM produits WHERE prix > 100;  -- Full table scan

-- Filtrer par stock seul
SELECT  FROM produits WHERE stock > 0;  -- Full table scan

Regle du prefixe gauche : Un index composite peut etre utilise pour les colonnes de gauche, mais pas pour les colonnes intermediaires ou de droite seules.

Strategie de choix des colonnes

-- Regle generale pour l'ordre des colonnes :
-- 1. Colonnes d'egalite d'abord (WHERE col = valeur)
-- 2. Colonnes de plage ensuite (WHERE col > valeur)
-- 3. Colonnes de tri a la fin (ORDER BY)

-- Exemple optimal pour la requete suivante :
SELECT  FROM commandes
WHERE clientid = 123
AND statut IN ('validee', 'payee')
AND createdat > '2024-01-01'
ORDER BY createdat DESC
LIMIT 10;

-- Index optimal :
CREATE INDEX idxclientstatutdate ON commandes(clientid, statut, createdat DESC);

EXPLAIN : Analyser les requetes

Syntaxe de base

-- EXPLAIN simple
EXPLAIN SELECT  FROM produits WHERE prix > 100;

-- EXPLAIN avec format JSON (plus detaille)
EXPLAIN FORMAT=JSON SELECT  FROM produits WHERE prix > 100;

-- EXPLAIN ANALYZE (MySQL 8.0.18+) - execute reellement la requete
EXPLAIN ANALYZE SELECT  FROM produits WHERE prix > 100;

Lecture du resultat EXPLAIN

EXPLAIN SELECT
    p.nom,
    c.nom AS categorie
FROM produits p
INNER JOIN categories c ON p.categorieid = c.id
WHERE p.prix > 100
ORDER BY p.prix DESC;

+----+-------------+-------+--------+-------------------+---------+---------+------------------------+------+-------------+
| id | selecttype | table | type   | possiblekeys     | key     | keylen | ref                    | rows | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | p     | range  | idxprix,idxcat  | idxprix| 5       | NULL                   |    6 | Using where |
|  1 | SIMPLE      | c     | eqref | PRIMARY           | PRIMARY | 4       | boutique.p.categorieid|    1 | NULL        |
+----+-------------+-------+--------+-------------------+---------+---------+------------------------+------+-------------+

Colonnes importantes

Types d’acces (du pire au meilleur)

-- 1. ALL : Full table scan (a eviter)
EXPLAIN SELECT  FROM produits WHERE description LIKE '%test%';
-- type: ALL, rows: 1000

-- 2. index : Full index scan (parcourt tout l'index)
EXPLAIN SELECT id FROM produits;
-- type: index

-- 3. range : Scan de plage sur index
EXPLAIN SELECT  FROM produits WHERE prix BETWEEN 50 AND 100;
-- type: range

-- 4. ref : Recherche sur index non-unique
EXPLAIN SELECT  FROM produits WHERE categorieid = 1;
-- type: ref

-- 5. eqref : Jointure sur cle primaire/unique
EXPLAIN SELECT  FROM commandes c JOIN clients cl ON c.clientid = cl.id;
-- type: eqref pour clients

-- 6. const : Recherche sur cle primaire avec valeur constante
EXPLAIN SELECT  FROM produits WHERE id = 1;
-- type: const

Indicateurs de problemes dans Extra

-- Using filesort : Tri non couvert par index
EXPLAIN SELECT  FROM produits ORDER BY nom;
-- Extra: Using filesort
-- Solution : Ajouter un index sur nom

-- Using temporary : Table temporaire creee
EXPLAIN SELECT categorieid, COUNT() FROM produits GROUP BY categorieid ORDER BY COUNT() DESC;
-- Extra: Using temporary; Using filesort
-- Solution : Optimiser ou accepter pour les petits volumes

-- Using where : Filtrage supplementaire apres lecture index
EXPLAIN SELECT  FROM produits WHERE prix > 100 AND actif = TRUE;
-- Extra: Using where
-- Solution : Index composite incluant actif

-- Using index (positif) : Requete couverte par l'index
EXPLAIN SELECT id, prix FROM produits WHERE prix > 100;
-- Extra: Using index (si index sur prix couvre id)

EXPLAIN ANALYZE

-- EXPLAIN ANALYZE execute la requete et montre les temps reels
EXPLAIN ANALYZE
SELECT
    c.nom AS categorie,
    COUNT() AS nbproduits,
    AVG(p.prix) AS prixmoyen
FROM categories c
LEFT JOIN produits p ON c.id = p.categorieid
GROUP BY c.id;

-- Resultat inclut :
-- - actual time : temps reel d'execution
-- - rows : nombre reel de lignes (vs estimation)
-- - loops : nombre d'iterations

Strategies d’indexation

Index pour les requetes de lecture

-- Scenario 1 : Recherche par criteres multiples
-- Requete frequente :
SELECT  FROM commandes
WHERE clientid = ?
AND statut = ?
AND createdat > ?;

-- Index optimal :
CREATE INDEX idxcommandesrecherche ON commandes(clientid, statut, createdat);

-- Scenario 2 : Tri et pagination
-- Requete frequente :
SELECT  FROM produits
WHERE categorieid = ?
ORDER BY prix DESC
LIMIT 20;

-- Index optimal :
CREATE INDEX idxproduitscatprix ON produits(categorieid, prix DESC);

-- Scenario 3 : Jointures frequentes
-- Les colonnes de jointure doivent etre indexees
CREATE INDEX idxcommandelignescmd ON commandelignes(commandeid);
CREATE INDEX idxcommandelignesprod ON commandelignes(produitid);

Index covering (couvrant)

Un index couvrant contient toutes les colonnes necessaires a la requete, evitant l’acces a la table.

-- Requete frequente :
SELECT id, nom, prix FROM produits WHERE categorieid = ? ORDER BY prix;

-- Index covering :
CREATE INDEX idxcovering ON produits(categorieid, prix, id, nom);

-- Verification :
EXPLAIN SELECT id, nom, prix FROM produits WHERE categorieid = 1 ORDER BY prix;
-- Extra: Using index (confirme que l'index couvre la requete)

Index pour les requetes d’agregation

-- Requete :
SELECT
    categorieid,
    COUNT() AS total,
    AVG(prix) AS prixmoyen,
    SUM(stock) AS stocktotal
FROM produits
WHERE actif = TRUE
GROUP BY categorieid;

-- Index optimal :
CREATE INDEX idxagregation ON produits(actif, categorieid, prix, stock);

Index partiels avec expressions (MySQL 8.0.13+)

-- Index fonctionnel sur expression
CREATE INDEX idxyear ON commandes((YEAR(createdat)));

-- Utilisation :
SELECT  FROM commandes WHERE YEAR(createdat) = 2024;

-- Index sur colonne JSON
CREATE INDEX idxjsonstatus ON orders((CAST(data->>'$.status' AS CHAR(20))));

Optimisation des requetes

Pattern 1 : Eviter les full table scans

-- MAUVAIS : Fonction sur colonne indexee (desactive l'index)
SELECT  FROM commandes WHERE YEAR(createdat) = 2024;

-- BON : Condition de plage
SELECT  FROM commandes
WHERE createdat >= '2024-01-01'
AND createdat < '2025-01-01';

-- MAUVAIS : LIKE avec wildcard au debut
SELECT  FROM produits WHERE nom LIKE '%phone%';

-- BON : LIKE avec prefixe
SELECT  FROM produits WHERE nom LIKE 'phone%';

-- BON : Utiliser FULLTEXT pour la recherche de texte
SELECT  FROM produits
WHERE MATCH(nom, description) AGAINST('phone' IN NATURAL LANGUAGE MODE);

-- MAUVAIS : OR sur colonnes differentes (souvent = full scan)
SELECT  FROM produits WHERE categorieid = 1 OR fournisseurid = 2;

-- BON : UNION de deux requetes indexees
SELECT  FROM produits WHERE categorieid = 1
UNION
SELECT  FROM produits WHERE fournisseurid = 2;

Pattern 2 : Optimiser les jointures

-- S'assurer que les colonnes de jointure sont indexees
SHOW INDEX FROM commandes WHERE Columnname = 'clientid';
SHOW INDEX FROM commandelignes WHERE Columnname IN ('commandeid', 'produitid');

-- Jointure optimisee
SELECT
    cl.nom AS client,
    cmd.numero,
    p.nom AS produit
FROM commandes cmd
INNER JOIN clients cl ON cmd.clientid = cl.id      -- clientid indexe
INNER JOIN commandelignes li ON cmd.id = li.commandeid  -- commandeid indexe
INNER JOIN produits p ON li.produitid = p.id       -- produitid indexe
WHERE cl.segment = 'vip'
AND cmd.createdat > '2024-01-01';

-- Ajouter un index composite si necessaire
CREATE INDEX idxclientssegment ON clients(segment);

Pattern 3 : Optimiser le GROUP BY et ORDER BY

-- GROUP BY sur colonnes indexees
CREATE INDEX idxcmdclientdate ON commandes(clientid, createdat);

SELECT
    clientid,
    DATE(createdat) AS jour,
    COUNT() AS nbcommandes,
    SUM(total) AS cajournalier
FROM commandes
WHERE clientid = 123
GROUP BY clientid, DATE(createdat)
ORDER BY DATE(createdat) DESC;

-- Attention : GROUP BY sur expression peut empecher l'utilisation de l'index
-- Preferer stocker la valeur calculee si les requetes sont frequentes
ALTER TABLE commandes ADD COLUMN createddate DATE GENERATED ALWAYS AS (DATE(createdat)) STORED;
CREATE INDEX idxcreateddate ON commandes(createddate);

Pattern 4 : Pagination efficace

-- MAUVAIS : OFFSET eleve (doit parcourir les lignes precedentes)
SELECT  FROM produits ORDER BY id LIMIT 10 OFFSET 100000;

-- BON : Pagination par curseur (keyset pagination)
SELECT  FROM produits
WHERE id > 100000
ORDER BY id
LIMIT 10;

-- BON : Avec criteres de tri complexes
-- Derniere valeur vue : id=50000, createdat='2024-03-15'
SELECT  FROM produits
WHERE (createdat, id) > ('2024-03-15', 50000)
ORDER BY createdat, id
LIMIT 10;

Index FULLTEXT pour la recherche

-- Creation d'un index FULLTEXT
CREATE FULLTEXT INDEX idxfulltextproduits ON produits(nom, description);

-- Recherche en mode naturel
SELECT ,
    MATCH(nom, description) AGAINST('smartphone samsung') AS score
FROM produits
WHERE MATCH(nom, description) AGAINST('smartphone samsung')
ORDER BY score DESC;

-- Recherche booleenne (plus de controle)
SELECT  FROM produits
WHERE MATCH(nom, description) AGAINST('+smartphone -apple' IN BOOLEAN MODE);

-- Operateurs booleens :
-- + : Le mot doit etre present
-- - : Le mot doit etre absent
--  : Wildcard (prefix)
-- "" : Phrase exacte
-- > < : Modifier le poids

-- Recherche avec expansion de requete
SELECT  FROM produits
WHERE MATCH(nom, description)
AGAINST('telephone mobile' WITH QUERY EXPANSION);

Maintenance des index

Statistiques d’utilisation

-- Voir l'utilisation des index (MySQL 8.0+)
SELECT
    objectschema AS db,
    objectname AS tablename,
    indexname,
    countread,
    countwrite,
    countfetch
FROM performanceschema.tableiowaitssummarybyindexusage
WHERE objectschema = 'boutique'
ORDER BY countread DESC;

-- Index inutilises (jamais lus)
SELECT
    objectschema,
    objectname,
    indexname
FROM performanceschema.tableiowaitssummarybyindexusage
WHERE indexname IS NOT NULL
AND countread = 0
AND objectschema NOT IN ('mysql', 'performanceschema');

Analyse et optimisation des tables

-- Mettre a jour les statistiques d'index
ANALYZE TABLE produits;

-- Verifier les tables
CHECK TABLE produits;

-- Optimiser la table (reorganise les donnees et reconstruit les index)
OPTIMIZE TABLE produits;

-- Reconstruire un index specifique
ALTER TABLE produits DROP INDEX idxprix, ADD INDEX idxprix(prix);

Taille des index

-- Taille des tables et index
SELECT
    tablename,
    ROUND(datalength / 1024 / 1024, 2) AS datamb,
    ROUND(indexlength / 1024 / 1024, 2) AS indexmb,
    ROUND((datalength + indexlength) / 1024 / 1024, 2) AS totalmb
FROM informationschema.tables
WHERE tableschema = 'boutique'
ORDER BY totalmb DESC;

-- Details par index
SELECT
    tablename,
    indexname,
    ROUND(statvalue  @@innodbpagesize / 1024 / 1024, 2) AS sizemb
FROM mysql.innodbindexstats
WHERE statname = 'size'
AND databasename = 'boutique'
ORDER BY sizemb DESC;

Monitoring des performances

Requetes lentes

-- Activer le log des requetes lentes
SET GLOBAL slowquerylog = 1;
SET GLOBAL longquerytime = 1;  -- Requetes > 1 seconde
SET GLOBAL slowquerylogfile = '/var/log/mysql/slow.log';

-- Voir les requetes lentes actuelles
SELECT  FROM mysql.slowlog ORDER BY starttime DESC LIMIT 10;

-- Utiliser Performance Schema
SELECT
    DIGESTTEXT,
    COUNTSTAR AS executions,
    ROUND(AVGTIMERWAIT / 1000000000, 2) AS avgtimems,
    ROUND(SUMTIMERWAIT / 1000000000, 2) AS totaltimems,
    SUMROWSEXAMINED AS rowsexamined,
    SUMROWSSENT AS rowssent
FROM performanceschema.eventsstatementssummarybydigest
WHERE SCHEMANAME = 'boutique'
ORDER BY SUMTIMERWAIT DESC
LIMIT 10;

Index Advisor (MySQL Enterprise)

-- Pour MySQL Community, utiliser pt-index-usage de Percona Toolkit
-- bash: pt-index-usage /var/log/mysql/slow.log --host localhost

-- Ou analyser manuellement les requetes problematiques
-- Identifier les requetes avec full table scan
SELECT
    DIGESTTEXT,
    COUNTSTAR,
    SUMNOINDEXUSED AS noindexcount,
    SUMNOGOODINDEXUSED AS badindexcount
FROM performanceschema.eventsstatementssummarybydigest
WHERE SUMNOINDEXUSED > 0 OR SUMNOGOODINDEXUSED > 0
ORDER BY COUNTSTAR DESC
LIMIT 20;

Erreurs courantes et solutions

Erreur 1 : Trop d’index

-- Chaque index :
-- - Ralentit les INSERT/UPDATE/DELETE
-- - Consomme de l'espace disque
-- - Necessite de la maintenance

-- Mauvaise pratique : un index par colonne
CREATE INDEX idxcol1 ON table(col1);
CREATE INDEX idxcol2 ON table(col2);
CREATE INDEX idxcol3 ON table(col3);

-- Bonne pratique : index composites strategiques
CREATE INDEX idxcomposite ON table(col1, col2, col3);

Erreur 2 : Index sur colonnes a faible cardinalite

-- Mauvais : index sur colonne booleenne seule
CREATE INDEX idxactif ON produits(actif);
-- Selectivite : 50% des lignes ont TRUE, 50% FALSE
-- L'optimiseur preferera souvent un full scan

-- Bon : combiner avec une colonne plus selective
CREATE INDEX idxcatactif ON produits(categorieid, actif);

Erreur 3 : Index non utilises a cause de conversions implicites

-- Table avec colonne VARCHAR
CREATE TABLE logs (
    id INT PRIMARY KEY,
    code VARCHAR(10),
    INDEX idxcode (code)
);

-- MAUVAIS : comparaison avec un entier = conversion implicite
SELECT  FROM logs WHERE code = 12345;  -- Index non utilise!

-- BON : comparaison avec le bon type
SELECT  FROM logs WHERE code = '12345';  -- Index utilise

Erreur 4 : Colonnes nullable dans les index

-- Les valeurs NULL peuvent affecter l'utilisation des index
CREATE INDEX idxtelephone ON clients(telephone);

-- Cette requete peut utiliser l'index
SELECT  FROM clients WHERE telephone = '0612345678';

-- Cette requete peut ne pas utiliser l'index efficacement
SELECT * FROM clients WHERE telephone IS NULL;

-- Solution : valeur par defaut au lieu de NULL si possible
ALTER TABLE clients MODIFY telephone VARCHAR(20) NOT NULL DEFAULT '';

Sauvegarde et restauration des index

# Sauvegarder la structure uniquement (inclut les index)
mysqldump -u root -p --no-data boutique > structurebackup.sql

# Voir les definitions d'index dans la sauvegarde
grep -E "CREATE INDEX|ADD INDEX|UNIQUE KEY|PRIMARY KEY|FULLTEXT" structurebackup.sql

# Restaurer
mysql -u root -p boutique < structurebackup.sql

Script de recreation des index

-- Generer les commandes de recreation d'index
SELECT
    CONCAT('CREATE ',
        IF(nonunique = 0, 'UNIQUE ', ''),
        'INDEX ', indexname, ' ON ', tablename,
        '(', GROUPCONCAT(columnname ORDER BY seqinindex), ');'
    ) AS createindexstatement
FROM informationschema.statistics
WHERE tableschema = 'boutique'
AND indexname != 'PRIMARY'
GROUP BY tablename, indexname, nonunique
ORDER BY tablename, indexname;

Conclusion

L’optimisation par les index est un art qui demande de la pratique et une bonne comprehension de vos patterns d’acces.

Points cles a retenir

Checklist d’optimisation

[ ] Toutes les colonnes de WHERE sont indexees
[ ] Les colonnes de JOIN sont indexees des deux cotes
[ ] Les colonnes de ORDER BY sont dans l'index
[ ] Pas de fonction sur colonne indexee dans WHERE
[ ] Index composites dans le bon ordre
[ ] Pas d'index dupliques
[ ] EXPLAIN montre type = ref/range/eqref (pas ALL)
[ ] Extra ne montre pas "Using filesort" inutilement

Prochaines etapes

Dans les articles suivants, vous apprendrez :

Une bonne strategie d’indexation est la fondation d’une application performante. Prenez le temps de bien l’implementer des le debut du projet.