MySQL索引调整后过滤行为异常及查询计划变化的技术咨询

这是个非常典型的索引顺序影响优化器执行逻辑的问题，咱们一步步拆解背后的原因：

1. 原索引CourierId_idx的高效逻辑 #

原索引结构是(iCourierId,vStatus,updated_at,created_at)，完全贴合你的查询条件：

• 前两列iCourierId（等值匹配users.id）、vStatus（等值匹配'delivered'）是等值过滤条件，优化器可以快速定位到这两个条件匹配的索引分支；
• 第三列updated_at是范围条件（>= '2022-12-06 09:05:27'），因为前两列已经锁定了索引范围，优化器可以直接在这个分支里做updated_at的范围扫描，这就是为什么updated_at会显示为with index condition——过滤在索引层面就完成了，不用把数据拉到内存再筛选；
• 最后is_deleted=false确实是内存过滤，但因为前面的索引已经过滤掉了大部分不符合条件的行，需要过滤的行数很少，所以整体效率很高。

2. 新索引test3的问题所在 #

新索引结构(iCourierId,vStatus,created_at,updated_at,is_deleted)的顺序完全打乱了查询条件的逻辑：

• 前两列还是等值匹配，但第三列是created_at——你的查询里根本没用到这个字段做过滤！这就导致优化器在匹配完前两列后，无法利用索引的有序性继续定位后面的条件；
• updated_at被放在了created_at之后，由于created_at没有过滤条件，优化器没办法在索引层面做updated_at的范围扫描，只能先把所有满足iCourierId=users.id AND vStatus='delivered'的索引行全部读出来（虽然是覆盖索引不用回表，但行数可能很多），然后在内存里逐一检查updated_at >= ...，这就是为什么它变成了Filter条件；
• is_deleted放在最后，同样因为前面的created_at没有过滤条件，优化器也没办法在索引层面用这个字段过滤，只能跟着updated_at一起做内存过滤。

这种情况下，内存过滤的行数比原索引的索引层面过滤多得多，自然会导致查询变慢。

3. 正确的索引调整方向 #

如果要把is_deleted加入索引，需要遵循查询条件的优先级来排序索引列：

• 先放等值匹配列：iCourierId、vStatus、is_deleted（因为is_deleted=false也是等值条件）；
• 再放范围匹配列：updated_at；
• 最后放其他需要覆盖的列：created_at（如果查询需要的话）。

也就是创建索引：test4(iCourierId, vStatus, is_deleted, updated_at, created_at)，这样优化器可以在索引层面完成所有过滤逻辑，不用内存二次筛选，效率会比原索引更高。

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