你有没有过这样的经历：一个简单的 SELECT * FROM user WHERE name='张三' 查询，在本地测试时只有几百条数据，秒出结果；一到线上跑了几个月，数据量涨到几十万、几百万，突然就卡得要死，甚至把数据库拖垮？

90% 以上的数据库慢查询问题，根源都在索引没设计好。

这篇文章我会用最通俗的语言，从 "什么是索引" 讲到 "索引底层原理"，再到 "怎么设计出高效的索引"，全程没有晦涩的公式，只有生活中的类比和真实的 SQL 例子。看完你不仅能彻底搞懂索引，还能直接用到实际项目中。

1. 先搞懂：索引到底是个啥？

1.1 最经典的类比：书籍的目录

想象一下，你手里有一本 1000 页的《MySQL 从入门到精通》，现在你想找到 "索引" 这一章的内容。

• 没有目录的情况：你只能从第 1 页开始，一页一页地翻，直到找到 "索引" 这三个字。运气不好的话，你可能要翻 900 多页才能找到。这在数据库里叫做全表扫描。
• 有目录的情况：你先翻到书的前几页看目录，找到 "索引" 对应的页码是第 350 页，然后直接翻到第 350 页。整个过程只需要几秒钟。

索引就是数据库的 "目录"。它是一种特殊的数据结构，存储了表中某一列（或几列）的值，以及这些值对应的实际数据行的位置。有了索引，数据库就不用再傻乎乎地扫描整个表，而是能直接定位到你要找的数据。

1.2 索引的本质：用空间换时间

天下没有免费的午餐。索引能让查询变快，是因为它付出了两个代价：

1. 占用更多的磁盘空间：索引本身也是数据，需要单独存储。一张表的数据量越大，索引占用的空间也越大。
2. 降低写入速度：当你对表进行INSERT、UPDATE、DELETE操作时，不仅要修改表中的数据，还要更新对应的索引。就像书的内容改了，目录也要跟着改一样。

所以，索引不是越多越好。很多新手会犯一个错误：给表中的每一列都加上索引，结果查询速度没快多少，写入速度反而慢得像蜗牛。

1.3 索引能解决什么问题？

索引的核心作用只有一个：加速查询。但它能衍生出很多好处：

• 加速WHERE条件的过滤（最常用）
• 加速表与表之间的JOIN连接
• 加速ORDER BY排序（不用再在内存中排序）
• 加速GROUP BY分组（不用再创建临时表）
• 把随机的磁盘 IO 变成顺序的磁盘 IO（这是数据库性能的关键）

2. 为什么 MySQL 偏偏选了 B + 树当索引？

很多人都知道 MySQL 的索引是用 B + 树实现的，但很少有人能说清楚：为什么不用数组、链表、二叉树，偏偏要用 B + 树？

要回答这个问题，我们得先搞懂一个数据库最核心的痛点：磁盘 IO 太慢了。

2.1 先理解一个关键概念：磁盘 IO

我们的电脑有两种存储设备：内存和磁盘。

• 内存：速度快，但容量小，断电数据就没了
• 磁盘：速度慢，但容量大，断电数据不会丢

数据库的数据是存在磁盘上的。每次查询数据，数据库都需要从磁盘上把数据读到内存里。这个过程叫做磁盘 IO。

一次磁盘 IO 的时间大约是 10 毫秒，看起来很短，但如果一次查询需要 1000 次磁盘 IO，那就是 10 秒，这在用户看来已经是无法忍受的慢了。

所以，数据库所有的优化，本质上都是在尽量减少磁盘 IO 的次数。

2.2 为什么其他数据结构都不行？

我们一个个来看，为什么这些常见的数据结构都不适合做数据库索引。

2.2.1 数组

• 优点：查询速度极快，通过下标直接访问，时间复杂度 O (1)
• 缺点：插入和删除数据时，需要移动后面的所有元素，时间复杂度 O (n)

不适合的原因：数据库的数据是频繁修改的。如果用数组做索引，每次插入一条数据都要移动半个数组，性能会差到爆炸。

2.2.2 链表

• 优点：插入和删除速度快，只需要修改指针，时间复杂度 O (1)
• 缺点：查询速度极慢，只能从头节点开始遍历，时间复杂度 O (n)

不适合的原因：查询是数据库最常用的操作。如果用链表做索引，查询一条数据又变回了全表扫描。

2.2.3 二叉搜索树

• 优点：查询速度快，平均时间复杂度 O (logn)
• 缺点：极端情况下会退化成链表，时间复杂度 O (n)

不适合的原因：如果你的数据是按顺序插入的（比如 1,2,3,4,5），二叉搜索树就会变成一条长长的链表。这时候查询 5 需要查 5 次，和全表扫描没区别。

2.2.4 平衡二叉树（AVL 树）

• 优点：严格平衡，查询速度稳定，时间复杂度 O (logn)
• 缺点：插入和删除数据时需要多次旋转来保持平衡，开销很大；树太高了

不适合的原因：即使是平衡二叉树，树高也太高了。比如 100 万条数据，平衡二叉树的高度大约是 20 层。这意味着查询一条数据需要 20 次磁盘 IO，太慢了。

2.2.5 B 树

• 优点：多路平衡树，树高低；每个节点可以存储多个关键字
• 缺点：非叶子节点也存储数据，每个节点能存储的关键字少；范围查询麻烦

不适合的原因：B 树的非叶子节点也存数据，导致每个节点能存的关键字数量变少，树高比 B + 树高。而且 B 树的叶子节点之间没有连接，范围查询需要多次回溯到上层节点。

2.3 B + 树：天生为数据库而生

B + 树是在 B 树的基础上改进而来的，它完美解决了上面所有的问题。

2.3.1 B + 树的三个核心特点

1. 所有数据都存在叶子节点，非叶子节点只存储索引键和指针
2. 叶子节点之间通过双向链表连接，形成一个有序的链表
3. 每个节点的大小等于 MySQL 的页大小（默认 16KB）

2.3.2 B + 树为什么这么牛？

我们一个个来看这三个特点带来的好处：

特点 1：所有数据都在叶子节点

• 非叶子节点不存数据，所以每个节点能存储更多的索引键。比如一个 16KB 的节点，每个索引键占 8 字节，每个指针占 8 字节，那么一个节点就能存大约 1000 个索引键。
• 这意味着 B + 树的树高极低。即使是 1 亿条数据，B + 树的高度也只有 3-4 层。也就是说，查询一条数据只需要 3-4 次磁盘 IO，这比平衡二叉树的 20 次快了 5 倍以上。

特点 2：叶子节点之间有双向链表

• 范围查询变得极其高效。比如你想查id BETWEEN 100 AND 200的数据，只要找到 id=100 的叶子节点，然后顺着链表往后遍历到 id=200 就行了，不用再回到上层节点一个个找。
• 排序也变得极其高效。因为叶子节点本身就是有序的，所以ORDER BY操作可以直接利用索引的顺序，不用再在内存中排序。

特点 3：每个节点等于一个磁盘页

• 每次磁盘 IO 正好能读取一个完整的节点，不会浪费磁盘 IO 的带宽。

3. MySQL 中索引的分类

MySQL 中的索引有很多种分类方式，我们从最实用的角度来讲解。

3.1 按功能分类（最常用）

3.1.1 普通索引（INDEX）

最基本的索引，没有任何限制，就是单纯用来加速查询的。就像图书馆里按书名排序的索引。

-- 创建普通索引
CREATE INDEX idx_user_name ON user(name);

-- 查看表的所有索引
SHOW INDEX FROM user;

-- 删除索引
DROP INDEX idx_user_name ON user;

3.1.2 唯一索引（UNIQUE）

索引列的值必须唯一，但允许有 NULL 值。如果是多个列的组合索引，那么这几个列的值的组合必须唯一。就像每个人的身份证号，不能重复。

-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_email ON user(email);

3.1.3 主键索引（PRIMARY KEY）

特殊的唯一索引，不允许有 NULL 值。一个表只能有一个主键索引。就像书的页码，是唯一标识每一页的。

-- 创建表时指定主键
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 给已存在的表添加主键
ALTER TABLE user ADD PRIMARY KEY (id);

3.1.4 组合索引（INDEX）

也叫联合索引，是在多个列上创建的索引。就像图书馆里先按作者排序，再按书名排序的索引。

-- 创建组合索引（先按name排序，name相同再按age排序）
CREATE INDEX idx_user_name_age ON user(name, age);

组合索引是索引优化中最重要的部分，后面我们会专门讲它的最左前缀原则。

3.2 按存储方式分类（最核心）

这是理解 InnoDB 索引的关键，也是面试最常问的点。

3.2.1 聚簇索引（Clustered Index）

聚簇索引的叶子节点存储的是整行数据。InnoDB 引擎的主键索引就是聚簇索引。

我用一个最经典的类比来解释：

聚簇索引就像一本按拼音排序的字典。字典的正文本身就是索引。你查 "中" 字，找到拼音 "zhong" 对应的页码，翻过去就是 "中" 字的解释。

聚簇索引的特点：

• 一个表只能有一个聚簇索引（因为数据只能按一种方式排序）
• 数据行本身就是索引的一部分
• 主键查询速度极快，因为不用回表
• 插入速度依赖于主键的顺序。如果主键是乱序插入的（比如用 UUID 做主键），会导致频繁的页分裂，性能很差。

3.2.2 非聚簇索引（Non-Clustered Index）

也叫二级索引，叶子节点存储的是主键值，而不是整行数据。InnoDB 中除了主键索引之外的所有索引都是非聚簇索引。

继续用字典的类比：

非聚簇索引就像字典后面的部首检字表。你先在部首表里找到 "中" 字对应的页码，然后再翻到正文里去看解释。

这个 "先查部首表，再查正文" 的过程，在数据库里叫做回表。

回表的代价： 回表会增加一次磁盘 IO。比如你用 name 索引查询 "张三"，先在 name 索引里找到张三的 id 是 123，然后再用 id=123 去主键索引里找张三的整行数据。这就需要两次磁盘 IO。

4. 组合索引的灵魂：最左前缀原则

最左前缀原则是组合索引中最重要、最容易搞错的原则。很多人索引建了一大堆，查询还是慢，就是因为没搞懂这个原则。

4.1 什么是最左前缀原则？

MySQL 会从左到右依次匹配组合索引的列，直到遇到范围查询（>、<、BETWEEN、LIKE）就停止匹配。

举个例子，假设我们有一个组合索引idx_abc(a, b, c)，那么这个索引的排序规则是：

1. 先按 a 列的值从小到大排序
2. a 列值相同的行，再按 b 列的值从小到大排序
3. a 和 b 列值都相同的行，再按 c 列的值从小到大排序

就像我们的通讯录，是先按姓氏拼音排序，姓氏相同再按名字拼音排序，名字相同再按电话号码排序。

4.2 哪些查询能用到这个索引？

我们一个个来看：

4.2.1 完全匹配

-- 能用到索引的所有三个列
SELECT * FROM table WHERE a=1 AND b=2 AND c=3;

这是最好的情况，索引的所有列都能用上。

4.2.2 匹配前 N 列

-- 能用到a和b列
SELECT * FROM table WHERE a=1 AND b=2;

-- 只能用到a列
SELECT * FROM table WHERE a=1;

4.2.3 跳过中间列

-- 只能用到a列，c列用不到
SELECT * FROM table WHERE a=1 AND c=3;

因为跳过了 b 列，MySQL 无法利用索引来过滤 c 列的值，只能先找到所有 a=1 的行，然后再一个个过滤 c=3 的行。

4.2.4 范围查询

-- 能用到a和b列，c列用不到
SELECT * FROM table WHERE a=1 AND b>2 AND c=3;

因为 b 列是范围查询，MySQL 在匹配到 b 列之后就停止了，后面的 c 列无法使用索引。

4.3 哪些查询不能用到这个索引？

-- 跳过第一列，完全用不到索引
SELECT * FROM table WHERE b=2 AND c=3;

-- 第一列是范围查询，后面的列都用不到
SELECT * FROM table WHERE a>1 AND b=2 AND c=3;

-- 顺序颠倒，MySQL会自动优化，能用到a列
SELECT * FROM table WHERE b=2 AND a=1;

注意最后一种情况，MySQL 的查询优化器会自动调整 WHERE 条件的顺序，所以即使你把 a=1 写在后面，也能用到索引。

5. 小心！这些情况会导致索引失效

即使你建了索引，在某些情况下 MySQL 也不会使用索引，这就是索引失效。这是慢查询最常见的原因。

5.1 对索引列使用函数或运算

-- 索引失效：对create_time列使用了YEAR()函数
SELECT * FROM user WHERE YEAR(create_time)=2023;

-- 索引有效：改成范围查询
SELECT * FROM user WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

为什么会失效？ 索引里存的是原始的列值，而不是函数计算后的值。所以 MySQL 无法利用索引来快速定位。

5.2 发生隐式类型转换

-- 索引失效：phone列是VARCHAR类型，却用数字13800138000来查询
SELECT * FROM user WHERE phone=13800138000;

-- 索引有效：加上引号，变成字符串
SELECT * FROM user WHERE phone='13800138000';

为什么会失效？ MySQL 会把 phone 列的所有值都转换成数字，然后再和 13800138000 比较。这个过程相当于对索引列使用了函数，所以索引失效。

这是新手最容易犯的错误之一，一定要注意！

5.3 LIKE 以通配符开头

-- 索引失效：以%开头
SELECT * FROM user WHERE name LIKE '%张三';

-- 索引有效：以%结尾
SELECT * FROM user WHERE name LIKE '张三%';

为什么会失效？ 索引是按前缀排序的。如果以 % 开头，MySQL 无法确定前缀是什么，只能全表扫描。

5.4 使用不等于、NOT IN、IS NOT NULL

-- 索引失效
SELECT * FROM user WHERE age != 18;
SELECT * FROM user WHERE id NOT IN (1,2,3);
SELECT * FROM user WHERE email IS NOT NULL;

这些操作都会导致 MySQL 放弃使用索引，转而进行全表扫描。

5.5 OR 连接的条件中有非索引列

-- 索引失效：age列没有索引
SELECT * FROM user WHERE name='张三' OR age=18;

因为 OR 两边只要有一个条件满足就行。如果 name=' 张三 ' 用了索引，而 age=18 需要全表扫描，那 MySQL 还不如直接全表扫描一次来得快。

5.6 MySQL 认为全表扫描更快

当你查询的数据量占表中数据的很大比例（通常超过 20%-30%）时，MySQL 会认为全表扫描比使用索引更快。

这是因为使用索引需要先查索引，再回表查数据，而全表扫描是顺序 IO，速度比随机 IO 快很多。当需要查询的数据很多时，全表扫描的总时间反而更短。

6. 索引优化的高级技巧

掌握了上面的基础知识，你已经能解决 80% 的索引问题了。下面这几个高级技巧，能让你的索引性能再上一个台阶。

6.1 覆盖索引：避免回表的终极武器

覆盖索引是指索引包含了查询所需的所有列，不需要回表查询。

这是索引优化中最有效、最常用的手段。

举个例子：

-- 创建组合索引
CREATE INDEX idx_user_name_age ON user(name, age);

-- 覆盖索引查询：只查name和age，不需要回表
SELECT name, age FROM user WHERE name='张三';

-- 非覆盖索引查询：需要查email，必须回表
SELECT name, age, email FROM user WHERE name='张三';

第一个查询只需要查 name 和 age，而这两个列正好在索引里，所以 MySQL 可以直接从索引里拿到数据，不用再去主键索引里回表。这就把两次磁盘 IO 变成了一次，性能提升了一倍。

怎么判断是不是覆盖索引？用EXPLAIN查看执行计划，如果Extra字段里有Using index，就说明是覆盖索引。

6.2 索引下推（ICP）：减少回表次数

索引下推是 MySQL 5.6 引入的一个重要优化特性。它允许在索引遍历过程中，对索引中包含的列进行条件过滤，减少回表次数。

还是用刚才的例子：

-- 组合索引 idx_user_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age=18;

没有索引下推时：

1. 先通过 name LIKE ' 张 %' 找到所有符合条件的索引记录（可能有 100 条）
2. 然后根据这 100 条记录的主键值，一个个回表查询整行数据
3. 最后在内存中过滤出 age=18 的记录（可能只有 10 条）

有索引下推时：

1. 先通过 name LIKE ' 张 %' 找到所有符合条件的索引记录（100 条）
2. 在索引中直接过滤出 age=18 的记录（10 条）
3. 只对这 10 条记录进行回表查询

你看，回表次数从 100 次变成了 10 次，性能提升了 10 倍！

怎么判断有没有用到索引下推？用EXPLAIN查看执行计划，如果Extra字段里有Using index condition，就说明用到了索引下推。

6.3 前缀索引：给大字段 "瘦身"

对于很长的字符串列（比如 VARCHAR (255) 的邮箱、地址），如果给整个列建索引，索引会变得很大，查询效率也会降低。

这时候我们可以只对字符串的前几个字符创建索引，这就是前缀索引。

-- 对email列的前10个字符创建索引
CREATE INDEX idx_user_email_prefix ON user(email(10));

怎么选择前缀长度？前缀长度不是越短越好，也不是越长越好。我们需要找到一个平衡点，让前缀的区分度尽可能接近整个列的区分度。

可以用下面的 SQL 来计算不同前缀长度的区分度：

-- 计算整个列的区分度
SELECT COUNT(DISTINCT email)/COUNT(*) FROM user;

-- 计算前5个字符的区分度
SELECT COUNT(DISTINCT LEFT(email,5))/COUNT(*) FROM user;

-- 计算前10个字符的区分度
SELECT COUNT(DISTINCT LEFT(email,10))/COUNT(*) FROM user;

当某个前缀长度的区分度接近整个列的区分度时，这个长度就是合适的。

6.4 联合索引的顺序优化

创建联合索引时，区分度高的列应该放在前面。

什么是区分度？区分度就是列中不同值的数量占总数量的比例。比例越高，区分度越高。

比如，name 列有 1000 个不同的值，gender 列只有 2 个不同的值，那么 name 列的区分度就比 gender 列高得多。

-- 好的顺序：区分度高的name在前
CREATE INDEX idx_user_name_gender ON user(name, gender);

-- 不好的顺序：区分度低的gender在前
CREATE INDEX idx_user_gender_name ON user(gender, name);

为什么？因为区分度高的列放在前面，能更快地过滤掉大部分数据，减少后面需要扫描的行数。

7. 怎么分析索引的使用情况？

光会建索引还不够，你还得会分析索引有没有被用到，用得好不好。MySQL 提供了一个非常强大的工具：EXPLAIN。

7.1 EXPLAIN 的基本使用

只需要在你的 SELECT 语句前面加上EXPLAIN关键字，MySQL 就会输出这条语句的执行计划，而不是执行这条语句。

EXPLAIN SELECT * FROM user WHERE name='张三';

执行计划有很多字段，我们只需要重点关注这三个：type、key、Extra。

7.2 最重要的字段：type

type字段表示 MySQL 访问表的方式，也就是它决定了查询的快慢。

从好到坏依次是：system > const > eq_ref > ref > range > index > ALL

• system：表只有一行，这是 const 类型的特例，几乎不会出现
• const：主键或唯一索引的等值查询，最多返回一行数据，速度极快
• eq_ref：主键或唯一索引的连接查询，对于每个来自前一个表的行，在当前表中最多匹配一行
• ref：非唯一索引的等值查询，非常常见
• range：范围查询（>、<、BETWEEN、IN 等），还不错
• index：扫描整个索引树，比全表扫描好一点，但也很慢
• ALL：全表扫描，性能最差，必须优化

记住： 如果你的type是ALL，那说明这条查询肯定有问题，必须优化。

7.3 其他重要字段

• key：MySQL 实际使用的索引。如果为 NULL，说明没有使用索引。
• rows：MySQL 认为必须检查的行数。这个值越小越好。
• Extra：额外的信息。常见的有：
  • Using index：使用了覆盖索引，非常好
  • Using index condition：使用了索引下推，不错
  • Using where：使用了 WHERE 条件过滤数据
  • Using filesort：需要在内存中排序，性能差，需要优化
  • Using temporary：需要创建临时表，性能很差，必须优化

8. 索引设计的最佳实践

最后，我给你总结了一套索引设计的最佳实践，你可以直接用到实际项目中。

8.1 应该创建索引的情况

1. 经常出现在WHERE子句中的列
2. 经常用于JOIN连接的列
3. 经常用于ORDER BY排序的列
4. 经常用于GROUP BY分组的列
5. 区分度高的列（如用户 ID、邮箱、手机号）

8.2 不应该创建索引的情况

1. 数据量小的表（几百行以内，全表扫描更快）
2. 经常更新的列（更新时需要同时更新索引）
3. 区分度低的列（如性别、状态，只有几个不同的值）
4. 不会出现在WHERE、JOIN、ORDER BY、GROUP BY中的列
5. NULL 值太多的列（索引对 NULL 值的处理效率不高）

8.3 其他注意事项

1. 主键尽量用自增 ID，不要用 UUID（乱序插入会导致页分裂）
2. 联合索引的列数不要超过 5 个
3. 避免创建冗余索引（比如已经有了idx(a,b)，就不要再创建idx(a)了）
4. 定期删除不用的索引，释放磁盘空间
5. 不要给每个列都加索引，索引不是越多越好

9. 总结

索引是 MySQL 性能优化中最重要的手段，但它不是万能的。我们需要：

1. 理解索引的本质是 "用空间换时间"
2. 搞懂为什么 MySQL 选择 B + 树作为索引结构
3. 区分聚簇索引和非聚簇索引，理解回表的代价
4. 牢记组合索引的最左前缀原则
5. 避免索引失效的常见情况
6. 学会使用EXPLAIN分析执行计划
7. 掌握覆盖索引、索引下推等高级优化技巧

最后记住一句话：好的索引是设计出来的，不是调出来的。 在设计表结构的时候就应该考虑索引的使用，而不是等到性能出现问题了才去救火。