MySQL分区表未指定分区键查询的性能影响及相关疑问

1. 不指定分区键的性能影响 #

• 完全无法触发分区剪枝，查询必须遍历所有分区。每个分区本质是独立的InnoDB表，数据库需要在每个分区上单独执行基于uuid_val索引的查找，再将所有分区的结果合并返回。
• 额外开销主要来自多分区索引的遍历、结果集合并这两个环节。

2. 与非分区表的性能差异 #

• 非分区表的uuid_idx是单一全局索引，查询只需扫描这一个索引即可；分区表则要扫描与分区数量相等的独立索引片段。理论上分区表的查询开销应高于非分区表，但实际表现可能因缓存、数据规模等因素出现偏差。

3. 6GB测试中跨分区开销极低的原因 #

• 内存缓存全覆盖：6GB数据量较小，所有分区的uuid_idx索引数据大概率被完全加载到InnoDB缓冲池中，查询时直接从内存读取，几乎没有磁盘IO开销，遍历多分区的额外成本微乎其微。
• 分区数量少：按月分区的6GB数据，分区数通常只有几个，遍历少量分区的额外逻辑开销可以忽略不计。
• 查询结果集极小：如果测试中的查询返回行数极少，合并结果的成本几乎可以忽略，整体延迟就会非常低。

4. 6TB高流量场景下的性能能否维持 #

• 绝对无法维持，主要原因包括：
  • 缓存无法覆盖：6TB数据的索引规模远超常规服务器的内存容量，大部分分区的索引无法进入缓冲池，查询时会频繁触发磁盘随机IO，每个分区的索引访问都会产生明显的IO延迟，累加后整体延迟会大幅上升。
  • 高流量放大开销：300 RPS的流量下，每个查询都要遍历几十甚至上百个分区（6TB按月分区的话，分区数至少几十），会导致CPU、IO资源被快速耗尽，引发性能瓶颈甚至数据库雪崩。
  • 结果合并开销线性增长：分区数越多，合并多分区结果集的CPU消耗越高，进一步加剧性能下降。

5. 无分区键时是否允许查询分区表 #

• 语法上完全可以查询，但绝不适合作为核心业务的常规查询方式：
  • 仅适合低频、低优先级的后台查询（如离线统计），绝对无法承载300 RPS的高流量。
  • 若业务无法避免这类查询，建议优化方案：
    • 重构业务逻辑，尽量在查询条件中带入分区键date_created。
    • 升级到MySQL 8.0.16及以上版本，为uuid_val建立全局二级索引（GLOBAL INDEX），让索引无需遍历所有分区即可完成查找。
    • 考虑建立(uuid_val, date_created)联合索引，若业务可以接受调整查询逻辑来利用该索引。

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