改善SQLite反连接性能

概述查看此问题底部的更新,下面提到的查询时间意外差异的原因已被确定为sqliteman怪癖的结果 我在SQLite DB中有以下两个表(结构可能看起来毫无意义,我知道但是请耐心等待) +-----------------------+| source |+-----------------------+| item_id | time | data |+----- 查看此问题底部的更新,下面提到的查询时间意外差异的原因已被确定为sqliteman怪癖的结果

我在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.dataFROM sourceleft JOIN target USING (item_ID,time)WHERE target.item_ID IS NulL;

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

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

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

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

SELECT     source.item_ID,source.dataFROM sourceWHERE NOT EXISTS (    SELECT 1 FROM target    WHERE target.item_ID = source.item_ID    AND target.time = source.time);

非常感谢！

更新

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

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

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

解决方法 这是您的查询的 query plan(任一)：

0|0|0|SCAN table source0|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 si0|1|1|SEARCH table target USING COVERING INDEX ti (item_ID=? AND time=?)

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

SELECT *FROM sourceWHERE rowID IN (SELECT source.rowID                FROM source                left JOIN target USING (item_ID,time)                WHERE target.item_ID IS NulL)

总结

以上是内存溢出为你收集整理的改善SQLite反连接性能全部内容，希望文章能够帮你解决改善SQLite反连接性能所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。