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.

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.

  • 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.

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.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„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 | CC-BY-NC-ND 3.0 Lizenz