在SQLite中如何写出高效的嵌套子查询？

SQLite不支持嵌套子查询的深度优化，性能随嵌套加深急剧下降；应改用JOIN、预聚合或字典缓存，并依赖EXPLAIN验证执行计划。

SQLite里压根不该写“高效的嵌套子查询”——它不支持子查询的深度优化，嵌套越深，性能掉得越狠。真正该做的是把 SELECT ... (SELECT ...) 这类结构直接干掉，换成连接或预聚合。

为什么嵌套子查询在SQLite里特别慢

SQLite的查询优化器非常简单，对子查询基本不做重写或物化处理。每个子查询都会被独立执行，且无法复用上层结果的索引上下文。比如 SELECT sender, (SELECT COUNT(*) FROM messages m2 WHERE m2.sender = m1.sender)，每行都触发一次全表扫描（除非有精准索引），实际是 O(n²) 行为。

• 子查询返回多行时直接报错：subquery returns more than one row
• 相关子查询（含外部引用）无法利用 sender 列上的索引加速，因为优化器不推导关联条件
• WAL模式、缓存调优等参数对子查询本身无加速作用，只缓解IO层面卡顿

用 JOIN 替代相关子查询的实操写法

原Python CGI中那个循环里反复查 SELECT COUNT(id) FROM messages WHERE sender=? AND day=? AND hour=?，本质是“按 sender-day-hour 三维分组计数”，完全可以用单条 GROUP BY 解决。

• 把嵌套的24×N次查询，压缩成1次聚合： SELECT sender, day, hour, COUNT(id) FROM messages GROUP BY sender, day, hour
• Python端用字典预加载： counts = {(row[0], row[1], row[2]): row[3] for row in curs.fetchall()}
• 渲染时直接查字典，避免任何SQL执行 —— 这比“优化子查询”快两个数量级

哪些子查询能勉强留，但必须加索引

只有两类非相关子查询（即不依赖外层字段）可能被SQLite较友好地处理，但前提是列上有索引：

• WHERE id IN (SELECT id FROM blacklist)：确保 blacklist.id 有索引，否则变成嵌套循环
• WHERE size > (SELECT AVG(size) FROM messages)：子查询只执行1次，但 AVG() 仍需全表扫描，建议提前算好缓存到变量
• 绝对不要写：WHERE date LIKE '%Jan%' 类子查询 —— LIKE 前缀通配符必然全表扫，哪怕加了索引也无效

EXPLAIN 是唯一能看清真相的工具

别猜，直接看执行计划。对任意含子查询的语句，先跑：EXPLAIN QUERY PLAN SELECT ...

• 如果输出里出现 SCAN TABLE messages 多次，说明子查询没被优化，正在重复扫表
• 出现 SEARCH TABLE messages USING INDEX idx_sender 才算真正走索引
• 注意 temp b-tree 出现次数 —— 这代表SQLite在内存建临时排序结构，数据量大时会溢出到磁盘，巨慢

最常被忽略的一点：SQLite的“嵌套子查询优化”根本不存在。所谓“技巧”都是绕过它，而不是驯服它。索引、JOIN、预聚合、缓存 —— 这些才是真实有效的路径。一旦发现代码里出现三层以上的括号嵌套，第一反应不是调参，而是重构SQL逻辑。