Accélérer ORDER BY / GROUP BY avec des index

Tuer le tri temporaire avec un index

Quand tu tries avec ORDER BY et que la colonne cible n'a pas d'index, la base doit sortir chaque ligne puis les trier.

Le plan montre ce tri temporaire comme USE TEMP B-TREE FOR ORDER BY (il construit une structure de travail temporaire juste pour faire la réorganisation).

Les choses changent dès que tu crées un index sur la colonne sur laquelle tu tries.

Un index est déjà dans l'ordre de cette colonne dès l'instant où il est construit.

Parcours l'ordre de l'index et le résultat tombe immédiatement.

Le tri temporaire n'est plus nécessaire, et USE TEMP B-TREE disparaît du plan.

Un tri temporaire réorganise les 50 000 lignes dans une structure de travail, donc le coût grandit avec le nombre de lignes.

Même si LIMIT 10 ne renvoie que les 10 premières lignes, prendre ces 10 premières exige quand même de réordonner d'abord chaque ligne — la charge ne se réduit pas.

Dans cet article, tu vas essayer en pratique « tuer le tri avec un index ».

-- Exemple : prendre les 10 premières lignes triées par amount
-- Avec un index, la base parcourt juste son ordre
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

EXPLAIN QUERY PLAN
SELECT sale_id, amount
FROM perf_sales
ORDER BY amount
LIMIT 10;

Imagine l'exigence : « On veut les ventes triées par date pour voir les premières. » Prends la requête qui trie par sale_date et compare son plan avec et sans index, en surveillant si USE TEMP B-TREE FOR ORDER BY apparaît. Supprime et reconstruis l'index dans cette seule exécution de console pour que le plan soit totalement autonome. (Exécute-le correctement pour révéler l'explication.)

① D'abord, utilise DROP INDEX IF EXISTS pour supprimer l'index sur sale_date, puis montre EXPLAIN QUERY PLAN pour les 10 premières lignes triées par sale_date (l'état sans index).

② Ensuite crée un index à colonne unique sur sale_date.

③ Montre à nouveau le plan pour la même requête de tri, et compare-le avec ① pour voir si le tri temporaire disparaît.

GROUP BY peut aussi s'appuyer sur l'ordre de l'index

GROUP BY a aussi besoin que les lignes ayant la même valeur soient côte à côte pour pouvoir les grouper.

Sans index, ça signifie un tri temporaire et USE TEMP B-TREE FOR GROUP BY dans le plan.

Crée un index sur la colonne de groupement et les lignes ayant la même valeur sont déjà adjacentes dans l'ordre de l'index, donc la base peut découper les groupes juste en le parcourant.

Par exemple, GROUP BY emp_id associé à un index sur emp_id signifie que les lignes de chaque emp_id sont déjà consécutives dans l'ordre de l'index, donc l'agrégation s'exécute sans tri temporaire.

Inclus aussi la colonne agrégée dans l'index et tu évites également de retourner à la table de base (l'Index-Only Scan de l'article précédent).

-- Exemple : compter par region
-- Avec un index sur region, GROUP BY n'a pas besoin de tri temporaire
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Imagine l'exigence : « On veut le nombre de ventes par commercial. » Prends la requête qui groupe par emp_id et compare son plan avec et sans index, en surveillant si USE TEMP B-TREE FOR GROUP BY apparaît.

① D'abord, utilise DROP INDEX IF EXISTS pour supprimer l'index, puis montre EXPLAIN QUERY PLAN pour la requête qui compte les lignes par emp_id (l'état sans index).

② Ensuite crée un index à colonne unique sur emp_id.

③ Montre à nouveau le plan pour la même requête d'agrégat, et compare-le avec ① pour voir si le tri temporaire disparaît.

DISTINCT tue son tri temporaire de la même façon

DISTINCT (qui supprime les doublons et ne renvoie que les valeurs uniques) a aussi besoin que les valeurs soient alignées pour repérer les doublons, donc sans index il déclenche un tri temporaire avec USE TEMP B-TREE FOR DISTINCT.

Le mécanisme est le même que pour GROUP BY : avec un index sur la colonne cible, les valeurs identiques sont déjà adjacentes dans l'ordre, donc supprimer les doublons revient juste à comparer chaque voisin.

Pour SELECT DISTINCT product FROM perf_sales, où tu veux les valeurs uniques d'une seule colonne, un index sur product supprime le besoin de tri temporaire.

Tri, groupement et déduplication partagent tous le trait d'être « des opérations qui ont besoin de valeurs alignées » — et un index sur la colonne cible te permet d'esquiver le tri temporaire pour n'importe laquelle.

-- Exemple : obtenir les valeurs uniques de region
-- Un index sur region évite le tri temporaire de DISTINCT
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT DISTINCT region
FROM perf_sales;

Imagine l'exigence : « On veut la liste des codes produit qu'on a traités, sans doublons. » Prends la requête qui renvoie les valeurs uniques de product et compare son plan avec et sans index, en surveillant si USE TEMP B-TREE FOR DISTINCT apparaît.

① D'abord, utilise DROP INDEX IF EXISTS pour supprimer l'index, puis montre EXPLAIN QUERY PLAN pour la requête qui renvoie les valeurs uniques de product (l'état sans index).

② Ensuite crée un index à colonne unique sur product.

③ Montre à nouveau le plan pour la même requête de déduplication, et compare-le avec ① pour voir si le tri temporaire disparaît.