SQL Performance Tuning Vocabulary (англійською)

Налаштування швидкодії SQL вимагає спеціалізованого словника, що охоплює виконання запитів, індексування, статистику, блокування і оптимізатор запитів. Цей словник з’ являється у документації, звітах про інциденти, інструментах моніторингу бази даних і оглядах швидкодії. У цьому довіднику наведено 50 термінів, які вам слід знати, щоб вільно обговорювати швидкодію запиту як адміністратору бази даних або старшому розробнику.

---

Основні принципи виконання запитів

Планування та виконання проекту

** План запиту ** (або ** План виконання **) — це набір кроків, які рушій бази даних обирає для виконання запиту SQL — які індекси використовувати, у якому порядку об’ єднувати таблиці, які алгоритми застосовувати.

План запиту є початковою точкою для всіх аналізів продуктивності.

«Перед тим, як ми почнемо налаштовувати, давайте поглянемо на план виконання — EXPLAIN ANALYZE покаже нам точно, де знаходиться вартість»

Планування запитів / оптимізація запитів

** Оптимізатор запиту ** — це компонент рушія бази даних, який створює план запиту шляхом оцінки можливих стратегій виконання і вибору найдешевшої з них на основі статистики.

Cost

У планах запитів, cost є оцінкою оптимізатора запиту обчислювальних ресурсів, необхідних для виконання операції — не час стелі. Низька вартість = більш ефективний план.

«Оцінена вартість сканування цієї таблиці становить 45 000 — саме тому запит є повільним»

Фактичних проти оцінених рядків

Коли ви запускаєте EXPLAIN ANALYZE, ви бачите ** оцінені рядки ** (що планувальник передбачив) і ** фактичні рядки ** (що було оброблено). Велика відмінність вказує на застарілу статистику.

“Оцінка рядків: 12. Фактична кількість рядків: 847 000. Планувальник масово недооцінив — нам потрібно оновити статистику»

---

Типи сканування

Послідовне сканування (повне сканування таблиці)

** Послідовне сканування ** читає кожен рядок таблиці від початку до кінця. Придатний, якщо великий відсоток рядків відповідає умові WHERE.

«Цей запит викликає послідовне сканування на 200-мільйонній таблиці рядків — це займає 45 секунд. Нам потрібен індекс»

Індексне сканування

** Сканування індексу ** обходить дерево індексу, щоб знайти відповідні рядки, а потім отримує фактичні рядки з таблиці. Ефективний для вибіркових запитів (небагато збігів рядків).

Сканування тільки індексу

** Сканування лише індексу ** задовольняє запит повністю з індексу без доступу до таблиці — можливо лише коли індекс покриває всі потрібні стовпчики (** покриваючий індекс**).

«Сканування тільки індексу тут ідеальне — запит потребує тільки стовпців в індексі, тому ми ніколи не торкаємося таблиці»

Сканування растрової купи

Сканування стека ** бітової мапи ** (PostgreSQL) спочатку створює бітову мапу з індексу відповідних розташувань рядків, а потім отримує фактичні рядки у фізичному порядку. Ефективний для помірно вибіркових запитів.

Сканування вільного індексу

** Сканування вільного індексу ** (MySQL) читає один рядок на межу індексного ключа — дуже ефективно для SELECT DISTINCT або GROUP BY на індексованій колонці.

---

Joins

Вкладене циклічне з’ єднання

** вкладене з’ єднання у циклі ** ітерує над зовнішньою таблицею і для кожного рядка шукає відповідності у внутрішній таблиці. Ефективний, коли внутрішня сторона маленька і індексована.

«Вбудоване з’єднання петлі тут в порядку — внутрішня таблиця мала, а стовпець з’єднання індексований.»

Приєднатися до хешування

** Hash join ** створює таблицю гешів з меншої таблиці, а потім перевіряє її для кожного рядка більшої таблиці. Ефективна для великих, несортованих таблиць без відповідних індексів.

Об’єднання (Sort Merge Join)

** Об’ єднання з об’ єднанням ** вимагає, щоб обидва вхідні набори були впорядковані за ключем об’ єднання. Ефективний, якщо дані вже впорядковані (наприклад, упорядковане сканування індексу надходить безпосередньо до з’ єднання).

Перехресне з’ єднання / декартове добуток

** Cross join ** поєднує кожен рядок однієї таблиці з кожним рядком іншої — N × M рядків. Майже завжди вада, якщо вона виявлена у непередбаченому плані запиту.

“Цей запит робить ненавмисне перехресне з’єднання — 1000 × 50000 = 50 мільйонів рядків, що обробляються. Ми не маємо умови JOIN»

---

Indexes

Індекс B-Tree

B-дерево індекс є типовим типом індексу в більшості баз даних — збалансований дерево, що ефективно підтримує рівність, діапазон, ORDER BY і MIN/MAX операції.

Композитний індекс

** Складений індекс ** (індекс з декількома стовпчиками) охоплює декілька стовпчиків. Порядок стовпців має значення: (a, b) підтримує запити на a сам по собі або a AND b, але не b сам по собі.

«Композиційний індекс на (customer_id, created_at) підтримує наші найпоширеніші запити — фільтр за клієнтом, а потім сортування за датою»

Покриття індексу

** Покриваючий індекс ** містить всі стовпчики, які потрібні для запиту — це дозволяє виконувати сканування лише індексів і виключає отримання таблиць.

Частковий індекс

** Частковий індекс ** індексує лише підмножину рядків, які задовольняють умові WHERE — менший і ефективніший для певних шаблонів запиту.

«Ми створили частковий індекс на (user_id) WHERE status = 'pending' — тільки очікувані замовлення потребують швидкого пошуку»

Не використовується індекс

Невикористовуваний індекс споживає надлишок запису (кожна операція INSERT/UPDATE/DELETE підтримує його) без надання переваги запиту. Регулярные аудиты должны их удалить.

Індекс розширення

** Роздутість індексу ** — це накопичення мертвих сторінок у індексі через оновлення і вилучення — збільшення розміру індексу і часу сканування. Регулярний VACUUM або реорганізація виправляє це.

---

Statistics

Статистична таблиця

** Статистика ** — це метадані, які оптимізатор запиту використовує для оцінювання кількості рядків, розподілів значень і вибірковості. Команди: ANALYZE (PostgreSQL), UPDATE STATISTICS (SQL Server), ANALYZE TABLE (MySQL).

Selectivity

** Селективність ** — це частка рядків, які відповідають предикату. Висока селективність (0,01 = 1% рядків) → ефективне сканування індексів. Низька селективність (0,9 = 90% рядків) → послідовне сканування може бути кращим.

Cardinality

** Кардинальність ** — це кількість різних значень у стовпчику. Булева величина має кардиналість 2; UUID має високу кардиналість (мільйони). Стовпчики з високою кардиналістю є кращими кандидатами для індексів.

Статистичні дані

Якщо ** статистика ** застаріла (не оновлюється після значних змін даних), оптимізатор приймає погані рішення на основі застарілих оцінок.

«Статистика не була оновлена, оскільки ми завантажили 30 мільйонів рядків минулої ночі. ANALYZE потрібно запустити до ранкових звітів»

---

Locking

Lock

** Замок ** керує одночасним доступом до ресурсу бази даних. Без блокувань одночасні записи можуть пошкодити дані.

Спільний блок (блок читання)

** спільний блок ** дозволяє одночасне читання, але забороняє запис. Кілька сеансів можуть одночасно мати спільні замки.

Запис блокування (Write Lock)

** виключний замок ** дозволяє запис і забороняє будь- який інший доступ — неможливість одночасного читання або запису під час його утримання.

Deadlock

** Deadlock ** відбувається, коли два сеанси мають блокування, яке потрібно іншому — обидва блокуються на неограничений час. База даних виявляє і розв’ язує його, вбиваючи одну транзакцію.

«У нас є повторюваний затор між обробкою замовлень і оновленням інвентарних операцій — вони отримують замки в протилежному порядку»

Заблокувати суперечку

** Конфлікт щодо блокування ** — це блокування одного сеансу під час очікування звільнення блокування іншого сеансу. Висока конкуренція є головним джерелом погіршення продуктивності.

«Інцидент з затримкою репліки був викликаний суперечкою щодо блокування — один аналітичний запит утримував блокування таблиці протягом 2 годин»

Тайм- аут очікування блокування

** Тайм- аут очікування блокування ** — це максимальний час очікування сеансу на блокування, після якого програма закінчить роботу з помилкою. Налаштовується для кожного сеансу або глобально.

MVCC (Multi-Version Concurrency Control) — багатоверсійний контроль одночасності

** MVCC ** — це механізм керування одночасністю, за якого читання ніколи не блокує запис, а запис ніколи не блокує читання — кожна транзакція отримує знімок даних з часу її початку. Використовується PostgreSQL, Oracle, MySQL (InnoDB) та іншими.

---

Оптимізація технологічних процесів

Перезапис запитів

** Переписування запиту ** змінює логічну структуру запиту SQL, щоб уможливити більш ефективний план виконання, наприклад, заміна пов’ язаного підзапиту на JOIN.

Натисніть на предикат

** Пересунути до нижнього рядка предка ** пересуває умови фільтра (клаузули WHERE) якомога ближче до джерела даних — зменшуючи кількість рядків, які обробляються на початковому етапі, і передаючи менше даних через конвеєр.

Обрізання розділів

** Обрізання розділів ** надає змогу оптимізатору пропускати всі розділи, які не відповідають умові WHERE — це критичне для швидкодії роботи з таблицями з розділами.

«З щомісячним розділенням і розділенням розділів, запит на замовлення минулого тижня сканує тільки 2 з 36 розділів»

Матеріалізований погляд

** матеріалізований перегляд ** — це попередньо обчислений, збережений результат запиту, який оновлюється періодично або за потреби. Значно прискорює складні агрегації.

«Запит на виконавчу панель займав 90 секунд — ми замінили його матеріалізованим виглядом, який попередньо агрегує щодня. Тепер він повертається за 200 мс»

Кеш запитів

** кеш запиту ** зберігає результати ідентичних запитів — наступні виконання повертають кешований результат. Менш поширений у сучасних базах даних через складність анульування.

---

Корисні фрази

** Діагностика продуктивності: **

• “Запустімо EXPLAIN ANALYZE для цього запиту — я хочу побачити фактичні проти оцінених рядків.”
• “Це послідовне сканування таблиці з 200М рядків є нашим в’ язким місцем — нам потрібно додати індекс на customer_id.”
• • “Статистика для цієї таблиці застаріла — саме тому планувальник обрав вкладений цикл замість геш- з’ єднання.” *

** Пояснення результатів у звітах про інциденти: **

• • “Коренева причина була суперечкою щодо блокування — довготривалий запит мав виключний блокування і блокував реплікацію протягом 2 годин.” *
• • “Після додавання індексу покриття, затримка P99 зменшилася з 4,2 секунд до 180 мс.” *

---

Practice

Збудуйте свій словник DBA за допомогою ** Набір вправ з адміністрування баз даних. Name ** і ** ДБЯ навчальний шлях **.