《面渣逆袭》MySQL 篇 · 第 6/10 章。原版 PDF（下载 / 打印）

索引可以说是 MySQL 面试中的重中之重，一定要彻底拿下。

27.能简单说一下索引的分类吗？

从三个不同维度对索引分类：

例如从基本使用使用的角度来讲：

主键索引: InnoDB 主键是默认的索引，数据列不允许重复，不允许为 NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。

普通索引: 基本的索引类型，没有唯一性的限制，允许为 NULL 值。

组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

28.为什么使用索引会加快查询？

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL 需要将表的数据从头到尾遍历一遍。

在我们添加完索引之后，MySQL 一般通过 BTREE 算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，在比较小的索引数据里查找，然后映射到对应的数据，能大幅提升查找的效率。

和我们通过书的目录，去查找对应的内容，一样的道理。

29.创建索引有哪些注意点？

索引虽然是 sql 性能优化的利器，但是索引的维护也是需要成本的，所以创建索引，也要注意：

1. 索引应该建在查询应用频繁的字段

在用于 where 判断、 order 排序和 join 的( on) 字段上创建索引。

2. 索引的个数应该适量

索引需要占用空间；更新时候也需要维护。

3. 区分度低的字段，例如性别，不要建索引。

离散度太低的字段，扫描的行数降低的有限。

4. 频繁更新的值，不要作为主键或者索引

维护索引文件需要成本；还会导致页分裂，IO次数增多。

5. 组合索引把散列性高( 区分度高) 的值放在前面

为了满足最左前缀匹配原则

6. 创建组合索引，而不是修改单列索引。

组合索引代替多个单列索引（对于单列索引，MySQL 基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）

7. 过长的字段，使用前缀索引。当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也

会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

8. 不建议用无序的值( 例如身份证、UUID) 作为索引

当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

30.索引哪些情况下会失效呢？

查询条件包含 or，可能导致索引失效如果字段类型是字符串，where 时一定用引号括起来，否则会因为隐式类型转换，索引失效

like 通配符可能导致索引失效。

联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

在索引列上使用 mysql 的内置函数，索引失效。

对索引列运算（如，+、-、*、/），索引失效。

索引字段上使用（！= 或者 <>，notin）时，可能会导致索引失效。

索引字段上使用 is null， is not null，可能导致索引失效。

左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

MySQL 优化器估计使用全表扫描要比使用索引快, 则不使用索引。

31.索引不适合哪些场景呢？

数据量比较少的表不适合加索引更新比较频繁的字段也不适合加索引离散低的字段不适合加索引（如性别）

32.索引是不是建的越多越好呢？

当然不是。

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率​。比如每次对表进行增删改操作，MySQL 不仅要保存数据，还有保存或者更新对应的索引文件。

33.MySQL 索引用的什么数据结构了解吗？

MySQL 的默认存储引擎是 InnoDB，它采用的是 B+ 树结构的索引。

B+ 树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

在这张图里，有两个重点：

最外面的方块，的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（粉色所示）和指针（黄色/ 灰色所示），如根节点磁盘包含数据项 17 和 35，包含指针 P1、P 2、P 3，P 1 表示小于 17 的磁盘块，P 2 表示在 17 和 35 之间的磁盘块，P 3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、4、5 … …、6 5。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、3 5 并不真实存在于数据表中。

叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表，可以进行范围查询。

34.那一棵 B+树能存储多少条数据呢？

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共14 字节。非叶子节点( 一页) 可以存储 16384/14=1170 个这样的 单元( 键值+ 指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170117016=21902400​。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

35.为什么要用 B+ 树，而不用普通二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数。

为什么不用普通二叉树？​

普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不用平衡二叉树呢？​

读取数据的时候，是从磁盘读到内存。如果树这种数据结构作为索引，那每查找一次数据就需要从磁盘中读取一个节点，也就是一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是

B+ 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快。

36.为什么用 B+ 树而不用 B 树呢？

B+ 相比较 B 树，有这些优势：

它是 BTree 的变种，BTree 能解决的问题，它都能解决。

BTree 解决的两大问题：每个节点存储更多关键字；路数更多扫库、扫表能力更强如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据。

B+Tree 的磁盘读写能力相对于 BTree 来说更强，IO次数更少根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，

IO 次数更少。

排序能力更强因为叶子节点上有下一个数据区的指针，数据形成了链表。

效率更加稳定

B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。

37.Hash 索引和 B+ 树索引区别是什么？

B+ 树可以进行范围查询，Hash 索引不能。

B+ 树支持联合索引的最左侧原则，Hash 索引不支持。

B+ 树支持 orderby 排序，Hash 索引不支持。

Hash 索引在等值查询上比 B+ 树效率更高。

B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，

Hash 索引根本无法进行模糊查询。

38.聚簇索引与非聚簇索引的区别？

首先理解聚簇索引不是一种新的索引，而是而是一种数据存储方式​。聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎— — MyISAM 采用的是非聚簇索引，InnoDB 采用的是聚簇索引。

可以这么说：

索引的数据结构是树，聚簇索引的索引和数据存储在一棵树上，树的叶子节点就是数据，非聚簇索引索引和数据不在一棵树上。

一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。

聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

39.回表了解吗？

在 InnoDB 存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如:select*fromuserwherename= ‘张三’ ;

40.覆盖索引了解吗？

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

比如，​selectnamefromuserwherename= ‘张三’ ;

41.什么是最左前缀原则/最左匹配原则？

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。

最左匹配原则​：在 InnoDB 的联合索引中，查询的时候只有匹配了前一个/ 左边的值之后，才能匹配下一个。

根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a 1）、( a1,a2) 和

(a1,a2,a3) 三个索引。

为什么不从最左开始查，就无法匹配呢？

比如有一个 user 表，我们给 name 和 age 建立了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，

age 在右边)。

从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候我们使用wherename= ‘张三‘andage= ‘20 ‘​去查询数据的时候， B+Tree 会优先比较

name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较 age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。

42.什么是索引下推优化？

索引条件下推优化（Index Condition Pushdown (ICP)）是 MySQL5.6 添加的，用于优化数据查

询。

不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给 MySQLServer，MySQL

Server 进行过滤条件的判断。

当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQLServer 将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合 MySQLServer 传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

例如一张表，建了一个联合索引（name,age），查询语句：​select*fromt_userwherenamelike

' 张% 'andage=10;​，由于name使用了范围查询，根据最左匹配原则：

不使用 ICP，引擎层查找到namelike' 张% '的数据，再由 Server 层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age​。

但是，使用了索引下推优化，把 where 的条件放到了引擎层执行，直接根据namelike' 张% 'and

age=10的条件进行过滤，减少了回表的次数。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。