知道“给字段加索引能加速”还不够。为什么有索引仍然扫描？为什么两个单列索引不等于一个联合索引？为什么查询只多取一列，访问路径就可能变了？

本文用同一张订单表逐步回答这些问题。先手工排列索引条目，再看 SQLite 的查询计划，最后运行一个不需要安装数据库服务的 Python 实验。核心不是尽量让计划出现 INDEX，而是减少不必要的候选访问、取行和排序，同时保持结果正确。

建议先了解表、行以及简单的 SELECT。全文讨论普通 SQLite rowid 表；不会把某个数据库引擎的存储细节直接推广到所有系统。

先明确要优化的查询

假设要查某个用户从指定日期开始的前五笔订单，按日期升序排列；同一天按 id 升序，确保顺序唯一。

1
2
3
4
5
SELECT id, created_at, amount_cents
FROM orders
WHERE user_id = ? AND created_at >= ?
ORDER BY created_at, id
LIMIT ?;

这里的 created_at 是从统一起点计算的整数日编号，便于手算，不是把现实中的时区问题消除了。金额以“分”为单位存储成整数，避免本实验引入浮点金额比较。问号表示绑定值，不是让读者把问号拼成字符串。

字段 本例的含义 为什么需要
id 唯一订单编号，INTEGER PRIMARY KEY 识别行，并打破同日排序平局
user_id 用户编号 等值筛选
created_at 非空整数日编号 范围筛选与排序
amount_cents 非空整数金额，单位为分 返回结果
note 非空备注文本 模拟查询并不需要的额外列

查询同时有三种工作：定位指定用户、排除较早日期、按稳定顺序返回前几条。索引设计要一起考虑这些工作，而不只是数 WHERE 后面出现了几个字段。

这张图表示逻辑职责，不是承诺引擎一定先过滤完全部行再排序。合适的访问路径可以把几步合在一起，甚至在得到足够结果后提前结束。

索引是额外组织而不是免费标签

无索引时，朴素做法是逐行检查。索引则把选定的键按特定顺序组织起来，使“某一段键值在哪里”变得容易回答。代价是多占空间，插入和修改时也要维护相应结构。

SQLite 的数据库文件使用 B 树页组织表和索引；索引页与表页的载荷安排并不完全相同。因此，本文用“多路有序树”建立直觉，不把所有引擎的实现都画成同一种 B+ 树。SQLite 文件格式中的 B 树页

模型 一次导航保留的候选 适合建立什么直觉
线性扫描 一条条排除 工作量随被检查的数据增长
二分查找 根据中点排除约一半 有序性使定位不必从头开始
多路树导航 根据多个分隔键选子页 一个页里放多个键，减少树的层数

做一个仅用于手算的理想模型：若每个内部页有 100 个子页、每个叶页有 100 条记录，则一个根页连接 100 个内部页、每个内部页连接 100 个叶页时，可以容纳 100 × 100 × 100 = 100 万条记录。到一个叶页只经过三层；但这不等于真实查询恰好发生三次物理读盘，页缓存、页填充、溢出数据与返回行数都会改变代价。

本例的 id 声明是 INTEGER PRIMARY KEY，在这种普通 rowid 表中它是 rowid 的别名。二级索引可以携带行定位信息，用来找到表里的完整记录。这个结论有明确范围：不要把 WITHOUT ROWID 表或其他引擎的主键组织方式也套进来。SQLite rowid 表说明

手工排列一次联合索引

考虑下面七条自造数据，查询参数是用户 7、起始日 180、最多 3 条：

id user_id created_at amount_cents
1 7 179 900
2 8 180 1500
3 7 180 1200
4 7 181 800
5 7 180 2000
6 8 182 700
7 7 183 1600

给三列建立有序联合索引：

1
CREATE INDEX idx_user_time ON orders(user_id, created_at, id);

按这个键的字典序排列，先比较用户，相同再比较日期，再相同才比较编号。下表是便于理解的键序列，不是 SQLite 页字节的转储。

顺序 索引键 user_id created_at id 本次是否满足条件
1 7，179，1 日期太早
2 7，180，3 第 1 条
3 7，180，5 第 2 条
4 7，181，4 第 3 条，达到 LIMIT
5 7，183，7 满足条件，但本次不必返回
6 8，180，2 其他用户
7 8，182，6 其他用户

因此结果为 (3,180,1200)、(5,180,2000)、(4,181,800)。日期相同的 id=3 与 id=5 不会因为访问路径变化而交换，因为 ORDER BY 明确写出了 id。

这种“相同左侧键聚在一起”的性质，解释了为什么这个索引能同时适配用户等值、日期范围和结果顺序。SQLite 联合索引与查询排序

最左前缀不是背一句绝对规则

对索引 (user_id, created_at, id)，最直接的访问模式是先约束左侧用户，再在该用户内部限定日期范围。WHERE 条件的书写顺序不需要和索引字段顺序相同；决定结构的是 CREATE INDEX 中的顺序。

条件或排序 该索引能直接提供的结构性帮助
user_id = 7 一个用户的连续区域
user_id = 7 且 created_at >= 180 用户区域中的日期后缀
user_id = 7 且按 created_at、id 排序 等值用户内的键序
只有 created_at >= 180 日期范围分散在各用户区域，不是单一全局日期段
user_id = 7 且 created_at >= 180 且 id = 9 日期范围之后的 id 条件通常不能继续缩成同一种连续边界，仍需检查

最后两行不等于“整个索引完全没用”。引擎还可能扫描较窄的索引、利用覆盖信息，或在满足条件时采用 skip-scan 等策略。SQLite 的优化器文档明确给出了常规前缀约束与例外；应读实际计划，而不是把“最左前缀”背成永不变化的判决。SQLite WHERE 分析与 skip-scan

本篇还比较相反顺序的 (created_at, user_id, id, amount_cents)。它把同一天的不同用户放在一起：日期范围容易描述，但用户 7 的条目会夹在其他用户之间。对这条“某个用户的最近几笔”查询，可能要跳过更多候选；对另一个“所有用户某日订单”的查询，权衡又可能不同。

两个单列索引分别保存两种顺序，并不会自动变成一份 (user_id, created_at) 的复合排序。能否组合多个访问结果取决于具体优化器与查询形式，不应把两个概念等同。

覆盖索引省去的是哪一步

上面的联合索引可以确定哪些订单该返回，但本次还需要 amount_cents。若索引里没有这个金额，就需要沿行定位信息再访问表记录，通常称为“回表”。

把查询需要的金额放到索引末尾：

1
2
CREATE INDEX idx_cover
ON orders(user_id, created_at, id, amount_cents);
本例索引 能否按用户与日期定位 能否满足本例排序 是否包含所有返回列
user_id 能先找用户 仍需处理日期顺序 否
user_id、created_at、id 能 能 缺 amount_cents
user_id、created_at、id、amount_cents 能 能 是

覆盖是索引相对于某条查询的属性，不是固定的索引种类。如果改成 SELECT * 或额外返回 note，同一索引就不再覆盖。这里把金额放在 id 之后，是为了不把同日订单的 id 顺序变成“先按金额再按 id”；不应随意改变字段顺序。SQLite 覆盖索引说明

覆盖并不代表把整张表都复制到索引里最划算。索引越宽，占用越多，也可能降低每页能容纳的条目数。本文实验只比较读取工作，不给写入和存储成本下结论。

读懂查询计划但不要把它当计时器

SQLite 的 EXPLAIN QUERY PLAN 提供高层访问策略。重点看访问对象、可用于搜索的约束、是否覆盖以及是否需要额外排序，不必记住节点编号。

常见提示 可以读出什么 不能单凭它断言什么
SCAN orders 以扫描方式访问表 不等于任何数据量下都很慢
SEARCH orders USING INDEX 使用索引约束缩小访问范围 不等于实际返回很少或没有回表
USING COVERING INDEX 此访问可以从索引提供所需数据 不等于零 I/O 或零 CPU 成本
USE TEMP B-TREE FOR ORDER BY 为排序安排临时结构 不等于必定写入磁盘

SCAN 也可能伴随 USING INDEX，表示按索引顺序扫描，而不是先由搜索约束截出一个小范围。另一个重要限制是：官方把此输出定位为交互式调试信息，格式可能随 SQLite 版本变化；不要把完整文本当成生产程序的稳定接口。SQLite EXPLAIN QUERY PLAN

因此，先验证结果，再解释计划，再测量真实工作负载。计划不包含本次运行的磁盘等待时间，也不能取代线上延迟观测。

可运行的 Python 内存实验

下面是完整脚本，使用 Python 标准库 sqlite3，不访问真实数据库文件。五种索引配置分别创建独立内存数据库，数据相同，没有删表、删索引或清理文件的步骤。保存后直接用 Python 运行即可。

脚本用普通 Python 筛选与排序生成参考答案，再逐配置核对 SQL 结果。参数限定为整数，LIMIT 非负；查询值始终通过占位符绑定。set_progress_handler 的回调计数只用于观察 SQLite 虚拟机工作量，不是秒数或读盘次数；回调每条指令执行会带来额外开销，因此本脚本不据此做耗时排名。Python sqlite3 参数绑定与进度回调

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
import random
import sqlite3

SCHEMA = """
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    amount_cents INTEGER NOT NULL,
    note TEXT NOT NULL
)
"""
INDEXES = {
    "none": None,
    "user": "CREATE INDEX idx_user ON orders(user_id)",
    "composite": (
        "CREATE INDEX idx_user_time ON orders(user_id, created_at, id)"
    ),
    "covering": (
        "CREATE INDEX idx_cover ON orders"
        "(user_id, created_at, id, amount_cents)"
    ),
    "time_first": (
        "CREATE INDEX idx_time_first ON orders"
        "(created_at, user_id, id, amount_cents)"
    ),
}
QUERY = """
SELECT id, created_at, amount_cents
FROM orders
WHERE user_id = ? AND created_at >= ?
ORDER BY created_at, id
LIMIT ?
"""


def make_rows(n=20000, seed=45892):
    rng = random.Random(seed)
    return [
        (i, rng.randrange(1, 201), rng.randrange(366),
         rng.randrange(100, 100000), "order-" + str(i))
        for i in range(1, n + 1)
    ]


def open_db(rows, kind):
    ddl = INDEXES[kind]  # Index choice is a fixed allowlist, not user SQL.
    con = sqlite3.connect(":memory:")
    try:
        con.execute(SCHEMA)
        con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", rows)
        if ddl is not None:
            con.execute(ddl)
        con.commit()
        con.execute("ANALYZE")
        return con
    except BaseException:
        con.close()
        raise


def reference(rows, user, cutoff, limit):
    selected = [r for r in rows if r[1] == user and r[2] >= cutoff]
    selected.sort(key=lambda r: (r[2], r[0]))
    return [(r[0], r[2], r[3]) for r in selected[:limit]]


def observe(con, user, cutoff, limit):
    if not all(type(x) is int for x in (user, cutoff, limit)) or limit < 0:
        raise ValueError("Use integer parameters and a nonnegative limit")
    params = (user, cutoff, limit)
    plan = [r[3] for r in con.execute("EXPLAIN QUERY PLAN " + QUERY, params)]
    steps = 0

    def tick():
        nonlocal steps
        steps += 1
        return 0

    # Counts VM progress callbacks, not disk reads and not elapsed time.
    con.set_progress_handler(tick, 1)
    try:
        result = con.execute(QUERY, params).fetchall()
    finally:
        con.set_progress_handler(None, 0)
    return result, plan, steps


def main():
    rows = make_rows()
    params = (7, 180, 5)
    expected = reference(rows, *params)
    print("SQLite", sqlite3.sqlite_version)
    for kind in INDEXES:
        con = open_db(rows, kind)
        try:
            result, plan, steps = observe(con, *params)
            assert result == expected, (kind, result, expected)
            print(kind, "|", " ; ".join(plan))
            print("  rows =", result)
            print("  vm_callbacks =", steps)
        finally:
            con.close()


if __name__ == "__main__":
    main()
配置 变化的部分 观察问题
none 不建二级索引 多少工作用于排除其他用户
user 只建 user_id 缩小用户范围后，排序还在不在
composite 用户、日期、编号 能否连贯地定位并按序返回
covering 在末尾加入金额 是否减少表记录访问
time_first 改为日期优先 目标用户分散时，是否检查更多候选

这里显式执行 ANALYZE，是为了让各份教学数据库都具有自己的统计信息，不是建议每个查询前都分析一次。实际应用应按引擎版本和生命周期管理统计；SQLite 当前文档推荐通过 PRAGMA optimize 适时触发必要分析，其中较新的自动限额行为有版本要求。SQLite 统计信息维护

实验结论怎样写才不越界

本机使用 Python 3.11.0、SQLite 3.38.4。脚本会自行打印 SQLite 版本和各阶段的实际计划；系统上 Python 的版本号不能替代它所链接的 SQLite 版本号。官方文档于 2026-10-03 核对，不代表本机运行库已经是最新版本。

在当前实验中应首先检查五份 rows 完全相同，再观察访问策略和 vm_callbacks 的变化。不要要求别的版本打印相同的计划节点编号，也不要把“回调次数降低”改写成“生产数据库快了同样倍数”。

下面是本机这次实际输出的摘要，数据量 20,000，随机种子 45892，参数为 (7,180,5)。五份结果的订单编号依次都是 17617、1404、10004、19234、2959。

配置 当前环境观察到的策略 虚拟机回调次数
none 扫描表，额外排序 60667
user 按用户搜索，额外排序 1024
composite 按用户和日期搜索 55
covering 覆盖搜索，无需取得表中金额 49
time_first 覆盖索引上的 skip-scan 1870

最后一行很值得注意：本机计划出现 ANY(created_at) AND user_id=?，说明优化器采用了 skip-scan，并非只能从日期开头逐项走到底。这正好说明最左前缀有适用范围，不能用一句口诀替代实际观察。计划中日期约束显示为 created_at>?，也不要据此把原 SQL 的 >= 改成 >；完整结果已按原查询语义独立核验。

指标 本实验是否检查 为什么有这个边界
返回值与排序 是 可以与独立参考实现逐项比较
查询计划 打印并在当前环境检查 优化器和文本格式有版本差异
虚拟机进度回调 是 仅为同环境下的工作量线索
磁盘读写、缓存命中 否 数据库在内存中
并发、锁等待、网络延迟 否 本实验没有多个客户端
写入成本与长期空间增长 否 需要另外设计工作负载

不能只记住索引成功的例子

假设 created_at 上有常规索引，但查询把列放进算术表达式：

1
2
3
SELECT id
FROM orders
WHERE created_at + 1 >= 181;

在本例“非空且取值很小的整数日编号”范围内，可以改写为 created_at >= 180。两者逻辑等价，但优化器不一定为任意表达式做这种代数转换。若业务本就查询某个表达式，也可评估匹配的表达式索引；SQLite 要求查询中的表达式与索引表达式对应，不能只因为数学上等价就假定匹配。SQLite 表达式索引

这个改写有适用条件：不要将它直接推广到可能溢出的整数、浮点数、NULL 或时区转换。优化之前必须先证明业务语义不变。

常见误区 更稳妥的做法
每列都建一个索引就够了 从查询的筛选、排序与返回列共同设计
范围条件右边的列彻底失效 区分搜索边界、后续过滤、排序和覆盖用途
WHERE 写法顺序决定联合索引顺序 检查索引键顺序与约束类型
看见 SCAN 就强制改成索引 先看表大小、返回比例和估算代价
覆盖索引越宽越好 将读取收益与空间、写入成本一起评估
LIMIT 一样，返回前几条就应一样 用唯一的完整排序键消除平局
EXPLAIN 文本可以当通用断言 将结果不变量与版本相关观察分开
拼接用户输入形成 SQL 使用参数绑定；标识符选择使用固定允许列表

把验证和排障接起来

本站验证程序提取上面的完整 Python 代码直接执行，再用另一份基于字典记录的筛选排序实现核对查询。测试包含空表、单行、重复日期、零条 LIMIT、找不到用户、边界日期，以及随机数据上的多种索引配置。比较的是完整有序结果，不只是行数。

验证脚本为 tests/verify-sqlite-index-article.cjs。本次通过 104 组小数据集和五种索引配置的测试，加上固定的 20,000 行实验，共完成 14,045 次结果比较，其中主查询逐项检查了 42,100 条有序结果；另有 2,080 次非法参数拒绝检查。表达式改写与增加非覆盖列也经过独立核对。查询计划文本不作为跨版本固定断言。

从网络请求与 TCP看，数据库查询只是一次请求中的一个环节；页面慢还可能来自连接、传输或应用计算。反过来，NumPy 数组与广播处理的是读入内存后的数据布局与运算，不能把它的数组下标与数据库索引混为一谈。

下一次遇到“加了索引为什么没变快”，先问四件事：条件是否缩小了候选、顺序是否能直接提供、是否需要回表、实际瓶颈是否真的在查询。返回计算机基础专题或资源索引继续阅读。