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
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. 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
same day un.add_time >= ‘2019-05-27 00:00:00’ AND un.add_time < ‘2019-05-28 00:00:00’
This month un.add_time >= ‘2019-05-01 00:00:00’ AND un.add_time < ‘2019-06-01 00:00:00’
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
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;
The total time of this time is from the previous 4s, Become 0.05s. Fast 80 times .
summary ：
1. We should try our best to avoid using subquery to do statistics .
2.where Clause optimization , Ranks the specified condition before the scope condition , And avoid using functions .
3. Indexing is the fastest and most effective solution , But avoid too many indexes （ Generally, one table is the most 6 individual ） As a result, the query rate is reduced .
Technology