MySQL索引的一些常见面试题大全(2022年)

吾爱主题阅读：206 2023-02-19 16:04:00 评论：0

为什么要建立索引？

当在非常大的表中进行查询，如果数据库进行全表遍历的话那么速度是会非常慢的，而我们的索引则可以建立一个b+树的结构，可以自上而下的去进行查询（有点像二分查找），可以在一定程度避免走全表查询，这样查询的速度是非常快的；

①一般情况下扫描索引的速度是远远大于扫描全表的速度的；

②索引是天然有序的，具备B+树的快速检索（类似二分查找）

③索引天然聚合（存储的数据是去重了的），在一些操作(分组，排序等)中不会再产生中间表；

哪些情况适合建立索引？

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

那么哪些情况下适合建索引？

1. 频繁作为where条件语句查询的字段

这是因为在频繁查询的字段列创建索引可以避免查询数据的时候走全表扫描，这样查询的速度就会大大增加；

2. 关联字段需要建立索引

关联的字段一般都是通过主键来进行两张表的关联，主键大部分情况下都是主键；如果关联的两个主键都没有索引，那么我们一般优先考虑在被驱动表中的字段建立索引，因为在外连接的查询中被驱动表是需要被多次重复扫描的，那么让它走索引查询是会快很多的，可以避免更多次数的全表扫描；

3. 排序字段可以建立索引

这是因为b+树结构的索引是天然有序的！

4.分组字段可以建立索引，因为分组的前提是排序

5.统计字段可以建立索引，例如count(),max()

这是因为索引是天然聚合的，就是存放在b+树的数据是已经去重的数据了，存储的数据还是比较紧凑的，那么通过B+树的双向指针可以更快的找到要统计的数据，而且在加了索引的列的统计的时候MySQL是不会产生中间表来专门去重了，可以减少不必要的性能开销；（在没有索引的列的统计，分组的SQL语句中，MySQL都是会创建临时表来存储数据的）

哪些情况下不适合建索引？

1.频繁更新的字段不适合建立索引 (因为数据比较大的表的索引的创建是非常耗时的，而且如果一个字段被频繁更新那么我们还需要频繁的维护这个树的结构，这个开销是非常大的)

2.参与列计算的列不适合建索引，因为计算后的列的值最后不一定是有序的，不有序那么就会导致索引会失效

3.表数据可以确定比较少的不需要建索引

4.数据重复且分布比较均匀的的字段不适合建索引，因为说不定你对这种索引字段的查询的速度还没有全表扫描快，例如性别，真假值；

5.where条件中用不到的字段不适合建立索引，因为索引是可以帮助我们在查询的时候大大的提高查询效率，但是在增加，删除操作确实异常消耗性能的，因为需要不断的维护B+树的结构（有序你就需要维护），你查询的时候都不需要使用到这个字段了，那还建立这个字段的索引列干啥？等着吃你系统的性能嘛？

为什么索引是使用B+树？（重点）

①因为b+树是把数据都存放在叶子节点中的（在innodb存储引擎中一个b+树的节点是一页(16k)），那么在固定大小的容量中 B+树的非叶子节点中就可以存放更多的索引列数据，也就意味着B+树的非叶子节点存储的数据的范围就会更大，那么树的层次就会更少，IO次数也就会更少；

②b+树的叶子节点维护了一个双向链表，它更有利于范围查询

③b+树中的叶子节点和非叶子节点的数据都是分开存储的，分别存放在叶子节点段和非叶子节点段，那么进行全表扫描的时候，就可以不用再扫描非叶子节点的数据了，并且这是一个顺序读取数据的过程（顺序读比随机读的速度要快很多很多），扫描的速度也会大大提高；

索引分为那几类？

从大类来分：分为聚簇索引和非聚簇索引；

从具体的种类来分有：

主键索引: 也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。

普通索引：就是普普通通的索引。

唯一索引：索引的值不能重复。

复合索引：在工作中用得比较频繁的一个索引；

当有多个查询条件时，我们推荐使用复合索引。比如：我们经常按照 A列 B列 C列进行查询时，通常的做法是建立一个由三个列共同组成的复合索引而不是对每一个列建立普通索引。

创建方式： 复合索引中的索引的顺序是非常重要的；

alert table test add idx_a1_a2_a3 table (a1,a2,a3)

使用复合索引可以极大的减少回表的带来的性能开销；（体现在复合索引可以进行更多的索引覆盖（因为你索引的个数明显更加多了呀），即便是回表也是携带更少的主键进行回表查询（与MySQL5.7后的索引下推有关））

复合索引是基于第一个索引的，比如你建立了一个（a,b,c）的复合索引，那么你不能跳过a索引直接去查询b索引，因为在建立（a,b,c）这个复合索引的时候，是会创建（a），（a,b），（a,b,c）这三个索引的，你会发现它们都是基于a索引的； (并不会单独的创建(a,c)这个索引)

hash索引：hash天然快（最快o(1),最慢o(n),树化(lon(n))），但是天然无序；

空间索引；

全文索引；

什么是聚簇索引？（重点）

聚簇索引就是将数据(一行一行的数据)跟索引结构放到一块，innodb存储引擎使用的就是聚簇索引；

注意点：

InnoDB使用的是聚簇索引（聚簇索引默认使用主键作为其索引），将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引具有唯一性，由于聚簇索引是将数据(一行一行的数据)跟索引结构放到一块，因此一个表仅有一个聚簇索引，其他辅助索引可能是只有几个列的数据和索引放在一起！

表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护（有序就一定需要维护）这个顺序；

聚簇索引中的索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个6个字节大小的row_id来作为主键，这个主键会作为聚簇索引中的索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

使用聚簇索引的优缺点？（知道）

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页（16k）中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(读取数据是按页读取的)，再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

注：我们知道一次io读写，可以获取到16K大小的资源，我们称之为读取到的数据区域为Page。而我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO操作中被读取到缓存中，所以在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行IO操作了。除非发生了页的分裂，即要查询的行数据不在上次IO操作的缓存里，才会触发新的IO操作。

3.因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

4.不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

为什么推荐使用自增主键作为索引？（知道）

主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂（比如之前的索引已经紧凑的排列在一起了，你此时需要在已经紧凑排列好的数据中插入数据就会导致前面已经排好序的索引出现松动和重构排序，但是使用自增id就不会出现这种情况了），导致索引树调整复杂度变大，消耗更多的时间和资源。但是使用自增主键就可以避免出现页分裂，因为自增主键后面的主键值是要比前面的大，那后来的数据直接放在后面就行；

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

什么叫回表？（重点）

如果一个查询是先走辅助索引（聚簇索引外的索引都叫辅助索引）的，那么通过这个辅助索引（innodb中的辅助索引的data存储的是主键）没有获取到我们想要的全部数据，那么MySQL就会拿着辅助索引查询出来的主键去聚簇索引中进行查询，这个过程就是叫回表；

什么叫索引覆盖？（重点）

如果一个查询是先走辅助索引的，那么通过这个辅助索引就直接获取到我们想要的全部数据了，不需要进行回表，这个过程就叫做索引覆盖；

什么是最左前缀原则？（重点）

大白话就是从最左的索引开始匹配，遇到范围查询就会让后面范围列后的索引失效；

mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

了解一下：

在MySQL5.6及5.6以前，最左匹配就是只有最左道索引会生效；（这个时候创建复合索引是为了避免回表）

在MySQL5.7最左匹配引入了索引下推，比如创建（a,b,c）的复合索引，在进行where a=xxx and b=xxx and c=xxx 的查询语句中，MySQL是先在索引中找到满足a条件的数据，然后再在a中取满足b的条件，然后再在b中取满足c的数据，最后再拿着非常少的主键到聚簇索引中查询最后的行数据；

比如：有1000W条数据的表，有如下sql:select from table where a =1 and b =2 and c =3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合b =2 and c = 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

MySQL索引失效的几种情况（重点）

①like查询以%开头，因为会导致查询出来的结果无序；

②类型转换，列计算也会可能会让索引失效，因为结果可能是无序的，也可能是有序的；

③在一些查询的语句中，MySQL认为走全表扫描比索引更加快也会导致索引失效；

④如果条件中有or并且or连接的字段中有列没有索引，那么即使其中有条件带索引也不会使用索引 (这是因为MySQL判断即便你开始走了索引查询，但是它发现查询中有Or ，也就是说or 后面的还是需要走全表扫描（因为or会导致后面的数据是无序的），所以MySQL还不如一开始就直接走全表扫描，这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】，这叫索引合并（取二者的并集）;

⑤复合索引不满足最左原则就不能使用全部索引