Лекция 8. Индексы, транзакции и ACID

Раздел 3. Базы данных в веб-приложениях Продолжительность: ~1 час 30 минут

---

Введение

В прошлых лекциях мы научились проектировать таблицы и писать SQL-запросы. Но как только приложение выходит в продакшн, появляются два новых вопроса:

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

Эти темы критичны именно для веб-приложений, где десятки и сотни запросов выполняются параллельно. Разберём всё по порядку.

---

1. Индексы и производительность

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

Представьте таблицу users на 5 миллионов строк и запрос:

SELECT * FROM users WHERE email = 'user@example.com';

Без индекса СУБД вынуждена прочитать все строки таблицы и сравнить каждую с условием. Это называется полное сканирование таблицы (full table scan, или Seq Scan в PostgreSQL). Его стоимость растёт линейно с числом строк: чем больше данных, тем медленнее.

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

С индексом по email поиск выполняется не за миллионы сравнений, а за единицы — СУБД спускается по дереву от корня к листу.

1.2. B-tree: как устроен индекс (обзор)

По умолчанию большинство СУБД (PostgreSQL, MySQL/InnoDB, SQLite) создают индексы типа B-tree (точнее B⁺-дерево). Это сбалансированное дерево поиска, оптимизированное для дисковых операций.

Ключевые свойства:

• Дерево сбалансировано: путь от корня до любого листа имеет примерно одинаковую длину.
• Каждый узел содержит много ключей (сотни), поэтому дерево «широкое и низкое» — обычно 3–4 уровня даже для миллионов строк.
• Значения хранятся в отсортированном порядке, листья связаны в список.

Из этого следуют два важных факта:

• Поиск, вставка и удаление выполняются за O(log n) — логарифмически, а не линейно.
• B-tree эффективен не только для точного поиска (=), но и для диапазонов (<, >, BETWEEN) и для сортировки (ORDER BY), потому что данные уже упорядочены.

Помимо B-tree есть и другие типы индексов (Hash — только для =, GIN/GiST — для полнотекстового поиска и JSON, BRIN — для очень больших таблиц). В большинстве веб-приложений хватает B-tree, поэтому именно он используется по умолчанию.

1.3. Когда создавать индексы

Хорошие кандидаты на индексирование — столбцы, которые часто участвуют в:

• условиях WHERE (WHERE email = ...);
• соединениях JOIN (внешние ключи почти всегда стоит индексировать);
• сортировках ORDER BY и группировках GROUP BY;
• ограничениях уникальности (UNIQUE автоматически создаёт индекс).

-- Поиск пользователя по email — основной сценарий
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Часто ищем OTP-коды по email
CREATE INDEX idx_otp_email ON otp_verification(email);

-- Удаляем просроченные коды по дате
CREATE INDEX idx_otp_created_at ON otp_verification(created_at);

Составные (многоколоночные) индексы

Если запрос фильтрует и сортирует сразу по нескольким столбцам, помогает составной индекс:

-- Найти последний код для конкретного email
SELECT code FROM otp_verification
WHERE email = 'user@example.com'
ORDER BY created_at DESC
LIMIT 1;

-- Этот индекс покрывает и фильтр по email, и сортировку по дате
CREATE INDEX idx_otp_email_created ON otp_verification(email, created_at);

Важен порядок столбцов. Составной индекс (email, created_at) работает по принципу «левого префикса»: он эффективен для условий по email и по email + created_at, но не поможет запросу, который фильтрует только по created_at. Правило: сначала ставьте столбцы для точного равенства, потом — для диапазонов и сортировки.

1.4. Цена индексов

Индексы не бесплатны. За ускорение чтения мы платим:

• Замедлением записи. Каждый INSERT, UPDATE, DELETE должен обновить не только таблицу, но и все индексы на ней. Десять индексов на таблице — десять дополнительных обновлений при каждой вставке.
• Дисковым пространством. Индекс — это отдельная структура, которая занимает место (иногда сопоставимое с самой таблицей).
• Накладными расходами на сопровождение. Индексы фрагментируются, их статистику нужно обновлять (ANALYZE).

Поэтому не нужно индексировать всё подряд. Типичные ошибки:

• индекс на столбце с низкой селективностью (например, BOOLEAN is_active — всего два значения, индекс почти бесполезен);
• дублирующиеся индексы ((email) и (email, created_at) одновременно — первый часто избыточен);
• индексы, которые ни один запрос реально не использует.

Правило: создавайте индекс, когда есть конкретный медленный запрос, и проверяйте эффект через EXPLAIN.

1.5. EXPLAIN: анализ плана запроса

EXPLAIN показывает, как СУБД собирается выполнять запрос, не выполняя его. EXPLAIN ANALYZE — реально выполняет и показывает фактическое время.

EXPLAIN SELECT * FROM otp_verification WHERE email = 'user@example.com';

Без индекса вы увидите в плане Seq Scan (полное сканирование):

Seq Scan on otp_verification  (cost=0.00..1845.00 rows=1 width=64)
  Filter: (email = 'user@example.com')

После создания индекса — Index Scan:

Index Scan using idx_otp_email on otp_verification  (cost=0.42..8.44 rows=1 width=64)
  Index Cond: (email = 'user@example.com')

На что смотреть:

• Seq Scan на большой таблице с условием WHERE — сигнал, что не хватает индекса.
• cost — оценка СУБД в условных единицах; меньше — лучше.
• rows — сколько строк планировщик ожидает обработать.
• В EXPLAIN ANALYZE сравнивайте rows ожидаемые и фактические: большое расхождение означает устаревшую статистику.

EXPLAIN ANALYZE
SELECT code FROM otp_verification
WHERE email = 'user@example.com'
ORDER BY created_at DESC
LIMIT 1;

1.6. Базовая оптимизация запросов

Несколько практических приёмов:

• Не пишите SELECT *, если нужны два столбца — выбирайте только их.
• Избегайте функций над индексированным столбцом в WHERE: WHERE LOWER(email) = '...' не сможет использовать обычный индекс по email.
• Фильтруйте на стороне БД, а не в Python. Загружать всю таблицу в приложение и фильтровать циклом — антипаттерн.
• Следите за проблемой N+1 в ORM: вместо одного JOIN приложение делает запрос за списком и ещё по запросу на каждый элемент. Решается «жадной» загрузкой (joinedload / selectinload).
• LIMIT для постраничного вывода — не тащите тысячи строк, если показываете двадцать.

---

2. Транзакции

2.1. Что такое транзакция

Транзакция — это последовательность операций с БД, выполняемая как единое неделимое целое: либо применяются все операции, либо ни одной.

Классический пример — перевод денег между счетами. Нужно списать сумму с одного счёта и зачислить на другой:

BEGIN;                                              -- начать транзакцию
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                                             -- зафиксировать

Если между двумя UPDATE произойдёт сбой (упадёт сервер, оборвётся сеть), деньги не должны «исчезнуть» — списаться с первого счёта, но не зачислиться на второй. Транзакция гарантирует: либо обе операции применятся, либо обе откатятся.

2.2. BEGIN, COMMIT, ROLLBACK

Три команды управляют жизненным циклом транзакции:

• BEGIN (или START TRANSACTION) — открыть транзакцию.
• COMMIT — зафиксировать все изменения, сделать их видимыми и постоянными.
• ROLLBACK — отменить все изменения с момента BEGIN.

BEGIN;

INSERT INTO users (email) VALUES ('user@example.com');
INSERT INTO otp_verification (email, code) VALUES ('user@example.com', '123456');

-- Если всё хорошо:
COMMIT;

-- Если возникла ошибка:
-- ROLLBACK;

В коде на Python (SQLAlchemy) транзакцией удобно управлять через контекстный менеджер — он сам вызовет commit при успехе и rollback при исключении:

from fastapi import HTTPException
from sqlalchemy.orm import Session

@app.post("/register/")
def register(email: str, code: str, db: Session = Depends(get_db)):
    try:
        with db.begin():                       # начало транзакции
            db.add(User(email=email))
            db.add(OTPVerification(email=email, code=code))
        # автоматический COMMIT при выходе из блока без ошибок
    except Exception:
        # автоматический ROLLBACK уже произошёл
        raise HTTPException(status_code=500, detail="Database error")

Часто транзакции открываются неявно. По умолчанию многие драйверы работают в режиме autocommit, где каждый отдельный запрос — это мини-транзакция. Как только нужно объединить несколько операций, мы открываем транзакцию явно.

---

3. Свойства ACID

ACID — это четыре свойства, которые гарантирует надёжная транзакционная СУБД. Аббревиатура расшифровывается так:

Буква	Свойство	Гарантия
A	Atomicity (Атомарность)	Транзакция выполняется целиком или не выполняется вовсе
C	Consistency (Согласованность)	БД переходит из одного корректного состояния в другое
I	Isolation (Изолированность)	Параллельные транзакции не мешают друг другу
D	Durability (Долговечность)	Зафиксированные изменения переживут сбой

3.1. Atomicity (Атомарность)

«Всё или ничего». В примере с переводом денег, если второй UPDATE не выполнится, первый будет отменён. Не бывает состояния, когда списание прошло, а зачисление — нет. Технически это обеспечивается журналом отмены (undo log): СУБД умеет откатить незавершённую транзакцию.

3.2. Consistency (Согласованность)

Транзакция переводит БД из одного корректного состояния в другое, не нарушая правил целостности: ограничений NOT NULL, UNIQUE, CHECK, внешних ключей.

-- Если на счёте есть ограничение CHECK (balance >= 0),
-- транзакция, уводящая баланс в минус, будет отклонена целиком
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- баланс 500
-- ERROR: нарушение CHECK, ROLLBACK

Согласованность — это про то, что инварианты данных (например, «сумма всех счетов постоянна» или «email уникален») сохраняются после транзакции.

3.3. Isolation (Изолированность)

Параллельно выполняющиеся транзакции не должны «видеть» промежуточные результаты друг друга. В идеале каждая транзакция работает так, будто она единственная в системе. На практике полная изоляция дорого стоит, поэтому есть уровни изоляции (раздел 4) — компромисс между строгостью и производительностью.

3.4. Durability (Долговечность)

После успешного COMMIT изменения сохранены навсегда, даже если сразу после этого отключится питание. Это достигается за счёт записи в журнал транзакций (WAL — Write-Ahead Log в PostgreSQL) на диск до подтверждения коммита. После перезапуска СУБД восстановит подтверждённые изменения из журнала.

---

4. Уровни изоляции и аномалии

4.1. Аномалии конкурентного доступа

Когда транзакции выполняются параллельно, могут возникать аномалии — нежелательные эффекты. Стандарт SQL описывает три основные:

Грязное чтение (Dirty Read) — транзакция читает данные, которые другая транзакция изменила, но ещё не зафиксировала. Если та откатится, мы прочитали значение, которого «никогда не было».

T1: UPDATE accounts SET balance = 0 WHERE id = 1;   -- не закоммичено
T2: SELECT balance FROM accounts WHERE id = 1;        -- читает 0 (грязное!)
T1: ROLLBACK;                                          -- баланс на самом деле прежний

Неповторяющееся чтение (Non-repeatable Read) — транзакция дважды читает одну и ту же строку и получает разные значения, потому что между чтениями другая транзакция изменила и зафиксировала эту строку.

T1: SELECT balance FROM accounts WHERE id = 1;   -- 500
T2: UPDATE accounts SET balance = 800 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1;   -- 800 (значение изменилось!)

Фантомное чтение (Phantom Read) — транзакция дважды выполняет один и тот же запрос с условием, и во второй раз появляются новые строки, добавленные другой транзакцией.

T1: SELECT COUNT(*) FROM orders WHERE user_id = 5;   -- 3 строки
T2: INSERT INTO orders (user_id, ...) VALUES (5, ...); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE user_id = 5;   -- 4 строки (фантом!)

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

Стандарт SQL определяет четыре уровня изоляции. Чем выше уровень, тем меньше аномалий, но тем дороже (больше блокировок, ниже параллелизм).

Уровень изоляции	Грязное чтение	Неповторяющееся чтение	Фантомы
READ UNCOMMITTED	возможно	возможно	возможно
READ COMMITTED	нет	возможно	возможно
REPEATABLE READ	нет	нет	возможно*
SERIALIZABLE	нет	нет	нет

* По стандарту на REPEATABLE READ фантомы возможны, но конкретные СУБД могут их предотвращать (например, PostgreSQL за счёт MVCC устраняет фантомы уже на этом уровне).

-- Установка уровня изоляции для текущей транзакции
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Практические замечания:

• READ COMMITTED — уровень по умолчанию в PostgreSQL и Oracle. Хороший баланс для большинства веб-приложений: грязных чтений нет, производительность высокая.
• REPEATABLE READ — по умолчанию в MySQL/InnoDB. Полезен, когда в рамках одной транзакции нужна стабильная «снимок-картина» данных.
• SERIALIZABLE — самый строгий: транзакции выполняются так, будто строго по очереди. Максимальная корректность, но возможны откаты из-за конфликтов сериализации, которые приложение должно уметь повторять.

---

5. Блокировки (кратко)

Чтобы обеспечить изоляцию, СУБД использует блокировки (locks). Упрощённо:

• Разделяемая блокировка (shared, на чтение) — несколько транзакций могут одновременно читать одну строку.
• Эксклюзивная блокировка (exclusive, на запись) — только одна транзакция может изменять строку; остальные ждут.

Явно запросить блокировку строк можно через SELECT ... FOR UPDATE:

BEGIN;
-- Заблокировать строку, чтобы никто другой её не изменил, пока мы работаем
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Взаимная блокировка (deadlock) возникает, когда две транзакции ждут ресурсы друг друга по кругу:

T1: заблокировала строку A, хочет B
T2: заблокировала строку B, хочет A
-> обе ждут вечно

СУБД автоматически обнаруживает deadlock и принудительно откатывает одну из транзакций с ошибкой. Чтобы снизить вероятность взаимных блокировок, обращайтесь к ресурсам в одинаковом порядке во всех транзакциях и держите транзакции короткими.

Многие современные СУБД (PostgreSQL, Oracle) для чтения используют MVCC (Multi-Version Concurrency Control) — хранят версии строк, поэтому читатели не блокируют писателей и наоборот. Это снижает количество явных блокировок.

---

6. Типичные проблемы конкурентного доступа в веб-приложениях

В веб-приложении сотни пользователей работают параллельно, поэтому конкурентные проблемы — не теория, а повседневность.

6.1. Потерянное обновление (Lost Update)

Два запроса читают значение, оба увеличивают его и записывают — одно обновление «затирает» другое.

# ОПАСНО: read-modify-write без защиты
item = db.query(Item).filter(Item.id == 1).first()   # quantity = 10
item.quantity = item.quantity - 1                      # оба прочитали 10
db.commit()                                            # оба записали 9, потеряли единицу

Два решения:

Атомарное обновление на стороне БД — переносим вычисление в SQL:

UPDATE items SET quantity = quantity - 1 WHERE id = 1 AND quantity > 0;

Блокировка строки перед чтением:

item = db.query(Item).filter(Item.id == 1).with_for_update().first()
item.quantity -= 1
db.commit()

6.2. Двойная отправка формы / двойное списание

Пользователь дважды нажал «Оплатить», и создалось два заказа. Защита — ограничение уникальности на ключе идемпотентности (UNIQUE (idempotency_key)): повторная вставка с тем же ключом будет отклонена БД.

6.3. Слишком длинные транзакции

Если открыть транзакцию и держать её, пока выполняется медленный внешний вызов (например, запрос к платёжному API), заблокированные строки остаются недоступными для других. Держите транзакции максимально короткими, не делайте сетевых вызовов внутри открытой транзакции.

6.4. Оптимистичная и пессимистичная блокировка

• Пессимистичная (SELECT ... FOR UPDATE) — блокируем строку заранее, исходя из того, что конфликт вероятен. Надёжно, но снижает параллелизм.
• Оптимистичная — не блокируем, но добавляем столбец version. При обновлении проверяем, что версия не изменилась:

UPDATE items SET quantity = 9, version = version + 1
WHERE id = 1 AND version = 5;
-- Если 0 строк обновлено — кто-то опередил нас, повторяем операцию

Оптимистичная блокировка хороша, когда конфликты редки; пессимистичная — когда они часты.

---

Краткие итоги

• Индекс — структура, ускоряющая поиск и сортировку; по умолчанию это B-tree, работающий за O(log n).
• Индексируйте столбцы из WHERE, JOIN, ORDER BY; но помните о цене: индексы замедляют запись и занимают место. Не индексируйте всё подряд.
• EXPLAIN показывает план запроса: Seq Scan на большой таблице — повод задуматься об индексе.
• Транзакция — неделимая последовательность операций; управляется командами BEGIN, COMMIT, ROLLBACK.
• ACID = Atomicity (всё или ничего), Consistency (корректное состояние), Isolation (независимость), Durability (сохранность после сбоя).
• Аномалии: грязное чтение, неповторяющееся чтение, фантомы. Уровни изоляции (от READ UNCOMMITTED до SERIALIZABLE) определяют, какие аномалии допустимы.
• Блокировки обеспечивают изоляцию; deadlock СУБД разрешает откатом одной транзакции.
• В веб-приложениях типичны потерянные обновления и двойные отправки; защищаемся атомарными UPDATE, блокировками строк, уникальными ограничениями и версионированием.

Вопросы для самопроверки

1. Что такое индекс и за счёт чего он ускоряет поиск? Какова сложность поиска в B-tree?
2. Назовите три типа запросов, для которых стоит создавать индексы. Почему индекс на BOOLEAN-столбце обычно бесполезен?
3. В чём «цена» индексов? Почему нельзя проиндексировать все столбцы?
4. Что покажет EXPLAIN, если в запросе с WHERE не хватает индекса? Чем EXPLAIN отличается от EXPLAIN ANALYZE?
5. Для составного индекса (email, created_at) — поможет ли он запросу, фильтрующему только по created_at? Почему?
6. Что такое транзакция? Опишите назначение команд BEGIN, COMMIT, ROLLBACK.
7. Расшифруйте ACID и приведите по одному примеру на каждое свойство.
8. Чем грязное чтение отличается от неповторяющегося чтения, а оно — от фантомного?
9. Заполните по памяти таблицу «уровень изоляции → допустимые аномалии».
10. Что такое потерянное обновление и какими двумя способами его предотвратить?
11. Чем отличается оптимистичная блокировка от пессимистичной? Когда какую выбрать?
12. Что такое deadlock и как снизить вероятность его возникновения?