记一次百万级数据SQL语句优化

起因：公司的订单数据日益增多，使得订单表做统计查询速度异常缓慢。查询耗时平均为4s。

优化方向：
1.查看mysql慢查询日志：
mysqldumpslow -t 10 /www/mysql/mysql-slow.log #显示出慢查询日志中最慢的10条sql

由于当初写统计查询时，没有考虑那么多。只想把结果早点显示出来，便出现了这种情况：

SELECT  s.id,s.shop_mer_id,s.shop_name,s.shop_head_img,s.shop_type,s.shop_reg_time,a.name as hhr,IFNULL(SUM(total_fee)*0.01,0) sum_total,
IFNULL(un2.day_total,0) day_total,IFNULL(un3.month_total,0) month_total from wz_shop s
LEFT JOIN wz_agent as a on s.shop_agent_id = a.id 
LEFT JOIN wz_user_notexist un ON s.merchantno_fuiou = un.merchantno_fuiou
LEFT JOIN (SELECT merchantno_fuiou,SUM(total_fee)*0.01 day_total FROM wz_user_notexist 
WHERE DATE_FORMAT(add_time,'%Y-%m-%d') = CURDATE() GROUP BY merchantno_fuiou) un2 ON s.merchantno_fuiou = un2.merchantno_fuiou
LEFT JOIN (SELECT merchantno_fuiou,SUM(total_fee)*0.01 month_total FROM wz_user_notexist 
WHERE DATE_FORMAT(add_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY merchantno_fuiou) un3 ON s.merchantno_fuiou = un3.merchantno_fuiou
WHERE s.shop_agent_id = 264 and s.status = '2' GROUP BY s.id  order by s.shop_reg_time desc;

2.根据sql语句explain分析：
此处wz_user_notexist表中的数据有两百万条，使用了两次子查询做统计。

可以看到这里的rows非常的大，虽然使用了索引（已在wz_user_notexist表中添加了复合索引），但是子查询两百万的数据做统计依然消耗资源。并且这里犯了个失误，数据量小的时候where子句中add_time使用函数来格式化并不会造成很大影响，但是数据量达到百万级别，若还在等式左边使用函数，就会造成函数使用了200W次，这时就会影响查询速率。

找出原因后针对这两个问题进行优化：
1.将子查询剖离出来，用单独的sql去查询并在程序中再将数据格式合并。
2.将子查询中的where语句优化为
当天un.add_time >= ‘2019-05-27 00:00:00’ AND un.add_time < ‘2019-05-28 00:00:00’
本月un.add_time >= ‘2019-05-01 00:00:00’ AND un.add_time < ‘2019-06-01 00:00:00’

子查询优化：

SELECT  s.id,IFNULL(SUM(total_fee)*0.01,0) month_total from wz_shop s
LEFT JOIN wz_user_notexist un ON s.merchantno_fuiou = un.merchantno_fuiou
WHERE s.shop_agent_id = 264 AND s.status = '2' AND un.add_time >= '2019-05-01 00:00:00' AND un.add_time < '2019-06-01 00:00:00'
GROUP BY s.id

explain分析后：明显rows的行数降低了非常之多

前面的主sql优化：

SELECT  s.id,s.shop_mer_id,s.shop_name,s.shop_head_img,s.shop_type,s.shop_reg_time,a.name as hhr,IFNULL(SUM(total_fee)*0.01,0) sum_total from wz_shop s
LEFT JOIN wz_agent as a on s.shop_agent_id = a.id 
LEFT JOIN wz_user_notexist un ON s.merchantno_fuiou = un.merchantno_fuiou
WHERE s.shop_agent_id = 264 and s.status = '2' GROUP BY s.id order by s.shop_reg_time desc;

这次的总耗时从之前的4s，变成了0.05s。速度快了80倍。
总结：
1.做百万级数量统计时应尽量避免使用子查询做统计。
2.where子句优化，将指定条件排在范围条件前，并且避免使用函数。
3.建立索引是最快最有效的解决方案，但是也要避免索引过多（一般一个表最多6个）导致查询速率反而降低。