План запроса и компромиссы проектирования

В прошлом уроке мы построили индекс и увидели, как он находит строку за несколько шагов. Но в SQL-запросе нет слов «используй индекс». Ты пишешь WHERE email = '...', а как искать, решает СУБД. Этот урок про то, кто и как принимает это решение, как подсмотреть его через EXPLAIN, и про компромиссы, которые закладываются в схему задолго до первого запроса.

Навигатор

Ты вбиваешь в навигатор адрес. Ты не говоришь ему «поверни направо на третьем перекрёстке». Навигатор сам смотрит на карту, на пробки и на длину каждой дороги и выбирает маршрут. По пустому городу ночью он поведёт по короткой дороге через центр, а в час пик по длинной объездной.

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

Индекс или полный просмотр

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

Вспомни указатель в конце книги. Чтобы найти одно упоминание митохондрий, указатель незаменим. А если тебе нужны все страницы, где встречается слово «клетка», и оно есть на 500 страницах из 600? Прыгать по указателю туда-сюда дольше, чем прочитать книгу подряд.

С таблицей то же самое. Индекс даёт адреса строк, разбросанные по разным страницам, и за каждой СУБД идёт отдельно. Если подходящих строк мало, это выгодно. Если много, дешевле прочитать все страницы подряд одним проходом.

Ключевое слово здесь селективность: какая доля строк проходит условие.

• WHERE email = 'petya@example.com': одна строка из миллиона, индекс.
• WHERE year = 1: в школе четыре года обучения, условие проходит четверть таблицы, скорее всего полный просмотр.
• WHERE city = 'Казань': зависит от того, сколько студентов из Казани.

Откуда оптимизатор знает, сколько студентов из Казани? Он держит статистику: примерное число строк, самые частые значения колонок, их распределение. В PostgreSQL статистику обновляет команда ANALYZE, её же периодически запускает фоновый процесс. Устаревшая статистика это частая причина плохого плана: оптимизатор думает, что в таблице сто строк, а их там уже миллион.