有人能帮我优化where子句吗

Question

我在下面用索引查询了它，但还没有优化。我不知道问题出在哪里。有人能帮我吗？

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

第3行mfd表我使用了idx_CRD_FIT_DATE_STATUS的力索引，但仍然需要时间才能得到相同的结果。

表格结构；

第1行：

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

第2行：

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`)

第3行：

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

以上索引均基于基数

njdnsfkndkjfbndksjbfkjdbsjkfbkjsdbfkjbdskjfbsdkjbfkjdsbjkfbkjdbfkj

Answer 1

您需要停止使用"旧式联接"；。这种风格已经30多年没有流行了，正在妨碍你。如果我重新编写您的查询以使用现代联接，它看起来像这样：

SELECT  
date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, 
count(*) cnt_fitment 
FROM MMHSRP_FITMENT_DATE mfd
JOIN CUSTOMER_REGISTRATION_DETAILS crd ON mfd.MFD_CRD_ID = crd.CRD_ID
JOIN EMBOSSING_STATION_MAPPING_DETAILS esmd ON esmd.ESMD_SDM_ID = crd.CRD_SDM_ID 
WHERE 
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' 

现在我们可以看到链接表上有两个过滤器——我们可以将这些过滤器移动到联接语句中(这是老式联接无法做到的(，如下所示：

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

这应该更快。

stackoverflow自己的Arron Bernard加入了更多关于老式的讨论https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

---

注释中的子查询版本：

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