第六章索引与查询优化

6.1 什么是索引 —— 类比与原理

一句话理解

索引就是数据库的"目录"——和书的目录一样，帮你快速定位数据，而不用从头翻到尾。

没有索引 vs 有索引

没有索引时（全表扫描）：

  "找 name = '张三' 的记录"
  → 从第 1 行开始，逐行比对，直到最后一行
  → 100 万行就要检查 100 万次

  ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐        ┌───┐
  │ 1 │→│ 2 │→│ 3 │→│ 4 │→│ 5 │→ ... →│ N │  逐个检查
  └───┘ └───┘ └───┘ └───┘ └───┘        └───┘


有索引时（B-Tree 查找）：

  "找 name = '张三' 的记录"
  → 通过索引树，几步就定位到目标行
  → 100 万行只需要检查约 20 次（log₂(1000000) ≈ 20）

            ┌───┐
            │ M │
          ╱     ╲
      ┌───┐     ┌───┐
      │ F │     │ T │
     ╱   ╲    ╱   ╲
  ┌───┐┌───┐┌───┐┌───┐
  │ B ││ J ││ P ││ Z │  → 找到 "张" 在这里！
  └───┘└───┘└───┘└───┘

索引的本质

SQLite 的索引是一棵独立的 B-Tree，它存储了：

被索引列的值（排好序的）
对应行的 rowid（用于回表查找完整数据）

索引 idx_users_name 的内部结构：

  ┌──────────┬────────┐
  │ name     │ rowid  │
  ├──────────┼────────┤
  │ 李四     │ 2      │  ← 按 name 排序存储
  │ 王五     │ 3      │
  │ 张三     │ 1      │
  │ 赵六     │ 4      │
  └──────────┴────────┘

  查询 WHERE name = '张三' 时：
  1. 在索引 B-Tree 中二分查找 → 找到 rowid = 1
  2. 用 rowid 到主表 B-Tree 中取出完整行
  → 跳过了 99% 的数据！

索引的代价

索引不是免费的午餐：

好处	代价
查询（SELECT）变快	插入（INSERT）变慢 —— 要同时更新索引
排序（ORDER BY）可以利用索引	更新（UPDATE）变慢 —— 索引列变化时要调整索引
唯一性检查更快（UNIQUE INDEX）	占额外磁盘空间

原则：读多写少的列适合建索引；写多读少的场景要控制索引数量。

6.2 创建与管理索引

创建普通索引

sql

-- 在 users 表的 name 列上创建索引
CREATE INDEX idx_users_name ON users(name);

-- 在 orders 表的 created_at 列上创建索引
CREATE INDEX idx_orders_created_at ON orders(created_at);

命名规范建议：idx_表名_列名，一眼就知道是哪张表的哪个列。

创建唯一索引

sql

-- email 不能重复（和 UNIQUE 约束效果相同，但索引可以后期添加）
CREATE UNIQUE INDEX idx_users_email ON users(email);

UNIQUE 约束 vs UNIQUE INDEX：建表时写 UNIQUE 约束，SQLite 会自动创建一个唯一索引。两者效果相同，只是创建方式不同。

复合索引（多列索引）

sql

-- 在 city 和 age 两列上创建复合索引
CREATE INDEX idx_users_city_age ON users(city, age);

复合索引的列顺序很重要——遵循"最左前缀"原则：

索引 idx_users_city_age (city, age) 可以加速：

  ✅ WHERE city = '北京'                  → 用到索引
  ✅ WHERE city = '北京' AND age > 25     → 用到索引
  ✅ ORDER BY city, age                   → 用到索引
  ❌ WHERE age > 25                       → 用不到！（跳过了 city）
  ❌ ORDER BY age, city                   → 用不到！（顺序反了）

复合索引的内部结构：

  ┌──────┬─────┬────────┐
  │ city │ age │ rowid  │
  ├──────┼─────┼────────┤
  │ 北京 │ 22  │ 3      │  ← 先按 city 排序
  │ 北京 │ 25  │ 1      │     再按 age 排序
  │ 上海 │ 28  │ 4      │
  │ 上海 │ 30  │ 2      │
  │ 广州 │ 35  │ 5      │
  └──────┴─────┴────────┘

  如果只查 age，索引帮不上忙（数据没有按 age 单独排序）

部分索引（Partial Index）

只对满足条件的行建索引，节省空间、提升效率：

sql

-- 只对活跃用户建索引（status = 'active' 的行）
CREATE INDEX idx_active_users ON users(name) WHERE status = 'active';

-- 只对未删除的记录建索引（软删除场景）
CREATE INDEX idx_users_not_deleted ON users(email) WHERE deleted_at IS NULL;

部分索引特别适合：大表中只需要频繁查询一小部分数据的场景。

表达式索引

对计算表达式建索引：

sql

-- 对 name 的小写形式建索引（加速不区分大小写的查询）
CREATE INDEX idx_users_name_lower ON users(lower(name));

-- 使用时 WHERE 条件必须匹配索引表达式
SELECT * FROM users WHERE lower(name) = 'zhang san';

查看已有索引

sql

-- 列出当前数据库的所有索引
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

-- 用点命令查看某张表的索引
.indices users

-- 查看索引的详细定义
.schema users

删除索引

sql

DROP INDEX idx_users_name;
DROP INDEX IF EXISTS idx_users_name;  -- 不存在也不报错

IF NOT EXISTS

sql

CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);

6.3 用 EXPLAIN QUERY PLAN 分析查询

EXPLAIN QUERY PLAN 是 SQLite 的"X 光机"——它告诉你查询将要怎么执行，让你判断索引是否被正确使用。

基本用法

sql

EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = '张三';

输出解读

text

QUERY PLAN
`--SEARCH users USING INDEX idx_users_name (name=?)

关键看第一个词：

关键词	含义	性能
`SCAN`	全表扫描，逐行检查	🔴 最慢
`SEARCH`	使用索引定位	🟢 快
`USING INDEX`	指明用了哪个索引	🟢 快
`USING COVERING INDEX`	覆盖索引，不需要回表	🟢 最快

四种扫描方式对比

sql

-- 准备示例表
CREATE TABLE products (
    id       INTEGER PRIMARY KEY,
    name     TEXT,
    category TEXT,
    price    REAL
);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_cat_price ON products(category, price);

① 全表扫描（SCAN）—— 没有可用索引

text

sqlite> EXPLAIN QUERY PLAN SELECT * FROM products WHERE price > 100;
QUERY PLAN
`--SCAN products

price 列没有索引，只能逐行检查。

② 索引查找（SEARCH USING INDEX）—— 用索引定位，然后回表

text

sqlite> EXPLAIN QUERY PLAN SELECT * FROM products WHERE category = '电子';
QUERY PLAN
`--SEARCH products USING INDEX idx_products_category (category=?)

先在索引中找到 category = '电子' 的 rowid，再回主表取完整行。

③ 覆盖索引（USING COVERING INDEX）—— 只读索引，不回表

text

sqlite> EXPLAIN QUERY PLAN SELECT category, price FROM products WHERE category = '电子';
QUERY PLAN
`--SEARCH products USING COVERING INDEX idx_products_cat_price (category=?)

查询的列（category、price）刚好都在索引 idx_products_cat_price 里，不需要回主表——这是最快的情况。

④ 主键查找 —— 直接 B-Tree 定位

text

sqlite> EXPLAIN QUERY PLAN SELECT * FROM products WHERE id = 42;
QUERY PLAN
`--SEARCH products USING INTEGER PRIMARY KEY (rowid=?)

用 rowid/INTEGER PRIMARY KEY 查找是最直接的，一步到位。

实战优化演示

sql

-- 优化前：全表扫描
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@test.com';
-- SCAN users  ← 🔴 慢

-- 加索引
CREATE INDEX idx_users_email ON users(email);

-- 优化后：索引查找
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@test.com';
-- SEARCH users USING INDEX idx_users_email (email=?)  ← 🟢 快

JOIN 的执行计划

text

sqlite> EXPLAIN QUERY PLAN
SELECT s.name, c.course FROM students s JOIN courses c ON s.id = c.student_id;

QUERY PLAN
|--SCAN c                        ← 先全表扫描 courses
`--SEARCH s USING INTEGER PRIMARY KEY (rowid=?)  ← 再用主键查 students

如果 courses.student_id 没有索引，可以添加一个：

sql

CREATE INDEX idx_courses_student_id ON courses(student_id);

养成习惯：写完 SQL 后，用 EXPLAIN QUERY PLAN 检查一下。如果看到 SCAN 且数据量较大，就考虑加索引。

6.4 索引使用原则与常见陷阱

该建索引的列

场景	说明
`WHERE` 条件中频繁出现的列	最基本的使用场景
`JOIN` 的关联列（外键）	加速表连接
`ORDER BY` 排序列	避免文件排序（filesort）
`UNIQUE` 约束列	SQLite 自动创建，但后期加的要手动建
高选择性的列（值重复率低）	如 email、手机号

不该建索引的列

场景	原因
值只有几种的列（如性别、状态）	选择性太低，索引收益小
很少出现在查询条件中的列	建了也用不上
频繁更新的列	每次 UPDATE 都要同时更新索引
数据量很小的表（几百行）	全表扫描也很快，索引反而增加开销

常见陷阱（索引失效的情况）

陷阱 1：对索引列使用函数

sql

-- ❌ 索引失效！对 name 列使用了函数
SELECT * FROM users WHERE upper(name) = 'ZHANG SAN';
-- 解决：建表达式索引 CREATE INDEX idx ON users(upper(name));

-- ✅ 不用函数，保持索引列"干净"
SELECT * FROM users WHERE name = 'Zhang San';

陷阱 2：隐式类型转换

sql

-- phone 列是 TEXT 类型
-- ❌ 传了 INTEGER，可能导致索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

陷阱 3：LIKE 以 % 开头

sql

-- ❌ 以通配符开头，无法使用索引
SELECT * FROM users WHERE name LIKE '%三';

-- ✅ 以确定字符开头，可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

陷阱 4：OR 导致索引失效

sql

-- ❌ OR 连接不同列，可能导致两个索引都用不上
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- ✅ 用 UNION 替代
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

陷阱 5：NOT IN / != 通常不走索引

sql

-- ❌ 否定条件通常需要全表扫描
SELECT * FROM users WHERE status != 'deleted';

-- ✅ 用部分索引替代
CREATE INDEX idx_active ON users(name) WHERE status != 'deleted';

陷阱 6：复合索引跳过左侧列

sql

-- 索引 (city, age)
-- ❌ 跳过了 city，无法使用索引
SELECT * FROM users WHERE age > 25;

-- ✅ 包含 city
SELECT * FROM users WHERE city = '北京' AND age > 25;

陷阱 7：ORDER BY 和索引排序方向不匹配

sql

-- 索引 (city ASC, age ASC)
-- ❌ 一个升序一个降序，无法利用索引排序
SELECT * FROM users ORDER BY city ASC, age DESC;

-- ✅ 方向一致
SELECT * FROM users ORDER BY city ASC, age ASC;

陷阱 8：SELECT * 阻止覆盖索引

sql

-- 索引 (category, price)
-- ❌ SELECT * 需要回表，无法成为覆盖索引
SELECT * FROM products WHERE category = '电子';

-- ✅ 只查索引中有的列，触发覆盖索引
SELECT category, price FROM products WHERE category = '电子';

本章小结

知识点	要点
索引原理	B-Tree 结构，按索引列排序存储 + rowid 回表
索引类型	普通 / 唯一 / 复合 / 部分 / 表达式索引
复合索引	列顺序很重要，遵循最左前缀原则
EXPLAIN QUERY PLAN	`SCAN` = 全表扫描 🔴；`SEARCH USING INDEX` = 索引查找 🟢
覆盖索引	查询列都在索引内，不回表，最快
建索引原则	WHERE / JOIN / ORDER BY 频繁出现的高选择性列
常见陷阱	函数包裹 / 类型不匹配 / LIKE '%x' / OR / 跳过左列 / SELECT *

下一章预告：学习事务与数据安全——BEGIN / COMMIT / ROLLBACK，WAL 日志模式，以及 SQLite 的并发锁机制。

第六章 索引与查询优化 ​

6.1 什么是索引 —— 类比与原理 ​

一句话理解 ​

没有索引 vs 有索引 ​

索引的本质 ​

索引的代价 ​

6.2 创建与管理索引 ​

创建普通索引 ​

创建唯一索引 ​

复合索引（多列索引） ​

部分索引（Partial Index） ​

表达式索引 ​

查看已有索引 ​

删除索引 ​

IF NOT EXISTS ​

6.3 用 EXPLAIN QUERY PLAN 分析查询 ​

基本用法 ​

输出解读 ​

四种扫描方式对比 ​

实战优化演示 ​

JOIN 的执行计划 ​

6.4 索引使用原则与常见陷阱 ​

该建索引的列 ​

不该建索引的列 ​

常见陷阱（索引失效的情况） ​

本章小结 ​

第六章索引与查询优化

6.1 什么是索引 —— 类比与原理

一句话理解

没有索引 vs 有索引

索引的本质

索引的代价

6.2 创建与管理索引

创建普通索引

创建唯一索引

复合索引（多列索引）

部分索引（Partial Index）

表达式索引

查看已有索引

删除索引

IF NOT EXISTS

6.3 用 EXPLAIN QUERY PLAN 分析查询

基本用法

输出解读

四种扫描方式对比

实战优化演示

JOIN 的执行计划

6.4 索引使用原则与常见陷阱

该建索引的列

不该建索引的列

常见陷阱（索引失效的情况）

本章小结