第 9 章:索引与执行计划
索引是 SQL 里"投入产出比"最高的一课:加一行
CREATE INDEX,一条查询可能从 3 秒变 3 毫秒。但它也是最容易被滥用的一课——每个索引都在为写入付税。
学习目标
- 看懂
EXPLAIN QUERY PLAN里的SCAN和SEARCH,知道它们在说什么 - 用 B-tree 的结构解释"为什么索引能跳过大部分行"
- 理解复合索引的"最左前缀",知道索引在什么情况下不生效
- 判断一个索引该不该建,而不是见查询慢就加索引
9.1 没有索引时,数据库在做什么
先看一条查询的执行计划:
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;QUERY PLAN
`--SCAN ordersSCAN 就是全表扫描:从第一行读到最后一行,逐行判断 customer_id = 2。
小书店只有 12 张订单,扫一遍感觉不到。换成 100 万行订单,每次查"某客户的订单"都要读 100 万行——这就是"数据一多就慢"的真正原因。
EXPLAIN QUERY PLAN 不会真的执行查询,只告诉你数据库打算怎么执行。它是排查慢查询的第一件工具,比猜有用得多。
注意是
EXPLAIN QUERY PLAN,不是EXPLAIN。后者在 SQLite 里输出的是虚拟机字节码,给内核开发者看的,日常排查用不上。
9.2 索引是什么:一棵能跳着找的树
给 orders.customer_id 建索引:
CREATE INDEX idx_orders_customer ON orders (customer_id);再查执行计划:
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)SCAN 变成了 SEARCH ... USING INDEX。差别不是"快一点",是根本不读无关的行。
B-tree 的直觉
索引的本质是一棵排好序的树。每个节点存一批"键值 + 指向数据行的位置",查找时从根往下比较,每次排除一大片:
查找 customer_id = 2 的路径是:根节点比较一次 → 进某个子树 → 再比较 → 到达叶子 → 拿到行的位置。比较次数约等于树的高度(通常 3–4 层),而不是行数。
这就是为什么 100 万行和 1000 行的索引查询耗时差不多——多一层树而已。
索引里存了什么
SQLite 的索引存的是"列值 + rowid"。查到 rowid 之后,还要回表去取整行数据(除非索引已经覆盖了你要的所有列,见 9.6)。
所以索引查询的代价是:树查找 + 回表。它仍然远小于全表扫描,但不是零成本。
9.3 复合索引与最左前缀
给两列建一个索引:
CREATE INDEX idx_books_cat_price ON books (category, price);这个索引的排序规则是:先按 category 排,category 相同的再按 price 排。就像通讯录先按姓、同姓再按名。
于是能不能用上索引,取决于你的条件是否命中"最左边的那一列":
-- ① 用上索引:命中最左列 category
SELECT * FROM books WHERE category = '文学';
-- SEARCH books USING INDEX idx_books_cat_price (category=?)
-- ② 用上索引:category + price 都命中
SELECT * FROM books WHERE category = '文学' AND price < 50;
-- SEARCH books USING INDEX idx_books_cat_price (category=? AND price<?)
-- ③ 用不上:跳过了 category,直接查 price
SELECT * FROM books WHERE price < 50;
-- SCAN books第三种情况就是最左前缀原则:复合索引只能从最左列开始连续使用。跳过 category 直接用 price,数据库没法在树上定位——因为 price 在整个索引里不是有序的,只在同一个 category 内有序。
三条实用推论:
- 建复合索引时,把最常单独查询的列放最左;
(a, b)的索引能支持a、a+b,不能支持b;- 已经有
(a, b)索引,通常不需要再单独建(a)(最左前缀已经覆盖)。
9.4 索引为什么会失效
建了索引却还是 SCAN,常见有五种原因:
| 原因 | 例子 | 为什么失效 |
|---|---|---|
| 列被函数包裹 | WHERE substr(ordered_at,1,7) = '2025-06' | 索引存的是原值,不是函数结果 |
| 前导模糊匹配 | WHERE title LIKE '%SQL%' | 前缀未知,无法定位起点 |
| 类型不匹配 | WHERE price = '49'(列是 REAL) | 比较前要转换,索引顺序对不上 |
OR 连接未建索引的列 | WHERE city = '北京' OR stock = 0 | 有一侧无法走索引,整体退化为扫描 |
| 范围条件后用后续列 | WHERE category > 'A' AND price < 50 | 第一列是范围,第二列无法再精确定位 |
函数包裹是最隐蔽的一种
需求:查 6 月的订单。很自然会写:
SELECT * FROM orders WHERE substr(ordered_at, 1, 7) = '2025-06';即使 ordered_at 上有索引,这条也走不了——因为数据库要先对每一行算出 substr,再比较。
改写方式是"把函数从列上移走":
SELECT * FROM orders
WHERE ordered_at >= '2025-06-01' AND ordered_at < '2025-07-01';这就是第 4 章强调"左闭右开"的另一个原因:它不仅正确,还能走索引。
OR 的两种情况
-- 两边都建了索引,可能走"OR 优化"
WHERE city = '北京' OR city = '上海'
-- 只有一边有索引,基本一定全表扫
WHERE city = '北京' OR stock = 0OR 的条件尽量作用在同一列上,或者改写成 UNION ALL 两条查询。
9.5 索引不是越多越好
每个索引都要付三笔成本:
| 成本 | 说明 |
|---|---|
| 写入变慢 | 每次 INSERT/UPDATE/DELETE 都要同步维护所有相关索引 |
| 占用空间 | 索引本身可能和表一样大,甚至更大 |
| 优化器负担 | 索引太多,选错索引的概率上升 |
一张写多读少的表(日志、埋点),索引建多了会明显拖慢写入。索引应该跟着查询走,而不是跟着列走:先看慢查询日志,找出真正高频的 WHERE/JOIN/ORDER BY 列,再针对性建。
一条经验:单表索引数量控制在 5 个以内,超过就该审视是不是表设计或查询写法有问题。
9.6 覆盖索引与深分页
如果一个索引包含了查询需要的所有列,数据库就不用回表,直接返回索引里的值:
CREATE INDEX idx_books_cat_price ON books (category, price);
SELECT category, price FROM books WHERE category = '文学';这条查询的列全在索引里,称为覆盖索引(covering index)。执行计划里可能显示 USING COVERING INDEX,省掉了回表那一步。
深分页也能用它优化。回顾第 4 章的问题:OFFSET 10000 LIMIT 10 要先把前 10000 行数过去。
一种改法是"先用覆盖索引拿到主键,再回表取 10 行":
SELECT b.*
FROM books b
JOIN (
SELECT id FROM books ORDER BY price DESC LIMIT 10 OFFSET 10000
) t ON t.id = b.id;子查询只在索引里走,回表只发生 10 次。数据量越大,收益越明显。
9.7 排查慢查询的顺序
遇到"这条查询很慢",按这个顺序走,比乱试有效:
EXPLAIN QUERY PLAN:先确认是SCAN还是SEARCH,有没有用错索引;- 看条件是否命中最左前缀:复合索引的第一列有没有出现在
WHERE里; - 看列上有没有函数/隐式转换:把函数移到常量侧,把类型对齐;
- 看是不是排序导致:
ORDER BY的列能不能走索引,否则会多一次排序; - 最后才考虑加索引:确认前面都没问题,再加,加完再看执行计划验证。
顺序不能反。先加索引再排查,往往加了三个索引,问题还在。
本章小结
| 要点 | 说明 |
|---|---|
SCAN vs SEARCH | 全表逐行 vs 走索引定位 |
EXPLAIN QUERY PLAN | 看计划,不执行,排查第一步 |
| B-tree 查找 ≈ 树高 | 3–4 次比较,和行数基本无关 |
| 最左前缀 | 复合索引只能从最左列连续使用 |
| 列上包函数必失效 | 改成对常量做区间 |
前导 % 必失效 | 模糊查询把 % 放右边 |
| 索引有写入成本 | 跟着查询建,不跟着列建 |
| 覆盖索引省回表 | 深分页先取主键再回表 |
下一章
索引解决"读得慢",下一章解决"写得乱":两个请求同时改同一行会怎样、转账转一半失败了怎么办、为什么会有死锁。事务是数据库从"能存数据"变成"能放心存钱"的分界线。