Understanding InnoDB Storage Architecture and MySQL Partitioning

1. InnoDB Logical Storage Structure

Introduce InnoDB logical storage structure and the concept of tablespace, segment, extent, and page. All data are stored logically in a tablespace, which consists of segments, extents, and pages.

Segment

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

Extent

Extents correspond to the extent area in the diagram. An extent is a contiguous space of pages; regardless of page size, the extent size defaults to 1 MB. InnoDB allocates 4–5 extents at a time, each containing 64 pages of 16 KB.

When a segment starts, it first uses 32 small pages for data; after they are exhausted, it requests 64 contiguous pages. This allows small tables or undo segments to use less space, reducing disk overhead.

Page

Pages correspond to the page area in the diagram and are the smallest unit of InnoDB disk management. Default size is 16 KB, configurable via innodb_page_size. Common page types include data page, undo page, system page, transaction data page, insert buffer bitmap page, uncompressed and compressed BLOB pages.

2. Partition Overview

In MySQL, partitioning means distributing rows of the same table into different physical files; each partition creates a separate .ibd file. MySQL added horizontal partition support in version 5.1.

Partitioning splits a table or index into multiple manageable parts. Each partition can be handled independently, and the application code does not need to change. However, improper use can negatively affect performance.

Partitioned tables store both data and indexes within each partition; MySQL does not support global partitions. When a primary key or unique index exists, the partition column must be part of that unique key.

3. Partition Types

MySQL supports RANGE, LIST, HASH, and KEY partitioning. RANGE is the most commonly used, especially for date columns.

RANGE Partition

Rows are placed into partitions based on a column value falling within a continuous range. It is often used for date columns (e.g., year‑month). Inserting a row with a value 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:

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 PARTITION shows that the optimizer scans only the relevant partition.

If a query does not use the partition key, MySQL may scan all partitions, which can be dangerous.

LIST Partition

Similar to RANGE, but the partition column values are discrete rather than continuous, defined with VALUES IN.

HASH Partition

Distributes rows evenly across predefined partitions using a hash function, aiming for roughly equal partition sizes.

KEY Partition

Works like HASH but uses MySQL’s internal function instead of a user‑defined hash.

4. Partition and Performance

Partitioning is not a guaranteed performance boost; it can improve query speed for OLAP workloads that scan large time‑based ranges, but may add overhead for OLTP workloads that typically retrieve few rows via indexes.

In OLTP scenarios, using partitions without the partition key can cause full‑table scans and increase logical I/O, especially when many partitions exist.

To inspect which partitions a query uses, run EXPLAIN PARTITIONS SELECT ….

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

《MySQL技术内幕》