Was weißt du über SQLite Indizierung?

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?

• Gar nicht: Die Abfrage wird ungefähr gleich schnell bleiben

  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

• Unzureichende Information: Die Frage kann nicht eindeutig beantwortet werden

  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

• Schlecht: Die Abfrage dauert länger

  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

• Gut: Die Abfrage wird schneller

  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

Zum Ergebnis

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:

• Passen: Keine Änderung nötig.

  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

• Passen nicht: Der Index oder die Abfrage sollten geändert werden

  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

Nächste Frage

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:

• Passen: Keine Änderung nötig.

  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.

• Passen nicht: Der Index oder eine Abfrage sollte geändert werden

  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.

Nächste Frage

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:

• Passen: Keine Änderung nötig.

  Die Abfrage kann als indizierte Top-N-Abfrage laufen. Dafür muss nur der B-Baum durchwandert werden (log(n)) und ein einziges mal auf die Tabelle zugegriffen werden.

  Tipp

  Verwende dieses Muster für Abfragen nach dem letzten, besten, … Eintrag.

  Dafür ist es essentiell, dass der Index die where und order by-Klauseln unterstützt. Die Datenbank nutzt den Index, um den letzten passenden Eintrag zu finden, und nimmt diese Zeile gleich als Ergebnis. Obwohl es eine order by-Klausel gibt, muss nicht sortiert werden.

  Mehr dazu

  • Die order by-Klausel indizieren

  • Die ersten N Zeilen schnell finden

  • fetch first ist die Standardsyntax für limit oder top

• Passen nicht: Der Index oder die Abfrage sollten geändert werden

  Die Abfrage kann als indizierte Top-N-Abfrage laufen. Dafür muss nur der B-Baum durchwandert werden (log(n)) und ein einziges mal auf die Tabelle zugegriffen werden.

  Tipp

  Verwende dieses Muster für Abfragen nach dem letzten, besten, … Eintrag.

  Dafür ist es essentiell, dass der Index die where und order by-Klauseln unterstützt. Die Datenbank nutzt den Index, um den letzten passenden Eintrag zu finden, und nimmt diese Zeile gleich als Ergebnis. Obwohl es eine order by-Klausel gibt, muss nicht sortiert werden.

  Mehr dazu

  • Die order by-Klausel indizieren

  • Die ersten N Zeilen schnell finden

  • fetch first ist die Standardsyntax für limit oder top

Nächste Frage