34. 索引 INDEX

本节目标：学完本章你能说清索引加速查询的原理，会为常用查询列建索引，并明白为什么索引不是越多越好。

先点一句 SQLite 的底层事实：它是 serverless 的嵌入式数据库，数据就存在那个单文件里，底层用 B 树（B-tree）组织。索引同样是这个文件里的一组 B 树结构。理解”索引是另一棵 B 树”，后面很多规则就好懂了。

一、索引为什么能加速

回想你查汉语字典：如果按拼音找字，先翻”拼音目录”一下就定位到页码，不用从第一页逐页翻。数据库的索引就是这本”目录”——它是一张额外的查找表，存的是”列值 → 行位置（ROWID）“的映射，并且排好序。

没有索引时，SQLite 要执行全表扫描（full table scan）：从表的第一行一直读到最后一行，逐行比对条件。表越大越慢。有了索引，数据库可以先在索引这棵较小的 B 树上做二分查找，快速定位到符合条件的 ROWID，再回表取数据，磁盘读取量大幅下降。

索引在底层大多用 B 树（B-tree，一种自平衡的多路搜索树） 组织。你可以把它想象成一棵”胖”的、每个节点有很多分叉的排序树：从根节点出发，沿着大小比较一步步往下走，几步就能定位到目标，而不用遍历所有数据。SQLite 的数据表本身也是 B 树（按 ROWID 排序），索引则是另建一棵按”索引列值”排序的 B 树。因为树的高度很低（通常三到四层就能容纳上百万行），所以查找极快。理解了”索引也是一棵 B 树”，你就明白为什么索引会占用额外空间、为什么写入时要维护它——本质是在同时维护两棵树。

Note

重点提示：索引加速的是”找”——主要是 SELECT ... WHERE、ORDER BY、JOIN 的匹配阶段。它不改变你存的数据，只是多了个排好序的”目录副本”。

二、创建与删除索引

单列索引：

CREATE INDEX IF NOT EXISTS idx_users_age ON users(age);

唯一索引（UNIQUE INDEX）：除了加速，还顺带约束该列（或列组合）不能有重复值，相当于一层数据完整性保护：

CREATE UNIQUE INDEX idx_users_email ON users(email);

组合索引（多列）：当查询经常同时用多个列做过滤时建立。

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

删除索引：

sqlite> DROP INDEX IF EXISTS idx_users_age;
sqlite> .indices users

注意一个细节：主键（INTEGER PRIMARY KEY）和 UNIQUE 约束会由 SQLite 自动建立隐藏索引（名字类似 sqlite_autoindex_表名_N），所以这些列其实已经”自带索引”了，不必再手动加。

还有个有用的概念叫覆盖索引（covering index）：如果索引本身已经包含了查询要取的所有列，SQLite 连基表都不用回，直接在索引里就把结果给了，能再快一倍。想做到这点，组合索引的列要把”查询条件列”和”SELECT 要取的列”都覆盖进去。

索引的选择性（selectivity） 指这一列的值有多少”彼此不同”。像 email、身份证号这种几乎每行都唯一的列，选择性高，索引能精准定位到少数几行，加速明显；而像 gender（只有男/女）这种大量重复值的列，选择性低——即使走了索引，也可能要回表捞一大片数据，优化器有时干脆放弃索引改走全表扫描。所以建索引前要看这列的”区分度”：区分度越高，索引越值钱；区分度低、还动辄占大头的列，建了也常常派不上用场。

三、写放大：索引的代价

天下没有免费午餐。每建一个索引，数据库就要在写入时同步维护它：

你 INSERT 一行，SQLite 除了写表，还要往每个相关索引的 B 树里插一条。
你 UPDATE 了被索引的列，旧索引项要删、新索引项要插。
你 DELETE 一行，所有相关索引项也要删。

这就是写放大（write amplification）：索引越多，每次写操作要维护的结构越多，写性能越差，磁盘占用也越大。所以”无脑给每列都建索引”是典型的反模式。

Tip

实用技巧：建索引前先问自己——这一列是不是经常出现在 WHERE / JOIN / ORDER BY 里？数据量大不大？如果答案都是”是”，才值得建。写少读多（比如报表、日志分析）的表适合多建索引；写多读少（比如高频流水写入）的表要克制。

Warning

常见坑：① 小表不必建索引，全表扫描本来就快，建了反而增加维护负担。② 组合索引要注意”最左前缀”——(a, b, c) 的索引，只有查询用到了 a（或 a,b）才能有效利用；只按 b 或 c 过滤时它帮不上忙。③ 列上大量重复值或大量 NULL 时，索引选择性差，加速效果有限。④ 索引不解决”模糊前缀带通配符”的慢查询，例如 LIKE '%abc' 开头的写法通常仍要走全表扫描。

四、什么时候用索引

频繁作为查询条件的列：建单列或组合索引。
需要唯一性的列：用 UNIQUE INDEX，既加速又防重。
大表的高频查询：索引收益明显；小表、频繁写入的表：慎用。

五、什么时候”不该”建索引

索引不是银弹，下面几种情况应当克制甚至避免：

表很小（几百行以内）：全表扫描本来就瞬间完成，索引纯属负担，反而拖慢写入、占空间。
写远多于读的表：比如高频埋点流水、实时日志，每多一个索引，每次写入就慢一分，而查询收益却很少。
列的选择性极低（见上文）：大量重复值的列，索引命中后往往还要回表捞一大片，优化器可能根本不屑于用。
列值频繁大范围更新：索引维护成本高于查询收益。

一句话记忆：索引服务于”读”，代价记在”写”上。先想清楚这张表是读多还是写多、这一列查得勤不勤，再决定要不要建。

六、进阶：部分索引、表达式索引与”索引是否真的用上了”

除了普通索引，SQLite 还支持两种更精细的索引，能省空间又提速：

部分索引（partial index）：用 WHERE 子句只给”部分行”建索引。例如大多数订单是已完成的，你只关心”未支付”的少数列，就可以 CREATE INDEX ... ON orders(user_id) WHERE status = 'unpaid'，索引只覆盖未支付订单，体积小、维护轻。
表达式索引（index on expression）：索引键不是列本身，而是一个表达式，比如 CREATE INDEX idx_lower_name ON users(LOWER(name))，之后用 WHERE LOWER(name) = ... 查询时就能命中。注意表达式里不能引用其他表，也不能用结果会变的函数（如 random()）。

那怎么确认索引真的被用上了？SQLite 提供了 EXPLAIN QUERY PLAN 前缀（详细用法在第 42 章展开），能告诉你某条查询是走了索引还是全表扫描。另外，SQLite 的查询优化器依赖数据统计来做选择，跑一次 ANALYZE; 可以让它更聪明地挑索引——尤其是列值分布不均匀时，效果明显。

举个实际例子（第 42 章会系统展开，这里先看个样子）：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 30;

如果输出里出现 SEARCH users USING INDEX idx_users_age，说明命中了索引；如果看到 SCAN users（全表扫描），说明没用上索引。写查询时养成用 EXPLAIN QUERY PLAN 验证的习惯，比凭感觉猜”有没有走索引”要靠谱得多。当你怀疑某条查询慢时，先拿它问一句”到底扫了全表还是用了索引”，往往能直接定位问题。

Note

重点提示：索引并不是”建了他就会用”。像 WHERE age + 1 = 25 这种把列包在函数/运算里的写法，往往用不上索引；写成 WHERE age = 24 才能让优化器直接比对索引键。写查询时尽量让”列”单独出现在条件一侧。

类比小结：索引像书的目录——查得快了，但每改一版书的内容，目录也得跟着重排，目录越厚，重排越累。所以目录要建在”常被翻”的章节上，而不是给每一句话都编目录；部分索引则是”只给重点章节编目录”，更省纸。