EXPLAIN et plans d'exécution : lire ce que fait la base

Décodez le plan que le planificateur SQLite choisit réellement pour vos requêtes avec EXPLAIN QUERY PLAN.

10 min · 3 questions

SQL est déclaratif : vous décrivez le résultat, pas la manière de l'obtenir. C'est le planificateur de requêtes qui décide comment lire les données. Pour l'observer, SQLite offre deux commandes : EXPLAIN QUERY PLAN donne un plan de haut niveau lisible, tandis que EXPLAIN seul décharge le bytecode de la machine virtuelle VDBE. Installez sqlite3 en ligne de commande, ou ouvrez sqliteonline.com dans votre navigateur, et rejouez chaque exemple qui suit.

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

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

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

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

Lancez EXPLAIN QUERY PLAN devant n'importe quelle requête SELECT. Chaque ligne du résultat décrit comment une table est lue. Deux mots-clés dominent : SCAN signifie que la table est parcourue entièrement, ligne par ligne, alors que SEARCH indique un accès ciblé guidé par un index. Sans index adapté, une simple égalité provoque déjà un parcours complet.

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Resultat : SCAN employees
-- (aucun index : toute la table est lue)

Créez un index sur la colonne filtrée, puis relancez exactement la même requête : le plan bascule de SCAN vers SEARCH ... USING INDEX. D'autres mentions sont précieuses : USE TEMP B-TREE révèle un tri ou un regroupement matérialisé en mémoire (souvent le signe qu'un index d'ordre manque), et USING COVERING INDEX signale que la table n'a même pas été ouverte.

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

Quiz de validation

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

1. En SQLite, que renvoie EXPLAIN QUERY PLAN comparé à EXPLAIN seul ?
   • Le bytecode VDBE, instruction par instruction
   • Un plan de haut niveau (SCAN, SEARCH, tri temporaire)
   • Le temps d'exécution réel mesuré de la requête
   • La liste des index existants sur la table
2. Dans un plan SQLite, que signifie la ligne SCAN employees ?
   • La table est parcourue entièrement, ligne par ligne
   • Un index couvrant est utilisé
   • Une recherche ciblée est faite via un index
   • La table est chargée en cache mémoire
3. À quoi sert la commande ANALYZE ?
   • Elle reconstruit tous les index de la base
   • Elle collecte des statistiques dans sqlite_stat1 pour guider le planificateur
   • Elle exécute la requête et en mesure la durée
   • Elle verrouille la table pendant l'analyse