mysql sum 大量数据查询慢的加速的一种处理方法

有时候查一个月数据，要用到sum来统计，数据大的话，查询起来那一个是慢，可以考虑用联合查询

1.建视图，把一个月要查的分成三段，然后做成一个视图再查 

CREATE VIEW B4SUM202302 AS SELECT * FROM (SELECT a,b FROM ( SELECT SUM(B4)AS a,COUNT(*) AS b FROM WTP.tralog WHERE Status='1' AND B39='00' AND

InstNo='00000000' AND Date BETWEEN '2023-02-01' AND '2023-02-10' AND
Id IN ('000000000005017','000000000005024','000000000005130','000000000005138',
'000000000005162','000000000005164','000000000005204','000000000005302','000000000005305',
'000000000006210','000000000006867','000000000007328' ) )AS tt UNION SELECT a,b FROM (SELECT SUM(B4)AS a,COUNT(*) AS b FROM  WTP.tralog WHERE Status='1' AND B39='00' AND
InstNo='00000000' AND Date BETWEEN '2023-02-11' AND '2023-02-20' AND
Id IN ('000000000005017','000000000005024','000000000005130','000000000005138',
'000000000005162','000000000005164','000000000005204','000000000005302','000000000005305',
'000000000006210','000000000006867','000000000007328' )) AS tt2 UNION SELECT a,b FROM (SELECT SUM(B4)AS a,COUNT(*) AS b FROM  WTP.tralog WHERE Status='1' AND B39='00' AND
InstNo='00000000' AND TransDate BETWEEN '2023-02-21' AND '2023-02-31' AND
Id IN ('000000000005017','000000000005024','000000000005130','000000000005138',
'000000000005162','000000000005164','000000000005204','000000000005302','000000000005305',
'000000000006210','000000000006867','000000000007328' )) AS tt3 ) AS tb3;

SELECT SUM(a) AS 金额,SUM(b)AS 消费笔数 FROM  B4SUM202302

 

 

不建视图

SELECT SUM(a) AS 金额,SUM(b)AS 消费笔数 FROM (SELECT * FROM (SELECT a,b FROM ( SELECT SUM(B4)AS a,COUNT(*) AS b FROM WTP.tralog WHERE Status='1' AND B39='00' AND
InstNo='00000000' AND Date BETWEEN '2023-04-01' AND '2023-04-10' AND
Id IN ('000000000005017','000000000005024','000000000005130','000000000005138',
'000000000005162','000000000005164','000000000005204','000000000005302','000000000005305',
'000000000006210','000000000006867','000000000007328' ) )AS tt UNION SELECT a,b FROM (SELECT SUM(B4)AS a,COUNT(*) AS b FROM WTP.tralog WHERE Status='1' AND B39='00' AND
InstNo='00000000' AND Date BETWEEN '2023-04-11' AND '2023-04-20' AND
Id IN ('000000000005017','000000000005024','000000000005130','000000000005138',
'000000000005162','000000000005164','000000000005204','000000000005302','000000000005305',
'000000000006210','000000000006867','000000000007328' )) AS tt2 UNION SELECT a,b FROM (SELECT SUM(B4)AS a,COUNT(*) AS b FROM WTP.tralog WHERE Status='1' AND B39='00' AND
InstNo='00000000' AND Date BETWEEN '2023-04-21' AND '2023-04-31' AND
Id IN ('000000000005017','000000000005024','000000000005130','000000000005138',
'000000000005162','000000000005164','000000000005204','000000000005302','000000000005305',
'000000000006210','000000000006867','000000000007328' )) AS tt3 ) AS tb3) AS tbb