六号战场问题日志记录 – 2020/02/18

2020/02/18 20:04 问题已解决

最近在调试某个接口时发现了某些SQL数据查询的耗时超乎想象(超过20秒),尝试了各种努力都没有任何改善。

Id、pidID 、 Id,pidID 、 pidID,Id 索引都已建立

select * from r6sg_record where pidID = 3 order by id DESC limit 1;

耗时25.44秒,返回1行/共84万行

select * from r6sg_record where pidID = 3 order by id DESC;

耗时4.47秒,返回1888行/共84万行

select * from r6sg_record where pidID = 3 order by id ;

耗时4.00秒,返回1888行/共84万行

select * from r6sg_record where pidID = 3 order by id limit 1;

耗时0.00秒,返回1行/共84万行

EXPLAIN select * from r6sg_record where pidID = 3 order by Id DESC limit 1;

id1
select_type SIMPLE
tabler6sg_record
partitionsnull
typeindex
possible_keysrecord,pidID,pidID_2
key PRIMARY
key_len4
refnull
rows613
filtered0.16
extraUsing where

EXPLAIN select * from r6sg_record where pidID = 3 order by Id DESC;

id1
select_type SIMPLE
tabler6sg_record
partitionsnull
typeref
possible_keysrecord,pidID,pidID_2
keyrecord
key_len4
refconst
rows1188
filtered100.00
extraUsing index condition; Using filesort