|
这两天遇到了查询mysql速度慢的问题，经过各种排查，mysql语句添加ORDER BY和不添加，速度不能相提并论。这里记录一下解决问题的两种情况。
情况一：未添加索引
sql语句如下：（当天数据1万多条，未添加索引时，执行时间1分钟。去掉ORDER BY a.begintime desc时，47毫秒查询完成！检查了表结构，mytable1_20211020、mytable2_20211020均无索引，mytable1_20211020添加索引jobid，mytable2_20211020表添加索引mcssn，同样的sql语句，这次执行完47毫秒）
SELECT a.bizno,a.jobid,a.begintime,a.title,b.status FROM mytable1_20211020 a
left join mytable2_20211020 b ON a.jobid = b.mcssn
where 1=1 ORDER BY a.begintime desc
LIMIT 0,10;
情况二：表里添加了索引，查询还是很慢
sql语句如下：（当天表里有4万多条数据，有添加索引，执行时间8秒。去掉ORDER BY a.datetime desc查询62毫秒完成！检测了表结构，able1_20211020表里有添加索引：datetime, userid ,可依然耗时较长）
select b.name as mername,CONCAT(a.datetime)as date,a.mcssn,a.requestsn,a.realname,
e.commodity_name as productid,f.bankname as bankname from table1_20211020 a
left join user b on a.userid=b.data_key
LEFT JOIN product e on a.transtype=e.transtype and a.productid=e.commodity_type
left join bankinfo f on a.bankid=f.bankid
where a.datetime BETWEEN "2021-10-20 00:00" and "2021-10-20 23:59"
and a.userid in ("6bd379", "1a020c", "5db1e6", "84e832", "4afd12")
ORDER BY a.datetime desc
limit 0,10;
解决办法：用子查询进行优化，优化后63毫秒，sql如下
select * from (
select b.name as mername,CONCAT(a.datetime)as date,a.mcssn,a.requestsn,a.realname,
e.commodity_name as productid,f.bankname as bankname from table1_20211020 a
left join user b on a.userid=b.data_key
LEFT JOIN product e on a.transtype=e.transtype and a.productid=e.commodity_type
left join bankinfo f on a.bankid=f.bankid
where a.datetime BETWEEN "2021-10-20 00:00" and "2021-10-20 23:59"
and a.userid in ("6bd379", "1a020c", "5db1e6", "84e832", "4afd12")
limit 0,10
) t ORDER BY a.date desc;
优化前sql和优化后sql不同在于，ORDER BY放到了查询外面，就是先在子查询查出需要的数据，在所要的几条数据中进行排序
|