MySQL 8.0.23含ROW_NUMBER查询选错索引，如何合理建索引无需强制指定

• 联合索引范围截断限制
  你预期使用的idx_client_enddate_division联合索引结构为(client_id, end_date, division_id)，根据MySQL最左匹配规则，过滤条件中end_date属于范围查询，范围查询之后的division_id过滤条件无法利用该索引做进一步过滤，优化器会判定该索引的过滤效率不足。
• 统计信息估算偏差
  MySQL 8.0.23默认的统计信息采样率较低时，会对end_date指定范围的匹配行数出现高估，错误判定该索引范围扫描需要读取的页数量高于idx_client_department的扫描成本，因此选择了不符合预期的索引。
• 窗口函数成本计算误差
  优化器未正确评估索引和窗口函数的适配性：如果索引顺序可以匹配ROW_NUMBER()窗口的PARTITION BY+ORDER BY逻辑，可以省去额外的排序操作，但当前版本的优化器计算成本时没有准确识别这部分收益，进一步误导了索引选择。

推荐创建如下联合索引，完全适配你的查询逻辑：

CREATE INDEX idx_client_div_enddate_someval ON 你的表名 (client_id, division_id, end_date, some_val DESC);

该索引的优势如下：

• 前两个字段匹配client_id=102等值过滤、division_id IS NOT NULL过滤条件，无范围截断问题，过滤效率远高于原有预期索引
• 前三个字段的有序性可以匹配窗口函数PARTITION BY client_id, end_date, division_id的分组逻辑，末尾的some_val DESC刚好匹配窗口的排序规则，窗口执行时不需要额外做filesort操作，性能提升明显
• 索引过滤后剩余行数更少，即使需要回表读取SELECT *的全量字段，整体成本也远低于其他索引

• 更新统计信息提升估算准确率，执行如下SQL生成字段直方图：

ANALYZE TABLE 你的表名 HISTOGRAM ON end_date, division_id WITH 100 BUCKETS;

• 开启行过滤优化开关（MySQL 8.0.19及以上版本支持），让优化器优先选择过滤性更强的索引：

SET SESSION optimizer_switch='rowid_filter=on';

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