加速NOT子查询

Question

晚上好,

目前，我正试图提高一些查询的性能。据我所知，如果不附加条件地对索引字段使用子查询，"IN“或"NOT”这样的语句使用大量的值会更快。

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

当涉及到在table2上使用条件时，查询在大量数据上变得非常缓慢。

SELECT *
FROM table1
WHERE
    field1 NOT IN (
        SELECT
            index_field
        FROM table2
        WHERE
            user_id = '2'
    )

我正在考虑使用LEFT Join，但是当我需要对user_id进行过滤时，它也变得很慢。

我该如何解决这个问题？目前，我对此一无所知。

原来的问题看上去像某物。像这样的

SELECT
    i.*
FROM stream_item si
LEFT JOIN user__publisher_item ui
    ON ui.user_id = 2
    && ui.item_id = si.item_id
INNER JOIN item i
    ON i.id = si.item_id
WHERE 
    si.stream_id IN (5,7,8,9,19,24,29,42,43,44)
    && ui.id IS NULL || (
        ui.`read` = 0 || ui.saved = 1
    )
GROUP BY
    si.item_id
ORDER BY
    si.`found` DESC
LIMIT 0,10

id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra
1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort
1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where
1       SIMPLE          i       eq_ref      PRIMARY                                                                                 PRIMARY             4           si.item_id          1   

我有一个表，它表示流和项之间的n:m关系。一个唯一的项目可以通过多个流访问。基于此，我有一个用于项的用户依赖状态的状态表。目前，只有在读取或保存第一个tim时才会生成用户状态项.否则，user_item表中就没有条目。在开始时，这个查询很好，但是现在，它需要很多时间。即使我省略了"INNER JOIN“，当前的表也需要7秒的时间，每个表的行数大约为500 K。

后台的表结构如下所示：

• 流
• stream_item //n:流与项的m关系
• 项目//独特项目
• stream_group //用户定义的多个流组
• stream_group_streams //_n: stream_groups与streams_的M关系
• 用户
• user_item // state -表，它保存项的用户依赖状态-如果没有状态，则项不存在行。

我已经在堆栈过流上问过这个问题了，但是后来我把我的两个编辑添加了更多的信息，这样我就没有答案了。

Answer 1

通常，您可以将NOT IN (...)子查询替换为NOT EXISTS (...)，而后者将更快。从逻辑上讲，它们是相同的，尽管一些数据库优化器更好地处理NOT EXISTS，因为它们可以在第一次匹配之后停止子查询检查。

相反，如果DB优化器不够聪明，不知道您在做什么，子查询有很多行，那么在执行每个NOT IN检查之前，它必须缓冲每个子查询的所有行。

试试这样的东西：

SELECT *
FROM table1 a
WHERE NOT EXISTS(SELECT 1
                   FROM table2 b
                  WHERE b.user_id = 2
                    AND b.index_field = a.field1)

在本例中，优化器只需要检查匹配子查询是否存在单个行，包括b.user_id = 2上的附加筛选器。如果index_field和user_id可用，则它们应该使用索引。