Index — Composite, partiel, expression et UNIQUE

Le jeu de données de cet article — la table perf_sales

Un index fonctionne comme l'index à la fin d'un livre : il te laisse trouver rapidement les lignes ayant une valeur de colonne donnée.

Cet article parcourt la création d'index avec CREATE INDEX, la construction d'index composites, partiels, sur expression et UNIQUE, et l'usage d'EXPLAIN QUERY PLAN pour confirmer comment la stratégie de recherche change.

Avant de plonger, vérifie les définitions de colonnes, un échantillon des données et le nombre de lignes de perf_sales. (Exécute les requêtes correctement pour révéler l'explication.)

① Utilise PRAGMA table_info(perf_sales); pour voir les noms de colonnes, les types et la clé primaire.

② Utilise SELECT COUNT(*) FROM perf_sales; pour confirmer le nombre total de lignes.

③ Prévisualise les 5 premières lignes avec SELECT * FROM perf_sales LIMIT 5;. Comme perf_sales fait 50 000 lignes, inclus toujours LIMIT.

CREATE INDEX et EXPLAIN QUERY PLAN — transformer un parcours complet de table en recherche par index

Crée un index avec CREATE INDEX nom_index ON table(colonne);.

Quand tu utilises une colonne indexée dans une condition d'égalité ou de plage WHERE, l'optimiseur peut choisir une recherche par index au lieu d'un parcours complet de table et sauter directement aux lignes correspondantes.

Vérifie la stratégie de recherche avec EXPLAIN QUERY PLAN.

Préfixe ta requête avec EXPLAIN QUERY PLAN et tu verras le plan pour trouver les lignes au lieu des résultats réels.

Sans index tu verras SCAN perf_sales (aussi appelé FULL SCAN — examiner chaque ligne de la table), et une fois qu'un index est utilisé tu verras quelque chose comme SEARCH perf_sales USING INDEX ... (aussi appelé INDEX SCAN — restreindre la recherche avec l'index).

La formulation exacte du plan varie selon l'environnement, donc cet article considère une exécution réussie comme la bonne réponse et lit le sens du plan dans la prose et les explications.

-- Plan sans index (exemple séparé en lecture seule)
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales (examiner chaque ligne)

-- Créer un index, puis regarder le même plan
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)
-- Note : exécute DROP -> CREATE -> EXPLAIN d'un bout à l'autre dans la même exécution

Imagine l'exigence : « On cherche souvent les ventes avec emp_id = 7, donc on veut passer d'un parcours complet de table à une recherche par index. » Tu vas exécuter trois consoles dans l'ordre pour comparer les temps d'exécution et les plans avant et après l'ajout de l'index.

Stratégie de comparaison en trois étapes : ① mesurer le temps d'exécution t1 sans index, ② créer l'index (coût ponctuel, exclu de la comparaison pure), ③ relancer la même requête et mesurer t3 avec l'index.

① Dans cette console, exécute SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; et l'EXPLAIN QUERY PLAN correspondant, puis confirme SCAN perf_sales et le temps d'exécution t1.

② Dans cette console, exécute DROP INDEX IF EXISTS ix; puis CREATE INDEX ix ON perf_sales(emp_id); pour construire l'index.

Note : la création de l'index est un coût ponctuel et ne fait pas partie de la pure comparaison de vitesse de recherche entre t1 et t3 — exclus le temps d'exécution de cette console de la comparaison.

③ Dans cette console, relance la même requête qu'à l'étape ① : SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; et l'EXPLAIN QUERY PLAN correspondant. Confirme que tu vois maintenant SEARCH perf_sales USING INDEX ix et un temps d'exécution t3 plus court que le t1 mesuré à l'étape ①.

Index composites — indexer plusieurs colonnes ensemble

Écrire plusieurs colonnes entre parenthèses — CREATE INDEX nom ON table(col1, col2); — produit un index composite (aussi appelé index multi-colonnes).

Il accélère les requêtes qui utilisent les conditions à partir de la colonne de tête, comme WHERE col1 = ... AND col2 >= ....

L'ordre des colonnes compte : un index composite n'aidera pas les requêtes qui n'utilisent pas sa colonne de tête dans la clause WHERE.

Si ton schéma d'accès est fixe — par exemple « égalité sur emp_id et plage sur sale_date » — construire un index composite dans cet ordre permet à un seul index de couvrir les deux conditions.

-- Index composite (exemple séparé en lecture seule)
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, sale_date);

EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE region = 'East' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING INDEX ix_demo (region=? AND sale_date>?)
-- Restreindre par region (égalité), puis restreindre sale_date par plage

Imagine l'exigence : « On choisit souvent un commercial avec emp_id et une période avec sale_date, donc on veut un seul index qui couvre les deux conditions. »

① Commence par DROP INDEX IF EXISTS ix;.

② Crée un index composite nommé ix sur perf_sales listant emp_id et sale_date dans cet ordre.

③ Exécute ensuite EXPLAIN QUERY PLAN sur une requête qui filtre par emp_id = 7 et sale_date >= '2024-01-01' et sélectionne sale_id et amount, et confirme que l'index composite est utilisé.

Index partiels et index sur expression

Un index partiel se construit avec CREATE INDEX nom ON table(colonne) WHERE condition; et n'indexe qu'un sous-ensemble des lignes de la table.

Si tu cherches fréquemment des lignes avec une valeur précise comme status = 'pending', indexer uniquement ces lignes garde l'index petit et concentré sur les requêtes que tu exécutes vraiment.

Dans perf_sales, seules 5 000 lignes (10 % du total) ont status = 'pending', donc un index partiel avec WHERE status = 'pending' est un choix naturel.

Un index sur expression se construit avec CREATE INDEX nom ON table(expression); et indexe le résultat d'une expression calculée plutôt qu'une colonne brute.

Utilise-le pour les requêtes qui appliquent la même expression côté recherche, comme WHERE amount / 100 = ....

Un index ordinaire sur une colonne n'aide normalement plus dès que tu appliques une fonction ou une opération arithmétique à cette colonne, mais si tu indexes l'expression elle-même, les recherches utilisant l'expression correspondante peuvent s'en servir.

-- Index partiel (exemple séparé en lecture seule)
DROP INDEX IF EXISTS ix_part;
CREATE INDEX ix_part ON perf_sales(sale_date) WHERE status = 'refunded';
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE status = 'refunded' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING ... INDEX ix_part (sale_date>?)

-- Index sur expression
DROP INDEX IF EXISTS ix_expr;
CREATE INDEX ix_expr ON perf_sales(qty * amount);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE qty * amount = 100000;
--> SEARCH perf_sales USING ... INDEX ix_expr (<expr>=?)

Imagine l'exigence : « On cherche souvent les ventes en attente (status = 'pending') par plage de dates, donc on veut un petit index qui couvre uniquement ces lignes. »

① Commence par DROP INDEX IF EXISTS ix;.

② Crée un index partiel nommé ix sur perf_sales(sale_date) qui cible uniquement les lignes où status = 'pending'.

③ Exécute EXPLAIN QUERY PLAN sur une requête qui filtre par status = 'pending' AND sale_date >= '2024-01-01' et confirme que l'index partiel est utilisé (SEARCH ... USING INDEX ix).

④ Exécute ensuite EXPLAIN QUERY PLAN sur une requête qui utilise la même colonne mais avec status = 'paid' (par exemple, status = 'paid' AND sale_date >= '2024-01-01') et confirme que l'index partiel n'est pas utilisé (SCAN perf_sales) parce que la condition ne correspond pas au prédicat de l'index.

Imagine l'exigence : « On cherche parfois par la valeur de tranche d'amount divisé par 100 (amount / 100), donc on veut indexer ce résultat calculé. » Les recherches qui appliquent une opération arithmétique à une colonne ne bénéficient pas d'un index ordinaire sur colonne, donc utilise un index sur expression à la place.

① Commence par DROP INDEX IF EXISTS ix;.

② Crée un index nommé ix sur l'expression amount / 100 sur perf_sales.

③ Exécute EXPLAIN QUERY PLAN sur une requête qui filtre par amount / 100 = 5000 et confirme que l'index sur expression est utilisé (SEARCH ... USING INDEX ix).

④ Exécute ensuite EXPLAIN QUERY PLAN sur une requête qui filtre par amount = 500000 sans passer par l'expression (un exemple utilisant la même colonne amount mais avec une forme différente de l'index) et confirme que l'index sur expression n'est pas utilisé (SCAN perf_sales).

Index UNIQUE — un index qui interdit les doublons

Un index UNIQUE — construit avec CREATE UNIQUE INDEX nom ON table(colonne); — accélère les recherches et empêche l'insertion de valeurs en doublon dans la colonne.

Si tu essaies de créer un index UNIQUE sur une colonne qui contient déjà des doublons, tu obtiens une erreur UNIQUE constraint failed et l'index n'est pas créé du tout.

Le revers : si tu en crées un sur une colonne dont les valeurs sont uniques (une clé primaire ou une clé métier unique), tu obtiens la prévention des doublons plus une recherche rapide par égalité.

Dans perf_sales, product (200 valeurs distinctes) a beaucoup de répétitions, donc un index UNIQUE n'y est pas possible. sale_id, en revanche, est la clé primaire et unique, donc tu peux en construire un.

L'exercice suivant crée un index UNIQUE sur sale_id, confirmant qu'une colonne unique accepte l'index et que les recherches par égalité sur cette colonne l'utilisent.

-- Index UNIQUE (exemple séparé en lecture seule)
DROP INDEX IF EXISTS ix_u_demo;
CREATE UNIQUE INDEX ix_u_demo ON perf_sales(sale_id);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE sale_id = 12345;

-- Essayer d'ajouter UNIQUE sur une colonne avec doublons est rejeté
-- CREATE UNIQUE INDEX ix_bad ON perf_sales(product);
--> UNIQUE constraint failed: perf_sales.product
-- product a 200 valeurs distinctes répétées sur 50 000 lignes, donc ne peut pas être rendu UNIQUE

Imagine l'exigence : « sale_id est unique par vente, donc on veut empêcher les doublons et accélérer les recherches mono-ligne. » C'est le dernier exercice de l'article.

① Commence par DROP INDEX IF EXISTS ix;.

② Crée un index UNIQUE nommé ix sur la colonne sale_id de perf_sales (ça marche parce que sale_id est unique).

③ Exécute ensuite EXPLAIN QUERY PLAN sur une requête qui filtre par sale_id = 12345 et sélectionne sale_id et amount, et confirme que la recherche mono-ligne passe par l'index.

Astuces — quand ajouter un index

Règle empirique : un index bat un SCAN quand tu peux restreindre le résultat à environ 15-20 % ou moins de la table. Au-delà, l'optimiseur choisit souvent SCAN automatiquement de toute façon.

- À ajouter : recherches par égalité ou par plage sur une colonne d'une grosse table, clés primaires / clés étrangères / colonnes UNIQUE, clés de JOIN, colonnes référencées par ORDER BY / GROUP BY

- À éviter : petites tables de quelques milliers de lignes ou moins, colonnes peu sélectives comme gender, tables à fortes écritures, LIKE '%foo%' (un joker en tête ne peut pas utiliser d'index)

Le flux de travail de base : vérifie le plan SCAN / SEARCH avec EXPLAIN QUERY PLAN et ajoute des index uniquement aux requêtes lentes.