Индексы и оптимизация SQL на собеседовании
Зачем аналитику знать про индексы
Аналитик не создаёт индексы каждый день — этим занимаются DBA и бэкенд-разработчики. Но на собеседовании вопросы по индексам и оптимизации появляются регулярно, особенно для позиций middle и senior. Интервьюер проверяет, понимаете ли вы, почему один запрос выполняется за секунду, а другой — за десять минут, и можете ли вы прочитать план выполнения.
Знание индексов отделяет аналитика, который просто пишет запросы, от аналитика, который понимает, как эти запросы работают под капотом.
Вопрос «почему этот запрос медленный?» встречается на middle-собеседованиях так же часто, как JOIN и оконные функции. Без понимания индексов ответить невозможно.
Как работают индексы
B-tree — самый распространённый тип индекса. Представьте телефонный справочник: фамилии отсортированы, поэтому поиск конкретного человека не требует перебора всех страниц. B-tree работает аналогично — хранит значения колонки в отсортированной структуре, позволяя находить нужную строку за логарифмическое время.
Составной индекс — индекс по нескольким колонкам. Порядок колонок критичен: индекс (city, age) ускорит запрос с WHERE city = 'Москва' AND age > 25, но не поможет при WHERE age > 25 без фильтра по city. Это правило «левого префикса» — индекс используется слева направо.
Покрывающий индекс — содержит все колонки, нужные запросу. Если SELECT читает только колонки из индекса, базе не нужно обращаться к основной таблице. Это значительно ускоряет запросы на больших таблицах.
Уникальный индекс — гарантирует уникальность значений. Помимо ускорения поиска, используется для обеспечения целостности данных. Primary key автоматически создаёт уникальный индекс.
EXPLAIN: как читать план выполнения
EXPLAIN показывает, как база данных будет выполнять запрос: какие индексы использует, в каком порядке обрабатывает таблицы, сколько строк ожидает обработать.
Ключевые элементы плана:
- Seq Scan — последовательный перебор всей таблицы, самая медленная операция
- Index Scan — поиск по индексу с обращением к таблице за остальными колонками
- Index Only Scan — все нужные данные в индексе, обращения к таблице нет
- Nested Loop, Hash Join, Merge Join — стратегии соединения таблиц
На собеседовании интервьюер может показать план запроса и попросить найти узкое место. Умение читать EXPLAIN — конкретный навык, который производит впечатление.
Когда индекс не помогает
- Низкая селективность — если колонка содержит всего два-три уникальных значения (например, пол), индекс бесполезен: базе проще перебрать всю таблицу.
- Функции на индексированной колонке — WHERE UPPER(name) = 'ИВАН' не использует индекс по name. Нужен функциональный индекс на UPPER(name).
- OR в условиях — WHERE city = 'Москва' OR age > 25 может не использовать индекс, потому что условия затрагивают разные колонки.
- LIKE с процентом в начале — WHERE name LIKE '%ванов' не может использовать B-tree индекс, потому что поиск идёт не с начала строки.
- Маленькие таблицы — на таблице в сто строк Seq Scan быстрее обращения к индексу. Оптимизатор это знает.
Частый вопрос на собеседовании: «Вы добавили индекс, но запрос не ускорился — почему?» Правильный ответ начинается с EXPLAIN и анализа, использует ли оптимизатор этот индекс.
Типичные вопросы на собеседовании
Вопрос 1: Объясните разницу между кластерным и некластерным индексом. Кластерный определяет физический порядок данных на диске — может быть только один на таблицу. Некластерных может быть несколько.
Вопрос 2: Когда стоит отказаться от индекса? Каждый индекс замедляет INSERT и UPDATE, потому что базе нужно обновлять не только таблицу, но и все её индексы. Для таблиц с частой записью лишние индексы вредны.
Вопрос 3: Как ускорить запрос с GROUP BY? Индекс по группирующим колонкам может помочь — база использует его для группировки без сортировки. Но если запрос возвращает большую часть таблицы, индекс не поможет.
FAQ
Нужно ли junior-аналитику знать индексы?
На junior-собеседовании глубоких вопросов по индексам обычно не задают. Достаточно понимать общий принцип: индекс ускоряет поиск, но замедляет запись. Для middle — знание EXPLAIN и составных индексов обязательно.
Какие индексы кроме B-tree бывают?
Hash-индекс — для точного равенства, не поддерживает диапазоны. GIN и GiST — для полнотекстового поиска, массивов, JSONB. BRIN — для больших таблиц с естественной сортировкой (логи по дате). На собеседовании обычно спрашивают только B-tree.
Как понять, нужен ли индекс конкретному запросу?
Запустите EXPLAIN ANALYZE — он покажет реальное время выполнения и количество обработанных строк. Если видите Seq Scan на большой таблице с малым количеством возвращаемых строк — это кандидат на индекс.