SQL: індекси й транзакції
20 питань · ~20 хв · Версія v3.0
Увійдіть, щоб продовжити
Як працюють і коли не допомагають індекси, плани запитів, транзакції й рівні ізоляції, блокування й дедлоки - на PostgreSQL і MySQL.
- За спробу
- 20
- У пулі
- 66
- Проходжень
- 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, мало що дасть - базі все одно читати майже всю таблицю.
DELETE FROM orders видаляє рядки по одному: позначає кожен як видалений (MVCC), запускає тригери ON DELETE, перевіряє зовнішні ключі, може мати WHERE. Місце звільняється пізніше, вакуумом.
TRUNCATE orders звільняє таблицю цілком - фактично підміняє файли таблиці порожніми. Миттєво навіть на мільярді рядків, і місце повертається одразу.
Відмінності:
DELETE |
TRUNCATE |
|
|---|---|---|
Умова WHERE |
так | ні, лише вся таблиця |
| Швидкість на великій таблиці | повільно, пропорційно кількості рядків | миттєво |
| Тригери на рядки | спрацьовують | ні (є окремі ON TRUNCATE) |
| Блокування | рядків | ексклюзивне на таблицю (ACCESS EXCLUSIVE) |
Лічильник identity/serial |
не скидає | скидає з RESTART IDENTITY |
| Зовнішні ключі | перевіряються по рядках | помилка, якщо на таблицю посилаються, без CASCADE |
Чи можна відкотити: у PostgreSQL так - TRUNCATE транзакційний:
BEGIN;
TRUNCATE orders;
ROLLBACK; -- дані на місці
У MySQL TRUNCATE - DDL-команда з неявним комітом, і відкотити її не можна.
Застереження:
TRUNCATEбере найсильніше блокування: поки транзакція з ним не завершилася, таблицю не може читати ніхто.TRUNCATE ... CASCADEочистить і всі таблиці, що посилаються на цю, - легко знищити більше, ніж планували.- У тестах
TRUNCATEміж тестами - швидкий спосіб очищення (так працюєDatabaseTruncationу Laravel), але транзакція з відкатом (RefreshDatabase) ще швидша.
Складений індекс (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 зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.
ACID - чотири гарантії, які дає транзакція в реляційній базі:
- Atomicity (атомарність) - усі операції транзакції виконуються або всі разом, або жодна. Переказ грошей не може зняти суму з одного рахунку й не зарахувати на інший.
- Consistency (узгодженість) - транзакція переводить базу з одного коректного стану в інший: обмеження (
NOT NULL, унікальність, зовнішні ключі,CHECK) виконуються після кожної завершеної транзакції. - Isolation (ізольованість) - паралельні транзакції не бачать проміжних станів одна одної. Наскільки суворо - визначає рівень ізоляції.
- Durability (довговічність) - після
COMMITдані не зникнуть навіть при падінні сервера: їх уже записано в журнал (WAL у PostgreSQL, redo log в InnoDB) на диск.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
Якщо між двома UPDATE сервер впаде, після перезапуску жодної зміни не буде - атомарність.
Важливо для співбесіди: ізольованість не означає «транзакції виконуються по черзі». На рівнях за замовчуванням (Read Committed у PostgreSQL, Repeatable Read у MySQL) деякі аномалії паралельного виконання можливі, і їх треба враховувати в коді.
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), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.
Прочитати - ще не значить знати
20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.