--- 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` всередину підзапиту та використовувати індекс.