SQL: продуктивність запитів
20 питань · ~20 хв · Версія v3.0
Увійдіть, щоб продовжити
Плани виконання, умови, що вимикають індекс, пагінація, MVCC і роздування таблиць, пули з'єднань і кешування планів - питання від middle до lead.
- За спробу
- 20
- У пулі
- 69
- Проходжень
- 0
- Середній бал
- -
- Пройшли на 70%+
- -
Питання для підготовки
40 питаньІндекс - окрема відсортована структура (найчастіше B-дерево), яка зберігає значення колонки й посилання на рядки таблиці. Як предметний покажчик у книжці: щоб знайти сторінку, не треба гортати всю книгу.
Без індексу запит WHERE email = 'a@b.com' змушує базу переглянути всю таблицю (Seq Scan / full table scan). З індексом пошук займає кілька кроків по дереву навіть на мільйонах рядків.
CREATE INDEX users_email_idx ON users (email);
Ціна індексу:
- Запис сповільнюється: кожен
INSERT,UPDATEіндексованої колонки йDELETEзмушує базу оновити ще й індекс. Десять індексів на таблиці - десять додаткових записів на кожну вставку. - Місце на диску і в пам'яті.
Що індексувати: колонки з WHERE, JOIN і ORDER BY у частих запитах, зовнішні ключі. Не варто - колонки, за якими не шукають, і таблиці на кілька сотень рядків.
Важливо: індекс корисний, коли умова відсіює більшість рядків. Колонка is_active, де 95% значень true, мало що дасть - базі все одно читати майже всю таблицю.
Складений індекс (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 зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.
PostgreSQL має кілька видів індексів, кожен під свій тип запитів:
- B-tree (за замовчуванням) - рівність, діапазони (
<,>,BETWEEN), сортування,LIKE 'початок%'. Покриває більшість випадків. - Hash - лише рівність (
=). З PostgreSQL 10 надійний (записується в WAL), але виграш над B-tree рідко помітний. - GIN (Generalized Inverted Index) - для значень, що містять багато елементів: масиви,
jsonb, повнотекстовий пошук (tsvector), триграми (pg_trgm). Шукає «рядки, де є цей елемент». - GiST - для геометрії й діапазонів: «перетинається», «містить», «найближчий» (PostGIS,
tsrange, exclusion constraints, триграми з пошуком найближчих). - SP-GiST - для нерівномірно розподілених даних: IP-адреси, телефонні префікси, квадродерева.
- BRIN - крихітний індекс для дуже великих таблиць, де значення фізично впорядковані на диску (час вставки в журналі подій).
CREATE INDEX orders_user_idx ON orders (user_id); -- B-tree
CREATE INDEX products_tags_idx ON products USING gin (tags); -- масив
CREATE INDEX docs_search_idx ON docs USING gin (to_tsvector('simple', body));
CREATE INDEX bookings_period_idx ON bookings USING gist (period); -- tsrange
CREATE INDEX events_created_brin ON events USING brin (created_at);
Як обирати: почати з B-tree. Інші типи - коли запит використовує оператори, які B-tree не підтримує (@>, &&, @@, <->), або коли таблиця настільки велика, що розмір індексу стає проблемою (BRIN).
Нюанс GIN: швидкий на читання, але дорожчий на запис. Тому він накопичує зміни в «відкладеному списку» (fastupdate), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.
Стандарт 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).
Прочитати - ще не значить знати
20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.