Index SQL : création et optimisation pour la performance

Les index SQL sont les clés de voûte de la performance des bases de données. Un index bien conçu peut transformer une requête lente de plusieurs secondes en une exécution quasi-instantanée, tandis qu'un mauvais indexage peut dégrader les performances d'écriture et consommer inutilement de l'espace disque.

Dans cet article, nous explorerons les différents types d'index, leurs mécanismes internes, et surtout comment les créer et les optimiser efficacement. Que vous prépariez un entretien technique ou que vous cherchiez à améliorer les performances de vos applications en production, cette connaissance approfondie des index vous sera indispensable.

Les index ne sont pas qu'une simple optimisation : ils constituent un élément fondamental de l'architecture des bases de données modernes. Comprendre leur fonctionnement vous permettra de prendre des décisions éclairées sur votre stratégie d'indexage.

Les types d'index SQL essentiels

Index B-tree : la référence universelle

L'index B-tree (Balanced Tree) constitue le type d'index le plus répandu dans les systèmes de gestion de bases de données. Sa structure arborescente équilibrée garantit des performances de recherche logarithmiques O(log n), même sur des tables de plusieurs millions d'enregistrements.

Le principe du B-tree repose sur une hiérarchie de nœuds : les nœuds internes contiennent des clés de routage, tandis que les feuilles stockent les pointeurs vers les lignes de données. Cette organisation permet d'effectuer des recherches, des insertions et des suppressions avec une complexité temporelle prévisible.

PostgreSQL, MySQL et SQL Server utilisent les B-tree comme structure d'index par défaut. Oracle Database emploie une variante appelée B+ tree, où seules les feuilles contiennent les données, optimisant ainsi les parcours séquentiels.

Index clustered vs non-clustered

Un index clustered détermine l'ordre physique de stockage des données sur le disque. Chaque table ne peut avoir qu'un seul index clustered, généralement défini sur la clé primaire. Les pages de données sont organisées selon l'ordre de cet index, ce qui optimise considérablement les requêtes de plage.

Les index non-clustered, en revanche, pointent vers les lignes de données sans influencer leur organisation physique. Une table peut avoir plusieurs index non-clustered, chacun optimisant différents types de requêtes.

SQL Server illustre parfaitement cette distinction : l'index clustered sur la clé primaire organise physiquement les données, tandis que les index non-clustered créent des structures séparées pointant vers ces données.

Création d'index : syntaxe et bonnes pratiques

Syntaxe de base CREATE INDEX

La création d'un index suit une syntaxe standardisée across les différents SGBD, avec quelques variations spécifiques :

-- Syntaxe générale
CREATE INDEX idx_nom_colonne ON table_name (colonne);

-- Index composite
CREATE INDEX idx_composite ON commandes (client_id, date_commande);

-- Index unique
CREATE UNIQUE INDEX idx_unique_email ON utilisateurs (email);

-- Index partiel (PostgreSQL)
CREATE INDEX idx_commandes_actives ON commandes (client_id) 
WHERE statut = 'actif';

La convention de nommage des index doit être cohérente et descriptive. Le préfixe "idx_" suivi du nom de la table et des colonnes concernées facilite la maintenance et la compréhension du schéma.

Pour les index composites, l'ordre des colonnes dans la définition est crucial. La règle générale consiste à placer les colonnes les plus sélectives en premier, suivies de celles utilisées dans les clauses ORDER BY.

Index composites et ordre des colonnes

L'efficacité d'un index composite dépend directement de l'ordre de ses colonnes. Un index sur (A, B, C) peut servir les requêtes filtrant sur A, sur (A, B), ou sur (A, B, C), mais pas efficacement sur B seul ou C seul.

Cette propriété découle de la structure B-tree : les entrées sont triées d'abord par la première colonne, puis par la seconde en cas d'égalité, et ainsi de suite. Une requête cherchant uniquement la colonne B doit parcourir toute la structure d'index.

L'ordre optimal place généralement les colonnes les plus discriminantes en premier. Une colonne avec une cardinalité élevée (beaucoup de valeurs distinctes) sera plus sélective qu'une colonne binaire ou avec peu de valeurs distinctes.

-- Bon ordre pour un index composite
CREATE INDEX idx_ventes_optimise ON ventes (
    date_vente,      -- Haute cardinalité, souvent filtrée
    produit_id,      -- Cardinalité moyenne
    statut           -- Faible cardinalité, mais souvent dans WHERE
);

Types d'index avancés selon les SGBD

Index bitmap et fonctionnels

Oracle Database propose des index bitmap particulièrement efficaces pour les colonnes à faible cardinalité. Ces index stockent des bitmaps pour chaque valeur distincte, permettant des opérations booléennes très rapides entre différents prédicats.

Les index fonctionnels (ou basés sur des expressions) permettent d'indexer le résultat d'une fonction plutôt que la valeur brute de la colonne. PostgreSQL et Oracle supportent cette fonctionnalité :

-- Index fonctionnel PostgreSQL
CREATE INDEX idx_email_lower ON utilisateurs (LOWER(email));

-- Index sur expression Oracle
CREATE INDEX idx_total_calcule ON commandes (quantite * prix_unitaire);

Cette approche évite les calculs répétitifs dans les requêtes et permet d'optimiser des prédicats complexes.

Index partiels et conditionnels

PostgreSQL et SQL Server permettent de créer des index partiels qui ne couvrent qu'un sous-ensemble des lignes répondant à une condition WHERE. Cette technique réduit la taille de l'index et améliore les performances pour des requêtes spécifiques.

-- Index partiel sur les commandes actives uniquement
CREATE INDEX idx_commandes_actives 
ON commandes (client_id, date_commande)
WHERE statut IN ('en_cours', 'validée');

L'index partiel est particulièrement utile quand une grande partie des données n'est jamais interrogée (archives, données supprimées logiquement, etc.).

Analyse des performances avec EXPLAIN PLAN

Interpréter les plans d'exécution

EXPLAIN PLAN révèle comment l'optimiseur de requêtes utilise (ou n'utilise pas) vos index. Chaque SGBD a sa syntaxe, mais les concepts restent similaires :

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM commandes WHERE client_id = 123;

-- SQL Server
SET STATISTICS IO ON;
SELECT * FROM commandes WHERE client_id = 123;

-- Oracle
EXPLAIN PLAN FOR SELECT * FROM commandes WHERE client_id = 123;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Les métriques clés à surveiller incluent le coût estimé, le nombre de lignes lues, les opérations d'E/S disque, et surtout la présence d'Index Seek versus Table Scan.

Un Index Seek indique que l'optimiseur utilise efficacement votre index pour localiser directement les données pertinentes. Un Table Scan (ou Index Scan complet) suggère que l'index n'est pas optimal pour cette requête.

Identifier les index manquants ou inutilisés

Les SGBD modernes fournissent des vues système pour identifier les index manquants ou sous-utilisés :

-- SQL Server - Index manquants
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) 
    + '_' + LEFT(PARSENAME(mid.statement, 1), 20) + '] ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle;

PostgreSQL offre l'extension pg_stat_statements pour analyser l'utilisation des index et identifier les requêtes coûteuses qui bénéficieraient d'un indexage supplémentaire.

Optimisation et maintenance des index

Stratégies de maintenance proactive

Les index se fragmentent au fil du temps avec les insertions, modifications et suppressions. Cette fragmentation dégrade les performances de lecture et augmente l'espace disque consommé. Une maintenance régulière est donc essentielle.

SQL Server distingue la fragmentation interne (pages partiellement remplies) de la fragmentation logique (pages non contiguës). Un REORGANIZE suffit pour une fragmentation modérée (5-30%), tandis qu'un REBUILD complet s'impose au-delà de 30%.

-- Analyser la fragmentation SQL Server
SELECT 
    object_name(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5;

PostgreSQL utilise VACUUM et REINDEX pour maintenir les index. Oracle recommande de surveiller les statistiques via DBMS_STATS et de reconstruire les index très fragmentés.

Optimisation des requêtes et couverture d'index

Un index de couverture (covering index) inclut toutes les colonnes nécessaires à une requête, éliminant ainsi le besoin d'accéder à la table principale. Cette technique, particulièrement efficace sur SQL Server avec la clause INCLUDE, peut transformer radicalement les performances.

-- Index de couverture SQL Server
CREATE INDEX idx_commandes_covering 
ON commandes (client_id, date_commande)
INCLUDE (montant_total, statut, produit_id);

Les colonnes dans INCLUDE ne participent pas à la structure B-tree mais sont stockées dans les feuilles, évitant les key lookups coûteux.

Pour les requêtes analytiques complexes, considérez les index columnaires (SQL Server, Oracle) qui comprennent et stockent les données par colonne plutôt que par ligne, optimisant ainsi les agrégations sur de gros volumes.

Comparaison des performances par type d'index