InnoDB Logical Storage Structure and MySQL Partitioning Overview

First, the InnoDB logical storage structure is introduced: all data are stored in a tablespace that consists of segments, extents, and pages.

Segment

Segments correspond to the "segment" area in the diagram and include data segments, index segments, and rollback segments; InnoDB manages them internally.

Extent

Extents correspond to the "extent" area; an extent is a contiguous set of pages, always 1 MB in size regardless of page size. InnoDB allocates 4–5 extents at a time, with each page defaulting to 16 KB, so an extent contains 64 pages.

Each segment initially uses 32 small pages; after they are filled, a block of 64 contiguous pages is allocated, which saves space for small tables or undo segments.

Page

Pages are the "page" area and represent the smallest unit of InnoDB disk management, defaulting to 16 KB (configurable via innodb_page_size). Common page types include data pages, undo pages, system pages, transaction data pages, insert buffer bitmap pages, uncompressed and compressed BLOB pages, etc.

2. Partition Overview

Partition

Here "partition" means distributing rows of the same table into separate physical files; each partition has its own .ibd file, distinct from the "extent" discussed earlier.

MySQL added horizontal partitioning support in version 5.1. Partitioning splits a table or index into multiple manageable parts that can be processed independently, without requiring changes to application code. Use it cautiously because improper use can degrade performance.

Each partition stores both its data and its indexes; MySQL does not support global indexes.

If a primary key or unique index exists, the partition column must be part of that key.

3. Partition Types

MySQL currently supports RANGE, LIST, HASH, and KEY partitions; RANGE is the most commonly used in practice.

RANGE Partition

RANGE partitions place rows into partitions based on a continuous range of column values, often used for date columns. Inserting a row whose value falls outside defined ranges raises an error.

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 sample data into the partitioned table:

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');

Running EXPLAIN PARTITION shows the optimizer scans only the relevant partition, not all partitions.

If a query cannot use the partition key, MySQL scans every partition, which can be dangerous for performance.

LIST Partition

LIST partitions are similar to RANGE but use discrete values defined with VALUES IN.

HASH Partition

HASH partitions distribute rows evenly across predefined partitions using a hash function, aiming for balanced storage.

KEY Partition

KEY partitions are like HASH but rely on MySQL's internal function rather than a user‑defined one.

4. Partition and Performance

Partitioning is not a magic performance booster; it mainly aids manageability and can improve OLAP query speed by limiting scans to relevant partitions.

In OLTP workloads, improper partitioning can hurt performance, especially when queries do not include the partition key, leading to scans of all partitions and increased logical I/O.

For example, a table with 10 million rows and a B‑tree height of 3, spread across 10 partitions, could require (3 + 3) × 10 logical I/O operations if the partition key is not used.

During development, you can check which partitions a query accesses using EXPLAIN PARTITIONS:

explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';

Reference: MySQL Technical Internals .