如何快速在超大数据集的两个子集中实现SQL交集查询？

针对你1.04亿行的my_table，要快速找到同时包含word_id=123和456的(doc_id, page_id)，核心优化方向是消除回表开销，以下是具体可行的方案：

1. 优先创建覆盖型复合索引 #

当前的单字段索引只能过滤word_id，但获取doc_id和page_id时需要回表访问主表，这是耗时的主要原因。创建包含查询所需所有字段的复合索引：

CREATE INDEX idx_word_doc_page ON my_table (word_id, doc_id, page_id);

这个索引的逻辑是：先按word_id分组，每个word_id下直接存储对应的doc_id和page_id，数据库查询时无需访问主表，直接从索引中提取数据，IO开销会大幅降低。

2. 基于复合索引的优化查询 #

方案A：JOIN查询（逻辑清晰直观） #

SELECT f.doc_id, f.page_id
FROM (SELECT doc_id, page_id FROM my_table WHERE word_id = 123) f
INNER JOIN (SELECT doc_id, page_id FROM my_table WHERE word_id = 456) b
  ON f.doc_id = b.doc_id AND f.page_id = b.page_id;

方案B：EXISTS子查询（部分数据库执行计划更优） #

SELECT doc_id, page_id
FROM my_table t1
WHERE word_id = 123
AND EXISTS (
    SELECT 1
    FROM my_table t2
    WHERE t2.word_id = 456
      AND t2.doc_id = t1.doc_id
      AND t2.page_id = t1.page_id
);

创建复合索引后，执行计划会显示Using index（MySQL）或Index Only Scan（PostgreSQL），说明完全使用索引完成查询，耗时可降至1秒以内。

3. 极端高频场景：预计算结果表 #

如果这类单词对交集查询是Web应用的核心高频操作，可以提前预计算并存储结果：

1. 创建预计算表：

CREATE TABLE word_pair_pages (
    word_id1 INT,
    word_id2 INT,
    doc_id INT,
    page_id INT,
    PRIMARY KEY (word_id1, word_id2, doc_id, page_id)
);

2. 定期（如每天/每小时）用优化后的查询填充表：

INSERT INTO word_pair_pages (word_id1, word_id2, doc_id, page_id)
SELECT 123, 456, doc_id, page_id
FROM my_table t1
WHERE word_id = 123
AND EXISTS (
    SELECT 1 FROM my_table t2 WHERE t2.word_id = 456 AND t2.doc_id = t1.doc_id AND t2.page_id = t1.page_id
)
ON DUPLICATE KEY UPDATE doc_id = doc_id; -- 避免重复插入

3. 查询时直接读取预计算表：

SELECT doc_id, page_id FROM word_pair_pages WHERE word_id1 = 123 AND word_id2 = 456;

这种方式查询耗时可达到毫秒级，适合固定单词对的高频查询场景。

4. 数据库特定优化：位图/GIN索引 #

如果使用PostgreSQL，可以尝试GIN索引加速交集查询：

CREATE INDEX idx_word_doc_page_gin ON my_table USING GIN (word_id, (ROW(doc_id, page_id)));

如果是MySQL，部分版本支持位图索引插件，但word_id通常是高基数字段，因此优先考虑复合覆盖索引。

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