简述--------Mysql索引机制到底什么？（面试必备）

最新推荐文章于 2022-09-27 13:03:18 发布

换名换一年

最新推荐文章于 2022-09-27 13:03:18 发布

阅读量471

点赞数

分类专栏： MySQL 文章标签： mysql 索引

本文链接：https://blog.csdn.net/qq_43590771/article/details/108371513

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

参考：https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_10409517059295814828%22%7D&n_type=1&p_from=4

1.什么是索引？

在关系数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构，它是某个表中的一列或者若干列值的集合和相应的指向表中物理标识这些值得数据页得逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码迅速找到所需的内容。
当表中有大量记录时，若要对表进行查询：
第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的 ROWID（相当于页码）快速找到表中对应的记录。

2.mysql5.5之后使用的数据结构：B+Tree

B+Tree 可以对 <，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的 LIKE 使用索引。（MySQL 5.5 后）

3.索引的优缺点

优点
- 索引大大减小了服务器需要扫描的数据量。索引可以帮助服务器避免排序和临时表，索引可以将随机i/o变成顺序i/o
缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重，但如果你在一个大表上创建了多种组合索引，且伴随大量数据量插入，索引文件大小也会快速膨胀。如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。对于非常小的表，大部分情况下简单的全表扫描更高效。
使用场景
- 我们应该只为最经常查询的和最经常排序的数据列建立索引（mysql中同一个数据表里的索引总数限制为16个）

4.mysql索引主要使用的数据结构的演化

二叉排序树 -> 二叉平衡树 -> B-Tree（B树） -> B+Tree(B+树)

5.B+树的前世今生

二叉排序树
- 理解 B+树之前，简单说一下二叉排序树，对于一个节点，它的左子树的孩子节点值都要小于它本身，它的右子树的孩子节点值都要大于它本身。如果所有节点都满足这个条件，那么它就是二叉排序树。（此处可以串一下二分查找的知识点）
- 上图是一颗二叉排序树，你可以尝试利用它的特点，体验查找 9 的过程：
- 9 比 10 小，去它的左子树（节点 3）查找。9 比 3 大，去节点 3 的右子树（节点 4）查找。9 比 4 大，去节点 4 的右子树（节点 9）查找。节点 9 与 9 相等，查找成功。
AVL树（自平衡二叉查找树）
- 高度要比二叉排序树要小
B树（Balanced Tree）多路平衡查找树，多叉的
- B树是一种多路自平衡搜索树，他类似普通的二叉树，但是B树允许每个节点有更多的子节点
- B树的特点：
  - 所有键值分布在整个树中。任何关键字出现且只出现在一个节点中。搜索有可能在非叶子节点结束。在关键字全集内做一次查找，性能逼近二分查找算法。
  - 为了提升效率，要尽量减少磁盘 I/O 的次数。实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。
  - 磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这样做的理论依据是计算机科学中注明的局部性原理
  - 由于磁盘顺序读取的效率很高（不需要寻址时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。预读的长度一般为页（page）的整倍数。MySQL（默认使用 InnoDB 引擎），将记录按照页的方式进行管理，每页大小默认为 16K（可以修改）。
  - B-Tree 借助计算机磁盘预读机制：每次新建节点的时候，都是申请一个页的空间，所以每查找一个节点只需要一次 I/O；因为实际应用当中，节点深度会很少，所以查找效率很高。
B+树（是B树的变体，也是一种多路搜索树）
- 从图中也可以看到，B+树与 B 树的不同在于：
- 所有关键字存储在叶子节点，非叶子节点不存储真正的 data，从而可以快速定位到叶子结点。为所有叶子节点增加了一个链指针，意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。
- 因此，B+Tree 可以对 <，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的 LIKE 使用索引。

在这里插入图片描述

B+树的优点
- 比较的次数均衡，减少了i/o次数，提高了查找速度，查找也更加稳定
- B+树的磁盘读写代价更低。B+树的查询效率更加稳定。
为什么Mysql索引选择了B+树而不是B树呢？
- 原因：
  - B+树更加适合外部存储（一般指磁盘存储），由于内节点（非叶子节点）不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更加精确。也就是说使用B+树单次磁盘i/o的信息量相比较B树更大，I/O小效率更高。
  - MySQL 是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以 B+树对索引列上的区间范围查询很友好。而 B 树每个节点的 key 和 data 在一起，无法进行区间查找。

ps：