Une requête qui prenait quelques millisecondes l’année dernière peut prendre quelques minutes aujourd’hui, simplement parce que la table s’agrandit. L’optimisation des requêtes SQL relève moins d’astuces astucieuses que d’un processus reproductible : découvrez ce que fait la base de données, supprimez le travail inutile et vérifiez que cela a aidé.

Nous avons testé chaque technique ci-dessous sur un million de lignes générées dans SQLite 3.50. Les timings sont le meilleur de plusieurs exécutions à partir d’un seul ordinateur portable, en utilisant une base de données en mémoire. Ils ne correspondront pas à votre système et PostgreSQL, MySQL et SQL Server ont chacun leurs propres optimiseurs et formats de plan. Concentrez-vous sur l’ampleur de chaque amélioration et sur les principes qui la sous-tendent.

Les résultats en un coup d’oeil

La configuration des tests

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

Un processus d’optimisation simple

1. Trouvez les requêtes qui comptent. Utilisez des journaux de requêtes lents ou une surveillance. Une requête de 200 ms exécutée mille fois par minute peut avoir plus d’importance qu’un rapport mensuel de 10 secondes.
2. Lisez le plan. Voyez ce que fait réellement la base de données.
3. Modifiez une chose. Ajoutez un index ou réécrivez une partie de la requête.
4. Mesurez à nouveau sur des données réalistes. Un plan sur 100 lignes de test vous en dit très peu sur un million.
5. À surveiller au fil du temps. Les plans peuvent changer à mesure que les données augmentent et que les statistiques évoluent.

Lire un plan d’exécution

Un plan d’exécution est la stratégie étape par étape de la base de données pour exécuter une requête, y compris les index qu’elle utilise et l’ordre dans lequel elle rejoint les tables. Préfixez une requête pour la voir :

Notez que EXPLAIN ANALYZE dans PostgreSQL exécute réellement la requête, alors soyez prudent lorsque vous l’utilisez avec UPDATE ou DELETE. Voici ce qu’il faut rechercher en premier :

Une analyse complète n’est pas automatiquement mauvaise. Si une requête nécessite la majeure partie d’une petite table, l’analyse peut être le choix le plus rapide. Dans PostgreSQL EXPLAIN ANALYZE, comparez également les lignes estimées avec les lignes réelles. Une grande différence signifie souvent que les statistiques des tableaux sont obsolètes.

Ajoutez le bon index

Cette requête recherche les commandes d’un client :

SELECT id, total FROM orders WHERE customer_id = 123;

Avant d’ajouter un index, le plan était une analyse complète :

SCAN orders
-- 20 rows in 73 ms

Ensuite nous avons ajouté un index sur la colonne de filtre :

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

Les mêmes 20 lignes sont revenues des milliers de fois plus rapidement, car la base de données ne lisait plus un million de lignes pour les retrouver. Aucune autre technique présentée dans cet article ne nous a permis de gagner autant. Les bons premiers candidats sont les colonnes utilisées dans WHERE, JOIN et ORDER BY, surtout lorsqu’elles réduisent les résultats à quelques lignes. Notre guide des index SQL explique leur fonctionnement.

Ne pas envelopper les colonnes indexées dans des fonctions

Voici deux façons de trouver les commandes de mars, avec un index sur created :

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Les deux renvoient des lignes identiques, mais la première a pris environ trois fois plus de temps. Le plan montre pourquoi : avec une fonction sur la colonne, la base de données ne peut pas accéder à une position dans l’index trié, elle doit donc SCAN parcourir chaque entrée. La version Range peut SEARCH directement au bon endroit. Une condition qui peut utiliser un index de cette manière est appelée sargable.

Le même problème apparaît avec ces modèles courants :

Nous avons également vérifié ce dernier. Un caractère générique de début est produit SCAN customers, car un index trié à partir de la première lettre ne peut pas aider à trouver une correspondance à la fin.

Index composites

Lorsqu’une requête filtre sur deux colonnes, un index à une seule colonne ne fait qu’une partie du travail :

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

L’ordre des colonnes est très important. Un index sur (status, created) fonctionne comme un annuaire téléphonique trié par nom puis prénom. Vous pouvez rechercher un nom de famille seul, mais pas un prénom seul. Notre requête sur status seule pourrait utiliser cet index. Le filtrage sur created seul ne pouvait généralement pas l’utiliser efficacement. En règle générale, placez les colonnes testées avec égalité en premier et les colonnes de plage après elles.

Ce plan indique également couvrant l’index, ce qui signifie que l’index contenait tout ce dont la requête avait besoin, de sorte que la table elle-même n’avait pas besoin d’être lue. Sélectionner uniquement les colonnes dont vous avez besoin rend cela plus probable, ce qui est une autre raison à éviter SELECT *.

Pagination plus rapide

Utiliser OFFSET pour les pages profondes représente un coût caché, puisque la base de données doit toujours parcourir toutes les lignes ignorées :

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

La pagination du jeu de touches reste rapide, quelle que soit la profondeur à laquelle vous allez. Cela évite également le déplacement des lignes entre les pages lorsque de nouvelles données sont ajoutées. Le compromis est que les utilisateurs ne peuvent pas accéder directement à la page 5 000, ce qui constitue rarement un réel besoin. Sur les tableaux avec des lignes larges ou avec plus de lignes à sauter, l’écart peut être beaucoup plus important qu’il ne l’était lors de notre test.

Écrivez des requêtes plus simples

• Sélectionnez uniquement les colonnes dont vous avez besoin. SELECT * envoie plus de données et peut empêcher un index de couvrir la requête.
• Filtrer tôt. Utilisez WHERE pour supprimer les lignes avant le regroupement, et HAVING uniquement pour les conditions sur les agrégats. Voir SQL GROUP BY et HAVING.
• Utilisez LIMIT lorsque vous n’avez besoin que de quelques lignes.
• Préférez UNION ALL lorsque vous n’avez pas besoin de supprimer les doublons. Notre test UNION vs UNION ALL montre ce que cela permet d’économiser.
• Évitez les DISTINCT inutiles. Il s’agit souvent de masquer une jointure qui produit des doublons.
• Utilisez EXISTS pour les vérifications d’existence. Cela peut s’arrêter à la première correspondance, comme l’explique notre guide des sous-requêtes SQL.
• Surveillez les requêtes N+1. Une requête par ligne dans une boucle, généralement provoquée par le code de l’application, est souvent le véritable problème. Une seule requête de jointure ou par lots peut remplacer des centaines d’allers-retours.

Rejoint

Indexez les colonnes sur lesquelles vous rejoignez. Nous avons réalisé un rapport sur les dépenses totales des clients d’une ville, et le plan correspondait exactement à ce que vous espériez :

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

Il scanne les clients, puis utilise un index pour retrouver les commandes de chacun. Sans index sur orders.customer_id, la base de données devrait faire beaucoup plus de travail pour chaque client. Un index sur la clé étrangère est l’un des ajouts les plus courants et les plus précieux que vous puissiez apporter. Pour rafraîchir votre compréhension des bases, consultez Explication des jointures SQL.

Quand les index vous coûtent cher

Les index ne sont pas gratuits, n’en ajoutez donc pas un pour chaque colonne :

• Écritures plus lentes. Chaque insertion, mise à jour et suppression doit également mettre à jour chaque index.
• Plus de stockage et de mémoire. Les index peuvent croître en taille sur les grandes tables.
• Peu d’avantages sur les colonnes non sélectives. Un index sur une colonne vrai ou faux peut ne pas aider si la plupart des lignes correspondent.
• Redondance. Un index sur (a) est souvent inutile si vous en avez déjà un sur (a, b).

Vérifiez périodiquement les statistiques d’utilisation et supprimez les index que rien n’utilise. Et gardez les statistiques à jour avec ANALYZE ou l’équivalent de votre base de données, car l’optimiseur s’appuie sur elles pour choisir un bon plan. Parfois, la partie la plus lente est la contention plutôt que la requête. Des transactions de longue durée détenant des verrous peuvent donner l’impression qu’une requête rapide semble lente.

Erreurs courantes

• Optimiser sans mesurer. De nombreuses suppositions sûres s’avèrent fausses.
• Tests avec des données minuscules. Les plans et les horaires changent considérablement à mesure que les tables s’agrandissent.
• Faire confiance à une astuce pour une autre base de données. Les conseils pour un moteur peuvent ne pas s’appliquer à un autre.
• Ajout d’index aveuglément. Chacun ralentit les écritures.
• Ignorer l’effet de première exécution. Les caches accélèrent une deuxième exécution, donc exécutez-les plusieurs fois.

Pour conclure

La plupart des requêtes lentes ont des causes ordinaires : un index manquant, une fonction sur une colonne ou trop de données récupérées. Prenez votre requête réelle la plus lente cette semaine, exécutez EXPLAIN dessus et recherchez une analyse complète sur une grande table. Réparez une chose, mesurez à nouveau et notez ce que vous avez modifié.

Questions fréquentes

Continuez à apprendre