MySQl 面试重点_2.常见的索引面试题总结
文章目录
一, 索引定义 (排序 + 定位)
索引是对数据库表的一列或者多列的值进行排序的一种数据结构，使用索引可以高效快速的定位到数据表中的特定信息。
- 常见的索引结构有: Hash, B树, B+树;
索引的两大功能: 排序和查找;
索引会对where后面的过滤查找, order by的排序有影响;
插一嘴: 本文是笔者断断续续花了一周时间, 查阅了大量的帖子, 总结(拼凑)出来的, 后面还会持续丰富和完善内容, 最大的目的就是把这些系列既能够当八股文, 也能够用来作为知识的扫盲! 能给你们带来帮助也是极好的!
如果发现有错误的话, 请评论中不吝赐教! 感恩的心 ❤
二, 索引的优缺点
优点:
- 使用索引可以大大加快数据的检索速度(通过减少索引数据量), 这也是创建索引的主要原因;
- 通过创建唯一索引, 还可以保证数据库表中每一行数据的唯一性;
- 通过索引列对数据进行排序, 当进行分组(groupby)和排序(orderby)子句时, 可以显著的减少分组和排序时间
- 在实现数据的参考完整性方面, 可以加速表和表之间的连接;即对于有依赖关系的子表和父表联合查询时, 可提高查询速度;
缺点:
- 创建和维护索引需要耗费时间, 数据量增多, 耗费的时间也会增加;
- 虽然索引提高了查询速度, 却会降低更新表的速度; 因为在对表中数据进行增删改时, 索引也要动态的维护, 降低了SQL执行效率;
- 索引使用物理文件存储, 会耗费一定的磁盘空间;
三, 索引的底层数据结构
索引的出现是为了对索引列排序, 快速定位数据, 索引采用的底层数据结构很多,常见的主要是哈希表, B-树, B+树.
3.1 哈希表
哈希表是键值对的集合, 通过哈希算法和键(key), 可以快速的存取出值(value); 但是由于不用的键(key)通过同一个哈希算法, 可能会得到相同的值(value), 这种我们称作是哈希冲突;
- 通常解决哈希冲突的方法是链地址法, 即把同一个哈希地址的值用链表连接起来, 在查找当前地址的值时, 遍历链表去查找;
- 就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。
3.2 B树 & B+树
3.2.1 B树的特点
数据结构上的定义, 可以不看
B树单一节点拥有的最多子节点数量，称为B树的"阶”。一个m阶的B树，具有如下几个特征：
- 根结点至少有两个子节点；
- 每个中间节点都包含k-1个元素和k个子节点，其中 m/2 <= k <= m；
- 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m；
- 所有的叶子结点都位于同一层；
- 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个子节点所包含的元素的值域划分。
3.2.2 B+树的特点
数据结构上的定义, 可以不看
一个m阶的B+树（Balance+ Tree）的特征：
- 有k个子节点的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
- 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字按照自小而大的顺序链接起来。
- 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
3.3 B+树和哈希的区别
首先要知道Hash索引和B+树索引的底层实现原理：
hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
那么可以看出他们有以下的不同：
-
hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。 -
hash索引不支持使用索引进行排序，原理同上。 -
hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。 -
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。 -
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。
3.4 B+树和B树的区别
B+树非叶节点只存索引不存数据，所有的 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好时间复杂度为O(1)。
B树
从上图可以看出，key 为 50 的节点就在第一层，B-树只需要一次磁盘 IO 即可完成查找。所以说B-树的查询最好时间复杂度是 O(1)。
B+树
由于B+树所有的 data 域都在根节点，所以查询 key 为 50的节点必须从根节点索引到叶节点，时间复杂度固定为 O(log n)。
B+树叶节点两两相连成一个双向链表可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，只能对区间数据进行中序遍历
- B+树可以很好的利用局部性原理,若我们访问节点 key为 50，则 key 为 55、60、62 的节点将来也可能被访问，我们可以利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。
B+树更适合外部存储。非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
简洁版的总结(面向面试):
拓展文章: 什么是跳表, LSM树?
跳表：是一种链表加多层索引的结构，时间复杂度O(logn)，支持区间查找，而B+树是一种多叉树，可以让每个节点大小等于操作系统每次读取页的大小，从而使读取节点时只需要进行一次IO即可。而且同数量级的数据，跳表索引的高度会比 B+ 树的高，导致 IO 读取次数多，影响查询性能。
3.5 B+树和红黑树的区别
(待补充)
Q: 为什么 MongoDB 索引选择B-树，而 Mysql 索引选择B+树
B树的树内存储数据，因此查询单条数据的时候，B树的查询效率不固定，最好的情况是O(1)。我们可以认为在做单一数据查询的时候，使用B树平均性能更好。但是，由于B树中各节点之间没有指针相邻，因此B树不适合做一些数据遍历操作。
Mysql的底层实现为B+树，而Mongodb的底层实现为B树。他们两种数据结构针对的是不同的业务场景，Mysql是关系型的数据库，而Mongodb是非关系型的数据库。Mysql中数据遍历操作比较多，所以用B+树作为索引结构。而Mongodb是做单一查询比较多，数据遍历操作比较少，所以用B树作为索引结构。
- 参考文章:
- https://blog.csdn.net/wwh578867817/article/details/50493940
- https://leetcode-cn.com/circle/discuss/F7bKlM/
- https://blog.csdn.net/zhibo_lv/article/details/117807830
- https://blog.csdn.net/m0_57488641/article/details/116030189
- https://blog.csdn.net/mu_wind/article/details/110128016#t6
- https://ivanzz1001.github.io/records/post/data-structure/2018/06/16/ds-bplustree
- https://www.jianshu.com/p/ace3cd6526c4
四, 索引类型
4.1 MySQL索引都有哪些分类?
| 角度 | 分类 |
|---|---|
| 按数据结构分类 | B+树索引, Hash索引, Full-test索引 |
| 按物理存储分类 | 聚簇索引, 二级索引(辅助索引) |
| 按字段特性 | 主键索引, 普通索引, 前缀索引 |
| 按字段个数 | 单列索引, 联合索引(复合/组合索引) |
4.2 按数据结构分类(B+树, Hash索引, 全文索引)
MySQL索引按照数据结构索引分类可分为:
B+树索引, Hash索引, Full-text索引
再来复习一次:
为什么MySQL采用B+树而不是B树作为索引?答案在上面
4.3 按物理存储分类(聚簇索引, 非聚簇索引/辅助/二级索引))
本节因为为了对比InnoDb和MyISAM索引上的不同, 所以内容有点乱, 可先参见:此文
MySQL索引按照叶子结点存储的是否为完整表数据, 把索引分为: 聚簇索引, 非聚集索引/二级索引/辅助索引;
1. 聚簇索引
将数据存储与索引放到了一块，找到索引也就找到了数据; 聚簇索引的每个叶子结点存储了一行完整的表数据, 叶子节点之间按id列递增连接, 可以方便的进行顺序检索;
InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。
以MyISAM为存储引擎的表不存在聚簇索引。
MyISAM表中的主键索引和非主键索引的结构是一样的，索引的叶子节点不存储表数据，存放的是表数据的地址。所以，MyISAM表可以没有主键。
MyISAM表的数据和索引是分开存储的。MyISAM表的主键索引和非主键索引的区别仅在于主键索引的B+tree上的key必须符合主键的限制(唯一的不可重复的)，非主键索引B+tree上的key只要符合相应字段的特性就可以了。
2. 非聚簇索引
二级索引的叶子结点并不存储一行完整的表数据, 而是存储了聚簇索引所在列的值;
回表查询
由于二级索引的叶子节点不存储完整的表数据，索引当通过二级索引查询到聚簇索引列值后，还需要回到聚簇索引也就是表数据本身进一步获取数据。
回表查询 需要额外的 B+tree 搜索过程，必然增大查询耗时。
需要注意的是，通过二级索引查询时，回表不是必须的过程，当SELECT的所有字段在单个二级索引中都能够找到时，就不需要回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。
可以用Explain命令查看SQL语句的执行计划，执行计划的Extra字段中若出现Using index，表示查询触发了索引覆盖。
五, 主键为什么建议选择自增主键
- 从性能方面, 避免页分裂;
如果选用自增主键的话，每次新增数据时，都是以追加的形式进行存储，在本页索引写满之后, 只需申请一个新页继续写入即可, 不会产生页分裂问题。
如果说采用业务字段作为主键的话，新增数据不一定是顺序的，需要挪动数据, 页快满时还要去分裂页, 保持索引的有序性，造成写数据成本较高。
- 从存储方面, 节省空间;
除了性能方面有区别外，再来看看存储方面。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
- 表中如果使用自增主键的话，若使用bigint做主键，占8个字节；如果使用身份证号作为主键的话，占用20字节。
所以，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，页存储索引越多。
上述从性能和存储空间进行分析采用自增主键和采用业务字段作为主键的优缺点，也推断出采用自增主键是比较合理的方案。
六, 什么是最左前缀原则？什么是最左匹配原则
最左前缀原则:
顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
最左匹配原则:
MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；
- 再来几个栗子:
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的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式.
七, 联合索引是什么? 为什么需要注意联合索引中的顺序
MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段从左边顺序挨个使用,否则无法命中索引.这种顺序就叫做最左匹配原则, 从联合索引的最左边一直向右进行连续匹配, 在遇到范围查询就停止匹配.(>, <, between, like)
八, 什么是索引下推?
如果你在面试中，听到MySQL5.6”、“索引优化” 之类的词语，你就要立马get到，这个问的是“索引下推”。
索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是 MySQL 5.6 发布后针对扫描二级索引的一项优化改进。总的来说是通过把
索引过滤条件下推到存储引擎，ICP 适用于 MYISAM 和 INNODB.
解释:
九, 如何创建索引? 创建索引应该注意什么?
创建索引的方式
十, 如何查看MySQL 语句有没有用到索引
十一, 哪些情况下适合建立索引? (设计索引的原则)
十二, 什么情况下不走索引(索引失效)
- 参考链接:
- https://www.hollischuang.com/archives/6330
- https://segmentfault.com/a/1190000040177994#item-12
