SQLite如何高效实现等效于INTERSECT ALL的交集查询

性能问题核心原因 #

你之前用全局统计URL出现次数取最小值的方案慢，本质是两个问题：

• 没有针对查询条件建合适的索引，导致每次查询都要扫全表
• 全局分组计数会处理所有标签下的所有数据，而你实际只需要p_tags='foo'和p_tags='bar'两个标签下的条目，做了大量无用计算

第一步：建覆盖索引（性能提升的核心，必做） #

先执行下面的建索引语句，建完之后查询不需要回表读原始数据，两个标签的过滤可以直接走索引定位：

CREATE INDEX IF NOT EXISTS idx_tags_tag_url ON Tags(p_tags, p_urls);

这个索引会先按p_tags排序，同标签下按p_urls排序，所有针对p_tags做等值过滤的查询都能直接命中索引，同标签下的URL天然有序，后续计算几乎不需要额外排序开销。

第二步：等价 INTERSECT ALL 的高效查询写法 #

支持窗口函数的场景（SQLite 3.25.0 及以上版本，推荐） #

用窗口函数给同标签下重复出现的URL按出现顺序编行号，两个集合按URL+行号做内连接，完全匹配INTERSECT ALL的语义，且全程走索引：

SELECT a.p_urls
FROM (
    SELECT
        p_urls,
        ROW_NUMBER() OVER (PARTITION BY p_urls) AS rn
    FROM Tags
    WHERE p_tags = 'foo'
) a
INNER JOIN (
    SELECT
        p_urls,
        ROW_NUMBER() OVER (PARTITION BY p_urls) AS rn
    FROM Tags
    WHERE p_tags = 'bar'
) b
ON a.p_urls = b.p_urls AND a.rn = b.rn;

语义正确性说明

比如foo标签下URL https://example.com 出现3次，bar标签下该URL出现2次，连接时会匹配到行号1、2的两条记录，最终返回2次该URL，和INTERSECT ALL的返回规则完全一致。

低版本SQLite不支持窗口函数的场景 #

如果你的SQLite版本低于3.25.0，可以用递归CTE的写法实现同样的逻辑，性能远高于全局分组计数：

WITH
foo_set AS (
    SELECT p_urls, ROWID AS row_id
    FROM Tags WHERE p_tags = 'foo'
    ORDER BY p_urls
),
bar_set AS (
    SELECT p_urls, ROWID AS row_id
    FROM Tags WHERE p_tags = 'bar'
    ORDER BY p_urls
),
recursive_intersect AS (
    SELECT f.p_urls res_url, f.row_id f_rid, b.row_id b_rid
    FROM foo_set f, bar_set b
    WHERE f.p_urls = b.p_urls
    LIMIT 1
    UNION ALL
    SELECT
        COALESCE(f_next.p_urls, b_next.p_urls),
        COALESCE(f_next.row_id, 0),
        COALESCE(b_next.row_id, 0)
    FROM recursive_intersect r
    LEFT JOIN foo_set f_next
        ON f_next.p_urls = r.res_url AND f_next.row_id > r.f_rid
    LEFT JOIN bar_set b_next
        ON b_next.p_urls = r.res_url AND b_next.row_id > r.b_rid
    WHERE f_next.row_id NOT NULL AND b_next.row_id NOT NULL
)
SELECT res_url AS p_urls FROM recursive_intersect;

额外优化注意点 #

• 不要对全表做GROUP BY p_tags, p_urls预聚合，除非你的表是写少读多的场景，否则预聚合的维护成本会很高，上面的写法在有索引的情况下已经足够快
• 如果需要查询更多标签的交集，只需要按同样的逻辑增加子查询做内连接即可，索引依然生效
• 建索引之后第一次查询会有索引构建的开销，后续查询性能会稳定在很高的水平

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