如何为WHERE与ORDER BY子句组合创建SQLite索引？

场景说明 #

输入为固定键序字典，键对应SQLite表result_table的列名，值为待查询内容，表中包含主键列idx：

input = {
    "A": ["string"],
    "B": [
          "1234",
          "4567"
    ],
    "C": ["string"],
    "D": ["string"]
}

生成的查询语句示例：

SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234)) LIMIT 100
SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234,5678)) LIMIT 100
SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234) and D in ('somestring')) LIMIT 100
SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234,5678)) ORDER BY A ASC, D DESC LIMIT 100
SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234,5678) and C in ('somestring2')) ORDER BY A ASC, C DESC LIMIT 100

当前已创建的索引：

CREATE INDEX idx_idx_A_B_C_D ON result_table(idx, A, B, C, D);
CREATE INDEX idx_idx_A_C_D ON result_table(idx, A, C, D);
CREATE INDEX idx_idx_A_D ON result_table(idx, A, D);
CREATE INDEX idx_idx_B_C_D ON result_table(idx, B, C, D);
CREATE INDEX idx_idx_B_D ON result_table(idx, B, D);
CREATE INDEX idx_idx_C_D ON result_table(idx, C, D);
CREATE INDEX idx_idx_D ON result_table(idx, D);

索引优化方案 #

SQLite要同时用索引完成过滤和排序，需要索引前缀完全匹配WHERE的过滤条件（按顺序），后续列匹配ORDER BY的列（仅最后一列支持反向排序匹配）。结合你的查询模式，优化如下：

1. 删除无用索引 #

以下索引因未匹配查询的过滤逻辑（WHERE中几乎不用A作为过滤条件），可直接删除以降低维护成本：

• (idx, A, B, C, D)
• (idx, A, C, D)
• (idx, A, D)

2. 保留有效索引 #

保留以下适配过滤场景的现有索引：

• (idx, B, D)：覆盖WHERE idx >= ? AND B IN (...) AND D IN (...)的过滤需求
• (idx, D)：若存在仅用D过滤的查询，可保留；否则可删除

3. 新增适配ORDER BY的索引 #

针对带排序的查询，新增两个索引，让SQLite直接通过索引完成过滤+排序，避免额外排序开销：

• 适配WHERE idx >= ? AND B IN (...) ORDER BY A ASC, D DESC：

  CREATE INDEX idx_idx_B_A_D ON result_table(idx, B, A ASC, D DESC);
  

• 适配WHERE idx >= ? AND B IN (...) AND C IN (...) ORDER BY A ASC, C DESC：

  CREATE INDEX idx_idx_B_C_A_C ON result_table(idx, B, C, A ASC, C DESC);
  

额外说明 #

• 对于无排序的WHERE idx >= ? AND B IN (...)查询，可复用上述新增索引的前缀(idx, B)，无需单独创建(idx, B)索引。
• 若后续有新的查询模式（比如新增ORDER BY列组合），再针对性新增索引即可，避免过度创建索引。

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