Conception d'index — Ordre des colonnes du composite et conditions qui utilisent l'index

Le jeu de données de cet article — perf_sales (50 000 lignes)

À partir d'ici, les articles de tuning utilisent une table avec beaucoup de lignes pour observer comment les index se comportent vraiment.

Cet article couvre l'ordre des colonnes d'un index composite (un index construit sur plusieurs colonnes dans un ordre précis), la règle du préfixe gauche, et comment écrire des conditions qui peuvent utiliser l'index (aussi appelées SARGable).

Avant la pratique, jette un coup d'œil rapide aux définitions de colonnes et à la taille et un échantillon des données de perf_sales. (Exécute les requêtes correctement et l'explication sera révélée.)

① 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 de lignes.

③ Prévisualise les 5 premières lignes avec SELECT emp_id, region, product, amount, sale_date, status FROM perf_sales LIMIT 5; (puisque la table a 50 000 lignes, ne tire jamais chaque ligne avec SELECT * — utilise l'agrégation ou LIMIT pour jeter un œil).

Ordre des colonnes d'un index composite — la règle du préfixe gauche

Un index composite (tel que CREATE INDEX ix ON table(col_A, col_B), listant plusieurs colonnes dans un ordre fixe) stocke ses entrées exactement dans cet ordre.

L'index est trié comme un dictionnaire : par col_A ascendant, et au sein du même col_A par col_B ascendant.

À cause de ça, savoir si l'index aide dépend de si tes conditions couvrent les colonnes en continu à partir de celle de tête.

Cela s'appelle la règle du préfixe gauche (un index composite n'aide que les conditions spécifiées en continu à partir de la colonne de tête).

Un index sur (emp_id, sale_date) marche pour une condition sur emp_id, ou sur emp_id et sale_date ensemble, mais pas pour une condition sur sale_date seul — le emp_id de tête est absent, donc l'index n'est pas utilisé.

-- Construire un index composite sur region et product, puis comparer
-- les plans pour une condition qui inclut le region de tête
-- avec une qui utilise product seul
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, product);

-- Utilise la colonne de tête region -> ix_demo se déclenche
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';

-- Saute le region de tête et filtre par product seul -> ix_demo ne se déclenche pas
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product = 'P050';

Construis un index composite sur emp_id et sale_date sur perf_sales et confirme la règle du préfixe gauche en lisant le plan d'exécution. Recrée l'index et montre les plans dans une seule exécution de console pour que les étapes soient autonomes. (Exécute les requêtes correctement et l'explication sera révélée.)

① Commence par DROP INDEX IF EXISTS pour que l'index puisse être reconstruit même s'il en reste un du même nom.

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

③ Utilise EXPLAIN QUERY PLAN pour montrer le plan d'une requête d'agrégation filtrée par emp_id seul.

④ Montre ensuite le plan d'une requête d'agrégation filtrée par sale_date seul et compare comment le plan change par rapport à ③.

Lister les colonnes dans l'ordre égalité-puis-intervalle

Il y a un guide pour l'ordre des colonnes dans un index composite.

Mets les conditions d'égalité (un = ou IN qui épingle une colonne à une valeur) à l'avant, et les conditions d'intervalle (>, >=, <, BETWEEN, etc., qui couvrent une plage) à l'arrière.

Épingler la colonne de tête avec l'égalité garde les entrées correspondantes dans un bloc contigu au sein de l'index, donc tu peux découper ce bloc d'un seul coup.

Une condition comme WHERE emp_id = 7 AND sale_date >= '2024-01-01' colle naturellement avec l'ordre (emp_id, sale_date).

-- Indexer region (égalité) et amount (intervalle) ensemble,
-- avec la colonne d'égalité en premier et la colonne d'intervalle en second
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE region = 'West' AND amount >= 500000;

Imagine l'exigence : « On veut récapituler les ventes pour un commercial précis à partir d'une date donnée. » Les conditions sont une égalité sur emp_id et un intervalle sur sale_date. Construis un index composite sur ces deux colonnes dans l'ordre égalité-puis-intervalle et confirme avec le plan d'exécution qu'il se transforme en recherche par index.

① Exécute DROP INDEX IF EXISTS pour supprimer tout index précédent.

② Crée un index composite avec la colonne d'égalité en premier et la colonne d'intervalle en second.

③ Utilise EXPLAIN QUERY PLAN sur une requête d'agrégation qui combine l'égalité sur emp_id avec l'intervalle sur sale_date via AND, et confirme qu'elle devient une recherche par index.

Conditions qui peuvent utiliser l'index — compare la colonne telle quelle, non enveloppée

Même avec un index en place, la façon dont tu écris la condition décide si elle est utilisée.

Une condition formulée de sorte que l'index puisse la restreindre s'appelle une condition qui peut utiliser l'index (en anglais on dit aussi SARGable).

La règle de base : compare la colonne indexée telle quelle, sans l'envelopper dans aucun calcul.

Quelque chose comme WHERE emp_id = 7, où la colonne est comparée directement par égalité ou intervalle, laisse l'index se déclencher.

À l'inverse, WHERE emp_id + 0 = 7 (la colonne est dans une expression) ou WHERE substr(sale_date,1,4) = '2024' (la colonne est enveloppée dans une fonction) brise la correspondance avec l'ordre de l'index et retombe sur un parcours complet de lignes.

Garde la colonne brute sur le côté gauche de la comparaison, et si un ajustement est nécessaire, applique-le sur la valeur du côté droit.

-- Même sens, formes différentes -> plans différents
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Colonne comparée telle quelle -> l'index se déclenche, SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Colonne dans une expression -> l'index ne se déclenche pas, retombe sur SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount + 0 >= 800000;

Observe comment deux conditions de même sens — l'une comparant la colonne telle quelle et l'autre l'enveloppant dans une expression — produisent des plans d'exécution différents. Construis un index sur emp_id et compare les plans pour les deux formes.

① Exécute DROP INDEX IF EXISTS pour supprimer l'index précédent.

② Crée un index mono-colonne sur emp_id.

③ Montre le plan d'une requête d'agrégation qui compare emp_id tel quel avec l'égalité.

④ Montre ensuite le plan d'une requête d'agrégation de même sens mais avec emp_id dans une expression, et compare comment le plan change par rapport à ③.