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 :

Recherche en O(log n) au lieu de O(n)

Efficace pour les comparaisons d’egalite et de plage

Supporte le tri (ORDER BY)

Les cles sont stockees dans l’ordre

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 AUTO_INCREMENT 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 idx_prix ON produits(prix);

Index clustered (cle primaire InnoDB) :

Un seul par table

Les lignes sont stockees dans l’ordre de la cle primaire

Acces direct aux donnees

Index secondaire :

Contient les valeurs indexees + la cle primaire

Necessite une deuxieme recherche pour recuperer les autres colonnes

Creation et gestion des index

Types d’index

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

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

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

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

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

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

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

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

Syntaxe de creation

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

    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    INDEX idx_status (status),
    INDEX idx_nom_status (nom, status),
    INDEX idx_date (created_at DESC)
) ENGINE=InnoDB;

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

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

-- 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 idx_cat_prix_stock ON produits(categorie_id, prix, stock);

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

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

-- 3. Filtrer par les trois colonnes
SELECT * FROM produits WHERE categorie_id = 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 client_id = 123
AND statut IN ('validee', 'payee')
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;

-- Index optimal :
CREATE INDEX idx_client_statut_date ON commandes(client_id, statut, created_at 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.categorie_id = c.id
WHERE p.prix > 100
ORDER BY p.prix DESC;

+----+-------------+-------+--------+-------------------+---------+---------+------------------------+------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref                    | rows | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | p     | range  | idx_prix,idx_cat  | idx_prix| 5       | NULL                   |    6 | Using where |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY           | PRIMARY | 4       | boutique.p.categorie_id|    1 | NULL        |
+----+-------------+-------+--------+-------------------+---------+---------+------------------------+------+-------------+

Colonnes importantes

Colonne	Description	Valeurs a surveiller
type	Type d’acces	ALL = full scan (mauvais), index, range, ref, eq_ref (bon), const (optimal)
possible_keys	Index disponibles	NULL = pas d’index utilisable
key	Index utilise	NULL = pas d’index utilise
rows	Estimation des lignes a examiner	Valeur elevee = requete couteuse
Extra	Informations supplementaires	« Using filesort », « Using temporary » = potentiel probleme

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 categorie_id = 1;
-- type: ref

-- 5. eq_ref : Jointure sur cle primaire/unique
EXPLAIN SELECT * FROM commandes c JOIN clients cl ON c.client_id = cl.id;
-- type: eq_ref 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 categorie_id, COUNT(*) FROM produits GROUP BY categorie_id 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 nb_produits,
    AVG(p.prix) AS prix_moyen
FROM categories c
LEFT JOIN produits p ON c.id = p.categorie_id
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 client_id = ?
AND statut = ?
AND created_at > ?;

-- Index optimal :
CREATE INDEX idx_commandes_recherche ON commandes(client_id, statut, created_at);

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

-- Index optimal :
CREATE INDEX idx_produits_cat_prix ON produits(categorie_id, prix DESC);

-- Scenario 3 : Jointures frequentes
-- Les colonnes de jointure doivent etre indexees
CREATE INDEX idx_commande_lignes_cmd ON commande_lignes(commande_id);
CREATE INDEX idx_commande_lignes_prod ON commande_lignes(produit_id);

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 categorie_id = ? ORDER BY prix;

-- Index covering :
CREATE INDEX idx_covering ON produits(categorie_id, prix, id, nom);

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

Index pour les requetes d’agregation

-- Requete :
SELECT
    categorie_id,
    COUNT(*) AS total,
    AVG(prix) AS prix_moyen,
    SUM(stock) AS stock_total
FROM produits
WHERE actif = TRUE
GROUP BY categorie_id;

-- Index optimal :
CREATE INDEX idx_agregation ON produits(actif, categorie_id, prix, stock);

Index partiels avec expressions (MySQL 8.0.13+)

-- Index fonctionnel sur expression
CREATE INDEX idx_year ON commandes((YEAR(created_at)));

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

-- Index sur colonne JSON
CREATE INDEX idx_json_status 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(created_at) = 2024;

-- BON : Condition de plage
SELECT * FROM commandes
WHERE created_at >= '2024-01-01'
AND created_at < '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 categorie_id = 1 OR fournisseur_id = 2;

-- BON : UNION de deux requetes indexees
SELECT * FROM produits WHERE categorie_id = 1
UNION
SELECT * FROM produits WHERE fournisseur_id = 2;

Pattern 2 : Optimiser les jointures

-- S'assurer que les colonnes de jointure sont indexees
SHOW INDEX FROM commandes WHERE Column_name = 'client_id';
SHOW INDEX FROM commande_lignes WHERE Column_name IN ('commande_id', 'produit_id');

-- Jointure optimisee
SELECT
    cl.nom AS client,
    cmd.numero,
    p.nom AS produit
FROM commandes cmd
INNER JOIN clients cl ON cmd.client_id = cl.id      -- client_id indexe
INNER JOIN commande_lignes li ON cmd.id = li.commande_id  -- commande_id indexe
INNER JOIN produits p ON li.produit_id = p.id       -- produit_id indexe
WHERE cl.segment = 'vip'
AND cmd.created_at > '2024-01-01';

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

Pattern 3 : Optimiser le GROUP BY et ORDER BY

-- GROUP BY sur colonnes indexees
CREATE INDEX idx_cmd_client_date ON commandes(client_id, created_at);

SELECT
    client_id,
    DATE(created_at) AS jour,
    COUNT(*) AS nb_commandes,
    SUM(total) AS ca_journalier
FROM commandes
WHERE client_id = 123
GROUP BY client_id, DATE(created_at)
ORDER BY DATE(created_at) 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 created_date DATE GENERATED ALWAYS AS (DATE(created_at)) STORED;
CREATE INDEX idx_created_date ON commandes(created_date);

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, created_at='2024-03-15'
SELECT * FROM produits
WHERE (created_at, id) > ('2024-03-15', 50000)
ORDER BY created_at, id
LIMIT 10;

Index FULLTEXT pour la recherche

-- Creation d'un index FULLTEXT
CREATE FULLTEXT INDEX idx_fulltext_produits 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
    object_schema AS db,
    object_name AS table_name,
    index_name,
    count_read,
    count_write,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'boutique'
ORDER BY count_read DESC;

-- Index inutilises (jamais lus)
SELECT
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_read = 0
AND object_schema NOT IN ('mysql', 'performance_schema');

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 idx_prix, ADD INDEX idx_prix(prix);

Taille des index

-- Taille des tables et index
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'boutique'
ORDER BY total_mb DESC;

-- Details par index
SELECT
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND database_name = 'boutique'
ORDER BY size_mb DESC;

Monitoring des performances

Requetes lentes

-- Activer le log des requetes lentes
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- Requetes > 1 seconde
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Voir les requetes lentes actuelles
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- Utiliser Performance Schema
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS executions,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_time_ms,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'boutique'
ORDER BY SUM_TIMER_WAIT 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
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED AS no_index_count,
    SUM_NO_GOOD_INDEX_USED AS bad_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY COUNT_STAR 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 idx_col1 ON table(col1);
CREATE INDEX idx_col2 ON table(col2);
CREATE INDEX idx_col3 ON table(col3);

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

Erreur 2 : Index sur colonnes a faible cardinalite

-- Mauvais : index sur colonne booleenne seule
CREATE INDEX idx_actif 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 idx_cat_actif ON produits(categorie_id, 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 idx_code (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 idx_telephone 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 > structure_backup.sql

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

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

Script de recreation des index

-- Generer les commandes de recreation d'index
SELECT
    CONCAT('CREATE ',
        IF(non_unique = 0, 'UNIQUE ', ''),
        'INDEX ', index_name, ' ON ', table_name,
        '(', GROUP_CONCAT(column_name ORDER BY seq_in_index), ');'
    ) AS create_index_statement
FROM information_schema.statistics
WHERE table_schema = 'boutique'
AND index_name != 'PRIMARY'
GROUP BY table_name, index_name, non_unique
ORDER BY table_name, index_name;

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

Analysez avant d'indexer : Utilisez EXPLAIN pour comprendre les requetes

Index composites : Respectez l'ordre des colonnes (egalite, plage, tri)

Moins c'est mieux : Chaque index a un cout en ecriture

Maintenez vos index : ANALYZE TABLE regulierement

Surveillez l'utilisation : Supprimez les index inutilises

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/eq_ref (pas ALL)
[ ] Extra ne montre pas "Using filesort" inutilement

Prochaines etapes

Dans les articles suivants, vous apprendrez :

PostgreSQL et ses fonctionnalites avancees (JSON, arrays)

Partitioning et sharding pour les grandes echelles

Replication et haute disponibilite

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