MySQL 8.0.37中排序查询未使用多列索引的原因排查

原因 #

• 优化器成本估算逻辑：MySQL优化器会优先选择能避免Using filesort的执行计划。单列is_public索引的叶子节点直接按is_public排序，添加ORDER BY is_public时无需额外排序；而多列索引cl_pub_need_tags是按product_class_id→is_public→JSON计算列的顺序排序的，单独按is_public排序无法直接复用该索引的顺序，仍需额外排序。优化器判定前者整体成本更低，因此选择单列索引。
• 多列索引前缀未被利用：如果JOIN查询中没有对product_class_id做等值或范围过滤，多列索引的前缀列（product_class_id）无法发挥作用，优化器会认为该索引的扫描范围过大，不如体积更小的单列索引高效。
• 计算列增大索引开销：索引中包含的JSON转换计算列会让单条索引记录体积变大，扫描该索引的IO成本更高。优化器权衡IO成本和排序成本后，更倾向于选择轻便的单列索引。

解决办法 #

• 强制指定目标索引：在查询的表名后添加FORCE INDEX(cl_pub_need_tags)，强制优化器使用多列索引，示例：

  SELECT ... FROM catalogue_product FORCE INDEX(cl_pub_need_tags) JOIN ... ORDER BY ...;
  

  注意：强制索引前要实际测试执行效率，避免因数据分布变化导致性能下降。
• 利用多列索引前缀：如果业务逻辑允许，在查询中增加对product_class_id的过滤条件（比如等值匹配或范围查询），让优化器能利用多列索引的前缀排序。此时ORDER BY product_class_id, is_public可以直接复用索引顺序，无需额外排序，优化器会更倾向于选择多列索引。
• 更新表统计信息：执行ANALYZE TABLE catalogue_product;更新表的统计数据，让优化器能更准确计算不同索引的扫描成本，避免因统计信息过时导致的错误选择。
• 调整索引结构：如果该查询不需要用到索引中的JSON计算列，可以创建仅包含product_class_id和is_public的复合索引，更小的索引体积会提升其被优化器选中的概率；如果必须保留计算列，可确认查询是否为覆盖查询（即SELECT的所有列都在索引中），覆盖查询无需回表，能提升多列索引的优先级。

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