RANGE COLUMNS按时间字段分区可使千万级订单/日志表查询耗时降至1/5～1/10，但需WHERE条件直接使用非函数包裹的分区键；其比RANGE(YEAR())更可靠因支持分区修剪，而后者因函数导致全分区扫描。
直接说结论：对千万级以上的订单、日志类表，用 RANGE COLUMNS 按时间字段分区，能立竿见影地把查询耗时压到原来的 1/5～1/10，前提是 WHERE 条件里必须带上分区键，且不能对它用函数。
为什么 RANGE COLUMNS 比 RANGE(YEAR()) 更可靠
很多人一上来就写 PARTITION BY RANGE (YEAR(created_at))，结果发现加了索引也没用——因为 MySQL 无法对函数结果做分区修剪（Partition Pruning）。查询时仍要扫描所有分区。
真正有效的写法是：
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
created_at DATETIME NOT NULL
) PARTITION BY RANGE COLUMNS(created_at) (
PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
- 分区键
created_at必须是非空（NOT NULL），否则建表失败 - 主键必须包含分区键，所以推荐用
PRIMARY KEY (id, created_at) - 每个
VALUES LESS THAN值必须是字面量日期字符串，不能是表达式或变量 - 使用
RANGE COLUMNS后，WHERE created_at >= '2023-01-15'这类条件才能精准命中分区
哪些查询能真正受益，哪些会失效
分区不是银弹。只有满足以下全部条件的查询，才会触发分区修剪：
- WHERE 子句中明确包含分区键（如
created_at） - 比较操作符是
=、>、<、>=、<=、BETWEEN - 分区键未被任何函数包裹（禁止
WHERE YEAR(created_at) = 2023） - 没有跨多个不连续分区的 OR 条件（如
created_at = '2023-01-01' OR created_at = '2023-06-01'可能导致全分区扫描）
验证是否生效，执行 EXPLAIN PARTITIONS SELECT ...，看 partitions 列是否只列出目标分区（比如 p202301,p202302），而不是 ALL。
分区数量与维护节奏怎么定
分区不是越多越好。太多分区（比如按天分三年就是 1000+ 个）会拖慢元数据操作、增大打开表缓存压力；太少（比如只分年）又起不到隔离冷热数据的作用。
- 电商/订单类场景：按月分区最平衡，兼顾查询粒度和管理成本
- 实时日志类场景：可按周或双周，避免单分区膨胀过快
- 务必提前创建未来 2–3 个月的分区，用
ALTER TABLE ... REORGANIZE PARTITION动态追加，别等数据插入时报错 - 删除旧数据时，用
ALTER TABLE orders DROP PARTITION p202101，比DELETE WHERE created_at < '2021-02-01'快百倍，且不锁表 - 每月初执行一次
ANALYZE TABLE orders，更新各分区统计信息，防止优化器误判
最容易被忽略的一点：分区键一旦选定就很难改。如果业务后期发现常用查询其实是按 user_id 聚合，而你当初只按时间分了区，那只能重建表——这在亿级表上意味着数小时停机。所以建表前，一定先翻一遍 slow_log 和核心报表 SQL，确认高频过滤字段是谁。
