MySQL InnoDB Internals

InnoDB stores table data and indexes in pages organized into tablespaces. A 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.

Data Dictionary and Metadata #

MySQL 8 stores the transactional data dictionary in InnoDB. Server metadata, including schemas, tables, columns, indexes, stored programs, and privileges, is therefore crash-safe and participates in atomic data-definition operations. Applications must use INFORMATION_SCHEMA, the Performance Schema, or SHOW statements to inspect metadata; internal dictionary tables are not an application interface.

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.

Tablespaces #

An InnoDB installation can use:

The system tablespace for change-buffer data and other shared InnoDB structures.
File-per-table tablespaces, which are created for InnoDB tables when innodb_file_per_table is enabled.
General tablespaces, which an administrator creates and assigns to compatible tables.
Separate undo and temporary tablespaces managed by 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.

Pages, Extents, and Index Records #

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.

Doublewrite Buffer and Recovery #

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.

Inspection #

Use supported schemas rather than file inspection:

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.