漫步coding

2022年MySQL最新面试题 - MySQL索引

3/6/2022

# 0、概要

  • 1、索引有哪些使用场景（重点）
  • 2、索引的数据结构（b树，hash）
  • 3、创建索引的原则是什么?（重中之重）
  • 4、使用索引查询一定能提高查询的性能吗？为什么
  • 5、索引有哪些优缺点？
  • 6、讲一讲聚簇索引与非聚簇索引？
  • 7、百万级别或以上的数据如何删除
  • 8、什么是最左前缀原则？什么是最左匹配原则
  • 9、数据库为什么使用B+树而不是B树
  • 10、非聚簇索引一定会回表查询吗？
  • 11、有哪些情况, 索引会失效, 可以简单说说吗?

# 1、索引有哪些使用场景

出现概率: ★★★★★

1)、应该创建索引的场景

  • 主键应该创建主键索引。
  • 频繁作为查询条件的字段应该创建索引。
  • 查询中需要与其他表进行关联的字段应该创建索引。
  • 需要排序的字段应该创建索引。
  • 需要统计或分组的字段应该创建索引。
  • 优先考虑创建复合索引。

2)、不应创建索引的场景

  • 数据记录较少的表。
  • 经常需要增删改操作的字段。
  • 数据记录重复较多且分布平均的字段（如性别、状态等）。

索引的选择性是指索引列中不同值的数目与表中记录总数的比。
索引的选择性越接近于1，创建索引的价值就越高。反之就越低。

# 2、索引的数据结构（B+树，hash）

出现概率: ★★★★★

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，MySQL默认采用的B+Tree， 这里主要讲讲B+树的特点:

1.非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2.叶子节点包含所有索引字段
3.叶子节点用指针连接，提高区间访问的性能 （快速定位范围查询，例如查询大于20，第一次io从根节点查询三次定位到20，然后通过后面的指针查询大于20的数据，就不用再从根节点的重新再查询，提高性能，叶子节点开始结束节点也是用指针连接串起来的）

# 3、创建索引的原则是什么?

出现概率: ★★★★

1)、选择唯一性索引

2)、为经常需要排序、分组和联合操作的字段建立索引

3)、为常作为查询条件的字段建立索引

4)、限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5)、尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6)、尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7)、最左前缀匹配原则

8)、查询时使用计算，会导致索引失效

# 4、使用索引查询一定能提高查询的性能吗？为什么

出现概率: ★★★★

不是所有的查询使用查询都能提高性能， 比如下面几个案例

  • 像 like % xxx% 、不满足最左匹配原则的情况下并不能使用到建好的索引
  • MySQL 在可以使用多个索引的情况下，查询优化器会根据查询范围的数据量估算索引代价，最坏的是估算完毕后，发现这些索引的字段区分度不高，还不如扫全表，于是 Mysql 扫全表了
  • 如果索引的列比需要查询的列少，Mysql 会通过聚簇索引回表查询其他字段
  • 如果索引的字段很大，每个页能存的条目就很少，读取时 IO 会消耗更多，页 Buffer 轮替的更快

# 5、索引有哪些优缺点？

出现概率: ★★★

1)、索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2)、索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

# 6、讲一讲聚簇索引与非聚簇索引？

出现概率: ★★★★

在 InnoDB 里，索引B+Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）

对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。

通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可

注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

# 7、百万级别或以上的数据如何删除

出现概率: ★★★

方法一:

索引是单独的文件，增删改时，当存在索引，会消耗额外io。删除速度和索引数量成正比

  • 删除索引
  • 删除需要删除的数据
  • 重新建立索引

这个方法有一个很明显的缺点, 就是在正式环境这个表如果访问频率比较高的话， 删除索引后有大量的SQL查询会导致数据库IO和CPU特别高

方法二:

之前我在正式环境的做法是 将删除任务拆分为一次删除1w条， 然后把删除任务重新压入的异步任务队列里面。

# 8、什么是最左前缀原则？什么是最左匹配原则

出现概率: ★★★★

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例： 对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

# 9、数据库为什么使用B+树而不是B树

出现概率: ★★★

B树和B+树的区别主要有两点：

  • 在B树中，你可以将键和值存放在内部节点和叶子节点，但在B+树中，内部节点都是键，没有值。叶子节点同时存放键和值
  • B+树的叶子节点有一条链相连，而B+树的叶子节点各自独立。

使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

数据库的数据读取都是需要进行代价巨大的磁盘IO操作，因此，更快地缩小范围和更少的读取次数是数据库需要关注的重点。而B+树在这些点上比B树做的更好。这就是为什么数据库要选用B+树作为底层实现。

# 10、非聚簇索引一定会回表查询吗？

出现概率: ★★★

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在学生表的成绩上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

也欢迎关注我的公众号: 漫步coding, 回复: mysql免费获取最新Mysql面试题汇总(含答案)。 一起交流, 在coding的世界里漫步。

希望这篇文章可以帮助大家, 也希望大家都能找到找到的好工作。

存储引擎