SQLite调用sqlite3_step最后一行卡顿1秒问题求助

现象 #

CLI执行包含ROW_NUMBER()窗口函数的查询时速度正常，但通过C API调用sqlite3_step()逐行遍历结果时，最后一次调用会卡顿约1秒才返回SQLITE_DONE。移除ROW_NUMBER()后，卡顿消失。查询计划显示多个临时B-TREE被用于排序操作。

原因 #

SQLite处理带PARTITION BY的ROW_NUMBER()时，需要为每个分区创建临时B-TREE完成排序和编号工作。这个过程并非完全在返回结果前完成：

1. 逐行调用sqlite3_step()时，SQLite会先返回所有计算好的行数据，把临时B-TREE的清理、资源释放操作留到最后一次sqlite3_step()调用时执行。
2. CLI执行时，所有结果会被一次性拉取，资源清理的时间被包含在整个命令的总耗时里，不会被单独感知；而C API的逐行遍历模式，将结果返回和收尾操作的时间拆分，导致最后一步的卡顿凸显。

解决办法 #

• 添加针对性索引：针对窗口函数的PARTITION BY字段创建索引，减少临时B-TREE的排序开销。比如针对示例中的日期字段：

  CREATE INDEX idx_events_start_day ON events (date(start_at / 1000, 'unixepoch', 'utc'));
  

• 调整窗口函数执行阶段：将ROW_NUMBER()的计算移到CTE内部，让排序和分区操作在CTE阶段完成，提前触发资源清理：

  WITH tmp AS (
    SELECT 
        e.id, 
        datetime(date(start_at / 1000, 'unixepoch', 'utc'), 'utc') AS start_of_day_timestamp,
        ROW_NUMBER() OVER ( PARTITION BY start_of_day_timestamp ) AS row_num
    FROM events e
    LIMIT 100
  )
  SELECT id, start_of_day_timestamp, row_num FROM tmp;
  

• 优化临时存储配置：设置SQLite使用内存存储临时数据，增大缓存，减少磁盘IO带来的卡顿：

  // 设置临时存储为内存模式
  sqlite3_exec(db, "PRAGMA temp_store = 2;", NULL, NULL, NULL);
  // 增大缓存大小（单位：页，默认每页4KB）
  sqlite3_exec(db, "PRAGMA cache_size = 10000;", NULL, NULL, NULL);
  

• 批量获取结果：改用sqlite3_get_table()或批量读取接口一次性获取所有结果，让资源清理在批量操作后统一进行，避免单步卡顿。

内容的提问来源于stack exchange，提问作者david_adler