MySQL分区表SELECT性能最优方案：显式选分区、WHERE修剪还是二者结合？

结论 #

优先选择A写法，其性能和理论最优的C写法差距极小，同时能满足你架构解耦的需求；三者中B写法性能最差且返回结果不准确。

三种写法的执行逻辑与性能对比 #

你的表采用year做范围分区，每个分区下按cycle做哈希子分区共12个，三种写法的执行逻辑差异如下：

• B写法：显式指定了p2022分区和p1子分区，但WHERE条件没有year和cycle过滤，会扫描这两个分区内所有满足amount>10的记录，不仅会返回不符合year=2022、cycle=1的错误数据，扫描的数据量也远大于另外两种写法，性能最差。
• C写法：显式指定分区+完整过滤条件，执行时直接跳过分区修剪步骤，锁定指定分区扫描符合条件的记录，理论性能是三者最高的。
• A写法：依赖自动分区修剪，因为WHERE条件携带了分区键year、子分区键cycle的等值条件，优化器可以直接计算匹配到唯一的目标分区，不需要扫描其他分区。

分区修剪的开销说明 #

你关心的简单等值条件下的分区修剪开销极低，属于微秒级，和后续查询扫描数据、IO读取的开销相比完全可以忽略不计，哪怕是高QPS场景下也不会成为性能瓶颈。

选择A写法的合理性 #

你倾向的分区修剪方案确实是更合理的工程选择，优势非常明显：

• 没有和底层分区规则强绑定，后续如果调整分区规则（比如拆分分区、新增分区、甚至取消分区改为普通表），现有查询语句不需要做任何修改，维护成本极低
• 避免手动指定分区写错的人为错误，比如误写分区名导致返回错误结果或者扫描更多分区
• 业务代码不需要感知底层存储的分区逻辑，可读性更好

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