mysql-internals.js·4 scenarios
MySQL/InnoDB internals concept page: storage engine architecture (InnoDB clustered B+tree, buffer pool, redo/undo log, doublewrite buffer, change buffer, binlog), secondary index double lookup, write path with WAL, async binlog replication with replica lag, Galera sync multi-master alternative. 4 scenarios: write path WAL, secondary index double lookup, async replication via binlog, Galera sync replication. ADR on InnoDB vs MyISAM vs PostgreSQL.
Любой backend-разработчик 5+ раз в неделю пишет SELECT/INSERT/UPDATE. Большинство — не понимает, почему SELECT * по secondary index в 2 раза дороже, почему UUIDv4 как PK разваливает write throughput, почему ALTER TABLE лочит репликацию на часы, и почему replica vдруг отстала на 6 часов.
Все эти странности — следствие трёх вещей: clustered B+tree по primary key, redo/undo log + buffer pool, и binlog отдельно от redo log. Если эта тройка в голове сложилась — 80% MySQL-граблей превращаются в очевидные следствия.
«MySQL InnoDB — это clustered B+tree по primary key + redo/undo log + buffer pool; всё остальное (replication, sharding, HA) — слои поверх этой троицы. Пока ты не понимаешь, что secondary index делает два lookup'а — ты не понимаешь MySQL.»
Три критичных следствия из этой модели:
(secondary_key, PK) → чтобы достать row, делается двойной lookup. Отсюда вся история про covering indexes и короткие PK..ibd. Dirty pages могут жить в buffer pool часами и сливаться лениво.sync_binlog=1 × innodb_flush_log_at_trx_commit=1 = два fsync на write.Три блока + клиент:
MySQL Primary (InnoDB) — SQL Layer (parser/optimizer/executor), Buffer Pool (16KB pages, 70-80% RAM), Change Buffer (отложенные secondary index writes), Redo Log (WAL), Undo Log (rollback + MVCC versions), Doublewrite Buffer (защита от torn pages), Tablespace .ibd (clustered B+tree + secondary indexes), Binary Log (server-level журнал для replication).
Async Replicas — Replica-1 IO Thread (тянет binlog в relay log), Replica-1 SQL Thread (single-threaded apply из relay log), Replica-2 (cross-region, выше lag).
Galera Cluster — три ноды, любая принимает writes, certification-based sync replication.
Edges: app → SQL layer; SQL ↔ Buffer Pool ↔ {Undo, Redo, Doublewrite, Tablespace, Binlog}; Binlog → Replicas. Для Galera — все ноды связаны попарно (broadcast writeset + certification).
INSERT/UPDATE проходит через 5 на самом деле важных шагов: undo first (для rollback + MVCC), dirty page в buffer pool, redo log fsync (вот это и есть durability), binlog event (two-phase commit с redo log), ack клиенту. Реальный flush dirty page в .ibd через doublewrite — асинхронный, может произойти через минуты. Если на этом этапе crash — redo log накатывает unflushed dirty pages при старте.
Ключевое: ack клиенту = два fsync (redo + binlog), а не запись страницы на диск.
SELECT * FROM users WHERE email = '...' с индексом idx_email: первый B+tree traversal по idx_email находит (email, PK), второй traversal по clustered B+tree по PK достаёт row. Два полных спуска по дереву = ~6 page reads. Plus MVCC check в undo log (видна ли version текущей транзакции).
Covering index убирает второй hop: INDEX (email, name) означает, что SELECT name не лезет в clustered. Это самый частый wins при оптимизации запросов.
Binlog отдельный от redo log журнал на уровне сервера. Replica качает binlog через сеть (IO thread → relay log), потом SQL thread читает relay log и выполняет события заново локально. SQL thread по умолчанию single-threaded — типичная причина replica lag. Cross-region replicas лагают на 50-200ms даже в healthy state.
Форматы binlog: STATEMENT (компактно, но ломается на NOW()/RAND()/UUID()), ROW (безопасно, дороже, default с 5.7), MIXED (авто-выбор). GTID (Global Transaction ID) заменил binlog file+position для tracking — failover после GTID проще.
Galera = synchronous multi-master: пишем в любую ноду, она собирает writeset (изменения rows + PK), broadcast'ит на все ноды, каждая нода сертифицирует writeset против своих pending transactions (по PK conflict detection), ack — commit. Latency commit'а = WAN RTT × 2. Hot row становится bottleneck'ом: все writes в одну строку конфликтуют на certification → retry storm.
Galera не масштабирует writes, она про HA + read-your-writes на любой ноде.
InnoDB vs MyISAM vs MyRocks vs PostgreSQL
Контекст: MySQL уникален тем, что storage engine pluggable. Одна и та же SQL-обвязка работает поверх InnoDB (clustered B+tree, MVCC, ACID), MyISAM (heap + table-locks, no transactions), Memory (in-RAM hash), MyRocks (LSM на RocksDB), Archive (compressed append-only). PostgreSQL же монолитен — heap + IndirectMVCC.
| Параметр | InnoDB | MyRocks | MyISAM | PostgreSQL |
|---|---|---|---|---|
| Engine | B+tree | LSM | heap | heap |
| ACID | yes | yes | NO | yes |
| Lock granularity | row | row | table | row |
| Crash safe | yes (WAL) | yes (WAL) | NO | yes (WAL) |
| Write amplification | high | low | low | medium |
| Compression | row/page | ~50% | low | TOAST |
| Use case | OLTP default | write-heavy big data | НИКОГДА новое | сложный SQL, JSON |
Решение: по умолчанию InnoDB. MyRocks — Facebook UDB-style сценарии (storage cost критичен, writes доминируют). PostgreSQL — когда нужны window functions / partial indexes / JSONB / CTEs / strong типизация. MyISAM в новом коде в 2026 — антипаттерн.
Replication: async vs semi-sync vs sync (Galera)
| Тип | Lag | Latency | Use case |
|---|---|---|---|
| Async (default) | 5-500ms | minimal | большинство, read-heavy |
| Semi-sync (plugin) | ~RTT | +RTT | финансы, критичные writes |
| Group Replication (Paxos) | 0 | +RTT × 2 | HA single-region |
| Galera (sync MM) | 0 | +RTT × 2 | HA, read-your-writes anywhere |
| Vitess sharded | varies | low | petabyte-scale |
Решение: async — default. Semi-sync — когда «потерять последнюю транзакцию» неприемлемо. Galera/Group Replication — когда нужен no-lag failover. Vitess — когда single instance уже не вытягивает.
user_id (известный paper «Sharding Pinterest»). Десятки тысяч MySQL hosts.SELECT * через ORM. Тянет BLOB/TEXT поля в каждом query, забивает buffer pool, ломает covering indexes. Явный список колонок + индекс под query.NOW()/RAND()/UUID()/LAST_INSERT_ID(). Silent divergence реплик — на primary одно значение, на replica другое. Переключай на ROW.ALTER TABLE на 1TB таблице на проде без pt-online-schema-change / gh-ost. Полный lock, replication лагает на часы, читатели стоят.SHOW PROCESSLIST + information_schema.innodb_trx.expire_logs_days/binlog_expire_logs_seconds. Диск переполнится ночью — типичный пейджер.SELECT ... FOR UPDATE на больших ranges в REPEATABLE READ. Gap locks превращаются в фактический table lock → deadlocks.max_connections = 10000 без connection pooler. MySQL не любит idle connections; каждая тратит память. Ставь ProxySQL.REPEATABLE READ без понимания. Не SQL-standard: phantom reads без gap locks возможны, gap locks дают неочевидные deadlocks при миграциях.tsvector. MySQL FULLTEXT — игрушка.