Мастерство реляционных СУБД: Архитектура, оптимизация и масштабирование MySQL и PostgreSQL

Архитектурные различия: Глубокое погружение в MySQL InnoDB и механизмы MVCC в PostgreSQL

Представьте, что два банка обрабатывают миллионы транзакций в секунду. Один банк хранит историю каждой монеты в огромном бесконечном журнале, а другой — постоянно переписывает балансы в главной книге, аккуратно складывая старые версии в сейф «на всякий случай». Несмотря на то, что оба банка гарантируют точность, их внутренняя логика работы с данными фундаментально различается. Этот парадокс лежит в основе противостояния MySQL и PostgreSQL: как обеспечить одновременный доступ тысяч пользователей к одним и тем же данным, не блокируя их?

Фундамент многопоточности: Концепция MVCC

В основе обеих систем лежит механизм Multi-Version Concurrency Control (MVCC). Его задача — позволить читающим транзакциям не ждать пишущих, и наоборот.

> MVCC — это метод управления конкурентным доступом, при котором база данных хранит несколько версий одной и той же строки. Это позволяет транзакции «видеть» снимок данных на момент её начала, игнорируя изменения, внесенные другими незавершенными операциями.

Однако реализация этого принципа в MySQL (движок InnoDB) и PostgreSQL различается на уровне физического хранения байтов на диске.

PostgreSQL: Версионность внутри таблицы

В PostgreSQL каждая строка (tuple) по сути является неизменяемой. Когда вы выполняете UPDATE, система не меняет существующую строку. Вместо этого она помечает старую строку как «удаленную» и вставляет новую версию в ту же таблицу.

Механика Heap и системные поля

Каждая строка в Postgres содержит скрытые поля, определяющие её видимость:

xmin: ID транзакции, которая создала строку.

xmax: ID транзакции, которая удалила строку или создала её новую версию.

Если для строки , это сигнал системе проверить, завершилась ли транзакция, установившая это значение. Если транзакция зафиксирована (committed), строка считается «мертвой» (dead tuple).

Проблема очистки (VACUUM)

Так как старые версии строк остаются в файлах данных, база начинает «раздуваться» (bloat). Для очистки мусора Postgres использует процесс VACUUM.

Где — общий объем таблицы, — полезные данные, а — место, занятое устаревшими версиями, которое нельзя использовать до очистки.

MySQL InnoDB: Кластеризованный индекс и Undo Log

MySQL (InnoDB) идет другим путем. Она хранит только одну актуальную версию строки непосредственно в области данных (в кластеризованном индексе), а все предыдущие версии выносит в специальный сегмент — Undo Log.

Роль Rollback Segment

Когда транзакции в InnoDB требуется «старая» версия данных, она не ищет её в основной таблице. Она берет текущую строку и последовательно применяет к ней «откатные» записи из Undo Log, восстанавливая состояние данных на нужный момент времени.

| Характеристика | PostgreSQL | MySQL (InnoDB) | | :--- | :--- | :--- | | Хранение версий | В основной таблице (Heap) | В отдельном Undo Log | | Первичный ключ | Опционален (Heap-таблица) | Обязателен (Clustered Index) | | Очистка данных | Фоновый процесс VACUUM | Очистка Undo Log (Purge thread) | | UPDATE | INSERT новой версии + пометка старой | In-place update + запись в Undo Log |

Индексы и физическая организация

Различие в хранении строк напрямую влияет на работу индексов.

В PostgreSQL все индексы являются «вторичными» и указывают на физический адрес строки (TID — Tuple ID). Поскольку UPDATE создает новую строку с новым адресом, при обновлении любого поля в Postgres приходится обновлять все индексы этой таблицы. Для смягчения этой проблемы используется механизм HOT (Heap Only Tuples), позволяющий избежать обновления индексов, если новая версия строки поместилась на ту же страницу данных.

В MySQL InnoDB данные организованы в виде B+ дерева вокруг первичного ключа (Primary Key). Вторичные индексы хранят не физический адрес, а значение первичного ключа.

Плюс: При обновлении данных, не затрагивающих индексы, сами индексы не меняются.

Минус: Любой поиск по вторичному индексу требует двух этапов: поиск в индексе, а затем «прыжок» в кластеризованный индекс по значению PK (Bookmark Lookup).

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

Хотя обе СУБД следуют стандарту SQL, их поведение на уровне REPEATABLE READ (уровень по умолчанию в MySQL) различается.

MySQL InnoDB: На уровне REPEATABLE READ защищает от фантомного чтения (Phantom Read) с помощью блокировок следующего ключа (Next-Key Locking).

PostgreSQL: На уровне REPEATABLE READ также не допускает фантомов, но делает это через проверку снимков данных (Snapshot), без избыточных блокировок диапазонов.

Резюме архитектурного выбора

Выбор между этими архитектурами — это компромисс между стоимостью записи и стоимостью обслуживания. Postgres жертвует местом и требует VACUUM, но обеспечивает невероятную гибкость и расширяемость. InnoDB экономит на индексах и предотвращает раздувание таблиц, но сильно зависит от производительности Undo Log и структуры первичного ключа.

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