Understanding InnoDB Storage Architecture and MySQL Partitioning Strategies

InnoDB Logical Storage Structure

InnoDB stores all data logically in a tablespace that is divided into segments, extents, and pages.

Segment

A segment corresponds to areas such as data, index, or undo segments, and its management is handled by the engine.

Extent

An extent consists of contiguous pages; its default size is 1 MB, with each page typically 16 KB, giving 64 pages per extent. InnoDB initially allocates 4–5 extents and uses a 32‑page fragment before allocating full extents to save space for small tables or undo segments.

Page

A page is the smallest unit of InnoDB disk management, default 16 KB (configurable via innodb_page_size). Common page types include data, undo, system, transaction, insert‑buffer bitmap, and BLOB pages.

Partition Overview

In MySQL, partitioning means storing rows of a single table in separate physical .ibd files. Since MySQL 5.1, horizontal partitioning is supported, allowing independent handling of each partition without code changes.

Partition Types

MySQL supports RANGE, LIST, HASH, and KEY partitioning. RANGE is most frequently used, especially for date columns, dividing rows into continuous intervals. The partition key must be part of any unique index.

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 partition, improving query efficiency when the partition key is used.

Partition and Performance

Partitioning can improve performance for OLAP workloads by limiting scans to relevant partitions, but for OLTP workloads it may add overhead if queries do not use the partition key. Careful design is required to avoid full‑partition scans.

To inspect which partitions a query uses, run EXPLAIN PARTITIONS with the SELECT statement.