Fortgeschrittene Indizes: zusammengesetzt, abdeckend, ignorierter Index

Meistere die Regel des am weitesten links stehenden Präfix, abdeckende Indizes und die Fallstricke, die einen Index für den Planer unsichtbar machen.

11 Min. · 3 Fragen

Ein zusammengesetzter Index ordnet die Zeilen über mehrere Spalten, in der deklarierten Reihenfolge. Die entscheidende Regel ist das am weitesten links stehende Präfix: Ein Index auf (a, b) kann einen Filter auf a oder auf a und b bedienen, aber niemals auf b allein. Die Reihenfolge der Spalten ist daher eine Design-Entscheidung, kein Detail. Lege zuerst den folgenden Datensatz an.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000),
  (4, 'Edsger', 1, 90000);

Legen wir einen Index auf (department_id, salary) an. Ein Filter, der mit department_id beginnt, löst ein SEARCH aus. Aber ein Filter allein auf salary durchbricht das am weitesten links stehende Präfix: Der Planer kann nicht direkt zu den richtigen Zeilen springen und fällt auf ein SCAN zurück. Vergleiche die beiden Pläne.

CREATE INDEX idx_dept_salary
  ON employees (department_id, salary);

-- Leftmost prefix present: index used
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
  AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary

-- Leading column missing: index ignored
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employees

Ein abdeckender Index enthält jede Spalte, die die Abfrage liest. Der Planer beantwortet die Abfrage dann direkt aus dem Index, ohne die Tabelle zu öffnen: Das ist die Bedeutung von USING COVERING INDEX. Hier deckt der Index (department_id, salary) eine Abfrage ab, die nur diese beiden Spalten liest. Denk daran, dass die id (die rowid) in jedem Index implizit vorhanden ist, also ebenfalls gratis abgedeckt wird.

-- Every column read is in the index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)

Wissenscheck

Stelle sicher, dass du die wichtigsten Punkte dieser Lektion behalten hast.

1. Welche Abfrage kann einen zusammengesetzten Index auf (department_id, salary) NICHT nutzen?
   • WHERE department_id = 1
   • WHERE department_id = 1 AND salary > 60000
   • WHERE salary > 60000
   • WHERE department_id = 1 AND name = 'Ada'
2. Was ist ein abdeckender Index?
   • Ein Index, der jede gelesene Spalte enthält und so das Öffnen der Tabelle vermeidet
   • Ein Index, der mehrere Tabellen auf einmal abdeckt
   • Ein Index, der nach jedem INSERT automatisch neu erstellt wird
   • Ein Synonym für einen eindeutigen Index auf dem Primärschlüssel
3. Welche dieser Bedingungen verhindert die Nutzung eines einfachen Index auf email?
   • WHERE email = 'a@b.co'
   • WHERE email > 'm'
   • WHERE lower(email) = 'a@b.co'
   • WHERE email IN ('a@b.co', 'c@d.co')