Индексы и транзакции в базе данных

Как устроен индекс на B-дереве, когда он ускоряет и когда мешает, свойства ACID, уровни изоляции транзакций, аномалии чтения, блокировки и взаимоблокировки в СУБД.

Индексы и транзакции — две темы, без которых практическая часть курсовой по базам данных выглядит поверхностной. Первая отвечает за скорость, вторая за целостность данных.

Зачем нужен индекс

Без индекса СУБД читает всю таблицу целиком — полное сканирование со сложностью O(n). Индекс на B-дереве позволяет найти нужную строку за O(log n): в таблице на миллион строк это порядка двадцати сравнений вместо миллиона.

CREATE INDEX idx_employees_dept ON employees(dept_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Составной индекс: порядок столбцов имеет значение
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Частичный индекс: только активные записи
CREATE INDEX idx_active_users ON users(last_login) WHERE is_active = true;

-- Посмотреть, используется ли индекс
EXPLAIN ANALYZE SELECT * FROM employees WHERE dept_id = 5;

Цена индекса

• Занимает место на диске — иногда сопоставимо с самой таблицей.
• Замедляет INSERT, UPDATE и DELETE: индекс нужно перестраивать при каждой записи.
• На маленькой таблице бесполезен: полное сканирование сотни строк быстрее обхода дерева.
• На столбце с малым числом различных значений (пол, статус) обычный индекс почти не помогает.
• Не используется, если столбец обёрнут функцией: WHERE UPPER(name) = 'ИВАНОВ' — нужен индекс по выражению.

Практическое правило: индексируйте внешние ключи, столбцы в WHERE и JOIN, поля сортировки. Не индексируйте всё подряд — на таблице с интенсивной записью лишние индексы обходятся дороже, чем экономят.

Транзакции и ACID

BEGIN;
  UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

-- При ошибке между операциями:
ROLLBACK;   -- деньги не исчезнут со счёта отправителя

Аномалии параллельного доступа

Уровни изоляции

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  SELECT balance FROM accounts WHERE id = 1;
  -- ... расчёты ...
  SELECT balance FROM accounts WHERE id = 1;   -- гарантированно то же значение
COMMIT;

Блокировки

• Разделяемая (S) — на чтение, несколько транзакций могут держать её одновременно.
• Исключительная (X) — на запись, несовместима ни с какой другой.
• Гранулярность: строка, страница, таблица. Чем мельче, тем выше параллелизм и больше накладных расходов на учёт.
• Оптимистичная блокировка через версионирование: проверяют номер версии при записи и повторяют операцию при конфликте.

-- Явная блокировка строки до конца транзакции
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Оптимистичный вариант без блокировки
UPDATE accounts
   SET balance = 900, version = version + 1
 WHERE id = 1 AND version = 5;
-- Изменено 0 строк — значит кто-то опередил, повторяем операцию

Взаимоблокировки в СУБД

Две транзакции блокируют строки в разном порядке и ждут друг друга. Современные СУБД обнаруживают такой цикл автоматически и снимают одну транзакцию с ошибкой deadlock detected — приложение должно её повторить.

Профилактика та же, что в операционных системах: единый порядок обращения к строкам, короткие транзакции, отсутствие пользовательского ввода внутри транзакции.

Что показать в курсовой

1. Замерить время запроса до и после создания индекса на таблице хотя бы в сто тысяч строк.
2. Приложить вывод EXPLAIN ANALYZE с планом Seq Scan и Index Scan — наглядное доказательство эффекта.
3. Продемонстрировать откат транзакции при нарушении ограничения целостности.
4. Смоделировать аномалию на низком уровне изоляции и показать, как её устраняет повышение уровня.