我有 2 个表 EMP 和EMP_DTL。我正在使用左连接,如下所示。
SELECT EMPDEL,
EMPYEAR,
EXCODE,
CONCAT(COALESCE(TCODE, ''), COALESCE(INTCODE, '')) AS INTCODE,
( SELECT GROUP_CONCAT(ECODE SEPARATOR ' ') FROM EMP_DTL
WHERE EMP_DTL.DTL_EID = EMP.EID AND ETYPE='') AS ECODE,
COUNT(SSN) AS SSN,
MIN(ESRP) AS ESRP,
GROUP_CONCAT(DISTINCT CURCODE SEPARATOR ' ') AS CURCODE
FROM EMP
WHERE EAND='TOY'
AND EMPYEAR IN ('2014', '2015')
AND EGORY IN('G')
AND (EHIND = 'N' OR EHIND = 'FALSE')
AND EMP.SSN IS NOT NULL
AND ESRP > 0
GROUP BY EMPDEL, EMPYEAR, EXCODE, INTCODE, ECODE
而表结构是这样的,
CREATE TABLE `emp` (
`EID` varchar(45) NOT NULL,
`ETYPE` varchar(1) NOT NULL,
`SSN` varchar(20) DEFAULT NULL,
`EMPDEL` varchar(4) DEFAULT NULL,
`EMPYEAR` varchar(4) DEFAULT NULL,
`ESCODE` varchar(2) DEFAULT NULL,
`EIAL` varchar(45) DEFAULT NULL,
`EAND` varchar(20) DEFAULT NULL,
`ETUS` char(2) DEFAULT NULL,
`ESRP` varchar(20) DEFAULT NULL,
`EHIND` varchar(20) DEFAULT NULL,
`EGORY` char(1) DEFAULT NULL,
`ECODE` varchar(200) DEFAULT NULL,
`EXCODE` varchar(10) DEFAULT NULL,
`TCODE` varchar(5) DEFAULT NULL,
`INTCODE` varchar(20) DEFAULT NULL,
`CURCODE` varchar(10) DEFAULT NULL,
`EAREA` varchar(5) DEFAULT NULL,
PRIMARY KEY (`EID`),
UNIQUE KEY `EID` (`EID`),
KEY `SSN_IDX` (`SSN`),
KEY `EMPDEL_IDX` (`EMPDEL`),
KEY `EMPYEAR_IDX` (`EMPYEAR`),
KEY `EAREA_IDX` (`EAREA`),
KEY `ETUS_IDX` (`ETUS`),
KEY `EAND_IDX` (`EAND`),
KEY `ESRP_IDX` (`ESRP`),
KEY `EHIND_IDX` (`EHIND`),
KEY `EGORY_IDX` (`EGORY`)
)
CREATE TABLE `emp_dtl` (
`DTL_EID` varchar(45) NOT NULL,
`SSN` varchar(20) DEFAULT NULL,
`ECODE` varchar(45) NOT NULL,
`ETYPE` varchar(45) DEFAULT NULL,
PRIMARY KEY (`DTL_EID`,`ECODE`),
KEY `EMP_DTL_IDX` (`DTL_EID`),
KEY `ETYPE_IDX` (`ETYPE`)
)
问题是查询在 30 秒左右执行 10000 条记录。
我尝试在上面的查询中添加这些东西以提高性能,
1) 新增"WHERE"条款
2) 在两个表上为 WHERE 子句中的 id 和列添加了适当的索引。
3) 执行执行计划以确保我的索引被使用。这是执行计划的输出。
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
=====================================================================================================================================================================================
'1', 'PRIMARY', 'EMP', 'ref', 'SSN_IDX,EMPYEAR_IDX,EAND_IDX,ESRP_IDX,EHIND_IDX, EGORY_IDX', 'EGORY_IDX', '2', 'const', '13', 'Using where; Using filesort'
'2', 'DEPENDENT SUBQUERY', 'EMP_DTL', 'ref', 'PRIMARY,EMP_DTL_IDX,ETYPE_IDX', 'ETYPE_IDX', '48', 'const', '1', 'Using where; Using index'
我应该再添加索引吗?有人可以帮助我进一步优化此查询以减少执行时间吗?
问题是您实际上并没有加入,而是在EMP_DTL中开始对 EMP 的每条记录进行单独的查找。 试试这个:
SELECT EMP.EMPDEL,
EMP.EMPYEAR,
EMP.EXCODE,
CONCAT(COALESCE(TCODE, ''), COALESCE(INTCODE, '')) AS INTCODE,
GROUP_CONCAT(EMP_DTL.ECODE SEPARATOR ' ') AS ECODE,
EMP.COUNT(SSN) AS SSN,
MIN(EMP.ESRP) AS ESRP,
EMP.GROUP_CONCAT(DISTINCT CURCODE SEPARATOR ' ') AS CURCODE
FROM EMP
LEFT JOIN EMP_DTL ON EMP_DTL.DTL_EID = EMP.EID AND EMP_DTL.ETYPE=''
WHERE EAND='TOY'
AND EMPYEAR IN ('2014', '2015')
AND EGORY IN('G')
AND (EHIND = 'N' OR EHIND = 'FALSE')
AND EMP.SSN IS NOT NULL
AND ESRP > 0
GROUP BY EMPDEL, EMPYEAR, EXCODE, INTCODE, ECODE
除了 Dirk 的左联接产品之外,您还有不同字段上的单独索引,这不会像多键索引那样真正有用。 尝试在表上放置一个索引,以涵盖 WHERE 子句的主要元素和组的元素...但是拥有这么多列可能太多且不切实际。 仅基于 WHERE 子句键设置的结果可能足以提高性能。
在... ( EGORY, EAND, EMPYEAR, EHIND, SSN, ESRP )