Оптимизация запросов и индексы

В таблице с десятью миллионами заказов запрос, находящий заказы одного покупателя, выполняется 8 секунд; с правильным индексом тот же запрос работает миллисекунды. SQL — декларативный язык: мы пишем, что хотим получить, а как это найти, выбирает оптимизатор СУБД. Он сравнивает несколько возможных планов и на основе статистики таблиц берёт самый дешёвый. В этом уроке мы разберёмся, как «думает» оптимизатор: как устроен индекс, как читать план и как писать запросы, облегчающие его работу. Хорошая новость: особые инструменты не нужны — всё начинается с команды EXPLAIN и нескольких простых правил.

Индекс B-дерево изнутри

В большинстве СУБД индекс по умолчанию — B-дерево (точнее, B+ дерево): ключи хранятся в отсортированном виде на страницах диска, в каждой внутренней странице — сотни ключей и указатели на уровень ниже, а листья ссылаются на строки и связаны друг с другом в цепочку. Дерево всегда сбалансировано: путь от корня до любого листа одной длины. Поэтому поиск занимает всего несколько чтений страниц, а цепочка листьев делает дешёвыми запросы по диапазону (BETWEEN, >, ORDER BY).

Условия, пригодные для индекса (sargable)

Sargable (search argument able) — условие, пригодное для поиска по индексу: столбец стоит «голым» по одну сторону сравнения. Если применить к столбцу функцию или арифметику (strftime('%m', order_date) = '03', price * 1.18 > 100), отсортированные значения индекса бесполезны, и СУБД приходится проверять каждую строку. Достаточно переписать то же условие как диапазон.

CREATE INDEX idx_orders_date ON orders (order_date);

-- not sargable: a function on the column
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE strftime('%m', order_date) = '03';

id | parent | notused | detail
2 | 0 | 214 | SCAN orders USING COVERING INDEX idx_orders_date

CREATE INDEX idx_orders_date ON orders (order_date);

-- sargable: a range on the bare column
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

id | parent | notused | detail
2 | 0 | 154 | SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?)

Составные и покрывающие индексы

Составной индекс отсортирован по нескольким столбцам: (customer_id, order_date) сначала по покупателю, а внутри одного покупателя — по дате. Он работает как телефонный справочник: искать можно по фамилии или по фамилии и имени, но не по одному имени — это правило левого префикса. Если индекс содержит все столбцы, нужные запросу, СУБД вообще не обращается к таблице — такой индекс называют покрывающим (covering). Кроме того, когда порядок индекса совпадает с ORDER BY, исчезает отдельный шаг сортировки.

-- before: function on the column, no suitable index
EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders
WHERE customer_id = 1 AND strftime('%Y', order_date) = '2025'
ORDER BY order_date;

id | parent | notused | detail
3 | 0 | 216 | SCAN orders
15 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY

-- after: sargable range + composite index
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
ORDER BY order_date;

id | parent | notused | detail
3 | 0 | 46 | SEARCH orders USING COVERING INDEX idx_orders_cust_date (customer_id=? AND order_date>? AND order_date<?)

В PostgreSQL EXPLAIN показывает план и оценку стоимости, а EXPLAIN ANALYZE реально выполняет запрос и показывает фактическое время. Ниже — типичный результат на таблице из 1 000 000 строк до и после создания индекса (числа зависят от оборудования). cost=начальная..полная — в условных единицах, rows — оценка оптимизатора; если она сильно отличается от фактического rows, статистика устарела и нужен ANALYZE.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..20834.00 rows=10 width=24) (actual time=0.018..61.922 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999988
Planning Time: 0.081 ms
Execution Time: 61.950 ms

Index Scan using idx_orders_customer on orders  (cost=0.42..44.58 rows=10 width=24) (actual time=0.024..0.039 rows=12 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.210 ms
Execution Time: 0.061 ms

Когда индекс не нужен

• Маленькие таблицы — просмотреть таблицу в несколько страниц дешевле, чем спускаться по дереву.
• Низкая селективность — на столбце с двумя значениями вроде is_active условие возвращает половину строк.
• Таблицы с интенсивной записью — каждый индекс обновляется при каждом INSERT, UPDATE и DELETE; для журналов и данных с датчиков это дорого.
• Дублирующиеся индексы — если есть (customer_id, order_date), отдельный индекс (customer_id) лишний: его заменяет левый префикс.

CREATE INDEX idx_orders_date ON orders (order_date);

-- slow version: WHERE strftime('%m', order_date) IN ('01', '02', '03')
SELECT id, order_date
FROM orders
-- WHERE: a range on the bare column
ORDER BY order_date, id;

id | order_date
1 | 2025-01-15
2 | 2025-01-15
3 | 2025-02-02
4 | 2025-02-10
5 | 2025-03-05
6 | 2025-03-18

SELECT category,
       COUNT(*) AS rows_count
       -- selectivity_pct = 100 * n / N
FROM products
GROUP BY category
ORDER BY rows_count DESC, category;

category | rows_count | selectivity_pct
Electronics | 4 | 40
Accessories | 2 | 20
Stationery | 2 | 20
Games | 1 | 10
Home | 1 | 10