Avancé · 20 min

DDL, contraintes et index : structurer et accélérer une base

CREATE TABLE et contraintes (clé primaire, étrangère, UNIQUE, NOT NULL) pour structurer une base, et EXPLAIN QUERY PLAN pour comprendre le rôle d'un index.

Contexte

Une base de données n'est pas qu'un ensemble de requêtes : quelqu'un doit d'abord définir SA STRUCTURE — les tables, leurs colonnes, les règles qu'elles doivent respecter — puis s'assurer que les requêtes fréquentes restent rapides à mesure que le volume grandit.

Explication

CREATE TABLE définit une table et ses contraintes : NOT NULL (valeur obligatoire), UNIQUE (aucun doublon), PRIMARY KEY (identifiant, implicitement NOT NULL + UNIQUE), REFERENCES (clé étrangère, la valeur doit exister dans l'autre table), CHECK (condition arbitraire sur la valeur).

CREATE TABLE table (
    id INTEGER PRIMARY KEY,
    col1 TEXT NOT NULL,
    col2 INTEGER REFERENCES autre_table(id),
    col3 INTEGER CHECK (col3 > 0)
);

Un index accélère les recherches et tris sur une colonne, au prix d'un espace disque et d'un coût supplémentaire à l'écriture — utile sur une colonne souvent filtrée ou jointe, pas sur toutes. EXPLAIN QUERY PLAN devant une requête montre si SQLite utilise un index (SEARCH … USING INDEX) ou parcourt toute la table (SCAN) :

EXPLAIN QUERY PLAN SELECT * FROM produits WHERE categorie_id = 3;

Pièges à connaître :

Ajouter une contrainte NOT NULL/UNIQUE à une table déjà remplie échoue si des lignes existantes la violent — c'est justement ce que la contrainte est censée empêcher désormais.
L'ordre des colonnes d'un index composite compte : il ne sert pleinement que si les colonnes de recherche apparaissent dans le même ordre que dans l'index (règle du préfixe gauche).

Exemple guidé

Sur la base boutique, l'historique des commandes d'un client (WHERE client_id = ? ORDER BY date_commande DESC) est une requête fréquente : un index composite sur (client_id, date_commande) — dans cet ordre — sert à la fois au filtre et au tri :

CREATE INDEX idx_commandes_client_date ON commandes(client_id, date_commande);

Même logique qu'un index simple (pour accélérer une recherche par ville), mais sur deux colonnes : SQLite peut alors sauter directement aux lignes du bon client déjà triées, au lieu de tout parcourir puis tout trier.

À retenir

CREATE TABLE définit la structure et les règles (NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, CHECK). Un index accélère une recherche/un tri fréquent sur une colonne, au prix de l'espace et de l'écriture. EXPLAIN QUERY PLAN montre si un index est utilisé (SEARCH) ou non (SCAN). L'ordre des colonnes d'un index composite doit suivre l'ordre d'utilisation.

Exercices de cette leçon

Faire les exercices →