数据库索引：从入门到踩坑

去年面试了个候选人，问他索引原理，答得头头是道：B+树、聚簇索引、覆盖索引……听着很专业。

然后问他：你线上加过索引吗？遇到过什么问题？

沉默了。

这篇文章不讲原理（网上太多了），主要讲讲实际踩过的坑。

一个真实的案例

有个查询特别慢：

SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

orders 表有 500 万数据，没有合适的索引。直觉告诉我应该给 user_id 加索引：

CREATE INDEX idx_user_id ON orders(user_id);

加了之后，查询时间从 5 秒降到了 0.5 秒。还不错。

但过了几天，运营反馈导出报表的时候特别慢。一看 SQL：

SELECT * FROM orders WHERE status = 'paid' AND created_at > '2025-01-01';

刚才加的索引用不上，因为查询条件没有 user_id。

这就是典型的”头痛医头、脚痛医脚”。索引不是加一个就完事的，要根据实际查询模式来设计。

数据库性能监控

索引设计原则

最左前缀原则

复合索引要按查询条件的使用频率排序：

-- 查询模式
WHERE user_id = ? AND status = ?
WHERE user_id = ?

-- 索引设计
CREATE INDEX idx_user_status ON orders(user_id, status);

user_id 放前面，因为单独用 user_id 查询的场景也有。如果 status 放前面，第二条查询就用不上索引了。

选择性高的列优先

选择性 = 不同值的数量 / 总行数。选择性越高，索引效果越好。

-- 性别：选择性低（只有 2 个值）
SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users; -- 约 0.0000001

-- 用户ID：选择性高
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders; -- 约 0.8

给性别加索引，意义不大。

避免索引失效

以下情况会导致索引失效：

-- 对索引列使用函数
WHERE DATE(created_at) = '2025-01-01';  -- 失效
WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02';  -- 生效

-- 隐式类型转换
WHERE phone = 13800138000;  -- phone 是 varchar，失效
WHERE phone = '13800138000';  -- 生效

-- 使用 OR
WHERE user_id = 123 OR status = 'paid';  -- 失效
-- 改成 UNION
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE status = 'paid';

查询计划分析

（图：用 EXPLAIN 分析查询计划，type 列可以看到索引使用情况）

EXPLAIN 使用指南

看懂 EXPLAIN 是基本功：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

重点关注这几列：

列含义好的值坏的值
type访问类型const, ref, rangeALL, index
key实际用的索引有索引名NULL
rows预估扫描行数越少越好越多越差
Extra额外信息Using indexUsing filesort, Using temporary

一个真实的例子：

-- 慢查询
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | orders | ref  | idx_user_id   | idx_user_id | 4 | const | 5000 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+

Using filesort 说明排序没走索引。解决：

CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

再跑一次，Extra 变成了空，查询时间从 0.5 秒降到 0.01 秒。

索引的代价

索引不是免费的，每次写入都要更新索引。

-- 没有索引
INSERT INTO orders (...) VALUES (...);  -- 很快

-- 有 5 个索引
INSERT INTO orders (...) VALUES (...);  -- 慢 5 倍

曾经有同事给一张高频写入的表加了 8 个索引，结果写入性能暴跌，数据库 CPU 直接飙到 90%。

删除了几个不必要的索引后，CPU 降到了 30%。

原则：索引数量控制在 5 个以内，除非你有充分的理由。

一些高级技巧

覆盖索引

查询的列都在索引里，不用回表：

-- 创建索引
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

-- 查询
SELECT user_id, status, created_at FROM orders WHERE user_id = 123;

Extra 显示 Using index，性能最好。

前缀索引

长字符串列，可以只索引前 N 个字符：

CREATE INDEX idx_email ON users(email(10));

问题是无法用于 ORDER BY 和覆盖索引。权衡使用。

延迟关联

分页查询深页很慢：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;  -- 慢

优化：

SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

先走索引只查 ID，再关联查详情，快很多。

总结

索引是数据库优化的核心手段，但不是万能药。关键在于：

  1. 了解查询模式，设计合适的索引
  2. 用 EXPLAIN 验证索引是否生效
  3. 监控慢查询，持续优化
  4. 控制索引数量，避免写入性能下降

最后，加索引之前先在测试环境验证效果，别像我当年一样，直接在生产环境加了个索引，然后看着慢查询列表一脸懵逼。