Index avancés : composites, couvrants, index ignoré

Maîtrisez la règle du préfixe gauche, les index couvrants et les pièges qui rendent un index invisible au planificateur.

11 min · 3 questions

Un index composite ordonne les lignes selon plusieurs colonnes, dans l'ordre déclaré. La règle décisive est celle du préfixe gauche : un index sur (a, b) peut servir un filtre sur a, ou sur a et b, mais jamais sur b seul. L'ordre des colonnes est donc un choix de conception, pas un détail. Créez d'abord le jeu de données ci-dessous.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000),
  (4, 'Edsger', 1, 90000);

Créons un index sur (department_id, salary). Un filtre qui commence par department_id déclenche un SEARCH. Mais un filtre portant uniquement sur salary casse le préfixe gauche : le planificateur ne peut pas sauter directement aux bonnes lignes et retombe sur un SCAN. Comparez les deux plans.

CREATE INDEX idx_dept_salary
  ON employees (department_id, salary);

-- Prefixe gauche present : index utilise
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
  AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary

-- Colonne de tete absente : index ignore
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employees

Un index couvrant contient toutes les colonnes lues par la requête. Le planificateur répond alors directement depuis l'index, sans ouvrir la table : c'est le sens de USING COVERING INDEX. Ici, l'index (department_id, salary) couvre une requête qui ne lit que ces deux colonnes. Souvenez-vous que l'id (le rowid) est implicitement présent dans chaque index, donc il est lui aussi couvert gratuitement.

-- Toutes les colonnes lues sont dans l'index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)

Quiz de validation

Vérifiez que vous avez bien retenu les points clés de cette leçon.

1. Avec un index composite sur (department_id, salary), quelle requête ne peut PAS l'exploiter ?
   • WHERE department_id = 1
   • WHERE department_id = 1 AND salary > 60000
   • WHERE salary > 60000
   • WHERE department_id = 1 AND name = 'Ada'
2. Qu'est-ce qu'un index couvrant ?
   • Un index qui contient toutes les colonnes lues, évitant d'ouvrir la table
   • Un index qui couvre plusieurs tables à la fois
   • Un index recréé automatiquement après chaque INSERT
   • Un synonyme d'index unique sur la clé primaire
3. Laquelle de ces conditions empêche l'usage d'un index simple sur email ?
   • WHERE email = 'a@b.co'
   • WHERE email > 'm'
   • WHERE lower(email) = 'a@b.co'
   • WHERE email IN ('a@b.co', 'c@d.co')