MySQL索引机制-图灵教育诸葛老师

最新推荐文章于 2022-05-26 08:54:53 发布

此生辽阔

最新推荐文章于 2022-05-26 08:54:53 发布

阅读量905

点赞数 2

分类专栏： Mysql

本文链接：https://blog.csdn.net/ningmengshuxiawo/article/details/118607117

版权

Mysql 专栏收录该内容

24 篇文章 7 订阅

订阅专栏

索引是帮助MySql高效获取数据的排好序的数据结构
在这里插入图片描述

在上图中，如果想要查找89使用select * from table where col2=89的话，需要遍历整个表，需要6次查询才能找到

如下图，如果给Col2列加上索引，那么只要2次就能查找到89了，查找到89所在的行，根据指针就能定位到89所在的行在磁盘中的地址0X77
在这里插入图片描述

这样就节约来了6次查找，查一次就是一次IO，我们要尽可能减少磁盘的IO次数

用合理的数据结构存储索引字段，再来执行sql语句，根据索引的数据结构来查找，效率就会提升很多

如果对上图的Col1建立索引，数据结构就用二叉查找树的话，那么就会形成一条单链表
在这里插入图片描述

索引也是放在磁盘上面的，上面的二叉树查找6也需要6次，跟全表扫描就没有区别了，意义不大

在这里插入图片描述

使用红黑树查找6需要找3次
红黑树的弊端：真实工作中，表的记录多大几百万行，假设有500条记录，就可能有500万个索引加到红黑树上面，红黑树的高度会非常高，数据量大的话，红黑树的性能会很低，红黑树是二叉树，也是二叉平衡树，带自我平衡的功能
在这里插入图片描述

那么同样高度的树，如果能够存储更多的元素，查找的效率会更高

改进，mysql会在磁盘上给索引划分空间，现在我们一次多划分一块空间，这个大的节点一次可以存储多个索引，那么这个节点产生分支的时候，每一个分支节点也可以非常大，相比于原来的红黑树一个节点存储一个索引，在树高度相同时，可以存储更多的索引，这个结构叫B树
在这里插入图片描述

在这里插入图片描述

mysql索引在B树上做了优化，使用的是B+树，非叶子节点没有存储data元素，而是存储了一些冗余的索引，所有的索引元素都是存在叶子节点的，每一个节点对应的所在行的磁盘文件地址存在data里面
在这里插入图片描述

我们来计算一下这个B+树能存放多少个索引

在这里插入图片描述
一个磁盘块是16kb 16kb/(8+6)b = 1170

这个data里面可能是存放的索引对应磁盘文件的地址，也可能是整列的数据，假设是1kb
在这里插入图片描述

那么 1170* 1170 *16=2190万，这么多个索引
假设表记录有2000多万行，高度为3的B+树就够放所有的索引了，如果不加索引，2000万行的记录全表扫描可能要查几分钟

假设要查找30这个索引
在执行索引的时候，mysql首先会把磁盘里面的索引节点加载到RAM里面（实际上mysql初始化的时候就把索引的根节点加载到内存里面了），首先通过折半查找，确定30在15-56之间

在这里插入图片描述

然后把下一层的索引加载到内存（高版本的mysql把索引的这一层非叶子节点也预先加载到内存了）
在这里插入图片描述

然后把找到的元素从磁盘加载到内存，然后定位到30这个索引，一共做了3次IO
这些数据只能用到的时候再从磁盘加载到内存，因为数据量太大了
在这里插入图片描述

那么mysql运行足够就久的话，所有的数据都有可能查到过，它们都在内存里面吗？

存储引擎是形容数据库表级别的,如下图，存储引擎生效是在表级别
在这里插入图片描述

MyIsam存储引擎的mysql语句的执行流程
.frm存储表的定义信息，结构信息
.MYD存储数据
.MYI存储索引
在这里插入图片描述

先看是不是索引字段，如果不是，就全表扫描，如果是索引字段，先在.MYI文件里面定位到索引所在行的磁盘文件地址，然后把地址取出来，去.MYD文件（回表），快速定位该地址对应的记录
在这里插入图片描述

实际上用的较多的是InnoDB存储引擎，因为多了事务的支持

InnoDB存储引擎的mysql语句的执行流程
.frm 存储表的定义和结构信息
.ibd 存储表的索引和数据
在这里插入图片描述

所以，索引是这样的，跟MyIsam索引的区别主要在叶子节点
聚集（聚簇，都一样，因为是翻译过来的嘛）索引：叶子节点包含了完整的数据记录
MyIsaM的主键索引就是非聚集索引，索引在MYI文件，数据在MYD文件，索引跟数据是分开存储的
在这里插入图片描述
注意，InnoDB的普通索引和主键索引是不一样的，普通索引的叶子节点存储的是主键的值

注意，下面的普通索引找到主键的值（比如这里主键是id,普通索引找到了id为18）之后，根据得到的id去主键索引里面（也就是全表）找到响应的id，取出记录，这个过程也叫做回表
在这里插入图片描述

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键

因为InnoDB的索引和数据文件是放在一起的，我们需要组织一个结构来通过索引访问数据，如果有主键，mysql就用主键来构建整个表的B+树索引结构

对于InnoDB表，如果不建立主键索引，那么mysql也会去依次判断每一列，看这一列的每一行的值是否不一样，如果找到了，就用这一列建立B+树索引维护整张表的数据，如果找不到这样的列，那么mysql就会建立一个rowid，添加一列，每一行的值是1 2 3 ，这种，保证每一行的值不一样，用这一列来维护整张表的B+树结构

在这里插入图片描述