【数据库】详解MySQL数据库中索引的本质与底层原理
目录
1.MySQL索引的本质
1.1.索引的重要性
从MySQL数据库中查找数据，就类似于从图书馆找书，如果书比较少，那么查找的速度会比较快，如果书比较多，那么查找的速度就会变慢。这个时候图书的分类管理就显得非常重要了，图书分类管理就类似于索引
1.2.索引演示
索引的本质是一种数据结构
假设MySQL中有这样一张表：emp表，表中有1000000
在没有索引的情况下执行select语句，执行速度相对较慢
SELECT * FROM emp WHERE ename = 'c999017';可以对ename的列创建索引
--显示emp表所有的索引
SHOW INDEX FROM emp;
--对ename列创建索引
CREATE INDEX index_ename ON emp (ename);
--删除索引
DROP INDEX index_name ON emp1.3.索引的底层原理
问：为什么在没有索引的情况下，查询速度会比较慢？
答：因为数据都是存储在磁盘上的，在执行SQL语句的时候，一定会从磁盘上读取相关的数据，这会产生磁盘IO。如果没有索引，那么会进行多次磁盘IO操作，比较耗时
1.3.1磁盘IO的原理
机械硬盘，由于信息载体为磁性物质，故又称磁盘
1.3.2.硬盘的主要结构
在硬盘盒里面有圆形盘片、机械手臂、磁头、主轴马达
1.3.3.工作情形
数据写在具有磁性物质的盘片上，而读写主要是通过在机械手臂上的磁头（head）达成
运行时，主轴马达让盘片转动，然后机械手臂可伸展让磁头在盘片上进行读写的动作
1.3.4.各主要部件说明
(1) 盘片和主马达
主马达就是一个电机，作用是让盘片转动起来
对于机械硬盘，最重要的结构是两面涂有磁性材料的盘片，在工作中会以每分钟7200转的速度旋转
盘片的作用是记录数据，在盘片上有序的排列了很多的小颗粒材料，它们都是磁性物质，可以被永久磁化和改变磁极，这两个磁极就分别表示了计算机二进制中的0和1
由于盘片是转动后读写数据的，所以，当初设计就是在类似盘片同心圆上面切出一个一个的小区块，这些小区块整合成一个圆形，让机器手臂上的磁头去存取。这个小区块就是磁盘的最小物理储存单位，称之为扇区（sector），那同一个同心圆的扇区组合成的圆就是所谓的磁道（track）
扇区容量：原本硬盘的扇区都是设计成 512Byte（即0.5KB） 的容量，但因为近期以来硬盘的容量越来越大，为了减少数据量的拆解，所以新的大容量硬盘已经有 4KByte（即4KB）的扇区设计
由于单一盘片的容量有限，因此有的硬盘内部会有两个以上的盘片
由于磁盘里面可能会有多个盘片，因此在所有盘片上面的同一个磁道可以组合成所谓的柱面 （cylinder）
数据存储在盘片的一个个扇区中
-
一个扇区可存储512Byte的数据
-
一个平面中同一半径下的多个扇区共同组成了一个磁道
-
一个盘片有两个盘面，每个盘面都对应着一个磁头，负责读写数据
-
一个硬盘可以有多个盘片
-
同一半径下的多个磁道共同组成了一个柱面
(2) 磁头和机械手臂
机械手臂的作用是控制磁头移动
磁头的作用是在盘片上读写数据。磁头通过改变盘片上小颗粒磁性物质的磁极方向来完成写入数据的功能，通过感知盘片上磁性物质的磁极方向来完成读取数据的功能
1.3.5.扇区中是如何表示01数据的
硬盘是在硬质盘片（一般是铝合金，以前 IBM 也尝试过使用玻璃）上涂敷薄薄的一层铁磁性材料。这些磁粉被划分成磁道的若干个同心圆，在每个同心圆的磁道上就好像有无数的任意排列的小磁铁，它们分别代表着0和1的状态。当这些小磁铁受到来自磁头的磁力影响时，其排列的方向会随之改变。利用磁头的磁力控制一些小磁铁方向，使每个小磁铁都可以用来储存信息
写入时，磁头线圈上加电，在周围产生磁场，磁化其下的磁性材料；电流的方向不同，所以磁场的方向也不同，可以表示0和1的区别
读取时，磁头线圈切割磁场线产生感应电流，磁性材料的磁场方向不同，所以产生的感应电流方向也不同
当需要从磁盘读取数据的时候，操作系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要讲磁头放到这个扇区的上方。为了实现这一点，磁头需要移动对准相应的磁道。这个过程叫寻道，所消耗的时间叫做寻道时间。然后磁盘旋转将目标扇区旋转到磁头下，这个过程消耗的时间叫做旋转时间
2.MySQL索引底层原理
2.1.二叉查找树
树形结构网站：
采用二叉树作为索引的数据结构时，特点是：左子节点的值比父节点的值要小，右节点的值要比父节点的大
优点：可以优化磁盘IO的次数。节点有顺序，可以进行范围的查询
缺点：插入数据的速度会比较慢，因为会更改数据结构。可能会产生倾斜的二叉树
2.2.平衡二叉查找树
插入数据会平衡，但是插入的时候会改变树的结构，插入数据比较慢，而且树的层级会变高，会增加磁盘IO的次数
二叉树是有顺序的，支持范围查找
2.3.B树和B+树
B树
B+树
B树或者B+树的节点可以存储多个数据，所以相对于完全平衡二叉树的高度会更低，会降低磁盘IO的次数
B+树相对于B树有数据的冗余，叶子结点中的数据是有顺序的，那么再进行顺序查找的时候会非常方便，只要在叶子结点顺序向后遍历即可
3.MySQL索引如何存储数据
3.1.MySQL如何使用B+树
MySQL中InnoDB和MyISAM存储引擎默认使用B+树数据结构作为索引，存储的数据结构如下
问：为什么只有叶子结点会存储数据，而非叶子结点不存储数据
答：局部性原理：当一个数据被用到时，其附近的数据被用到的概率会增大，所以操作系统为了提高效率，读取数据时往往不是按需读取，而是每次都会预读，即使只需要一个字节，操作系统也会从这个位置开始，顺序向后读取一定长度的数据放入内存中。这里的长度叫做页，也就是计算机操作系统操作磁盘的基本单位，一般操作系统中一页的大小为4kb
在MySQL中可以使用如下的命令查看InnoDB引擎页的默认大小
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'MySQL页的大小默认是16kb，B+树的设计非常适合读取数据
如果节点既存储数据又存储索引，一个节点的大小一定，数据较多时索引就较少，每个节点的分支会变少，树的高度会变高，导致磁盘IO变多，效率变低
3.2.叶子节点可以存储多少数据
一个节点可以存储16kb的数据，这是MySQL默认的节点大小：一页16kb
假设一行记录的大小为1kb（其实已经很大了）
那么一个叶子节点就能存储16条数据
非叶子节点里面存储的是索引的值和指针，MySQL默认的索引值大小是8B，指针大小是6B，合在一起是14B，那么非叶子节点可以存储的索引+指针的个数为：
16 * 1024 / 14 = 1170个
如果树的高度是2层，叶子节点的个数是1170个，那么可以存储的数据条数就是：
1170 * 16 = 18720条
如果树的高度是3层，那么叶子节点可以存储的数据条数就是
1170 * 1170 * 16 = 21902400条
3.3.InnoDB引擎存储结构
数据文件
user.frm是创建表的文件
user.ibd是数据+索引文件
InnoDB引擎是聚集索引，索引和数据文件在一起
如果以name字段设置索引，那就是二级索引（辅助索引）。叶子结点中存储该列的数据和主键值，也就意味着还需要再通过主键去查找一次数据
3.4.MyISAM引擎存储结构
MyISAM引擎的索引采用的非聚集索引，索引和表数据分开存储
user.frm文件是创建表的文件
user.MYD文件是表的数据文件
user.MYI文件是索引文件
索引文件：
数据文件：
MyISAM引擎通过索引值找到对应的地址，通过地址找到数据
原文地址：https://blog.csdn.net/2302_78914800/article/details/145328486
免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！