cause ： The company's order data is increasing day by day , Make the order table to do statistics query speed is abnormal slow . The average query time is 4s.

Optimization direction ：
1. see mysql Slow query log ：
mysqldumpslow -t 10 /www/mysql/mysql-slow.log # Shows the slowest of the slow query logs 10 strip sql

Since the original statistical query was written , I didn't think that much . Just want to show the results earlier , This is what happened ：
SELECT
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
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. according to sql sentence explain analysis ：
here wz_user_notexist There are two million pieces of data in the table , Two sub queries are used for statistics .

You can see it here rows Very big , Although the index is used （ Already in wz_user_notexist The composite index is added to the table ）, However, the sub query of 2 million data for statistics still consumes resources . And there was a mistake , When the amount of data is small where Clause add_time Using functions to format doesn't make a lot of difference , But the amount of data is million , If you still use a function on the left side of the equation , Will cause the function to use 200W second , This will affect the query rate .

After finding out the reasons, we optimize the two problems ：
1. Split the subquery out , With separate sql To query and merge the data formats in the program .
2. Set the where The statement is optimized to

Subquery optimization ：
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 After analysis ： obvious rows The number of rows for has been greatly reduced

The Lord in front sql optimization ：
SELECT