MySQL带WHERE与ORDER BY的查询索引选择疑难咨询

让我逐个拆解你的三个疑问，结合你的表结构和查询场景来解释：

Q1：使用customer_sale_idx索引为何还出现Using filesort？ #

核心问题出在隐式类型转换导致的索引前缀失效：你的customer_id是varchar(50)类型，但查询条件写的是customer_id=2（整数），MySQL会自动把customer_id字段的值转换成整数再和2比较。这个转换操作让优化器无法利用customer_sale_idx的customer_id前缀做精准区间定位——它没办法直接找到customer_id等于"2"的索引范围，只能扫描整个customer_sale_idx索引，再过滤出转换后符合条件的行。

虽然customer_sale_idx里同一customer_id的行是按sale排序的，但因为无法精准锁定customer_id的区间，优化器拿到的是跨多个customer_id的结果集，这些行的sale并不是全局有序的，所以必须额外执行filesort来排序，才能取到sale最小的那一行（limit 1）。

另外你看到的Using index是因为InnoDB的二级索引叶子节点会默认存储主键id，所以customer_sale_idx包含了select *需要的所有列（id、customer_id、sale），属于覆盖索引，不需要回表查询，但这解决不了排序的问题。

Q2：sale_customer_idx索引如何消除Using filesort？ #

这个索引的排序顺序是sale在前、customer_id在后，整个索引默认是按sale升序排列的。当优化器选择这个索引时，它会从索引的起始位置顺序扫描，每扫描一行就检查customer_id转换后是否等于2——因为索引本身是按sale从小到大排的，所以第一个符合条件的行就是sale最小的那一行，直接返回即可（因为limit 1）。

整个过程不需要对结果集做任何额外排序，自然就消除了Using filesort。同样，这个索引也是覆盖索引（包含sale、customer_id和主键id），所以Extra里会显示Using index，不需要回表。

Q3：为什么possible_keys仅显示customer_sale_idx，实际却用了sale_customer_idx？ #

possible_keys是MySQL根据WHERE子句中的字段，筛选出理论上能够匹配查询条件的索引列表。你的WHERE条件是customer_id=2，customer_sale_idx的前缀是customer_id，符合匹配条件，所以被列入possible_keys；而sale_customer_idx的前缀是sale，WHERE子句里没有用到sale，所以MySQL不会把它加入possible_keys。

但优化器在评估所有可用索引的执行成本时，发现sale_customer_idx可以通过顺序扫描+提前终止（找到第一个符合条件的行就停止）的方式，只需要扫描极少的行就能得到结果，比扫描整个customer_sale_idx再排序的成本低得多。所以最终优化器选择了这个不在possible_keys列表里的索引——毕竟possible_keys只是候选参考，优化器的核心目标是选成本最低的执行计划。

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