嵌套SELECT查询有时卡顿

Question

我在一个Python应用程序中使用sqlite3，测试的时候发现我的查询有时会导致程序卡住。大概的例子是这样的：

SELECT id,name FROM main_table WHERE name IN 
(SELECT name FROM another_table WHERE another_table.attribute IN 
('foo', 'bar', 'baz'))

通常，第一次尝试这样的查询时，程序会直接卡住。不过，如果我先执行子查询，然后再执行整个复杂的查询，它几乎是立刻就能完成。

我猜测程序可能是在缓存第一个简单查询的结果，这样下次执行的时候就会快一些。但即便如此，我还是想知道怎么才能避免第一次就卡住。

Answer 1

使用“select ... where ... in ...”这样的查询语句，通常会运行得很慢。优化器通常会把它们当作一大堆条件来处理，比如（name=val1 或 name=val2 或 ... 或 name=valn）。

可以尝试用内连接（inner join）来替代子查询：

SELECT 
    id
    ,name 
FROM 
    main_table 
    INNER JOIN another_table on 
        main_table.name=another_table.name
        and another_table.attribute in (
            'foo','bar','baz'
        )

Answer 2

你没有提到任何关于索引的事情……两个表中的 name 至少应该有索引。

这里有一个使用 JOIN 的等效写法：

SELECT DISTINCT
       x.id,
       x.name 
  FROM main_table x
  JOIN ANOTHER_TABLE y ON y.name = x.name
                      AND y.attribute IN ('foo', 'bar', 'baz')

但是要注意，如果 ANOTHER_TABLE 中有多个记录与 MAIN_TABLE 的一条记录关联，JOIN 会产生重复的结果。因此需要使用 DISTINCT（或者如果你喜欢的话，可以用 GROUP BY）。

使用 EXISTS 可能比使用 IN 更好：

SELECT x.id,
       x.name 
  FROM main_table x
 WHERE EXISTS(SELECT NULL
                FROM ANOTHER_TABLE y 
               WHERE y.name = x.name
                 AND y.attribute IN ('foo', 'bar', 'baz'))