MySQL 分区表按时间拆分示意图

MySQL 分区表实战：按时间分区管理日志与订单数据

作者

为什么需要分区表

当一张表的数据量持续增长到数千万甚至上亿行时，即使索引设计合理，查询和写入性能也会明显下降。全表扫描变慢、索引膨胀、备份耗时增加，这些问题会逐渐侵蚀数据库的可用性。分区表（partitioned table）通过把一张大表按某种规则拆分成多个物理存储单元，让查询和运维操作只作用于相关分区，从而显著改善性能。

MySQL 的分区（partition）是一种表级优化手段：逻辑上仍是一张表，物理上却按分区键（partition key）把数据分散到多个分区文件中。最常见的分区方式是按时间，例如按月份或按年份分区。对于日志、订单、流水这类天然带有时间属性的数据，按时间分区既能加速查询，又能方便地清理过期数据。本文将以日志表和订单表为例，完整演示 MySQL 分区表的建表、维护与查询优化。如果你希望从整体上提升数据库性能，可以先参考我们关于 MySQL 慢查询分析 的实践，建立排查慢查询的基础能力。

MySQL 分区表按时间拆分示意图

选择合适的分区类型

MySQL 支持多种分区类型，选择哪种取决于分区键的数据特征。RANGE 分区按连续区间划分，适合按时间或数值范围分区；LIST 分区按离散值列表划分，适合按地区或状态分区；HASH 分区按哈希值均匀分布，适合没有明显范围特征的数据；KEY 分区与 HASH 类似，但使用 MySQL 内置的哈希函数。

-- RANGE 分区：按时间范围
CREATE TABLE access_log (
  id BIGINT NOT NULL,
  log_time DATETIME NOT NULL,
  url VARCHAR(255),
  PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);

对于日志和订单数据，RANGE 分区是最常用的选择，因为它能按时间自然切分，并且支持后续按分区直接删除过期数据。需要注意的是，分区键必须包含在主键（primary key）中，否则 MySQL 会报错。上面的示例把 log_time 加入主键，就是为了满足这一约束。

按时间分区建表实战

下面以一张订单表为例，演示按月份分区的完整建表语句。订单数据通常需要按时间查询，例如统计某个月的销售额，或查询某段时间内的订单明细。按月份分区后，这类查询只需扫描对应分区，速度会明显提升。

CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  customer_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at),
  KEY idx_customer (customer_id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01'))
);

TO_DAYS() 函数把日期转换为天数，用于按天边界划分分区。每个分区对应一个自然月，LESS THAN 指定分区的上界。当新月份的数据到来时，需要提前添加新分区，否则超出最后一个分区上界的数据会插入失败。对于订单这类持续增长的数据，建议在每月初自动添加下月分区。

分区表的日常维护

分区表的核心优势之一，是可以通过分区级别的操作快速完成数据管理，而无需逐行删除。例如，要清理三个月前的日志，只需删除对应的分区，比 DELETE 语句快得多，也不会产生大量 binlog 和碎片。

-- 删除过期分区（快速清理历史数据）
ALTER TABLE access_log DROP PARTITION p2024;

-- 添加新分区
ALTER TABLE access_log ADD PARTITION (
  PARTITION p2027 VALUES LESS THAN (2028)
);

-- 查看分区信息
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'access_log';

DROP PARTITION 会直接删除整个分区的数据文件，速度远快于逐行删除，且能立即释放磁盘空间。ADD PARTITION 用于扩展分区范围。通过 information_schema.PARTITIONS 可以查看每个分区的行数和存储情况，便于监控分区是否均衡、是否需要清理。对于日志类数据，建议把分区清理纳入定时任务，例如每月自动删除超过保留期限的分区，避免数据无限增长挤占磁盘空间。关于磁盘空间与日志清理的更多实践，可以参考我们关于 Nginx 限流配置 的思路，从流量入口控制日志产生量。

MySQL 分区表日常维护示意图

查询优化与分区裁剪

分区表只有在查询条件包含分区键时，才能触发”分区裁剪”（partition pruning），即只扫描相关分区而不是全表。如果查询条件不包含分区键，MySQL 会扫描所有分区，性能反而可能下降。因此，设计查询时必须把分区键纳入过滤条件。

-- 触发分区裁剪：查询条件包含分区键 created_at
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-02-01'
  AND created_at < '2026-03-01'
  AND customer_id = 1001;

-- 查看执行计划中的分区信息
EXPLAIN PARTITIONS SELECT * FROM orders
WHERE created_at >= '2026-02-01';

使用 EXPLAIN 查看执行计划时，如果 partitions 列只显示少数几个分区，说明分区裁剪生效。在典型场景下，按月份分区后，单月查询只需扫描 1 个分区，相比全表扫描能减少 90% 以上的数据读取量（典型区间），查询延迟显著下降。对于需要按客户和时间联合查询的场景，建议在分区键之外再建立合适的二级索引，进一步加速定位。如果查询仍然缓慢，可以结合 Redis 持久化与恢复 的思路，把高频只读数据缓存到内存层，减轻数据库的查询压力。

MySQL 分区裁剪查询优化示意图

分区表的注意事项

分区表并非万能，使用前需要权衡几个限制。首先，分区键必须包含在所有唯一索引（unique index）中，这限制了某些业务场景的建模方式。其次，分区数量不宜过多，一般建议控制在几十到几百个分区，过多分区会增加元数据管理开销。第三，分区表对某些操作的支持有限，例如外键（foreign key）在分区表中不受支持。

-- 检查分区表是否支持当前 MySQL 版本
SHOW PLUGINS;
-- 查看分区相关状态
SELECT * FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_db';

在决定是否使用分区表前，建议先评估数据量和查询模式。如果单表数据量在千万行以内，且查询条件稳定，普通索引可能已经足够；只有当数据量持续增长、存在明显的时间维度、且需要定期清理历史数据时，分区表才真正发挥价值。对于日志类数据，分区表几乎是标配方案；对于订单类数据，则需要结合业务查询模式谨慎评估。此外，分区表在备份和恢复时也有特殊性，建议在测试环境验证分区表的备份流程，确保数据可完整恢复，相关思路可以参考我们关于 Certbot 与服务器 SSL（Secure Sockets Layer，安全套接层）排错 中提到的”先验证再上线”的运维原则。

总结

MySQL 分区表通过按时间拆分大表，让日志和订单数据的查询、清理和维护都变得更加高效。从选择分区类型、建表、日常维护到查询优化，每一步都需要结合业务特点设计。分区裁剪是性能提升的关键，务必让查询条件包含分区键。如果你正在排查数据库慢查询问题，可以参考我们关于 MySQL 慢查询分析 的实践，结合执行计划定位瓶颈。对于需要长期保存大量日志的场景，分区表配合定期清理策略，能有效控制数据规模，让数据库保持稳定运行。Hostease 的云数据库方案支持分区表等高级特性，为数据量增长提供了灵活的扩展空间。建议你从一张数据量最大的日志表开始，先在小范围测试分区方案，验证查询性能和清理效率后再推广到其他表，逐步建立一套可持续的数据库容量管理流程。

发表评论