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

Один и тот же SQL можно выполнить десятком способов. Кто выбирает лучший и как? Разберём оптимизатор, план запроса и баланс нормализации со скоростью.

План выполнения запроса — это конкретная последовательность физических операций (сканирований, соединений, сортировок), которой СУБД реализует декларативный SQL-запрос. План выбирает оптимизатор, оценивая стоимость альтернатив.

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

Этот урок замыкает идею, заявленную в самом начале курса: реляционные запросы декларативны. В первом разделе мы говорили, что вы описываете что нужно, а не как, и что это даёт независимость данных. Оптимизатор — то место, где обещание исполняется буквально. Поскольку вы не зафиксировали способ выполнения, СУБД вольна выбрать любой эквивалентный план — и пересматривать его, когда меняются данные, индексы или статистика. Тот же самый SQL-запрос на пустой таблице выполнится сканированием, а на миллионной — через индекс, и вам не нужно ничего переписывать: оптимизатор адаптируется сам. Сравните с навигационными моделями из первого раздела, где план был зашит в код программиста, — там адаптация означала переписывание. Декларативность не просто удобство синтаксиса: она передаёт СУБД свободу оптимизации, и эта свобода — одна из главных причин, по которой реляционные базы пережили десятилетия роста объёмов данных.

Зачем нужен оптимизатор

SQL декларативен: вы пишете что хотите получить, но не как. А способов «как» — множество. Соединить две таблицы можно вложенным циклом, хеш-соединением или соединением слиянием; данные таблицы взять полным сканированием или через индекс; фильтр применить раньше или позже. Все эти варианты дают одинаковый результат, но различаются по стоимости в сотни раз. Выбор делает оптимизатор запросов — компонент СУБД, который перебирает планы и оценивает, какой дешевле. Это и есть та самая физическая независимость: вы не пишете план вручную, СУБД строит его сама.

Стоимость и кардинальность

Чтобы сравнивать планы, оптимизатор оценивает их стоимость — в первом приближении число обращений к страницам (мы помним: диск — главная цена). Ключевой вход для оценки — кардинальность: сколько строк вернёт операция. Если фильтр city = 'Москва' отберёт 1% строк, выгоден индекс; если 90% — выгоднее полное сканирование. Оценить кардинальность точно нельзя, поэтому СУБД хранит статистику: гистограммы распределения значений, число уникальных значений, размер таблиц. По этой статистике оптимизатор и прикидывает, сколько строк пройдёт через каждый шаг.

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

Способы выполнить соединение

Чтобы понять работу оптимизатора, разберём конкретный выбор — как выполнить соединение двух таблиц. Есть три классических алгоритма. Соединение вложенными циклами: для каждой строки одной таблицы перебираем строки другой; просто, но дорого, если обе большие (зато отлично, когда одна таблица крошечная или есть индекс по ключу соединения). Хеш-соединение: строим хеш-таблицу по ключу меньшего отношения, затем прогоняем через неё большее; эффективно для больших таблиц и соединений по равенству. Соединение слиянием: если оба отношения отсортированы по ключу соединения, идём по ним параллельно, как при слиянии двух отсортированных списков; выгодно, когда данные уже отсортированы (например, берутся через индекс). Все три дают одинаковый результат, но их стоимость различается в разы в зависимости от размеров таблиц, наличия индексов и сортировки. Выбрать лучший для конкретного случая — и есть работа оптимизатора. Понимая эти варианты, вы осмысленно читаете план: видя «hash join» или «nested loop», вы знаете, что СУБД решила и почему.

Как смотреть план

Узнать выбранный план можно командой EXPLAIN перед запросом — она показывает дерево операций, не выполняя сам запрос. По плану видно главное: использует ли запрос индекс или идёт полным сканированием, в каком порядке соединяются таблицы, где происходит сортировка. Чтение планов — основной навык оптимизации: вы не угадываете, а смотрите, что СУБД реально собирается делать.

CREATE TABLE clients (id INTEGER PRIMARY KEY, city TEXT);
INSERT INTO clients VALUES (1,'Москва'),(2,'Казань'),(3,'Москва');
CREATE INDEX idx_city ON clients(city);

-- EXPLAIN QUERY PLAN показывает, как SQLite выполнит запрос
EXPLAIN QUERY PLAN
SELECT id FROM clients WHERE city = 'Москва';

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

Почему оптимизатор иногда ошибается

Полезно знать, что оптимизатор не всемогущ, и понимать, отчего он промахивается, — это спасает на практике. Главная причина — неточная оценка кардинальности. Оптимизатор не выполняет запрос, он лишь прикидывает по статистике, сколько строк пройдёт через каждый шаг. Если статистика устарела или данные распределены неравномерно (например, 90% заказов из одного города), оценка может сильно разойтись с реальностью, и выбранный план окажется плохим: СУБД решит, что фильтр отсеет почти всё, и выберет индекс, а на деле под условие попадёт половина таблицы. Вторая причина — слишком сложный запрос с десятком соединений: число возможных планов взрывается, и оптимизатор вынужден не перебирать все, а эвристически отсекать, иногда упуская лучший. Что с этим делать практику: держать статистику свежей, упрощать запросы, иногда подсказывать оптимизатору. Понимание, что план строится на оценках, объясняет частый сюрприз «вчера запрос летал, сегодня тормозит»: данные изменились, а статистика — нет, и план перестал подходить.

Главный компромисс: нормализация против производительности

Мы потратили целый раздел на нормализацию ради устранения избыточности и аномалий. Но у медали две стороны. Нормализованная схема разносит данные по таблицам — и чтобы собрать осмысленный ответ, нужны соединения. Много таблиц — много соединений — выше стоимость чтения. Денормализованная схема (данные в одной широкой таблице) читается быстрее, но страдает от аномалий обновления.

Когда денормализация оправдана

Чтобы компромисс не остался абстрактным, назовём конкретные ситуации, где денормализация уместна. Аналитика и отчёты: системы, где данные в основном читают и редко меняют (хранилища данных), часто денормализуют сознательно — широкие таблицы с предрассчитанными агрегатами читаются быстрее, а редкие обновления делают риск рассинхронизации управляемым. Дорогие повторяющиеся вычисления: если сумма заказа считается на каждой странице тысячу раз в секунду, разумно хранить готовое поле total и пересчитывать его при изменении позиций. Кеширование популярных соединений: продублировать имя автора рядом с книгой, чтобы каталог не соединялся с таблицей авторов на каждом запросе. Общий признак этих случаев: чтений несоизмеримо больше, чем записей, а соединение или вычисление доказанно дорогое. Если же данные часто меняются, денормализация быстро обернётся кошмаром синхронизации и потеряет смысл. Поэтому денормализуют там, где «читаем часто, пишем редко», и избегают там, где наоборот.

Как балансировать на практике

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

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

Типичные ошибки

• Оптимизируют вслепую. Меняют запросы, не глядя в EXPLAIN; не видят, использует ли запрос индекс.
• Забывают про статистику. Устаревшая статистика заставляет оптимизатор выбирать плохие планы.
• Денормализуют вместо индексации. Жертвуют чистой схемой там, где хватило бы индекса.
• Нормализуют до абсурда без оглядки на чтение. Десятки крошечных таблиц превращают каждый запрос в каскад соединений.

Итог

• Оптимизатор выбирает план выполнения, оценивая стоимость альтернатив; вход для оценки — кардинальность и статистика.
• Команда EXPLAIN показывает план, не выполняя запрос; чтение планов — основной навык оптимизации.
• Нормализация уменьшает избыточность, но добавляет соединения; денормализация ускоряет чтение ценой аномалий.
• Сначала нормализованная схема и индексы; денормализация — точечно и по измерениям.