针对千万级detail表的SQL查询性能优化需求咨询

原查询语句 #

SELECT 
    d.USPS_IMB AS IMb,
    d.CorrelationId,
    d.EINNo AS EIN,
    d.UniqueFormId AS FormId,
    d.ProductionFacility AS Facility,
    CASE 
        WHEN d.ProductionStatusNo < 5 THEN 'Received'
        WHEN d.ProductionStatusNo = 5 THEN 'Printed'
        WHEN d.ProductionStatusNo = 6 THEN 'Folded / Sealed'
        WHEN d.ProductionStatusNo = 7 THEN 'Mailed'
        ELSE 'Unknown'
    END AS Status,
    d.StatusDateTime AS StatusDateUTC
FROM detail d
JOIN (
    SELECT 
        UniqueFormId,
        MAX(ProductionStatusNo) AS MaxProductionStatusNo
    FROM detail
    WHERE ProductionFacility='MAIL'
    GROUP BY UniqueFormId
) AS maxStatus
ON d.UniqueFormId = maxStatus.UniqueFormId AND d.ProductionStatusNo = maxStatus.MaxProductionStatusNo
WHERE d.ProductionFacility='MAIL';

当前表结构（关键部分） #

CREATE TABLE `detail` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `EINNo` varchar(45) NOT NULL,
  `ProductionStatusNo` int NOT NULL,
  `StatusDateTime` datetime DEFAULT NULL,
  `UniqueFormId` varchar(450) NOT NULL,
  `ProductionFacility` varchar(450) NOT NULL,
  `USPS_IMB` varchar(45) DEFAULT NULL,
  -- 其他字段省略
  PRIMARY KEY (`Id`,`ProductionStatusNo`),
  -- 现有单列索引省略
  /*!50100 PARTITION BY RANGE (`ProductionStatusNo`)
  -- 分区定义省略
  */;

具体优化方案 #

1. 给子查询创建复合覆盖索引

子查询的逻辑是先过滤ProductionFacility='MAIL'，再按UniqueFormId分组取最大的ProductionStatusNo，现有单列索引无法高效支撑这个逻辑。创建以下复合索引后，子查询可以直接从索引获取数据，无需回表：

CREATE INDEX idx_prod_facility_uniqueform_statusno ON detail (ProductionFacility, UniqueFormId, ProductionStatusNo);

索引顺序按过滤条件→分组字段→聚合字段排列，完美匹配子查询的执行路径。

2. 为主查询创建覆盖索引

主查询需要获取多个业务字段，创建包含所有查询字段的复合覆盖索引，避免回表查询主键索引：

CREATE INDEX idx_detail_covering_query ON detail (ProductionFacility, UniqueFormId, ProductionStatusNo, USPS_IMB, CorrelationId, EINNo, StatusDateTime);

注意：如果CorrelationId是表中实际存在的字段（原表结构未列出，假设存在）必须包含；如果是笔误，记得从索引中移除。

3. 用窗口函数替换JOIN子查询，减少表扫描

原查询通过子查询+JOIN的方式获取每个表单的最新状态，改用ROW_NUMBER()窗口函数可以一次性完成逻辑，避免两次全表扫描：

SELECT 
    USPS_IMB AS IMb,
    CorrelationId,
    EINNo AS EIN,
    UniqueFormId AS FormId,
    ProductionFacility AS Facility,
    CASE 
        WHEN ProductionStatusNo < 5 THEN 'Received'
        WHEN ProductionStatusNo = 5 THEN 'Printed'
        WHEN ProductionStatusNo = 6 THEN 'Folded / Sealed'
        WHEN ProductionStatusNo = 7 THEN 'Mailed'
        ELSE 'Unknown'
    END AS Status,
    StatusDateTime AS StatusDateUTC
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY UniqueFormId ORDER BY ProductionStatusNo DESC) AS rn
    FROM detail
    WHERE ProductionFacility='MAIL'
) t
WHERE rn = 1;

这个写法直接为每个UniqueFormId标记最新状态的行，过滤rn=1即可得到目标数据，逻辑更简洁，性能更优。

4. 评估现有分区的有效性

当前表按ProductionStatusNo做范围分区，但查询过滤条件是ProductionFacility='MAIL'。如果MAIL对应的状态码集中在少数几个分区，分区可以保留；如果状态码分布分散，分区无法有效减少扫描范围。若MySQL版本在8.0.16以上，可考虑改用RANGE COLUMNS复合分区，结合ProductionFacility和ProductionStatusNo进一步缩小扫描范围。

5. 清理冗余单列索引

创建上述复合索引后，idx_detail_ProductionFacility、idx_detail_UniqueFormId、idx_detail_ProductionStatusNo这些单列索引可以删除，避免额外的索引维护开销，同时减少优化器的选择负担。

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