Skip to content
Lv1SQL 与数据库进度 9 / 12

第 9 章:索引与执行计划 ​

索引是 SQL 里"投入产出比"最高的一课:加一行 CREATE INDEX,一条查询可能从 3 秒变 3 毫秒。但它也是最容易被滥用的一课——每个索引都在为写入付税。


学习目标 ​

  • 看懂 EXPLAIN QUERY PLAN 里的 SCAN 和 SEARCH,知道它们在说什么
  • 用 B-tree 的结构解释"为什么索引能跳过大部分行"
  • 理解复合索引的"最左前缀",知道索引在什么情况下不生效
  • 判断一个索引该不该建,而不是见查询慢就加索引

9.1 没有索引时,数据库在做什么 ​

先看一条查询的执行计划:

sql
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;
QUERY PLAN
`--SCAN orders

SCAN 就是全表扫描:从第一行读到最后一行,逐行判断 customer_id = 2。

小书店只有 12 张订单,扫一遍感觉不到。换成 100 万行订单,每次查"某客户的订单"都要读 100 万行——这就是"数据一多就慢"的真正原因。

EXPLAIN QUERY PLAN 不会真的执行查询,只告诉你数据库打算怎么执行。它是排查慢查询的第一件工具,比猜有用得多。

注意是 EXPLAIN QUERY PLAN,不是 EXPLAIN。后者在 SQLite 里输出的是虚拟机字节码,给内核开发者看的,日常排查用不上。


9.2 索引是什么:一棵能跳着找的树 ​

给 orders.customer_id 建索引:

sql
CREATE INDEX idx_orders_customer ON orders (customer_id);

再查执行计划:

sql
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 复合索引与最左前缀 ​

给两列建一个索引:

sql
CREATE INDEX idx_books_cat_price ON books (category, price);

这个索引的排序规则是:先按 category 排,category 相同的再按 price 排。就像通讯录先按姓、同姓再按名。

于是能不能用上索引,取决于你的条件是否命中"最左边的那一列":

sql
-- ① 用上索引:命中最左列 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 内有序。

三条实用推论:

  1. 建复合索引时,把最常单独查询的列放最左;
  2. (a, b) 的索引能支持 a、a+b,不能支持 b;
  3. 已经有 (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 月的订单。很自然会写:

sql
SELECT * FROM orders WHERE substr(ordered_at, 1, 7) = '2025-06';

即使 ordered_at 上有索引,这条也走不了——因为数据库要先对每一行算出 substr,再比较。

改写方式是"把函数从列上移走":

sql
SELECT * FROM orders
WHERE ordered_at >= '2025-06-01' AND ordered_at < '2025-07-01';

这就是第 4 章强调"左闭右开"的另一个原因:它不仅正确,还能走索引。

OR 的两种情况 ​

sql
-- 两边都建了索引,可能走"OR 优化"
WHERE city = '北京' OR city = '上海'
-- 只有一边有索引,基本一定全表扫
WHERE city = '北京' OR stock = 0

OR 的条件尽量作用在同一列上,或者改写成 UNION ALL 两条查询。


9.5 索引不是越多越好 ​

每个索引都要付三笔成本:

成本说明
写入变慢每次 INSERT/UPDATE/DELETE 都要同步维护所有相关索引
占用空间索引本身可能和表一样大,甚至更大
优化器负担索引太多,选错索引的概率上升

一张写多读少的表(日志、埋点),索引建多了会明显拖慢写入。索引应该跟着查询走,而不是跟着列走:先看慢查询日志,找出真正高频的 WHERE/JOIN/ORDER BY 列,再针对性建。

一条经验:单表索引数量控制在 5 个以内,超过就该审视是不是表设计或查询写法有问题。


9.6 覆盖索引与深分页 ​

如果一个索引包含了查询需要的所有列,数据库就不用回表,直接返回索引里的值:

sql
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 行":

sql
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 排查慢查询的顺序 ​

遇到"这条查询很慢",按这个顺序走,比乱试有效:

  1. EXPLAIN QUERY PLAN:先确认是 SCAN 还是 SEARCH,有没有用错索引;
  2. 看条件是否命中最左前缀:复合索引的第一列有没有出现在 WHERE 里;
  3. 看列上有没有函数/隐式转换:把函数移到常量侧,把类型对齐;
  4. 看是不是排序导致:ORDER BY 的列能不能走索引,否则会多一次排序;
  5. 最后才考虑加索引:确认前面都没问题,再加,加完再看执行计划验证。

顺序不能反。先加索引再排查,往往加了三个索引,问题还在。


本章小结 ​

要点说明
SCAN vs SEARCH全表逐行 vs 走索引定位
EXPLAIN QUERY PLAN看计划,不执行,排查第一步
B-tree 查找 ≈ 树高3–4 次比较,和行数基本无关
最左前缀复合索引只能从最左列连续使用
列上包函数必失效改成对常量做区间
前导 % 必失效模糊查询把 % 放右边
索引有写入成本跟着查询建,不跟着列建
覆盖索引省回表深分页先取主键再回表

下一章 ​

索引解决"读得慢",下一章解决"写得乱":两个请求同时改同一行会怎样、转账转一半失败了怎么办、为什么会有死锁。事务是数据库从"能存数据"变成"能放心存钱"的分界线。

👉 第 10 章:事务与并发

本文阅读--次|全站访问--次|访客--人