--- title: "Оптимизация запросов баз данных: мастер-класс" description: "Изучите чтение EXPLAIN и EXPLAIN ANALYZE, оптимизацию JOIN-ов и CTE, внедрение keyset-пагинации и особенности работы MVCC в различных СУБД." published: 2026-05-31 faq: - question: "В чем разница между Sequential Scan (последовательным сканированием) и Index Scan (сканированием индекса)?" answer: "Sequential Scan считывает каждую страницу таблицы от начала до конца для поиска подходящих строк. Index Scan обходит дерево индекса для поиска подходящих записей, а затем извлекает только соответствующие страницы из кучи (Heap). Для небольших таблиц или запросов, извлекающих большой процент строк, последовательное сканирование часто работает быстрее, чем случайный ввод-вывод (random I/O) при сканировании индекса." - question: "Что такое Nested Loops, Hash Joins и Merge Joins?" answer: "Nested Loops (вложенные циклы) соединяют таблицы путем сканирования внешней таблицы и поиска совпадающих строк во внутренней (оптимально для малых наборов данных). Hash Joins (соединение хешированием) строит хеш-таблицу в памяти для меньшего отношения и сканирует большее отношение для поиска совпадений (оптимально для больших неотсортированных наборов). Merge Joins (соединение слиянием) сортирует оба отношения по ключу соединения и сканирует их параллельно (оптимально для больших предварительно отсортированных наборов или при наличии подходящих индексов)." - question: "Почему keyset-пагинация работает быстрее, чем LIMIT/OFFSET?" answer: "Запросы с LIMIT/OFFSET вынуждают СУБД прочитать и отбросить все строки вплоть до значения смещения (OFFSET), что ведет к линейному падению производительности O(N) по мере увеличения глубины страниц. Keyset-пагинация использует фильтр WHERE по уникальному отсортированному столбцу (например, `WHERE id > ?`), чтобы сразу перейти к целевому ряду по индексу, обеспечивая константное время поиска O(log N)." - question: "Что такое барьер оптимизации CTE?" answer: "В старых версиях Postgres (до 12) и в новых версиях при указании `MATERIALIZED` обобщенные табличные выражения (CTE) действуют как барьер оптимизации: СУБД сначала вычисляет CTE, сохраняет результат во временную область памяти и только потом соединяет его. Это мешает оптимизатору пробросить условия внешнего WHERE (например, по ID) внутрь CTE, что приводит к медленной работе." --- # Оптимизация запросов баз данных: мастер-класс Один медленный запрос может обрушить все приложение корпоративного уровня. В продакшене производительность баз данных редко упирается в мощность процессора; обычно все зависит от того, насколько эффективно планировщик запросов перемещается по дисковым страницам, строит таблицы соединений в памяти и обрабатывает конкурентный доступ. Когда вы пишете SQL-запрос, вы описываете, *какие* данные хотите получить, а не *как* их извлечь. За построение плана выполнения отвечает оптимизатор запросов СУБД. Чтобы создавать высокопроизводительные приложения, вы должны научиться думать как оптимизатор. На этом мастер-классе мы разберем планы выполнения, изучим алгоритмы соединений, исследуем масштабирование пагинации и проанализируем внутренние механизмы MVCC в PostgreSQL и MySQL. --- ## 1. Расшифровка EXPLAIN и EXPLAIN ANALYZE Чтобы оптимизировать запрос, сначала нужно изучить его план выполнения с помощью команды `EXPLAIN`. Однако стандартный `EXPLAIN` показывает лишь *оценку* стоимости запроса оптимизатором. Чтобы увидеть, что на самом деле происходило во время выполнения, используйте `EXPLAIN ANALYZE` (эта команда реально выполняет запрос). ### Понимание индикаторов стоимости (формат Postgres) План выполнения выводит стоимость в формате: `cost=0.00..431.25 rows=10500 width=244`. * **Startup Cost (`0.00`):** Затраты на подготовку к возврату первой строки (например, построение хеш-таблицы или сортировка узлов индекса). * **Total Cost (`431.25`):** Оценочная стоимость возврата всех строк. Измеряется в условных единицах чтения страниц (обычно `1.0` за последовательное чтение страницы, `4.0` за случайное чтение). * **Rows (`10500`):** Оценочное количество возвращаемых строк. * **Width (`244`):** Средний размер возвращаемой строки в байтах. ### Типы сканирования узлов 1. **Sequential Scan (Seq Scan):** Движок считывает всю таблицу от начала до конца. Это оптимально для маленьких таблиц или когда запрашивается $> 20\%$ строк таблицы. 2. **Index Scan:** Движок обходит дерево индекса B-Tree, собирает совпадающие TID/первичные ключи, а затем считывает соответствующие страницы из кучи/таблицы. Это связано со случайным доступом к диску. 3. **Index Only Scan:** Если все запрашиваемые столбцы содержатся в самом индексе, СУБД полностью пропускает чтение страниц таблицы. 4. **Bitmap Index Scan и Bitmap Heap Scan (Postgres):** При извлечении множества подходящих строк по индексу Postgres сначала строит битовую карту страниц в памяти (Bitmap Index Scan), а затем считывает эти страницы в их физическом порядке на диске (Bitmap Heap Scan). Это превращает медленный случайный ввод-вывод в более быстрый последовательный. ### Алгоритмы соединений (Join Algorithms) * **Nested Loop:** База данных сканирует внешнюю таблицу и для каждого её ряда ищет совпадения во внутренней таблице. Это работает очень быстро на малых объемах данных, особенно если столбец соединения во внутренней таблице проиндексирован. * **Hash Join:** База данных сканирует меньшую таблицу, строит хеш-таблицу в памяти по ключам соединения, а затем сканирует большую таблицу, хешируя её ключи для моментального поиска совпадений. Оптимально для больших неотсортированных наборов данных. * **Merge Join:** Обе таблицы сортируются по ключам соединения, после чего база данных сканирует их параллельно, сливая совпадающие строки. Это эффективно для очень больших наборов данных, особенно если они уже отсортированы индексами. --- ## 2. Продвинутая оптимизация JOIN и CTE Не все соединения одинаково полезны. То, как вы пишете подзапросы и CTE, сильно влияет на способность оптимизатора отсекать лишние пути поиска. ### Inner/Outer JOIN и Anti-JOIN Антисоединение (anti-join) находит записи в одной таблице, которых *нет* в другой. Разработчики часто пишут это через `NOT IN`, что приводит к низкой производительности и ошибкам, если подзапрос возвращает `NULL`. Оптимальный подход — использовать `NOT EXISTS`. ```sql -- database/queries/anti_join_bad.sql -- Плохо: NOT IN сканирует подзапрос и ведет себя непредсказуемо при наличии NULL SELECT id, name FROM users WHERE id NOT IN (SELECT user_id FROM orders); -- database/queries/anti_join_good.sql -- Хорошо: NOT EXISTS преобразуется в оптимизированный Hash Anti Join или Merge Anti Join SELECT u.id, u.name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id ); ``` ### LATERAL Joins (коррелированные подзапросы) Соединение `LATERAL` работает как цикл `foreach` в SQL. Оно позволяет подзапросу ссылаться на столбцы из предшествующих таблиц в секции `FROM`. Это незаменимо для извлечения «топ-N» записей для каждой группы. ```sql -- database/queries/lateral_join.sql -- Получить последние 3 заказа для каждого пользователя (Postgres и MySQL 8.0.14+) SELECT u.id, u.name, recent_orders.id AS order_id, recent_orders.amount, recent_orders.created_at FROM users u CROSS JOIN LATERAL ( SELECT o.id, o.amount, o.created_at FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 3 ) AS recent_orders; ``` ### Барьеры оптимизации CTE (Optimization Fences) Обобщенные табличные выражения (CTE) делают код чище, но исторически они выступали в роли **барьеров оптимизации**. * **Postgres (до версии 12):** CTE всегда материализовались (вычислялись и записывались во временное пространство) до выполнения внешнего запроса. Это не позволяло оптимизатору использовать индексы из внешних условий WHERE. * **Postgres (12+):** Теперь CTE встраиваются в код (inline) по умолчанию, если только они не рекурсивные или не имеют побочных эффектов. Вы можете принудительно управлять материализацией с помощью ключевых слов `MATERIALIZED` или `NOT MATERIALIZED`. ```sql -- database/queries/cte_fence.sql -- Запрещаем материализацию, чтобы планировщик мог пробросить условие по user_id внутрь CTE WITH user_stats AS NOT MATERIALIZED ( SELECT user_id, COUNT(*) as total_orders, SUM(amount) as total_spent FROM orders GROUP BY user_id ) SELECT u.name, s.total_orders, s.total_spent FROM users u JOIN user_stats s ON u.id = s.user_id WHERE u.id = 42; ``` --- ## 3. Масштабирование запросов: Keyset-пагинация и партиционирование Когда таблицы вырастают до миллиардов строк, простые запросы начинают тормозить, если вы не измените подход к сканированию данных. ### Keyset-пагинация (по курсору) против LIMIT / OFFSET Пагинация через смещение (`LIMIT 10 OFFSET 100000`) заставляет БД прочитать и отбросить 100 000 строк только для того, чтобы вернуть 10. Производительность падает линейно ($O(N)$), делая глубокие страницы недоступными по скорости. Keyset-пагинация фильтрует уже просмотренные строки с помощью секции where по сортируемому индексу, обеспечивая константное время поиска $O(\log N)$. ```sql -- database/queries/offset_pagination_bad.sql -- Плохо: сканирует и отбрасывает 100 000 записей перед возвратом 10 SELECT id, amount, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 100000; -- database/queries/keyset_pagination_good.sql -- Хорошо: переходит сразу к нужному набору по составному индексу SELECT id, amount, created_at FROM orders WHERE (created_at, id) < ('2026-05-30 15:30:00', 987654) ORDER BY created_at DESC, id DESC LIMIT 10; ``` > [!NOTE] > Keyset-пагинация требует детерминированной сортировки. Если вы сортируете по неуникальному столбцу вроде `created_at`, обязательно добавляйте уникальный столбец (например, `id`) в качестве предохранителя от пропуска или дублирования строк. ### Партиционирование таблиц Партиционирование разделяет одну большую таблицу на несколько физических таблиц меньшего размера (партиций) по ключевому значению (например, по диапазону дат), сохраняя единый логический интерфейс. * **Исключение партиций (Partition Pruning):** Когда запрос фильтрует по ключу партиционирования, планировщик полностью игнорирует ненужные партиции, сканируя только целевую. ```sql -- database/migrations/2026_05_31_partitioned_orders.sql -- Декларативное партиционирование по диапазону в PostgreSQL CREATE TABLE orders_partitioned ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (created_at, id) ) PARTITION BY RANGE (created_at); -- Создание отдельных таблиц-партиций CREATE TABLE orders_2026_q1 PARTITION OF orders_partitioned FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2026-04-01 00:00:00'); ``` --- ## 4. MVCC, очистка (Vacuum) и отставание очистки (Purge Lag) Конкурентный доступ в современных БД реализуется через **многоверсионность (MVCC)**. Вместо блокировки строк при чтении СУБД хранит несколько версий одной и той же строки одновременно. ### PostgreSQL MVCC и Autovacuum В Postgres каждая строка (кортеж) хранится на диске с системными заголовками, включая `xmin` (ID транзакции, создавшей строку) и `xmax` (ID транзакции, удалившей/изменившей строку). * **Обновления:** Операция `UPDATE` не перезаписывает строку на месте. Она записывает новый кортеж в кучу и устанавливает `xmax` на старом кортеже, указывая на транзакцию обновления. * **Раздувание (Bloat):** Старая версия строки становится «мертвым кортежем», когда её больше не видит ни одна активная транзакция. * **Vacuuming:** Postgres требует регулярного выполнения команды `VACUUM` (этим управляет демон autovacuum) для сканирования страниц, освобождения места от мертвых кортежей и обновления карты видимости. Если autovacuum не справляется при интенсивной записи, раздувание таблиц и индексов снижает скорость запросов. ### MySQL InnoDB MVCC и Undo-логи Движок InnoDB в MySQL обрабатывает MVCC иначе. * **Обновления на месте:** InnoDB выполняет обновления на месте в листовом узле кластеризованного индекса. * **Undo-логи и сегменты отката:** Старая версия строки не хранится в основной таблице. InnoDB записывает историческое состояние строки в **Undo-лог** и сохраняет указатель отката внутри обновленной строки. Когда транзакции-читателю нужна старая версия, она читает текущую строку и восстанавливает прежнее состояние по цепочке undo-логов. * **Purge-потоки:** По мере завершения старых транзакций фоновый процесс **Purge Thread** удаляет устаревшие undo-логи и очищает записи вторичных индексов. При высокой нагрузке на запись может возникнуть отставание очистки (**Purge Lag**), что ведет к лавинообразному росту файлов undo-логов и падению производительности. --- ## 5. Частые ошибки ### Ошибка 1: Глубокая пагинация через OFFSET **Плохая практика:** ```sql -- database/queries/bad_pagination.sql SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 50000; ``` *Почему это плохо:* База данных должна пройти по индексу/куче 50 000 записей, загрузить их в память и тут же отбросить, впустую расходуя ресурсы процессора и диска. **Хорошая практика:** ```sql -- database/queries/good_pagination.sql -- Keyset-пагинация с использованием последнего известного ID (например, 50000) SELECT * FROM users WHERE id > 50000 ORDER BY id LIMIT 20; ``` *Почему это хорошо:* База данных выполняет поиск по индексу за $O(\log N)$, сразу переходя к `id > 50000`, минуя сканирование ненужных строк. ### Ошибка 2: Отсутствие индекса на внешних ключах (Foreign Keys) **Плохая практика:** ```sql -- database/migrations/bad_foreign_key.sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT, FOREIGN KEY (user_id) REFERENCES users(id) ); ``` *Почему это плохо:* PostgreSQL и MySQL не создают индексы на внешних ключах автоматически. Если вы часто делаете JOIN таблиц по `user_id` или удаляете пользователей (что вызывает каскадные проверки), СУБД будет вынуждена делать последовательное сканирование (Seq Scan) дочерней таблицы. --- ## 6. Интерактивный тест ### Вопрос 1 Во время выполнения `EXPLAIN ANALYZE` в Postgres вы заметили узел `Bitmap Heap Scan`, идущий после `Bitmap Index Scan`. О чем это говорит?
Нажмите, чтобы увидеть ответ **Ответ:** Это говорит о том, что Postgres посчитал прямое чтение строк по отдельным указателям индекса слишком медленным из-за случайного ввода-вывода (random I/O). Вместо этого: 1. `Bitmap Index Scan` просканировал индекс и построил в памяти битовую карту страниц таблицы, содержащих подходящие строки. 2. `Bitmap Heap Scan` затем считал эти страницы в последовательном физическом порядке на диске, извлекая нужные строки. Это превращает медленный случайный ввод-вывод в более быстрое последовательное чтение.
--- ### Вопрос 2 Почему при интенсивной записи в MySQL InnoDB размер табличного пространства undo-логов начинает лавинообразно расти и как это влияет на запросы чтения, которым требуются старые данные?
Нажмите, чтобы увидеть ответ **Ответ:** При высокой интенсивности записи поток очистки (Purge Thread) не успевает вовремя удалять устаревшие сегменты отката (возникает Purge Lag). Из-за этого пространство undo-логов растет, чтобы хранить старые версии строк. Старые читающие транзакции начнут работать медленнее, так как им придется проходить по длинным цепочкам undo-логов для восстановления состояния данных на момент начала их транзакции.
--- ### Вопрос 3 Почему CTE, определенный через стандартную конструкцию `WITH` в Postgres 10, может стать бутылочным горлышком производительности, если во внешнем запросе идет фильтрация по конкретному ID?
Нажмите, чтобы увидеть ответ **Ответ:** В Postgres 10 (и более старых версиях) CTE действуют как барьер оптимизации. База данных сначала вычисляет CTE целиком и материализует его результаты в памяти или на диске. Только после этого выполняется внешний запрос, где применяется фильтр `WHERE id = 42`. В Postgres 12+ (или при использовании `WITH ... AS NOT MATERIALIZED`) оптимизатор может встроить CTE напрямую, что позволяет прокинуть фильтр `WHERE id = 42` внутрь подзапроса и использовать индекс.