InnoDB Storage Structure

InnoDB storage engine’s disk structure is its core component.

1. Tablespaces

Tablespaces are physical files where InnoDB stores data, which can be divided into:

  • System Tablespace: Stores data dictionary, doublewrite buffer and other metadata information, default filename is ibdata1
  • File-Per-Table Tablespace: Each table can have its own tablespace file (.ibd file)
  • General Tablespace: Multiple tables can share the same tablespace
  • Temporary Tablespace: Stores temporary tables and temporary data

2. InnoDB Data Dictionary

Data dictionary contains the following metadata:

  • Table structure information (column names, types, indexes, etc.)
  • Tablespace information
  • Foreign key constraints

3. Doublewrite Buffer

  • A special area in the system tablespace (default 2MB)
  • Function: Prevents partial page write
  • Workflow: First writes dirty pages to doublewrite buffer, then writes dirty pages to actual data file location

4. Redo Log

  • Physical log, records physical modifications to pages
  • Composition: Default 2 files ib_logfile0 and ib_logfile1
  • Function: Implements transaction durability

5. Undo Log

  • Logical log, records data state before transaction modification
  • Function: Restores data during transaction rollback, implements MVCC

Tablespace Explained

System Tablespace

Contains InnoDB data dictionary, Doublewrite Buffer, Change Buffer, Undo Log storage area.

File-Per-Table Tablespaces

Enabled by default, each table is created in its own data file.

General Tablespaces

Shared tablespace created through create tablespace syntax.

Temporary Tablespaces

  • Session Temporary Tablespaces: Temporary tables for each session
  • Global Temporary Tablespaces: Globally shared temporary tables

Data Dictionary

InnoDB data dictionary is a core component of MySQL storage engine, composed of a series of internal system tables:

  • SYS_TABLES: Stores basic table information
  • SYS_COLUMNS: Records column definitions
  • SYS_INDEXES: Saves index information
  • SYS_FIELDS: Records index fields
  • SYS_FOREIGN: Stores foreign key constraints