SQL: продуктивність запитів
20 питань · ~20 хв · Версія v3.0
Увійдіть, щоб продовжити
Плани виконання, умови, що вимикають індекс, пагінація, MVCC і роздування таблиць, пули з'єднань і кешування планів - питання від middle до lead.
- За спробу
- 20
- У пулі
- 69
- Проходжень
- 0
- Середній бал
- -
- Пройшли на 70%+
- -
Питання для підготовки
40 питаньСкладений індекс (a, b, c) впорядковує рядки спершу за a, всередині однакових a - за b, далі за c. Як телефонний довідник: за прізвищем, потім за ім'ям.
CREATE INDEX orders_user_status_idx ON orders (user_id, status, created_at);
Правило лівого префікса: індекс допомагає, коли умова використовує колонки з початку:
WHERE user_id = 5- так.WHERE user_id = 5 AND status = 'paid'- так.WHERE user_id = 5 AND status = 'paid' ORDER BY created_at- так, і сортування береться з індексу.WHERE status = 'paid'- ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.
Як обирати порядок:
- Спершу колонки з рівністю (
=), потім ті, що в діапазоні (>,BETWEEN) чи вORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють. - Серед колонок з рівністю - ті, що використовуються в більшості запитів.
Наслідок: індекс (user_id, status) робить окремий індекс на user_id зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.
Стандарт SQL визначає чотири рівні - від найслабшого до найсуворішого. Кожен забороняє більше аномалій:
| Рівень | Брудне читання | Неповторюване читання | Фантоми |
|---|---|---|---|
| Read Uncommitted | можливе | можливе | можливі |
| Read Committed | ні | можливе | можливі |
| Repeatable Read | ні | ні | можливі (за стандартом) |
| Serializable | ні | ні | ні |
- Брудне читання - бачимо незакомічені зміни іншої транзакції.
- Неповторюване читання - той самий рядок, прочитаний двічі, змінився між читаннями.
- Фантом - повторний запит з тією ж умовою повертає нові рядки.
На практиці:
- PostgreSQL за замовчуванням Read Committed. Read Uncommitted поводиться як Read Committed. Його Repeatable Read фантомів не допускає (снапшот на всю транзакцію), а Serializable реалізований через SSI.
- MySQL InnoDB за замовчуванням Repeatable Read. Звичайні
SELECTчитають снапшот, але блокувальні читання (FOR UPDATE) іUPDATEбачать найсвіжіші дані.
Що це означає для коду: класична гонка «прочитав баланс → перевірив → записав» не захищена на Read Committed. Рішення - атомарний UPDATE ... SET balance = balance - 100 WHERE balance >= 100, блокування FOR UPDATE або Serializable.
На Repeatable Read і Serializable база може відхилити транзакцію з помилкою серіалізації. Застосунок мусить бути готовий повторити таку транзакцію - це частина контракту, а не збій.
MVCC (Multi-Version Concurrency Control) - спосіб дати паралельним транзакціям узгоджений знімок даних без блокувань на читання. Читачі не блокують письменників, письменники - читачів.
Як це влаштовано в PostgreSQL: UPDATE не змінює рядок на місці, а створює нову версію рядка, позначаючи стару як застарілу (з якої транзакції вона вже невидима). DELETE лише позначає рядок. Кожна транзакція бачить ті версії, що були актуальні на момент її знімка.
Звідси потреба у VACUUM: старі версії («мертві кортежі») лишаються в таблиці, доки жодна транзакція вже не може їх бачити. VACUUM:
- звільняє місце мертвих кортежів для повторного використання;
- оновлює visibility map (потрібна для index-only scan);
- «заморожує» старі ідентифікатори транзакцій, щоб уникнути переповнення лічильника транзакцій (transaction ID wraparound) - аварійної ситуації, за якої база перестає приймати запис.
Зазвичай усе це робить фоновий autovacuum.
Типові проблеми на проді:
- Роздування (bloat) таблиць з частими
UPDATE: autovacuum не встигає, таблиця й індекси ростуть, запити сповільнюються. Лікують тонким налаштуванням autovacuum для конкретних таблиць. - Довга транзакція (чи забута відкрита сесія
idle in transaction) не дає вакууму прибирати навіть старі версії по всій базі. VACUUM FULLповертає місце ОС, але переписує таблицю під ексклюзивним блокуванням - на проді його уникають.
InnoDB теж використовує MVCC, але зберігає старі версії в undo log, тож окремого вакууму не потребує (його роль виконує фоновий purge).
EXPLAIN показує, як база збирається виконати запит: у якому порядку читати таблиці, чи використовувати індекси, як з'єднувати. Сам запит при цьому не виконується.
EXPLAIN SELECT * FROM orders WHERE user_id = 5;
Index Scan using orders_user_id_idx on orders (cost=0.43..8.45 rows=3 width=64)
Index Cond: (user_id = 5)
Що шукати новачку:
Seq Scan(PostgreSQL) /type: ALL(MySQL) на великій таблиці - повний перегляд таблиці. Часто ознака відсутнього індексу.Index Scan/Index Only Scan(PostgreSQL),type: ref,range(MySQL) - використано індекс.rows- скільки рядків база очікує. Якщо оцінка сильно відрізняється від реальності, план може бути поганим.Sortз великою кількістю рядків чиUsing filesortу MySQL - сортування, яке не взяли з індексу.
cost - умовні одиниці вартості, не мілісекунди. Перше число - вартість до першого рядка, друге - до останнього.
План читають знизу вгору й зсередини назовні: вкладені вузли виконуються першими й передають рядки батьківському.
Наступний крок - EXPLAIN ANALYZE, який виконує запит і показує реальний час і кількість рядків.
Кожне з'єднання з PostgreSQL - це окремий процес сервера з власною пам'яттю. Сотні з'єднань споживають багато ресурсів, а встановлення нового з'єднання помітно повільне. Тим часом PHP-FPM з 200 воркерами на кількох серверах легко відкриває тисячу з'єднань, більшість з яких простоює.
PgBouncer стоїть між застосунком і базою: тисячі клієнтських з'єднань він обслуговує невеликим пулом серверних.
Режими:
- Session pooling - серверне з'єднання видається клієнту на всю сесію. Безпечно, але виграш малий.
- Transaction pooling - з'єднання видається лише на час транзакції (або окремого запиту поза транзакцією). Головна економія - і головні сюрпризи.
- Statement pooling - на окремий запит; транзакції з кількох запитів неможливі.
Що ламається в transaction pooling: усе, що прив'язане до сесії, бо наступний запит може піти іншим серверним з'єднанням:
SET(часовий пояс,search_path,statement_timeout) безLOCAL- налаштування «перетече» до іншого клієнта.- Сесійні advisory-блокування,
LISTEN/NOTIFY, тимчасові таблиці. - Підготовлені запити (prepared statements) - PgBouncer підтримує їх на рівні протоколу лише з версії 1.21 і з налаштуванням
max_prepared_statements. До того PDO з емуляцією вимкненою отримував помилки на кшталтprepared statement does not exist.
Альтернативи й доповнення: керовані пули (RDS Proxy, Supavisor у Supabase), довгоживучі процеси (Octane), що тримають сталі з'єднання, і просто обмеження кількості PHP-воркерів під реальну ємність бази.
Прочитати - ще не значить знати
20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.