Индексы и планы запросов

Что это такое

Индекс — отдельная структура, где значения столбца хранятся в порядке, позволяющем движку находить строки, не читая таблицу целиком. План запроса описывает, как движок собирается отвечать: какую таблицу он прочитает первой, каким индексом воспользуется, сколько строк ожидает и станет ли сортировать, соединять или агрегировать, — и именно чтение плана показывает, почему запрос медленный.

Почему это важно

У проблем с производительностью в базе почти всегда одна из двух причин: запрос читает куда больше строк, чем нужно, либо индекса, который этого позволил бы избежать, не хватает. Обе причины видны в плане и не видны в тексте запроса. Добавить индекс дёшево, однако неверный индекс стоит места и замедляет каждую запись, которая его поддерживает, да ещё и создаёт ложное чувство защищённости. Разницу здесь делает измерение до и после.

Ключевые идеи

Индекс превращает сканирование в поиск

Пока индекса нет, найти строки по значению можно только чтением каждой строки. Упорядоченный индекс позволяет движку сразу найти нужные значения и перейти по указателям к строкам. Для маленькой таблицы сканирование всё равно может оказаться дешевле — поэтому планировщик иногда осознанно игнорирует индекс, а не ошибается.

scan:  читать каждую строку, проверять условие
index: найти значение по порядку, взять подходящие строки

Порядок столбцов в составном индексе определяет, что он может обслужить

Индекс по двум столбцам в заданном порядке поддерживает поиск по первому, а также по первому и второму вместе, но на запрос, который фильтрует только по второму, он эффективно ответить не может. Поэтому порядок столбцов в составном индексе — проектное решение, привязанное к реально выполняемым запросам. Этим же объясняется и то, почему неиспользуемый индекс часто оказывается нужным индексом в неверном порядке.

Покрывающие индексы позволяют вовсе не обращаться к таблице

Если в индексе есть все столбцы, которые нужны запросу, движок ответит прямо из индекса, не читая строки, и большая доля стоимости узкого частого запроса исчезнет. Плата — более широкий индекс, который приходится поддерживать при каждой записи.

Избирательность решает, помогает ли индекс

Индекс по столбцу с малым числом различных значений — флаг состояния, булево значение — помогает редко: поиск вернёт большую долю таблицы, и сканирование окажется дешевле. Индексы окупаются там, где условие совпадает с малой долей строк. Именно это понимание останавливает привычку индексировать каждый столбец, упомянутый в запросе.

План сообщает оценку числа строк, и оценка может быть неверной

Выбирая способ выполнения, планировщик опирается на статистику распределения значений. После большой загрузки данных статистика устаревает, выбор оказывается плохим, и запрос внезапно замедляется. Диагностировать это позволяет сравнение оценки с фактическим числом строк в плане, а лечится оно обновлением статистики.

Подозрительных признаков несколько: сканирование большой таблицы там, где ожидался поиск; сортировка, которой можно было бы избежать с помощью индекса; вложенный цикл, внутренняя сторона которого сканируется по разу на каждую внешнюю строку. Найти один из них и разобраться с ним обычно и есть основная часть улучшения.

подозрительно: SCAN on orders (estimated 4,200,000 rows)
ожидалось:     INDEX LOOKUP on orders_status_idx

Каждый индекс — это стоимость записи и места

Индекс обновляется при каждом изменении своей таблицы: чем их больше, тем медленнее вставляются строки и тем больше занимает диск. Индексы, оправданные запросом, который никто не выполняет, — чистый расход. Разбирать, какие индексы действительно используются, и убирать остальные так же полезно, как добавлять те, которых не хватает.

Практика

Прочитайте план медленного запроса

Возьмите запрос, который фильтрует большую таблицу, и прочитайте его план: определите, какой способ доступа выбрал движок и сколько строк он оценил.

Готово, когда: вы можете сказать, каким был доступ — сканированием или поиском, — и совпала ли оценка с числом возвращённых строк.

Добавьте индекс и измерьте дважды

Добавьте индекс для условия фильтрации запроса и сравните план и измеренное время до и после.

Готово, когда: в плане виден поиск, а не сканирование, и вы записали изменение числа прочитанных строк, а не только затраченное время.

Частые ошибки

Индексирование каждого столбца в запросе — место и затраты на запись ради индексов, которые не помогают; исправить: индексируйте те столбцы, которые планировщик действительно может использовать, и сверяйтесь с планом.
Неверный столбец первым в составном индексе — запросы с фильтром по другому столбцу таким индексом не воспользуются; исправить: расставляйте столбцы в том порядке, в каком по ним фильтруют.
Доверие плану с устаревшими оценками — выбор сделан по устаревшей статистике; исправить: обновляйте статистику после больших изменений и сверяйте оценки с фактическими числами.
Сохранение индексов, которыми никто не пользуется — каждая запись медленнее без всякой пользы; исправить: проверяйте использование и удаляйте лишние.
Чтение только затраченного времени — быстрая машина скрывает запрос, который читает миллион строк; исправить: сравнивайте число прочитанных строк до и после.

Что читать дальше

Запросы и соединения (05-backend-data/07-sql-relational-databases/02-queries-and-joins) — запросы, которым эти индексы и служат.
SQL и реляционные базы данных (05-backend-data/07-sql-relational-databases) — движок, чей планировщик строит эти планы.
Транзакции и изоляция (05-backend-data/07-sql-relational-databases/04-transactions-and-isolation) — вторая половина гарантий базы и частая причина неожиданных блокировок.