Générateur d'expression cron et calculateur du prochain lancement — Maîtrisez le cron sans avoir à deviner 2

Votre requête dure 2 ms sur localhost avec 500 lignes. En production avec 3 millions de lignes, elle dure 9 secondes et vos utilisateurs voient un spinner. La requête elle-même est probablement bonne — c'est la structure de la base de données qui est problématique. Vous avez besoin d'un index, et vous devez comprendre pourquoi alors que vous cessez de deviner la prochaine fois que cela se produit.

Ce que fait la base de données sans index

Un index B-tree est une copie triée d'une colonne (ou de plusieurs colonnes), stockée dans un arbre équilibré que la base de données peut rechercher en O(log n). Sans index, la base de données effectue une lecture complète de la table — elle lit chaque ligne pour trouver les correspondances. Avec 3 millions de lignes, cela représente 3 millions de lectures de lignes. Avec un index sur la bonne colonne, cela ne fait que 20 à 30 parcours de nœuds d'arbre pour trouver les lignes correspondantes.

Imaginons cela comme un dictionnaire. Trouver « Smith, John » dans l'ordre alphabétique prend des secondes. Trouver tous les personnes nommées John en lisant chaque entrée à partir de la page 1 prend des siècles. Un index est le tri alphabétique — la base de données l'utilise pour sauter directement aux lignes pertinentes au lieu de lire tout.

Une chose importante : les indexes existent sur disque à côté de votre table. La base de données les maintient automatiquement lors des écritures. C'est gratuit pour les lectures, mais coûte quelque chose à chaque INSERT, UPDATE et DELETE — c'est pourquoi vous ne pouvez pas indexer tout.

Identifier le problème avec EXPLAIN

Avant de toucher quoi que ce soit, exécutez EXPLAIN sur la requête lente. Voici ce que vous cherchez dans MySQL :

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Deux champs importants :

• type : ALL — lecture complète de la table. C'est presque toujours mauvais une fois que votre table dépasse environ 100 000 lignes.
• rows : 2847391 — l'optimiseur estime qu'il devra examiner 2,8 millions de lignes pour retourner vos résultats.

Maintenant, ajoutez l'index et exécutez à nouveau :

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type : ref et rows : 12. C'est la solution. 2,8 millions de lignes examinées → 12.

Dans PostgreSQL, utilisez EXPLAIN ANALYZE pour obtenir des données de temps réels :

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934 ms → 0,3 ms. Ce n'est pas un exemple choisi à la main — c'est l'impact réel d'un seul index manquant sur une table avec quelques millions de lignes.

Indices composés et la règle de la préfixe gauche

Une fois que vous avez plusieurs colonnes dans votre clause WHERE, vous avez besoin d'indices composés — et l'ordre des colonnes importe.

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

Le règle de la préfixe gauche: la base de données utilise un indice composé en partant de la première colonne et en avançant vers la droite, mais cesse d'être utile dès qu'elle rencontre un vide. Avec un indice sur (a, b, c):

• WHERE a = 1 — utilise l'indice
• WHERE a = 1 AND b = 2 — utilise les deux colonnes de l'indice
• WHERE a = 1 AND b = 2 AND c = 3 — utilise l'ensemble de l'indice
• WHERE b = 2 — ne utilise pas l'indice (colonne a ignorée)
• WHERE a = 1 AND c = 3 — utilise seulement la partie a, ignore c

La règle pratique d'ordre : les conditions d'égalité en premier, les conditions de plage en dernier. Les prédicats de plage (>, <, BETWEEN, LIKE 'prefix%') rompent la préfixe utilisable pour les colonnes suivantes. Ainsi :

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

Pensez aussi à la sélectivité : mettez la colonne la plus sélective (moins de valeurs répétées) en premier dans l'indice. Si customer_id a 50 000 valeurs distinctes et status a 3, customer_id devrait venir en premier — elle réduit plus rapidement l'ensemble des résultats.

Quand les indices rendent les choses pires

Les indices ne sont pas gratuits. Chaque indice coûte des performances d'écriture et de l'espace disque. La base de données met à jour chaque indice à chaque INSERT, UPDATE et DELETE sur cette table. Sur des tables à forte fréquence d'écriture, des indices excessifs nuisent réellement à la performance. Voici où les indices échouent ou se retournent :

• Colonnes à faible cardinalité. Un indice sur un champ booléen ou sur un champ avec 3 valeurs distinctes est souvent ignoré par l'optimiseur. Si une requête retournerait plus de 10 à 15% de lignes via l'indice, la base de données préfère une lecture complète de la table — le coût de recherche dans l'indice n'est pas justifié. Vérifiez avec EXPLAIN si l'indice est effectivement utilisé. status Tables à forte fréquence d'écriture.
• Tables de journal, tables d'événements, tables d'audit — soyez prudent. Une table avec 10 indices et des millions d'écritures par heure paie 10 fois le coût d'écriture sur ces indices. Indices inutilisés.
• Dans PostgreSQL, montre pg_stat_user_indexes — combien de fois chaque indice a été utilisé depuis la dernière mise à jour des statistiques. Un indice avec zéro lecture est purement un surcoût. MySQL le suit via le schéma de performance ( idx_scan dans MySQL 5.7+).sys.schema_unused_indexes L'optimiseur se trompe.
• Les planificateurs de requêtes ne sont pas toujours justes. Parfois, ils choisissent un indice plus lent qu'une lecture complète de la table. va détecter cela — comparez les lignes estimées aux lignes réelles. Dans MySQL, vous pouvez utiliser EXPLAIN ANALYZE ; dans PostgreSQL, vous pouvez tester avec USE INDEX (idx_name)(n'oubliez pas de le supprimer en production). SET enable_seqscan = off Index ou réécriture ? Le vrai compromis

Parfois, l'index est la solution. Parfois, la requête est le problème et l'index ne le masque qu'avec. Quelques modèles où vous devriez réécrire la requête au lieu de (ou en plus de) créer un index :

Fonctions sur des colonnes indexées

Envelopper une colonne dans une fonction empêche l'utilisation standard de l'index. L'index stocke les valeurs de

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

, pas les valeurs de created_at— ce sont des choses différentes. PostgreSQL supporte les indices d'expression ( YEAR(created_at) ) comme solution, mais la réécriture de la requête est plus propre et fonctionne partout.CREATE INDEX ON orders (YEAR(created_at))Indices couvrants pour les parcours de lecture fréquents

indice couvrant

UN inclut toutes les colonnes nécessaires à une requête, donc la base de données n'a jamais besoin de lire les lignes réelles de la table — elle répond entièrement à partir de l'indice. Cela peut être beaucoup plus rapide qu'un indice standard pour des tables à forte lecture : Modèles de recherche LIKE

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

Les préfixes initiales ne peuvent pas être corrigées avec des indices B-tree. Si vous avez besoin de recherche de suffixe ou de sous-chaine, vous avez besoin d'un indice de texte complet (

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

dans MySQL,FULLTEXT dans PostgreSQL) ou d'un système externe de recherche comme Elasticsearch. GIN avec pg_trgm N+1 requêtes

Aucun index ne corrige un problème de N+1. Si votre ORM fait une requête pour récupérer 200 commandes, puis une requête séparée pour chaque commande pour obtenir le nom du client, vous avez 201 requêtes. Chaque requête peut être rapide. Le problème est le volume. Corrigez-le avec un JOIN ou une requête en lot — aucun nombre d'indices ne change le nombre de requêtes.

Une autre chose : nettoyez la requête avant de l'analyser

Avant d'exécuter EXPLAIN sur une requête lente, il est utile d'avoir la requête formatée clairement. Une requête en une ligne de 200 caractères sans indentation est plus difficile à comprendre qu'une requête structurée. Le

formatteur SQL à IO Tools gère MySQL, PostgreSQL, SQL Server et d'autres dialectes — utile lorsque vous copiez une requête depuis des logs ou un output d'ORM et que vous devez voir sa structure à l'œil. La liste pratique

avant d'ajouter n'importe quel index. Vérifiez d'abord le problème.

• Exécutez EXPLAIN Recherchez
• dans MySQL) ou type: ALL dans PostgreSQL) — généralement là où se situe le problème. Seq Scan estimation dans EXPLAIN. Des millions de lignes lues pour retourner quelques dizaines = indice manquant.
• Vérifiez la rows Dans les indices composés : les conditions d'égalité en premier, les conditions de plage en dernier.
• Les colonnes à plus grande sélectivité viennent en premier dans un indice composé.
• Ne pas indexer des colonnes à faible cardinalité sans vérifier que l'optimiseur utilise effectivement l'indice.
• Audit régulièrement les indices inutilisés :
• dans PostgreSQL, pg_stat_user_indexes dans MySQL. sys.schema_unused_indexes Si une fonction enveloppe votre colonne dans une condition WHERE, réécrivez la requête au lieu d'indexer la fonction.
• Pour des parcours de lecture fréquents avec un petit ensemble de colonnes, envisagez les indices couvrants.
• N+1 est un problème d'architecture de requête, pas un problème d'index.
• GitHub Actions YAML Linter & Formatter

Vous aimerez peut-être aussi