MySQL面试题 —— 索引篇_mysql索引面试

一、索引的分类

可以按照四个角度来分类索引

1. 按数据结构：B+tree索引、Hash索引、Full-text索引

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

2. 为什么要使用B+树作为索引

• 树的高度低：B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，相比存储即存索引又存记录的 B 树，B+树一个数据页中可以存放更多的索引，因此 B+ 树高度更低，查询底层节点的磁盘 I/O次数会更少。

• 范围查询：B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。并且通过使逻辑相邻的叶子节点尽量在物理存储也相邻，范围查询时对于IO更加友好。

• 维护代价小：B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除时不容易像B树那样频繁的需要调整树的结构，效率更高。

3. 按物理存储：聚簇索引（主键索引）、辅助索引（二级索引）

聚簇索引（主键索引）：根据数据的主键创建的索引，B+Tree非叶节点中存储主键作为索引，在叶节点中按主键顺序存储数据，并且叶节点间通过链表连接。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为聚簇索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值且值唯一的列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

辅助索引（二级索引）：辅助索引通常是对主键之外的字段建立的索引，其叶节点并不存储完整数据，而是存储主键值，通过辅助索引查到主键值后，再通过主键值到聚簇索引中查找对应的数据，这种需要二次查表的过程也称为回表。如果只需要叶子节点数据则不需要回表 直接返回，这称为索引覆盖。

4. 按字段特性：主键索引、唯一索引、普通索引、前缀索引

主键索引：主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值且值必须唯一。

PRIMARY KEY (index_column_1) USING BTREE
1

唯一索引：索引列的值必须唯一，但是允许有空值。一张表可以有多个唯一索引

UNIQUE KEY(index_column_1,index_column_2,...) 
1

普通索引：普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

INDEX(index_column_1,index_column_2,...) 
1

前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引

INDEX(column_name(length))
1

5. 按字段个数：单列索引、联合索引

单列索引：建立在单列上的索引称为单列索引，比如主键索引

联合索引：通过将多个字段组合成一个索引，该索引就被称为联合索引。联合索引是按字段的顺序优先级进行构建和排序，也就是说先按第一个字段进行排序，第一个字段相同时才按第二个字段排序。

由于联合索引中每个字段的顺序都是建立在前一字段的顺序之上，也就是只有前一个字段值相同的情况下，后一字段的值在索引表中才存在顺序性。因此只有查询条件中为联合索引中左边的部分在查询时才有可能利用到联合索引，例如联合索引（a,b,c），查询条件中只有（a）、（a,b）或（a,b,c）的部分索引才有效，这称为最左匹配原则。

并且根据最左匹配原则向右匹配的过程中遇到范围查询（>、<、between、like）后就会停止，因为显然一个字段一定范围内的数据其下一个字段并不是有序的，无法使用索引，比如查询条件a=3 and b=4 and c>5 and d=6如果建立（a，b，c，d）顺序的索引，d是用不到索引的。对于范围查询，只有等于范围边界的部分，后续字段才能用到联合索引。

索引区分度：建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。区分度就是某个字段 column 不同值的个数「除以」表的总行数。比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置。

二、优化索引的方法

1. 前缀索引

在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小，增加一个索引页中存储的索引值。

2. 覆盖索引

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

3. 主键索引最好是自增的

这样插入数据时直接在末尾追加，避免分裂页移动数据的消耗。

4. 索引列最好设置为 NOT NULL

第一：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化。

第二：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表。

三、索引失效的六种情况

1. 左/左右模糊匹配

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

2. 使用函数

当我们在查询条件中对索引列使用函数，就会导致索引失效。

3. 使用表达式

当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。

4. 隐式转换

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

5. 联合索引非最左匹配

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

6. where or子句

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。