MySQL索引底层面试常问的问题

一、聚簇索引和非聚簇索在索引树的叶子结点上存储的数据有什么区别？

聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式，术语“聚簇”表示数据行和和相邻的键值紧凑的存储在一起，因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引。
聚簇索引的优点：数据访问更快，聚簇索引将索引和数据保存在同一个索引树中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
InnoDB中的聚簇索引：InnoDB默认对主键建立聚簇索引，如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB会以聚簇索引的形式来存储实际的数据，它是其他二级索引的基础。
非聚簇索引：又称为普通索引，它的索引树树的叶子节点存储的数据是主键列。因此对于非聚簇索引查询来说，它是无法通过一次扫描索引树然后定位行记录的。

举例：

- 有表  table（id，name，sex，flag）。
- id为主键(聚簇索引)，name是普通索引。

id	name	sex	flag
1	shenjian	m	A
3	zhangsan	m	A
5	lisi	m	A
9	wangwu	f	B

两种索引在索引树中的数据存储如下图：

1、id为聚簇索引，叶子结点存储行记录；
2、name为普通索引，叶子结点存储主键值，即id；

既然普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？

# 通常情况下，需要扫描两遍索引树。
-select *from where name=‘lisi’;

图中的扫描过程，需要扫描两遍索引树：

• 1、先通过普通索引定位到主键值id=5；
• 2、再通过聚簇索引定位到行记录；

这就是所谓的回表查询，先定位到行记录，然后扫描聚簇索引的索引树。它的性能较扫描一遍索引树效率更低。

二、覆盖索引

覆盖索引：一个索引中包含所有需要查询字段的值。

MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

如何实现覆盖索引？

常见的方法是：将被查询的字段，建立到联合索引里去。

使用覆盖索引的好处？

无需回表，少了一个索引树的扫描。

三、索引失效的底层原理

只讨论最左前缀法则的失效问题，因为其他的我也不知道呀。

联合索引排序的原理

先对第一个字段进行排序，在第一个字段相同的情况下对第二个字段进行排序，然后在第二个字段相同的情况下才对第三个字段排序…

大多数情况下两个字段的值怎么会相同呢？所以很容易发生索引失效的问题

范围查找右边的索引会失效

expalin select *from test_user where a>1 and b=1;

我们可以找到a>1的所有的节点，但是此时的b索引是无序的，二分查找没办法继续下去了。