MySQL InnoDB Storage Structure: Buffer Pool, Write Buffer and Log Mechanism Deep Dive
InnoDB Storage Structure
InnoDB is MySQL’s default storage engine and has been widely used in transaction-oriented systems since version 5.5. Its architecture is divided into two main parts: memory structure and disk structure.
Memory Structure
The memory structure mainly includes four major components: Buffer Pool, Change Buffer, Adaptive Hash Index, and Log Buffer.
Buffer Pool
Basic Concept: Buffer Pool (BP) is the core memory area of the InnoDB storage engine, mainly used for caching table data and index data.
Storage Unit: Page is the basic management unit of BP, with a default size of 16KB.
Data Structure: Uses an improved linked list structure for management:
- Free List: Maintains all free pages
- LRU List: Manages pages in use, using an improved LRU algorithm
- Flush List: Records modified dirty pages
Key Configuration Parameters:
innodb_buffer_pool_size: Total size (recommended to set to 50-70% of physical memory)innodb_buffer_pool_instances: Number of instances
Change Buffer
Concept: Change Buffer is an important mechanism in InnoDB storage engine for optimizing non-unique ordinary index updates.
Core Features:
- Change Buffer occupies space in Buffer Pool, default configuration is 25%
- Applicable to non-unique ordinary index pages
Merge Trigger Timing:
- Active reading
- Background thread periodic merge
- Insufficient space
Adaptive Hash Index
Adaptive Hash Index is used to optimize queries of BP data. The InnoDB storage engine monitors table index lookups. If it observes that building a hash index can improve speed, it will build a hash index.
Log Buffer
Log Buffer is mainly used for temporarily storing data that is about to be written to disk log files.
Main Functions:
- Data buffering: All Redo and Undo logs generated by DML operations are first written to Log Buffer
- Automatic refresh mechanism
- Manual refresh control
innodb_flush_log_at_trx_commit Parameter:
| Setting | Description | Risk | Performance |
|---|---|---|---|
| 0 | Batch write every second | Most recent 1 second may be lost | Highest |
| 1 (default) | Each transaction commit | No data loss | Lowest |
| 2 | Write to OS Cache | No loss on process crash | Medium |