如何将SQLite双表JOIN计数查询性能优化至单表查询级别？

咱们先来拆解下当前的问题：两张单表的COUNT查询都很快（分别300ms和80ms），但关联后的COUNT直接慢到2700ms，核心问题出在关联过程中的索引效率和回表开销上。结合你的表结构和查询计划，我给你几个能把性能拉到单表级别的方案：

1. 优化索引，消除回表开销 #

当前b表的索引ix_b_deleted_bid只包含deleted和bid，当执行关联查询时，数据库需要从索引找到符合deleted=0的行后，回表去取aid字段才能和a表关联，这额外的IO操作在1.1M行的量级下会累积成巨大的开销。

解决办法是给b表创建一个包含aid的覆盖索引：

CREATE INDEX ix_b_deleted_aid_bid ON b (deleted, aid, bid) WHERE deleted = 0;

这个索引直接包含了查询需要的所有字段（deleted用于过滤，aid用于关联，bid用于计数），查询时完全不需要回表，能大幅减少IO时间。

同时a表的ix_a_deleted_aid已经是合适的覆盖索引（包含deleted和aid，刚好满足关联时的过滤和匹配需求），不需要修改。

优化后的查询计划应该会变成：

SEARCH TABLE b USING COVERING INDEX ix_b_deleted_aid_bid (deleted=?)
SEARCH TABLE a USING COVERING INDEX ix_a_deleted_aid (deleted=? AND aid=?)

这个调整后，关联查询的时间应该能降到几百毫秒，和单表查询的级别接近。

2. 预统计结果，实现毫秒级查询 #

如果你的COUNT结果不需要实时绝对准确（比如允许几分钟的延迟），可以用预统计的方式彻底解决性能问题：

• 创建一个统计记录表：

CREATE TABLE stats (
    stat_name TEXT PRIMARY KEY,
    stat_value INTEGER NOT NULL
);
-- 初始化统计值
INSERT INTO stats VALUES 
('active_a_count', (SELECT COUNT(aid) FROM a WHERE deleted=0)),
('active_b_count', (SELECT COUNT(bid) FROM b WHERE deleted=0)),
('active_ab_join_count', (SELECT COUNT(bid) FROM b JOIN a ON b.aid=a.aid WHERE b.deleted=0 AND a.deleted=0));

• 给a和b表添加触发器，当deleted字段更新或有新行插入/删除时，自动更新统计值：

-- 当a表的deleted状态变化时更新统计
CREATE TRIGGER trg_a_update_stats AFTER UPDATE OF deleted ON a
BEGIN
    UPDATE stats SET stat_value = (SELECT COUNT(aid) FROM a WHERE deleted=0) WHERE stat_name='active_a_count';
    UPDATE stats SET stat_value = (SELECT COUNT(bid) FROM b JOIN a ON b.aid=a.aid WHERE b.deleted=0 AND a.deleted=0) WHERE stat_name='active_ab_join_count';
END;

-- 当b表的deleted状态或aid变化时更新统计
CREATE TRIGGER trg_b_update_stats AFTER UPDATE OF deleted, aid ON b
BEGIN
    UPDATE stats SET stat_value = (SELECT COUNT(bid) FROM b WHERE deleted=0) WHERE stat_name='active_b_count';
    UPDATE stats SET stat_value = (SELECT COUNT(bid) FROM b JOIN a ON b.aid=a.aid WHERE b.deleted=0 AND a.deleted=0) WHERE stat_name='active_ab_join_count';
END;

-- 插入/删除行的触发器逻辑类似，可根据业务需求补充

之后查询直接从stats表取数：

SELECT stat_value FROM stats WHERE stat_name='active_ab_join_count';

这个查询的速度就是毫秒级，完全和单表查询一个级别。

3. 调整查询语句，利用EXISTS优化关联逻辑 #

原查询用的是JOIN，你可以改成用EXISTS子查询，让SQLite优化器更高效地终止不必要的查找：

SELECT COUNT(*) FROM (
    SELECT b.bid 
    FROM b 
    WHERE b.deleted = 0 
      AND EXISTS (
          SELECT 1 FROM a 
          WHERE a.aid = b.aid 
            AND a.deleted = 0
      )
);

EXISTS会在找到第一个匹配的a表行后就停止查找，相比JOIN的全匹配，能减少一部分关联开销。配合上面的索引优化，效果会更好。

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