> Как проверить использование индексов и построение плана выполнения запроса в базе данных? (PHP)

Короткий ответ

В PostgreSQL и MySQL для проверки использования индексов используется команда EXPLAIN (или EXPLAIN ANALYZE в PostgreSQL). Она показывает план выполнения запроса: порядок сканирования таблиц, тип соединений, оценку количества строк и используемые индексы. В MySQL также доступен EXPLAIN FORMAT=JSON для детального вывода. Ключевые поля: type (должен быть ref, range, index, а не ALL), key (какой индекс выбран), rows (оценка строк). Для диагностики реального времени - EXPLAIN ANALYZE в PostgreSQL и EXPLAIN ANALYZE в MySQL 8.0+.

Подробное объяснение

План выполнения - это дерево операций, которые СУБД выполнит для получения результата. Проверка использования индексов сводится к анализу этого дерева.

Основные инструменты:

• EXPLAIN - показывает план без выполнения. Даёт оценку стоимости, но не фактические данные.
• EXPLAIN ANALYZE - выполняет запрос и возвращает реальное время и количество строк на каждом шаге. В PostgreSQL это стандарт, в MySQL - начиная с 8.0.18.
• EXPLAIN (FORMAT JSON) - структурированный вывод для автоматического анализа.

Что смотреть в выводе:

1. Тип сканирования (type в MySQL, Node Type в PostgreSQL):

   • ALL (MySQL) / Seq Scan (PostgreSQL) - полное сканирование таблицы, индекс не используется.
   • index - полное сканирование индекса (лучше, чем ALL, но не идеально).
   • range - сканирование диапазона по индексу.
   • ref / eq_ref - поиск по неуникальному/уникальному ключу.
   • const - константный поиск по первичному ключу.

2. Поле key - какой индекс реально выбран. Если NULL - индекс не используется.

3. rows - оценка количества строк, которые будут прочитаны. Если значение близко к общему количеству строк в таблице - индекс неэффективен.

4. Extra (MySQL) / Filter (PostgreSQL) - наличие Using where без Using index может указывать на то, что фильтрация идёт после чтения строк, а не по индексу.

5. Порядок соединений - в MySQL EXPLAIN показывает порядок таблиц в id и select_type. В PostgreSQL - дерево с Nested Loop, Hash Join, Merge Join.

Почему индекс может не использоваться:

• Функция или выражение над колонкой в WHERE (например, WHERE YEAR(created_at) = 2024).
• Неявное приведение типов (сравнение VARCHAR с числом).
• LIKE '%pattern' - индекс не работает для ведущего wildcard.
• Низкая селективность - оптимизатор решает, что дешевле прочитать всю таблицу.
• Устаревшая статистика - нужно выполнить ANALYZE TABLE (MySQL) или ANALYZE (PostgreSQL).

На практике

Для PHP-разработчика важно уметь быстро диагностировать медленные запросы. Типичный workflow:

1. Включить логирование медленных запросов (slow query log) в MySQL или auto_explain в PostgreSQL.
2. Найти проблемный запрос.
3. Выполнить EXPLAIN ANALYZE с реальными параметрами.
4. Проверить, какие индексы существуют (SHOW INDEX FROM table в MySQL, \d+ table в PostgreSQL).
5. Если индекс не используется - понять причину (функция, тип, селективность).
6. Попробовать переписать запрос или добавить композитный индекс.

В PHP-проектах часто используют ORM (Doctrine, Eloquent). Важно уметь получить SQL из ORM и прогнать его через EXPLAIN. Например, в Doctrine: $query->getSQL(), в Eloquent: $query->toSql().

Полезные практики:

• Для PostgreSQL использовать EXPLAIN (ANALYZE, BUFFERS) - покажет чтение из кэша и диска.
• Для MySQL - EXPLAIN FORMAT=JSON и обращать внимание на attached_condition и used_key_parts.
• Проверять план на репрезентативных данных - на пустой таблице оптимизатор может выбрать Seq Scan, а на заполненной - индекс.

Пример кода

SQL
-- PostgreSQL: реальный план с временем
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users 
WHERE email = 'test@example.com';

-- MySQL 8+: JSON-план
EXPLAIN FORMAT=JSON 
SELECT * FROM users 
WHERE email = 'test@example.com';

-- MySQL: классический вывод
EXPLAIN SELECT * FROM users 
WHERE email = 'test@example.com';

Пример вывода PostgreSQL:

Index Scan using idx_users_email on users  (cost=0.43..8.45 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (email = 'test@example.com'::text)
  Buffers: shared hit=3
Planning Time: 0.045 ms
Execution Time: 0.028 ms

Пример вывода MySQL:

+----+-------------+-------+------------+------+------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_users_email  | idx_users_email | 1023 | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+---------+---------+-------+------+----------+-------+

В PHP-коде можно автоматизировать проверку:

PHP
$sql = "EXPLAIN ANALYZE " . $query->toSql();
$result = $pdo->query($sql)->fetchAll();
// Анализ $result: проверка Node Type, Index Name, Actual Rows

Как отвечать на собеседовании

Начните с EXPLAIN как основного инструмента. Затем уточните разницу между EXPLAIN и EXPLAIN ANALYZE - первый показывает план, второй - реальное выполнение. Упомяните, что в MySQL и PostgreSQL синтаксис и поля отличаются.

Покажите понимание, что индекс - не панацея: оптимизатор может отказаться от него при низкой селективности или из-за функций в WHERE. Приведите пример, когда индекс не сработает: WHERE DATE(created_at) = '2024-01-01'.

Расскажите про композитные индексы и правило левостороннего префикса. Например, индекс (user_id, created_at) эффективен для WHERE user_id = ? ORDER BY created_at, но не для WHERE created_at = ?.

Упомяните практические инструменты: pg_stat_statements в PostgreSQL, performance_schema в MySQL, EXPLAIN ANALYZE для реальных данных.

Завершите мысль о том, что проверка плана - это итеративный процесс: изменили индекс → пересмотрели план → проверили на реальной нагрузке.

Что проверяет интервьюер

Интервьюер оценивает:

• Знание синтаксиса EXPLAIN в конкретной СУБД (важно уточнить, о какой БД речь).
• Умение читать план: отличать Seq Scan от Index Scan, понимать Nested Loop vs Hash Join.
• Понимание причин, по которым индекс не используется.
• Знание композитных индексов и порядка колонок.
• Практический опыт: как вы находите и исправляете медленные запросы в реальном проекте.
• Умение объяснить trade-off: индекс ускоряет чтение, но замедляет запись и занимает место.

Для senior-позиции важно показать системный подход: не просто "выполнил EXPLAIN", а "проанализировал статистику, проверил селективность, переписал запрос или изменил индекс".

Типичные ошибки

• Путаница между EXPLAIN и EXPLAIN ANALYZE - первый не выполняет запрос, поэтому не показывает реальное время.
• Игнорирование rows - если оценка строк большая, индекс неэффективен, даже если key заполнен.
• Непонимание, что type = index в MySQL - это не использование индекса для поиска, а полное сканирование индекса.
• Уверенность, что индекс всегда ускоряет запрос - на маленьких таблицах Seq Scan быстрее.
• Забывают про неявное приведение типов: WHERE varchar_col = 123 - индекс не сработает.
• Не проверяют план на реальных данных - на пустой таблице оптимизатор ведёт себя иначе.
• В MySQL не смотрят на filtered - если 10%, то оптимизатор прочитает много лишних строк.
• Не учитывают, что ORM может генерировать неоптимальный SQL - нужно смотреть именно итоговый запрос.