©2020-2024 ioDraw All rights reserved,
Privacy Policy
起因：公司的订单数据日益增多，使得订单表做统计查询速度异常缓慢。查询耗时平均为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个）导致查询速率反而降低。