Unlocking MySQL InnoDB: Deep Dive into Tablespaces, Undo Logs, and Indexes

The author introduces MySQL InnoDB architecture, which consists of an in‑memory structure and an on‑disk structure.

Tablespaces（表空间）

Tablespaces are the highest logical layer of InnoDB storage; all data resides in tablespaces.

System Tablespace (System Tablespace)

File‑Per‑Table Tablespaces (独立表空间)

General Tablespaces (通用表空间)

Undo Tablespaces (撤销表空间)

Temporary Tablespaces (临时表空间)

Tables（表）

Indexes（索引）

System Tablespace（系统表空间）

By default a 10 MB file named

ibdata1

is created; it stores the Change Buffer and may also contain table and index data.

To enable automatic growth, set

innodb_data_file_path

with the

autoextend

attribute and restart the server.

<code>innodb_data_file_path=ibdata1:10M:autoextend</code>

Using independent or general tablespaces can prevent the system tablespace from becoming too large.

File‑Per‑Table Tablespaces（独立表空间）

When the independent tablespace option is enabled, each InnoDB table gets its own physical file, supporting isolated transaction management.

Internally, a tablespace is organized from top to bottom as segment → extent → page. A page (16 KB) is the basic storage unit; rows are stored in pages.

Each index creates two segments: one for leaf nodes and one for non‑leaf nodes. Segments contain extents, each consisting of 64 consecutive pages (1 MB).

General Tablespaces（通用表空间）

General tablespaces are shared InnoDB tablespaces created with

CREATE TABLESPACE

. They can store multiple tables and may reduce memory usage compared to many independent tablespaces.

<code>CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
    [ENGINE [=] engine_name]</code>

Limitations:

Existing tablespaces cannot be converted to general tablespaces.

Temporary general tablespaces are not supported.

General tablespaces cannot hold temporary tables.

Partitioned tables cannot be placed in general tablespaces.

In replication,

ADD DATAFILE

is unsupported when source and replica share the same host.

Undo Tablespaces（撤销表空间）

Undo tablespaces store Undo Logs used for transaction rollback and MVCC.

MySQL InnoDB Undo Tablespaces are the core component for managing Undo Logs.

Undo logs enable transaction rollback and provide multi‑version concurrency control.

Prior to MySQL 5.7, Undo Logs were stored in the system tablespace (

ibdata1

), causing uncontrolled growth. MySQL 5.7+ introduced independent undo tablespaces to isolate this load.

MySQL 8.0 creates two undo tablespace files (

undo_001

,

undo_002

) of 16 MB each by default; the number can be adjusted via

innodb_undo_tablespaces

(2‑127).

Each undo tablespace contains 128 rollback segments (controlled by

innodb_rollback_segments

), each managing 1024 undo segments composed of 16 KB pages.

Temporary Tablespaces（临时表空间）

InnoDB temporary tablespaces are divided into session temporary tablespaces and a global temporary tablespace.

Session temporary tablespaces store user‑created temporary tables and optimizer‑generated internal temporary tables. Files have the

.ibt

extension and reside in

#innodb_temp

. Up to two files per session are allocated.

Global temporary tablespace stores rollback segments for temporary tables; the default file is

ibtmp1

(12 MB) and expands automatically via

innodb_temp_data_file_path

.

Tables（表）

How to create a table in MySQL?

InnoDB tables are created with

CREATE TABLE

. Example:

<code>CREATE TABLE t1 (a INT, b CHAR(20), PRIMARY KEY (a)) ENGINE=InnoDB;</code>

By default each table uses its own file‑per‑table tablespace. To create a table in the system tablespace, disable

innodb_file_per_table

before creation.

Rows are stored in pages (16 KB). InnoDB uses B+Tree indexes; each table has a clustered index (primary key) and optional secondary indexes.

Row Formats（行格式）

Supported row formats:

REDUNDANT

,

COMPACT

,

DYNAMIC

(default), and

COMPRESSED

.

DYNAMIC

and

COMPRESSED

allow index key prefixes up to 3072 bytes, while the other two support up to 767 bytes.

Primary Keys（主键）

Best practice is to define a primary key on a column that is frequently queried, non‑null, unique, and rarely updated. Often a surrogate numeric ID is used.

Auto‑increment columns can be defined with

AUTO_INCREMENT

. The lock mode for auto‑increment is controlled by

innodb_autoinc_lock_mode

:

0

– traditional lock (table‑level lock for each INSERT).

1

– consecutive lock (used for bulk inserts, holds lock until statement end).

2

– interleaved lock (no table‑level lock, highest concurrency, unsafe for statement‑based replication).

Indexes（索引）

InnoDB indexes are of two types: clustered indexes and secondary indexes, both implemented as B+Trees.

Clustered index: physical order of rows matches the index order; only one per table; leaf nodes store the full row data.

Secondary index: leaf nodes store the index key and the primary key value; non‑leaf nodes store only index keys and page pointers.

In a clustered index, each data page contains a file header with

fil_page_prev

and

fil_page_next

fields that link pages into a doubly‑linked list.

Secondary index leaf pages store the primary key ID to enable a “back‑to‑table” lookup.