SQLite : index et performances

Comprendre pourquoi une requête est lente et la corriger. Les exemples utilisent les tables clients et commandes.

Les index

Un index accélère la recherche, au prix d’un peu d’espace et d’écritures plus lentes. Sans index, SQLite parcourt toute la table (SCAN).

CREATE INDEX idx_commandes_client ON commandes (client_id);
CREATE INDEX idx_commandes_statut_date ON commandes (statut, passee_le);   -- index composé
CREATE UNIQUE INDEX uq_clients_nom_ville ON clients (nom, ville);
CREATE INDEX idx_commandes_payees ON commandes (passee_le) WHERE statut = 'payee';   -- index partiel
CREATE INDEX idx_clients_email_min ON clients (lower(email));                        -- index sur expression
.indexes commandes
PRAGMA index_list(commandes);
PRAGMA index_info(idx_commandes_statut_date);
DROP INDEX idx_commandes_payees;

Les colonnes de WHERE, de jointure et de ORDER BY fréquents sont de bonnes candidates.
SQLite n’indexe pas automatiquement les clés étrangères : indexer client_id de commandes pour accélérer les jointures et les suppressions en cascade.
Dans un index composé, l’ordre des colonnes compte : (statut, passee_le) sert aux filtres sur statut, ou sur statut et passee_le, mais pas sur passee_le seul.
Les clés primaires et les contraintes UNIQUE sont indexées automatiquement.

`EXPLAIN QUERY PLAN` : lire le plan

EXPLAIN QUERY PLAN SELECT * FROM commandes WHERE client_id = 1;

Mot du plan	Signification
`SCAN commandes`	Parcours complet de la table
`SEARCH commandes USING INDEX idx_commandes_client (client_id=?)`	Recherche par index
`SEARCH … USING INTEGER PRIMARY KEY (rowid=?)`	Recherche par la clé primaire
`USE TEMP B-TREE FOR ORDER BY`	Tri en mémoire ou fichier temporaire : un index adapté peut l’éviter
`COVERING INDEX`	L’index contient toutes les colonnes demandées : la table n’est pas lue

Comparer avant et après la création d’un index :

EXPLAIN QUERY PLAN SELECT * FROM commandes WHERE statut = 'payee';
CREATE INDEX idx_commandes_statut ON commandes (statut);
EXPLAIN QUERY PLAN SELECT * FROM commandes WHERE statut = 'payee';

Suggérer des index : `.expert`

.expert
SELECT * FROM commandes WHERE client_id = 1 AND statut = 'payee';

La commande .expert analyse la requête et propose les index qui l’accéléreraient.

Mesurer

.timer on
SELECT COUNT(*) FROM commandes;
.timer off
.stats on          -- statistiques détaillées après chaque requête
.stats off

Statistiques de l’optimiseur

ANALYZE;                   -- calcule les statistiques de sélectivité
PRAGMA optimize;           -- le fait seulement si utile (à lancer avant de fermer la connexion)

Réglages (`PRAGMA`)

Propres à chaque connexion, sauf journal_mode (enregistré dans le fichier) :

PRAGMA journal_mode = WAL;        -- lectures simultanées à une écriture
PRAGMA synchronous = NORMAL;      -- plus rapide, sûr en mode WAL
PRAGMA cache_size = -64000;       -- cache de 64 Mo (valeur négative = Ko)
PRAGMA temp_store = MEMORY;       -- fichiers temporaires en mémoire
PRAGMA mmap_size = 268435456;     -- lecture par projection mémoire (256 Mo)
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

Insertions en masse : une seule transaction

Chaque instruction hors transaction est validée séparément (écriture sur disque). Grouper les insertions :

BEGIN;
INSERT INTO clients (nom, email) VALUES ('Client 1', 'c1@exemple.com');
INSERT INTO clients (nom, email) VALUES ('Client 2', 'c2@exemple.com');
-- … des milliers d'insertions
COMMIT;

Le gain peut être de plusieurs ordres de grandeur.

Réflexes pour une requête lente

EXPLAIN QUERY PLAN : repérer SCAN sur une grande table.
Ajouter l’index sur les colonnes de filtre, de jointure et de tri (.expert aide).
Ne sélectionner que les colonnes nécessaires ; LIMIT et pagination.
Éviter les fonctions sur une colonne indexée dans le WHERE (utiliser un index sur expression).
Grouper les écritures en transactions et activer le mode WAL.
Lancer PRAGMA optimize régulièrement.

Pour aller plus loin

Manuel : Query Planner et PRAGMA
Sauvegarde : Sauvegarde et maintenance