MySQL 索引速通指南：从原理到面试

序言

本篇文章是我学习 MySQL 索引时整理的简洁版笔记，主要面向初学和面试复习。内容不会过度深入源码，而是围绕索引的基本概念、底层结构、使用方式、失效场景和常见面试题展开。

一、初识索引

1.1 索引是什么

索引可以理解为数据库中的“目录”。

如果没有索引，MySQL 查询数据时可能需要从第一行开始一行一行扫描，也就是全表扫描。

如果有索引，MySQL 就可以根据索引快速定位到数据，减少扫描的数据量，提高查询效率。

简单来说：

索引的作用：提高查询效率 索引的代价：占用额外空间，降低写入、更新、删除效率

二、索引的优缺点

2.1 优点

  1. 提高查询效率。

  2. 减少数据库扫描的数据量。

  3. 可以加速排序和分组。

  4. 唯一索引可以保证数据唯一性。

2.2 缺点

  1. 索引会占用额外的磁盘空间。

  2. 插入、更新、删除数据时，需要维护索引，写入性能会下降。

  3. 索引不是越多越好，过多索引会增加维护成本。

三、索引的底层结构

3.1 为什么不用普通二叉树

普通二叉树在极端情况下可能退化成链表，查询效率会变低。

例如数据按顺序插入时，树可能变成这样：

1 \ 2 \ 3 \ 4

这种情况下查询效率接近全表扫描。

3.2 为什么不用红黑树

红黑树虽然可以保持平衡，但是它是二叉树。

数据量很大时，树的高度仍然可能比较高。树越高，查询时需要的磁盘 IO 次数就越多。

MySQL 数据通常存储在磁盘中，磁盘 IO 比内存操作慢很多，所以 MySQL 更希望树的高度尽可能低。

3.3 为什么不用 Hash

Hash 查询等值条件很快。

例如：

select * from user where id = 1;

Hash 可以快速定位。

但是 Hash 有几个明显问题：

  1. 不支持范围查询。
select * from user where id > 100;
  1. 不支持排序。
select * from user order by id;
  1. 不适合最左前缀匹配。
  2. 哈希冲突需要额外处理。

MySQL 中很多查询都不是单纯的等值查询，还包括范围查询、排序、分组、联合索引匹配等场景。

所以 Hash 不适合作为 InnoDB 的主要索引结构。

3.4 为什么不用 B 树

B 树是一种多路平衡搜索树。

相比二叉树，B 树一个节点可以存储多个 key，也可以有多个子节点，因此树的高度更低。

但是 B 树的节点中既存索引，也存数据。

这会导致一个页中能存放的索引数量变少，树的分叉数量也会减少，树的高度可能变高。

而 MySQL 更希望一次磁盘 IO 能读取尽可能多的索引项，从而减少磁盘 IO 次数。

所以 InnoDB 最终使用的是 B+ 树，而不是普通 B 树。

3.5 B+ 树的优势

MySQL InnoDB 默认使用 B+ 树作为索引结构。

B+ 树的特点：

  1. 非叶子节点只存储索引，不存储完整数据。

  2. 叶子节点存储完整数据或者主键值。

  3. 叶子节点之间通过指针连接，适合范围查询。

  4. 树的高度较低，可以减少磁盘 IO 次数。

四、InnoDB 中的索引

4.1 聚簇索引

聚簇索引就是数据和索引放在一起。

InnoDB 中，主键索引就是聚簇索引。

叶子节点保存的是完整的一行数据。

如果表中有主键，InnoDB 会使用主键作为聚簇索引。

如果没有主键，InnoDB 会选择一个唯一非空索引。

如果还没有，InnoDB 会自动生成一个隐藏字段作为聚簇索引。

4.2 二级索引

除了主键索引以外，其他索引都可以理解为二级索引，也叫辅助索引。

二级索引的叶子节点保存的不是完整数据，而是主键值。

所以通过二级索引查询数据时，可能需要先找到主键，再根据主键去聚簇索引中查完整数据。

这个过程叫做回表。

4.3 回表

回表就是通过二级索引找到主键后，再根据主键去聚簇索引中查询完整数据。

例如：

select * from user where name = '张三';

如果name字段有索引，MySQL 会先通过name索引找到对应的主键 ID，然后再根据 ID 查询完整数据。

如果查询的字段只在索引中就能拿到，就不需要回表。

4.4 覆盖索引

覆盖索引指的是：查询需要的字段都可以从索引中直接获取，不需要回表。

例如有一个联合索引：

(name, age)

查询：

select name, age from user where name = '张三';

这时查询字段name和age都在索引中，可以直接从索引中拿到，不需要回表。

五、索引的分类

5.1 主键索引

主键索引是一种特殊的唯一索引，不能为 null。

primary key(id)

5.2 唯一索引

唯一索引用来保证字段值不能重复。

unique key uk_phone(phone)

5.3 普通索引

普通索引只用于提高查询效率，不限制字段值是否重复。

index idx_name(name)

5.4 联合索引

联合索引是多个字段组成的索引。

index idx_name_age(name, age)

联合索引需要重点理解最左前缀原则。

六、联合索引和最左前缀原则

6.1 什么是最左前缀原则

联合索引会按照字段顺序建立索引。

例如：

index idx_name_age_address(name, age, address)

这个索引可以支持：

where name = ? where name = ? and age = ? where name = ? and age = ? and address = ?

但是不能很好支持：

where age = ? where address = ? where age = ? and address = ?

因为没有从最左边的name字段开始使用。

6.2 为什么要遵守最左前缀

联合索引底层会先按照第一个字段排序。

第一个字段相同，再按照第二个字段排序。

第二个字段相同，再按照第三个字段排序。

所以如果跳过最左边的字段，后面的字段就无法直接利用索引的有序性。

七、索引失效场景

7.1 对索引列使用函数

select * from user where substring(phone, 1, 3) = '138';

可能导致索引失效。

7.2 对索引列进行计算

select * from user where age + 1 = 18;

可能导致索引失效。

7.3 字符串不加引号

select * from user where phone = 13800138000;

如果phone是字符串类型，不加引号可能发生隐式类型转换，导致索引失效。

7.4 like 以 % 开头

select * from user where name like '%三';

前面有%时，索引可能失效。

如果是：

select * from user where name like '张%';

一般可以使用索引。

7.5 or 使用不当

如果or前后的字段不是都有索引，可能导致索引失效。

7.6 违反最左前缀原则

联合索引没有从最左字段开始使用，也可能导致索引失效。

八、如何查看索引是否生效

可以使用EXPLAIN查看 SQL 执行计划。

例如：

explain select * from user where name = '张三';

重点看几个字段：

  1. type：访问类型，常见有 all、index、range、ref、const 等。

  2. possible_keys：可能使用的索引。

  3. key：实际使用的索引。

  4. rows：预估扫描的行数。

  5. Extra：额外信息，比如是否使用覆盖索引、是否需要临时表、是否文件排序。

一般来说，type越好、rows越少，SQL 性能越好。

九、索引设计原则

9.1 给查询频繁的字段加索引

经常出现在where、order by、group by后面的字段，可以考虑加索引。

9.2 区分度高的字段适合加索引

比如手机号、用户 ID 这种区分度高的字段适合加索引。

性别这种字段区分度很低，一般不适合单独加索引。

9.3 尽量使用联合索引

多个条件经常一起查询时，可以建立联合索引。

9.4 避免索引过多

索引不是越多越好。

索引越多，写入、更新、删除时维护成本越高。

9.5 尽量使用覆盖索引

如果查询字段都能从索引中拿到，就可以避免回表，提高查询效率。

十、面试高频问题

10.1 MySQL 索引是什么？

索引可以理解为数据库中的目录，用来提高查询效率。没有索引时，MySQL 可能需要全表扫描；有索引时，可以根据索引快速定位数据。

10.2 索引为什么能提高查询效率？

因为索引底层使用了更适合查找的数据结构，比如 B+ 树，可以减少扫描的数据量和磁盘 IO 次数。

10.3 InnoDB 为什么使用 B+ 树？

因为 B+ 树树高低，磁盘 IO 次数少，并且叶子节点之间有链表连接，适合范围查询。

10.4 什么是聚簇索引？

InnoDB 中主键索引就是聚簇索引。聚簇索引的叶子节点保存完整的一行数据。

10.5 什么是回表？

通过二级索引查询时，先找到主键值，再根据主键去聚簇索引中查询完整数据，这个过程叫回表。

10.6 什么是覆盖索引？

查询需要的字段都在索引中，不需要再回表查询完整数据，这就是覆盖索引。

10.7 什么是最左前缀原则？

联合索引需要从最左边的字段开始使用。如果跳过最左字段，后面的字段可能无法使用索引。

10.8 索引一定会生效吗？

不一定。比如对索引字段使用函数、计算、隐式类型转换、like '%xxx'、违反最左前缀原则等，都可能导致索引失效。

10.9 索引是不是越多越好？

不是。索引会占用空间，并且插入、更新、删除时需要维护索引。索引过多反而会影响写入性能。

10.10 如何判断 SQL 有没有走索引？

可以使用EXPLAIN查看执行计划，重点看key字段是否实际使用了索引，以及type、rows、Extra等字段。