因BI操作生产库引起的内存近20分钟负载100%报警

持续时间：10:14-10:34

sql:

SELECT platform, shopname, shopcode, sum(income_amount)
, sum(EXPEND_AMOUNT)
FROM (
SELECT *
FROM order_info_bill_01
UNION ALL
SELECT *
FROM order_info_bill_02
UNION ALL
SELECT *
FROM order_info_bill_03
UNION ALL
SELECT *
FROM order_info_bill_04
UNION ALL
SELECT *
FROM order_info_bill_05
UNION ALL
SELECT *
FROM order_info_bill_06
UNION ALL
SELECT *
FROM order_info_bill_07
UNION ALL
SELECT *
FROM order_info_bill_08
UNION ALL
SELECT *
FROM order_info_bill_09
UNION ALL
SELECT *
FROM order_info_bill_10
UNION ALL
SELECT *
FROM order_info_bill_11
UNION ALL
SELECT *
FROM order_info_bill_12
) t1
WHERE (is_amount = 1
OR IS_REFUNDAMOUNT = 1)
AND shopcode = 'qnymz'
GROUP BY platform, shopname, shopcode

 

 

 执行耗时：1227046.18ms

禁止在生产库操作大量数据查询，因查询业务部分因对账或审计未完成，生产和查询相关架构未真正分开，需要BI有时实时读取或修改生产库数据，容易造成相关生产和查询需求冲突。