改善SQLite反连接性能

我在SQLite DB中有以下两个表(结构可能看起来毫无意义,我知道但是请耐心等待)

+-----------------------+
| source                |
+-----------------------+
| item_id | time | data |
+-----------------------+

+----------------+
| target         |
+----------------+
| item_id | time |
+----------------+

--Both tables have a multi column index on item_id and time

源表包含大约500,000行,目标表中永远不会有多个匹配记录,实际上几乎所有源行都可能具有匹配的目标行.

我试图执行一个相当标准的反连接来查找源中的所有记录而没有目标中的相应行,但我发现很难创建具有可接受执行时间的查询.

我使用的查询是：

SELECT
    source.item_id,source.time,source.data
FROM source
LEFT JOIN target USING (item_id,time)
WHERE target.item_id IS NULL;

只有没有WHERE子句的LEFT JOIN需要大约200ms来完成,随之增加到5000ms.

虽然我最初注意到在我的消费应用程序中的慢查询,但是通过直接从sqliteman中执行语句获得了上面的时间.

是否有一个特殊的原因可以解释为什么这个看似简单的条款会大大增加执行时间,是否有某种方法可以重构此查询以改进它？

我也尝试了以下相同的结果. (我想底层的查询计划是一样的)

SELECT 
    source.item_id,source.data
FROM source
WHERE NOT EXISTS (
    SELECT 1 FROM target
    WHERE target.item_id = source.item_id
    AND target.time = source.time
);

非常感谢！

更新

非常抱歉,事实证明这些明显的结果实际上是由于与sqliteman的怪癖.

似乎sqliteman任意对返回到256的行数应用限制,并且在滚动它们时会更加动态加载.这将使对大型数据集的查询显示得比实际更快,使其成为估计查询性能的不良选择.

尽管如此,他们有任何明显的方法可以改善这个查询的性能,还是我只是限制了SQLite的功能？

解决方法

0|0|0|SCAN TABLE source
0|1|1|SEARCH TABLE target USING COVERING INDEX ti (item_id=? AND time=?)

这几乎尽可能高效：

>必须检查源中的每一行
>在目标中搜索匹配的行.

有可能做一点改进.
源行可能没有排序,因此目标搜索将在索引中的随机位置执行查找.
如果我们可以强制源扫描按索引顺序排列,那么目标查找也将按顺序进行,这使得这些索引页更有可能已经在缓存中.

如果我们不使用索引中没有的任何列,SQLite将使用源索引,即,如果我们删除数据列：

> EXPLAIN QUERY PLAN
  SELECT source.item_id,source.time
  FROM source
  LEFT JOIN target USING (item_id,time)
  WHERE target.item_id IS NULL;
0|0|0|SCAN TABLE source USING COVERING INDEX si
0|1|1|SEARCH TABLE target USING COVERING INDEX ti (item_id=? AND time=?)

这可能没多大帮助.
但是如果它有帮助,并且如果你想要源代码中的其他列,你可以通过首先进行连接,然后通过rowid查找源行来实现这一点(如果结果很少,额外的查找应该不会受到影响)：

SELECT *
FROM source
WHERE rowid IN (SELECT source.rowid
                FROM source
                LEFT JOIN target USING (item_id,time)
                WHERE target.item_id IS NULL)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!