mysql 索引的原理及数据结构

最新推荐文章于 2024-08-17 21:44:45 发布

blue$boy

最新推荐文章于 2024-08-17 21:44:45 发布

阅读量834

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_31851107/article/details/103483964

版权

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1. 初识索引

1）为什么要用索引？
提高数据库对数据的查询速度
一般来说如果表里的数据量较小，则没必要用索引；但是生产环境上常遇到数据量较大且查询操作较为复杂的场景，这个时候索引的作用就尤为重要了

2）什么是索引？
a）索引在mysql中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构；
b）当表中的数据量越大时，索引的效果就越发显得重要，对性能的影响也越发重要；
c）索引就相当于《新华字典》的目录：如果没有目录，需要从头开始逐个查找；但有了目录，就可直接定位到要查找数据的位置

3）使用索引需要注意什么？
a）索引固然重要，但不代表索引越多越好。若索引太多，应用程序的性能可能会受到影响；索引太少，又会影响查询性能。因此需要找一个平衡点
b）要学会从设计数据库开始就考虑索引的创建，而非事后诸葛
c）要学会正确使用索引，要给最合适的字段建立索引（在索引的使用一文中有对创建索引的依据做总结）
d）索引会影响数据写入的速度（每次写入，B+树都要重新平衡）
e）索引也会占用磁盘空间，不合理的索引可能会导致数据库所在磁盘占用过高或被耗尽，从而导致db异常

2. 索引原理及本质

1）索引的本质是什么？
a）是帮助数据库高效获取数据的排好序的数据结构
b）通过不断地缩小想要获取数据的范围来筛选到最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据（类似通过“二分法”来查找一个数）

2）索引存储在哪里？
索引是存在磁盘上的，具体会在数据库目录下的数据库文件，可参考：mysql 索引的使用与效果测试

3）磁盘的I/O操作
从数据库里查询数据，其实本质上就是从磁盘上查询数据，因此我们需要先了解一下磁盘IO的相关知识

磁盘读取数据靠的是机械运动，每次读取数据话费的时间可以分为 寻道时间、旋转延迟、传输时间 3个部分：
寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
旋转延迟就是我们常听说的转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒能转120次，旋转延迟就是1/120/2=4.17ms；
传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相较于前两个时间可以忽略不计。

那么综上，访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17=9ms左右，听起来很快，但是一台500 MIPS(Million Instructions Per Second)的机器每秒可以执行5亿条指令，时间换算，也就是一次IO的时间可以执行450万条指令，显然9ms是对时间的浪费。另外，当前数据库动辄十万百万乃至千万级的数据量，如果每次9毫秒，显然是不合理的。（比如一张未做索引的10000行的表，查询最后一行数据时则需要对表进行遍历，也就是说要进行10000次IO操作，查询肯定十分缓慢）

考虑到磁盘IO是非常昂贵的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页的数据时候，实际上才发生了i次IO，这个理论对索引的数据结构设计非常有帮助

**注：**虽然表数据是随机存在磁盘上的，即便是表中相邻的两个数据页不一定在磁盘上相邻；但是我们可以借助磁盘的页的设计，来对数据库的索引结构进行设计

3. mysql索引底层数据结构与算法

首先我们先明确，mysql索引底层用的数据结构是B+树(B+Tree)

3.1 各种数据结构介绍

1）二叉树

二叉树的特点
（1）若左子树不空，则左子树上所有结点的值均小于它的根(父)节点的值；
（2）若右子树不空，则右子树上所有结点的值均大于它的根(父)节点的值；
（3）叶子节点有0个子节点
（3）左、右子树也分别为二叉排序树(每个节点最多有两个子节点，除叶子节点)
（4）没有键值相等的节点（因此，插入的时候一定是叶子节点）。
（5）可以减少查询次数

二叉树示意图
在这里插入图片描述
数字1-7按照二叉查找树来合理排序的话，如下：

二叉树缺点
二叉树存在单边增长的可能性
比如要加索引的列的数据是逐渐增加的，如果用二叉树存储索引，就会出现单边增长的情况，这和不加索引相比没什么区别
如下，数字1-4依次插入，就成了单边增长的情况，当我们查找的时候仍然需要依次遍历所有数据，直到找到我们想要的数据为止
在这里插入图片描述

2）红黑树

上面我们已经知道了，二叉树是存在单边增长的，也就是说可能如果插入的数据线性增加的话，就会只有右边一条腿，同理，插入的数据如果线性减小的话，就会只有左边一条腿
那么我们有没有一种策略可以保持树的左右两边平衡而不出现这种一腿独长的情况呢(即降低树的高度，提高查询效率)？答案是有的，这便涉及到了我们的红黑树算法
红黑树也是一种二叉查找树，属于弱平衡二叉树

红黑树的特点
（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
（4）如果一个节点是红色的，则它的子节点必须是黑色的。
（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。[这里指到叶子节点的路径]

红黑树示意图
当1-7按照递增顺序依次插入时如下：
在这里插入图片描述
如上图，即红黑树插入结果，解决了二叉树单边增长的问题，一定程度上降低了树的高度

红黑树缺点
红黑树的时间复杂度是logN，随着数据量的增加，树的高度仍不能保持在较小范围，查找效率仍然比较低，因此对于mysql大数据量来说，也不太合适
那么我们有什么办法能降低树的高度，以此来提高数据库的查询效率呢？

3）B树

此处要补充一个知识点
查找某一个节点的某个元素是怎么查找的？
一次I/O会把一个节点上所有的索引值都load到内存中，然后再在内存中遍历查找（在内存中的查找操作时间基本可以忽略）；与在磁盘上查找相比，速率极高；因此也就大约是一次I/O的时间

从查找元素的机制我们有何启发？如何优化红黑树以提高其查询效率？
可以每个节点横向扩容，多存储几行数据的索引值，这样就能有效的降低树的高度，提高查询效率
如上改造之后的红黑树，即B树
B树本质上是一个多叉平衡树

B树的特点
（1）度(Degree)：节点度数据存储个数
（2）叶节点具有相同的深度
（3）叶节点的指针为空
（4）节点中的数据key(索引)从左到右递增排列

每个节点都是key-value的形式存储的，其中key即索引的值，而value即该索引对应的一行数据(具体是该行数据的地址指针还是除去索引信息之后的具体数据，和mysql的引擎有关，后续会讲)

B树示意图
数字1-7存储示意图
在这里插入图片描述
mysql B树数据存储示意图

4）B+树

B+树，本身也是在B树的基础上进行优化而来的

B+树和B树的区别：
（1）B+树非叶子节点不存储data，只存储key
（2）叶子节点不存储指针
（3）顺序访问指针，提高区间访问的性能

B+树示意图
插入数字1-7示意图：
在这里插入图片描述
会看到叶子节点有一些冗余key，是为了不再非叶子节点存储数据，这样一个节点就可以多存一些key(度大)，降低树高，增加查询效率

mysql B+树数据存储示意图
在这里插入图片描述

关于B+树的几个疑问解答

问1. B Tree 和 B+Tree 的区别？
1. B+Tree只有叶子节点存储数据/data，而非叶子节点只存key
2. B+Tree叶子节点有指针(B+Tree的优势)

问2. 我们都知道，树的高度越小，经过的I/O也少；那么为什么一个节点不能无限的横向扩容，以此来节省I/O？
cpu与内存进行数据交换，最小数据交换单位(页)，一页大小为4k；不到4kcpu也会取一页；
而cpu一次也就取几页(具体需要百度)，不会取太多数据，否则一次I/O可能会取上G的数据，不合理

mysql在底层将一个节点大小(页大小)设置为16k，可通过下面sql进行查询

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

也就是说一个节点最大为16k字节

问3. 为什么B+Tree要把data移动到叶子节点去？
由上面问2可知，把data都移到叶子节点之后，就给非叶子节点腾出了更多的空间，那么非叶子节点横向就能存储更多的索引值，因此查询效率会更高

问4.B+树中叶子节点带顺序访问指针的好处？
访问指针好处：比如说我们查找>20的数据，B树可能需要再返回根节点继续查找(可能要重复多次)，而B+树因为有指针，可以顺序去查就ok，所以可以直接将索引对应的数据查找出来，而不需要返回父节点的操作，加快了查询速度

问5. 为什么mysql要把一个节点设置成16k(为什么mysql页文件默认设置为16k)？
假设我们一行数据大小为1k，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页就可以存储16k/14=1170个(主键+指针)
那么一颗高度为2的B+Tree就能存储的数据为：117016=18720条；
一颗高度为3的B+Tree能存储的数据为：11701170*16=21902400(千万级条)
一般B+TREE的高度就在1-3之间，极端情况会到5；因此其性能非常高

3.2 mysql的存储引擎

mysql的存储引擎，都是形容数据库表的，而不是形容数据库的，也就是不同的表的存储引擎可以不同

1）MyISAM存储引擎索引实现(非聚集索引)

MyISAM存储引擎底层用的是B+TREE来存储数据的

MyISAM索引文件和数据文件是分离的

非聚集索引：数据与索引是分开存储的

MyISAM存储引擎下表文件描述
mysql数据库中，每张表在磁盘中(数据库的对应目录下)有几个文件相对应，分别存储着这张表的数据、表结构、索引等信息；例如：test_myisam表对应如下
1. test_myisam.frm —> 表的表结构
2. test_myisam.MYD —> 表的所有数据
3. test_myisam.MYI —> 表的索引(主键自带主键索引)

MYI文件(索引文件)中B+TREE数据结构的索引与表中数据的关联关系：
在这里插入图片描述

2）InnoDB存储引擎索引实现(聚集索引)

InnoDB存储引擎也是通过B+TREE存储索引，但是结构和MyISAM有一些不同

InnoDB存储引擎的数据和索引是存储在一个文件中的

聚集索引：数据与索引是存在一起的，且B+树的叶节点包含了完整的数据结构

InnoDb存储引擎下表文件描述
mysql数据库中，每张表在磁盘中(数据库的对应目录下)有几个文件相对应，分别存储着这张表的数据、表结构、索引等信息；例如：test_innodb表对应如下
1. test_innodb.frm —> 表结构文件
2. test_innodb.ibd —> 数据+索引文件

ibd文件(数据+索引文件)中B+TREE数据结构的索引与表中数据的关联关系：
在这里插入图片描述

由上我们可知在InnoDB中：
1）表数据文件本身就是按B+Tree组织的一个索引结构文件
2. 聚集索引：叶节点包含了完整的数据结构

为什么InnoDB的数据和索引存放在一起？
MyISAM在到叶子节点确定了需要查找到数据之后，还需要一个文件指针到数据文件中查找对应数据，而InnoDB因为数据与索引是存在一起的(一个ibd文件中)，因此不用再通过指针到其他文件，提高了查询效率

主键索引
InnoDB存储引擎的一个表的主键索引就是一个聚集索引
在这里插入图片描述

非主键索引
在这里插入图片描述

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
1）为什么必须有主键：
innodb的表的数据文件就是个B+Tree组织的一个索引结构文件，这种结构就决定了必须要有一个索引，而且必须要有一个主键，因为主键索引就是一个聚集索引；即便你新建表时没有手动建立一个索引，mysql后台也会帮忙自动创建一个索引(会找一个可唯一标示一行数据的字段加索引，如果没有，则会自己加一列可唯一标示的数据做索引)
2）为什么推荐使用整型自增：
a）为什么不推荐用UUID做索引而用整型
用UUID(长串字符串，占存储较多)，而整型的8B，所以一方面UUID更占磁盘空间，同时，对于一个节点16k来讲横向存储的数据更少，树的高度更高，那么查找的效率更低
另外，通过索引查找数据是，同一节点肯定要通过比较大小之后才能找到对应的指针到达下一层节点，而整型比较大小的效率明显要比UUID的字符串比较大小的效率要高(UUID需要先转为ascII码才能再进行比较)
b）为什么自增
B+Tree叶子节点，从左到右依次递增(继承了B Tree的特点)；如果索引用UUID，下一行数据不一定比上一行数据大，不一定是递增的；如果用递增的，那么就可以直接追加在叶子节点的最右边即可，如果不是递增，那这行数据可能要插到叶子节点的中间的某个位置；而其上层非叶子节点16k目前已占满，因此需要再该叶子节点进行分裂(树的分裂)，再插入这行数据，相较于直接追加在叶子节点最右侧，树的分裂明显效率要慢得多

为什么非主键索引结构叶子节点存储的是主键值？
1. 一致性
2. 节省存储空间