MySQL 5.5中DAYOFYEAR无法触发分区裁剪的问题排查

针对你遇到的stat_activity表分区裁剪不生效的问题，结合MySQL 5.5.37版本的特性限制，我整理了几个关键的排查点和解决方法：

1. 确保查询条件与分区键表达式严格匹配 #

你已经改为按TO_DAYS(activity_date)分区，但MySQL 5.5的优化器不会自动将日期字段的直接比较转换为对应的函数表达式，这会直接导致裁剪失效。

• 错误示例（无法触发裁剪）：

  SELECT * FROM stat_activity WHERE activity_date = '2024-05-20';
  

• 正确示例（显式使用相同函数匹配分区键）：

  SELECT * FROM stat_activity WHERE TO_DAYS(activity_date) = TO_DAYS('2024-05-20');
  

  这样优化器才能识别出查询条件与分区键的关联，进而触发分区裁剪。

2. 验证分区表定义的正确性 #

检查你的分区定义是否存在范围重叠或边界错误，比如正确的RANGE分区定义应该是这样的：

CREATE TABLE stat_activity (
    id INT,
    activity_date DATE,
    -- 其他业务字段
)
PARTITION BY RANGE (TO_DAYS(activity_date)) (
    PARTITION d1 VALUES LESS THAN (TO_DAYS('2024-05-21')),
    PARTITION d2 VALUES LESS THAN (TO_DAYS('2024-05-28')),
    PARTITION d3 VALUES LESS THAN MAXVALUE
);

确保每个分区的范围连续无重叠，边界值的计算要和查询条件中的TO_DAYS结果完全对应。

3. 用执行计划确认裁剪情况 #

执行带分区信息的执行计划，直观查看是否触发了裁剪：

EXPLAIN PARTITIONS SELECT * FROM stat_activity WHERE TO_DAYS(activity_date) = TO_DAYS('2024-05-20');

查看输出结果中的partitions列，如果仅显示d1，说明裁剪生效；如果仍显示所有分区，继续排查后续点。

4. 尝试改用RANGE COLUMNS分区（5.5原生支持） #

MySQL 5.5开始支持RANGE COLUMNS分区，直接基于日期字段分区，不需要函数转换，优化器更容易识别裁剪条件：

CREATE TABLE stat_activity (
    id INT,
    activity_date DATE,
    -- 其他业务字段
)
PARTITION BY RANGE COLUMNS (activity_date) (
    PARTITION d1 VALUES LESS THAN ('2024-05-21'),
    PARTITION d2 VALUES LESS THAN ('2024-05-28'),
    PARTITION d3 VALUES LESS THAN MAXVALUE
);

此时使用范围查询即可触发裁剪：

SELECT * FROM stat_activity WHERE activity_date >= '2024-05-20' AND activity_date < '2024-05-21';

5. 更新表统计信息 #

过时的统计信息可能导致优化器无法做出正确的分区裁剪决策，执行以下命令更新：

ANALYZE TABLE stat_activity;

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