Si vous me donnez une requête lente et une heure, le correctif est un index plus souvent que toute autre chose. Les index sont l’outil de performance le plus puissant d’une base relationnelle, et c’est aussi là que je vois l’intuition la plus confiante et la plus fausse. Les gens ajoutent un index sur chaque colonne « au cas où », ou ils ajoutent un index multi-colonnes dans le mauvais ordre et se demandent pourquoi le planificateur l’ignore. Voici mon modèle mental de fonctionnement des index et de la manière dont je décide lesquels construire.
Rien de tout cela ne compte si le schéma en dessous est un désastre, donc si ce n’est pas déjà fait, les fondations viennent d’une bonne conception et normalisation. Supposons ici que le modèle est sain et que nous le rendons rapide.
Ce qu’est réellement un index
Un index est une structure de données séparée et triée qui permet à la base de trouver des lignes sans parcourir toute la table. Le choix par défaut dans la plupart des bases relationnelles est un arbre B, qui maintient les clés en ordre trié et prend en charge les recherches d’égalité et les parcours de plage en temps logarithmique. Cet ordre trié est tout l’intérêt, et il explique presque tout ce qu’un index peut et ne peut pas faire.
Parce que les clés sont triées, un arbre B est excellent pour trois choses. Trouver une valeur précise, trouver une plage de valeurs, et renvoyer des lignes déjà triées pour que la base puisse sauter une étape de tri séparée. Il est inutile pour l’inverse. Une requête qui demande tout sauf une valeur, ou qui enveloppe la colonne dans une fonction que l’index ne connaît pas, ne peut pas utiliser la structure triée et retombe sur un parcours complet.
Le coût dont personne ne parle
Chaque index que vous ajoutez rend les lectures plus rapides et les écritures plus lentes. Ce n’est pas un slogan, c’est mécanique. Quand vous insérez, mettez à jour ou supprimez une ligne, la base doit mettre à jour chaque index couvrant les colonnes touchées. Une table avec huit index paie huit petites opérations de maintenance à chaque écriture. Les index prennent aussi de l’espace disque et de la mémoire, et un index gonflé qui ne tient pas en cache cesse d’être l’accélération que vous vouliez.
Je n’indexe donc pas par défense. J’indexe en réponse à des preuves. Le bon nombre d’index est le plus petit ensemble qui rend vos vraies requêtes rapides, et trouver cet ensemble passe par la lecture des plans de requête plutôt que par la devinette.
Lire le plan de requête
La compétence la plus utile en performance de base de données est la lecture de la sortie EXPLAIN. Elle vous dit ce que le planificateur a l’intention de faire, et EXPLAIN ANALYZE vous dit ce qui s’est réellement passé avec des temps réels. Je le lance en permanence.
-- Voir le plan et les vrais chiffres d'exécution
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, placed_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY placed_at DESC
LIMIT 20;Ce que je regarde, dans l’ordre. Y a-t-il un parcours séquentiel sur une grande table où j’attendais un parcours d’index ? C’est l’alarme la plus bruyante. À quel point l’estimation de lignes du planificateur est-elle éloignée du nombre réel ? Un grand écart signifie des statistiques périmées et je lance ANALYZE sur la table. Y a-t-il un tri coûteux qu’un index pourrait satisfaire directement ? La même table est-elle parcourue plus d’une fois ?
Index composites et ordre des colonnes
Les index multi-colonnes sont là où le plus de points se gagnent et se perdent. La règle que j’ai mis trop de temps à intérioriser est que l’ordre des colonnes est primordial, et il découle de la structure triée. Un index sur (customer_id, status, placed_at) est trié d’abord par customer_id, puis par status, puis par placed_at. Cet ordre lui permet de servir une requête filtrant sur customer_id seul, ou customer_id et status, ou les trois. Il ne peut pas servir efficacement une requête qui filtre uniquement sur status, parce que status n’est pas la colonne de tête.
La ligne directrice que j’utilise est les colonnes d’égalité d’abord, puis la colonne de plage ou de tri en dernier. Pour la requête ci-dessus, un index sur (customer_id, status, placed_at) est proche de l’idéal. Les deux prédicats d’égalité restreignent la recherche, et parce que placed_at est la dernière colonne et déjà triée, la base peut satisfaire le ORDER BY et le LIMIT sans tri séparé. Un index, pas d’étape de tri, vingt lignes.
-- Colonnes d'égalité d'abord, puis la colonne de tri
CREATE INDEX idx_orders_customer_status_time
ON orders (customer_id, status, placed_at DESC);Index couvrants et parcours index-only
Il y a une astuce supplémentaire. Si un index contient toutes les colonnes dont une requête a besoin, la base peut répondre à la requête depuis le seul index et ne jamais toucher la table. C’est un parcours index-only, et il peut être bien plus rapide parce qu’il évite les lectures aléatoires de retour vers le tas. J’y arrive en incluant les colonnes supplémentaires que la requête renvoie.
-- INCLUDE ajoute des colonnes de charge utile sans changer la clé de tri
CREATE INDEX idx_orders_cover
ON orders (customer_id, status)
INCLUDE (placed_at, id);Je ne fais pas cela partout, parce que des index plus larges coûtent plus cher à maintenir et à stocker. Mais pour une requête chaude et bien comprise qui tourne en permanence, la transformer en parcours index-only est l’un des meilleurs retours sur effort disponibles.
Les index que le planificateur refusera discrètement
Un nombre surprenant d’index restent inutilisés à cause de la façon dont la requête est écrite, pas de la façon dont l’index est construit. Les erreurs classiques que je cherche en premier.
- Fonctions sur la colonne indexée. WHERE lower(email) = ‘x’ ne peut pas utiliser un index simple sur email. Stockez la valeur normalisée, utilisez un type insensible à la casse, ou construisez un index d’expression sur lower(email).
- Jokers en tête. LIKE ‘foo%’ peut utiliser un arbre B, mais LIKE ‘%foo’ ne le peut pas, parce que l’ordre trié est inutile quand le début de la chaîne est inconnu.
- Incompatibilités de type. Comparer une colonne texte à un nombre force une conversion qui peut désactiver l’index. Faites correspondre vos types.
- Faible sélectivité. Un index sur une colonne à deux valeurs possibles aide rarement, parce que lire l’index plus le tas est souvent plus lent qu’un simple parcours. Le planificateur le sait et le saute, à juste titre.
Index partiels pour données asymétriques
L’un de mes outils préférés pour les charges réelles est l’index partiel, qui ne couvre que les lignes correspondant à une condition. Si 95 pour cent de vos commandes sont terminées et que vous interrogez presque toujours la petite tranche encore en attente, n’indexer que les lignes en attente vous donne un index minuscule et rapide qui reste chaud en mémoire.
-- Indexer seulement les lignes que nous cherchons réellement
CREATE INDEX idx_orders_pending
ON orders (placed_at)
WHERE status = 'pending';Cela garde l’index petit, ce qui le garde rapide et peu coûteux à maintenir. Pour les tables à forte asymétrie c’est souvent la différence entre un index qui tient en cache et un qui n’y tient pas.
Comment je travaille réellement
Ma boucle est ennuyeuse et elle marche. Trouver la requête lente à partir de vraies métriques, pas d’une intuition. Lancer EXPLAIN ANALYZE et le lire attentivement. Identifier si le problème est un index manquant, un mauvais ordre de colonnes, des statistiques périmées, ou une requête écrite d’une façon qui défait l’indexation. Faire un changement. Mesurer à nouveau. Répéter jusqu’à ce que le plan soit propre.
Je résiste à l’envie d’ajouter cinq index d’un coup, parce qu’alors je ne peux pas dire lequel a aidé et j’ai souscrit à un surcoût d’écriture dont je n’ai peut-être pas besoin. Un changement, une mesure. Et je révise l’ensemble des index périodiquement, parce que les charges dérivent et l’index essentiel d’hier peut devenir le poids mort d’aujourd’hui qui ne fait que ralentir les écritures. Le même soin qui va dans le schéma et le modèle de données va dans le maintien d’index honnêtes. Mesurer, changer une chose, mesurer à nouveau. Cette discipline bat l’astuce à tous les coups.