11、索引与查询优化：CREATE INDEX、EXPLAIN QUERY PLAN、复合索引、索引维护

说实话，索引这东西，刚入行的时候觉得它挺神秘。不就是个加速查询的东西嘛，加就完了。后来被现实狠狠教育过几次，才明白索引是把双刃剑。用好了，查询快如闪电；用砸了，写入慢得像蜗牛，甚至查询也变慢。

今天咱们就把索引这事儿彻底聊透。我会结合 SQLite 来演示，因为它的语法简单，而且 EXPLAIN QUERY PLAN 这个工具特别好用。你把这些思路搞懂了，换到 MySQL、PostgreSQL 上，道理是一样的。

11.1 索引的本质：为什么能加速？

没有索引的时候，数据库查数据怎么查？全表扫描。说白了就是一条一条地翻，翻到目标为止。如果表里有 100 万行，查一个 id=999999 的记录，最坏情况要翻 100 万次。

有了索引就不一样了。索引本质上是一棵 B+ 树（或者类似的结构）。它把数据的关键字排好序，然后通过二分查找来定位。100 万条数据，二分查找最多只需要 20 次比较。这差距，你想想看。

11.2 CREATE INDEX：基础索引的创建

语法很简单，但细节决定成败。咱们先看一个最基础的例子。

-- 创建一个简单的单列索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引（保证 email 不重复）
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 创建索引时可以指定排序
CREATE INDEX idx_users_age ON users(age DESC);

我在项目中遇到过一个问题：有个同事给一个只有 200 条记录的小表加了索引。结果查询没快多少，写入反而慢了。为什么？因为索引维护有开销。对于小表，全表扫描可能比走索引还快。所以我的建议是：数据量低于几千行，别急着加索引。

11.3 EXPLAIN QUERY PLAN：看穿查询的执行计划

这是优化查询最重要的工具，没有之一。它能告诉你数据库到底是怎么执行你的 SQL 的。咱们来看个例子。

-- 先看看没有索引时的查询计划
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@example.com';
-- 输出：SCAN TABLE users
-- 看到 SCAN 了吗？这就是全表扫描，危险信号。

-- 创建索引后，再看
CREATE INDEX idx_users_email ON users(email);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@example.com';
-- 输出：SEARCH TABLE users USING INDEX idx_users_email (email=?)
-- 看到 SEARCH 和 USING INDEX 了吗？这才是走索引了。

我曾经接手过一个慢查询系统，查一个订单要 3 秒。我第一件事就是跑 EXPLAIN QUERY PLAN。结果发现，一个本该走索引的查询，因为 WHERE 条件里写了 LOWER(email)，导致索引失效，变成了全表扫描。嗯，这里要注意：对索引列使用函数，索引通常就废了。

11.4 复合索引：多列查询的利器

单列索引好理解。但实际业务中，查询条件往往不止一个。比如查「某个城市、某个年龄段、最近一个月注册的用户」。这时候就需要复合索引了。

-- 创建一个复合索引，顺序很重要
CREATE INDEX idx_users_city_age_regdate 
ON users(city, age, reg_date);

-- 这个查询能完美利用上面的索引
SELECT * FROM users 
WHERE city = '北京' 
  AND age BETWEEN 20 AND 30 
  AND reg_date > '2024-01-01';

-- 但这个查询只能用到索引的前两列
SELECT * FROM users 
WHERE city = '北京' 
  AND age = 25;
-- 因为 reg_date 条件没出现，但 city 和 age 依然能走索引

复合索引有个「最左前缀原则」。什么意思？你创建了 (A, B, C) 的索引，那么查询条件必须从 A 开始，依次往右。你可以只用 A，也可以用 A+B，或者 A+B+C。但如果你跳过了 A 直接查 B，索引就废了。

查询条件	能否使用索引 (city, age, reg_date)	说明
WHERE city = '北京'	✅ 完全使用	匹配第一列
WHERE city = '北京' AND age = 25	✅ 完全使用	匹配前两列
WHERE city = '北京' AND age = 25 AND reg_date > '2024-01-01'	✅ 完全使用	匹配全部三列
WHERE age = 25	❌ 无法使用	跳过了第一列 city
WHERE city = '北京' AND reg_date > '2024-01-01'	⚠️ 部分使用	只用到了 city，跳过了 age

11.5 索引维护：别建完就不管了

索引不是建好就一劳永逸的。随着数据的增删改，索引会慢慢「变质」。具体来说，有这几个问题：

• 索引碎片：频繁的插入和删除，会让索引页变得不连续。就像书架上的书，抽掉几本再塞几本，时间长了就乱糟糟的。
• 统计信息过时：数据库优化器靠统计信息来决定是否走索引。如果统计信息没更新，优化器可能做出错误的选择。
• 冗余索引：随着业务变化，有些索引可能不再需要了。留着它们只会浪费空间和写入性能。

怎么维护？SQLite 里可以这样：

-- 重建索引，整理碎片
REINDEX idx_users_email;

-- 或者重建整个数据库的所有索引
REINDEX;

-- 分析表，更新统计信息
ANALYZE;

-- 查看当前数据库的所有索引
SELECT * FROM sqlite_master WHERE type = 'index';

我曾经接手过一个跑了三年的系统，数据库里居然有 47 个索引。其中 12 个是重复的，8 个从来没被用过。你想想看，每次插入一条数据，要维护 47 个索引，那写入能快吗？后来我清理掉那些冗余索引，写入性能提升了 30%。

11.6 知识体系总览

下面这张图，把索引相关的核心知识点串起来了。你可以把它当作一个检查清单，每次做索引优化时对照着看。

11.7 实战建议：索引优化的正确姿势

最后，我总结一下做索引优化的步骤。这套方法我在多个项目里验证过，靠谱。

1. 先定位慢查询。用 EXPLAIN QUERY PLAN 找出那些全表扫描的查询。
2. 分析查询条件。看看 WHERE、JOIN、ORDER BY 里用了哪些列。
3. 设计索引。把区分度高的列放前面，遵循最左前缀原则。
4. 验证效果。加完索引后，再用 EXPLAIN QUERY PLAN 确认是否走了索引。
5. 监控写入性能。如果写入变慢太多，考虑减少索引数量。
6. 定期维护。重建索引、更新统计信息、清理冗余索引。