今天收到一个BUG反馈,说页面打开非常慢,经过排查得知是程序获取数据的时间过长,
SELECT * FROM `cmf_record`
WHERE `SN` = '6860FC03A735EC30' AND `status` = 1
AND ( `endtime` >= '2023-07-16 00:00:00' AND `endtime` <= '2023-07-17 11:10:49' )
ORDER BY id DESC LIMIT 0,30
经过explain后发现明明sn做过索引,但是语句依旧使用主键进行索引,所以速度非常慢。但是去掉limit 0,30后,就启用了SN索引,速度贼快。
上网查了下得知,当使用 order by xxx limit 后,如果XXX是主键,程序会默认启用主键进行索引,这时就需要对sql语句进行优化,
方式1:强制指定索引
SELECT * FROM `cmf_record` FORCE INDEX(SN)
WHERE `SN` = '6860FC03A735EC30' AND `status` = 1
AND ( `endtime` >= '2023-07-16 00:00:00' AND `endtime` <= '2023-07-17 11:10:49' )
ORDER BY id DESC LIMIT 0,30
方式2:使用子查询
SELECT * FROM (
SELECT * FROM `cmf_record`
WHERE `SN` = '6860FC03A735EC30' AND `status` = 1 AND ( `endtime` >= '2023-07-16 00:00:00' AND `endtime` <= '2023-07-17 11:10:49' ) )AS a
ORDER BY id DESC LIMIT 0,30