Индексы SQL — Почему ваш запрос медленный и что нужно делать на самом деле

Ваш запрос занимает 2 мс на локальном хосте с 500 строками. На продакшене с 3 миллионами строк он занимает 9 секунд, и пользователи смотрят на спиннер. Сам запрос, вероятно, в порядке — проблема в схеме. Вам нужен индекс, и вам нужно понять почему так что вы перестанете угадывать следующий раз, когда это произойдёт.

Что делает база данных без индекса

Индекс B-дерева — это отсортированная копия столбца (или столбцов), хранящаяся в сбалансированном дереве, в котором база данных может выполнять бинарный поиск за O(log n) времени. Без него база данных выполняет полный скан таблицы — она читает каждую строку, чтобы найти соответствия. С 3 миллионами строк это означает 3 миллиона чтений строк. С индексом на правильном столбце это может быть 20–30 проходов по узлам дерева для нахождения соответствующих строк.

Представьте это как телефонный справочник. Поиск «Смит, Джон» в алфавитном порядке занимает несколько секунд. Поиск всех людей с именем Джон, читая каждую запись с первой страницы, занимает вечность. Индекс — это алфавитная сортировка; база данных использует его, чтобы сразу перейти к соответствующим строкам, вместо того чтобы сканировать всё.

Одно важное дело: индексы хранятся на диске вместе с вашей таблицей. База данных автоматически поддерживает их при записи. Это бесплатно для чтений, но требует затрат при каждом INSERT, UPDATE и DELETE — именно поэтому вы не можете просто индексировать всё.

Выявление проблемы с EXPLAIN

Перед тем как что-либо менять, выполните EXPLAIN на медленном запросе. Вот то, что вы ищете в MySQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Две важные колонки:

• type: ALL — полный скан таблицы. Почти всегда плох, если таблица имеет более 100 тысяч строк.
• rows: 2847391 — оптимизатор оценивает, что потребуется проверить 2,8 миллиона строк для получения результатов.

Теперь добавьте индекс и выполните запрос снова:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type: ref и rows: 12. Это и есть решение. 2,8 миллиона строк проверены → 12.

В PostgreSQL используйте EXPLAIN ANALYZE (которое на самом деле выполняет запрос) для получения реальных временных значений:

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934мс → 0,3мс. Это не пример, подобранный специально — это реальное влияние одного отсутствующего индекса на таблицу с несколькими миллионами строк.

Составные индексы и правило левого префикса

Когда у вас есть несколько столбцов в условии WHERE, вам нужны составные индексы — и порядок столбцов имеет значение.

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

The правило левого префикса: база данных использует составной индекс, начиная с левого столбца и двигаясь вправо, но становится бесполезным, когда достигает пропуска. При наличии индекса на (a, b, c):

• WHERE a = 1 — использует индекс
• WHERE a = 1 AND b = 2 — использует оба столбца индекса
• WHERE a = 1 AND b = 2 AND c = 3 — использует полный индекс
• WHERE b = 2 — не использует индекс (пропущен столбец a)
• WHERE a = 1 AND c = 3 — использует только часть a, игнорирует c

Практическое правило порядка: условия равенства сначала, а затем условия диапазона. Условия диапазона (>, <, BETWEEN, LIKE 'prefix%') нарушают рабочий префикс для последующих столбцов. Поэтому:

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

Также подумайте о селективности: поместите наиболее селективный столбец (наименьшее количество дубликатов) на более раннем месте в индексе. Если customer_id имеет 50 000 различных значений и status имеет 3, customer_id должен идти первым — он быстрее сужает набор результатов.

Когда индексы ухудшают ситуацию

Индексы не бесплатны. Каждый индекс ухудшает производительность записи и занимает место на диске. База данных обновляет каждый индекс при каждом INSERT, UPDATE и DELETE на этой таблице. На записывающих таблицах избыточные индексы действительно снижают производительность. Вот где индексы не справляются или вредят:

• Столбцы с низкой кардинальностью. Индекс на булевом столбце или на столбце с 3 различными значениями часто игнорируется оптимизатором. Если запрос вернёт более 10–15% строк через индекс, база данных обычно предпочитает полный скан таблицы — стоимость поиска в индексе не оправдана. Проверьте с EXPLAIN, используется ли индекс на самом деле. status Записывающие таблицы.
• Журналы, события, таблицы аудита — будьте осторожны. Таблица с 10 индексами и миллионами записей в час платит 10-кратный перегруз на записях. Неиспользуемые индексы.
• В PostgreSQL, показывает pg_stat_user_indexes — сколько раз каждый индекс был использован с момента последнего сброса статистики. Индекс с нулевыми счётчиками — это чистый избыток. В MySQL это отслеживается через Performance Schema ( idx_scan — сколько раз каждый индекс был использован с последнего сброса статистики. Индекс с нулевым сканированием является чистым излишком. MySQL отслеживает это через схему производительности (sys.schema_unused_indexes в MySQL 5.7+).
• Оптимизатор ошибается. Планировщики запросов не всегда правы. Иногда они выбирают индекс, который медленнее, чем полный скан. EXPLAIN ANALYZE поймёт это — сравните оценённое и фактическое количество строк. В MySQL вы можете указать с помощью USE INDEX (idx_name); в PostgreSQL вы можете протестировать с SET enable_seqscan = off (не оставляйте это в производственной среде).

Индекс или переписывание запроса? Реальная компромисс

Иногда индекс — это решение. Иногда проблема в самом запросе, а индекс просто маскирует её. Некоторые паттерны, где вы должны переписать запрос вместо (или вместе с) добавлением индекса:

Функции на индексированных столбцах

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

Обёртывание столбца в любую функцию блокирует стандартное использование индекса. Индекс хранит значения created_at, а не значения YEAR(created_at) — это разные вещи. PostgreSQL поддерживает индексы на выражениях (CREATE INDEX ON orders (YEAR(created_at))) как решение, но переписывание запроса чище и работает во всех случаях.

Покрывающие индексы для часто читаемых путей

А покрывающий индекс включает все столбцы, которые нужны запросу, поэтому база данных никогда не должна читать реальные строки таблицы — она отвечает полностью из индекса. Это может быть значительно быстрее, чем обычный индекс, для часто читаемых таблиц:

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

Шаблоны LIKE

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

Начальные шаблоны не могут быть исправлены с помощью индексов B-дерева. Если вам нужно поисковое поисковое по суффиксам или подстрокам, вам нужно полный текстовый индекс (FULLTEXT в MySQL, GIN с pg_trgm в PostgreSQL) или внешняя система поиска, например Elasticsearch.

N+1 запросы

Индекс не решает проблему N+1 запроса. Если ваш ORM делает один запрос для получения 200 заказов, а затем отдельный запрос на каждый заказ для получения имени клиента, у вас 201 запроса. Каждый из них может быть быстрым. Проблема — объём. Решите это с помощью JOIN или группового запроса — никакое количество индексов не изменит количество запросов.

Ещё одно дело: очистите запрос перед анализом его

Перед выполнением EXPLAIN на медленном запросе, помогает иметь SQL в чётком виде. Однострочная строка из 200 символов без отступов труднее понять, чем структурированный запрос. Инструмент SQL Formatter на IO Tools работает с MySQL, PostgreSQL, SQL Server и другими синтаксисами — полезен, когда вы копируете запрос из логов или отладочного вывода ОРМ и хотите увидеть его структуру на глаз.

Практический чек-лист

• Выполните EXPLAIN до добавления любого индекса. Убедитесь, что проблема была выявлена сначала.
• Ищите type: ALL (MySQL) или Seq Scan (PostgreSQL) — обычно там, где проблема.
• Проверьте rows оценку в EXPLAIN. Миллионы строк сканируются для получения десятков = отсутствует индекс.
• В составных индексах: условия равенства сначала, условия диапазона позже.
• Столбцы с более высокой селективностью идут раньше в составном индексе.
• Не индексируйте столбцы с низкой кардинальностью без проверки того, что оптимизатор действительно использует индекс.
• Регулярно проверяйте неиспользуемые индексы: pg_stat_user_indexes (PostgreSQL), sys.schema_unused_indexes (MySQL).
• Если функция обёртывает ваш столбец в WHERE, перепишите запрос вместо индексирования функции.
• Для часто читаемых путей с небольшим набором столбцов рассмотрите покрывающие индексы.
• N+1 — это проблема архитектуры запросов, а не проблема индексов.

Вам также может понравиться