MySQL 面试题：索引

什么是索引？

• 索引是帮助 MySQL 高效获取数据的数据结构。
• 索引是在存储引擎中实现的，每种存储引擎的索引不一定完全相同。

索引设计原则？

什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广（列值区分度高）
（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

索引优点

• 索引加快数据库的检索效率，降低数据库 IO 的成本。
• 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
• 可以加速表与表之间的连接。

索引缺点

• 创建索引和维护索引需要耗费时间，随着数据量增加，时间会增加。
• 索引要占磁盘空间。
• 会降低更新表的速度。

索引常见的模型有哪些，各自的适用场景

• 哈希表是一种以键 - 值（key-value）存储数据的结构。哈希表适合等值查询的场景，因为数据无序，区间查询很慢。
• 有序数组在等值查询和范围查询场景中的性能就都非常优秀，而有序数组在等值查询和范围查询场景中的性能就都非常优秀。有序数组索引只适用于静态存储引擎，比如历史数据。
• B+ 树，读写性能优异。

什么是聚簇索引和非聚簇索引（二级索引）？

聚簇索引

聚簇索引并不是一种单独索引类型，而是一种数据存储方式，即索引即数据，数据即索引。Innodb 索引的文件为 ibd。

特点：

1. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
   • 页内的记录是按照主键的大小顺序排成一个单向链表。
   • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
   • 存放目录项记录的页分为不同的层次，在同-层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
2. B+树的叶子节点存储的是完整的用户记录。
   所谓完整的用户记录，就是指这个记录中存储了所有列的值(包括隐藏列)。

我们把具有这两种特性的 B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点这种聚簇索引并不需要我们在 MySQL 语句中显式的使用 INDEX 语句去创建，InnoDB 存储引擎会自动的为我们创建聚簇索引。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连,数据库不用从多个数据块中提取数据，所以节省了大量的 io 操作。

缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表,我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制:

• 对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引。
• 由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 如果没有定义主键，Innodb 会选择非空的唯一索引代替。如果没有这样的索引，Innodb 会隐式的定义一个主键来作为聚簇索引。
• 为了充分利用聚簇索引|的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id,比如 UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

非聚簇索引

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为 B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?肯定不能是从头到尾沿着链表依次遍历记录一遍。
答案:我们可以多建几棵 B+树，不同的 B+树中的数据采用不同的排序规则。比方说我们用 c2 列的大小作为数据页、页中记录的排序规则，再建一棵 B+树,效果如下图所示:

这个 B+树与上边介绍的聚簇索引有几处不同:

使用记录 c2 列的大小进行记录和页的排序,这包括三个方面的含义:

• 页内的记录是按照 c2 列的大小顺序排成一个单向链表。
• 各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一 个双向链表。
• 存放目录项记录的页分为不同的层次，在同一-层次中的页也是根据页中目录项记录的 c2 列大小顺序排成一个双向链表。

B+树的叶子节点存储的并不是完整的用户记录,而只是 c2 列+主键这两个列的值。

目录项记录中不再是主键+页号的搭配，而变成了 c2 列+页号的搭配。

小结:聚簇索引与非聚簇索引|的原理不同，在使用上也有一些区别:

1. 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不
   会影响数据表的物理存储顺序。
2. -个表只能有一个聚簇索引，因为只能有一-种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引
   目录提供数据检索。
3. .使用聚簇索引|的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索
   引低。

Hash 结构效率高，那为什么索引结构要设计成树形？

• 原因 1: Hash 索引仅能满足(=) (<>) 和 IN 查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为 O(n);而树型的“有序”特性,依然能够保持 O(log2N)的高效率。

• 原因 2: Hash 索引还有一个缺陷，数据的存储是没有顺序的，在 ORDER BY 的情况下，使用 Hash 索引还需要对数据重新排序。

• 原因 3:对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的, 无法对单独的一一个键或者几个索引键进行查询。

• 原因 4:对于等值查询来说,通常 Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较,找到查询的关键字，非常耗时。所以，Hash 索弓|通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

B+ 树索引的结构图

• 每个数据页之间是双向链表，每个数据页内部的记录会按照住建值从小到大的顺序组成一个单向链表。

• 根页面位置万年不动

• 内节点中目录项记录的唯一性，主键的目录项记录的内容是索引列 + 页号的搭配，二级索引是索引列 + 主键列 + 页号。

• 一个页面至少存储 2 个条记录。

B+ 树索引数据页内部的结构图

• Infimum 记录和 Supremum 记录没有主键值，但是规定他们是一个页面中最小和最大的记录。

• Infimum 记录所在分组只能有 1 条记录，Supremum 记录所在的分组拥有的记录条数只能在 1-8 之间，其他剩余的分组记录范围在 4-8 条之间。

• 初始情况下，一个数据页中只有 Infimum 记录和 Supremum 记录。页目录也只有两个槽位，分别代表 Infimum 和 Supremum 记录在页面中的地址偏移量。

• 查找页内数据时，通过而分法确定该记录所在分组对应的槽位，然后找到该槽位所在分组中逐渐值最小的那条记录，通过记录的 next_record 属性遍历该槽所在组中的各个记录。

为什么 InnoDB 引擎使用 B+ 树，而不使用二叉树作为存储结构？

1. IO 次数与索引树的高度是相关的。二叉树随着数据的增加，树高会增加，IO 次数会变多。
2. B+树的高度一般为 2-4 层，所以查找记录时最多只需要 2-4 次 IO。叶子结点存储数据，叶子节点用指针连接，提高区间访问的性能。

什么是回表？怎样减少回表？

• 先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据。
• 实现覆盖索引，只需要在一棵索引树上就能获取 SQL 所需的所有列数据（将被查询的字段，建立到联合索引里去）

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

• 在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。查询时尽可能利用这一原则，提高查询效率。

联合索引的技巧？

1. 覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少 IO 磁盘读写读取正行数据
2. 最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
3. 联合索引：根据创建联合索引的顺序，以最左原则进行 where 检索，比如（age，name）以 age=1 或 age= 1 and name=‘张三’可以使用索引，单以 name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
4. 索引下推：like 'hello%’and age >10 检索，MySQL5.6 版本之前，会对匹配的数据进行回表查询。5.6 版本后，会先过滤掉 age<10 的数据（对索引中不满足数据进行剔除），再进行回表查询，减少回表率，提升检索速度。

谈谈你对索引设计的理解？

给表创建索引时，应该创建哪些索引，每个索引应该包含哪些字段，字段的顺序怎么排列，这个问题没有标准答案，需要根据具体的业务来做权衡。不过有些思路还是可供参考的：

1. 既然是一个权衡问题，没有办法保证所有的查询都高效，那就要优先保证高频的查询高效，较低频次的查询也尽可能的使用到尽可能长的最左前缀索引。可以借助 pt-query-digest 来采样统计业务查询语句的访问频度，可能需要迭代几次才能确定联合索引的最终字段及其排序。
2. 业务是在演进的，所以索引也是要随着业务演进的，并不是索引建好了就万事大吉了，业务发生变化时，我们需要重新审视当初建的索引是不是还依然高效，依然能满足业务需求。
3. 业内流传的有一些 mysql 军规，其实这些并不是真正的军规，只是典型场景下的最佳实践。真正的军规其实就一条：高效的效满足业务需求