SQLite在处理子查询时，并不像一些大型数据库那样有激进的查询重写器，它更多依赖嵌套循环、索引查找和临时B树来完成计算。换句话说，你写出来的子查询形态，会很大程度决定执行计划是否高效。优化子查询的第一步，不是急着改SQL，而是先通过EXPLAIN QUERY PLAN观察SQLite到底怎样组织表访问顺序。下面围绕几种常见场景展开说明。
一、关联子查询为何容易放大查询成本
普通子查询通常独立于外层查询执行，结果集可以被缓存或物化。相关性子查询则不同，它引用了外层查询的列，SQLite必须针对外层每一行重新计算内部查询。比如要统计每个订单最近一次支付时间，常见写法是在SELECT列表里放一个用order_id关联的MAX子查询。如果orders表有一万行，payments表即使建立了order_id索引，SQLite仍可能执行一万次索引查找。每次查找虽然快，但一万次随机I/O叠加起来就非常可观，尤其是数据不在内存中的时候。
另一个更隐蔽的问题是，SQLite对关联子查询的优化空间有限，因为外层列的值只有在运行时才确定。优化器无法提前把子查询转换成一次性扫描或半连接。因此，当你在执行计划里看到多次SCAN或者每次外层行都伴随一次SEARCH，基本可以判断需要改写查询结构。
可以先用下面的语句查看执行计划。关注输出中是否出现CO-ROUTINE、SCAN TABLE、SEARCH TABLE以及是否使用临时B树等字样。
EXPLAIN QUERY PLAN
SELECT o.id,
(SELECT MAX(p.paid_at)
FROM payments p
WHERE p.order_id = o.id) AS last_pay_time
FROM orders o
LIMIT 500;
如果输出显示orders表被扫描，同时子查询对payments表执行SEARCH USING INDEX，说明每行都在走索引，但如果orders行数很大，整体开销仍然会线性放大。这个计划并非不可用，只是它把循环放到了SQLite内部，应用层很难控制批次和缓存。
因此，判断一个关联子查询是否值得优化，主要看两个指标：外层结果集规模，以及内层查询是否能在索引下快速定位。外层结果集越大，越应该考虑把多次查找合并成一次连接或分组聚合。
二、用EXISTS和JOIN替代低效IN子查询
很多业务查询只关心主表里哪些行满足条件，并不需要子查询返回具体数据。此时IN子查询经常不如EXISTS来得直接。IN语句要求SQLite处理右侧结果集，可能涉及去重、排序或临时表。如果右侧SELECT返回几万甚至几十万行，仅构建这个结果集本身就会消耗大量内存和CPU。EXISTS则采用短路判断，每遇到一个匹配行就停止继续查找，对存在性检查非常友好。
看一个典型的客户筛选场景。原始IN写法如下：
SELECT o.id, o.amount FROM orders o WHERE o.customer_id IN ( SELECT c.id FROM customers c WHERE c.level > 3 );
可以改写成EXISTS形式，让子查询直接引用外层o.customer_id。思维上从先找集合再匹配，变成逐行判断是否存在高等级客户。
SELECT o.id, o.amount
FROM orders o
WHERE EXISTS (
SELECT 1
FROM customers c
WHERE c.id = o.customer_id
AND c.level > 3
);
这种改写不要求customers.id返回结果集，配合customers(id, level)复合索引，SQLite可以快速定位到对应客户并检查level条件。orders表仍然会循环，但每行只进行极少次数的B树查找，不需要维护临时表。需要注意的是，如果orders表没有合适的过滤条件，EXISTS依然需要扫描整张orders表，这时应优先考虑给外层过滤列建立索引，或者通过分区思路减少扫描范围。
JOIN改写则适合需要同时使用子查询表字段的场景。假设业务需要拿到客户名称和订单金额，使用JOIN一次连接即可。但JOIN可能因一对多关系产生重复行，必须用DISTINCT或GROUP BY去重，否则订单金额会被放大。SQLite对DISTINCT的实现依赖B树排序，数据量大时同样有成本，因此并非所有IN都要盲目改成JOIN，必须结合返回是否唯一来判断。
SELECT DISTINCT o.id, o.amount, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.level > 3;
这条查询先通过c.level索引筛选客户，再回表拿name并连接orders，适合客户过滤后行数明显减少的情况。如果符合条件的客户很多，JOIN的中间结果会膨胀，执行时间反而可能比EXISTS更长。
三、覆盖索引与物化子查询减少回表和重复执行
覆盖索引是SQLite子查询优化中成本最低、收益最明显的手段之一。如果内部查询只需要少数列，把这些列全部放进一个复合索引，SQLite就可以只读索引B树，完全不用回聚集索引或行表。比如查询支付表最近一次支付时间，建立(order_id, paid_at)索引后，MAX(paid_at)可以只遍历索引尾部节点，配合order_id等值条件直接定位。
CREATE INDEX idx_payments_order_paid ON payments(order_id, paid_at);
建完索引后，原来的SELECT MAX(p.paid_at) WHERE p.order_id = o.id可以在索引内部完成，不需要访问payments正文。即使仍然存在外层循环，单次查找的I/O已经降到很低。若业务查询不涉及payments其他字段，这个索引就能发挥很大作用。
对于非关联子查询，物化是另一种常见优化方向。SQLite在遇到IN (SELECT ...)且右侧结果集规模适中时，可能自动把子查询结果写到内存B树或哈希表，后续匹配就变成对临时结构的查找。要确认是否发生物化，可以查看EXPLAIN QUERY PLAN输出里有没有MATERIALIZE字样。你也可以用CTE把子查询结果存放起来复用，减少SQL重复计算。
WITH high_level_customers AS ( SELECT id FROM customers WHERE level > 3 ) SELECT o.id, o.amount FROM orders o WHERE o.customer_id IN ( SELECT id FROM high_level_customers );
这个写法在语义上和直接IN类似，但在复杂查询中能提高可读性，部分场景下还能帮助优化器复用已经物化的结果集。不过要记住，CTE不是强制物化指令，真正是否使用临时表仍然由SQLite决定。
关联子查询的优化要点是减少每次查找的代价，或者干脆将多次查找合并成一次扫描。比如把每个订单的最近支付时间改成LEFT JOIN加GROUP BY，让SQLite只扫描一次payments表，按照order_id分组取最大值。
SELECT o.id, MAX(p.paid_at) AS last_pay_time FROM orders o LEFT JOIN payments p ON p.order_id = o.id GROUP BY o.id;
这条查询把订单表和支付表连接后分组，执行计划通常会对payments表使用order_id索引扫描或全表扫描后做哈希分组。它避免了orders循环中的重复子查询，但会生成较大的中间结果。适合支付表本身不太大，或者已经对order_id有聚簇排序的场景。如果orders和payments都很大，应考虑先按时间或其他条件缩小范围，再连接聚合。
四、避免在关联条件上使用函数与隐式类型转换
索引失效是子查询变慢的另一大原因。SQLite里如果对索引列套上函数，比如date(paid_at)或者对order_id做加减运算，优化器就无法直接使用B树定位。关联子查询中，很多人习惯把外层列放进去一起运算，例如用date(p.paid_at) = date(o.created_at)判断是否同一天支付。这种写法让payments表的paid_at索引完全失效，SQLite只能逐行扫描payments表，再计算函数并比较。
更合理的做法是把函数作用在常量或外层值上，让内层索引列保持干净。比如要查某订单创建后一天内的支付记录，可以用两个范围条件替代函数包裹。内层查询写成p.paid_at >= o.created_at AND p.paid_at < datetime(o.created_at, '+1 day')，其中右侧是对外层列的函数，而p.paid_at本身没有被包裹，索引仍然可以使用。
SELECT o.id,
(SELECT COUNT(*)
FROM payments p
WHERE p.order_id = o.id
AND p.paid_at >= o.created_at
AND p.paid_at < datetime(o.created_at, '+1 day')) AS pay_count
FROM orders o;
注意这里虽然datetime函数仍然作用于外层列，但它在比较中只影响边界值计算，不会阻止SQLite通过p.order_id和p.paid_at复合索引进行范围扫描。相较于直接对p.paid_at套函数，这种写法能显著减少扫描行数。
隐式类型转换同样需要留意。SQLite在比较不同类型时可能应用亲和规则，导致索引失效。例如把数字列和字符串字面量比较，SQLite可能会把列值转换成文本再比较。遇到类似问题，应保持比较双方类型一致，或者在建表时明确列类型。执行计划中如果出现SCAN TABLE而不是SEARCH TABLE，往往就是索引没有被利用。
最后一个容易被忽视的点是子查询内部排序。很多人习惯在子查询里写ORDER BY再交给外层LIMIT，但SQLite不一定把排序下推，可能先扫描全部行再排序，再被外层过滤。对于关联子查询，这种写法尤其危险。应该尽量把LIMIT和排序条件放到外层，或者通过索引顺序避免显式排序。比如用MAX/MIN替代ORDER BY加LIMIT 1，更直接地表达业务意图，也给优化器更大空间。