MySQL查询未命中illegal_time联合索引原因及优化咨询

• 联合索引覆盖字段不足：你创建的illegal_time联合索引仅包含illegal_time、audit_status、uploading_status三个字段，没有包含查询需要的device_id、device_name字段。如果走这个索引，筛选出符合条件的行后，还需要逐一回表到主键索引查询缺失的字段，随机IO成本非常高。
• 优化器成本计算优先级：你当前查询需要按device_id分组，device_id独立索引是有序结构，相同device_id的索引项连续存储，分组时不需要额外排序，直接遍历索引就能完成count统计，优化器判定这个方案的成本比走illegal_time联合索引+回表+分组排序的成本更低，因此选择了device_id索引。
• 删除device_id索引后走全表扫描的逻辑：当没有device_id索引时，走illegal_time联合索引需要先筛选符合条件的行、回表取字段、再对device_id做排序分组，整套流程的成本已经高于直接顺序扫描全表的成本，因此优化器选择全表扫描。

这个结论只在当前场景下成立：当索引扫描需要大量回表操作，且需要访问的数据量占总表数据量比例超过15%~20%时，索引扫描产生的随机IO成本会远高于全表扫描的顺序IO成本，此时全表扫描性能确实更好。如果能创建符合查询逻辑的覆盖索引，索引扫描的性能会远高于全表扫描。

可以创建专门适配当前查询的覆盖索引，完全避免回表和额外排序：

CREATE INDEX idx_audit_device_time ON illegal_record (audit_status, device_id, illegal_time, device_name);

这个索引的逻辑优势：

1. 等值条件audit_status=1放在最左位，快速筛选符合状态的记录
2. 第二位是分组字段device_id，相同device_id的索引项连续存储，分组时不需要额外排序
3. 第三位是范围过滤字段illegal_time，可以直接在索引内完成时间范围过滤
4. 最后包含查询需要的device_name字段，所有查询用到的字段都在索引中，不需要回表

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