InnoDB File Structure: Physical and Logical Organization

This article provides a comprehensive overview of InnoDB's file structure, covering physical organization from pages to tablespaces, logical structure including segments and indexes, and file management processes.

The physical structure begins with pages (16KB each) as the basic unit, organized into extents (64 pages = 1MB), which are grouped into segments, and ultimately form tablespaces. Different file types include system tablespace (ibdata), user tablespace files (ibd), redo log files (ib_logfile), temporary tablespace (ibtmp), and undo independent tablespaces.

System tablespace contains critical information including metadata, transaction system data, insert buffer information, and double write buffer. User tablespace files can be either per-table files (when innodb_file_per_table is enabled) or general tablespaces (introduced in MySQL 5.7).

The logical structure involves three extent-linked lists in the tablespace header: FSP_FREE (completely unused extents), FSP_FREE_FRAG (partially used extents), and FSP_FULL_FRAG (completely used extents). Segments are logical units that manage physical files and can request allocation and release of pages or extents. Each segment maintains three extent-linked lists: FSEG_FREE, FSEG_NOT_FULL, and FSEG_FULL.

Indexes are implemented as B-trees, with each index corresponding to a B-tree structure. The primary key (cluster index) stores all column data in leaf nodes, while secondary indexes store key values plus cluster index key values. Each B-tree uses two segments: one for leaf nodes and one for non-leaf nodes.

The article also details the file management processes including B-tree creation, segment creation, extent allocation, and page allocation. These processes involve complex algorithms for efficient space utilization and maintaining data integrity.

Overall, the article provides a thorough understanding of how InnoDB organizes and manages its physical and logical file structures, which is essential for database administrators and developers working with MySQL databases.