尧图网站建设 
【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 条索引前后结果逐项一致本机 SQLite3.45.3实际通过检查15 项运行后先检查版本、原计划、新计划和结果一致性。这里实际通过十五项检查第一条返回记录为用户四十二、时间整数一七〇〇〇〇九九四二完整数字也在程序输出中。测试包含跨用户范围、额外金额字段、表达式改写和参数绑定不只验证最顺利的一条路径。下一次遇到订单列表慢可以沿三个问题继续往下问筛选真正缩小了多少范围排序能否直接利用既有顺序返回字段是否需要再查表。你自己的查询更接近“某个用户的最新订单”还是“全站最新订单”这两个看似相近的页面值得分别设计和验证索引。附录完整可运行实验脚本中的检查基于本文人工样本。执行计划观察随版本可能不同出现提示时保留完整输出检查原因不将这些字符串断言直接放入生产业务逻辑。A disposable, in-memory SQLite index experiment. Python 3.10.importjsonimportsqlite3 QUERYSELECT user_id, created_at FROM orders WHERE user_id ? ORDER BY created_at DESC LIMIT 20defmain():consqlite3.connect(:memory:,cached_statements0)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,1700000000i,100i%9000)foriinrange(1,10001)))con.commit()defplan(sql,args()):return[r[3]forrincon.execute(EXPLAIN QUERY PLAN sql,args)]beforeplan(QUERY,(42,))expectedcon.execute(QUERY,(42,)).fetchall()con.execute(CREATE INDEX idx_user_time ON orders(user_id, created_at))con.execute(ANALYZE)afterplan(QUERY,(42,))actualcon.execute(QUERY,(42,)).fetchall()extraQUERY.replace(user_id, created_at FROM,user_id, created_at, amount_cents FROM)acrossQUERY.replace(user_id ?,user_id ?)expressionQUERY.replace(user_id ?,user_id 0 ?)cross_planplan(across,(42,))expr_planplan(expression,(42,))extra_planplan(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_id42).fetchone()[0]100,result_20:len(actual)20,same_results:actualexpected,descending:actualsorted(actual,keylambdar:r[1],reverseTrue),latest_value:actual[0](42,1700009942),before_scan:any(SCAN ordersinsforsinbefore),before_sort:any(TEMP B-TREEinsforsinbefore),after_search:any(SEARCH ordersinsforsinafter),after_covering:any(COVERING INDEX idx_user_timeinsforsinafter),after_no_sort:notany(TEMP B-TREEinsforsinafter),extra_not_covering:notany(COVERINGinsforsinextra_plan),range_needs_sort:any(TEMP B-TREEinsforsincross_plan),expression_same_results:con.execute(expression,(42,)).fetchall()actual,bound_parameter_not_sql:con.execute(QUERY,(42 OR 11,)).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_asciiFalse,indent2))ifnotall(checks.values()):raiseSystemExit(Plan observation differs; inspect SQLite version and output.)if__name____main__:main()技术资料SQLite 查询规划EXPLAIN QUERY PLAN优化器与跳跃扫描表达式索引统计信息与 ANALYZErowid 表Python sqlite3 与参数绑定 【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 条索引前后结果逐项一致本机 SQLite3.45.3实际通过检查15 项运行后先检查版本、原计划、新计划和结果一致性。这里实际通过十五项检查第一条返回记录为用户四十二、时间整数一七〇〇〇〇九九四二完整数字也在程序输出中。测试包含跨用户范围、额外金额字段、表达式改写和参数绑定不只验证最顺利的一条路径。下一次遇到订单列表慢可以沿三个问题继续往下问筛选真正缩小了多少范围排序能否直接利用既有顺序返回字段是否需要再查表。你自己的查询更接近“某个用户的最新订单”还是“全站最新订单”这两个看似相近的页面值得分别设计和验证索引。附录完整可运行实验脚本中的检查基于本文人工样本。执行计划观察随版本可能不同出现提示时保留完整输出检查原因不将这些字符串断言直接放入生产业务逻辑。A disposable, in-memory SQLite index experiment. Python 3.10.importjsonimportsqlite3 QUERYSELECT user_id, created_at FROM orders WHERE user_id ? ORDER BY created_at DESC LIMIT 20defmain():consqlite3.connect(:memory:,cached_statements0)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,1700000000i,100i%9000)foriinrange(1,10001)))con.commit()defplan(sql,args()):return[r[3]forrincon.execute(EXPLAIN QUERY PLAN sql,args)]beforeplan(QUERY,(42,))expectedcon.execute(QUERY,(42,)).fetchall()con.execute(CREATE INDEX idx_user_time ON orders(user_id, created_at))con.execute(ANALYZE)afterplan(QUERY,(42,))actualcon.execute(QUERY,(42,)).fetchall()extraQUERY.replace(user_id, created_at FROM,user_id, created_at, amount_cents FROM)acrossQUERY.replace(user_id ?,user_id ?)expressionQUERY.replace(user_id ?,user_id 0 ?)cross_planplan(across,(42,))expr_planplan(expression,(42,))extra_planplan(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_id42).fetchone()[0]100,result_20:len(actual)20,same_results:actualexpected,descending:actualsorted(actual,keylambdar:r[1],reverseTrue),latest_value:actual[0](42,1700009942),before_scan:any(SCAN ordersinsforsinbefore),before_sort:any(TEMP B-TREEinsforsinbefore),after_search:any(SEARCH ordersinsforsinafter),after_covering:any(COVERING INDEX idx_user_timeinsforsinafter),after_no_sort:notany(TEMP B-TREEinsforsinafter),extra_not_covering:notany(COVERINGinsforsinextra_plan),range_needs_sort:any(TEMP B-TREEinsforsincross_plan),expression_same_results:con.execute(expression,(42,)).fetchall()actual,bound_parameter_not_sql:con.execute(QUERY,(42 OR 11,)).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_asciiFalse,indent2))ifnotall(checks.values()):raiseSystemExit(Plan observation differs; inspect SQLite version and output.)if__name____main__:main()技术资料SQLite 查询规划EXPLAIN QUERY PLAN优化器与跳跃扫描表达式索引统计信息与 ANALYZErowid 表Python sqlite3 与参数绑定