Продвинутые индексы: составной, покрывающий, проигнорированный

Освой правило самого левого префикса, покрывающие индексы и ловушки, из-за которых индекс становится невидимым для планировщика.

11 мин · 3 вопросов

Составной индекс упорядочивает строки сразу по нескольким столбцам, в объявленном порядке. Решающее правило - самый левый префикс: индекс по (a, b) может обслужить фильтр по a или по a и b, но никогда по одному только b. Порядок столбцов - это проектное решение, а не мелочь. Сначала создай набор данных ниже.

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);

Создадим индекс по (department_id, salary). Фильтр, который начинается с department_id, запускает SEARCH. А вот фильтр только по salary ломает самый левый префикс: планировщик не может сразу перейти к нужным строкам и откатывается к SCAN. Сравни два плана.

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

Покрывающий индекс содержит все столбцы, которые читает запрос. Тогда планировщик отвечает прямо из индекса, не открывая таблицу: именно это означает USING COVERING INDEX. Здесь индекс (department_id, salary) покрывает запрос, который читает только эти два столбца. Помни, что id (rowid) неявно присутствует в каждом индексе, поэтому он тоже покрыт бесплатно.

-- 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=?)

Проверка знаний

Убедись, что запомнил ключевые моменты этого урока.

1. Какой запрос НЕ сможет использовать составной индекс по (department_id, salary)?
   • WHERE department_id = 1
   • WHERE department_id = 1 AND salary > 60000
   • WHERE salary > 60000
   • WHERE department_id = 1 AND name = 'Ada'
2. Что такое покрывающий индекс?
   • Индекс, который содержит все читаемые столбцы и позволяет не открывать таблицу
   • Индекс, который покрывает сразу несколько таблиц
   • Индекс, который автоматически пересоздаётся после каждого INSERT
   • Синоним уникального индекса по первичному ключу
3. Какое из этих условий мешает использовать простой индекс по email?
   • WHERE email = 'a@b.co'
   • WHERE email > 'm'
   • WHERE lower(email) = 'a@b.co'
   • WHERE email IN ('a@b.co', 'c@d.co')