【SQLite】从零开始学数据库索引——用 EXPLAIN 找出慢查询-平芜编程栈

【SQLite】从零开始学数据库索引——用 EXPLAIN 找出慢查询

订单列表只显示某个用户最近二十条记录，表里却存着所有人的订单。数据少时，这条 SQL 几乎没有存在感；数据一多，列表开始等待。有人建议给用户编号加索引，也有人建议给创建时间加索引：到底该选哪一个？

与其先背索引口诀，不如做一个能重复的小实验。本文使用 Python 自带的 SQLite，在内存中创建一万条订单，观察同一条查询建索引前后的执行计划，再故意改动返回字段、筛选条件和表达式，看看原来的优势如何变化。重点是理解读取路径，不是拿微秒级计时许诺生产收益。

1. 把“查询慢”缩小成一个问题

我们需要的不是所有订单，而是用户四十二最近的二十条。要回答三个问题：数据库如何找到这个用户，如何按时间排列，返回的字段从哪里取。筛选、排序、取值是同一条查询中的不同工作，某个索引可能只帮到其中一部分。

实验表包含订单编号、用户编号、创建时间和金额。时间使用整数模拟，金额以分存储，避免把浮点金额精度也混进索引练习。这里没有真实客户资料、网络连接、线上写操作，也不需要安装独立数据库服务。

SELECTuser_id,created_atFROMordersWHEREuser_id=?ORDERBYcreated_atDESCLIMIT20;

问号是绑定参数的位置，运行时传入整数四十二。不要把网页输入直接拼接进 SQL。参数绑定解决值的传递和转义问题，并不保证查询必然快速；安全写法与索引设计需要分别做好。表名、排序方向等结构也不能随意接受用户字符串。

一万条订单平均分给一百个用户，每个用户一百条。这个分布是为了让实验容易解释，不代表真实电商流量。真实系统可能有少数用户占据大量订单，优化器面对这样的数据时，估计和选择都可能不同。

返回二十条也不等于只检查二十条。如果数据库还不知道谁的记录符合条件、哪条最新，它可能需要读取更多数据才能交出最终结果。LIMIT 限制的是结果数量，不能被当成固定工作量的承诺。

2. 索引保存的，不是一个“加速开关”

可以把订单表想象成一摞记录。业务要按用户找，而记录并不一定按这个顺序摆放。索引为相关字段维护另一种有序组织，让查询有机会定位到较小范围，不必每次从头检查所有记录。

这次候选索引按用户编号、创建时间排列。它先比较用户编号；用户相同时，再比较创建时间。理解这种先后关系，比记住“联合索引”四个字更重要：列顺序决定哪些记录挨在一起，以及局部范围内部是什么顺序。

本文使用普通 rowid 表，声明为 INTEGER PRIMARY KEY 的订单编号具有相应的行标识语义。不要把这个实验直接套到 WITHOUT ROWID 表，或认为所有数据库的叶子节点都保存完全一样的内容。我们只借用“额外目录”的直观比喻，不把纸张插画当成磁盘页结构图。

为什么不分别建用户索引和时间索引？因为目标是一条同时筛选用户、按时间取前几项的查询，两个独立目录不等于一个按联合顺序组织的目录。是否能组合多个索引取决于数据库和查询形式，不能自行假设它会把两者的好处简单相加。

索引也没有改变 SQL 所表达的业务目标。没有 ORDER BY 就不能依赖返回顺序；有了索引以后碰巧“看起来有序”，不意味着应用可以删除排序条件。执行方式会变化，而明确写出的查询语义才是读者可以依靠的约定。

3. 先记录原计划，再建立索引

在完整脚本中，先创建表、插入数据，然后执行 EXPLAIN QUERY PLAN。Python 获取的是多列结果，我们取每行的 detail 描述，避免把节点编号也写成必须背下来的答案。本机使用 SQLite 3.45.3，原查询得到以下两行：

如果第一次接触 Python 的数据库接口，可以先看连接和取值这两步。connect 的冒号内存参数创建一次性数据库；execute 提交语句和参数，fetchall 把本次结果读出来。批量插入使用 executemany，数据通过生成器逐条提供，没有先构造一万个庞大的字典对象。插入后提交事务，再观察查询，能让实验阶段更清楚。

脚本关闭了连接层的语句缓存，目的是减少同一连接中反复改变结构、查看同名查询时的干扰，不是建议线上一律禁用缓存。查询参数使用单元素元组，四十二后面的逗号不能省略成普通括号表达式。若运行时报参数数量或类型错误，先检查传参，而不是修改索引来解决无关问题。

SCAN orders USE TEMP B-TREE FOR ORDER BY

第一行表明这里使用扫描路径，第二行说明为 ORDER BY 采用了临时排序结构。这是理解本次执行策略的线索，不是已经测量出的总耗时，也没有给出实际访问页数。更不能仅凭这两行推断磁盘一定发生了多少次读写。

接下来建立候选索引，再收集实验统计信息：

CREATEINDEXidx_user_timeONorders(user_id,created_at);ANALYZE;

实验中的 ANALYZE 面对的是一次性内存数据库，用来帮助优化器了解刚生成的数据。不要不加判断地在繁忙线上照抄所有维护命令。真实环境需要结合 SQLite 版本、数据变化、维护策略与开销，安排统计信息更新。

SEARCH orders USING COVERING INDEX idx_user_time (user_id=?)

新的描述指出按用户条件搜索，使用了候选覆盖索引，之前的额外排序描述不再出现。原查询和参数没有变，二十条结果逐项相同，说明我们改变的是到达结果的路径，而不是通过少查数据、改错筛选来制造更好看的数字。

执行计划文本是交互诊断输出，不是稳定的应用接口。不同 SQLite 版本可能调整文字、节点和计划；文末脚本的字符串检查只用于固定样本教学。升级后某项观察不同，先检查版本与实际输出，不要据此宣布数据库发生了正确性故障。

4. 为什么这两个字段能一起起作用

用户编号的等值条件把搜索限定在同一个用户的范围内。范围内部已经按创建时间排列，因此可以朝需要的方向读取最新记录。虽然创建索引时没有写 DESC，本次查询仍能利用反向遍历满足单个用户的时间倒序。

把查询想象成先打开用户四十二的抽屉，再从较新的时间位置读取，比较容易理解它为什么不需要把所有人的订单重新排一次。这里成立的关键不是“时间列被索引了”，而是前面的用户列已经被等值固定。

如果顺序改成时间在前、用户在后，整份目录首先按时间排列。寻找某个用户时，可能沿时间方向检查许多属于别人的记录。这样的索引并非对所有查询都不好：如果业务主要展示全站最新订单，它可能更贴近需求。索引顺序应该由查询组合决定，而不是由字段名称决定。

本文时间值刻意唯一，因此降序结果没有并列。真实订单可能同一毫秒生成多条，分页通常还需要订单编号作为稳定的次级排序字段。增加次级排序后，要把完整 ORDER BY 一起检查，不能继续用只含一个时间条件的实验结论代替验证。

还有一个容易被“最左前缀”口诀遮住的细节：没有前导列约束，不代表任何情况下都不可能利用该索引。扫描覆盖索引、跳跃扫描等选择可能存在，具体取决于查询、分布和优化器。更准确的问题是：当前计划用了哪些条件缩小搜索范围，又在哪里继续做了工作？

5. 多返回一个金额，计划为什么变了

原查询只返回用户和时间，这两个字段都在候选索引里。对这条查询而言，可以直接从索引取得所需值，这就是本例中 COVERING 的含义。它描述索引与查询的关系，而不是另一种必须使用专门语法创建的神秘对象。

现在把 SELECT 扩充为用户、时间和金额，筛选排序不变。本机输出变成下面这样：

SEARCH orders USING INDEX idx_user_time (user_id=?)

搜索条件仍然有效，排序优势也没有因此全部消失，但金额不在候选索引中，需要取得表里的值。所以“没有 COVERING”与“索引完全失效”不是一回事。排查时应分清定位范围、消除排序和减少查表这三种收益。

是否把金额也加进索引？先看这个查询有多重要、执行多频繁、一次返回多少条。为高频读路径增加覆盖列可能值得，但字段越宽，索引占用和维护成本也可能增长。不能为了让计划多一个漂亮单词，把整张表所有字段再复制进一个大索引。

这也是少用无意识 SELECT * 的原因。应用只需要两列，却请求所有列，不仅影响覆盖机会，还可能增加数据传输与对象创建。但反过来也不要为了覆盖而删掉业务真正需要的字段；先守住结果需求，再选择合理的读取路径。

6. 改一个符号，原来的排序优势就可能消失

把用户条件从等于四十二改为大于等于四十二，就不再是一个用户的局部范围，而是多个用户的集合。每个用户内部时间有序，拼在一起却不是全局按时间排列。实验仍使用候选索引搜索，但重新出现了临时排序描述。

SEARCH orders USING COVERING INDEX idx_user_time (user_id>?) USE TEMP B-TREE FOR ORDER BY

注意，SQL 写的是大于等于，计划里的简略描述显示大于，这是本机的诊断文字，不代表数据库擅自改变了 SQL 条件。判断查询语义要看实际 SQL 与结果，不要把计划中的提示字符串当成一条可以直接执行的重写语句。

再试一次，把等值左侧改成 user_id + 0。本样本的字段是非空整数，两种写法结果相同；但本机计划成为覆盖索引扫描，并出现额外排序。它展示了一个重要区别：数学上等价，不代表优化器会把每种表达式都转回同一条索引搜索路径。

不能因此归纳为“出现函数必然完全不用索引”。SQLite 支持表达式索引，有自己的匹配限制；而本实验即使没有按用户点查，仍扫描了覆盖索引。描述具体丢失的能力，比笼统贴上“失效”标签更有助于解决问题。

当你遇到计划与预期不符，先确认字段类型、比较表达式、排序规则、参数值和实际命中比例。将函数移到参数端也要确保语义一致，不能为了迎合索引把时区、大小写、空值或精度处理偷偷改变。

7. 六种常见误判，逐个拆开

第一，只看到 SEARCH 就认为查询一定快。搜索命中了较大范围，仍然可能返回很多行；下游还可能排序、连接或生成大量对象。把计划作为结构证据，再用代表性数据测量整个请求，才有完整判断。

第二，只看到 SCAN 就立刻加索引。小表或需要大部分记录的查询，扫描未必不合理；扫描有时也沿着索引顺序进行。应先确认成本出在哪里，避免为了消灭一个单词增加长期写入负担。

第三，把建索引后的第一次运行与无索引冷启动直接比较。缓存、连接建立、解释器启动、磁盘状态和后台负载都能影响结果。本文只报告计划与结果一致性，不把内存小样本当成正式压测。需要计时时，先规定预热、重复次数和观测范围。

第四，在不同数据或参数上做前后对比。一个大用户与一个只有三条订单的用户，命中比例差别很大；测试时顺手改了 LIMIT，也会改变返回工作量。至少保存查询、参数、版本、数据规模和结果摘要，确保比较的是同一个问题。

第五，给每一列都建索引。额外目录占据空间，插入和更新时还要维护；索引中字段被修改时也有相关成本。查询得到的收益，需要与写入延迟、存储和维护开销一起衡量，没有通用的“越多越保险”。

第六，直接在生产库运行教程的建表、建索引语句。索引创建可能涉及大量读取、写入和锁等待，具体影响取决于环境。先在隔离副本验证兼容性与计划，再按应用的维护流程实施；本文脚本只连内存数据库，不打开任何已有业务文件。

8. 把自己的慢查询放进这个实验框架

先固定查询语义与参数，记录版本和基线结果。再提出一个明确的索引假设，例如“用用户等值条件缩小范围，顺便按时间输出”。每次只改变一个候选设计，查看计划变化，并确保返回结果一致。这样才能解释优化来自哪里。

测试数据至少覆盖普通用户、热门用户、空结果和边界时间。再考虑数据持续增长、写入频率和分页方式。我们的一百个均匀用户只适合展示机制，不足以为所有负载选择最终索引。真实应用的字段分布，比索引名字起得漂亮更重要。

调整实验规模时，可以先增加生成订单的范围，再改变用户分配公式，分别观察行数增长和分布倾斜的影响。不要同时修改三个因素，否则即使计划变化，也很难判断是哪一步促成。内存数据库消除了文件持久化这部分背景；转向文件数据库以后，还应重新测量缓存、存储与并发带来的影响，不能沿用内存实验的速度印象。

文末程序会生成全部数据，不依赖外部下载。保存为 index_lab.py，用 Python 3.10 或更新版本运行下列命令即可。程序退出后内存数据库消失，不会留下订单库，也不会覆盖已有文件。

python index_lab.py

验证项	实际结果
人工订单	10,000 条
每个用户	100 条
查询返回	20 条
索引前后结果	逐项一致
本机 SQLite	3.45.3
实际通过检查	15 项

运行后先检查版本、原计划、新计划和结果一致性。这里实际通过十五项检查，第一条返回记录为用户四十二、时间整数一七〇〇〇〇九九四二；完整数字也在程序输出中。测试包含跨用户范围、额外金额字段、表达式改写和参数绑定，不只验证最顺利的一条路径。

下一次遇到订单列表慢，可以沿三个问题继续往下问：筛选真正缩小了多少范围，排序能否直接利用既有顺序，返回字段是否需要再查表。你自己的查询更接近“某个用户的最新订单”，还是“全站最新订单”？这两个看似相近的页面，值得分别设计和验证索引。

附录：完整可运行实验

脚本中的检查基于本文人工样本。执行计划观察随版本可能不同，出现提示时保留完整输出检查原因，不将这些字符串断言直接放入生产业务逻辑。

"""A disposable, in-memory SQLite index experiment. Python 3.10+."""importjsonimportsqlite3 QUERY='''SELECT user_id, created_at FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20'''defmain():con=sqlite3.connect(':memory:',cached_statements=0)con.execute('''CREATE TABLE orders( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, created_at INTEGER NOT NULL, amount_cents INTEGER NOT NULL)''')con.executemany('INSERT INTO orders VALUES (?,?,?,?)',((i,i%100,1700000000+i,100+i%9000)foriinrange(1,10001)))con.commit()defplan(sql,args=()):return[r[3]forrincon.execute('EXPLAIN QUERY PLAN '+sql,args)]before=plan(QUERY,(42,))expected=con.execute(QUERY,(42,)).fetchall()con.execute('CREATE INDEX idx_user_time ON orders(user_id, created_at)')con.execute('ANALYZE')after=plan(QUERY,(42,))actual=con.execute(QUERY,(42,)).fetchall()extra=QUERY.replace('user_id, created_at FROM','user_id, created_at, amount_cents FROM')across=QUERY.replace('user_id = ?','user_id >= ?')expression=QUERY.replace('user_id = ?','user_id + 0 = ?')cross_plan=plan(across,(42,))expr_plan=plan(expression,(42,))extra_plan=plan(extra,(42,))checks={'rows_10000':con.execute('SELECT count(*) FROM orders').fetchone()[0]==10000,'user_rows_100':con.execute('SELECT count(*) FROM orders WHERE user_id=42').fetchone()[0]==100,'result_20':len(actual)==20,'same_results':actual==expected,'descending':actual==sorted(actual,key=lambdar:r[1],reverse=True),'latest_value':actual[0]==(42,1700009942),'before_scan':any('SCAN orders'insforsinbefore),'before_sort':any('TEMP B-TREE'insforsinbefore),'after_search':any('SEARCH orders'insforsinafter),'after_covering':any('COVERING INDEX idx_user_time'insforsinafter),'after_no_sort':notany('TEMP B-TREE'insforsinafter),'extra_not_covering':notany('COVERING'insforsinextra_plan),'range_needs_sort':any('TEMP B-TREE'insforsincross_plan),'expression_same_results':con.execute(expression,(42,)).fetchall()==actual,'bound_parameter_not_sql':con.execute(QUERY,('42 OR 1=1',)).fetchall()==[],}report={'sqlite_version':sqlite3.sqlite_version,'before':before,'after':after,'extra_column':extra_plan,'range':cross_plan,'expression':expr_plan,'first_result':actual[0],'checks':checks,'passed':sum(checks.values()),'scope':'10000 synthetic in-memory rows; no timing benchmark'}con.close()print(json.dumps(report,ensure_ascii=False,indent=2))ifnotall(checks.values()):raiseSystemExit('Plan observation differs; inspect SQLite version and output.')if__name__=='__main__':main()