MySQL 8中如何按年份分区再按月份子分区优化查询性能

问题背景 #

现有一张包含month和year列的大表，原查询（如WHERE month=1 AND year=2022）耗时约2分30秒；按month分区后，查询耗时缩短至21秒。现需在MySQL 8.0中实现按年份分区、再按月份子分区进一步优化性能，同时了解MySQL 8.0与5.x版本在分区/子分区特性上的差异。

相关表结构及查询语句如下：

原表结构 #

CREATE TABLE `table_1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `month` int NOT NULL DEFAULT '0',
  `year` int NOT NULL DEFAULT '0',
  `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 (`id`),
  KEY `idx_month_year` (`month`,`year`, `entity_type`)
)

按month分区后的表结构 #

CREATE TABLE `table_1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `month` int NOT NULL DEFAULT '0',
  `year` int NOT NULL DEFAULT '0',
  `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 (`id`,`month`),
  KEY `idx_month_year` (`month`,`year`, `entity_type`)
) ENGINE=InnoDB AUTO_INCREMENT=21000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`month`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
 PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p12 VALUES IN (12) ENGINE = InnoDB) */

实际查询语句 #

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

一、按年份分区+月份子分区的实现步骤 #

1. 表结构设计（含分区定义） #

MySQL中，子分区仅支持对RANGE或LIST类型的主分区进行拆分。这里选择LIST按年份做主分区，再用LIST按月份做子分区，同时注意：主键必须包含所有分区键（主分区键+子分区键），否则无法创建分区表。

示例表结构如下：

CREATE TABLE `table_1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `month` int NOT NULL DEFAULT '0',
  `year` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  -- 主键必须包含year和month（分区键）
  PRIMARY KEY (`id`, `year`, `month`),
  -- 覆盖索引：包含查询过滤与返回字段，避免回表
  KEY `idx_year_month_type_score` (`year`, `month`, `entity_type`, `score`)
) ENGINE=InnoDB AUTO_INCREMENT=21000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY LIST (`year`)
SUBPARTITION BY LIST (`month`)
SUBPARTITION TEMPLATE (
  SUBPARTITION sp0 VALUES IN (0),
  SUBPARTITION sp1 VALUES IN (1),
  SUBPARTITION sp2 VALUES IN (2),
  SUBPARTITION sp3 VALUES IN (3),
  SUBPARTITION sp4 VALUES IN (4),
  SUBPARTITION sp5 VALUES IN (5),
  SUBPARTITION sp6 VALUES IN (6),
  SUBPARTITION sp7 VALUES IN (7),
  SUBPARTITION sp8 VALUES IN (8),
  SUBPARTITION sp9 VALUES IN (9),
  SUBPARTITION sp10 VALUES IN (10),
  SUBPARTITION sp11 VALUES IN (11),
  SUBPARTITION sp12 VALUES IN (12)
) (
  PARTITION p2021 VALUES IN (2021),
  PARTITION p2022 VALUES IN (2022),
  PARTITION p2023 VALUES IN (2023),
  -- 可根据实际业务年份继续添加
  PARTITION p_other VALUES IN (0) -- 存放异常年份数据
);

2. 关键说明 #

• SUBPARTITION TEMPLATE：定义子分区模板后，所有主分区会自动生成对应月份的子分区，无需逐个主分区重复配置。
• 索引优化：新索引idx_year_month_type_score覆盖了查询的过滤条件（year/month/entity_type/score）和返回字段，查询时直接从索引取数，无需回表查询主键索引，进一步提升效率。
• 分区维护：后续新增年份时，执行ALTER TABLE table_1 ADD PARTITION (PARTITION p2024 VALUES IN (2024));即可自动创建对应月份的子分区。

二、MySQL 8.0与5.x版本分区/子分区特性差异 #

1. 存储引擎支持限制

   • MySQL 5.x：支持InnoDB、MyISAM、NDB等存储引擎的分区表；
   • MySQL 8.0：仅支持InnoDB和NDB存储引擎，MyISAM不再支持分区操作。

2. 分区修剪优化

   • MySQL 8.0优化了分区修剪逻辑，对IN、BETWEEN等条件的查询能更精准定位目标分区/子分区，减少无效数据扫描。

3. 在线分区操作支持

   • MySQL 8.0支持ADD PARTITION、DROP PARTITION等在线操作，执行时不锁全表，对业务影响更小；MySQL 5.x中多数分区操作会锁表，影响可用性。

4. 子分区管理灵活性

   • 两者均要求所有主分区的子分区定义完全一致，但MySQL 8.0优化了SUBPARTITION TEMPLATE的执行逻辑，批量配置子分区更稳定，降低手动出错概率。

5. 分区键类型放宽

   • MySQL 5.x中分区键仅限整数或可转换为整数的类型；MySQL 8.0支持直接用日期类型（DATE/DATETIME）作为分区键，无需转换为整数。

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