mysqli查询where not in(select)inner join

k4emjkb1 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(364)

>     SELECT 
>         SUM(m_out) AS totalOut
>     FROM
>         m_detal
>     WHERE
>         opers = '25'
>             AND (m_type = 'Out'
>             OR m_type = 'Merged')
>             AND m_date <= '2018-11-28 07:30:00'
>             AND mark_delete IS NULL
>             AND m_ids NOT IN (SELECT 
>                 m.m_ids
>             FROM
>                 (SELECT 
>                     m_ids
>                 FROM
>                     m_detal
>                 WHERE
>                     opers = '25'
>                         AND (m_type = 'Out'
>                         OR m_type = 'Merged')
>                         AND (m_onhold != 'onhold'
>                         OR m_onhold IS NULL)
>                         AND mark_delete IS NULL
>                         AND m_date <= '2018-11-28 07:30:00') AS m
>                     INNER JOIN
>                 n_combine_tbl AS t ON (t.comb_id1 = m.m_ids
>                     OR t.comb_id2 = m.m_ids)
>                     AND t.time <= '2018-11-28 07:30:00');

这个查询花了我30多秒！not in中的查询有点大，大约有7-9k个id。有没有更有效的方法？我认为内部连接部分是在检查n\u combine\u表的两列（comb\u id1或comb\u id2）时使其变慢。
有没有更有效的方法？

mysql

来源：https://stackoverflow.com/questions/53531092/mysqli-query-where-not-in-select-inner-join

1条答案

按热度按时间

5f0d552i1#

我将删除子查询中的重复条件，并执行类似操作（两个不在子查询中的条件可以替换为联合）：

SELECT
    SUM(m.m_out) AS totalOut
FROM
    m_detal AS m
WHERE
        m.opers = '25'
    AND m.m_type IN ('Out', 'Merged')
    AND m.m_date <= '2018-11-28 07:30:00'
    AND m.mark_delete IS NULL
    AND m.m_onhold = 'onhold'
    AND m.m_ids NOT IN (
        SELECT 
            t1.comb_id1
        FROM
            n_combine_tbl AS t1
        WHERE
            t1.time <= '2018-11-28 07:30:00'
    )
    AND m.m_ids NOT IN (
        SELECT 
            t2.comb_id2
        FROM
            n_combine_tbl AS t2
        WHERE
            t2.time <= '2018-11-28 07:30:00'
    )
;

或不存在：

SELECT
    SUM(m.m_out) AS totalOut
FROM
    m_detal AS m
WHERE
        m.opers = '25'
    AND m.m_type IN ('Out', 'Merged')
    AND m.m_date <= '2018-11-28 07:30:00'
    AND m.mark_delete IS NULL
    AND m.m_onhold = 'onhold'
    AND NOT EXISTS (
        SELECT 
            *
        FROM
            n_combine_tbl AS t
        WHERE
                t.time <= '2018-11-28 07:30:00'
            AND (
                   t.comb_id1 = m.m_ids
                OR t.comb_id2 = m.m_ids
            )
    )
;

你应该检查“暂停”逻辑。
您应该尝试在n\u combine\u tbl上添加索引：（time，comb\u id1），（time，comb\u id2），（comb\u id1，time），（comb\u id2，time）并检查对您的数据有什么好处。
还应考虑mêdetal表上的多列索引。

赞(0）回复(0）举报 2021-06-18

我来回答

mysqli查询where not in(select)inner join

1条答案

相关问题

热门标签

最新问答