SQLite für iOS prüfen: zusammengesetzte Indizes und Scan-Budgets

In der Testdatenbank öffnet sich die Bestellliste eines Kunden sofort. Nach dem Import historischer Daten wird sie jedoch spürbar langsam. Für Entwicklungsstudios, die mehrere Projekte auf einem Cloud-Mac ausliefern, sollte die Abnahme deshalb zunächst anhand von Abfrageplänen mit festen Testdaten erfolgen – nicht durch wiederholte Zeitmessungen auf wechselnden Rechnern. Mit festen Daten lassen sich fehlende Indizes gezielt erkennen. Beim Rechnerwechsel werden dagegen leicht Cache-Effekte und Lastschwankungen als Optimierungserfolg verbucht. Anhand einer Bestellabfrage entsteht hier ein Prüfablauf von der Vorprüfung in der Kommandozeile bis zur abschließenden Kontrolle über die tatsächliche Datenbankverbindung der iOS-App.

1. Erst den Abfragevertrag festlegen, dann über Indizes sprechen

Die Liste soll ausschließlich abgeschlossene Bestellungen eines bestimmten Kunden liefern: absteigend nach Erstellungszeitpunkt, bei gleichem Zeitpunkt absteigend nach ID und mit höchstens zwanzig Ergebnissen pro Abruf. Der Test muss Filterung, Reihenfolge und Ergebnismenge prüfen, nicht nur die Laufzeit.

Bereiten Sie drei Parametersätze vor: einen Kunden mit Bestellungen, einen nicht vorhandenen Kunden und einen Kunden, auf den besonders viele Datensätze entfallen. Kleine, gleichmäßig verteilte Datenbestände eignen sich zur Prüfung der Logik, bilden aber nicht die tatsächliche Geschäftsverteilung ab. In Produktionsdaten kann ein Großteil der Datensätze auf wenige Großkunden entfallen.

Dokumentieren Sie sqlite_version(), das Tabellenschema, die Indexdefinitionen und das Verfahren zur Erzeugung der Statistiken. Das auf dem Mac vorinstallierte Kommandozeilenprogramm und die App verwenden möglicherweise unterschiedliche Datenbank-Engines. Gerade wenn das Projekt SQLite selbst einbindet, dürfen die Pläne beider Umgebungen nicht als gleichwertiger Befund behandelt werden.

Ein Abfrageplan ist eine Entscheidung des Optimierers, keine stabile Textschnittstelle. Die wortwörtliche Übereinstimmung der gesamten Ausgabe darf deshalb kein versionsübergreifendes Freigabekriterium sein.

2. Eine Test-Fixture ohne Zugriff auf Kundendatenbanken erstellen

Führen Sie das folgende Skript im Terminal aus. Es legt ein eigenes temporäres Verzeichnis an und stellt keine Verbindung zur Produktionsdatenbank her. Die Datensatz-IDs sind fest vorgegeben; wiederholte Ausführungen erzeugen denselben logischen Datenbestand. SQL-Datei und Datenbank liegen im selben Verzeichnis, damit sich die Prüfung bei der Übergabe erneut ausführen lässt.

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"

Speichern Sie zunächst die Terminalausgabe. Führen Sie anschließend die eigentliche Abfrage aus und prüfen Sie die Ergebnisse. Die Testdaten enthalten bewusst identische Erstellungszeitpunkte, damit sich die Wirkung des zweiten Sortierschlüssels überprüfen lässt. Wer nur den neuesten Datensatz kontrolliert, übersieht mögliche Verschiebungen in der Reihenfolge von Datensätzen mit demselben Zeitstempel.

3. Zugriffspfade verstehen statt Planzeichenfolgen auswendig lernen

Ohne Index zeigt der Plan in der Regel einen Scan der Bestelltabelle und eine temporäre Struktur für die Sortierung. Prüfen Sie nach dem Anlegen des Index, ob die Suche über Kunde und Status erfolgt und ob weiterhin eine zusätzliche Sortierung nötig ist. Die genaue Formulierung hängt von der SQLite-Version ab; eine dauerhaft identische Ausgabe darf nicht vorausgesetzt werden.

Im zusammengesetzten Index stehen zuerst die Spalten mit Gleichheitsbedingungen, danach die Sortierspalten. So kann die Engine im passenden Wertebereich direkt die ersten zwanzig Einträge lesen. Der Index deckt außerdem die in diesem Beispiel abgefragten Spalten vollständig ab. Werden später große Datenfelder ergänzt, kann diese Abdeckung entfallen. Dann ist eine erneute Abnahme nötig, statt weiterhin auf den alten Bericht zu verweisen.

Ein SCAN ist nicht automatisch ein Fehler: Ein geordneter Scan eines abdeckenden Index ist etwas anderes als das zeilenweise Filtern einer großen Tabelle. Umgekehrt garantiert SEARCH keinen geringen Aufwand. Bedingungen mit geringer Selektivität können weiterhin dazu führen, dass viele Datensätze gelesen werden.

Indizes sind für häufig genutzte Listen sinnvoll, sollten aber nicht wahllos für sämtliche Felder angelegt werden. Sie erhöhen den Schreibaufwand und den Speicherbedarf. Führen Sie bei der Abgabe einer Änderung auch Tests für Einfügen, Aktualisieren und Löschen aus, um sicherzustellen, dass die Leseoptimierung die für die Auslieferung vereinbarten Budgets nicht sprengt.

4. Das Scan-Budget über die tatsächliche Verbindung prüfen

Nach erfolgreicher Prüfung in der Kommandozeile importieren Sie die Fixture in die App-Testumgebung. Führen Sie dieselbe parametrisierte Abfrage mit der Datenbankabstraktion aus, die das Projekt tatsächlich verwendet. Die Datentypen der gebundenen Parameter müssen übereinstimmen. Lesen Sie das Statement vollständig bis zum Ende aus; die Zählung darf nicht bereits nach der ersten Zeile abgebrochen werden.

Wenn die zugrunde liegende Verbindung zugänglich ist, setzen Sie vor der Ausführung mit sqlite3_stmt_status den Zähler SQLITE_STMTSTATUS_FULLSCAN_STEP zurück und lesen ihn nach der Ausführung aus. Ermitteln Sie zusätzlich über SQLITE_STMTSTATUS_SORT die Anzahl der Sortieroperationen. Der erste Zähler erfasst Schritte im Zusammenhang mit vollständigen Tabellenscans. Der zweite misst weder die Laufzeit noch die Anzahl sortierter Zeilen.

Auch wenn beide Zähler null anzeigen, ist die Abfrage nicht kostenlos. Es können weiterhin zahlreiche Indexzugriffe stattfinden. Ergänzend lässt sich SQLITE_STMTSTATUS_VM_STEP erfassen und zusammen mit der Anzahl zurückgegebener Zeilen bewerten. Stellt die Datenbankabstraktion das Statement-Handle nicht bereit, ergänzen Sie zunächst einen Testadapter. Aus Anwendungslogs extrahierte Laufzeiten sind kein Ersatz für Scan-Statistiken.

Das Budget muss aus einer geprüften Baseline der festen Fixture abgeleitet werden, nicht aus pauschal übernommenen Grenzwerten. Setzen Sie die Zähler vor jeder Messung zurück, damit bei der Wiederverwendung eines Prepared Statements keine Werte aus der vorherigen Ausführung mitgezählt werden.

5. Änderungen der Datenverteilung in die Abnahme aufnehmen

Für die Abnahme sollten mindestens zwei Datenbanken erhalten bleiben: eine mit gleichmäßiger Verteilung zur Prüfung der grundlegenden Zugriffspfade und eine mit schiefer Verteilung zur Simulation von Großkunden. Prüfen Sie in beiden Datenbanken die Obergrenze von zwanzig Zeilen, eine stabile Sortierung, leere Ergebnisse und unterschiedliche Statusparameter.

Statistiken beeinflussen den Abfrageplan. Dokumentieren Sie daher getrennt die Ergebnisse der neu angelegten Datenbank und die Ergebnisse nach ANALYZE. Erzeugt das Produkt im Betrieb keine Statistiken, darf sich die Abnahme nicht auf den Idealzustand mit vorhandenen Statistiken beschränken. Erzwingen Sie auch keinen bestimmten Index, nur um einen ansprechenden Plan zu erhalten und die tatsächliche Entscheidung des Optimierers zu verdecken.

Archivieren Sie abschließend SQL, Engine-Version, Beschreibung der Datenverteilung, Indexdefinitionen, Pläne und Zählerstände. Bei neu auftretenden Regressionen werden zuerst diese Belege verglichen und erst danach die Laufzeiten. So kann das Studio auch nach einem Wechsel des Cloud-Macs oder einer Projektverzögerung anhand derselben Fixture erklären, ob eine Änderung den Zugriffspfad verändert hat oder lediglich zufällig auf einen warmen Cache traf.

Häufig gestellte Fragen