Langsame Abfragen in D1: So diagnostizieren und optimieren

SQLite hat den Ruf, eine einfache Datenbank zu sein, die „funktioniert und das war’s“. Dieser Ruf ist in Fällen verdient, in denen es als eingebettete Datenbank in Desktop- und mobilen Anwendungen verwendet wird – wo der Datensatz klein ist und der Abfrageplaner eine leichte Aufgabe hat. In D1 ändert sich dieser Kontext: Tabellen mit Hunderttausenden Zeilen, mehrere Abfragen pro Benutzeranforderung und ein Preismodell, das für jede Zeile berechnet, die die Bank während der Ausführung einer Abfrage liest, und nicht für jede an die Anwendung zurückgegebene Zeile. Eine schlecht optimierte Abfrage in der Produktion ist nicht nur langsam, sondern auch teuer, und die Kosten steigen linear mit der Datenmenge.

ABFRAGEPLAN ERKLÄREN: Diagnose vor Optimierung

Bevor Sie einen Index erstellen oder eine Abfrage neu schreiben, führen Sie EXPLAIN QUERY PLAN für die problematische Abfrage aus. Auf D1 können Sie dies remote über wrangler d1 execute NOME_DO_BANCO --remote --command "EXPLAIN QUERY PLAN SELECT ..." oder lokal tun, wobei sqlite3 auf Datei .wrangler/state/v3/d1/ verweist.

Die Ausgabe ist eine Liste von Vorgängen, die der Abfrageplaner ausführen wird. „SCAN TABLE order“ ist das Warnsignal: Es bedeutet, dass die Datenbank alle Zeilen in der Tabelle scannt. „SUCHE Bestellungen MIT INDEX idx_orders_user_id (user_id=?)“ ist das, was Sie sehen möchten: Die Datenbank verwendet den Index, um die relevanten Zeilen direkt zu finden. „SEARCHorders USING INDEX idx_orders_user_date (user_id=?AND date>?)“ zeigt an, dass ein zusammengesetzter Index sowohl für den Gleichheitsfilter als auch für den Bereichsfilter genutzt wird.

Der häufigste Fehler besteht darin, Indizes zu erstellen, nachdem in der Produktion Probleme aufgetreten sind. Der EXPLAIN QUERY PLAN sollte Teil des Entwicklungsprozesses sein – er wird vor der ersten Bereitstellung für jede Abfrage ausgeführt, die Tabellen mit mehr als ein paar tausend Zeilen berührt. Die Kosten für einen unnötigen Index sind Speicherplatz. Die Kosten einer Abfrage ohne Index in der Produktion werden in Geld gemessen.

Die Muster, die einen Tabellenscan in D1 generieren

Vier wiederkehrende Situationen führen zu einem vollständigen Tabellenscan. Das erste ist das offensichtlichste: Fehlen eines Indexes für die in WHERE verwendete Spalte. SELECT * FROM orders WHERE user_id = ? ohne Index auf user_id liest alle Zeilen in der Tabelle, um die Zeilen des Benutzers zu finden. Die Korrektur ist einfach: CREATE INDEX idx_orders_user_id ON orders(user_id).

Die zweite Situation ist die Kombination von WHERE mit ORDER BY, die nicht durch einen zusammengesetzten Index abgedeckt ist. SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC kann den Index auf user_id zum Filtern verwenden, muss dann aber das Ergebnis im Speicher sortieren – ein Vorgang namens „Filesort“. Ein zusammengesetzter Index auf (user_id, created_at) eliminiert die Dateisortierung, da die Daten innerhalb jedes user_id bereits nach created_at sortiert sind.

Der dritte ist LIKE mit einem anfänglichen Platzhalter. WHERE title LIKE '%termo%' kann keinen Index verwenden: Der Platzhalter am Anfang der Zeichenfolge verhindert, dass die Datenbank die Indexreihenfolge verwendet, um Zeilen zu verwerfen. Für die zweiseitige Platzhaltertextsuche verwenden Sie virtuelle FTS5-Tabellen: CREATE VIRTUAL TABLE posts_fts USING fts5(title, content, content=posts). FTS5-Abfragen mit MATCH sind indiziert und skalierbar.

Der vierte ist Typenzwang. SQLite verwendet Typaffinität – eine als TEXT definierte Spalte kann Ganzzahlen speichern, und ein WHERE id = 42-Vergleich mit einer TEXT-Spalte verwendet möglicherweise nicht den Index, je nachdem, wie die Werte eingegeben wurden. Die Aufrechterhaltung der Typkonsistenz zwischen Schema, eingefügten Werten und Abfragen ist in SQLite wichtiger als in Datenbanken mit strikter Typisierung.

N+1 in D1 und die Rolle von db.batch()

Das N+1-Problem hat in D1 eine zusätzliche Dimension: Jede Abfrage ist eine Unteranfrage, und Unteranfragen haben ein Limit von 1000 pro Worker-Aufruf. Ein Endpunkt, der nach 50 Anfragen sucht und dann eine SELECT-Operation für die Elemente in jeder Anfrage durchführt, führt einzeln 51 Abfragen aus – 51 Unteranfragen, auf Kosten von 51 Roundtrips zur Datenbank, wobei jede einzelne die Netzwerklatenz zur Gesamtantwortzeit erhöht.

db.batch() löst dieses Problem, indem mehrere Abfragen in einer einzigen Unteranfrage gruppiert werden. Alle Abfragen im Batch werden in einem einzigen Roundtrip zur Datenbank ausgeführt. Das Ergebnis ist ein Array mit einem Element pro Abfrage in derselben Reihenfolge, in der sie gesendet wurden. Für das Muster „Bestellungen und Artikel“ enthält der Stapel: die Abfrage „Bestellungen“ und eine Abfrage mit IN, die alle Bestell-IDs abdeckt. Insgesamt zwei Unteranfragen, unabhängig davon, wie viele Anfragen zurückgegeben werden.

ORMs, die D1 unterstützen – wie Drizzle ORM, das über eine native Integration verfügt – verfügen über Eager-Loading-Optionen, die automatisch Abfragen mit JOIN oder Batch statt N+1 erstellen. Das Standardverhalten der meisten ORMs generiert jedoch N+1, es sei denn, Sie konfigurieren Eager Loading explizit. Die Überprüfung des mit console.log generierten SQL in der Entwicklungsumgebung vor der Produktion ist der direkteste Weg, diese Muster zu identifizieren.

Die tatsächlichen Kosten, wenn kein Index vorhanden ist: ein Beispiel mit Zahlen

Eine D1-Bank mit 200.000 Aufträgen. Die Abfrage SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20 ohne Index in status führt einen vollständigen Tabellenscan durch: 200.000 Zeilen gelesen, 20 Zeilen zurückgegeben. Bei 0,001 $ pro Million Lesevorgänge kostet jede Ausführung dieser Abfrage 0,0002 $.

Bei 100.000 täglichen Ausführungen dieses Endpunkts – üblich für ein Panel, das über Umfragen oder ein Betriebs-Dashboard aktualisiert wird – betragen die Kosten allein für diese Abfrage 20 US-Dollar pro Tag und 600 US-Dollar pro Monat. Der zusammengesetzte Index CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC) ändert den Plan völlig: Die Datenbank liest nur die Datensätze mit status = 'pending' unter Verwendung des Index, bereits geordnet nach created_at. Unter der Annahme von 5.000 ausstehenden Anfragen liest die Abfrage 5.000 Zeilen, gibt 20 zurück und kostet 0,000005 $ pro Ausführung. Bei 100.000 US-Dollar pro Tag sinken die Kosten auf 0,50 US-Dollar pro Tag und 15 US-Dollar pro Monat.

Der Index selbst nimmt für 200.000 Zeilen etwa 5–10 MB ein. Mit 0,75 $/GB-Monat kostet dieser Speicher weniger als 0,01 $ pro Monat. Der Unterschied zwischen 600 US-Dollar/Monat und 15 US-Dollar/Monat an Lesekosten für den Bruchteil einer Penny-Investition in den Speicher ist die Art von Optimierung, die nie aufgeschoben werden sollte, bis „nachdem der Datenverkehr zunimmt“ – denn wenn der Datenverkehr zunimmt, fallen die Kosten bereits an.

Langsame Abfragen in D1: So diagnostizieren und optimieren

ABFRAGEPLAN ERKLÄREN: Diagnose vor Optimierung

Die Muster, die einen Tabellenscan in D1 generieren

N+1 in D1 und die Rolle von db.batch()

Die tatsächlichen Kosten, wenn kein Index vorhanden ist: ein Beispiel mit Zahlen

Lesen Sie auch