Увійти Реєстрація
Блог Серії
Кар'єра
Вакансії Компанії
Навчання
Документація Співбесіди Тестування Відео
Екосистема
Пакети Ресурси Проєкти Інструменти Події
Інше
Про нас Реклама

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' - ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.

Як обирати порядок:

  1. Спершу колонки з рівністю (=), потім ті, що в діапазоні (>, BETWEEN) чи в ORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють.
  2. Серед колонок з рівністю - ті, що використовуються в більшості запитів.

Наслідок: індекс (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).

Докладніше в документації: Регулярне очищення (VACUUM)

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, який виконує запит і показує реальний час і кількість рядків.

Докладніше в документації: Використання EXPLAIN

Кожне з'єднання з 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-воркерів під реальну ємність бази.

Докладніше в документації: Можливості PgBouncer

Прочитати - ще не значить знати

20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.