昨天帮同事优化了一个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 |
执行计划如下：
执行计划部分我说的不是很清晰，有可以描述清晰朋友可以帮忙解释下.
