Valider SQLite pour iOS : index composites et budget de parcours

La liste des commandes d’un client s’ouvre instantanément dans la base de test, mais ralentit nettement après l’import de l’historique. Pour un studio qui livre plusieurs projets sur un Mac cloud, mieux vaut commencer par valider le plan de requête sur un jeu de données fixe que multiplier les chronométrages en changeant de machine : la première approche aide à repérer un index manquant, tandis que la seconde risque de faire passer les variations de cache et de charge pour des gains d’optimisation. Cet article s’appuie sur une requête de commandes pour établir une procédure allant des vérifications préalables en ligne de commande au contrôle sur la connexion réellement utilisée sous iOS.

1. Définir le contrat de la requête avant de parler d’index

La liste doit renvoyer uniquement les commandes terminées d’un client donné, par date de création décroissante, puis par identifiant décroissant en cas d’égalité, avec un maximum de vingt résultats par appel. Les tests doivent vérifier le filtrage, l’ordre et le nombre de résultats, pas seulement la durée d’exécution.

Préparez trois jeux de paramètres : un client ayant des commandes, un client inexistant et un client concentrant une forte proportion des commandes. Un petit jeu de données uniforme convient pour vérifier la logique, mais ne représente pas la distribution réelle de l’activité ; en production, quelques gros clients peuvent concentrer la majorité des enregistrements.

Consignez sqlite_version(), le schéma des tables, les définitions des index et la méthode de génération des statistiques. L’outil en ligne de commande fourni avec macOS et l’application peuvent utiliser des moteurs différents, notamment lorsque le projet intègre lui-même SQLite. Leurs plans ne doivent donc pas être considérés comme une seule et même conclusion.

Un plan de requête reflète un choix de l’optimiseur ; ce n’est pas une interface textuelle stable. N’imposez pas une égalité caractère par caractère de toute la sortie comme critère de validation entre versions.

2. Créer un jeu de test sans toucher à la base du client

Exécutez le script suivant dans le terminal. Il crée un répertoire temporaire indépendant et ne se connecte à aucune base de production. Les identifiants des données sont fixes : chaque exécution reproduit le même contenu logique. Le fichier SQL est placé à côté de la base pour faciliter une nouvelle exécution lors de la livraison.

set -eu
work="$(mktemp -d)"
cat > "$work/check.sql" <<'SQL'
.bail on
SELECT sqlite_version();
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
WITH RECURSIVE seq(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM seq WHERE x < 10000
)
INSERT INTO orders
SELECT x, x % 100,
       CASE WHEN x % 3 = 0 THEN 'done' ELSE 'open' END,
       x / 10
FROM seq;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX orders_customer_status_time
ON orders(customer_id, status, created_at DESC, id DESC);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
SQL
sqlite3 "$work/fixture.sqlite" < "$work/check.sql"
printf '%s\n' "$work"

Enregistrez d’abord la sortie du terminal, puis exécutez la requête elle-même pour vérifier les résultats. Plusieurs commandes partagent volontairement la même date de création afin de confirmer que la seconde clé de tri est bien prise en compte. Vérifier uniquement la commande la plus récente ne permettrait pas de détecter un ordre instable entre les enregistrements de même date.

3. Lire le chemin d’accès plutôt que mémoriser le texte du plan

Sans index, le plan indique généralement un parcours de la table des commandes et la création d’une structure temporaire pour le tri. Après la création de l’index, vérifiez que la recherche s’appuie sur le client et le statut, et qu’aucun tri supplémentaire n’est nécessaire. Le libellé exact varie selon la version de SQLite : ne supposez pas que la sortie restera toujours identique.

Dans l’index composite, les colonnes soumises à une condition d’égalité précèdent les colonnes de tri. L’objectif est que le moteur lise les vingt premiers résultats directement dans la plage visée. Cet index couvre aussi toutes les colonnes sélectionnées dans cet exemple. Si des colonnes volumineuses sont ajoutées ultérieurement à la requête, cette couverture peut disparaître : il faut alors refaire la validation plutôt que continuer à citer l’ancien rapport.

La présence de SCAN ne suffit pas à conclure à un échec : parcourir un index couvrant dans l’ordre n’équivaut pas à filtrer ligne par ligne une grande table. À l’inverse, SEARCH ne garantit pas un faible volume de travail : des conditions peu sélectives peuvent encore entraîner la lecture de nombreux enregistrements.

Un index convient à une liste consultée fréquemment, mais cela ne justifie pas d’indexer indistinctement tous les champs. Il augmente le coût des écritures et du stockage. Lors de la soumission de la modification, exécutez aussi les tests d’insertion, de mise à jour et de suppression pour vérifier que l’optimisation des lectures reste compatible avec le budget prévu pour la livraison.

4. Contrôler le budget de parcours sur la connexion réelle

Une fois les vérifications en ligne de commande réussies, importez le jeu de données dans les tests de l’application et exécutez la même requête paramétrée avec la couche d’accès à la base réellement utilisée par le projet. Conservez les mêmes types pour les paramètres liés et lisez les résultats jusqu’au bout : ne cessez pas de compter après la première ligne.

Si la connexion sous-jacente est accessible, utilisez sqlite3_stmt_status avant l’exécution pour remettre SQLITE_STMTSTATUS_FULLSCAN_STEP à zéro, puis relevez sa valeur après l’exécution. Consultez également SQLITE_STMTSTATUS_SORT pour connaître le nombre d’opérations de tri. Le premier compteur mesure les étapes liées aux parcours complets de table ; le second ne mesure ni une durée ni un nombre de lignes triées.

Même si ces deux compteurs restent à zéro, la requête n’est pas pour autant gratuite. Elle peut encore effectuer de nombreux accès à l’index. Vous pouvez aussi relever SQLITE_STMTSTATUS_VM_STEP et l’interpréter avec le nombre de lignes renvoyées. Si la couche d’accès n’expose pas le handle de l’instruction, ajoutez d’abord un adaptateur de test. N’utilisez pas les durées extraites des journaux applicatifs comme substitut aux statistiques de parcours.

Le budget doit découler d’une référence vérifiée sur le jeu de données fixe, et non d’un seuil générique repris tel quel. Réinitialisez les compteurs avant chaque mesure pour éviter que la réutilisation d’une instruction préparée n’y ajoute les valeurs de l’exécution précédente.

5. Intégrer les variations de distribution aux contrôles de livraison

Conservez au moins deux bases pour la validation : une base à distribution uniforme pour vérifier le chemin d’accès de base et une base à distribution déséquilibrée pour simuler les gros clients. Dans les deux cas, vérifiez la limite de vingt résultats, la stabilité du tri, les résultats vides et les différents paramètres de statut.

Les statistiques influencent le plan. Consignez séparément les résultats obtenus sur une base nouvellement créée et après l’exécution de ANALYZE. Si le produit ne génère pas de statistiques à l’exécution, ne validez pas uniquement le cas idéal où elles sont disponibles. Ne forcez pas non plus l’utilisation d’un index pour obtenir un plan plus flatteur au prix de masquer le choix réel de l’optimiseur.

Enfin, archivez le SQL, la version du moteur, la description de la distribution des données, les définitions des index, les plans et les compteurs. Lorsqu’une régression apparaît, comparez d’abord ces éléments avant d’examiner les temps d’exécution. Même si le studio change de Mac cloud ou si le projet prend du retard, le même jeu de test permettra encore d’expliquer si la modification a changé le chemin d’accès ou si la mesure a simplement bénéficié d’un cache chaud.

Questions fréquentes