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

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) ще швидша.

Докладніше в документації: TRUNCATE

Складений індекс (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 зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.

Докладніше в документації: Багатоколонкові індекси

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 хв. Після завершення - розбір кожної помилки з посиланням на питання.