MySQL 索引优化实战：从原理到调优，一篇搞定

前言

数据库慢查询是后端开发最常遇到的性能问题之一，而索引是解决慢查询最直接的手段。但索引不是建得越多越好，建错了反而拖慢写入、浪费空间。本文从索引原理出发，梳理常见索引类型、失效场景、优化思路和实战案例，适合日常开发和面试查阅。

一、为什么需要索引？

没有索引时，查询需要全表扫描（Full Table Scan），数据量一大就慢。索引的本质是用空间换时间，通过额外的数据结构（B+ 树）快速定位数据，把 O(n) 的扫描变成 O(log n) 的查找。

但索引也有代价：

• 占用磁盘空间
• 每次 INSERT/UPDATE/DELETE 都要维护索引树，影响写入性能
• 索引过多会导致优化器选错索引，反而变慢

所以索引要建在刀刃上：高频查询字段、区分度高的字段、WHERE/JOIN/ORDER BY/GROUP BY 中常用的字段。

二、索引的数据结构：为什么是 B+ 树？

MySQL InnoDB 默认使用 B+ 树作为索引结构，而不是哈希、二叉树或 B 树，原因如下：

B+ 树的关键特点：

• 所有数据都存在叶子节点，非叶子节点只存索引
• 叶子节点之间用双向链表连接，范围查询效率高
• 树的高度通常为 3~4 层，一次查询只需 3~4 次磁盘 IO

三、聚簇索引 vs 非聚簇索引

InnoDB 中索引分为两类：

• 聚簇索引（Clustered Index）：叶子节点存储整行数据。InnoDB 按主键组织数据，主键索引就是聚簇索引。如果没有显式主键，InnoDB 会选唯一非空索引，都没有就自动生成一个隐藏的 row_id。
• 非聚簇索引（Secondary Index / 二级索引）：叶子节点存储的是索引列的值 + 主键值。查询时需要先查二级索引拿到主键，再回表查聚簇索引拿到完整行数据，这个过程叫回表。

覆盖索引

如果查询的列都在二级索引中，就不需要回表，这就是覆盖索引。例如：

-- idx_name_age (name, age) 是联合索引
SELECT name, age FROM users WHERE name = '张三';  -- 覆盖索引，不回表
SELECT name, age, email FROM users WHERE name = '张三';  -- 需要回表查 email

覆盖索引是优化的重要手段，能显著减少 IO。

四、常见索引类型

最左前缀原则

联合索引 (a, b, c) 相当于建立了 (a)、(a, b)、(a, b, c) 三个索引。查询条件必须从最左边开始匹配，否则索引失效：

--  走索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

--  不走索引（跳过了 a）
WHERE b = 2
WHERE c = 3

--  部分走索引（a 走索引，b 不走）
WHERE a = 1 AND c = 3

五、索引失效的常见场景

1. 对索引列做函数运算

--  索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;

--  走索引
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

2. 隐式类型转换

-- phone 是 VARCHAR 类型
--  索引失效，字符串和数字比较会隐式转换
SELECT * FROM users WHERE phone = 13800138000;

--  走索引
SELECT * FROM users WHERE phone = '13800138000';

3. LIKE 左模糊

--  索引失效
SELECT * FROM users WHERE name LIKE '%张%';

--  走索引
SELECT * FROM users WHERE name LIKE '张%';

4. OR 连接非索引列

--  如果 age 没有索引，整个查询走全表扫描
SELECT * FROM users WHERE name = '张三' OR age = 25;

5. NOT IN / != / IS NOT NULL

-- 通常不走索引（优化器可能选择全表扫描）
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status NOT IN (1, 2);

6. 联合索引不满足最左前缀

见上文"最左前缀原则"。

六、如何分析 SQL 是否走索引？

用 EXPLAIN 命令：

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

重点关注这几个字段：

优化目标：让 type 至少达到 ref 或 range，避免 ALL（全表扫描）；Extra 中尽量避免 Using filesort 和 Using temporary。

七、索引优化实战案例

案例 1：分页优化

-- 深分页慢查询
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;

原理：子查询只查主键（覆盖索引），拿到 10 个 id 后再回表，避免大量回表操作。

案例 2：避免 SELECT *

--  可能回表
SELECT * FROM users WHERE name = '张三';

--  覆盖索引，不回表
SELECT id, name FROM users WHERE name = '张三';  -- idx_name(id, name)

案例 3：范围查询后索引失效

-- idx_name_age_status (name, age, status)
-- name 走索引，age 走范围，status 索引失效
SELECT * FROM users WHERE name = '张三' AND age > 20 AND status = 1;

联合索引中，范围查询后面的列无法使用索引。如果 status 也需要走索引，可以考虑调整索引列顺序或拆成多个索引。

案例 4：ORDER BY 优化

--  如果 idx_name_age (name, age)，以下查询可以利用索引排序
SELECT * FROM users WHERE name = '张三' ORDER BY age;

--  以下无法利用索引排序，会 Using filesort
SELECT * FROM users WHERE name > '张三' ORDER BY age;
SELECT * FROM users WHERE name = '张三' ORDER BY age DESC, create_time ASC;

八、索引设计最佳实践

1. 区分度高的列建索引：性别（只有男女）不适合建索引，用户 ID、手机号适合。
2. 联合索引把区分度高的放左边：但不是绝对，要结合查询场景和最左前缀原则。
3. 避免过多索引：单表索引建议不超过 5~8 个，写入频繁的表更要谨慎。
4. 优先使用前缀索引：长字符串（如 URL、邮箱）可以取前缀建索引，节省空间。
5. 删除不再使用的索引：用 pt-duplicate-key-checker 等工具定期清理冗余索引。
6. **大表加索引用 ****ALTER TABLE ... ALGORITHM=INPLACE**：避免锁表时间过长（MySQL 5.6+ 支持 Online DDL）。

九、常见追问

1. 主键为什么建议用自增 ID？

InnoDB 聚簇索引按主键顺序存储，自增 ID 保证数据顺序写入，减少页分裂和碎片。用 UUID 做主键会导致随机 IO、页分裂频繁，插入性能差且占用空间大。

2. 什么情况下索引会失效但优化器还是选择走索引？

优化器基于成本估算选择执行计划。如果估算走索引的成本比全表扫描低，即使部分失效也可能走索引。可以用 FORCE INDEX 强制指定索引，但一般不建议，优先让优化器自己选择。

3. 怎么判断一个索引是否该建？

看慢查询日志，用 EXPLAIN 分析，结合业务查询频率。不要凭感觉建索引，要用数据说话。

总结

索引优化的核心思路：

1. 用 EXPLAIN 定位慢查询
2. 检查是否全表扫描（type = ALL）
3. 确认索引是否失效（函数运算、类型转换、最左前缀等）
4. 优先考虑覆盖索引，减少回表
5. 定期清理冗余索引，避免过度索引

掌握这些，日常开发中 80% 的 SQL 性能问题都能搞定。