sql - 带有“分组依据”的累积查询导致速度缓慢

Question

我们有一个表，它将存储用户的信息，如执行的交易、日期等。同一用户可以多次执行相同的交易。现在，如果交易编号相同，我们要显示用户完全执行的累计金额

使用 GROUP BY 导致缓慢查询以显示加入同一个表的累积总和。表有 270 万条记录。这适用于正确的索引扫描（索引结构）。

INDEX `IDX_GRPBY2` (`DEAL`, `FIN`),
INDEX `Table1_TEMP` (`CREATE1`, `FEVSTATUS`, `EVE`)

下面是预期的结果

预期结果

以下是实际查询，如果它是相同的交易，则用于获取累积总和的交易：

SELECT  MAX(A.PKEY) PKEY,
        A.ENT, A.DEAL, A.FIN, A.DATE1, A.TYPE1, A.STEPNO, 
        A.CREATE1, A.EVE, A.FEVSTATUS, A.STATUSDATE, A.INTAMT,
        A.INTPAID, A.INT_TYPE, A.PENALTY_1, A.CONV_PAID_4,
        A.INT_PAID_4, A.CONVFIN_4, A.INTTYPE,
        B.DEAL AS DEAL_B, B.FIN AS FIN_B,
        SUM(B.CONV_PAID_4) AS CONV_PAID_4_OUT
    FROM  Table1 A, Table1 B
    WHERE  A.CREATE1 = '0'
      AND  (A.FEVSTATUS = '1'
              OR  A.EVE IN ('E06', 'E07', 'E02', 'E15', 'E03', 'E04')
           )
      AND  A.DEAL = B.DEAL
      AND  A.FIN = B.FIN
      AND  A.PKEY >= B.PKEY
    GROUP BY  A.ENT, A.DEAL, A.FIN, A.DATE1, A.TYPE1, A.STEPNO,
        A.CREATE1, A.EVE, A.FEVSTATUS, A.STATUSDATE, A.INTAMT,
        A.INTPAID, A.INT_TYPE, A.PENALTY_1, A.CONV_PAID_4,
        A.INT_PAID_4, A.CONVFIN_4, A.INTTYPE,
        B.DEAL, B.FIN;

我尝试更改为以下以在子查询中总结然后加入，但结果仍然需要很长时间

更改查询：

SELECT  MAX(A.PKEY) PKEY, A.ENT, A.DEAL, A.FIN, A.DATE1, A.TYPE1,
        A.STEPNO, A.CREATE1, A.EVE, A.FEVSTATUS, A.STATUSDATE,
        A.INTAMT, A.INTPAID, A.INT_TYPE, A.PENALTY_1, A.CONV_PAID_4,
        A.INT_PAID_4, A.CONVFIN_4, A.INTTYPE, A.DEAL AS DEAL_B,
        A.FIN AS FIN_B, 
    (
        SELECT  SUM(B.CONV_PAID_4)
            FROM  Table1 B
            WHERE  A.DEAL = B.DEAL
              AND  A.FIN = B.FIN
              AND  A.PKEY >= B.PKEY
    ) AS PRI_CONV_PAID_4_OUT
    FROM  Table1 A
    WHERE  A.CREATE1 = '0'
      AND  (A.FEVSTATUS = '1'
              OR  A.EVE IN ('E06', 'E07', 'E02', 'E15', 'E03', 'E04')
           )
    GROUP BY  A.ENT, A.DEAL, A.FIN, A.DATE1, A.TYPE1, A.STEPNO,
        A.CREATE1, A.EVE, A.FEVSTATUS, A.STATUSDATE, A.INTAMT,
        A.INTPAID, A.INT_TYPE, A.PENALTY_1, A.CONV_PAID_4, A.INT_PAID_4,
        A.CONVFIN_4, A.INTTYPE, B.DEAL, B.FIN;

对更快地重新构建查询有任何帮助吗？

下面显示创建表

CREATE TABLE `Table1` (
    `PKEY` DECIMAL(10,0) NOT NULL DEFAULT '0',
    `ENT` CHAR(3) NOT NULL DEFAULT '',
    `DEAL` CHAR(14) NOT NULL DEFAULT '',
    `FIN` CHAR(3) NOT NULL DEFAULT '',
    `DATE1` DATETIME NULL DEFAULT NULL,
    `TYPE1` CHAR(3) NULL DEFAULT NULL,
    `STEPNO` CHAR(3) NULL DEFAULT NULL,
    `CREATE1` CHAR(1) NULL DEFAULT NULL,
    `EVE` CHAR(3) NULL DEFAULT NULL,
    `FEVSTATUS` CHAR(1) NULL DEFAULT NULL,
    `STATUSDATE` DATETIME NULL DEFAULT NULL,
    `INTAMT` DECIMAL(19,5) NULL DEFAULT NULL,
    `INTPAID` DECIMAL(19,5) NULL DEFAULT NULL,
    `INT_TYPE` CHAR(1) NULL DEFAULT NULL,
    `PENALTY_1` DECIMAL(9,6) NULL DEFAULT NULL,
    `CONV_PAID_4` DECIMAL(15,2) NULL DEFAULT NULL,
    `CONV_PAID_4` DECIMAL(19,5) NULL DEFAULT NULL,
    `CONV_FIN_4` CHAR(3) NULL DEFAULT NULL,
    `INTTYPE` CHAR(1) NULL DEFAULT NULL,
    PRIMARY KEY (`PKEY`),
    UNIQUE INDEX `IXQDWFEV_PK` (`PKEY`),
    UNIQUE INDEX `IXIDWFEV` (`ENT`, `DEAL`, `FIN`, `DATE1`),
    INDEX `IXQDWFEV_GI1` (`ENT`, `DEAL`, `CONV_FIN_4`),
    INDEX `IXQDWFEV_GI2` (`ENT`, `DEAL`, `TYPE1`, `STEPNO`),
    INDEX `IDX_GRPBY2` (`DEAL`, `FIN`),
    INDEX `IXQDWFEV1_TEMP` (`CREATE1`, `FEVSTATUS`, `EVE`)
)
COLLATE='utf8_general_ci'
;

标签： sqlperformancegroup-bymariadb