MySQL索引相关面试题

什么是索引？

索引就是帮我们快速查找的一种数据结构。

形象的说就是索引就是数据库的目录。

索引位于存储引擎中。

索引的分类？⭐⭐⭐⭐

• 按【数据结构】分类：B+tree索引、Hash索引、Full-text索引。
• 按【物理存储】分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按【字段特性】分类：主键索引、唯一索引、普通索引、前缀索引。
• 按【字段个数】分类：单列索引、联合索引。

聚簇索引和二级索引？⭐⭐⭐⭐

• 聚簇索引的B+Tree的叶子节点存储的是实际数据，完整的记录。
• 二级索引的叶子节点存储的是主键值，不是实际数据。

一般使用二级索引查询数据是要进行回表的，但是如果说需要查询的值就是索引本身，就不用回表了，这就是覆盖索引。

为什么MySQL选用B+tree作为索引的数据结构？⭐⭐⭐⭐

• B+tree只在叶子节存储数据，B+tree的单个节点的数据量更小。相同磁盘I/O次数下，能查询更多的节点。
• 对于有N个叶子节点的B+tree，搜索复杂度是O(logdN)，d是B+tree的阶数。
• B+tree的高度最低，和磁盘的I/O次数更少。
• B+tree支持范围查询，而Hash不支持。

什么是前缀索引？⭐⭐

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

什么是联合索引？⭐⭐⭐⭐

将多个字段组合成一个索引。

联合索引遵循最左匹配原则，B+tree的节点存储的是联合索引的字段。先按照左边第一个字段进行比较，如果相同，再按照左边第二个字段进行比较，以此类推。

如果不遵循最左匹配原则，索引就会失效。

比如说索引（a，b，c）：

• where a = 1
• where a = 1 and b = 2 and c = 3
• where a = 1 and b = 2

注意，因为有查询优化器，a字段在where子句的顺序不重要。

上面的索引都会生效。

但是：

• where b = 2
• where c = 3
• where b = 2 and c = 3

就会索引失效。因为是先按a排序，在a相同的情况下，按b排序。在b相同的情况下，再按c排序。所以b和c是全局无序的，局部相对有序的。

联合索引范围查询？⭐⭐⭐⭐

并不是查询过程使用到了联合索引，就代表索引中的所有字段都用到了联合索引。

比如：select * from table where a > 1 and b = 2，联合索引（a，b），哪个用到了索引？

由于联合索引按照a字段排序，a可以生效。

但是在a>1的范围下，b字段是无序的，所以b索引会失效。

比如：select * from table where a >=1 and b = 2， 联合索引（a，b），哪个用到了索引？

a肯定是用到了索引，当a=1的情况下，b也可以使用索引。

所以，a和b都可以用到索引。

联合索引区分度？

在创建联合索引的时候，把区分度越大的字段放在前面，越可能被更多的sql用到。

比如，性别这种字段，区分度很小，适合放在后面，而uuid这种，区分度很大，适合放在前面。

什么时候需要/不需要创建索引？

需要：

• 字段有唯一限制的：比如商品编码。
• 经常用于WHERE条件的。
• 经常用于GROUP BY和ORDER BY的字段。

不需要：

• 表数据量太少。
• 经常更新的字段。
• 大量重复的字段，比如性别。

索引优化的方法？⭐⭐⭐

• 覆盖索引优化：使用SQL中的query的所有字段，在B+Tree的叶子节点上都能找到的那些索引。从二级索引中查询得到记录，而不需要通过聚簇索引查询，避免回表。
• 主键索引最好是自增的：每次插入的新数据会按顺序添加到索引节点的位置，不需要移动已有的数据。当页写满的时候，会新开一个页面，每次插入新记录，都是追加操作，不需要移动数据，效率更高。
• 索引字段都设置成NOT NULL：NULL没有意义，还会占物理空间。
• 防止索引失效。

总结

• 为什么MySQL选用B+Tree作为索引的数据结构？
  • 存储相同的数据量的情况下，B+Tree的高度更低，磁盘I/O更少。
  • B+Tree叶子节点采用双向链表，适合范围查询。
• 什么时候索引会失效？
  • 不遵循最左匹配原则。
  • 对索引列做了计算、函数、类型转换等。
  • OR条件前面是索引，而后面不是索引。