SQL关联查询WHERE子句优化求助：索引已用但性能未达最优

现有如下SQL查询语句，已使用索引但性能仍未达到最优，恳请协助优化：

select 
  date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, 
  count(*) cnt_fitment 
FROM 
  MMHSRP_FITMENT_DATE mfd, 
  CUSTOMER_REGISTRATION_DETAILS crd, 
  EMBOSSING_STATION_MAPPING_DETAILS esmd 
where 
  mfd.MFD_CRD_ID = crd.CRD_ID 
  AND esmd.ESMD_SDM_ID = crd.CRD_SDM_ID 
  AND esmd.ESMD_ESM_ID = '9'
  AND mfd.MFD_STATUS = '0' 
  AND mfd.MFD_FITMENT_DATE >= '2022-10-07' 
  AND mfd.MFD_FITMENT_DATE <= '2022-12-06' 
  AND crd.CRD_VARIFICATION_STATUS IN (1, 2, 4) 
GROUP BY 
  mfd.MFD_FITMENT_DATE 
HAVING 
  COUNT(*) >= '5000' \G

执行计划 #

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: esmd
         type: ref
possible_keys: idx_ESMD_SDM_ID,idx_ESMD_ESM_ID
          key: idx_ESMD_ESM_ID
      key_len: 8
          ref: const
         rows: 440
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: crd
         type: ref
possible_keys: PRIMARY,idx_CRD_SDM_ID,idx_CRD_VARIFICATION_STATUS,idx_crd_sdm_id_verfication_status
          key: idx_crd_sdm_id_verfication_status
      key_len: 4
          ref: celexkeyline.esmd.ESMD_SDM_ID
         rows: 660
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: mfd
         type: ref
possible_keys: MFD_STATUS,idx_MFD_CRD_ID,idx_combo,idx_new,MFD_FITMENT_DATE,idx_CRD_FIT_DATE_STATUS
          key: MFD_STATUS
      key_len: 12
          ref: const,celexkeyline.crd.CRD_ID
         rows: 1
        Extra: Using where; Using index

已尝试对mfd表使用FORCE INDEX(idx_CRD_FIT_DATE_STATUS)，但查询耗时无明显改善。以下是各表结构及索引信息：

EMBOSSING_STATION_MAPPING_DETAILS表 #

PRIMARY KEY (`ESMD_ID`),
  KEY `idx_ESMD_SDM_ID` (`ESMD_SDM_ID`),
  KEY `idx_ESMD_ESM_ID` (`ESMD_ESM_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14006 DEFAULT CHARSET=latin1

CUSTOMER_REGISTRATION_DETAILS表 #

PRIMARY KEY (`CRD_ID`),
  KEY `CRD_APP_ID` (`CRD_APP_ID`),
  KEY `idx_CRD_CMM_ID` (`CRD_CMM_ID`),
  KEY `idx_CRD_SDM_ID` (`CRD_SDM_ID`),
  KEY `idx_CRD_ZM_ID` (`CRD_ZM_ID`),
  KEY `idx_CRD_REGN_NUMBER` (`CRD_REGN_NUMBER`),
  KEY `idx_CRD_MOBILE_NUMBER` (`CRD_MOBILE_NUMBER`),
  KEY `idx_CRD_VARIFICATION_STATUS` (`CRD_VARIFICATION_STATUS`),
  KEY `idx_CRD_CHASSIS_NO` (`CRD_CHASSIS_NO`),
  KEY `idx_CRD_REGN_NUMBER_CRD_ID` (`CRD_REGN_NUMBER`,`CRD_ID`),
  KEY `CRD_FITMENT_DATE` (`CRD_FITMENT_DATE`),
  KEY `idx_crd_sdm_id_verfication_status` (`CRD_SDM_ID`,`CRD_VARIFICATION_STATUS`),
  KEY `idx_CRD_IS_REPLACEMENT` (`CRD_IS_REPLACEMENT`)

MMHSRP_FITMENT_DATE表 #

PRIMARY KEY (`MFD_ID`),
  KEY `MFD_STATUS` (`MFD_STATUS`,`MFD_CRD_ID`,`MFD_FITMENT_DATE`),
  KEY `idx_MFD_CRD_ID` (`MFD_CRD_ID`),
  KEY `idx_combo` (`MFD_FITMENT_DATE`,`MFD_CRD_ID`,`MFD_STATUS`),
  KEY `idx_new` (`MFD_STATUS`,`MFD_FITMENT_DATE`,`MFD_CRD_ID`),
  KEY `MFD_FITMENT_DATE` (`MFD_FITMENT_DATE`),
  KEY `idx_CRD_FIT_DATE_STATUS` (`MFD_CRD_ID`,`MFD_FITMENT_DATE`,`MFD_STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=2421779 DEFAULT CHARSET=latin1

所有索引均基于基数创建。

优化方案 #

1. 调整查询逻辑与执行顺序 #

当前执行计划先从小表esmd取数再关联大表，建议优先过滤数据量最大的mfd表，减少后续关联的数据量。同时改用标准JOIN语法，逻辑更清晰：

SELECT 
    DATE(mfd.MFD_FITMENT_DATE) AS MFD_FITMENT_DATE, 
    COUNT(*) AS cnt_fitment 
FROM 
    MMHSRP_FITMENT_DATE mfd
INNER JOIN 
    CUSTOMER_REGISTRATION_DETAILS crd ON mfd.MFD_CRD_ID = crd.CRD_ID
INNER JOIN 
    EMBOSSING_STATION_MAPPING_DETAILS esmd ON esmd.ESMD_SDM_ID = crd.CRD_SDM_ID
WHERE 
    mfd.MFD_STATUS = '0' 
    AND mfd.MFD_FITMENT_DATE >= '2022-10-07' 
    AND mfd.MFD_FITMENT_DATE <= '2022-12-06' 
    AND crd.CRD_VARIFICATION_STATUS IN (1, 2, 4)
    AND esmd.ESMD_ESM_ID = '9'
GROUP BY 
    DATE(mfd.MFD_FITMENT_DATE)
HAVING 
    cnt_fitment >= 5000;

注：将COUNT(*) >= '5000'改为cnt_fitment >= 5000，避免字符串与数字的隐式转换开销。

2. 优化mfd表的索引 #

当前mfd表使用的MFD_STATUS索引无法高效支持日期范围过滤，建议创建覆盖索引，直接包含查询所需的所有字段，避免回表：

CREATE INDEX idx_mfd_status_fitdate_crdid ON MMHSRP_FITMENT_DATE(MFD_STATUS, MFD_FITMENT_DATE, MFD_CRD_ID);

该索引先通过等值条件MFD_STATUS过滤，再通过MFD_FITMENT_DATE做范围筛选，最后带上关联用的MFD_CRD_ID，完全覆盖mfd表在查询中的所有需求。

3. 消除临时表与文件排序 #

执行计划中的Using temporary; Using filesort是分组排序导致的性能瓶颈，可通过以下方式优化：

• 如果MFD_FITMENT_DATE是datetime类型，DATE()函数会导致索引失效。建议在mfd表新增一个日期类型字段MFD_FITMENT_DATE_DAY，预计算并存储日期值，然后修改分组逻辑为GROUP BY MFD_FITMENT_DATE_DAY，同时在新索引中加入该字段。

4. 更新统计信息 #

过时的统计信息可能导致优化器选择错误的执行计划，执行以下语句更新各表统计信息：

ANALYZE TABLE MMHSRP_FITMENT_DATE, CUSTOMER_REGISTRATION_DETAILS, EMBOSSING_STATION_MAPPING_DETAILS;

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