大厂计划 | MySQL 高频面试题02：索引_mysql及索引高级面试-优快云博客

我们知道，在 MySQL 中，树的每一个节点的读取，都对应着一次磁盘 I/O 操作。那也就是说，树的高度越高，那每次查询数据时磁盘 I/O 操作的次数也就越多。
而红黑树是二叉查找树的变种，它虽然保持了平衡，但红黑树的一个节点最多也只能存储一个键值对；而 B/B+ 树是一种多叉的查找树，一个节点可存储多个键值对；所以在数据量相等的情况下，B/B+ 树是要远远比红黑树更矮胖的，那磁盘 I/O 操作的次数就会更少，花费的时间也就越少，性能也就越好。

为什么 InnoDB 索引实现选用 B+ 树而不是 B 树呢？

第一：B+ 树比 B 树更加矮胖，磁盘 I/O 的次数就会更少。

因为 B 树的所有节点都存储着键值和数据；
而 B+ 树只有在叶子节点才存储键值和数据，而非叶子节点只存储键值，所以在数据量相等的情况下，理论上 B+ 树是会比 B 树更加矮胖的；

而我们知道 MySQL 设计者将一个节点刚好设计为一个磁盘块的大小，所以树越矮胖，磁盘 I/O 的次数就会更少。

第二：B+ 树的连续范围查找要比 B 树快得多。

由于 B+ 树的叶子节点之间使用了双向指针进行连接，所以 B+ 树最底层的叶子节点们构成了双向有序链表。
假设现在要进行连续的范围查找，分别都在最差的情况下，B 树需要每次都从根节点一直遍历到叶子节点才结束，而 B+ 树从根节点到叶子节点仅仅只需要遍历一次，之后在叶子节点之间根据彼此的指针进行查找即可。所以在连续范围查找的情况下，B+ 树磁盘 I/O 的次数会比 B 树少得多，那查找的效率也就更高。

总结：基于树的层级以及实际业务场景使用的特性，所以 InnoDB 存储引擎的所以实现使用了 B+ 树作为底层结构。

索引的分类？

数据结构角度

树索引
哈希索引

逻辑角度

主键索引
普通索引

唯一索引
联合索引

全文索引
空间索引

存储角度

聚簇索引
辅助索引/二级索引/非聚簇索引

优化手段

覆盖索引
前缀索引（对文本建立前缀索引，如邮箱，节省空间）

谈谈你对哈希索引的理解？

哈希索引是以哈希表为数据结构实现的，以键值对的方式存储数据，key 存储索引列，value 存储行记录。

哈希索引能以 O(1) 的时间复杂度的进行精确查找，但进行范围查找时效率却非常低。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用得非常频繁时，就会在 B+ 树索引之上再创建一个哈希索引，这样就可以让 B+ 树索引拥用快速哈希查找的优点。

谈谈你对聚簇索引的理解？

我以 InnoDB 为例来说明。

每一张 InnoDB 表都有一个聚簇索引，并且这个聚簇索引是使用 B+ 树构建的，叶子节点存储的是整行记录的数据、键值以及指针；而非叶子节点存储的是键值和指针，不存储数据。

并且一般情况下，InnoDB 的主键索引就相当于是聚簇索引，当一张表没有创建主键索引时，InnoDB 会自动创建一个聚簇索引。

聚簇索引不同于辅助索引：在 InnoDB 中，辅助索引的叶子节点存储的是主键的值，无法获得整行的记录，必须在聚簇索引中根据辅助索引检索到的主键值进行回表才能够获取到整行的数据。

谈谈你对覆盖索引的理解？

覆盖索引它不是一种索引结构，而是一种常见的优化手段，它利用组合索引进行实现，可以有效的避免回表，减少磁盘 I/O，提高检索性能。

具体例子的话：假设我们要查找的字段有2个，但是却只根据其中1个字段进行检索，如果假设这两个字段组合成联合索引的话，那么最后在辅助索引的叶子节点中就存在我们想要查找的两个字段的键值，就不用再去回表了。

当然，不是说为了要每个查询都能用上覆盖索引，就要把语句中设计到的字段都建上联合索引，因为索引还是有维护代价的，这是一个需要权衡的决定。

你知道什么叫做回表嘛？

所谓回表其实就是当我们使用辅助索引，也就是非聚簇索引去查询我们想要的值时，非聚簇索引的叶子节点上的值值除了主键 Id 之外，没办法满足我们要查询的需求，这个时候就只得拿着这个非聚簇索引叶子节点上的主键 Id 值，回到聚簇索引中去查出整行的数据，那这整行的数据肯定就包含我们想要的所有值的信息，这个过程就叫回表。

要想避免回表，可以用上覆盖索引。

谈谈你对最左前缀匹配原则的理解？

首先，最左前缀匹配原则和联合索引是密切相关的：在使用组合索引查询时，MySQL 会一直从左向右进行匹配，直到遇到范围查询（>, <, between, like）就停止匹配。

比如创建了组合索引 A，B，C 的话，如果查询条件都给出了这三个索引的值，也就是进行组合索引查询，那么 B+ 树首先会根据索引 A 的值来确定搜索的方向，是往左还是往右；如果索引 A 对应的值存在很多相等的情况，那么 B+ 树就会根据索引 B 的值来确定搜索的方向；依次类推，这就是最左前缀匹配原则。

也就是说，这个组合索引 A，B，C 相当于创建了三个索引：（A）（A，B）和（A，B，C）。

同时需要注意的是：如果查询条件中没有给出索引 A 的值，那就查询不了了，B+ 树不知道第一步该从哪个节点开始查。

如何知道创建的索引有没有被使用到？

可以使用 explain 命令来查看当前 SQL 语句的执行计划。

其中：key 字段表示的是将会使用到的索引；rows 字段是预估的扫描行数。可以通过这些来判断当前 SQL 语句是否使用到了索引、索引是否有效或者 MySQL 优化器是否选错了索引。

什么情况下索引会失效？

参考：《MySQL 实战45讲：18 | 为什么这些 SQL 语句逻辑相同，性能却差异巨大》https://time.geekbang.org/column/article/74059

参考：优快云：详解 MySQL 什么情况下不走索引https://blog.youkuaiyun.com/lmp5023/article/details/106850252/

条件的索引字段进行函数操作与表达式运算

对条件的索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

mysql> select count(*) from t where month(t_modified) = 7; // month 函数操作
mysql> select count(*) from t where t_modified - 1 = 7; // month 表达式运算

隐式类型转换（如字符串转数字）

MySQL 中，如果条件的索引字段存储的是字符串，要和数字做比较的话，会将该字符串转为数字，进而索引失效。

mysql> select * from t where t_text=110717; // 字符串转数字

隐式字符编码转换

当两张表的字符集编码不同时，做连表查询时就用不上索引，因为连接过程中要求在被驱动表的索引字段上加函数操作，进而导致索引失效。

前导模糊查询或全模糊查询不走索引

select * from article where author like '%吴'; // 不走索引,进行全表扫描
select * from article where author like '%吴%'; // 不走索引,进行全表扫描

非前导模糊查询可以使用索引

select * from article where author like '吴%'; // 走索引

负向条件索引不会使用索引，建议用 in

负向条件有：!=、<>、not in、not exists、not like 等

select * from article where id != 1 and id != 2 // 全表扫描

如果知道 id 的所有取值范围，可以改为类似如下形式：

select * from article where id in (0, 3, 4) // 走索引

新版 MySQL 的 or 可以命中索引

select * from article where id = 1 or id = 2