SQLite a la réputation d'être une base de données simple qui « fonctionne et c'est tout ». Cette réputation est méritée dans les cas où il est utilisé comme base de données intégrée dans des applications de bureau et mobiles, où l'ensemble de données est petit et où le planificateur de requêtes a un travail facile. Dans D1, ce contexte change : des tables avec des centaines de milliers de lignes, plusieurs requêtes par demande utilisateur et un modèle de tarification qui facture pour chaque ligne que la banque lit lors de l'exécution d'une requête, et non pour chaque ligne renvoyée à l'application. Une requête mal optimisée en production n'est pas seulement lente, elle est coûteuse et le coût augmente linéairement avec le volume de données.
EXPLAIN QUERY PLAN : diagnostic avant optimisation
Avant de créer un index ou de réécrire une requête, exécutez EXPLAIN QUERY PLAN sur la requête problématique. Sur D1, vous pouvez le faire à distance via wrangler d1 execute NOME_DO_BANCO --remote --command "EXPLAIN QUERY PLAN SELECT ...", ou localement avec sqlite3 pointant vers le fichier .wrangler/state/v3/d1/.
Le résultat est une liste d’opérations que le planificateur de requêtes effectuera. "SCAN TABLE commandes" est le signal d'avertissement : cela signifie que la base de données analysera toutes les lignes de la table. "RECHERCHER les commandes À L'AIDE DE L'INDEX idx_orders_user_id (user_id=?)" est ce que vous voulez voir : la base de données utilise l'index pour trouver directement les lignes pertinentes. « RECHERCHER les commandes USING INDEX idx_orders_user_date (user_id=?AND date>?) » indique qu'un index composite est exploité à la fois pour le filtre d'égalité et le filtre de plage.
L'erreur la plus courante consiste à créer des index après l'apparition de problèmes en production. Le EXPLAIN QUERY PLAN doit faire partie du processus de développement : il est exécuté sur chaque requête qui touche des tables comportant plus de quelques milliers de lignes avant le premier déploiement. Le coût d’un index inutile est l’espace de stockage. Le coût d'une requête sans index en production se mesure en argent.
Les modèles qui génèrent l'analyse de table dans D1
Quatre situations récurrentes provoquent une analyse complète de la table. La première est la plus évidente : absence d'index sur la colonne utilisée dans WHERE. SELECT * FROM orders WHERE user_id = ? sans index sur user_id lit toutes les lignes du tableau pour trouver les lignes de l'utilisateur. La correction est simple : CREATE INDEX idx_orders_user_id ON orders(user_id).
La deuxième situation est la combinaison de WHERE avec ORDER BY non couverte par un index composite. SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC peut utiliser l'index de user_id pour filtrer, mais il doit ensuite trier le résultat en mémoire — une opération appelée tri de fichiers. Un index composite sur (user_id, created_at) élimine le tri de fichiers car les données sont déjà triées par created_at au sein de chaque user_id.
Le troisième est LIKE avec un caractère générique initial. WHERE title LIKE '%termo%' ne peut utiliser aucun index : le caractère générique au début de la chaîne empêche la base de données d'utiliser l'ordre d'index pour supprimer des lignes. Pour une recherche de texte générique recto-verso, utilisez les tables virtuelles FTS5 : CREATE VIRTUAL TABLE posts_fts USING fts5(title, content, content=posts). Les requêtes FTS5 avec MATCH sont indexées et évolutives.
Le quatrième est la coercition de type. SQLite utilise l'affinité de type — une colonne définie comme TEXT peut stocker des entiers, et une comparaison WHERE id = 42 avec une colonne TEXT peut ne pas utiliser l'index en fonction de la façon dont les valeurs ont été saisies. Le maintien de la cohérence des types entre le schéma, les valeurs insérées et les requêtes est plus important dans SQLite que dans les bases de données avec typage strict.
N+1 en D1 et le rôle de db.batch()
Le problème N+1 a une dimension supplémentaire en D1 : chaque requête est une sous-requête, et les sous-requêtes ont une limite de 1 000 par invocation de Worker. Un point de terminaison qui recherche 50 requêtes puis effectue un SELECT pour les éléments de chaque requête exécute individuellement 51 requêtes – 51 sous-requêtes, au prix de 51 allers-retours vers la base de données, chacun ajoutant la latence du réseau au temps de réponse total.
db.batch() résout ce problème en regroupant plusieurs requêtes en une seule sous-requête. Toutes les requêtes du lot sont exécutées en un seul aller-retour vers la base de données. Le résultat est un tableau avec un élément par requête, dans le même ordre dans lequel ils ont été envoyés. Pour le modèle commandes et articles, le lot contient : la requête commandes et une requête avec IN couvrant tous les identifiants de commande. Deux sous-requêtes au total, quel que soit le nombre de requêtes renvoyées.
Les ORM qui prennent en charge D1 – tels que Drizzle ORM, qui a une intégration native – disposent d'options de chargement rapides qui créent automatiquement des requêtes avec JOIN ou batch au lieu de N+1. Cependant, le comportement par défaut de la plupart des ORM génère N+1, sauf si vous configurez explicitement le chargement rapide. Vérifier le SQL généré avec console.log dans l'environnement de développement avant de passer en production est le moyen le plus direct d'identifier ces modèles.
Le coût réel de ne pas avoir d'index : un exemple avec des chiffres
Une banque D1 avec 200 mille commandes. La requête SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20 sans index dans status effectue une analyse complète de la table : 200 mille lignes lues, 20 lignes renvoyées. À 0,001 $ par million de lectures, chaque exécution de cette requête coûte 0,0002 $.
Avec 100 000 exécutions quotidiennes de ce point de terminaison – ce qui est courant pour un panneau qui se met à jour via une interrogation ou un tableau de bord des opérations – le coût est de 20 $ par jour, 600 $ par mois, rien que pour cette requête. L'index composite CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC) change complètement de plan : la base de données lit uniquement les enregistrements avec status = 'pending' à l'aide de l'index, déjà ordonné par created_at. En supposant 5 000 requêtes en attente, la requête lit 5 000 lignes, en renvoie 20 et coûte 0,000005 $ par exécution. À 100 000 $ d'exécutions/jour, le coût tombe à 0,50 $ par jour, soit 15 $ par mois.
L'index lui-même occupe environ 5 à 10 Mo pour 200 000 lignes. À 0,75 $/Go-mois, ce stockage coûte moins de 0,01 $ par mois. La différence entre 600 $/mois et 15 $/mois en frais de lecture, pour une fraction d'un centime d'investissement dans le stockage, est le type d'optimisation qui ne devrait jamais être reportée jusqu'à « après que le trafic ait augmenté » — car lorsque le trafic augmente, le coût sera déjà engagé.
A lire aussi
- D1 en production : performances, limites et ce qui ne évolue pas seul
- Consommation de la batterie dans les applications : comment optimiser les performances mobiles -Performance logicielle : les étapes essentielles pour commencer à optimiser
- Optimisation de la base de données SQL : index, partitionnement et réglage
- Optimisation des performances mobiles : Guide complet -Optimisation des performances mobiles - Exemples réels pour les débutants