mysql - 子查询耗时过长，需要优化

Question

SELECT PROJECTKEY, CONCAT(FIRSTNAME,' ',LASTNAME) as USRCREATED,
                              CONCAT('/userImages/',md.USERKEY,'/',md.IMAGE) as USRCREATED_PROFILE_IMG,mm.DTCREATED
                              FROM kbedumoment mm
                              left join users md on mm.USRCREATED=md.USERKEY

where mm.id in
(

SELECT mm.id from kbedumoment mm
left join kbedumomentpostto pt on pt.MOMENT_ID=mm.id
where mm.deleted_at is NULL
   && pt.childkey=1005

union all

SELECT id from kbedumoment mm
where POST_TO='school' && mm.deleted_at is NULL && mm.PROJECTKEY =2

)                              


order by mm.id desc

上面的查询需要 6.875 秒，这花费的时间太长了。

我尝试单独执行子查询。

SELECT mm.id from kbedumoment mm
left join kbedumomentpostto pt on pt.MOMENT_ID=mm.id
where mm.deleted_at is NULL
   && pt.childkey=1005

union

SELECT id from kbedumoment mm
where POST_TO='school' && mm.deleted_at is NULL && mm.PROJECTKEY =2

结果:

+-------+
| id    |
+-------+
|  253  |
|  1264 |
|  1    |
|  238  |
+-------+

持续时间:0.109 秒。所以子查询没问题。

然后我执行以下测试，用我已经知道的 ID 替换子查询。

SELECT PROJECTKEY, CONCAT(FIRSTNAME,' ',LASTNAME) as USRCREATED,
                              CONCAT('/userImages/',md.USERKEY,'/',md.IMAGE) as USRCREATED_PROFILE_IMG,mm.DTCREATED
                              FROM kbedumoment mm
                              left join users md on mm.USRCREATED=md.USERKEY

where mm.id in
(
1264,253,238,1
)                              


order by mm.id desc

持续时间:0.016 秒解释

为什么会这样？

Answer 1

你可以试试这样的:

SELECT PROJECTKEY,
    CONCAT(FIRSTNAME, ' ', LASTNAME) AS USRCREATED,
    CONCAT('/userImages/', md.USERKEY, '/', md.IMAGE) AS USRCREATED_PROFILE_IMG,
    mm.DTCREATED
FROM kbedumoment mm
    INNER JOIN (
        SELECT mm.id
        FROM kbedumoment mm
            LEFT JOIN kbedumomentpostto pt ON pt.MOMENT_ID = mm.id
        WHERE mm.deleted_at IS NULL
            AND pt.childkey = 1005 
        UNION 
        SELECT id
        FROM kbedumoment mm
        WHERE POST_TO = 'school'
            AND mm.deleted_at IS NULL
            AND mm.PROJECTKEY = 2
    ) temp ON temp.id = mm.id
    LEFT JOIN users md ON mm.USRCREATED = md.USERKEY
ORDER BY mm.id DESC;

我在这里使用的是使用具有 && 的预先存在的子查询并将其替换为 AND 然后将其用作 INNER JOIN< 的生成表 因为这两个记录必须存在于 mm 和 temp 之间。

我还增强了格式，以便您可以更好地阅读查询。干杯