如何优化含重复子查询与多分组的MySQL查询

我用两个完全相同的子查询（a和b）生成子查询n，再对n做聚合得到最终结果，但查询响应时间太长。试过合并a与b、a与n都没成功，求优化思路。

原查询SQL：

select n.businessLiaision,
       n.channel,
       n.name,
       n.dt,
       n.ifNew,
       count(n.productRowId),
       sum(n.totalQty),
       sum(n.totalAmount)
from ( select a.productRowId,
              a.name,
              a.rowId,
              a.dt,
              a.businessLiaision,
              a.channel,
              a.ct,
              a.totalQty,
              a.totalAmount,
              case when a.ct = sum(b.ct) then 'true' else 'false' end as 'ifNew'
       from  ( select d.productRowId,
                      p.name,
                      DATE_FORMAT(o.effectiveTime, '%m/%Y') as 'dt',
                      p.rowId,
                      p.businessLiaision,
                      p.channel,
                      count(*) as 'ct',
                      sum(d.qty) as 'totalQty',
                      sum(d.amountPostDiscount) as 'totalAmount'
               from transactionParty as p 
               join transactionOrderHist as o on p.rowId = o.transactionPartyRowId
               join transactionOrderDetailHist as d on o.rowId = d.orderRowId
               where o.businessType = 'sales'
               group by d.productRowId, p.name, DATE_FORMAT(o.effectiveTime, '%m/%Y'), p.rowId, p.businessLiaision, p.channel
               ) as a
       left join ( select d.productRowId,
                          p.name,
                          DATE_FORMAT(o.effectiveTime, '%m/%Y') as 'dt',
                          count(*) as 'ct'
                   from transactionParty as p 
                   join transactionOrderHist as o on p.rowId = o.transactionPartyRowId
                   join transactionOrderDetailHist as d on o.rowId = d.orderRowId
                   where o.businessType = 'sales'
                   group by d.productRowId, p.name, DATE_FORMAT(o.effectiveTime, '%m/%Y')
                  ) as b on b.productRowId = a.productRowId and b.name = a.name and b.dt <= a.dt
        group by  a.productRowId, a.name, a.rowId, a.dt, a.ct, a.businessLiaision, a.channel, a.ct, a.totalQty, a.totalAmount
       ) as n
group by n.businessLiaision, n.channel, n.name, n.dt,  n.ifNew

执行计划显示查询存在多次重复表扫描与聚合操作，性能瓶颈集中在子查询a和b的重复计算，以及关联阶段的全量匹配。

transactionParty #

CREATE TABLE `transactionParty` ( 
`rowId` varchar(50) NOT NULL, 
`name` varchar(100) DEFAULT NULL, 
`code` varchar(20) DEFAULT NULL,
`businessLiaision` varchar(20) DEFAULT NULL, 
`type` varchar(20) DEFAULT NULL, 
`contractualType` varchar(20) DEFAULT NULL, 
`paymentMethod` varchar(20) DEFAULT NULL, 
`partyGroup` varchar(20) DEFAULT NULL, 
`channel` varchar(20) DEFAULT NULL, 
`costCenter` varchar(20) DEFAULT NULL, 
`warehouseRowId` varchar(100) DEFAULT NULL, 
`taxOption` varchar(20) DEFAULT NULL, 
PRIMARY KEY (`rowId`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

transactionOrderHist #

CREATE TABLE `transactionOrderHist` (
 `rowId` varchar(50) NOT NULL COMMENT '明道记录ID', 
 `orderId` varchar(50) DEFAULT NULL COMMENT '单据ID', 
 `orderCreationTime` datetime DEFAULT NULL COMMENT '单据创建时间', 
 `orderCreator` varchar(20) DEFAULT NULL COMMENT '单据创建人', 
 `businessType` varchar(20) DEFAULT NULL COMMENT '业务类型', 
 `businessLiaision` varchar(20) DEFAULT NULL COMMENT '内部业务负责人，如业务员/采购/文员', 
 `transactionPartyRowId` varchar(50) DEFAULT NULL COMMENT '往来单位', 
 `outboundWarehouse` varchar(50) DEFAULT NULL COMMENT '发货仓', 
 `inboundWarehouse` varchar(50) DEFAULT NULL COMMENT '收货仓', 
 `outboundWarehouseType` varchar(50) DEFAULT NULL, 
 `inboundWarehouseType` varchar(50) DEFAULT NULL, 
 `effectiveTime` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '单据过账时间', 
 `orderEffectuater` varchar(20) DEFAULT NULL COMMENT '单据过账人', 
 `remark` varchar(200) DEFAULT NULL, 
 `productCount` int(10) DEFAULT NULL, 
 `totalUnitCount` int(10) DEFAULT NULL, 
 `totalCostAmount` decimal(10,0) DEFAULT NULL, 
 `totalPostDiscountAmount` decimal(10,0) DEFAULT NULL, 
 `paymentStatus` varchar(50) DEFAULT NULL, 
 `paymentDate` datetime DEFAULT NULL, 
 `outboundWarehouseRowId` varchar(50) DEFAULT NULL, 
 `inboundWarehouseRowId` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`rowId`) USING BTREE, 
 KEY `orderEffectiveDate` (`effectiveTime`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

transactionOrderDetailHist #

CREATE TABLE `transactionOrderDetailHist` ( 
 `rowId` varchar(50) NOT NULL, 
 `orderRowId` varchar(50) DEFAULT NULL, 
 `productRowId` varchar(50) DEFAULT NULL, 
 `qty` int(20) DEFAULT NULL, 
 `price` decimal(20,2) DEFAULT NULL, 
 `cost` decimal(20,2) DEFAULT NULL, 
 `amount` decimal(20,2) DEFAULT NULL, 
 `amountPostDiscount` decimal(20,2) DEFAULT NULL, 
 `type` varchar(50) DEFAULT NULL, 
 `effectiveTime` datetime DEFAULT NULL, 
 `costAmount` decimal(20,2) DEFAULT NULL, 
 PRIMARY KEY (`rowId`) USING BTREE 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. 消除重复子查询，复用基础聚合结果 #

子查询a和b数据源完全一致，仅字段数量不同，可先计算一次基础聚合结果，再复用该结果完成后续逻辑，避免重复的表连接与计算：

WITH base_agg AS (
    SELECT 
        d.productRowId,
        p.name,
        DATE_FORMAT(o.effectiveTime, '%m/%Y') AS dt,
        p.rowId,
        p.businessLiaision,
        p.channel,
        COUNT(*) AS ct,
        SUM(d.qty) AS totalQty,
        SUM(d.amountPostDiscount) AS totalAmount
    FROM transactionParty AS p 
    JOIN transactionOrderHist AS o ON p.rowId = o.transactionPartyRowId
    JOIN transactionOrderDetailHist AS d ON o.rowId = d.orderRowId
    WHERE o.businessType = 'sales'
    GROUP BY d.productRowId, p.name, DATE_FORMAT(o.effectiveTime, '%m/%Y'), p.rowId, p.businessLiaision, p.channel
)
SELECT 
    n.businessLiaision,
    n.channel,
    n.name,
    n.dt,
    n.ifNew,
    COUNT(n.productRowId),
    SUM(n.totalQty),
    SUM(n.totalAmount)
FROM (
    SELECT 
        ba.productRowId,
        ba.name,
        ba.rowId,
        ba.dt,
        ba.businessLiaision,
        ba.channel,
        ba.ct,
        ba.totalQty,
        ba.totalAmount,
        CASE WHEN ba.ct = SUM(ba_sub.ct) THEN 'true' ELSE 'false' END AS ifNew
    FROM base_agg ba
    LEFT JOIN base_agg ba_sub 
        ON ba_sub.productRowId = ba.productRowId 
        AND ba_sub.name = ba.name 
        AND ba_sub.dt <= ba.dt
    GROUP BY ba.productRowId, ba.name, ba.rowId, ba.dt, ba.ct, ba.businessLiaision, ba.channel, ba.totalQty, ba.totalAmount
) n
GROUP BY n.businessLiaision, n.channel, n.name, n.dt, n.ifNew;

2. 用窗口函数替代关联聚合，减少层级 #

原逻辑中ifNew的计算可以通过窗口函数实现，避免左连接+分组的开销，进一步简化查询结构：

WITH base_agg AS (
    SELECT 
        d.productRowId,
        p.name,
        DATE_FORMAT(o.effectiveTime, '%m/%Y') AS dt,
        p.rowId,
        p.businessLiaision,
        p.channel,
        COUNT(*) AS ct,
        SUM(d.qty) AS totalQty,
        SUM(d.amountPostDiscount) AS totalAmount
    FROM transactionParty AS p 
    JOIN transactionOrderHist AS o ON p.rowId = o.transactionPartyRowId
    JOIN transactionOrderDetailHist AS d ON o.rowId = d.orderRowId
    WHERE o.businessType = 'sales'
    GROUP BY d.productRowId, p.name, DATE_FORMAT(o.effectiveTime, '%m/%Y'), p.rowId, p.businessLiaision, p.channel
),
calc_ifnew AS (
    SELECT 
        ba.*,
        CASE WHEN ba.ct = SUM(ba.ct) OVER (PARTITION BY ba.productRowId, ba.name ORDER BY ba.dt) THEN 'true' ELSE 'false' END AS ifNew
    FROM base_agg ba
)
SELECT 
    businessLiaision,
    channel,
    name,
    dt,
    ifNew,
    COUNT(productRowId),
    SUM(totalQty),
    SUM(totalAmount)
FROM calc_ifnew
GROUP BY businessLiaision, channel, name, dt, ifNew;

3. 添加针对性索引，减少扫描范围 #

当前表索引覆盖不足，建议添加以下联合索引：

• 给transactionOrderHist添加：(businessType, transactionPartyRowId, effectiveTime)，覆盖过滤条件、关联字段和日期字段
• 给transactionOrderDetailHist添加：(orderRowId, productRowId)，加速与订单表的关联及后续分组
• 给transactionParty添加覆盖索引：(rowId, name, businessLiaision, channel)，避免回表查询

4. 优化日期函数对索引的影响 #

DATE_FORMAT(o.effectiveTime, '%m/%Y')会导致effectiveTime的索引失效，可新增计算列并建立索引：

ALTER TABLE transactionOrderHist ADD COLUMN effective_month_year VARCHAR(7) AS (DATE_FORMAT(effectiveTime, '%m/%Y')) STORED;
CREATE INDEX idx_effective_month ON transactionOrderHist(effective_month_year);

之后查询中直接使用effective_month_year替代日期函数调用，可利用索引加速分组和关联。

内容的提问来源于stack exchange，提问作者jackxu