MySQL大表日累计计数报表查询性能优化求助

问题背景 #

本地环境detail表存200万条记录时，执行指定存储过程生成累计计数报表耗时约2分钟；生产环境该表日数据量达8000万条，性能瓶颈亟待解决。已为StatusDateTime、ProductionFacility、ProductionStatusNo字段创建索引，且按ProductionStatusNo（取值0至12）对表进行分区。

现有存储过程代码 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `a_dashboard_count`(
IN p_StatusDate Date, 
IN p_UnassignedProductionFaciltiy nvarchar(500)
)
BEGIN

     DECLARE PD_S_C INT;
     SELECT COALESCE(cumulative_count, 0) INTO PD_S_C  FROM (   
     SELECT 0 AS dummy_value 
        ) dummy
LEFT JOIN dailysummary ON  ProductionStatusNo = 1     AND StatusDateTime = DATE_SUB(p_StatusDate, INTERVAL 1 DAY);
    

INSERT INTO dailysummary(ProductionStatus ,ProductionStatusNo,StatusDateTime ,count , cumulative_count ) 

SELECT 'Unassigned' AS ProductionStatus,
        0 AS ProductionStatusNo, 
        p_StatusDate AS StatusDate,
       COUNT(DISTINCT d.UniqueFormId) as DayCount,
       COUNT(DISTINCT d.UniqueFormId) as CumulativeCount
FROM detail d
LEFT JOIN (
    SELECT DISTINCT UniqueFormId
    FROM detail exd
    WHERE exd.StatusDate <= p_StatusDate AND exd.ProductionStatusNo != 1
) exd ON d.UniqueFormId = exd.UniqueFormId
WHERE d.ProductionFacility = p_UnassignedProductionFaciltiy
  AND d.StatusDate <= p_StatusDate
  AND exd.UniqueFormId IS NULL
  
union all

 SELECT
    ps.Status,
    ps.Id,
    p_StatusDate,
    COALESCE(totalcount, 0) AS count,
    COALESCE(totalcount, 0) + PD_S_C AS cumulative_count
FROM
    productionstatus AS ps
LEFT JOIN (
    SELECT
        COUNT(DISTINCT d.UniqueFormId) AS totalcount,
        p_StatusDate AS StatusDate,
        d.ProductionStatusNo
    FROM
        detail AS d
    LEFT JOIN
        detail AS exd ON d.UniqueFormId = exd.UniqueFormId
                       AND exd.StatusDate < p_StatusDate
                       AND exd.ProductionStatusNo = d.ProductionStatusNo
    WHERE
        d.StatusDate = p_StatusDate
        AND exd.UniqueFormId IS NULL
    GROUP BY
        d.ProductionStatusNo
) AS d ON ps.Id = d.ProductionStatusNo
WHERE
    ps.Id = 1



        UNION all

        SELECT     ps.Status AS ProductionStatus,    
        ps.Id AS ProductionStatusNo,  
        p_StatusDate AS StatusDate,   
        COALESCE(c, 0)  AS  DayCount,  
        COALESCE(c, 0) AS CumulativeCount 
        FROM  productionstatus as ps
        LEFT JOIN (    SELECT         COUNT(*) as c,        ed.psn 
        FROM (
        SELECT
            UniqueFormId,MAX(productionStatusNo) as psn  FROM   detail
        WHERE   StatusDate <= p_StatusDate
        GROUP BY UniqueFormId
            ) as ed
        GROUP BY ed.psn
        ) as l ON ps.Id = l.psn
        WHERE
            ps.Id not in ( 0,1);
END

detail表结构及索引 #

CREATE TABLE `detail` (
  `Id` int NOT NULL AUTO_INCREMENT,
  
 
  `EINNo` varchar(45) NOT NULL,
  `EmployeeNo` varchar(45) NOT NULL,
  `Form` varchar(45) NOT NULL,
  
  `ProductionStatusNo` int NOT NULL,
  
  
  `UniqueFormId` varchar(450) NOT NULL,
  `ProductionFacility` varchar(450) NOT NULL,
  
  `StatusDate` date DEFAULT NULL,
  PRIMARY KEY (`Id`,`ProductionStatusNo`),
  KEY `idx_detail_EINNo` (`EINNo`),
  KEY `idx_detail_EmployeeNo` (`EmployeeNo`),
  KEY `idx_detail_Form` (`Form`),
  KEY `idx_detail_UniqueFormId` (`UniqueFormId`),
  KEY `idx_detail_ProductionFacility` (`ProductionFacility`),
  KEY `idx_detail_ProductionStatusNo` (`ProductionStatusNo`),
  KEY `idx_detail_StatusDate` (`StatusDate`)
) ENGINE=InnoDB AUTO_INCREMENT=11652798 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`ProductionStatusNo`)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

性能优化方案 #

1. 重构查询逻辑，避免全表扫描与重复计算 #

• 预计算每日快照：每日凌晨定时计算当日各状态的统计数据并写入dailysummary表，报表查询直接读取快照数据，彻底避免实时全表聚合计算。
• 替换子查询为NOT EXISTS：原LEFT JOIN + IS NULL的关联方式可改为NOT EXISTS，减少不必要的数据关联开销，例如第一个UNION分支可重构为：

  SELECT 'Unassigned' AS ProductionStatus,
         0 AS ProductionStatusNo, 
         p_StatusDate AS StatusDate,
         COUNT(DISTINCT d.UniqueFormId) as DayCount,
         COUNT(DISTINCT d.UniqueFormId) as CumulativeCount
  FROM detail d
  WHERE d.ProductionFacility = p_UnassignedProductionFaciltiy
    AND d.StatusDate <= p_StatusDate
    AND NOT EXISTS (
        SELECT 1
        FROM detail exd
        WHERE exd.UniqueFormId = d.UniqueFormId
          AND exd.StatusDate <= p_StatusDate 
          AND exd.ProductionStatusNo != 1
    )
  

2. 优化索引设计，覆盖查询全链路 #

现有单字段索引无法覆盖复合查询场景，建议创建以下复合索引：

• 针对第一个UNION分支：idx_facility_statusdate_uniqueformid (ProductionFacility, StatusDate, UniqueFormId, ProductionStatusNo)，直接覆盖WHERE条件与查询字段，避免回表。
• 针对第二个UNION分支：idx_statusdate_productionstatus_uniqueformid (StatusDate, ProductionStatusNo, UniqueFormId)，覆盖日期筛选、状态匹配及关联字段。
• 针对第三个UNION分支：idx_statusdate_uniqueformid_productionstatus (StatusDate, UniqueFormId, ProductionStatusNo)，支持按UniqueFormId分组取最大状态值的操作。

3. 调整分区策略，适配时间维度查询 #

当前按ProductionStatusNo分区无法利用分区裁剪减少时间范围查询的数据量，建议：

• 改为按StatusDate范围分区（按天或按月），查询指定日期时直接命中对应分区，大幅缩减扫描数据量；若需保留状态维度分区，可采用复合分区（先按时间分区，再按状态子分区）。

4. 优化存储过程执行逻辑 #

• 复用中间结果：将多次查询的公共结果存入临时表，例如第三个UNION分支中MAX(productionStatusNo)的计算结果，可存入临时表供后续统计复用，避免重复扫描全表。
• 简化聚合计算：若UniqueFormId在detail表中唯一（或分组时无重复），可将COUNT(DISTINCT)改为COUNT(*)，减少计算开销。

5. 数据库配置调优 #

• 增大innodb_buffer_pool_size（建议设置为服务器内存的70%-80%），让更多热数据缓存在内存中，降低磁盘IO频率。
• 针对大表关联与排序操作，适当调大sort_buffer_size和join_buffer_size，避免使用磁盘临时表。

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