极客工场

Appearance

共 1,437 字预计阅读约 5 分钟

SQLite 索引与查询优化：EXPLAIN QUERY PLAN、覆盖索引与嵌入式批量写入 ​

更新时间：2026-09-18。SQLite 优化器思路与服务端数据库同源，但作为嵌入式单文件库，手段高度集中在索引设计、单条 SQL 的扫描方式、以及 fsync/事务粒度。本篇建立在 SQLite 架构：VDBE 与 WAL 之上。通用 B+ 树原理见 MySQL 索引原理，服务端 EXPLAIN 见 MySQL EXPLAIN 深挖。

本文要回答的问题 ​

  • 怎么用 EXPLAIN QUERY PLAN 判断全表扫描还是索引？
  • rowid、INTEGER PRIMARY KEY、WITHOUT ROWID 如何影响回表？
  • 复合索引最左前缀、覆盖索引怎么生效？
  • LIKE/OR/类型亲和性/函数为何让索引失效？
  • 嵌入式批量插入慢，事务/PRAGMA 怎么调又快又安全？

一、先会读 EXPLAIN QUERY PLAN ​

sql
CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INT, status TEXT, created_at TEXT, amount REAL);
CREATE INDEX idx_orders_user ON orders(user_id, created_at);

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 7;
-- SEARCH orders USING INDEX idx_orders_user (user_id=?)   -- 命中索引后回表
-- SEARCH ... COVERING INDEX ...                          -- 只读索引列,不回表
-- SCAN orders                                            -- 全表扫描
输出含义
SCAN table全表扫描，大表高选择性查询要警惕
SEARCH ... USING INDEX走二级索引，可能回表
SEARCH ... USING PRIMARY KEY走 rowid/主键，聚簇定位
COVERING INDEX覆盖索引，不回表
TEMP B-TREE排序/分组/去重临时 b-tree，大数据量是坏味道

注意 SQLite 的 EXPLAIN（VDBE 字节码）与 EXPLAIN QUERY PLAN（访问路径）是两回事，优化看后者。

二、rowid、INTEGER PRIMARY KEY 与 WITHOUT ROWID ​

普通 rowid 表内部：隐藏 rowid 是 table b-tree 的键、数据按 rowid 有序；INTEGER PRIMARY KEY 是 rowid 别名，点查直接定位聚簇页；二级索引叶子存 (索引列,rowid)，命中后回表。

sql
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE id = 100;
-- SEARCH orders USING PRIMARY KEY (id=?)

WITHOUT ROWID 用声明的 PRIMARY KEY 作 table b-tree 键（类似索引组织表），适合复合主键、行小、按键范围查多的表，省隐藏 rowid 和一次回表：

sql
CREATE TABLE order_item(order_id INT, sku_id INT, qty INT,
  PRIMARY KEY(order_id, sku_id)) WITHOUT ROWID;

坑一：非 INTEGER 主键不等于聚簇。 TEXT PRIMARY KEY 仍有隐藏 rowid，主键本身是唯一 index b-tree、查它要回表；要按键聚簇请用 INTEGER PRIMARY KEY 或 WITHOUT ROWID。

三、复合索引与覆盖索引 ​

  1. 最左前缀：(a,b,c) 服务 a、a,b、a,b,c，缺 a 用不上。
  2. 范围列之后不再定位：a=? AND b>? AND c=? 中 c 不能继续做索引定位。
  3. 覆盖索引：所需列全在索引里 → COVERING INDEX，省回表，读多场景收益最大。
sql
CREATE INDEX idx_user_time ON orders(user_id, created_at);
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders WHERE user_id=7 ORDER BY created_at DESC LIMIT 20;
-- 过滤+排序走索引且列被覆盖时：COVERING INDEX 且有序,免 TEMP B-TREE

让 ORDER BY 利用索引有序性可避免 USE TEMP B-TREE FOR ORDER BY；做不到全覆盖时，至少让过滤+排序走索引，只对最终 LIMIT 的少量行回表。

四、索引失效的 SQLite 特色 ​

1）前导通配 LIKE。 LIKE 'abc%' 可走索引（注意 collation/NOCASE），LIKE '%abc' 只能 SCAN（后缀匹配可建 reverse(name) 表达式索引）。

2）列上套函数/运算。 WHERE amount*2>10、lower(name)='tom' 命不中普通索引；SQLite 支持表达式索引，与查询表达式书写一致即可命中：

sql
CREATE INDEX idx_name_lower ON orders(lower(name));

3）OR 分支不能都用索引。 WHERE a=1 OR b=2 若两侧无都可用的索引会全表 SCAN，可拆 UNION ALL 或观察 OR-by-union 是否用了两个索引。

4）类型亲和性（affinity）。 SQLite 动态类型，值与列亲和性不匹配时隐式转换会破坏索引；建表列类型规范、查询值类型与列一致（数字列传数字、文本列传带引号文本）。

5）!=、IS NOT NULL 等否定条件匹配面大，优化器倾向 SCAN，这是成本选择而非 bug。

五、ANALYZE：让优化器拿到统计 ​

SQLite 默认对分布做粗略假设，数据倾斜会选错索引。ANALYZE 把统计写进 sqlite_stat1：

sql
ANALYZE;          -- 全库
ANALYZE orders;   -- 单表
-- 大批量导入、分布剧变后要重新 ANALYZE；统计不会自动更新

坑二：大批量导入后没 ANALYZE，计划可能变差。 灌完分布变化明显的数据应执行一次 ANALYZE。

六、嵌入式批量写入提速 ​

默认每条 INSERT 一个事务、每次提交 fsync——插一万行就是一万次磁盘同步，极慢。按风险分级提速：

1）批量写包进单个事务（首选、不牺牲安全）：

sql
BEGIN;
INSERT INTO t ...;   -- 很多条
COMMIT;              -- 只 fsync 一次

2）预编译语句 + 参数绑定，复用 stmt，省解析/计划，还能防注入。

3）WAL + busy_timeout（读写并发）：

sql
PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;
PRAGMA synchronous=NORMAL;  -- WAL 下崩溃不损坏库，仅极端情况可能丢最后事务

4）一次性初始化导入可临时放宽（导完即恢复）：

sql
PRAGMA synchronous=OFF;     -- 仅用于可重建数据导入，掉电可能损坏库，禁用于常态业务
PRAGMA journal_mode=MEMORY;
PRAGMA cache_size=-200000;  -- 负号=KB(约200MB)，正号是页数
PRAGMA temp_store=MEMORY;
-- 导入 + ANALYZE 后恢复 WAL + synchronous=NORMAL/FULL
手段提速来源风险
单事务批量提交N 次 fsync 合 1 次无（事务内原子）
prepared statement省解析/计划无
WAL读写不互斥不可用网络盘
synchronous=NORMAL降低 fsync 严格度WAL 下崩溃不损坏
synchronous=OFF/MEMORY几乎不等 IO掉电可能损坏库

坑三：synchronous=OFF 只能用于数据可重建的导入窗口。 移动/嵌入式设备可能随时断电，常态业务必须回到 NORMAL/FULL。

七、相关与延伸 ​

页/rowid/WAL/锁底层见 SQLite 架构：VDBE 与 WAL；通用 B+ 树最左前缀与回表见 MySQL 索引原理；服务端 EXPLAIN 对比见 MySQL EXPLAIN 深挖；选型边界见 PostgreSQL 与 MySQL 差异。

总结 ​

SQLite 索引与查询优化：用 EXPLAIN QUERY PLAN 区分 SCAN/SEARCH/COVERING INDEX/TEMP B-TREE；INTEGER PRIMARY KEY 即 rowid 走聚簇点查，复合主键且行小用 WITHOUT ROWID 省回表，普通二级索引叶子带 rowid 要回表；遵守最左前缀、范围列后不再定位、争取覆盖索引并让 ORDER BY 走索引有序；前导通配 LIKE、列上函数（改表达式索引）、OR、类型亲和不匹配、否定条件会令索引失效，倾斜或大导入后用 ANALYZE 刷统计；嵌入式写入慢主因是逐条 fsync，首选单事务批量提交 + prepared statement + WAL/busy_timeout/synchronous=NORMAL，synchronous=OFF 仅用于可重建数据的临时导入。