【DataBase】SQL优化案例：其一
原始SQL:
这里想做的事情就是查询一周的一个计算值
可以理解为报表的那种
主表 t_wechat_clue 生产库上200万数据量
然后需要联表一些限制条件
SELECT IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -6 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.follow_total ELSE 0 END)),0) AS y1, IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -5 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.follow_total ELSE 0 END)),0) AS y2, IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -4 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.follow_total ELSE 0 END)),0) AS y3, IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -3 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.follow_total ELSE 0 END)),0) AS y4, IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -2 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.follow_total ELSE 0 END)),0) AS y5, IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.follow_total ELSE 0 END)),0) AS y6, IFNULL(SUM((CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL 0 DAY) AND HOUR(CLUE.gen_time) > HOUR(DATE_ADD(NOW(), INTERVAL -2 HOUR)) THEN CLUE.follow_total ELSE 0 END)),0) AS y7, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -6 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a1, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -5 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a2, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -4 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a3, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -3 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a4, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -2 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a5, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AND HOUR(CLUE.gen_time) = 22 THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a6, IFNULL(SUM(DISTINCT CASE WHEN CLUE.org_code IS NOT NULL THEN (CASE WHEN CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL 0 DAY) AND HOUR(CLUE.gen_time) > HOUR(DATE_ADD(NOW(), INTERVAL -2 HOUR)) THEN CLUE.dist_total ELSE 0 END) ELSE 0 END), 0) AS a7 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department
反正实际上数据返回到页面渲染Echarts花了10秒的样子
经理说8行，必须要改动
前提情况是，这里联表的字段和日期时间都做了索引处理
慢的原因是查询的字段导致
另外还有就是联表的字段数据类型需要一致
不要让MySQL去推导转换，这会浪费一部分性能
优化之后：
随后同事给我一套UnionALL解法，虽然SQL很多，但是效率提升明显！
SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -6 DAY) AND HOUR(CLUE.gen_time) = 22 UNION ALL SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -5 DAY) AND HOUR(CLUE.gen_time) = 22 UNION ALL SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -4 DAY) AND HOUR(CLUE.gen_time) = 22 UNION ALL SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -3 DAY) AND HOUR(CLUE.gen_time) = 22 UNION ALL SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -2 DAY) AND HOUR(CLUE.gen_time) = 22 UNION ALL SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AND HOUR(CLUE.gen_time) = 22 UNION ALL SELECT IFNULL(SUM(CLUE.follow_total),0) AS y1 , SUM(DISTINCT (CASE WHEN CLUE.org_code IS NOT NULL THEN CLUE.dist_total ELSE 0 END)) a1 FROM t_wechat_clues CLUE JOIN t_dms_member DMS_MEMBER ON CLUE.emp_id = DMS_MEMBER.employee_id AND LENGTH(CLUE.org_code)=8 AND SUBSTR(CLUE.org_code, 1, 1)='P' JOIN t_wechat_member WC_MEMBER ON DMS_MEMBER.wechat_emp_id = WC_MEMBER.userid AND WC_MEMBER.STATUS = 1 AND WC_MEMBER.TYPE = 2 JOIN (SELECT TT.id ,TT.NAME FROM ( WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte) TT ) TREE_ORG ON TREE_ORG.id = WC_MEMBER.department WHERE CLUE.gen_date = DATE_ADD(CURRENT_DATE,INTERVAL -0 DAY) AND HOUR(CLUE.gen_time) > HOUR(DATE_ADD(NOW(), INTERVAL -2 HOUR))
生产库执行结果是909ms 1秒差点就查完了
