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`, чтобы вместо `Sort` план мог читать строки упорядоченно (например, через `Index Scan`) или сократить набор до сортировки более селективным `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` ускорит `ORDER BY`, позволив базе сразу читать строки в нужном порядке без `Sort`.
BИндекс только по `user_id` отфильтрует строки пользователя, а `LIMIT 50` уберёт лишнее — `Sort` допустим на 50 строках.
CСоставной индекс по `(user_id, created_at)` (в порядке, совместимом с `ORDER BY`), чтобы сделать `Index Scan` сразу в нужном порядке.
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` просто обрезает «как получилось» — порядок может зависеть от плана (`Seq Scan` или `Index Scan`), физического расположения строк и других факторов. Если нужен воспроизводимый сэмпл, добавьте детерминирующий `ORDER BY` (часто по ключу).
14Вы видите `Index Scan` в `EXPLAIN` и делаете вывод «это точно быстро». Насколько это корректно?
AКорректно: `Index Scan` всегда быстрее `Seq Scan`.
BКорректно, если в плане `cost` меньше, значит время тоже обязательно меньше.
CНекорректно: `EXPLAIN` показывает оценки, а не гарантии; при большом количестве возвращаемых строк `Seq Scan` может быть быстрее, а время зависит от многих факторов.
DНекорректно только для запросов без `WHERE`.
Ответ: `Index Scan` — это сигнал, но не обещание скорости.
Индексы помогают выборочным запросам, но при больших выборках могут проигрывать последовательному чтению. Кроме того, `EXPLAIN` без `ANALYZE` не измеряет фактическое время, а показывает модель планировщика. Используйте план для гипотез и проверяйте на данных, особенно для критичных дашбордов.
15Вы смотрите `EXPLAIN` запроса для дашборда и видите фрагмент: `Sort` → `Limit`. При этом сортировка происходит по миллионам строк. Где с наибольшей вероятностью узкое место?
AВ узле `Limit`, потому что он «режет» результат.
BВ самом операторе `SELECT`, потому что он всегда самый дорогой.
CВ сетевой передаче результата клиенту.
DВ узле `Sort`, потому что чтобы применить `ORDER BY`, базе нужно упорядочить большой набор до `Limit` (если нет подходящего индекса).
Ответ: Часто дорого не `LIMIT`, а сортировка перед ним.
Если нет индекса, который может отдать строки уже в нужном порядке, база вынуждена выполнять `Sort` для всего набора, а потом брать первые строки через `Limit`. Поэтому для ускорения обычно пытаются уменьшить объём данных до сортировки (более селективный `WHERE`) или добавить индекс под `ORDER BY`.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram