EXPLAIN и оптимизация: вопросы для собеседования (часть 2)
EXPLAIN показывает план выполнения запроса и помогает понять, где теряется производительность. На собеседованиях спрашивают про индексы, Seq Scan vs Index Scan, стоимость JOIN-ов и как переписать медленный запрос. Этот навык отличает аналитика, который просто пишет SQL, от того, кто понимает, как база данных его выполняет.
Вопросы 6–10 из 20
6На большой таблице `events` запрос `SELECT * FROM events ORDER BY created_at DESC LIMIT 100` неожиданно работает быстро. Какое объяснение наиболее вероятно при наличии индекса по `created_at`?
AПлан может пройти `Index Scan` по индексу на `created_at` и взять первые строки без отдельного шага `Sort`
BНаличие `LIMIT 100` отключает сортировку в плане, поэтому `ORDER BY` фактически не влияет на стоимость запроса
CОператор `ORDER BY` в таких запросах игнорируется оптимизатором, если в таблице нет полей с уникальным значением
DПланировщик всегда использует хэш-агрегацию, когда в запросе одновременно встречаются `ORDER BY` и `LIMIT`
Ответ: Связка `ORDER BY` и `LIMIT` часто ускоряется индексом по полю сортировки.
Если есть подходящий индекс по `created_at`, планировщик может читать строки уже в нужном порядке и остановиться после `LIMIT 100`. Тогда дорогая операция `Sort` не нужна или становится значительно дешевле. Поэтому одинаковый запрос без индекса может быть на порядки медленнее.
Подробный разбор → 7В плане `EXPLAIN` для запроса по пользователю вы видите проход по индексу `orders_user_id_idx` для таблицы заказов. Какой вывод наиболее корректен?
AПланировщик использует индекс, чтобы найти подходящие строки по ключу, не читая всю таблицу заказов целиком
BТакой план гарантирует, что запрос всегда выполнится максимально быстро среди всех возможных способов исполнения
CТакой план означает, что результат уже отсортирован по любому столбцу таблицы заказов без дополнительных операций
DТакой план означает, что база создала временную таблицу заказов и теперь читает её последовательно при каждом запросе
Ответ: Проход по индексу позволяет найти подходящие строки быстрее, чем последовательное сканирование всей таблицы.
При проходе по индексу база сначала находит подходящие записи в индексе (например, по идентификатору пользователя), а затем читает соответствующие строки таблицы. Это особенно выгодно, когда условие в `WHERE` отбирает малую долю строк. Реальная скорость зависит от объёма возвращаемых данных и количества дисковых чтений, поэтому проход по индексу не является автоматической гарантией максимальной производительности.
Подробный разбор → 8В выводе `EXPLAIN` вы видите узел `Seq Scan on orders`. Что это обычно означает в плане выполнения запроса?
AБаза использует индекс по фильтруемой колонке и читает только подходящие строки, не обращаясь к остальным страницам таблицы `orders`
BБаза возвращает результат целиком из кэша запросов и не обращается к таблице `orders` на диске на этом шаге плана выполнения
CБаза блокирует таблицу `orders` до конца запроса и не позволяет другим транзакциям читать или изменять данные в ней до коммита
DБаза читает таблицу `orders` целиком и затем применяет фильтр (`Filter`) с условием `WHERE` к каждой прочитанной строке таблицы
Ответ: `Seq Scan` — последовательное чтение всей таблицы с применением фильтра к каждой строке, без использования индекса.
При `Seq Scan` планировщик считает, что проще пройтись по всем строкам, чем прыгать по индексу. Это нормально для маленьких таблиц или когда условие `WHERE` отбирает большую долю данных. Узкое место возникает, когда таблица большая, а фильтр должен был сильно сузить выборку — тогда стоит проверить наличие подходящего индекса. Кэш запросов и блокировки — это про другие механизмы, не про последовательное чтение.
Подробный разбор → 9В выводе `EXPLAIN` вы видите оценку `cost=0.00..431.00`. Какой вывод аналитик может сделать безопасно?
AТочное время выполнения запроса в миллисекундах на текущей нагрузке базы
BТочное количество строк, которое запрос вернёт после фактического выполнения
CОтносительная оценка планировщика для сравнения альтернативных планов запроса
DОбъём оперативной памяти в мегабайтах, который запрос выделит на стадии исполнения
Ответ: `cost` в `EXPLAIN` — это оценка планировщика для сравнения планов, а не секунды или строки.
В Postgres-подобных СУБД `cost` используется планировщиком, чтобы выбрать один план из нескольких. Значения помогают прикинуть «дороже/дешевле» на одной и той же базе при схожих условиях. Но переводить `cost` в секунды и обещать точное время выполнения по `EXPLAIN` нельзя. Это не количество строк (для этого есть `rows`) и не объём памяти — единицы условные, привязаны к стоимости последовательного чтения страницы.
Подробный разбор → 10В `EXPLAIN` вы видите два последовательных шага: `Seq Scan` с `cost=0.00..120000.00` и затем `Aggregate` с `cost=120000.00..120500.00`. С чего логичнее начать поиск узкого места?
AС шага с самой большой «стоимостью» и объёмом данных (здесь `Seq Scan`), потому что он кормит все остальные узлы плана.
BС верхней строки плана, потому что она выполняется первой и задаёт порядок чтения данных всеми нижними узлами.
CС любого шага, где встречается слово `Aggregate`, потому что агрегаты всегда самые дорогие в плане выполнения.
DС названия индекса в плане: если индекса нет в списке узлов, значит запрос обречён на медленное выполнение.
Ответ: Ищите тяжёлые узлы по `cost` и `rows` и помните, что «нижние» шаги кормят «верхние».
Обычно имеет смысл смотреть на узлы, которые читают или генерируют много строк (часто это сканы и сортировки). Если «внизу» много работы, то «наверху» это уже не исправить. Поле `cost` — не время выполнения, но оно помогает быстро понять, где планировщик ожидает основные затраты, а `rows` показывает объём данных между узлами.
Подробный разбор →