MySQL InnoDB Disk Structure: Tablespace, Log System and Data Dictionary Deep Dive

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