大量的union all 关联的统计数据如何优化？ 临时表又只能用于一个union，谢谢

chilli6519 2020-09-26 04:04:25
需要给前端提供一个多结果的统计数据，来自很多不同的数据表， 现在使用的是 union all ，但如果基础数据表超过 10000条数据的时候，查询效率很低，需要 10秒左右，请问各位大拿像需要这种统计结果的时候一般如何处理
谢谢





SELECT count(1) from t_user_quanrl where qid = 16 and roles > 5 

union all(select count(1) from `sys_user` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and (DATE(NOW()) = DATE(a.regdate))) 



union all(select count(1) from `sys_user` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`userid` = b.`userid` and a.ugender = 1) 



union all(select count(1) from `sys_user` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`userid` = b.`userid` and a.ugender = 2) 



union all(select count(1) from `t_user_loginhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`uid` = b.`userid` and(DATE(NOW()) = DATE(a.vdate))) 



union all(SELECT count(1) from `t_user_clinc` a  where (datediff(NOW(), clincdate) = 0) and  a.bclsid = 16) 



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid`  and(DATE(NOW()) = DATE(a.mdate))) 



union all(select count(1) from `t_user_mentalhis` a inner join tmp_ql b on a.`muid` = b.`userid` ) union all(select count(1) from `t_user_iot_examdx` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`userid` = b.`userid`  and(DATE(NOW()) = DATE(a.cdate))) 



union all(select count(1) from tmp_mhis a inner join tmp_ql1 b on a.`muid` = b.`userid`  ) union all(select count(1) from `sys_user` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and year(regdate) = year(curdate()) and (month(regdate) = month(curdate()))) 



union all(select count(1) from `sys_user` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and year(regdate) = year(curdate()) and month(regdate) = month(curdate()) and (week(regdate) = week(curdate())))  



union all ( select sum(ubalance-5) from `t_user_account` a inner join (select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` ) 



union all(select sum(realpayfee) from `t_user_paybill` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`uid` = b.`userid`  and (DATE(NOW()) = DATE(a.buydate)) ) 



union all(select sum(realpayfee) from `t_user_paybill` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`uid` = b.`userid` and year(buydate) = year(curdate()) and ( month(buydate) = month(curdate()) ) ) 



union all(select sum(transfee) from `t_user_payment_bill` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`userid` = b.`userid` and payflag = 0  and(DATE(NOW()) = DATE(a.ucdate))) 



union all(select sum(transfee) from `t_user_payment_bill` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`userid` = b.`userid` and payflag = 0 and year(ucdate) = year(curdate()) and(month(ucdate) = month(curdate()))) 



union all(select sum(transfee) from `t_user_payment_bill` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`userid` = b.`userid` and payflag = 1 and year(ucdate) = year(curdate()) and (month(ucdate) = month(curdate()))) 



union all(select count(1) from `sys_user_hext` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and isbp = 1 )  



union all(select count(1) from `sys_user_hext` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and islip = 1 )  



union all(select count(1) from `sys_user_hext` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and isDiabet = 1 )  



union all(select count(1) from `t_user_iot_ecgx` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`userid` = b.`userid` and ECGRESULT like '%异常%' )  



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 3 and year(mdate) = year(curdate()) and ( month(mdate) = month(curdate()) )    ) 



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 3 and year(mdate) = year(curdate()) and ( month(mdate) = month('2020-08-26') )    ) 





union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 3 and year(mdate) = year(curdate()) and ( month(mdate) = month('2020-07-26') )    ) 





union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 4 and year(mdate) = year(curdate()) and ( month(mdate) = month(curdate()) )    ) 





union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 4 and year(mdate) = year(curdate()) and ( month(mdate) = month('2020-08-26') )    ) 



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 4 and year(mdate) = year(curdate()) and ( month(mdate) = month('2020-07-26') )    ) 



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 5 and year(mdate) = year(curdate()) and ( month(mdate) = month(curdate()) )    ) 



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 5 and year(mdate) = year(curdate()) and ( month(mdate) = month('2020-08-26') )    ) 



union all(select count(1) from `t_user_mentalhis` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 )  b on a.`muid` = b.`userid` and `vwarnlevel` = 5 and year(mdate) = year(curdate()) and ( month(mdate) = month('2020-07-26') )    ) 



union all(select count(1) from `sys_hservice_hypertension` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_hypertension` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 



union all(select count(1) from `sys_hservice_diabetes` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_diabetes` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 



union all(select count(1) from `sys_hservice_tuber_sf` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_tuber_sf` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 



union all(select count(1) from `sys_hservice_pregnant25` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_pregnant25` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 





union all(select count(1) from `sys_hservice_pregnant_born` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_pregnant_born` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 



union all(select count(1) from `sys_hservice_pregnant_b42` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_pregnant_b42` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`ARCHIVEID` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 



union all(select count(1) from `sys_hservice_babyfollow` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) ) 



union all(select count(1) from `sys_hservice_babyfollow` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) and (month(created_date) = month(curdate())) ) 



union all(select count(1) from `sys_hservice_healthinfo` a inner join(select userid from t_user_quanrl where qid = 16 and roles > 5 ) b on a.`archiveid` = b.`userid` and year(created_date) = year(curdate()) )
...全文
1786 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
落笔不要酱紫 2021-06-24
  • 打赏
  • 举报
 回复

你好，我目前也遇到了，相似的问题，能不问下您以前的解决方案嘛

chengangcsdn 2020-10-14
  • 打赏
  • 举报
 回复
如果同表 的count 可以把条件写在case中 这样你也以少写好多count 举例 select count(case when field1 = a and field = b then 1 else then 0 end ) as cnt from table
一根烂笔头 2020-10-13
  • 打赏
  • 举报
 回复
逻辑层并发如何？
AHUA1001 2020-10-13
  • 打赏
  • 举报
 回复
以上，只是最简单的例子，字段也少，可能根据您实际需求扩展。
AHUA1001 2020-10-13
  • 打赏
  • 举报
 回复
假设表Y，有数据十亿条，保存了最近10年（大约3650天）的数据，每天的数据量大体相同，每天大致有25万到30万的数据量。有CREATE_DATE字段保存数据生成时间。假如现在的时间是2020-10-15
我们需要查询最近一个时间周期的数据，比如5年的，大概的原始SQL如下：
SELECT USERID,COUNT(0) C FROM Y WHERE CREATE_DATE BETWEEN TO_DATE('20150101000000','YYYYMMDDHH24MISS') AND TO_DATE('20201015235959','YYYYMMDDHH24MISS') GROUP BY USERID ;
以上语言，大致涉及到的数据有五亿条，即使CREATE_DATE字段有分区、索引等，估计执行的速度，也不会特别理想。
解决办法，创建表S。结构USERID(类型和Y表的USERID相同)、C（数字类型）、CREATE_DATE(时间类型)。
每天晚上过了0点，定时任务执行语句
INSERT INTO S
SELECT USERID,COUNT(0) C FROM Y WHERE CREATE_DATE BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)-1/86400 AND ... ... GROUP BY USERID ,TRUNC(TRUNC(SYSDATE-1));
COMMIT ;
以上语句，是将昨天的数据，放入到S表中。
由于是定时任务，可以接受的时间比较长，执行一两个小时也不会有什么影响，而且只执行24小时的数据，应该不会特别慢。
这样，就可以将最原始的语句，修改成
SELECT USERID,SUM(C) C FROM (
SELECT USERID,COUNT(0) C FROM Y WHERE CREATE_DATE BETWEEN TO_DATE('20151014000000','YYYYMMDDHH24MISS') AND TO_DATE('20201013235959','YYYYMMDDHH24MISS') GROUP BY USERID
UNION ALL
SELECT USERID,SUM(C) C FROM S WHERE CREATE_DATE BETWEEN TO_DATE('20201014000000','YYYYMMDDHH24MISS') AND TO_DATE('20201015235959','YYYYMMDDHH24MISS') GROUP BY USERID ) GROUP BY USERID;
这样，原始表Y的数据只查询最近不到2天的数据，大致数据量不会超过60万。而S表的数据量，总共也就几千条。效率会比之前高很多。
最后的语句查询Y的时候，查询最近2天的数据，而不是1天，是因为如果查询1天，在每天刚刚过0点，定时任务没有完成的时候，查询1天会有问题。
以上，纯手工输入，可能有笔误。

说了这么多，估计您也不会改，因为改的比较多。
AHUA1001 2020-10-10
  • 打赏
  • 举报
 回复
需要实时的数据，有可以转移成本，大致有2个办法。
1、每天凌晨过了12点，把前一天所有的数据，都生成，发在统计表里。用的时候，直接查询统计表的数据，再加上当天的数据。
2、业务表里，如果有需要添删改操作，同时更新统计表里的内容，查询只查询统计表即可。但是这样对那些需要海量添删改的业务，不大适用。
以上操作，有点小复杂，但是当数据量到达一定程度的时候，在不升级硬件的前提下，单纯的靠索引、分区优化，可能到了某个级别的数据量时，查询效率还是无法接受。这个时候，就要把查询成本转移到其他的地方。思路就是在查询的时候，查询尽量少的内容，在查询前的时候，把需要查询的内容整理好。
AHUA1001 2020-10-09
  • 打赏
  • 举报
 回复
有一些相同的表，可以合并，比如：
SELECT COUNT(1) FROM TABLE_NAME WHERE ID = 1
UNION ALL
SELECT COUNT(1) FROM TABLE_NAME WHERE ID = 2
可以修改成
SELECT COUNT(1) FROM TABLE_NAME WHERE ID IN (1,2) GROUP BY ID
另外，你的这个查询，如果不涉及查询当天的数据，建议用定时任务，每天凌晨把查询结果放到一个专门的表里，以后每次查询，只查询单一的表就可以了。
chilli6519 2020-10-09
  • 打赏
  • 举报
 回复
引用 3 楼 AHUA1001 的回复:
有一些相同的表，可以合并，比如： SELECT COUNT(1) FROM TABLE_NAME WHERE ID = 1 UNION ALL SELECT COUNT(1) FROM TABLE_NAME WHERE ID = 2 可以修改成 SELECT COUNT(1) FROM TABLE_NAME WHERE ID IN (1,2) GROUP BY ID 另外，你的这个查询，如果不涉及查询当天的数据，建议用定时任务，每天凌晨把查询结果放到一个专门的表里，以后每次查询，只查询单一的表就可以了。
所以合并和定时任务都不太符合，不知道是否有其他的方案，谢谢
chilli6519 2020-10-09
  • 打赏
  • 举报
 回复
引用 3 楼 AHUA1001 的回复:
有一些相同的表，可以合并，比如： SELECT COUNT(1) FROM TABLE_NAME WHERE ID = 1 UNION ALL SELECT COUNT(1) FROM TABLE_NAME WHERE ID = 2 可以修改成 SELECT COUNT(1) FROM TABLE_NAME WHERE ID IN (1,2) GROUP BY ID 另外，你的这个查询，如果不涉及查询当天的数据，建议用定时任务，每天凌晨把查询结果放到一个专门的表里，以后每次查询，只查询单一的表就可以了。
基本上每一个的检索条件都不一样， 而且数据基本都需要在检索时实时获取
chilli6519 2020-10-05
  • 打赏
  • 举报
 回复
引用 1 楼 老紫竹 的回复:
难道你要【应用端】，开10个并行，每个查90/10=9个SQL，再应用端汇总。反正结果集没区别，就那么几个字段。
没明白你啥意思，能告知的清晰些么，谢谢
老紫竹 2020-09-27
  • 打赏
  • 举报
 回复
难道你要【应用端】，开10个并行，每个查90/10=9个SQL，再应用端汇总。反正结果集没区别，就那么几个字段。
mysql数据库，使用union all 关联临时表中，临时表数据共享
mysql数据库，使用union all 关联临时表中，临时表数据共享 ( SELECT @orderTimes := order_time, @afteramount := after_amount afteramount FROM sid_acct_balancelog t WHERE 1 = 1 AND t.cust_id = '1853' ) UNION ALL
SQL union，union all，sql临时表
第一次做网站的后台的项目，在做的过程中遇到了很多的问题，首先就是有一个函数要进行多张表的查询，然后再进行对其中的列进行求和运算 但是有一个问题就是在其中一张表上的数据比较多，而且这三张表的字段并不相同，所以首先分别对这三张表进行处理，把处理的结果放入三张临 时表里面 这里记录一下创建临时表的方法：        select * into #临时表名 from 你的表;    select
sql 统计数据将结果集放入临时表 使用union存入集合
我们在做统计报表时，有时需要将集合放入一个临时表中，然后将所有结果，放入一个结果集中。下面我写了一个例子，供大家参考。 GO /****** Object: StoredProcedure [dbo].[TaskStatics] Script Date: 04/30/2013 17:17:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIF
hive Union all 优化
https://www.cnblogs.com/end/archive/2013/01/15/2861448.html 优化时，把hive sql当做map reduce程序来读，会有意想不到的惊喜。 理解hadoop的核心能力，是hive优化的根本。这是这一年来，项目组所有成员宝贵的经验总结。 长期观察hadoop处理数据的过程，有几个显著的特征: 1.不怕数据多，就怕数据倾斜。 ...
Hive优化总结（union all）
https://www.cnblogs.com/end/archive/2013/01/15/2861448.html 优化时，把hive sql当做map reduce程序来读，会有意想不到的惊喜。 理解hadoop的核心能力，是hive优化的根本。这是这一年来，项目组所有成员宝贵的经验总结。   长期观察hadoop处理数据的过程，有几个显著的特征: 1.不怕数据多，就怕数据倾斜。 ...
MySQL

56,678

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章