怎样为数据库表添加合适的索引？

本文目录导读：

1. 核心思想：从查询出发
2. 第一步：分析查询模式（最关键）
3. 第二步：遵循索引设计的黄金原则
4. 第三步：如何设计一个具体的联合索引？
5. 第四步：避坑指南（常见错误）
6. 第五步：验证与调优
7. 总结流程

这个问题是数据库优化的核心,为数据库表添加合适的索引，并不是简单地“给查询的列加索引”，而是需要结合数据模型、查询模式、数据量级和数据库引擎（最常用的是 MySQL InnoDB）来系统考虑。

以下是一套完整的、可操作的步骤和原则，帮你做出合理的索引决策。

核心思想：从查询出发

索引的本质是为了加速查询（SELECT、UPDATE、DELETE的WHERE条件），但同时会减慢写入（INSERT、UPDATE、DELETE）。

第一步不是看表有哪些列，而是看你的应用程序有哪些核心查询。

---

第一步：分析查询模式（最关键）

你需要找出那些慢的、且频繁执行的查询，记录下它们的 WHERE 条件、JOIN 条件、ORDER BY 和 GROUP BY 子句。

示例查询：

-- 查询1：按用户和状态查订单
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'paid' 
ORDER BY created_at DESC 
LIMIT 20;
-- 查询2：按月统计销售额
SELECT MONTH(created_at) as month, SUM(amount) 
FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY MONTH(created_at);

---

第二步：遵循索引设计的黄金原则

为 WHERE 条件中的列建立索引

• 最常用、最直接的原则。
• 对于 WHERE column = value 或 WHERE column IN (...), 单列索引通常有效。
• 对于范围查询 WHERE column > value 或 BETWEEN，索引可以帮助快速定位范围起点。

为 JOIN 的连接列建立索引

• 如果两表通过 orders.user_id = users.id 连接，必须在 orders.user_id 上建立索引（users.id 已是主键索引）。
• 这个原则通常比 WHERE 更重要，因为 JOIN 操作代价高昂，没有索引，数据库会全表扫描内表。

为 ORDER BY 和 GROUP BY 的列建立索引

• 这可以避免文件排序（filesort）和临时表，大幅提升排序和分组性能。
• 注意： ORDER BY 的列必须是索引的最左前缀，且排序方向一致，否则可能不会被使用。

优先使用 联合索引 而不是多个单列索引

• 当查询条件有多个列时,联合索引（如 INDEX(user_id, status)）比两个单独索引（INDEX(user_id) 和 INDEX(status)）高效得多。
• 最左前缀原则：索引 (A, B, C) 可以加速：
  • WHERE A = ?
  • WHERE A = ? AND B = ?
  • WHERE A = ? AND B = ? AND C = ?
  • WHERE A = ? ORDER BY B （索引已排好序，避免额外排序）
• 无法使用：WHERE B = ? 或 WHERE C = ?（跳过了第一列A）。

---

第三步：如何设计一个具体的联合索引？

假设我们有上面的查询1： SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

如何设计这个索引？

1. 常规思路（简单直接）： INDEX idx_user_status (user_id, status)

   • 作用：快速定位到 user_id=123 且 status='paid' 的所有行。
   • 问题： 如果用户有大量已支付订单，数据库还需要对这些行进行排序，这会消耗时间和内存。

2. 进阶思路（覆盖排序）： INDEX idx_user_status_created (user_id, status, created_at DESC)

   • 作用：
     • 快速定位到目标数据。
     • 由于 created_at 已经在索引中排好序（注意：MySQL 8.0 支持降序索引），ORDER BY created_at DESC 可以直接通过索引逆序扫描来获取，完全避免了文件排序。
     • 如果需要查询的列全部在索引中（即查询是 SELECT user_id, status, created_at ...），甚至可以实现覆盖索引（只用索引，不回表查数据），性能最高。

对于该查询，(user_id, status, created_at DESC) 是最优解。

---

第四步：避坑指南（常见错误）

1. 不要给每个列都加索引

   • 索引会占用磁盘空间,并拖慢写入速度。
   • 每张表的核心业务字段通常只需 3-5 个索引（1个主键，2-3个联合索引）。

2. 避免冗余索引

   • 已有索引 (A, B)，再建索引 (A) 是浪费，因为 (A, B) 已经可以覆盖 A 列的查询。
   • 但若已有 (A)，再建 (A, B) 是合理的，因为后者的排序和过滤能力更强。

3. 不要把索引放在计算或函数里

   • WHERE DATE(created_at) = '2024-01-01' → 索引失效。
   • 应改为：WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
   • WHERE price + 10 > 100 → 索引失效，应改为 WHERE price > 90。

4. 注意索引的选择性

   • 索引列的值越分散（区分度越高），效果越好，性别”列（只有男/女）索引效果很差，因为还要回表扫一半的数据。
   • “身份证号”或“邮箱”这类高选择性的列，索引效果极佳。

5. 索引不是万能的

   • 对于需要扫描表中大量数据的查询（如 SELECT * FROM huge_table），索引失效，全表扫描可能更快。
   • 对于 LIKE '%keyword%' 这种左模糊匹配，索引通常失效，可以考虑全文索引或搜索引擎（如 Elasticsearch）。

---

第五步：验证与调优

创建索引后,必须验证它是否被实际使用了。

使用 EXPLAIN 命令：

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

关键输出解读：

• type：最好看到 ref（等值匹配）或 range（范围匹配），如果是 ALL，就是全表扫描，没走索引。
• key：显示实际使用的索引名，如果是 NULL，表示没使用索引。
• rows：预估扫描的行数，越小越好。
• Extra：看看有没有 Using filesort（需要额外排序，应尽量避免）、Using temporary（用了临时表，应尽量避免）、Using index（覆盖索引，最好）。

总结流程

1. 定位慢查询：通过慢查询日志（Slow Query Log）找出耗时最长的查询。
2. 分析查询：提取 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列。
3. 设计索引：
   • 等值条件放前面。
   • 范围条件放中间或后面。
   • 排序字段紧跟其后。
   • 考虑覆盖索引（把 SELECT 的列也加入索引）。
4. 创建索引：

   -- 单列
   CREATE INDEX idx_user_id ON orders(user_id);
   -- 联合
   CREATE INDEX idx_user_status ON orders(user_id, status, created_at DESC);

5. 验证：用 EXPLAIN 确认查询走了正确的索引，且 Extra 没有 filesort 或 temporary。
6. 监控：上线后继续观察慢查询和数据库性能。

遵循这套流程,你就能为你的数据库表设计出既高效又经济的索引方案。