Comprendre l'indexation de base de données pour de meilleures performances

Vos requêtes de base de données prennent des secondes alors qu’elles devraient prendre des millisecondes. Le coupable ? Des index manquants ou mal conçus. L’indexation de base de données fait la différence entre parcourir des millions de lignes et accéder directement aux données dont vous avez besoin—mais une mauvaise approche peut ralentir les écritures et gonfler votre stockage.

Cet article couvre les bases de l’indexation de base de données, des index B-tree et composites aux techniques avancées comme les index couvrants et les plans de requête. Vous apprendrez à indexer les types de données modernes, notamment les champs JSON et texte, et surtout, comment éviter le piège de performance de la sur-indexation dans les bases de données modernes.

Ce que font réellement les index de base de données

Imaginez interroger une table users avec 10 millions de lignes :

SELECT * FROM users WHERE email = 'user@example.com';

Sans index, PostgreSQL ou MySQL doit vérifier chaque ligne—un parcours complet de table. Avec un index sur email, la base de données accède directement à la ligne correspondante en utilisant une structure B-tree, réduisant généralement le temps de recherche de O(n) à O(log n).

Un index est essentiellement une copie triée de colonnes spécifiques avec des pointeurs vers les lignes complètes. Le compromis : des lectures plus rapides au prix d’écritures plus lentes (l’index doit être mis à jour) et d’un stockage supplémentaire (généralement 10 à 20 % de la taille de la table par index).

Index B-tree : le pilier des bases de données

Les index B-tree alimentent la plupart des requêtes de base de données. Ils maintiennent les données dans une structure arborescente équilibrée où chaque nœud contient plusieurs clés triées. Cette conception minimise les E/S disque—crucial puisque l’accès disque est plusieurs ordres de grandeur plus lent que l’accès mémoire.

CREATE INDEX idx_users_created_at ON users(created_at);

Les B-trees excellent pour :

• Les vérifications d’égalité (WHERE status = 'active')
• Les requêtes de plage (WHERE created_at > '2024-01-01')
• Le tri (ORDER BY created_at)
• Les recherches par préfixe (WHERE email LIKE 'john%')

PostgreSQL et MySQL InnoDB utilisent des variantes B+tree où toutes les données résident dans les nœuds feuilles, rendant les parcours de plage plus efficaces. MongoDB utilise des structures B-tree similaires pour ses index, bien que les bases de données documentaires ajoutent de la complexité avec l’indexation de champs imbriqués.

Index composites : l’ordre des colonnes compte

Les index composites couvrent plusieurs colonnes, mais l’ordre des colonnes est critique :

CREATE INDEX idx_events_user_date ON events(user_id, created_at);

Cet index prend en charge :

• WHERE user_id = 123
• WHERE user_id = 123 AND created_at > '2024-01-01'

Mais PAS :

• WHERE created_at > '2024-01-01' (sans user_id)

L’index fonctionne de gauche à droite. Placez d’abord les colonnes utilisées pour l’égalité, puis les plages. Pour un endpoint d’API qui filtre les événements par utilisateur et plage de dates, cet index composite élimine le besoin d’index séparés sur chaque colonne.

Index couvrants et plans de requête

Un index couvrant inclut toutes les colonnes nécessaires à une requête, permettant des analyses uniquement sur index :

-- Exemple PostgreSQL
CREATE INDEX idx_orders_covering 
ON orders(user_id, status, created_at) 
INCLUDE (total_amount);

Pour cette requête :

SELECT total_amount FROM orders 
WHERE user_id = 123 AND status = 'completed';

La base de données ne touche jamais la table—tout provient de l’index. Utilisez EXPLAIN pour vérifier :

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Recherchez “Index Only Scan” dans PostgreSQL ou “Using index” dans MySQL. Les plans de requête révèlent si vos index sont réellement utilisés. Les parcours séquentiels à coût élevé indiquent des index manquants, tandis que les index inutilisés gaspillent des ressources.

Indexation des champs JSON et texte

Les applications modernes stockent des données non structurées nécessitant des index spécialisés :

Indexation JSON (PostgreSQL)

CREATE INDEX idx_metadata_gin ON products 
USING GIN (metadata);

-- Prend en charge les requêtes comme :
SELECT * FROM products 
WHERE metadata @> '{"category": "electronics"}';

Les index GIN gèrent efficacement les requêtes de contenance JSON, bien qu’ils soient plus volumineux et plus lents à mettre à jour que les B-trees. Pour les tables à forte écriture, indexer uniquement des chemins JSON spécifiques peut être plus efficace que d’indexer des documents entiers.

Recherche plein texte

CREATE INDEX idx_posts_text ON posts 
USING GIN (to_tsvector('english', content));

-- Permet :
SELECT * FROM posts 
WHERE to_tsvector('english', content) @@ 'database & indexing';

Les index texte permettent des capacités de recherche similaires à Google mais peuvent doubler les besoins de stockage pour les tables riches en texte.

Éviter la sur-indexation dans les bases de données modernes

Plus d’index n’est pas toujours mieux. Chaque index :

• Ralentit les opérations INSERT/UPDATE/DELETE (doit mettre à jour l’index)
• Augmente le stockage (10 à 20 % par index)
• Ajoute une surcharge de maintenance (fragmentation, reconstructions)

Signes de sur-indexation :

• Tables à forte écriture avec 5 index ou plus
• Index dupliqués (par ex., (a, b) et (a))
• Index inutilisés (vérifiez pg_stat_user_indexes ou sys.dm_db_index_usage_stats)
• Taille d’index dépassant la taille de la table

Bonnes pratiques :

1. Commencez par des index sur les clés primaires et étrangères
2. Ajoutez des index basés sur les journaux de requêtes lentes, pas sur des suppositions
3. Consolidez avec des index composites lorsque possible
4. Supprimez les index inutilisés après surveillance pendant 30 jours ou plus
5. Envisagez des index partiels pour les grandes tables avec requêtes filtrées :

CREATE INDEX idx_orders_recent 
ON orders(created_at) 
WHERE created_at > '2024-01-01';

Sélectivité d’index et performances réelles

La sélectivité d’index—le ratio de valeurs uniques sur le total de lignes—détermine l’efficacité. Un index sur une colonne booléenne (2 valeurs) a une faible sélectivité et aide rarement. Un index sur email (toutes uniques) a une sélectivité parfaite.

Surveillez les métriques de production réelles :

• Temps d’exécution des requêtes avant/après indexation
• Taux de succès d’index
• Impact sur les performances d’écriture
• Croissance du stockage

Des outils comme pgAdmin, MySQL Workbench ou MongoDB Compass fournissent une analyse visuelle des requêtes. Pour la surveillance en production, envisagez DataDog ou New Relic.

Conclusion

Une indexation de base de données efficace nécessite de comprendre vos modèles de requête, pas de suivre des règles rigides. Commencez par des index B-tree sur les colonnes dans les clauses WHERE, ORDER BY et JOIN. Utilisez stratégiquement les index composites, en considérant l’ordre des colonnes. Exploitez les index couvrants pour les requêtes à forte lecture et les index spécialisés pour les données JSON et texte.

Plus important encore, mesurez tout. Utilisez EXPLAIN pour comprendre les plans de requête, surveillez les statistiques d’utilisation des index et suivez l’impact réel sur les performances de lecture et d’écriture. L’objectif n’est pas d’indexer tout—c’est d’indexer exactement ce dont votre application a besoin.

FAQ

Understand every bug

Uncover frustrations, understand bugs and fix slowdowns like never before with OpenReplay — the open-source session replay tool for developers. Self-host it in minutes, and have complete control over your customer data. Check our GitHub repo and join the thousands of developers in our community.