MySQL聚簇主键与表分区的差异及组合使用价值探讨

云原生阿岚

2026-7-31

聚簇主键与分区的数据组织差异及方案价值分析

原表结构与聚簇索引理解

我有一张表table1，其主键为PRIMARY(year, month, id)。该主键对应的InnoDB聚簇索引会按year、month、id的顺序将数据物理相邻存储，示例如下：

(2021, 12, 1)
(2022, 12, 1)
(2022, 12, 2)
(2023, 1, 1)

原表结构定义：

CREATE TABLE `table1` (
  `id` int AUTO_INCREMENT NOT NULL,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `raw` json DEFAULT NULL,
  `month` int NOT NULL,
  `year` int NOT NULL,
  `date` DATE NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`year`, `month`, `id`),
  KEY (`id`),
  KEY `table1_indx` (`year`, `month`,`score`,`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

针对年月的查询会因聚簇索引的有序存储而高效，例如：

EXPLAIN
SELECT
  table1.entity_id AS entity_id,
  table1.entity_type,
  table1.score
FROM table1
WHERE table1.month = 12
  AND table1.year = 2022
  AND table1.score > 0
  AND table1.entity_type IN ('type1', 'type2', 'type3', 'type4');

分区后的表结构

若将表按year做RANGE分区、按month做HASH子分区，表结构如下：

CREATE TABLE `table1` (
  `id` int AUTO_INCREMENT NOT NULL,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `raw` json DEFAULT NULL,
  `month` int NOT NULL,
  `year` int NOT NULL,
  `date` DATE NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`year`, `month`, `id`),
  KEY (`id`),
  KEY `table1_indx` (`year`, `month`,`score`,`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`year`)
SUBPARTITION BY HASH (`month`)
(PARTITION p2021 VALUES LESS THAN (2022)
 (SUBPARTITION dec_2021 ENGINE = InnoDB,
  SUBPARTITION jan_2021 ENGINE = InnoDB,
  SUBPARTITION feb_2021 ENGINE = InnoDB,
  SUBPARTITION mar_2021 ENGINE = InnoDB,
  SUBPARTITION apr_2021 ENGINE = InnoDB,
  SUBPARTITION may_2021 ENGINE = InnoDB,
  SUBPARTITION jun_2021 ENGINE = InnoDB,
  SUBPARTITION jul_2021 ENGINE = InnoDB,
  SUBPARTITION aug_2021 ENGINE = InnoDB,
  SUBPARTITION sep_2021 ENGINE = InnoDB,
  SUBPARTITION oct_2021 ENGINE = InnoDB,
  SUBPARTITION nov_2021 ENGINE = InnoDB),
 PARTITION p2022 VALUES LESS THAN (2023)
 (SUBPARTITION dec_2022 ENGINE = InnoDB,
  SUBPARTITION jan_2022 ENGINE = InnoDB,
  SUBPARTITION feb_2022 ENGINE = InnoDB,
  SUBPARTITION mar_2022 ENGINE = InnoDB,
  SUBPARTITION apr_2022 ENGINE = InnoDB,
  SUBPARTITION may_2022 ENGINE = InnoDB,
  SUBPARTITION jun_2022 ENGINE = InnoDB,
  SUBPARTITION jul_2022 ENGINE = InnoDB,
  SUBPARTITION aug_2022 ENGINE = InnoDB,
  SUBPARTITION sep_2022 ENGINE = InnoDB,
  SUBPARTITION oct_2022 ENGINE = InnoDB,
  SUBPARTITION nov_2022 ENGINE = InnoDB),
 PARTITION p2023 VALUES LESS THAN (2024)
 (SUBPARTITION dec_2023 ENGINE = InnoDB,
  SUBPARTITION jan_2023 ENGINE = InnoDB,
  SUBPARTITION feb_2023 ENGINE = InnoDB,
  SUBPARTITION mar_2023 ENGINE = InnoDB,
  SUBPARTITION apr_2023 ENGINE = InnoDB,
  SUBPARTITION may_2023 ENGINE = InnoDB,
  SUBPARTITION jun_2023 ENGINE = InnoDB,
  SUBPARTITION jul_2023 ENGINE = InnoDB,
  SUBPARTITION aug_2023 ENGINE = InnoDB,
  SUBPARTITION sep_2023 ENGINE = InnoDB,
  SUBPARTITION oct_2023 ENGINE = InnoDB,
  SUBPARTITION nov_2023 ENGINE = InnoDB),
 PARTITION pmax VALUES LESS THAN MAXVALUE
 (SUBPARTITION dec_max ENGINE = InnoDB,
  SUBPARTITION jan_max ENGINE = InnoDB,
  SUBPARTITION feb_max ENGINE = InnoDB,
  SUBPARTITION mar_max ENGINE = InnoDB,
  SUBPARTITION apr_max ENGINE = InnoDB,
  SUBPARTITION may_max ENGINE = InnoDB,
  SUBPARTITION jun_max ENGINE = InnoDB,
  SUBPARTITION jul_max ENGINE = InnoDB,
  SUBPARTITION aug_max ENGINE = InnoDB,
  SUBPARTITION sep_max ENGINE = InnoDB,
  SUBPARTITION oct_max ENGINE = InnoDB,
  SUBPARTITION nov_max ENGINE = InnoDB));

核心问题

聚簇主键与分区在数据组织上的差异是什么？
同时使用PRIMARY(year,month,id)和该分区方案是否具有价值？

一、聚簇主键与分区的数据组织差异

1. 无分区时的聚簇索引组织

InnoDB的聚簇索引以(year, month, id)为排序键，全局范围内所有数据物理连续且有序：

所有同year的数据集中在一起，按year递增排列；
同year内按month递增排列；
同year+month内按id递增排列；
整个表是单一的、连续的物理存储结构，跨年月的数据也保持全局有序。

2. 分区+子分区的组织方式

分区方案将数据物理拆分为多个独立的存储单元，每个单元内部仍遵循聚簇索引的有序性，但单元之间物理隔离：

RANGE分区：按year将数据拆分到不同的父分区（如p2021、p2022），每个父分区对应一个年份的数据，物理上独立存储；
HASH子分区：每个父分区内按month的哈希值拆分到12个子分区（因month取值1-12，哈希后每个month对应一个子分区），同一年不同月份的数据存储在不同子分区中；
每个子分区内部的数据仍按(year, month, id)有序存储，但不同子分区、父分区之间的数据没有物理连续性，比如2022年1月和12月的数据分别在两个独立的子分区中，不是连续存储的。

二、方案价值判断

是否有价值取决于业务场景：

有价值的场景

历史数据归档/批量删除
- 若需定期删除或归档某一年的数据，直接执行ALTER TABLE DROP PARTITION p2021;即可快速完成，操作仅修改元数据，比全表扫描删除高效得多，且不会产生大量数据碎片；
- 若需单独处理某一年的某个月数据，可针对性操作对应子分区（如ALTER TABLE TRUNCATE SUBPARTITION dec_2022;）。
IO性能优化与资源隔离
- 不同分区/子分区可挂载到不同存储介质（如历史冷数据放机械硬盘，当前热数据放SSD），平衡存储成本与IO性能；
- 查询指定年月的数据时，数据库会通过分区裁剪直接定位到目标分区/子分区，仅扫描该范围内的数据，减少IO开销。比如你的示例查询，无分区时需扫描全局聚簇索引中year=2022且month=12的范围，分区后直接扫描p2022的dec_2022子分区，数据量极大时差异更明显。
与二级索引协同优化
- 二级索引table1_indx会随分区拆分，查询时结合分区裁剪与覆盖索引，无需回表即可获取所需数据，进一步提升查询效率。

无价值甚至反效果的场景

小数据量表
- 若表数据量仅几万或几十万，分区带来的元数据维护复杂度会超过性能收益，聚簇索引的有序性已足够高效。
频繁跨分区查询
- 若经常需要查询跨多个年份或月份的数据（如查询2021-2023年所有12月的数据），分区会导致数据库扫描多个分区/子分区，反而不如无分区时的全局连续扫描高效。
冗余的HASH子分区
- 按month做HASH子分区的意义不大，因为month是1-12的离散值，用LIST子分区按month划分会更直观，HASH子分区反而增加了不必要的复杂度。

总结

数据组织核心差异：聚簇索引是全局单一有序结构，分区是物理拆分的多个独立有序结构，每个结构内部遵循聚簇索引顺序，但结构间物理隔离；
方案价值：若有大量历史数据归档需求、数据量极大需IO隔离，则方案有价值；若数据量小或频繁跨分区查询，则方案会增加复杂度，收益有限。

内容的提问来源于stack exchange，提问作者Kay

MySQL聚簇主键与表分区的差异及组合使用价值探讨

原表结构与聚簇索引理解 #

分区后的表结构 #

核心问题 #

一、聚簇主键与分区的数据组织差异 #