SQLite体积小、部署简单，但麻雀虽小五脏俱全，它的索引机制和大型数据库并无本质区别。不少人在建了索引之后发现查询速度没提升，甚至写入还变慢了，问题往往出在对索引原理和适用场景理解不够。这篇文章把SQLite索引的核心知识点梳理一遍，从底层结构讲到日常操作，再集中解答一些常见疑问。
一、索引的底层原理：B树到底做了什么
SQLite中的索引本质上是一张有序的查找表，底层采用B树（准确说是B+树的变体）结构存储。理解这一点非常重要：没有索引时，SQLite执行一次查询需要全表扫描，逐行比对条件；有了索引之后，SQLite可以在B树上进行二分式的定位，把O(n)的线性查找降到接近O(log n)的对数级查找。
具体来说，索引中保存的是被索引列的值以及指向原始数据行的rowid。当查询命中索引时，SQLite先在B树中找到匹配的键值，再通过rowid回到主表取出完整记录，这个回表动作是理解后续覆盖索引、复合索引等概念的基础。一张千万行的表，全表扫描可能要读取几十万个页，而走索引可能只需要读取几十个页，差距就在这里。
需要注意，索引本身也是存储在数据库文件中的数据页，它不是凭空存在的。因此每建一个索引，数据库文件体积会变大，而且每次INSERT、UPDATE、DELETE操作都要同步维护这些索引树，写入开销会随索引数量线性增加。这是索引的两面性：读快了，写慢了，空间也多了。
二、索引的基本操作：创建、查看与删除
先准备一张测试表：
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
order_no TEXT NOT NULL,
amount REAL,
created_at TEXT
);最常见的创建方式是普通索引，语法是CREATE INDEX 索引名 ON 表名(列名)：
-- 给 user_id 列建索引，常用于按用户查订单 CREATE INDEX idx_orders_user ON orders(user_id); -- 复合索引：先按 user_id 排，再按 created_at 排 CREATE INDEX idx_orders_user_time ON orders(user_id, created_at); -- 唯一索引：兼具约束和索引两种作用 CREATE UNIQUE INDEX idx_orders_no ON orders(order_no); -- 表达式索引：对表达式结果建索引（SQLite 3.9.0 以上支持） CREATE INDEX idx_orders_amount_tier ON orders(CASE WHEN amount > 1000 THEN 1 ELSE 0 END); -- 删除索引 DROP INDEX idx_orders_user;
查看一张表上有哪些索引，可以查sqlite_master系统表，或者使用.indexes命令：
SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 'orders'; -- 命令行工具中也可以 -- .indexes orders
建完索引后最关键的一步是验证它有没有被使用，这就要靠EXPLAIN QUERY PLAN。它会告诉你SQLite打算怎么执行查询，如果输出中出现USING INDEX idx_xxx字样，说明索引生效了；如果出现USING COVERING INDEX，说明是覆盖索引，性能更佳：
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1001;
如果结果是SCAN TABLE而不是SEARCH TABLE，说明查询没走索引，需要分析原因，下一节的常见疑问中会展开。
三、高频疑问解答：这些坑不要踩
疑问一：WHERE里用了函数，索引为什么失效了
对索引列做函数运算或拼接操作，索引就无法直接使用了。比如WHERE substr(order_no, 1, 4) = '2024'这种写法，SQLite无法利用idx_orders_no，只能全表扫描。解决办法有两个：改写成对原始列的等值或范围条件，或者干脆对表达式本身建立表达式索引，也就是前面演示的CREATE INDEX写法。
疑问二：复合索引的列顺序重要吗
非常重要。复合索引遵循最左前缀原则，查询条件必须从索引的最左列开始连续命中，索引才能被利用。(user_id, created_at)这个索引，可以支持WHERE user_id = 1、WHERE user_id = 1 AND created_at > '2024-01-01'，但单独WHERE created_at > '2024-01-01'就用不上它。安排列顺序的经验法则是：等值查询的列放前面，范围查询的列放后面，区分度高的列优先。
疑问三：什么是覆盖索引，为什么它更快
如果查询需要的所有列都包含在索引里，SQLite就不需要回表取数据，直接从索引返回结果，这就是覆盖索引。例如把索引建成(user_id, created_at, amount)，那么SELECT created_at, amount FROM orders WHERE user_id = 1就完全不碰主表，减少大量随机IO。这通常是SQLite中性价比最高的优化手段之一。
疑问四：索引是不是越多越好
不是。每个索引都要占用存储空间，并且拖慢所有写操作。经验上，只给WHERE、JOIN、ORDER BY中高频出现的条件建索引，写入频繁的表要克制，读多写少的分析型表可以适当多建。用不上的索引应该及时DROP掉。
疑问五：ORDER BY能吃到索引吗
可以。索引本身就是有序的，如果ORDER BY的列顺序和某个索引的列顺序一致且方向匹配，SQLite会直接按索引顺序读取，省掉排序步骤。这也是复合索引设计时要考虑的因素之一：把常用来排序的列纳入索引，能同时优化过滤和排序。
疑问六：主键需要单独建索引吗
不需要。INTEGER PRIMARY KEY本身就是rowid，天然是表的聚簇查找结构；TEXT类型的主键在SQLite内部也会自动创建唯一索引，手动再建纯属浪费。
四、日常使用的几点建议
第一，养成先跑EXPLAIN QUERY PLAN再上线的习惯，凭感觉判断索引是否生效极不可靠。第二，SQLite提供了ANALYZE命令，它会把表和索引的统计信息写入sqlite_stat1表，帮助查询优化器做出更准确的选择，数据分布变化较大后重新执行一次会有帮助。第三，记得开启PRAGMA优化或定期执行PRAGMA optimize，让数据库自动维护统计信息。第四，索引优化永远要配合实际查询语句来做，脱离业务SQL谈索引没有意义。
总结一下：SQLite索引的核心是B树加速查找，代价是写入变慢和空间增长；操作上掌握CREATE INDEX、EXPLAIN QUERY PLAN、sqlite_master三件套就够日常使用；设计上抓住最左前缀、覆盖索引、按需建索引这几个要点，基本就能避开绝大多数弯路。