Understanding InnoDB Logical Storage Structure and MySQL Partitioning

InnoDB stores all data logically in a tablespace that is divided into segments, extents, and pages. Segments represent logical groups such as data, index, or undo; each extent (called a "区") consists of 64 contiguous 16 KB pages (total 1 MB). The engine initially allocates a small number of pages for a segment and later requests full extents to reduce disk usage for small tables or undo segments.

MySQL partitioning separates rows of a single table into independent physical files, allowing each partition to be managed individually. Partitioning is useful for large tables, especially in OLTP environments where careful use is required, and in OLAP scenarios to limit scans to relevant time ranges.

The supported partition types are:

RANGE : rows are placed in partitions based on a continuous range of column values, commonly used for date columns.

LIST : similar to RANGE but uses discrete values defined with VALUES IN.

HASH : distributes rows evenly across partitions using a user‑defined hash function.

KEY : like HASH but uses MySQL’s built‑in function.

Example of a RANGE‑partitioned table and data insertion:

CREATE TABLE `m_test_db`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partition_key` INT NOT NULL,
  `amt` DECIMAL(5) NULL,
  PRIMARY KEY (`id`, `partition_key`)
) PARTITION BY RANGE (partition_key)
PARTITIONS 5 (
  PARTITION part0 VALUES LESS THAN (201901),
  PARTITION part1 VALUES LESS THAN (201902),
  PARTITION part2 VALUES LESS THAN (201903),
  PARTITION part3 VALUES LESS THAN (201904),
  PARTITION part4 VALUES LESS THAN (201905)
);

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1','201901','1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2','201902','800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3','201903','1200');

Using EXPLAIN PARTITIONS shows that the optimizer scans only the relevant partitions when the query predicates include the partition key; otherwise it may scan all partitions, which can be costly for large tables.

Performance considerations: partitioning can improve query speed for OLAP workloads that filter by partitioned columns, but in OLTP workloads it may increase logical I/O if queries do not use the partition key, potentially leading to slower performance.

For further reading see “MySQL技术内幕” and the author’s blog.