MySQL Range 分区实战：解决千万级数据查询性能瓶颈

尝试按月分区

计划按消息时间按月划分分区，使用 to_days() 函数将日期转换为天数作为分区依据。起初编写的 SQL 如下：

ALTER TABLE chat_message_history PARTITION BY RANGE(to_days(message_time)) (
  PARTITION p201708 VALUES LESS THAN (to_days('2017-08-31')),
  PARTITION p201709 VALUES LESS THAN (to_days('2017-09-30')),
  -- ... 中间省略部分月份 ...
  PARTITION p202XYZ VALUES LESS THAN (MAXVALUE)
);

执行时报错：ERROR 1566 (HY000): Not allowed to use NULL value in VALUES LESS THAN。

排查发现，虽然指定了明确的日期，但 to_days() 函数对非法日期（如 2 月 30 日）会返回 NULL。例如：

mysql> SELECT TO_DAYS('2017-02-30');
+----------------------+
| TO_DAYS('2017-02-30') |
+----------------------+
| NULL                 |
+----------------------+

修正方案： 将每个分区的边界值调整为下个月的第一天，避免跨月日期计算错误。

ALTER TABLE chat_message_history PARTITION BY RANGE(to_days(message_time)) (
  PARTITION p201708 VALUES LESS THAN (to_days('2017-09-01')),
  PARTITION p201709 VALUES LESS THAN (to_days('2017-10-01')),
  -- ... 后续月份依次类推 ...
  PARTITION p202012 VALUES LESS THAN (to_days('2021-01-01')),
  PARTITION p202XYZ VALUES LESS THAN (MAXVALUE)
);

再次执行后，报错变为：ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function。

这是 MySQL 的硬性约束：分区函数中使用的列必须包含在主键中。由于我们按 message_time 分区，而原主键仅为 id，因此需要修改主键结构。

修正方案： 删除原有主键，建立包含 id 和 message_time 的联合主键。

ALTER TABLE chat_message_history DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `message_time`);

完成上述两步后，重新执行分区语句，最终成功创建。

效果验证

对比分区前后的 EXPLAIN 结果，直观感受性能变化。查询条件为 message_time > '2017-12-01' AND message_time < '2018-01-01'。

分区前

+----+-------------+---------------------+------+---------+------+-------+------+----------+-------------+
| id | select_type | table               | type | possible_keys | key | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------+------+---------+------+-------+------+----------+-------------+
|  1 | SIMPLE      | chat_message_history| ALL  | NULL    | NULL | NULL    | NULL | 5103176  | Using where |
+----+-------------+---------------------+------+---------+------+-------+------+----------+-------------+

全表扫描行数高达 510 万，接近总数据量 530 万。

分区后

+----+-------------+---------------------+------+---------+------+-------+------+--------+-------------+
| id | select_type | table               | type | possible_keys | key | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+------+---------+------+-------+------+--------+-------------+
|  1 | SIMPLE      | chat_message_history| ALL  | NULL    | NULL | NULL    | NULL | 829848 | Using where |
+----+-------------+---------------------+------+---------+------+-------+------+--------+-------------+

分区后，扫描行数降至 80 多万，效率提升明显。

总结与思考

1. 性能提升显著：对于千万级数据量的表，Range 分区能有效减少查询时的扫描范围，缓解数据库压力。
2. 主键约束：实施 Range 分区时，务必确保分区字段包含在主键或唯一索引中，否则无法创建。
3. 长期规划：分区虽能缓解当前问题，但若查询数据量持续增长，仍需结合业务场景考虑表拆分或归档策略，以保障系统长期的可用性。

此次调整解决了线上连接数耗尽的问题，但也提醒我们在面对海量数据时，架构设计需预留足够的扩展空间。