mysql数据库sql语句优化

昨天帮同事优化了一个sql语句发出来共勉下:

SELECT
T.*,
(
SELECT
S.codeName
FROM
sys_codelist S
WHERE
S.codeValue = T.packagingtype
AND S.kindCode = 'PACKAGING'
) AS packagingtypeName,
S.codeName AS codename,
(
SELECT
picpath
FROM
zl_b_gd_pic
WHERE
1 = 1
AND gdid = T.gdid
LIMIT 0,
1
) AS gdmainimagepath
FROM
zl_b_gd T
LEFT JOIN sys_codelist S ON S.codeValue = T.levelcode
WHERE
1 = 1
AND T.gdmode <> '3'
AND TO_DAYS(T.invaliddate) > TO_DAYS(NOW())
AND T.gdstatus = '2'
AND gdtype = '2'
ORDER BY
STR_TO_DATE(
gdpublishdate,
'%Y-%m-%d %H:%i:%s'
) DESC

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

主表1600条数据，codelist表4000+数据，当前查询时间为2.33s.

 

SELECT
T.*,
PG.CODENAME AS packagingtypeName,
S.codeName AS codename,
(
SELECT
picpath
FROM
zl_b_gd_pic
WHERE
1 = 1
AND gdid = T.gdid
LIMIT 0,
1
) AS gdmainimagepath
FROM
zl_b_gd T
LEFT JOIN (SELECT * FROM sys_codelist WHERE KINDCODE='CORN_LEVEL') S ON T.levelcode = S.codeValue
LEFT JOIN (SELECT * FROM sys_codelist WHERE kindCode = 'PACKAGING') PG ON T.packagingtype = PG.codeValue
WHERE
1 = 1
AND T.gdmode <> '3'
AND TO_DAYS(T.invaliddate) > TO_DAYS(NOW())
AND T.gdstatus = '2'
AND gdtype = '2'
ORDER BY
STR_TO_DATE(
gdpublishdate,
'%Y-%m-%d %H:%i:%s'
) DESC

 

 

 

 

 

 

 

 

 

 

 

 

 

子查询换成了关联查询，查询时间为0.54s.

 

 

执行计划如下：

执行计划部分我说的不是很清晰，有可以描述清晰朋友可以帮忙解释下.