SQLite sqlite3_step() 与大数据库挂起

Question

我正在编写一个小型 Objective-C 库，它与嵌入式 SQLite 数据库一起工作。

我使用的 SQLite 版本是 3.7.13(使用 SELECT sqlite_version() 检查)

我的查询是:

SELECT ROUND(AVG(difference), 5) as distance 
FROM (
  SELECT (
    SELECT A.timestamp - B.timestamp 
    FROM ExampleTable as B 
    WHERE B.timestamp = (
      SELECT MAX(timestamp) 
      FROM ExampleTable as C 
      WHERE C.timestamp < A.timestamp
    )
  ) as difference 
  FROM ExampleTable as A 
  ORDER BY timestamp)

基本上它输出按时间戳排序的行之间的平均时间戳差异。

我在一个包含 35k 行的示例数据库上尝试了查询，它运行了大约 100 毫秒。到目前为止一切顺利。

然后我在另一个具有 100k 行的示例数据库上尝试查询，它在 sqlite3_step() 处挂起，占用了 100% 的 CPU 使用率。

由于我无法使用调试器进入 sqlite3_step()，是否有另一种方法可以让我了解函数挂起的位置或此处问题的调试日志？

我还尝试在 100k 行数据库上运行我的库中的其他查询，没有问题，但这些也是没有子查询的简单查询。也许这就是问题所在？

谢谢

更新

这是 EXPLAIN QUERY PLAN 请求的输出:

"1","0","0","SCAN TABLE ExampleTable AS A"
"1","0","0","EXECUTE CORRELATED SCALAR SUBQUERY 2"
"2","0","0","SCAN TABLE ExampleTable AS B"
"2","0","0","EXECUTE CORRELATED SCALAR SUBQUERY 3"
"3","0","0","SEARCH TABLE ExampleTable AS C"
"1","0","0","USE TEMP B-TREE FOR ORDER BY"
"0","0","0","SCAN SUBQUERY 1"

Answer 1

通过timestamp 值查找行可以使用此列上的索引进行优化:

CREATE INDEX whatever ON ExampleTable(timestamp);

并且此查询效率低下:ORDER BY 不影响取平均值的值，并且 B 和 C 中的 timestamp 值始终是相同，因此您可以删除其中一个:

SELECT ROUND(AVG(difference), 5) AS distance 
FROM (
  SELECT timestamp -
         (SELECT MAX(timestamp)
          FROM ExampleTable AS B
          WHERE timestamp < A.timestamp)
         AS difference
  FROM ExampleTable AS A)