von Markus Winand.

Was weißt du über SQLite Indizierung?

Die meisten Fragen folgen einem einfachen Muster: Sie zeigt einen Index und eine Abfrage. Die Frage ist dann, ob Index und Abfrage zusammenpassen, oder nicht.

Falls du eine Abfrage ändern würdest, um die Performance zu verbessern, muss die neue Abfrage dasselbe Ergebnis liefern. Das bedeutet auch alle Spalten, wenn select * genutzt wird.

Bevor ich dir das Ergebnis zeige…

Lade deine Antworten bitte hoch

Ich würde mich freuen, wenn du mir deine Antworten zur Analyse überlässt (wie in diesem Artikel).

(Datenschutz)

Frage 5

Diese Frage ist anders. Sieh dir zuerst den Index und die ursprüngliche Abfrage an:

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column

Gehe davon aus, dass die Abfrage zumindest einige Zeilen liefert und dass es keine anderen Indizes auf dieser Tabelle gibt.

Um eine neue Anforderung umzusetzen, wird eine weitere Bedingung (b = 1) in die where-Klausel aufgenommen:

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Wie wird sich diese Änderung auf die Performance auswirken?

  • Falsch! Die Abfrage wird langsamer.

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusätzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan und Myth: select * is bad

  • Falsch. Die Information oben lässt den rückschluss zu, dass die Abfrage langsamer wird.

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusätzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan und Myth: select * is bad

  • Richtig: die Abfrage wird langsamer

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusätzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan und Myth: select * is bad

  • Falsch: die Abfrage wird langsamer!

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusätzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan und Myth: select * is bad

Frage 4

Passen Index und Abfrage zueinander oder nicht?

CREATE INDEX tbl_idx ON tbl (text)
PRAGMA case_sensitive_like = true
SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

Deine Antwort:

  • Obwohl like-Ausdrücke, die mit einem Wildcard-Zeichen (% oder _) beginnen, diesen Index nicht effizient nutzen können, kann ein Suchbegriff, der das Wildcard-Zeichen nur am Ende hat diesen Index sehr effizient nutzen.

    Siehe auch: Eine bildhafte Erklärung, wann like langsam ist

  • Obwohl like-Ausdrücke, die mit einem Wildcard-Zeichen (% oder _) beginnen, diesen Index nicht effizient nutzen können, kann ein Suchbegriff, der das Wildcard-Zeichen nur am Ende hat diesen Index sehr effizient nutzen.

    Siehe auch: Eine bildhafte Erklärung, wann like langsam ist

Frage 3

Passt der Index zu beiden Abfragen?

CREATE INDEX tbl_idx ON tbl (a, b)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1

Deine Antwort:

  • Der Index passt nur zur ersten Abfrage. Die Zweite Abfrage kann den Index nicht effizient nutzen.

    Grundsätzlich kann der Index immer noch komplett gelesen werden (Full-Index-Access). Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Dreht man die Spalten im Index um, wird der Index für beide Abfragen nutzbar – ohne zusätzlichen Wartungsaufwand. Der Index sollte daher wie folgt aussehen:

    CREATE INDEX tbl_idx ON tbl (b, a)

    Tipp

    Indizes können nur von links nach rechts effizient genutzt werden. Wenn der erste Indexspalte nicht in der where-Klausel ist, ist der Index nutzlos.

    Lies mehr über mehrspaltige Indizes.

  • Der Index passt nur zur ersten Abfrage. Die Zweite Abfrage kann den Index nicht effizient nutzen.

    Grundsätzlich kann der Index immer noch komplett gelesen werden (Full-Index-Access). Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Dreht man die Spalten im Index um, wird der Index für beide Abfragen nutzbar – ohne zusätzlichen Wartungsaufwand. Der Index sollte daher wie folgt aussehen:

    CREATE INDEX tbl_idx ON tbl (b, a)

    Tipp

    Indizes können nur von links nach rechts effizient genutzt werden. Wenn der erste Indexspalte nicht in der where-Klausel ist, ist der Index nutzlos.

    Lies mehr über mehrspaltige Indizes.

Frage 2

Passen Index und Abfrage zueinander oder nicht?

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1

Deine Antwort:

Frage 1

Passen Index und Abfrage zueinander oder nicht?

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE STRFTIME ('%Y', date_column) = '2017'

Deine Antwort:

  • Durch die Anwendung der Funktion wird der Index für diese Abfrage nutzlos.

    Grundsätzlich kann der Index immer noch komplett gelesen werden (Full-Index-Access). Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Tipp

    Schreibe Suchen nach zusammenhängenden Perioden als explizite Bereichsbedingung.

    Besser ist es, den Index zu behalten und die Abfrage umzuschreiben:

    SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= '2017-01-01'
   AND date_column <  '2018-01-01'

    Beachte, dass ein funktionsbasierter Index nicht die empfohlene Lösung für solche Abfragen ist.

    Mehr dazu

  • Durch die Anwendung der Funktion wird der Index für diese Abfrage nutzlos.

    Grundsätzlich kann der Index immer noch komplett gelesen werden (Full-Index-Access). Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Tipp

    Schreibe Suchen nach zusammenhängenden Perioden als explizite Bereichsbedingung.

    Besser ist es, den Index zu behalten und die Abfrage umzuschreiben:

    SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= '2017-01-01'
   AND date_column <  '2018-01-01'

    Beachte, dass ein funktionsbasierter Index nicht die empfohlene Lösung für solche Abfragen ist.

    Mehr dazu

Dein Ergebnis

Du hast ${RESULT} von 5 richtig Beantwortet.

Diese fünf Fehler solltest du nie wieder machen:

  • Unnötige Funktionen auf indizierte Spalten in der where-Klausel anwenden

  • Nicht von indizierten Top-N-Abfragen profitieren

  • Die Spaltenreihenfolge in mehrspaltigen Indizes falsch wählen

  • Like-Filter ineffizient anwenden

  • Den Index-Only Scan nicht nutzen

Die Erklärungen zu den einzelnen Punkten findest du unten. Wenn du die Beispiele selbst ausprobieren möchtest, findest du die nötigen create-Anweisungen in diesem Download.

Hinweis in eigener Sache

Wenn du das Quiz magst, könnte mein Buch SQL Performance Explained oder mein Training etwas für dich sein.

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Der 3-Minuten Test

Inhaltsverzeichnis

Aktuelle Blog Posts

Geek-Ware

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz