有一次线上反馈说某个接口要 5 秒多排查下来 SQL 其实很简单就是在订单表里按用户和日期查最近几条记录。表结构没问题数据量也就两百多万行索引也都建了可 EXPLAIN QUERY PLAN 一看居然走了一个选择性很差的 status 索引把几万条状态为已完成的记录全部扫描出来再逐行过滤。那一刻我意识到SQLite 的优化器虽然多数时候靠谱但它真选错索引时不会像有些数据库那样给出明显的信号。而 SQLite 里恰好有一个冷门又好用的特性叫INDEXED BY能让你直接插手执行计划强制查询走某条索引。这篇文章就围绕这个特性展开讲讲它的语法、适用场景、实际案例和容易踩的坑适合正在用 SQLite 做应用、又对查询性能有要求的开发者参考。1. 当优化器选错索引一个典型的性能事故现场1.1 事故重现同样的表和数据为什么执行计划不一样先还原一下我当时遇到的情况。订单表 orders 大概长这样CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, status INTEGER NOT NULL, amount REAL NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); CREATE INDEX idx_orders_status ON orders(status);数据大约 230 万行user_id 区分度很高status 只有几种取值。查询是后台列表页的核心语句SELECT * FROM orders WHERE user_id 12345 AND created_at 2025-01-01 ORDER BY created_at DESC LIMIT 20;按照常识最理想的执行计划应该走idx_orders_user_created先用 user_id 精确匹配再用 created_at 做范围过滤。可实际上 EXPLAIN QUERY PLAN 告诉我的却是QUERY PLAN |--SEARCH orders USING INDEX idx_orders_status (status?) |--ORDER BY created_at DESC --USE TEMP B-TREE FOR ORDER BY是的优化器选择了 status 索引。为什么因为 status 的所有取值加起来覆盖了订单表大约 78% 的行走这个索引等于先把大量不相关数据读出来再做 user_id 和时间的过滤最后还因为索引无法排序额外加了一次临时 B 树排序。整个查询慢了 30 倍左右从最初的几十毫秒直接掉到好几秒。这类问题的典型特征是表结构合理、索引俱全但统计信息或者优化模型让优化器做出了反直觉的选择。别以为只有 SQLite 会这样MySQL、PostgreSQL 都有类似情况只不过 SQLite 的修复手段相对隐蔽INDEXED BY 就是其中关键的一招。1.2 排查工具链用 EXPLAIN QUERY PLAN 定位执行计划遇到这种问题第一件事不是猜而是把执行计划打出来看。SQLite 自带的最直接的工具就是EXPLAIN QUERY PLAN。我个人的习惯是先在 sqlite3 命令行里开启三个开关sqlite3 your.db .timer on .eqp on.eqp on会自动在每个查询前打印执行计划.timer on会显示真实耗时。比如你输入那条订单查询就能立刻看到走的是哪个索引、有没有临时排序、预估扫描多少行。如果你用的是图形工具比如 DB Browser for SQLite、DBeaver、SQLiteStudio 这类通常也都有执行计划面板。这些工具对新手更友好可以图形化看到 SEARCH、SCAN、USING INDEX 这些信息甚至能直接看到每一步的父节点与子节关系。查索引时最需要盯住两个词SEARCH说明走索引查找定位行数少好。SCAN说明全表扫描或者全索引扫描数据量大时往往就是问题根源。另外USING TEMP B-TREE FOR ORDER BY这行也很关键它代表排序没被索引覆盖需要在临时空间单独排。这类额外操作在数据量大时极伤性能后面会展开讲。1.3 根因SQLite 的统计信息与优化器的决策逻辑为什么优化器放着区分度那么高的 user_id 索引不用偏去走 status 索引这得从 SQLite 优化器的成本模型说起。SQLite 的成本估算依赖sqlite_stat1表里的统计信息而这个统计信息是执行ANALYZE命令生成的。如果你从来没在数据库上跑过 ANALYZE那优化器只能按一套保守的默认规则来猜最典型的结果是它认为所有单列索引的区分度差不多然后按照索引的某些内部顺序选择一个看起来合理的索引。sqlite_stat1的 stat 字段是一些用空格分隔的数字。比如SELECT * FROM sqlite_stat1; -- 输出类似 -- orders|idx_orders_status|2300000 3 -- orders|idx_orders_user_created|2300000 1150000第一个数字是表的总行数估算值后面的数字依次表示索引第一列、前两列、前三列……的组合区分度。如果这些数字因为数据更新而严重失真优化器就会算错成本。比如订单表做了一次大规模数据迁移把十年前的订单都塞了进来而旧的 stat 信息还停留在几万行时评估结果自然全乱套。我在实际排障中还发现一个很容易忽略的点ANALYZE 收集的统计信息不是自动更新的。你在事务里批量插入了 20 万行数据统计信息不会跟着变。索引当然会同步更新但优化器的眼睛还是老数据它做的每一个成本判断都可能偏离事实。所以很多同一套代码、同一个数据库换个环境就变慢的诡异现象根子就在这里。2. INDEXED BY 语法与行为边界它是一道命令不是一条建议2.1 基本语法SELECT / DELETE / UPDATE 中的写法INDEXED BY 的位置放在表名之后作用是告诉 SQLite访问这张表时必须使用我指定的索引没有商量余地。最基本的 SELECT 写法SELECT * FROM orders INDEXED BY idx_orders_user_created WHERE user_id 12345 AND created_at 2025-01-01 ORDER BY created_at DESC LIMIT 20;如果表起了别名子句要写在别名后面SELECT * FROM orders AS o INDEXED BY idx_orders_user_created WHERE o.user_id 12345;DELETE 和 UPDATE 同样支持DELETE FROM orders INDEXED BY idx_orders_user_created WHERE user_id 12345 AND created_at 2024-01-01; UPDATE orders INDEXED BY idx_orders_user_created SET status 9 WHERE user_id 12345 AND created_at 2024-01-01;语法本身非常简单难的是理解它的语义。它不像 MySQL 的FORCE INDEX、USE INDEX那样偏向建议SQLite 的 INDEXED BY 是实打实的命令。只要指定了查询计划就必然会尝试使用该索引而不是去挑别的。2.2 行为边界强约束带来的报错与限制正因为它是命令所以有几个边界行为必须搞清楚不然上线后等着你的就是查询直接报错。第一索引必须存在而且名字必须写对。写错了 SQLite 会直接报no such index。这个看似简单但很多项目里索引名靠代码自动生成一旦命名规则不一致迁移环境时容易翻车。第二当指定索引无法满足查询时SQLite 不会默默把索引忽略掉而是直接报no query solution。我遇到比较多的情况是部分索引partial index。假设你建了一个只针对 status1 的部分索引CREATE INDEX idx_orders_user_active ON orders(user_id, created_at) WHERE status 1;然后查询里根本没有约束 status只是 user_id、created_at 条件这时如果你强制INDEXED BY idx_orders_user_activeSQLite 无法只用这个索引完成查询就会报错。这一特性既可以说是保护也可以说是陷阱必须对索引定义有精确认知。第三INDEXED BY 和 NOT INDEXED 仅适用于普通 rowid 表对 WITHOUT ROWID 表不支持。这个细节在官方文档里有明确说明。如果项目里为了省空间用了 WITHOUT ROWID 表又想用 INDEXED BY得先确认版本和表类型否则就是白忙活。提示INDEXED BY 的适用面其实比你想象得窄。它适合解决优化器判断失误这一类问题而不是用来替代合理的索引设计。2.3 NOT INDEXED 的对照实验量化索引收益的土办法与 INDEXED BY 相对的SQLite 还提供了一个NOT INDEXED子句允许你显式禁止使用索引强制全表扫描。这两个子句搭配起来可以很方便地做性能对照实验量化某个索引到底值不值得保留。比如你怀疑 idx_status 这个索引对查询有帮助但又不确定可以分别跑两遍SELECT count(*) FROM orders NOT INDEXED WHERE status 1; SELECT count(*) FROM orders INDEXED BY idx_orders_status WHERE status 1;用.timer on观察耗时差异再配合 EXPLAIN QUERY PLAN 看具体执行路径。这种 A/B 对照是我在做索引优化时最常用的手法比纸上谈兵分析成本模型直观得多。有一次我就是通过这个土办法发现一个教训一个看似没用的索引在某种特定查询条件下其实有奇效而另一个看起来很有用的复合索引因为列顺序排错实际帮助几乎为零。没有 NOT INDEXED 这种开关你很难干净利落地做这样的对照。3. 实战案例数据权限过滤查询为什么总走错索引3.1 案例背景一张订单表和一个复杂过滤查询上一节的语法看起来很容易上手但真实场景远比教科书复杂。我这里有一个印象比较深的案例是一个带数据权限的管理后台查询。背景是系统里的角色分区域查询语句在运行时会被拼上一堆权限条件结构大概是SELECT id, user_id, status, amount, created_at, region_id FROM orders WHERE region_id IN (1001, 1002, 1003) AND status IN (0, 1, 2) AND user_id 0 AND created_at 2025-03-01 ORDER BY created_at DESC LIMIT 100;表上有三个索引CREATE INDEX idx_orders_region ON orders(region_id); CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_created ON orders(created_at);理论上最合适的方案是先用idx_orders_created把时间过滤掉或者用idx_orders_region把区域收窄。可优化器偏偏挑了idx_orders_status因为它在所有单列索引里被提前创建且 SQLite 在没有足够统计信息时对单列索引的评估几乎一视同仁。结果就是性能时好时坏——区域参数不同、状态参数不同走的计划都不一样最夸张的时候一个列表页查询耗时 8.7 秒。3.2 优化器为什么不买复合索引的账我当时的第一个念头是建一个复合索引比如(region_id, created_at)。建完之后用 EXPLAIN QUERY PLAN 一看傻眼了走的还是 status 索引。为什么因为查询条件里既有 IN 又有范围条件而且还有user_id 0这种几乎无过滤效果的条件。优化器评估复合索引时要同时考虑每个条件的过滤度和索引扫描成本。region_id 和 status 的 IN 列表组合起来有多少种可能优化器算得并不精准尤其是这些字段的重复率差别很大时。还有一个更隐蔽的问题查询里所有条件都是可选项运行时权限不同拼出来的 SQL 条件集不同。对优化器来说它看到的似乎是一个全新形状的查询没办法稳定选择一个复合索引。这种情况下索引再多也只是给优化器提供更多猜错的机会。这类条件多变、排列组合极多的查询本质上是索引设计的一个难点。简单加索引往往解决不了问题反而会让优化器更加迷茫。3.3 用 INDEXED BY 修正执行计划并验证数据一致性当时为了快速止血我在核心查询上加上了 INDEXED BY直接指定idx_orders_createdSELECT id, user_id, status, amount, created_at, region_id FROM orders INDEXED BY idx_orders_created WHERE region_id IN (1001, 1002, 1003) AND status IN (0, 1, 2) AND user_id 0 AND created_at 2025-03-01 ORDER BY created_at DESC LIMIT 100;这样优化器只能从 created_at 索引开始扫描再回表过滤 region_id 和 status。EXPLAIN QUERY PLAN 变成了QUERY PLAN |--SEARCH orders USING INDEX idx_orders_created (created_at?) --ORDER BY created_at DESC由于 created_at 索引和 ORDER BY 排序方向一致连临时排序都没了。耗时从 8.7 秒降到了 120 毫秒左右效果立竿见影。这里要特别提一句强制索引后必须做一次结果集对比确认返回数据跟原来完全一致。我自己有个习惯会在测试环境分别跑优化前后的 SQL然后把结果按主键排序后做 diff。表面上看 SQLite 不会因为走不同索引而返回不同结果但如果你查询里带了子查询、LEFT JOIN 或者依赖某种隐式排序的写法结果顺序可能会变。哪怕 content 相同排序不同也会让分页接口出现问题。3.4 理解边界从掐住计划到清理根因INDEXED BY 帮我快速解决了线上问题但我很清楚它只是止血不是根治。后续我又做了几件事把根因处理掉重新设计了一个更贴合业务查询的复合索引(status, region_id, created_at)。在表结构稳定后执行ANALYZE orders让统计信息跟上真实数据分布。把那种条件可拼可不拼的动态 SQL 改成固定结构的模板用缺省值代替条件拼接让优化器每次面对的是同一个形状的查询。最终我甚至把 INDEXED BY 从代码里摘掉了让优化器自己去选新的复合索引。因为那条 SQL 已经变成了长得一致、索引匹配度高的稳定查询不再需要人肉干预。提示INDEXED BY 的最佳使用姿势是短期止血 长期优化。等索引、统计信息、SQL 结构都调整到位后应该重新评估是否还需要强制指定。4. 容易被忽略的索引隐形杀手排序规则、LIKE 与部分索引4.1 排序规则不一致索引明明存在却用不上有一种情况很恼人索引明明存在EXPLAIN QUERY PLAN 也显示它存在但查询就是不走或者走了也用不上。多数时候问题出在排序规则collation不匹配。SQLite 的索引默认建立在 BINARY 排序规则上但列可以被定义为TEXT COLLATE NOCASE或RTRIM。假设有张用户表CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT COLLATE NOCASE ); CREATE INDEX idx_users_username ON users(username);索引会继承列的 collation也就是 NOCASE。但如果你在查询里写了WHERE username Admin COLLATE BINARY或者用了某些函数导致表达式类型变化优化器会认为索引的排序规则无法匹配当前比较操作的语义于是放弃索引。这类问题在代码里最不容易发现因为你肉眼看着就是同一个字段、同一个条件。排查方法还是回到 EXPLAIN QUERY PLAN如果看到SCAN users但你又确信索引没问题优先检查查询条件里是否显式指定了 COLLATE或某个连接查询的列 collation 是否一致。4.2 LIKE 前缀匹配字面量能走索引参数化却会翻车LIKE 和索引之间的互动坑更多。SQLite 对 LIKE 做了优化如果 pattern 是常量字符串且不是以%或_开头那么 LIKE 可以被转换成范围查询从而使用索引。比如SELECT * FROM orders WHERE created_at LIKE 2025-03-%;这条语句的 LIKE 条件等价于 created_at 在某个时间范围内SQLite 可以直接用 created_at 索引。但如果你参数化了SELECT * FROM orders WHERE created_at LIKE :pattern || %;这时优化器没法在编译 SQL 阶段把 pattern 翻译成确定的范围值于是状态一下子倒退到全表扫描。解决思路也不复杂像这种场景干脆别用 LIKE 做前缀查询直接用范围比较SELECT * FROM orders WHERE created_at :start AND created_at :end;索引利用率高语义还更清晰。这不是 INDEXED BY 能解决的问题但属于索引策略里特别重要的一环——先写一个优化器能看懂的 SQL再谈要不要强制走索引。4.3 部分索引的边界行为强制指定时可能直接报错部分索引是 SQLite 3.8.0 之后引入的特性可以在建索引时加 WHERE 过滤让索引体积更小、命中更集中。比如只给活跃订单建索引CREATE INDEX idx_active_orders_user ON orders(user_id, created_at) WHERE status 1;这个索引在正常查询时很好用SELECT * FROM orders WHERE status 1 AND user_id 10086;但如果你在另一个查询里写SELECT * FROM orders INDEXED BY idx_active_orders_user WHERE user_id 10086;查询条件没有 status 1强制用这个部分索引就可能导致no query solution报错因为优化器发现索引本身的 WHERE 条件无法满足查询的语义需要。这是我实际测试中踩过的坑也和团队的同事讨论过很多次。结论是对 partial index 使用 INDEXED BY 前一定要再三确认查询条件覆盖了索引的 WHERE 约束否则代码可能跑到某个特殊参数分支时直接 500。5. 在真实工程里怎么用 INDEXED BY稳定优先还是性能优先5.1 嵌入式产品里最怕执行计划漂移INDEXED BY 如果只用于在线服务可能显得有点多余毕竟优化器大部分时候是聪明的。但在嵌入式产品里情况完全不同。嵌入式设备上的 SQLite数据量可能只有几万行但 CPU 和磁盘性能都极其有限而且经常要长期运行。设备端最常见的问题不是单次查询慢而是执行计划漂移。比如设备固件升级后某条 SQL 突然从走索引变成全表扫描用户感受到的就是界面卡顿。这时候使用 INDEXED BY 的意义不是追求极致性能而是追求确定性和稳定性。嵌入式系统没有专职 DBA 盯着也没法实时 EXPLAIN确保关键查询每次走同一条路径比偶尔更快、偶尔卡死重要得多。我见过不少工控上位机项目甚至直接把 INDEXED BY 写死在所有核心查询里换来的就是十年不换代码也不出性能故障。5.2 数据库版本升级后的计划突变另一个场景是 SQLite 版本升级。SQLite 的查询规划器在 3.8、3.16、3.24、3.35 等版本都有明显改动最典型的是对 OR 条件、LIKE、子查询的优化策略变化。你可能只是把 SQLite 从一个版本升到另一个版本结果一模一样的数据和 SQL执行计划全变了。我遇到过的情况是应用本来运行得好好的升级一次 SQLite 后某条统计报表 SQL 从 400 毫秒涨到了 11 秒。用 EXPLAIN QUERY PLAN 对比新旧版本发现优化器从用主键索引变成了扫描一个很大的辅助索引完全没有理由就是成本模型的参数变了。这种问题非常适合用 INDEXED BY 来固定计划。如果代码本身还会被部署到不同设备上各设备 SQLite 版本又不一样那更需要靠这个子句来锁死关键路径。5.3 和覆盖索引组合让查询少回一次表使用 INDEXED BY 时还可以考虑配合覆盖索引让查询完全不用回表读取原始行。覆盖索引的意思是查询需要的所有列都包含在索引里SQLite 直接扫描索引就拿到了全部数据。比如下面的查询SELECT user_id, status FROM orders INDEXED BY idx_orders_user_status WHERE user_id 10086 AND status 1;其中索引是CREATE INDEX idx_orders_user_status ON orders(user_id, status);EXPLAIN QUERY PLAN 会显示USING COVERING INDEX idx_orders_user_status意思是连回表都不需要所有数据都从索引页里拿。相比普通索引回表少一次随机 IO在机械硬盘或者网络文件系统上差别尤其明显。我做性能测试时会刻意用覆盖索引版本对比非覆盖版本经常发现整体耗时可以下降 30% 到 60%。但要注意覆盖索引不是越宽越好。索引列太多会让写入变慢、文件变大要平衡。5.4 发布前补一课ANALYZE 与 PRAGMA optimize 的正确节奏无论你最终是否使用 INDEXED BY有一点必须养成习惯关键表的数据结构稳定后执行一次 ANALYZE并且在大批量数据导入后重新 ANALYZE。SQLite 从 3.18 版本开始提供了PRAGMA optimize;它相当于做了一次轻量级 ANALYZE不会消耗太多时间。文档建议应用在每次 close 数据库连接前调用一次或者在启动后的空闲时段执行。我个人的节奏是表结构变更后立刻分析该表。批量导入超过表总量 10% 的数据后立刻分析该表。线上应用启动后在后台线程执行一次PRAGMA optimize;。这样能让优化器手里的统计信息尽量贴近实际情况大幅减少莫名走错索引的几率。有了这份底子INDEXED BY 才不是天天需要救火而是偶尔出场。6. 我替你们踩过的三个坑索引名、统计信息与迁移思维6.1 索引名带不带前缀写错就是硬报错INDEXED BY 里写的索引名必须和sqlite_master里记录的名称完全一致。别想着写成schema.index_name或者带表名前缀的错觉名比如-- 错误写法orders 表名混进去了 SELECT * FROM orders INDEXED BY orders_idx_user_created WHERE user_id 1;如果真实索引名是idx_user_created这句直接报no such index: orders_idx_user_created。这个问题在从 MySQL 迁移过来的代码里尤其常见因为 MySQL 的习惯是索引名可以自由取。SQLite 里还是老老实实用工具或 sqlite_master 把真实索引名列出来再写。另外当表的索引很多、命名又风格不统一时建议专门写一个查询脚本SELECT name, tbl_name, sql FROM sqlite_master WHERE type index ORDER BY tbl_name, name;上线前对着这个清单核对一遍能省掉后续大量排查时间。6.2 大批量导入后忘记重新 ANALYZE执行计划直接翻车我有一次在生产环境踩过一个挺丢人的坑凌晨用 UPSERT 批量导入了 20 万行数据自认为索引和统计信息都很完善直接在白天高峰期开放功能。结果查询响应时间直线上升数据库 CPU 飙升。后来定位到原因批量导入前我执行过 ANALYZE导入后没有重新执行。统计信息里估计的表行数还是导入前的几万行优化器完全低估了新数据量于是一个原本该走主键索引的查询被改成了全索引扫描把整个表都扫了一遍。从那以后我把大批量写入后重新 ANALYZE写进了运维手册里和备份、数据一致性检查放在同一个级别。如果你项目里也有定期的 ETL 或数据同步任务务必在任务结束时补上这一句ANALYZE orders;6.3 不要把 SQL Server / MySQL 的 FORCE INDEX 习惯带进来最后想聊一个思维层面的坑。很多人第一次接触 INDEXED BY 时会觉得它和 MySQL 的FORCE INDEX、USE INDEX差不多顺手就把 MySQL 的使用习惯带过来了。但在生产环境吃了几次亏后我发现两者差异非常大。MySQL 的 FORCE INDEX 某种程度上仍然允许优化器在成本差异过大时绕过建议而 SQLite 的 INDEXED BY 几乎没有回旋余地。你指定了这条索引就得走这条索引哪怕它根本不是最优解。也就是说它把优化器犯傻的风险变成了DBA/开发必须每次判断正确的责任。所以我现在的原则是能通过重建索引、重写 SQL、更新统计信息解决问题就优先用这些常规手段只有遇到执行计划漂移、版本升级行为变化、或者明确对比验证过特定索引更优时才动用 INDEXED BY。它是一把手术刀用得好能精准切开病灶用不好反而会制造另一个伤口。这些年用 SQLite 越深越觉得它的轻量并不等于简单粗暴很多看似不起眼的小功能在关键时刻能起到大作用。INDEXED BY 就是这样一个存在——平时你可能根本想不到它但真遇到执行计划不合理时它往往是成本最低、效果最直接的解决方案。希望这篇文章能帮你少走一些弯路下次再被 SQLite 的慢查询折磨时至少知道还有这么一张牌可以用。 
长河君 · 2026/9/14 4:40:19 · 阅读 1.2k SQLite索引优化：用INDEXED BY强制指定索引解决查询性能问题
有一次线上反馈说某个接口要 5 秒多排查下来 SQL 其实很简单就是在订单表里按用户和日期查最近几条记录。表结构没问题数据量也就两百多万行索引也都建了可 EXPLAIN QUERY PLAN 一看居然走了一个选择性很差的 status 索引把几万条状态为已完成的记录全部扫描出来再逐行过滤。那一刻我意识到SQLite 的优化器虽然多数时候靠谱但它真选错索引时不会像有些数据库那样给出明显的信号。而 SQLite 里恰好有一个冷门又好用的特性叫INDEXED BY能让你直接插手执行计划强制查询走某条索引。这篇文章就围绕这个特性展开讲讲它的语法、适用场景、实际案例和容易踩的坑适合正在用 SQLite 做应用、又对查询性能有要求的开发者参考。1. 当优化器选错索引一个典型的性能事故现场1.1 事故重现同样的表和数据为什么执行计划不一样先还原一下我当时遇到的情况。订单表 orders 大概长这样CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, status INTEGER NOT NULL, amount REAL NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); CREATE INDEX idx_orders_status ON orders(status);数据大约 230 万行user_id 区分度很高status 只有几种取值。查询是后台列表页的核心语句SELECT * FROM orders WHERE user_id 12345 AND created_at 2025-01-01 ORDER BY created_at DESC LIMIT 20;按照常识最理想的执行计划应该走idx_orders_user_created先用 user_id 精确匹配再用 created_at 做范围过滤。可实际上 EXPLAIN QUERY PLAN 告诉我的却是QUERY PLAN |--SEARCH orders USING INDEX idx_orders_status (status?) |--ORDER BY created_at DESC --USE TEMP B-TREE FOR ORDER BY是的优化器选择了 status 索引。为什么因为 status 的所有取值加起来覆盖了订单表大约 78% 的行走这个索引等于先把大量不相关数据读出来再做 user_id 和时间的过滤最后还因为索引无法排序额外加了一次临时 B 树排序。整个查询慢了 30 倍左右从最初的几十毫秒直接掉到好几秒。这类问题的典型特征是表结构合理、索引俱全但统计信息或者优化模型让优化器做出了反直觉的选择。别以为只有 SQLite 会这样MySQL、PostgreSQL 都有类似情况只不过 SQLite 的修复手段相对隐蔽INDEXED BY 就是其中关键的一招。1.2 排查工具链用 EXPLAIN QUERY PLAN 定位执行计划遇到这种问题第一件事不是猜而是把执行计划打出来看。SQLite 自带的最直接的工具就是EXPLAIN QUERY PLAN。我个人的习惯是先在 sqlite3 命令行里开启三个开关sqlite3 your.db .timer on .eqp on.eqp on会自动在每个查询前打印执行计划.timer on会显示真实耗时。比如你输入那条订单查询就能立刻看到走的是哪个索引、有没有临时排序、预估扫描多少行。如果你用的是图形工具比如 DB Browser for SQLite、DBeaver、SQLiteStudio 这类通常也都有执行计划面板。这些工具对新手更友好可以图形化看到 SEARCH、SCAN、USING INDEX 这些信息甚至能直接看到每一步的父节点与子节关系。查索引时最需要盯住两个词SEARCH说明走索引查找定位行数少好。SCAN说明全表扫描或者全索引扫描数据量大时往往就是问题根源。另外USING TEMP B-TREE FOR ORDER BY这行也很关键它代表排序没被索引覆盖需要在临时空间单独排。这类额外操作在数据量大时极伤性能后面会展开讲。1.3 根因SQLite 的统计信息与优化器的决策逻辑为什么优化器放着区分度那么高的 user_id 索引不用偏去走 status 索引这得从 SQLite 优化器的成本模型说起。SQLite 的成本估算依赖sqlite_stat1表里的统计信息而这个统计信息是执行ANALYZE命令生成的。如果你从来没在数据库上跑过 ANALYZE那优化器只能按一套保守的默认规则来猜最典型的结果是它认为所有单列索引的区分度差不多然后按照索引的某些内部顺序选择一个看起来合理的索引。sqlite_stat1的 stat 字段是一些用空格分隔的数字。比如SELECT * FROM sqlite_stat1; -- 输出类似 -- orders|idx_orders_status|2300000 3 -- orders|idx_orders_user_created|2300000 1150000第一个数字是表的总行数估算值后面的数字依次表示索引第一列、前两列、前三列……的组合区分度。如果这些数字因为数据更新而严重失真优化器就会算错成本。比如订单表做了一次大规模数据迁移把十年前的订单都塞了进来而旧的 stat 信息还停留在几万行时评估结果自然全乱套。我在实际排障中还发现一个很容易忽略的点ANALYZE 收集的统计信息不是自动更新的。你在事务里批量插入了 20 万行数据统计信息不会跟着变。索引当然会同步更新但优化器的眼睛还是老数据它做的每一个成本判断都可能偏离事实。所以很多同一套代码、同一个数据库换个环境就变慢的诡异现象根子就在这里。2. INDEXED BY 语法与行为边界它是一道命令不是一条建议2.1 基本语法SELECT / DELETE / UPDATE 中的写法INDEXED BY 的位置放在表名之后作用是告诉 SQLite访问这张表时必须使用我指定的索引没有商量余地。最基本的 SELECT 写法SELECT * FROM orders INDEXED BY idx_orders_user_created WHERE user_id 12345 AND created_at 2025-01-01 ORDER BY created_at DESC LIMIT 20;如果表起了别名子句要写在别名后面SELECT * FROM orders AS o INDEXED BY idx_orders_user_created WHERE o.user_id 12345;DELETE 和 UPDATE 同样支持DELETE FROM orders INDEXED BY idx_orders_user_created WHERE user_id 12345 AND created_at 2024-01-01; UPDATE orders INDEXED BY idx_orders_user_created SET status 9 WHERE user_id 12345 AND created_at 2024-01-01;语法本身非常简单难的是理解它的语义。它不像 MySQL 的FORCE INDEX、USE INDEX那样偏向建议SQLite 的 INDEXED BY 是实打实的命令。只要指定了查询计划就必然会尝试使用该索引而不是去挑别的。2.2 行为边界强约束带来的报错与限制正因为它是命令所以有几个边界行为必须搞清楚不然上线后等着你的就是查询直接报错。第一索引必须存在而且名字必须写对。写错了 SQLite 会直接报no such index。这个看似简单但很多项目里索引名靠代码自动生成一旦命名规则不一致迁移环境时容易翻车。第二当指定索引无法满足查询时SQLite 不会默默把索引忽略掉而是直接报no query solution。我遇到比较多的情况是部分索引partial index。假设你建了一个只针对 status1 的部分索引CREATE INDEX idx_orders_user_active ON orders(user_id, created_at) WHERE status 1;然后查询里根本没有约束 status只是 user_id、created_at 条件这时如果你强制INDEXED BY idx_orders_user_activeSQLite 无法只用这个索引完成查询就会报错。这一特性既可以说是保护也可以说是陷阱必须对索引定义有精确认知。第三INDEXED BY 和 NOT INDEXED 仅适用于普通 rowid 表对 WITHOUT ROWID 表不支持。这个细节在官方文档里有明确说明。如果项目里为了省空间用了 WITHOUT ROWID 表又想用 INDEXED BY得先确认版本和表类型否则就是白忙活。提示INDEXED BY 的适用面其实比你想象得窄。它适合解决优化器判断失误这一类问题而不是用来替代合理的索引设计。2.3 NOT INDEXED 的对照实验量化索引收益的土办法与 INDEXED BY 相对的SQLite 还提供了一个NOT INDEXED子句允许你显式禁止使用索引强制全表扫描。这两个子句搭配起来可以很方便地做性能对照实验量化某个索引到底值不值得保留。比如你怀疑 idx_status 这个索引对查询有帮助但又不确定可以分别跑两遍SELECT count(*) FROM orders NOT INDEXED WHERE status 1; SELECT count(*) FROM orders INDEXED BY idx_orders_status WHERE status 1;用.timer on观察耗时差异再配合 EXPLAIN QUERY PLAN 看具体执行路径。这种 A/B 对照是我在做索引优化时最常用的手法比纸上谈兵分析成本模型直观得多。有一次我就是通过这个土办法发现一个教训一个看似没用的索引在某种特定查询条件下其实有奇效而另一个看起来很有用的复合索引因为列顺序排错实际帮助几乎为零。没有 NOT INDEXED 这种开关你很难干净利落地做这样的对照。3. 实战案例数据权限过滤查询为什么总走错索引3.1 案例背景一张订单表和一个复杂过滤查询上一节的语法看起来很容易上手但真实场景远比教科书复杂。我这里有一个印象比较深的案例是一个带数据权限的管理后台查询。背景是系统里的角色分区域查询语句在运行时会被拼上一堆权限条件结构大概是SELECT id, user_id, status, amount, created_at, region_id FROM orders WHERE region_id IN (1001, 1002, 1003) AND status IN (0, 1, 2) AND user_id 0 AND created_at 2025-03-01 ORDER BY created_at DESC LIMIT 100;表上有三个索引CREATE INDEX idx_orders_region ON orders(region_id); CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_created ON orders(created_at);理论上最合适的方案是先用idx_orders_created把时间过滤掉或者用idx_orders_region把区域收窄。可优化器偏偏挑了idx_orders_status因为它在所有单列索引里被提前创建且 SQLite 在没有足够统计信息时对单列索引的评估几乎一视同仁。结果就是性能时好时坏——区域参数不同、状态参数不同走的计划都不一样最夸张的时候一个列表页查询耗时 8.7 秒。3.2 优化器为什么不买复合索引的账我当时的第一个念头是建一个复合索引比如(region_id, created_at)。建完之后用 EXPLAIN QUERY PLAN 一看傻眼了走的还是 status 索引。为什么因为查询条件里既有 IN 又有范围条件而且还有user_id 0这种几乎无过滤效果的条件。优化器评估复合索引时要同时考虑每个条件的过滤度和索引扫描成本。region_id 和 status 的 IN 列表组合起来有多少种可能优化器算得并不精准尤其是这些字段的重复率差别很大时。还有一个更隐蔽的问题查询里所有条件都是可选项运行时权限不同拼出来的 SQL 条件集不同。对优化器来说它看到的似乎是一个全新形状的查询没办法稳定选择一个复合索引。这种情况下索引再多也只是给优化器提供更多猜错的机会。这类条件多变、排列组合极多的查询本质上是索引设计的一个难点。简单加索引往往解决不了问题反而会让优化器更加迷茫。3.3 用 INDEXED BY 修正执行计划并验证数据一致性当时为了快速止血我在核心查询上加上了 INDEXED BY直接指定idx_orders_createdSELECT id, user_id, status, amount, created_at, region_id FROM orders INDEXED BY idx_orders_created WHERE region_id IN (1001, 1002, 1003) AND status IN (0, 1, 2) AND user_id 0 AND created_at 2025-03-01 ORDER BY created_at DESC LIMIT 100;这样优化器只能从 created_at 索引开始扫描再回表过滤 region_id 和 status。EXPLAIN QUERY PLAN 变成了QUERY PLAN |--SEARCH orders USING INDEX idx_orders_created (created_at?) --ORDER BY created_at DESC由于 created_at 索引和 ORDER BY 排序方向一致连临时排序都没了。耗时从 8.7 秒降到了 120 毫秒左右效果立竿见影。这里要特别提一句强制索引后必须做一次结果集对比确认返回数据跟原来完全一致。我自己有个习惯会在测试环境分别跑优化前后的 SQL然后把结果按主键排序后做 diff。表面上看 SQLite 不会因为走不同索引而返回不同结果但如果你查询里带了子查询、LEFT JOIN 或者依赖某种隐式排序的写法结果顺序可能会变。哪怕 content 相同排序不同也会让分页接口出现问题。3.4 理解边界从掐住计划到清理根因INDEXED BY 帮我快速解决了线上问题但我很清楚它只是止血不是根治。后续我又做了几件事把根因处理掉重新设计了一个更贴合业务查询的复合索引(status, region_id, created_at)。在表结构稳定后执行ANALYZE orders让统计信息跟上真实数据分布。把那种条件可拼可不拼的动态 SQL 改成固定结构的模板用缺省值代替条件拼接让优化器每次面对的是同一个形状的查询。最终我甚至把 INDEXED BY 从代码里摘掉了让优化器自己去选新的复合索引。因为那条 SQL 已经变成了长得一致、索引匹配度高的稳定查询不再需要人肉干预。提示INDEXED BY 的最佳使用姿势是短期止血 长期优化。等索引、统计信息、SQL 结构都调整到位后应该重新评估是否还需要强制指定。4. 容易被忽略的索引隐形杀手排序规则、LIKE 与部分索引4.1 排序规则不一致索引明明存在却用不上有一种情况很恼人索引明明存在EXPLAIN QUERY PLAN 也显示它存在但查询就是不走或者走了也用不上。多数时候问题出在排序规则collation不匹配。SQLite 的索引默认建立在 BINARY 排序规则上但列可以被定义为TEXT COLLATE NOCASE或RTRIM。假设有张用户表CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT COLLATE NOCASE ); CREATE INDEX idx_users_username ON users(username);索引会继承列的 collation也就是 NOCASE。但如果你在查询里写了WHERE username Admin COLLATE BINARY或者用了某些函数导致表达式类型变化优化器会认为索引的排序规则无法匹配当前比较操作的语义于是放弃索引。这类问题在代码里最不容易发现因为你肉眼看着就是同一个字段、同一个条件。排查方法还是回到 EXPLAIN QUERY PLAN如果看到SCAN users但你又确信索引没问题优先检查查询条件里是否显式指定了 COLLATE或某个连接查询的列 collation 是否一致。4.2 LIKE 前缀匹配字面量能走索引参数化却会翻车LIKE 和索引之间的互动坑更多。SQLite 对 LIKE 做了优化如果 pattern 是常量字符串且不是以%或_开头那么 LIKE 可以被转换成范围查询从而使用索引。比如SELECT * FROM orders WHERE created_at LIKE 2025-03-%;这条语句的 LIKE 条件等价于 created_at 在某个时间范围内SQLite 可以直接用 created_at 索引。但如果你参数化了SELECT * FROM orders WHERE created_at LIKE :pattern || %;这时优化器没法在编译 SQL 阶段把 pattern 翻译成确定的范围值于是状态一下子倒退到全表扫描。解决思路也不复杂像这种场景干脆别用 LIKE 做前缀查询直接用范围比较SELECT * FROM orders WHERE created_at :start AND created_at :end;索引利用率高语义还更清晰。这不是 INDEXED BY 能解决的问题但属于索引策略里特别重要的一环——先写一个优化器能看懂的 SQL再谈要不要强制走索引。4.3 部分索引的边界行为强制指定时可能直接报错部分索引是 SQLite 3.8.0 之后引入的特性可以在建索引时加 WHERE 过滤让索引体积更小、命中更集中。比如只给活跃订单建索引CREATE INDEX idx_active_orders_user ON orders(user_id, created_at) WHERE status 1;这个索引在正常查询时很好用SELECT * FROM orders WHERE status 1 AND user_id 10086;但如果你在另一个查询里写SELECT * FROM orders INDEXED BY idx_active_orders_user WHERE user_id 10086;查询条件没有 status 1强制用这个部分索引就可能导致no query solution报错因为优化器发现索引本身的 WHERE 条件无法满足查询的语义需要。这是我实际测试中踩过的坑也和团队的同事讨论过很多次。结论是对 partial index 使用 INDEXED BY 前一定要再三确认查询条件覆盖了索引的 WHERE 约束否则代码可能跑到某个特殊参数分支时直接 500。5. 在真实工程里怎么用 INDEXED BY稳定优先还是性能优先5.1 嵌入式产品里最怕执行计划漂移INDEXED BY 如果只用于在线服务可能显得有点多余毕竟优化器大部分时候是聪明的。但在嵌入式产品里情况完全不同。嵌入式设备上的 SQLite数据量可能只有几万行但 CPU 和磁盘性能都极其有限而且经常要长期运行。设备端最常见的问题不是单次查询慢而是执行计划漂移。比如设备固件升级后某条 SQL 突然从走索引变成全表扫描用户感受到的就是界面卡顿。这时候使用 INDEXED BY 的意义不是追求极致性能而是追求确定性和稳定性。嵌入式系统没有专职 DBA 盯着也没法实时 EXPLAIN确保关键查询每次走同一条路径比偶尔更快、偶尔卡死重要得多。我见过不少工控上位机项目甚至直接把 INDEXED BY 写死在所有核心查询里换来的就是十年不换代码也不出性能故障。5.2 数据库版本升级后的计划突变另一个场景是 SQLite 版本升级。SQLite 的查询规划器在 3.8、3.16、3.24、3.35 等版本都有明显改动最典型的是对 OR 条件、LIKE、子查询的优化策略变化。你可能只是把 SQLite 从一个版本升到另一个版本结果一模一样的数据和 SQL执行计划全变了。我遇到过的情况是应用本来运行得好好的升级一次 SQLite 后某条统计报表 SQL 从 400 毫秒涨到了 11 秒。用 EXPLAIN QUERY PLAN 对比新旧版本发现优化器从用主键索引变成了扫描一个很大的辅助索引完全没有理由就是成本模型的参数变了。这种问题非常适合用 INDEXED BY 来固定计划。如果代码本身还会被部署到不同设备上各设备 SQLite 版本又不一样那更需要靠这个子句来锁死关键路径。5.3 和覆盖索引组合让查询少回一次表使用 INDEXED BY 时还可以考虑配合覆盖索引让查询完全不用回表读取原始行。覆盖索引的意思是查询需要的所有列都包含在索引里SQLite 直接扫描索引就拿到了全部数据。比如下面的查询SELECT user_id, status FROM orders INDEXED BY idx_orders_user_status WHERE user_id 10086 AND status 1;其中索引是CREATE INDEX idx_orders_user_status ON orders(user_id, status);EXPLAIN QUERY PLAN 会显示USING COVERING INDEX idx_orders_user_status意思是连回表都不需要所有数据都从索引页里拿。相比普通索引回表少一次随机 IO在机械硬盘或者网络文件系统上差别尤其明显。我做性能测试时会刻意用覆盖索引版本对比非覆盖版本经常发现整体耗时可以下降 30% 到 60%。但要注意覆盖索引不是越宽越好。索引列太多会让写入变慢、文件变大要平衡。5.4 发布前补一课ANALYZE 与 PRAGMA optimize 的正确节奏无论你最终是否使用 INDEXED BY有一点必须养成习惯关键表的数据结构稳定后执行一次 ANALYZE并且在大批量数据导入后重新 ANALYZE。SQLite 从 3.18 版本开始提供了PRAGMA optimize;它相当于做了一次轻量级 ANALYZE不会消耗太多时间。文档建议应用在每次 close 数据库连接前调用一次或者在启动后的空闲时段执行。我个人的节奏是表结构变更后立刻分析该表。批量导入超过表总量 10% 的数据后立刻分析该表。线上应用启动后在后台线程执行一次PRAGMA optimize;。这样能让优化器手里的统计信息尽量贴近实际情况大幅减少莫名走错索引的几率。有了这份底子INDEXED BY 才不是天天需要救火而是偶尔出场。6. 我替你们踩过的三个坑索引名、统计信息与迁移思维6.1 索引名带不带前缀写错就是硬报错INDEXED BY 里写的索引名必须和sqlite_master里记录的名称完全一致。别想着写成schema.index_name或者带表名前缀的错觉名比如-- 错误写法orders 表名混进去了 SELECT * FROM orders INDEXED BY orders_idx_user_created WHERE user_id 1;如果真实索引名是idx_user_created这句直接报no such index: orders_idx_user_created。这个问题在从 MySQL 迁移过来的代码里尤其常见因为 MySQL 的习惯是索引名可以自由取。SQLite 里还是老老实实用工具或 sqlite_master 把真实索引名列出来再写。另外当表的索引很多、命名又风格不统一时建议专门写一个查询脚本SELECT name, tbl_name, sql FROM sqlite_master WHERE type index ORDER BY tbl_name, name;上线前对着这个清单核对一遍能省掉后续大量排查时间。6.2 大批量导入后忘记重新 ANALYZE执行计划直接翻车我有一次在生产环境踩过一个挺丢人的坑凌晨用 UPSERT 批量导入了 20 万行数据自认为索引和统计信息都很完善直接在白天高峰期开放功能。结果查询响应时间直线上升数据库 CPU 飙升。后来定位到原因批量导入前我执行过 ANALYZE导入后没有重新执行。统计信息里估计的表行数还是导入前的几万行优化器完全低估了新数据量于是一个原本该走主键索引的查询被改成了全索引扫描把整个表都扫了一遍。从那以后我把大批量写入后重新 ANALYZE写进了运维手册里和备份、数据一致性检查放在同一个级别。如果你项目里也有定期的 ETL 或数据同步任务务必在任务结束时补上这一句ANALYZE orders;6.3 不要把 SQL Server / MySQL 的 FORCE INDEX 习惯带进来最后想聊一个思维层面的坑。很多人第一次接触 INDEXED BY 时会觉得它和 MySQL 的FORCE INDEX、USE INDEX差不多顺手就把 MySQL 的使用习惯带过来了。但在生产环境吃了几次亏后我发现两者差异非常大。MySQL 的 FORCE INDEX 某种程度上仍然允许优化器在成本差异过大时绕过建议而 SQLite 的 INDEXED BY 几乎没有回旋余地。你指定了这条索引就得走这条索引哪怕它根本不是最优解。也就是说它把优化器犯傻的风险变成了DBA/开发必须每次判断正确的责任。所以我现在的原则是能通过重建索引、重写 SQL、更新统计信息解决问题就优先用这些常规手段只有遇到执行计划漂移、版本升级行为变化、或者明确对比验证过特定索引更优时才动用 INDEXED BY。它是一把手术刀用得好能精准切开病灶用不好反而会制造另一个伤口。这些年用 SQLite 越深越觉得它的轻量并不等于简单粗暴很多看似不起眼的小功能在关键时刻能起到大作用。INDEXED BY 就是这样一个存在——平时你可能根本想不到它但真遇到执行计划不合理时它往往是成本最低、效果最直接的解决方案。希望这篇文章能帮你少走一些弯路下次再被 SQLite 的慢查询折磨时至少知道还有这么一张牌可以用。