EXPLAIN и оптимизация: вопросы для собеседования (часть 3)
EXPLAIN показывает план выполнения запроса и помогает понять, где теряется производительность. На собеседованиях спрашивают про индексы, Seq Scan vs Index Scan, стоимость JOIN-ов и как переписать медленный запрос. Этот навык отличает аналитика, который просто пишет SQL, от того, кто понимает, как база данных его выполняет.
Вопросы 11–15 из 20
11В `EXPLAIN` вашего отчёта видно, что выполняется `Sort` по `created_at` на очень большом наборе строк. Какое действие чаще всего помогает уменьшить работу сортировки?
AДобавить `OFFSET 0` в начало выборки, чтобы планировщик пропустил внутреннюю сортировку и сразу читал страницы
BУбрать условие `WHERE` из запроса, чтобы планировщик выбрал более эффективный путь и задействовал индекс
CДобавить индекс по полю из `ORDER BY created_at` или сократить вход в сортировку селективным `WHERE`
DЗаменить `ORDER BY created_at` на `GROUP BY created_at`: группировка делается без шага сортировки строк
Ответ: Сортировка ускоряется, если база может получить строки уже в нужном порядке из индекса.
Если план вынужден делать `Sort` по миллионам строк, это часто главный потребитель ресурсов. Индекс по полю из `ORDER BY` (или составной индекс под фильтр + порядок) позволяет избежать полной сортировки или существенно её удешевить. Альтернативно можно уменьшить вход в сортировку через более точный `WHERE`.
Подробный разбор → 12Есть запрос `SELECT * FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 50`. Какой вариант индекса чаще всего помогает, чтобы избежать `Sort` и быстро получить первые строки?
AИндекс по `(created_at)`: чтение строк уже в нужном порядке, фильтр по `user_id` после `Index Scan`
BИндекс по `(user_id)`: фильтрует строки пользователя, далее `Sort` на 50 итоговых строках допустим
CИндекс по `(user_id, created_at)`: `Index Scan` сразу в нужном порядке без отдельного шага `Sort`
DИндекс по `(created_at, user_id)`: покрывает оба столбца, даёт `Index Only Scan` без таблицы
Ответ: Составной индекс может одновременно поддержать фильтр и порядок сортировки.
Когда запрос фильтрует по `user_id` и сортирует по `created_at`, составной индекс по `(user_id, created_at)` часто позволяет читать нужный диапазон уже упорядоченным. Тогда планировщик может выбрать `Index Scan` и применить `Limit` почти сразу, без отдельного шага `Sort`. Это типичный паттерн для «последние N событий пользователя».
Подробный разбор → 13В отчёте для проверки качества данных вы используете `SELECT user_id FROM users LIMIT 100` и замечаете, что набор пользователей меняется между запусками. Почему это ожидаемо?
AПотому что `LIMIT` сам по себе делает равномерную случайную выборку из всех строк таблицы пользователей
BПотому что без `ORDER BY` СУБД всегда возвращает строки в порядке возрастания первичного ключа `user_id`
CПотому что `SELECT` без `WHERE` обязан вернуть строки в порядке физической вставки и не пересортировывать
DПотому что без `ORDER BY` порядок строк не гарантирован, и `LIMIT` может выдать разный набор при изменении плана или индексов
Ответ: `LIMIT` без `ORDER BY` не даёт стабильного набора строк.
SQL по умолчанию не гарантирует порядок строк, если вы явно не указали `ORDER BY`. Поэтому `LIMIT` просто обрезает «как получилось» — порядок может зависеть от плана выполнения, физического расположения строк и других факторов. Если нужен воспроизводимый результат, добавьте детерминирующий `ORDER BY` (часто по ключу).
Подробный разбор → 14Вы видите `Index Scan` в `EXPLAIN` и делаете вывод «это точно быстро». Насколько это корректно?
AНекорректно: `EXPLAIN` показывает оценки планировщика, и при больших выборках `Seq Scan` бывает быстрее `Index Scan`
BКорректно: `Index Scan` опережает `Seq Scan` при любом объёме выбираемых строк и текущей статистики таблиц
CКорректно при условии, что `cost` плана меньше, чем у `Seq Scan`: тогда фактическое время заведомо меньше
DНекорректно лишь для запросов без `WHERE`, в остальных случаях вывод о скорости делается прямо по виду оператора
Ответ: `EXPLAIN` без `ANALYZE` показывает оценки планировщика, а `Index Scan` не всегда быстрее `Seq Scan`.
Индексы помогают выборочным запросам, но при больших выборках последовательное чтение часто оказывается быстрее. `EXPLAIN` без `ANALYZE` не измеряет фактическое время — это модельная оценка стоимости. Поэтому план — это гипотеза о работе запроса, которую нужно проверять на реальных данных, особенно для критичных дашбордов.
Подробный разбор → 15Вы смотрите `EXPLAIN` запроса для дашборда и видите фрагмент: `Sort` → `Limit`. При этом сортировка происходит по миллионам строк. Где с наибольшей вероятностью узкое место?
AВ узле `Limit`, потому что он «режет» большой результат и часто становится самым дорогим шагом в плане `EXPLAIN`
BВ операторе `SELECT`, потому что выборка столбцов всегда самый дорогой шаг и формирует итоговое множество строк
CВ сетевой передаче результата клиенту, потому что объём в дашборде превышает скорость соединения по `network`
DВ узле `Sort`: чтобы применить `ORDER BY`, базе нужно упорядочить большой набор строк до `Limit` без индекса
Ответ: Часто дорого не `LIMIT`, а сортировка перед ним: `Sort` обходит весь набор, если нет подходящего индекса.
Если нет индекса, который может отдать строки уже в нужном порядке, база вынуждена выполнять `Sort` для всего набора, а затем брать первые строки через `Limit`. Поэтому для ускорения обычно уменьшают объём данных до сортировки более селективным `WHERE` или добавляют индекс под `ORDER BY`. Сам `Limit` после готовой сортировки обычно дешёвый, а сетевая передача редко лимитирует результат, ограниченный десятками строк.
Подробный разбор →