SELECT   SUM (moneylist0_.money) AS col_0_0_
FROM   tab_base_moneylist moneylist0_
WHERE   moneylist0_.GROUP_ID = :1

--这个语句应该是统计商户账户上有多少余额的

针对以上SQL,建议作以下改善

转化成

SELECT VALUE FROM TAB_TOPUP_CUSTOMER_ACCOUNT WHERE GROUP_ID=:1

存在问题：若数据增长很大，GROUP_ID 对应的充值与消费很多且分别分布在很多年里面，则整个表(tab_base_moneylist)的数据将会很大，不利于在线交易数据的处理,若实施瘦身计划(只保留最近一两年的交易数据)，则造成业务上的不完整。
优化思路:
    1. 生成一个实时统计汇总报表(TAB_TOPUP_CUSTOMER_ACCOUNT)，表(tab_base_moneylist)每一笔数据的DML(insert,update,delete)实时反应到实时统计汇总报表(TAB_TOPUP_CUSTOMER_ACCOUNT)
    2. 若实现上述功能则需要触发器(TRG_TAB_BASE_MONEYLIST)实时触发到实时统计汇总报表(TAB_TOPUP_CUSTOMER_ACCOUNT)，以反应对表(tab_base_moneylist)每一笔数据的DML操作
    3. 为保证业务的完整性，需要设置两表GROUP_ID的非空属性，表(tab_base_moneylist)作为交易操作及日志查询表，表(TAB_TOPUP_CUSTOMER_ACCOUNT)则反应GROUP_ID总的余额
    4. 经测试对于GROUP_ID 的汇总一致，查询时间原SQL 大概5秒左右，转化后该SQL只需要20毫秒

1.原记录表：TOPUP.TAB_BASE_MONEYLIST

CREATE TABLE TOPUP.TAB_BASE_MONEYLIST
(
ID            NUMBER(19)                      NOT NULL,
CREATEDATE    TIMESTAMP(6),
LASTDATE      TIMESTAMP(6),
MEMO          VARCHAR2(200 CHAR),
MONEY         FLOAT(126)                      NOT NULL,
PK            NUMBER(19),
SERIALNUMBER VARCHAR2(50 CHAR),
TOTLEMONEY    FLOAT(126)                      NOT NULL,
URL           VARCHAR2(150 CHAR),
VALID         NUMBER(1)                       NOT NULL,
AGENT_ID      NUMBER(19),
EMPLOYEE_ID   NUMBER(19),
TRADETYPE_ID NUMBER(19),
GROUP_ID      NUMBER(19)                      NOT NULL,
CLOSEOFF      NUMBER(1),
BURSELIST_ID NUMBER(10)
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

2.新增实时统计汇总报表：TAB_TOPUP_CUSTOMER_ACCOUNT

CREATE TABLE TOPUP.TAB_TOPUP_CUSTOMER_ACCOUNT
(
GROUP_ID NUMBER(19)                          NOT NULL,
VALUE     NUMBER
)
TABLESPACE ORDER_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

3.新增实时触发器：TOPUP.TRG_TAB_BASE_MONEYLIST

CREATE OR REPLACE TRIGGER TOPUP.TRG_TAB_BASE_MONEYLIST
AFTER INSERT OR UPDATE OR DELETE
ON TOPUP.TAB_BASE_MONEYLIST REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
--VAR_GROUP_ID NUMBER;
--VAR_MONEY    NUMBER;
VAR_IS_EXIST NUMBER;
BEGIN
   CASE
   WHEN INSERTING THEN
   --VAR_GROUP_ID=:NEW.GROUP_ID;
   --VAR_MONEY=:NEW.MONEY;
   SELECT COUNT(GROUP_ID) INTO VAR_IS_EXIST FROM TOPUP.TAB_TOPUP_CUSTOMER_ACCOUNT WHERE GROUP_ID=:NEW.GROUP_ID;
   IF VAR_IS_EXIST > 0 THEN
   UPDATE TOPUP.TAB_TOPUP_CUSTOMER_ACCOUNT SET VALUE=VALUE+:NEW.MONEY WHERE GROUP_ID=:NEW.GROUP_ID;
   ELSE
   INSERT INTO TOPUP.TAB_TOPUP_CUSTOMER_ACCOUNT VALUES(:NEW.GROUP_ID,:NEW.MONEY);
   END IF;
   WHEN UPDATING THEN
   UPDATE TOPUP.TAB_TOPUP_CUSTOMER_ACCOUNT SET VALUE=VALUE+:NEW.MONEY WHERE GROUP_ID=:NEW.GROUP_ID;
   WHEN DELETING THEN
   UPDATE TOPUP.TAB_TOPUP_CUSTOMER_ACCOUNT SET VALUE=VALUE-:OLD.MONEY WHERE GROUP_ID=:NEW.GROUP_ID;
   END CASE;
   EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line('PLEASE OPERATION AGAIN,THANKS!');
       RAISE;
END TRG_TAB_BASE_MONEYLIST;
/

ITPub博客

统计商户账户上有多少余额的优化

最新文章