Архитектура MySQL: сервер и InnoDB через handler, ICP на границе слоёв, INNODB STATUS как карта, словарь без .frm и бинлог над движком

Один и тот же запрос в MySQL выполняют два слоя, и цена зависит от того, сколько работы стоит на их границе. Сервер разбирает SQL, строит план и ведёт бинлог; движок, в большинстве установок InnoDB, хранит страницы, версии строк и redo. Между ними один интерфейс handler: сервер просит «дай следующую строку по этому индексу», движок отдаёт строку, а лишнее отсеивает уже сервер. Здесь граница показана на одном запросе, потом по SHOW ENGINE INNODB STATUS разложены компоненты InnoDB со ссылками на статьи, где каждый разобран: MVCC и undo, redo и бинлог, кластерный индекс. Раскладка строки по страницам трёх движков — в отдельной статье.

DBA / 01koddo

Сервер, интерфейс handler и компоненты InnoDB

Стенд

Миллион строк с составным индексом (customer_id, status) и маленькая таблица на MyISAM для опыта с бинлогом; сервер и движок в одном контейнере:

docker run -d --name dba-mysql-innodb-architecture-overview --memory 1500m -e MYSQL_ROOT_PASSWORD=pg mysql:8.4

CREATE DATABASE lab; USE lab;
CREATE TABLE seq (n INT PRIMARY KEY);
SET SESSION cte_max_recursion_depth = 2000;
INSERT INTO seq WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<2000) SELECT n FROM r;
CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL, status INT NOT NULL, payload VARCHAR(200) NOT NULL,
  KEY idx_cs (customer_id, status)
) ENGINE=InnoDB;
INSERT INTO orders (customer_id, status, payload)
SELECT 1+FLOOR(RAND()*10000), FLOOR(RAND()*1000), REPEAT('x', 150) FROM seq a JOIN seq b ON b.n <= 500;
ANALYZE TABLE orders;
CREATE TABLE legacy (id INT PRIMARY KEY, v INT) ENGINE=MyISAM;
INSERT INTO legacy VALUES (1,10);

SELECT version() дал 8.4.13, поэтому код ниже взят по тегу mysql-8.4.13. Все команды опытов выполнялись в одной сессии клиента: SHOW SESSION STATUS видит только счётчики своей сессии.

Опыт 1: что проходит через границу

Условие status = 7 идёт по второму столбцу индекса, но диапазон по первому (customer_id BETWEEN 1 AND 300) не даёт искать по нему прямо. Индекс всё же содержит status, и движок может проверить условие по записи индекса, не читая строку из таблицы. Это Index Condition Pushdown (ICP). Сначала обычный EXPLAIN, потом то же с выключенным проталкиванием:

EXPLAIN SELECT * FROM orders WHERE customer_id BETWEEN 1 AND 300 AND status = 7\G
SET optimizer_switch='index_condition_pushdown=off';
EXPLAIN SELECT * FROM orders WHERE customer_id BETWEEN 1 AND 300 AND status = 7\G

вывод (поля Extra):

Extra: Using index condition; Using MRR
Extra: Using where; Using MRR

Using index condition — условие уехало в движок, Using where — его проверяет сервер. В дереве это видно ещё лучше: при включённом ICP в строке Index range scan появилось with index condition: …, при выключенном над сканом встал отдельный узел Filter. Теперь цена: счётчики Handler_read_* обнуляются FLUSH STATUS и читаются после EXPLAIN ANALYZE.

FLUSH STATUS;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id BETWEEN 1 AND 300 AND status = 7\G
SHOW SESSION STATUS LIKE 'Handler_read%';

вывод ICP включён (имена с нулями опущены):

-> Index range scan on orders using idx_cs over (1 <= customer_id <= 300 AND status = 7), with index condition: (...)  (cost=39909 rows=55096) (actual time=1.63..1.69 rows=20 loops=1)
Handler_read_key      21
Handler_read_next     20
Handler_read_rnd      20

вывод ICP выключен:

-> Filter: ((orders.`status` = 7) and (orders.customer_id between 1 and 300))  (cost=39909 rows=5510) (actual time=20.1..309 rows=20 loops=1)
    -> Index range scan on orders using idx_cs over (1 <= customer_id <= 300 AND status = 7)  (cost=39909 rows=55096) (actual time=6.53..305 rows=29882 loops=1)
Handler_read_key      29883
Handler_read_next     29882
Handler_read_rnd      29882

Диапазон один и тот же: 29 882 записи индекса. При выключенном ICP каждая из них превратилась в вызов handler и в чтение строки по первичному ключу (Handler_read_rnd: вторичный индекс хранит PK, за остальными столбцами идёт переход в кластерный индекс), и только Filter на сервере оставил 20. При включённом ICP InnoDB проверил условие на записи индекса, и через границу прошли 20 строк. Время трёх прогонов на режим: 309, 310 и 317 мс при выключенном ICP против 1,69, 1,57 и 3,55 мс при включённом. Если выбрать диапазон 1..2000 (около 20 % таблицы), оптимизатор вообще отказывается от индекса: type: ALL, Handler_read_rnd_next = 1000001. Выбор плана тоже принадлежит серверу.

Как это выглядит в коде. Сервер вызывает idx_cond_push при построении плана, и по умолчанию интерфейс отвечает, что движок условие не берёт и возвращается всё (handler.h, строки 6161–6163):

  virtual Item *idx_cond_push(uint keyno [[maybe_unused]], Item *idx_cond) {
    return idx_cond;
  }

InnoDB переопределяет метод и забирает условие целиком (ha_innodb.cc, строки 24049–24059); возвращённый nullptr означает «серверу проверять нечего»:

class Item *ha_innobase::idx_cond_push(uint keyno, class Item *idx_cond) {
  DBUG_TRACE;
  assert(keyno != MAX_KEY);
  assert(idx_cond != nullptr);

  pushed_idx_cond = idx_cond;
  pushed_idx_cond_keyno = keyno;
  in_range_check_pushed_down = true;
  /* We will evaluate the condition entirely */
  return nullptr;
}

Итераторы исполнителя только просят строки: IndexRangeScanIterator::Read вызывает file->ha_multi_range_read_next (index_range_scan.cc, строка 374), ha_innobase::index_read увеличивает ha_read_key_count (это и есть Handler_read_key) и идёт в B-дерево. Поэтому Handler_read_* считает строки, пересёкшие границу: проверки внутри движка в нём не видны, а Filter без ICP стоит выше границы, на сервере.

Опыт 2: INNODB STATUS как карта компонентов

SHOW ENGINE INNODB STATUS снят после UPDATE orders SET status=status+1 WHERE id<=200000, чтобы у redo и пула была работа. В отчёте по разделу на компонент; из каждого взята одна строка:

Нули в Ibuf согласуются с этим. Адаптивный хеш в 8.4 тоже выключен (innodb_adaptive_hash_index = 0), поэтому 0.00 hash searches/s и 28 812 поисков в секунду «non-hash» говорят о настройке, а не о нагрузке: на своём сервере сначала посмотри значение переменной. Doublewrite в отчёте отдельного раздела не имеет, он виден в каталоге данных (#ib_16384_0.dblwr, #ib_16384_1.dblwr) и разобран в статье про torn page. Отчёт показывает InnoDB, а не сервер: ни одного слова про бинлог или оптимизатор в нём нет.

Опыт 3: словарь данных

С MySQL 8.0 определения таблиц лежат в самом InnoDB, а не в файлах .frm. Список таблиц и табличных пространств:

SELECT TABLE_ID, NAME, SPACE, ROW_FORMAT FROM information_schema.INNODB_TABLES WHERE NAME LIKE 'lab/%';
SELECT COUNT(*) FROM mysql.tables;

вывод:

TABLE_ID	NAME	SPACE	ROW_FORMAT
1068	lab/seq	2	Dynamic
1069	lab/orders	3	Dynamic
ERROR 3554 (HY000) at line 1: Access to data dictionary table 'mysql.tables' is rejected.

Таблицы словаря скрыты, читать их можно только через представления information_schema. Что лежит на диске:

docker exec dba-mysql-innodb-architecture-overview sh -c 'cd /var/lib/mysql; ls -l lab mysql.ibd; find . -name "*.frm" | wc -l'

вывод:

-rw-r-----. 1 mysql mysql 32505856 Oct 28 01:33 mysql.ibd

lab:
total 250028
-rw-r-----. 1 mysql mysql         9 Oct 28 01:33 legacy.MYD
-rw-r-----. 1 mysql mysql      2048 Oct 28 01:33 legacy.MYI
-rw-r-----. 1 mysql mysql      2764 Oct 28 01:33 legacy_369.sdi
-rw-r-----. 1 mysql mysql 255852544 Oct 28 01:33 orders.ibd
-rw-r-----. 1 mysql mysql    163840 Oct 28 01:32 seq.ibd
0

.frm нет ни одного, а определения таблиц lab лежат в словаре mysql.ibd (32 505 856 Б); данные InnoDB — по файлу .ibd на таблицу. У MyISAM-таблицы legacy рядом с файлами .MYD и .MYI лежит legacy_369.sdi, тоже не .frm. Устройство словаря описано в документации.

Опыт 4: бинлог принадлежит серверу

Таблицу legacy со стенда создали на MyISAM, строка в неё вставлена. Оба движка видны в SHOW ENGINES (InnoDB DEFAULT, MyISAM YES), а в бинлоге (позиции у тебя будут другие, начинай с SHOW BINLOG EVENTS без FROM):

SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'binlog.000002' FROM 237641807;

вывод:

Log_name	File_size	Encrypted
binlog.000001	2981501	No
binlog.000002	237642264	No
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
binlog.000002	237641807	Query	1	237641946	CREATE TABLE lab.legacy (id INT PRIMARY KEY, v INT) ENGINE=MyISAM
binlog.000002	237641946	Anonymous_Gtid	1	237642025	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000002	237642025	Query	1	237642096	BEGIN
binlog.000002	237642096	Table_map	1	237642148	table_id: 92 (lab.legacy)
binlog.000002	237642148	Write_rows	1	237642192	table_id: 92 flags: STMT_END_F
binlog.000002	237642192	Query	1	237642264	COMMIT

Строка в MyISAM попала в тот же файл, что и транзакции InnoDB, а закрывает её Query COMMIT, не Xid: для движка без транзакций двухфазного коммита нет. Сам бинлог встроен в тот же интерфейс как скрытый движок: binlog_init задаёт ему commit, rollback, prepare и флаги HTON_NOT_USER_SELECTABLE | HTON_HIDDEN (binlog.cc, строки 1538–1543):

  binlog_hton->commit = binlog_commit;
  binlog_hton->rollback = binlog_rollback;
  binlog_hton->prepare = binlog_prepare;
  binlog_hton->set_prepared_in_tc = binlog_set_prepared_in_tc;
  binlog_hton->recover = binlog_dummy_recover;
  binlog_hton->flags = HTON_NOT_USER_SELECTABLE | HTON_HIDDEN;

Из этого следует, что репликация читает бинлог сервера и получает изменения MyISAM-таблицы так же, как InnoDB. Физические копии InnoDB (XtraBackup и CLONE) строятся из файлов и redo движка, бинлога там нет, поэтому их позиция в бинлоге записывается отдельно.

MariaDB: что расходится и что из этого следует

MariaDB форкнута от MySQL, и граница handler у неё та же, но слои за ней расходятся. Документация MariaDB (проверено 28.10.2026, опыт здесь не запускался) говорит о трёх вещах. Во-первых, оптимизатор: страница совместимости сообщает, что MariaDB меняет оптимизатор запросов относительно MySQL и это «может приводить к другим планам в EXPLAIN». Во-вторых, движок Aria, описанный как устойчивая к сбоям замена MyISAM; в MariaDB все системные таблицы лежат в Aria, а в MySQL 8.4 их хранит InnoDB (mysql.ibd выше). В-третьих, системно-версионные таблицы WITH SYSTEM VERSIONING, которые хранят историю изменений и читаются через FOR SYSTEM_TIME (документация); в MySQL 8.4 такого синтаксиса нет. Страница совместимости отдельно предупреждает: расхождения в реализации InnoDB растут с каждой версией, поэтому «подменить MySQL на MariaDB» пригодно как временная мера, а не как гарантия.

Для консультанта отсюда два правила. Планы и счётчики Handler_read_* из MySQL на MariaDB переносить нельзя: первым делом снять EXPLAIN на той же версии, на которой запрос работает. А перенос данных между ними считать миграцией с проверкой планов и репликации, а не обновлением версии.

Что из этого следует

• Лишнюю работу сервера видно по Handler_read_* и по узлам Filter и Sort над сканом. Если условие можно проверить по записи индекса, ему нужен составной индекс и включённый ICP: здесь 29 882 пересечения границы стали 20, 309 мс — 1,69 мс.
• SHOW ENGINE INNODB STATUS отвечает на вопросы про InnoDB (пул, redo, history list), но не про запрос и не про бинлог; их ищут в Performance Schema и в SHOW BINARY LOGS.
• Определения таблиц живут в mysql.ibd, файлов .frm нет, и DDL атомарен; бинлог пишется для любого движка и закрывает транзакцию InnoDB иначе, чем запись MyISAM.
• Сведения о MariaDB берутся из её документации и проверяются на её сервере: расхождение планов и системных таблиц начинается за границей handler.

Чек-лист

1. Прежде чем советовать индекс, прочитай Extra в EXPLAIN: Using index condition значит, что движок уже отсеивает по записи индекса, Using where над индексом — что отсеивает сервер.
2. Сравнивай Handler_read_* до и после изменения (FLUSH STATUS в одной сессии): счётчик показывает, сколько строк пересекло границу.
3. Для InnoDB смотри Log sequence number против Last checkpoint at, History list length и hit rate разностью, а не накопительно.
4. Чтобы понять, в каком движке таблица, смотри information_schema.TABLES.ENGINE; бинлог при этом общий, у MyISAM он закрывается COMMIT без Xid.
5. Убери контейнер вместе с томом: docker rm -f -v dba-mysql-innodb-architecture-overview.