MySQL InnoDB 内部机制

InnoDB 将表数据和索引存储在按表空间组织的页面中。一个 tablespace has a fixed page size, selected when the data directory is initialized. The default page size is 16 KiB. Page-oriented I/O explains why index locality, buffer-pool capacity, and row width affect query cost.

数据字典与元数据 #

MySQL 8 将事务性数据字典存储在 InnoDB 中。 Server metadata, including schemas, tables, columns, indexes, stored programs, and privileges, is therefore crash-safe and participates in atomic data-definition operations. 应用必须使用 INFORMATION_SCHEMA、Performance Schema 或 SHOW 语句检查元数据；内部字典表不是应用接口。

INFORMATION_SCHEMA exposes SQL views over metadata. The sys schema adds human-oriented views over Performance Schema and Information Schema data, such as expensive statements and unused indexes.

Data-definition operations update dictionary metadata and storage-engine state as one atomic DDL transaction when the operation supports atomic DDL. Dictionary records remain server internals; reading or modifying underlying InnoDB tables directly is unsupported.

表空间 #

InnoDB 安装可以使用：

系统表空间用于更改缓冲区数据和其他共享 InnoDB structures.
独立表文件表空间，在启用 innodb_file_per_table is enabled.
通用表空间，由管理员创建并分配给 compatible tables.
由 InnoDB 管理的独立撤销表空间和临时表空间。

A table does not map safely to one operating-system file across all configurations. Backup and restore must use MySQL-aware tools or a documented physical-backup procedure because a consistent database spans data files, redo logs, and metadata.

页面、区与索引记录 #

A tablespace is divided into pages, and pages are grouped into extents. With the default 16 KiB page size, an extent contains 64 pages and occupies 1 MiB. InnoDB allocates small segments page by page before using whole extents as they grow.

Each table is organized around its clustered index. Clustered leaf pages contain row records, while internal pages contain separator keys and child-page pointers. Secondary-index leaf records contain secondary keys plus the primary key used to locate the clustered record. Undo pages retain older row versions needed by MVCC, and internal temporary tables can use the global temporary tablespace or session temporary tablespaces depending on their kind and release.

双写缓冲区与恢复 #

InnoDB writes dirty pages through the doublewrite mechanism before making the final tablespace write. A storage failure can otherwise leave a partially written page, called a torn page. During recovery, InnoDB can use the valid doublewrite copy when page checksums identify a torn final write.

Redo logging records changes before affected pages are flushed. During crash recovery, InnoDB replays required redo and rolls back incomplete transactions with undo records. Disabling durability settings changes acknowledged-write semantics and must be treated as a data-loss decision, not a performance-only tuning choice.

检查 #

使用受支持的模式，而不是检查文件：

sql

SELECT name, space_type
FROM information_schema.innodb_tablespaces
ORDER BY name;

SELECT pool_id, pool_size, free_buffers, database_pages
FROM information_schema.innodb_buffer_pool_stats;

SELECT event_name, count_star, sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/file/innodb/%';

INFORMATION_SCHEMA.INNODB_* tables describe logical InnoDB objects and buffer state. Performance Schema attributes observed work and waits. Both interfaces change across releases, so operational queries must target the deployed MySQL version.