MySQL 8.0按月RANGE分区未显式指定分区时分区修剪失效

现有配置的三个核心问题 #

• 分区表达式设计完全不满足时间分区的实用要求
  现有方案用month(logdate)作为分区键，意味着所有年份的1月数据都会存入同一个part0分区，所有年份的2月数据都存入part1分区，以此类推。分区缺少年份维度的隔离能力，长期运行后，12个固定分区每个都会堆积跨数年的同月份数据，完全失去分区表拆分数据规模、缩小扫描范围的核心价值。
• 带完整日期范围的查询不触发分区修剪的根因
  MySQL分区修剪的触发逻辑，对“原始列被函数包裹作为分区键”的场景，不会自动把原始列上的范围条件，反向推导为函数返回值的匹配范围。比如查询里写logdate between '2020-11-01' and '2020-11-30'，优化器仅能识别到对logdate列的过滤操作，不会主动计算这个日期区间对应的month值、匹配对应分区——毕竟反向推导函数输入到输出的计算代价过高，通用场景下优化器默认不做这类转换，最终只能选择全分区扫描。
• 带month(logdate)=11等值判断的查询不触发修剪的根因
  这个查询场景的修剪失效是两层因素叠加导致的：一是MONTH()函数的返回值是循环跳变的（比如12月之后接1月，返回值从12跳回1），优化器没法为这类非全局单调的函数，快速建立“返回值=X对应哪个分区”的匹配映射，会直接放弃修剪判断；二是MySQL本身不推荐仅用MONTH()这类仅返回时间子部分的函数作为RANGE分区的分区键，8.0版本的多个小版本都存在这类场景的修剪失效问题。哪怕显式指定分区能返回正确结果，优化器也没法自动识别该路由到哪个分区。

可靠的修正方向 #

对应官方文档给出的两类时间分区实现方案，可任选其一调整：

1. 保留函数式RANGE分区的写法，替换分区表达式
   把分区键从month(logdate)改成可生成全局严格递增整数值的时间表达式，比如YEAR(logdate)*100 + MONTH(logdate)，分区边界也对应按这个整数值设置（比如某分区VALUES LESS THAN (202012)，代表存储2020年11月的全量数据）。这种写法下，不管是写完整日期范围条件，还是直接写分区表达式的等值判断，都能稳定触发分区修剪。
2. 改用RANGE COLUMNS分区（官方优先推荐的可靠方案）
   直接把原始logdate列作为分区列，分区边界直接写具体日期值，不需要包裹任何函数。这种写法下优化器不需要做任何函数推导，只要WHERE子句带logdate的范围过滤，就能直接匹配对应分区，修剪触发可靠性最高，示例结构：

   PARTITION BY RANGE COLUMNS(logdate)
   (
     PARTITION part202011 VALUES LESS THAN ('2020-12-01'),
     PARTITION part202012 VALUES LESS THAN ('2021-01-01'),
     PARTITION partmax VALUES LESS THAN MAXVALUE
   )
   

注：现有主键设计(id, logdate)符合MySQL分区表的约束要求（分区键需要包含在所有唯一键定义中），调整分区方案时不需要修改主键结构。

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