Mysql ORDER BY DESC 降序排列 索引无效分析
业务场景:Mysql版本5.7.22,查询设备生产的指定状态最新在制品,tt_wo_wip(引擎InnoDB)735951条数据,equip_id为32e4537e59404a589b7f1ec4a55ab69b大概由162675条
- 无索引:4~5s
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' AND wip.wip_status != '10' AND wip.del_flag='0'
ORDER BY wip.finish_time DESC LIMIT 0,1;
- 里程碑:创建索引
CREATE INDEX idx_equipId_finishTime ON tt_wo_wip (equip_id,finish_time);
- equip_id存在0.02s;equip_id不存在5~6s
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' AND wip.wip_status != '10' AND wip.del_flag='0'
ORDER BY wip.finish_time DESC LIMIT 0,1;
- equip_id存在Extra没有索引为什么这么快
- equip_id不存在耗时较长原因分析
减少参数一 equip_id存在0.02s;equip_id不存在0.3~0.4s
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' -- AND wip.wip_status != '10' AND wip.del_flag='0'
ORDER BY wip.finish_time DESC LIMIT 0,1;
减少参数二
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' AND wip.wip_status != '10' -- AND wip.del_flag='0'
ORDER BY wip.finish_time DESC LIMIT 0,1;
减少参数三
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' AND wip.del_flag='0' -- AND wip.wip_status != '10'
ORDER BY wip.finish_time DESC LIMIT 0,1;
修改升序排列 equip_id存在0.02s;equip_id不存在0.2~0.3s
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' AND wip.wip_status != '10' AND wip.del_flag='0'
ORDER BY wip.finish_time ASC LIMIT 0,1;
减少参数+修改升序排列 equip_id存在0.02s;equip_id不存在0.2~0.3s
EXPLAIN SELECT wip.pk_id FROM tt_wo_wip wip WHERE wip.equip_id = '32e4537e59404a589b7f1ec4a55ab69b' -- AND wip.wip_status != '10' AND wip.del_flag='0'
ORDER BY wip.finish_time ASC LIMIT 0,1;
分析结果:列使用不等于不一定影响其他列使用索引;ORDER BY降序想使用索引需要查询条件都是索引列
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步