起因:公司的订单数据日益增多,使得订单表做统计查询速度异常缓慢。查询耗时平均为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个)导致查询速率反而降低。

技术
©2020 ioDraw All rights reserved
2020前端学习路线收集整理【C语言】乘法表29岁“退休程序员”郭宇: 有钱的人不一定自由,自由的人不一定有钱python gui界面实例-Python GUI编程完整示例pytorch训练过程中GPU利用率低学习java 的第一节课vue element-ui实现input输入框金额数字添加千分位对于面试官的问答: 你在项目组里拿到一个项目是怎么开展的呢???算法题的输入大总结小程序表单中 多个循环input 如何取每个input的值