Statistiques et optimiseur — ANALYZE et sélectivité

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

Cet article parle de la façon dont la base décide quel index utiliser.

L'élément clé : les statistiques.

Les statistiques sont des faits de distribution sur tes données — « combien de lignes la table a », « combien de valeurs distinctes une colonne contient » — et tu les collectes avec la commande ANALYZE.

À partir de ces statistiques, la base calcule la sélectivité (l'estimation de la fraction de lignes qui survit à une condition) et décide si elle utilise un index ou lit toute la table.

Tu vas travailler avec la table de ventes perf_sales (50 000 lignes).

Elle a des colonnes aux cardinalités très différentes : emp_id (30 commerciaux distincts), region (4 valeurs : East / West / North / South), product (200 valeurs, P001 à P200), et status (le plus souvent paid, plus pending et refunded).

Tu vas observer comment le plan change avant et après ANALYZE.

Avant les exercices, vérifie les définitions de colonnes, un échantillon des données, et comment les valeurs sont réparties sur chaque colonne de perf_sales. La génération des données prend un instant, donc le premier passage peut faire une pause de quelques secondes.

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

② Utilise SELECT * FROM perf_sales LIMIT 5; pour prévisualiser les 5 premières lignes.

③ Compte les lignes par valeur de region pour voir comment les valeurs sont réparties (cardinalité).

ANALYZE et sqlite_stat1 — collecter les statistiques

Exécute ANALYZE et la base parcourt les tables et les index, collecte des statistiques et les enregistre dans une table interne appelée sqlite_stat1.

sqlite_stat1 enregistre des faits comme « combien de lignes en moyenne correspondent à une seule valeur de cet index ».

Exécuter ANALYZE construit des statistiques ancrées dans tes données réelles, ce qui aide l'optimiseur à choisir des plans d'exécution précis.

Utilise ANALYZE seul pour couvrir toutes les tables, ou ANALYZE nom_table pour cibler une table précise.

-- Indexer deux colonnes aux cardinalités différentes (status=3 valeurs / qty=20 valeurs)
DROP INDEX IF EXISTS ix_status;
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_status ON perf_sales(status);
CREATE INDEX ix_qty    ON perf_sales(qty);

-- Collecter les statistiques et regarder ce qui est stocké (lignes moyennes par valeur)
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1
WHERE tbl = 'perf_sales' ORDER BY idx;
--> ix_status : une ligne comme 50000 16667 (environ 16667 lignes par valeur)
--> ix_qty :    une ligne comme 50000 2500 (environ 2500 lignes par valeur)

Tu vas réellement collecter les statistiques qui capturent « à quel point chaque index peut restreindre » et lire le résultat. Effectue la création des index, la collecte des statistiques et la vérification en une seule exécution. (Exécute correctement pour révéler l'explication.)

① Supprime tout index du même nom avec DROP INDEX IF EXISTS, puis utilise CREATE INDEX pour construire un index sur emp_id et un autre sur region.

② Exécute ANALYZE; pour collecter les statistiques.

③ Depuis sqlite_stat1, tire les lignes où tbl vaut perf_sales et regarde la colonne stat pour chaque index (le total de lignes plus la moyenne de lignes par valeur).

La sélectivité change le plan — colonnes qui sont rentables versus celles qui ne le sont pas

La sélectivité est l'estimation de « quelle fraction de toutes les lignes survit après un filtre ».

Plus cette fraction est petite, plus la sélectivité est forte et plus l'index est rentable.

emp_id = 7 restreint à environ 1 667 lignes (autour de 3 % de la table), donc forte sélectivité et l'index vaut la peine.

À l'inverse, status = 'paid' laisse environ 40 000 lignes (autour de 80 % de la table), donc la sélectivité est faible et lire chaque ligne dans l'ordre bat le fait de sauter une ligne à la fois dans l'index.

Une fois les statistiques en place, l'optimiseur (le planificateur de requêtes) lit la sélectivité et compare le coût d'un plan par index versus celui d'un parcours complet.

Avec des index composites ou plusieurs candidats en jeu, il s'appuie sur les statistiques pour préférer « l'index qui restreint à moins de lignes ».

L'exercice suivant montre que même avec la même table et les mêmes index, le plan peut changer selon la sélectivité de la colonne du WHERE et selon que les colonnes que tu sélectionnes sont dans l'index ou non.

-- Indexer une colonne à forte sélectivité (amount : presque unique) et une à faible (status : 3 valeurs),
-- puis après ANALYZE comparer les plans pour des requêtes de même forme
DROP INDEX IF EXISTS ix_amount;
DROP INDEX IF EXISTS ix_status;
CREATE INDEX ix_amount ON perf_sales(amount);
CREATE INDEX ix_status ON perf_sales(status);
ANALYZE;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount = 500000;
--> SEARCH perf_sales USING INDEX ix_amount (amount=?)

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'paid';
--> la faible sélectivité rend possible un plan tendant vers SCAN

Compare « une condition à laquelle presque aucune ligne ne correspond » avec « une condition à laquelle presque chaque ligne correspond », formulées comme des requêtes qui sélectionnent une colonne qui n'est pas dans l'index. Avec COUNT(*), SQLite peut finir de compter en n'utilisant que l'index, donc l'index est utilisé quelle que soit la sélectivité. Pour voir SCAN gagner sur un filtre à faible sélectivité, cet exercice utilise la forme SELECT sale_id, amount — en tirant `amount`, qui n'est pas dans l'index.

① Supprime tout index du même nom avec DROP INDEX IF EXISTS, construis un index sur emp_id et un autre sur status, puis exécute ANALYZE; pour collecter les statistiques.

② Ajoute EXPLAIN QUERY PLAN et vérifie le plan pour la requête qui sélectionne sale_id et amount pour les lignes avec emp_id = 7 (forte sélectivité : environ 1 667 lignes / 3 %).

③ Ajoute ensuite EXPLAIN QUERY PLAN et vérifie le plan pour la même forme avec status = 'paid', et lis comment le côté emp_id et le côté status se répartissent entre SEARCH et SCAN (faible sélectivité : environ 40 000 lignes / 80 %).

Sur la console de ce cours, la faible sélectivité peut quand même conserver un INDEX SCAN

Sur la console navigateur ici (SQLite exécuté en mémoire), le plan du status = 'paid' de l'étape ③ peut rester sur SEARCH perf_sales USING INDEX ix_status (status=?). Voici pourquoi.

- Elle tourne en mémoire : sur une base sur disque, l'écart entre « E/S aléatoires via un index » et « un SCAN séquentiel » est grand, c'est pour ça qu'une faible sélectivité favorise SCAN. En mémoire, les deux sont rapides et l'écart se réduit.

- Les entrées d'index sont triées par valeur : ix_status est ordonné par status, donc le bloc 'paid' est lu comme une plage contiguë — pas d'accès aléatoire.

- L'accès à la table par rowid est bon marché : SQLite peut récupérer une ligne directement via son rowid interne, donc même les lectures de table pilotées par l'index restent raisonnablement bon marché.

Le comportement d'un basculement net vers `SCAN` quand la sélectivité chute est plus facile à observer sur PostgreSQL ou Oracle, des SGBDR avec un vrai stockage sur disque et un optimiseur par coût complet. Retiens de cet exercice qu'il y a un principe en jeu : même avec des index identiques, la combinaison de la sélectivité et des colonnes sélectionnées peut faire basculer le choix de l'optimiseur. Le seuil exact entre SCAN et SEARCH bouge avec le moteur, la présence de disque et la précision des statistiques.

Optimisation par coût et variables de liaison — internes des autres bases

La machinerie que tu as vue — « estimer la sélectivité à partir des statistiques, puis choisir le plan le moins cher » — s'appelle un optimiseur par coût (CBO).

Dans les grandes bases comme Oracle et SQL Server, cette machinerie a plus de couches à l'intérieur.

L'une est le parsing (analyse de l'instruction SQL) : analyser une instruction SQL pour la première fois et construire un plan est un hard parse, tandis que réutiliser un plan déjà analysé est un soft parse.

Une autre, ce sont les variables de liaison (bind variable : un mécanisme qui ne place pas les valeurs directement dans le SQL mais laisse un emplacement comme ? et passe la valeur à l'exécution), conjuguées au bind peeking — jeter un œil à la valeur reçue par l'optimiseur et choisir un plan selon le biais de cette valeur.

Ce sont des mécanismes internes aux moteurs d'exécution des grandes bases. Connaître les concepts rend les discussions de tuning bien plus faciles à suivre.

Sur la console navigateur de ce cours, tu peux réellement observer comment le plan réagit à la sélectivité (section précédente), mais le basculement entre hard et soft parses et le comportement du bind peeking ne peuvent pas être reproduits ici comme l'explique la callout ci-dessous, donc tu parcourras les concepts avec un schéma et un échantillon de code en lecture seule.

Hard / soft parses et bind peeking sont des internes de style Oracle

Le basculement entre hard et soft parses et le bind peeking (rechoisir le plan selon le biais de la valeur passée) vivent à l'intérieur des moteurs d'exécution des grandes bases comme Oracle et SQL Server.

La console navigateur de ce cours n'expose pas les vues nécessaires pour observer ces états internes (pense au V$SQL d'Oracle), donc il n'y a aucun moyen de les démontrer dans la console.

Ici tu auras le concept à partir du schéma et de l'échantillon de code en lecture seule ci-dessous.

En revanche, la collecte de statistiques avec ANALYZE et la façon dont le plan réagit à la sélectivité, couvertes plus tôt, sont observables pour de vrai.

Le cœur de l'optimisation par coût — « statistiques -> sélectivité -> choix du plan » — est quelque chose que tu peux confirmer en pratique dans la console de ce cours, donc maîtrise-le d'abord.

-- Une idée de ce que ça donne dans Oracle (lecture seule ; ne pas exécuter sur la console de ce cours)
-- Les variables de liaison passent la valeur plus tard (:s est rempli à l'exécution)
-- SELECT * FROM perf_sales WHERE status = :s;
--
-- Des chaînes SQL identiques réutilisent le plan = soft parse
-- Même légèrement différentes, ça le reconstruit = hard parse
-- Dans Oracle tu inspecterais le parsing via V$SQL.SQL_TEXT, mais pas ici

-- Ce que tu peux réellement observer sur la console de ce cours :
-- une fois les statistiques collectées, l'estimation de sélectivité est ancrée dans les vraies données
ANALYZE;
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'pending';
--> SEARCH perf_sales USING INDEX ... (status=?)

De l'optimisation par coût, la partie que la console de ce cours te laisse observer est « collecter les statistiques fait évoluer l'estimation », et tu vas la vérifier sur la colonne product (200 valeurs, forte sélectivité). Effectue la création de l'index et les vérifications de plan avant et après collecte des statistiques en une seule exécution.

① Supprime tout index du même nom avec DROP INDEX IF EXISTS, puis crée un index sur la colonne product.

② Ajoute EXPLAIN QUERY PLAN et vérifie le plan pour une requête qui compte les lignes où product vaut 'P050', sans statistiques en place.

③ Exécute ANALYZE; pour collecter les statistiques, puis relance le EXPLAIN QUERY PLAN de la même requête. Comme product a 200 valeurs et une forte sélectivité, le plan devrait continuer d'utiliser l'index après ANALYZE aussi.