SoFunction
Updated on 2025-04-12

Detailed explanation of physical storage structure in MySQL

The physical storage structure of MySQL is mainly composed of data files, log files and storage engine-specific organization methods. Here are detailed descriptions of its core components:

1. Data directory structure

MySQL data is stored indatadirThe directory specified by the configuration usually contains the following content:

Database Directory: Each database corresponds to a subdirectory (such as/var/lib/mysql/db_name), internal storage of table structure and data files.

Table file: Different storage engines have different table file types:

  • InnoDB：.ibd(Independent tablespace file) and.frm(Structural files before 8.0, metadata after 8.0 are stored in the data dictionary).
  • MyISAM：.MYD(Data file),.MYI(index file) and.frm(Structure file).
  • Memory Engine: The data is stored in memory only, no disk files (the table structure is still stored in.frm）。

2. Physical structure of InnoDB storage engine

InnoDB uses indexed organization tables (IOTs), and data is stored in primary key order, and the physical structure is layered as follows:

Tablespace

  • System tablespace (ibdata1): Store data dictionary, Undo logs (before 8.0), Change Buffer, etc. Data for all tables is included by default (if independent tablespace is not enabled).
  • Independent tablespace (.ibd file): Each table stores data and indexes independently (enabled by default), improving management flexibility.
  • General tablespace: Can store data from multiple tables, throughCREATE TABLESPACEcreate.
  • Undo tablespace(8.0+): Undo logs are stored independently, defaultundo_001andundo_002。
  • Temporary tablespace: Store temporary tables and sort operation data.

Storage unit level

  • Segment: consists of multiple regions (such as data segment, index segment, and rollback segment).
  • Area (Extent): Consisting of 64 consecutive pages (default 1MB, 16KB per page).
  • Page: Minimum disk management unit (16KB), containing information such as header, line records, and end of page.
  • Row: Data is stored in row format (such as Compact, Dynamic). When the Dynamic format processes large fields, only overflow page pointers are stored.

Log files

  • Redo Log(ib_logfile)*: Physical log, record data page modification, used for crash recovery.
  • Undo Log: Logical logs, support transaction rollback and MVCC (saved in Undo tablespace after 8.0).

3. The physical structure of MyISAM storage engine

Separate data from index: The table consists of three files:

  • .frm: Table structure definition (before 8.0).
  • .MYD: Data files, stored in the order of insertion.
  • .MYI: Indexed file, supports full-text indexing and compressed tables.

characteristic: Table-level lock, no transaction support, suitable for scenarios with more read, more write and less.

4. Log files

  • Binary log (binlog): Record all data changes for master-slave replication and point recovery.
  • Error log: Record server running status and error information.
  • Slow query log: Record SQL statements whose execution time exceeds the threshold.

5. Memory structure assistance

  • Buffer Pool: InnoDB caches data pages and indexes to reduce disk I/O.
  • Change Buffer: Caches non-unique index changes to improve write performance.

Version differences and precautions

  • MySQL 8.0+: Remove.frmFile, metadata is stored in the data dictionary (); the default Undo log is independent.
  • Configuration Options：innodb_file_per_tableControls whether independent tablespaces are enabled.
  • Overflow processing: Dynamic row format only stores 20 byte pointers for large fields (such as BLOB), and the data is stored on the overflow page.

Summarize

  • InnoDB: Suitable for transaction processing, guaranteeing ACID through tablespace, page structure and logging mechanism.
  • MyISAM: Lightweight, suitable for static data, simple physical structure but lack transaction support.
  • Log system: Ensure data durability and system recovery.

Understanding physical storage structures can help optimize database design (such as selecting row formats, partitioning policies) and troubleshooting (such as analyzing file corruption issues).

The above is personal experience. I hope you can give you a reference and I hope you can support me more.