MySQL InnoDB Storage Structure: Tablespace, Segment, Extent, Page and Row Format Deep Dive

InnoDB Data File Storage Structure

Hierarchical management: Tablespace → Segment → Extent → Page → Row

1. Tablespace

• As the highest-level storage unit
• Each table usually corresponds to an independent tablespace file
• System tablespace stores shared system information

2. Segment

• Data segment (Leaf Node Segment): Stores B+ tree leaf node data
• Index segment (Non-leaf Node Segment): Stores B+ tree non-leaf node data
• Rollback segment (Rollback Segment): Stores information needed for transaction rollback

3. Extent

• Fixed size of 1MB (64 consecutive pages × 16KB/page)
• Uses pre-allocation strategy to reduce fragmentation

4. Page

• Fixed size of 16KB
• Main types: data page, undo page, system page, BLOB page

5. Row

• Transaction ID (Trx Id)
• Roll Pointer (Roll Pointer)
• Field pointers
• Actual column data

File Format

Antelope Format

• Supports older row formats
• REDUNDANT: Best compatibility
• COMPACT: Default row format

Barracuda Format

• Supports all row formats
• DYNAMIC: Optimized for variable-length columns
• COMPRESSED: Supports table and index data compression

SET GLOBAL innodb_file_format='Barracuda';

ALTER TABLE table_name ROW_FORMAT = format_type

Row Format Explained

Detailed Comparison of Each Format

Row Format	Features	Applicable Scenarios
COMPACT	Space-saving	General scenarios
DYNAMIC	Better handling of large rows	Tables with many large fields
COMPRESSED	Supports compressed storage	Limited storage space
REDUNDANT	Compatible with old versions	Backward compatibility