mysql索引底层原理分析

最新推荐文章于 2024-09-19 22:06:54 发布

布道

最新推荐文章于 2024-09-19 22:06:54 发布

阅读量2.9w

点赞数 27

分类专栏： oracle|sqlserver|mysql 文章标签： mysql InnoDB MyISAM

本文链接：https://blog.csdn.net/alex_xfboy/article/details/82818753

版权

oracle|sqlserver|mysql 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

大家都知道索引的重要性，基本用法在上章《最全面的mysql索引知识大盘点》已分享过，本章主要是探索索引的底层实现原理。当然了，我们还是以mysql为基准进行探讨。

前言：innodb和myisam的区别

	InnoDB	MyISAM
简介	由Innobase Oy公司开发。支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。	默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。
使用场景	在线事务处理(OLTP)型应用	在线分析处理(OLAP) 型应用
锁差异	Innodb支持事务和行级锁，是innodb的最大特色。事务的ACID属性，并发事务带来的几个问题：更新丢失，脏读，不可重复读，幻读。事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)	myisam只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。
数据库文件差异	innodb属于索引组织表 innodb有两种存储方式，共享表空间存储和多表空间存储两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm。如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。	myisam属于堆表 myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。 .frm 用于存储表的定义 .MYD 用于存放数据 .MYI 用于存放表索引 myisam表还支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉) 动态表压缩表
索引差异	1、关于自动增长 myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。 innodb引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。 2、关于主键 myisam允许没有任何索引和主键的表存在， myisam的索引都是保存行的地址。 innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见) innodb的数据是主索引的一部分，附加索引保存的是主索引的值。 3、关于count()函数 myisam保存有表的总行数，如果select count() from table;会直接取出出该值 innodb没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。 4、全文索引 myisam支持 FULLTEXT类型的全文索引 innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询） 5、delete from table 使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限) 6、索引保存位置 myisam的索引以表名+.MYI文件分别保存。 innodb的索引和数据一起保存在表空间里

InnoDB有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)：

InnoDB的每一个表都会有聚集索引
如果表没有定义PK，则第一个非空unique列是聚集索引，否则，InnoDB会创建一个隐藏的row-id作为聚集索引
InnoDB的普通索引，实际上会扫描两遍：第一遍，由普通索引找到PK，第二遍，由PK找到行记录

聚集索引

数据文件本身就是索引文件，非叶子节点存放<key,address>，address就是下一层的地址，通过PK可以直接定位到数据地址

非聚簇索引

叶子节点上的data是主键(即聚簇索引的主键)，而不是记录所在地址，因为记录所在地址并不能保证一定不会变，但主键可以保证。定位流程第一步，由普通索引找到PK，第二步，由PK找到行记录

1.物理磁盘知识

首先dbms本身就是一个文件管理系统，只是它的实现方式确实比较复杂，但本质上是要通过访问磁盘才能完成数据的存储与检索。本着刨根问底的精神，就要分析文件是存储及检索的。

1.1基本概念

盘片	硬盘中一般会有多个盘片组成，盘片一般用铝合金材料做基片硬盘的盘片组在 2-14 片不等，通常有 2-3 个盘片
盘面	一个盘片都有上下两个盘面，一般每个盘面都会得到利用，都可以存储数据，成为有效盘面，也有极个别的硬盘盘面数为单数，每一个有效盘面都有一个盘面号，按顺序从上至下从 0 开始编号
磁头	每一个有效盘面都有一个对应的读写磁头，作用就是将存储在硬盘盘片上的磁信息转化为电信号向外传输工作原理则是利用特殊材料的电阻值会随着磁场变化的原理来读写盘片上的数据。磁头是用线圈缠绕在磁芯上制成的。硬盘在工作时，磁头通过感应旋转的盘片上磁场的变化来读取数据；通过改变盘片上的磁场来写入数据。为避免磁头和盘片的磨损，在工作状态时，磁头悬浮在高速转动的盘片上方，而不与盘片直接接触，只有在电源关闭之后，磁头会自动回到在盘片上的固定位置（称为着陆区，此处盘片并不存储数据，是盘片的起始位置）。
磁道	磁盘在格式化时盘面被划分成许多同心圆，这些同心圆轨迹叫做磁道，而磁带的磁道是沿磁带长度方向的直线，这些磁道用肉眼是根本看不到的。磁道从外向内从0开始顺序编号，每一个盘面有 300-1024 个磁道，新式大容量硬盘每面的磁道数更多，信息以脉冲串的形式记录在这些轨迹中，这些同心圆不是连续记录数据，而是被划分成一段段的圆弧。当磁盘旋转时，磁头若保持在一个位置上，则每个磁头都会在磁盘表面划出一个圆形轨迹，这些圆形轨迹就叫做磁道
柱面	所有盘面上的同一磁道（具有相同编号磁道）构成一个圆柱，通常称作柱面。每个圆柱上的磁头由上而下从 0 开始编号，数据的读 / 写按柱面进行，只有在同一柱面所有的磁头全部读 / 写完毕后磁头才转移到下一柱面。选取磁头只需要通过电子切换即可，而选取柱面则必须机械切换，电子切换相当快。
扇区	每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区，扇区是硬盘的最小读写单元。操作系统以扇区形式将信息存储在硬盘上，每个扇区包括512个字节的数据和一些其他信息，一个扇区有两个主要部分：存储数据地点的标识符和存储数据的数据段。标识符就是扇区头标，包括组成扇区三维地址的三个数字：盘面号，柱面号，扇区号（块号）。数据段可分为数据和保护数据的纠错码（ECC）。
磁盘块/簇	虚拟出来的，块是操作系统中最小的逻辑存储单位，操作系统与磁盘打交道的最小单位是磁盘块。通俗的来讲，在Windows下如NTFS等文件系统中叫做簇；在Linux下如Ext4等文件系统中叫做块（block）。每个簇或者块可以包括2、4、8、16、32、64…2的n次方个扇区读取方便：由于扇区的数量比较小，数目众多在寻址时比较困难，所以操作系统就将相邻的扇区组合在一起，形成一个块，再对块进行整体的操作。分离对底层的依赖：操作系统忽略对底层物理存储结构的设计。通过虚拟出来磁盘块的概念，在系统中认为块是最小的单位。
Page	操作系统经常与内存打交道的最小单位是页，类似于“块”的概念，都需要一种虚拟的基本单位。

磁盘容量计算

存储容量＝磁头数 × 磁道(柱面)数 × 每道扇区数 × 每扇区字节数

某磁盘是一个 3个圆盘6个磁头，7个柱面（每个盘片7个磁道）的磁盘，每条磁道有12个扇区，所以此磁盘的容量为：6 * 7 * 12 * 512 = 258048

1.2硬盘中的数据

信息存储在硬盘里，硬盘是由很多的盘片组成，通过盘片表面的磁性物质来存储数据。
把盘片放在显微镜下放大，可以看到盘片表面是凹凸不平的，凸起的地方被磁化，代表数字 1，凹的地方没有被磁化，代表数字 0，因此硬盘可以通过二进制的形式来存储表示文字、图片等的信息。
所有的盘片都固定在一个旋转轴上，这个轴即盘片主轴，所有的盘片之间是绝对平行的，在每个盘片的盘面上都有一个磁头，磁头与盘片之间的距离比头发丝的直径还小。
所有的磁头连在一个磁头控制器上，由磁头控制器负责各个磁头的运动，磁头可沿盘片的半径方向移动，实际上是斜切运动，每个磁头同一时刻必须是同轴的，即从正上方往下看，所有磁头任何时候都是重叠的。
由于技术的发展，目前已经有多磁头独立技术了，在此不考虑此种情况。
盘片以每分钟数千转到上万转的速度在高速运转，这样磁头就能对盘片上的指定位置进行数据的读写操作。
由于硬盘是高精密设备，尘埃是其大敌，所以必须完全密封。

1.3磁盘的读写原理

系统将文件存储到磁盘上时，按柱面、磁头、扇区的方式进行，即最先是第1磁道的第一磁头下的所有扇区，然后是同一柱面的下一个磁头……
一个柱面存储满后就推进到下一个柱面，直到把文件内容全部写入磁盘。
系统也以相同的顺序读出数据，读出数据时通过告诉磁盘控制器要读出扇区所在柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。

注：操作系统读取同理，只是颗粒的更大的块操作

1.5磁盘的读取响应时间

当需要从磁盘读取数据的时候，系统会将数据的逻辑地址传递个磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。

首先必须找到柱面，即磁头需要移动对准相应磁道，这个过程叫做寻道。

然后目标扇区旋转到磁头下，即磁盘旋转将目标扇区旋转到磁头下。

寻道（时间）：磁头移动定位到指定磁道所需要的时间，寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在3－15ms，一般都在10ms左右。

旋转延迟（时间）：盘片旋转将请求数据所在扇区移至读写磁头下方所需要的时间，旋转延迟取决于磁盘转速。普通硬盘一般都是7200rpm，慢的5400rpm。

数据传输（时间）：数据在磁盘与内存之间的实际传输所需要的时间。

确定磁盘地址（柱面号，磁头号，扇区号），内存地址（源/目）：
为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点：
即一次访盘请求（读 / 写）完成过程由三个动作组成：

注：读写一次磁盘信息所需的时间中软件应着重考虑减少寻道时间和延迟时间。

1.6 I/O 的预读与局部性原理

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费的时间，磁盘的存取速度往往是主存的几百分之一。

因此，计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据一般来说也会被马上使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需要很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。

预读的长度一般为页（在许多操作系统中，页的大小通常为 4k）的整数倍。操作系统以内存页为单位管理内存，内存页的大小对系统性能有影响。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信息，磁盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中（系统从磁盘读取数据时是以磁盘块为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取），然后异常返回，程序继续运行。

2.推理并拆解普通查询语句

select * from talbe_name where id=1

step1：找到数据文件

step2：读取数据文件

step3：读取id=1的数据

理论上是这样的，索引是一种用来实现高效获取数据的数据结构，建索引的目的是为了查找的优化，特别是当数据很庞大的时候，非常重要。一般的查找算法有顺序查找、折半查找、快速查找等，但是每种查找算法只能应用于特定的数据结构，例如顺序查找依赖于顺序结构，折半查找通过二叉查找树或红黑树实现二分搜索。因此在数据之外，数据库系统还维护着满足特定查找算法的数据结构，它以某种方式引用数据。

3.为什么要用B+Tree实现

目前大多数数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。B+ 树中的 B （balance）代表平衡，而不是二叉。B+ 树是从最早的平衡二叉树演化而来的。B+ 树是由二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree）逐步优化而来。

二叉查找树：左子树的键值小于根的键值，右子树的键值大于根的键值。
AVL 树：平衡二叉树（AVL 树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为 1，但不是红黑树。
平衡多路查找树（B-Tree）：为磁盘等外存储设备设计的一种平衡查找树。

那么纠结该如何选型呢？

索引的标准：IO渐进复杂度，说白了就是推演过程（每个节点都是1次IO），B+树是为磁盘及其他存储辅助设备而设计一种平衡查找树（不是二叉树），所有记录的节点按大小顺序存放在同一层的叶节点中，各叶节点用指针进行连接。

InnoDB 存储引擎使用页作为数据读取单位，页是其磁盘管理的最小单位，默认 page 大小是 16k。系统的一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。在查询数据时如果一个页中的每条数据都能助于定位数据记录的位置，这将会减少磁盘 I/O 的次数，提高查询效率。

3.1 B-Tree

B-树是一种多路自平衡的搜索树它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

注：B-Tree就是我们常说的B树，一定不要读成B减树，否则就很丢人了

那么m阶 B-Tree 是满足下列条件的数据结构：

所有键值分布在整颗树中
搜索有可能在非叶子结点结束，在关键字全集内做一次查找,性能逼近二分查找
每个节点最多拥有m个子树
根节点至少有2个子树
分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 17 和 35，P1 指针指向的子树的数据范围为小于 17，P2 指针指向的子树的数据范围为 17~35，P3 指针指向的子树的数据范围为大于 35。

模拟查找关键字 29 的过程：

根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】
比较关键字 29 在区间（17,35），找到磁盘块 1 的指针 P2。
根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】
比较关键字 29 在区间（26,30），找到磁盘块 3 的指针 P2。
根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】
在磁盘块 8 中的关键字列表中找到关键字 29。

分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。

但同时B-Tree也存在问题：

每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小。
当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率

3.2 B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。它带来的变化点：

B+树每个节点可以包含更多的节点，这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快
非叶子节点存储key，叶子节点存储key和数据
叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高

注：MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，因此力求达到树的深度不超过 3，也就是说 I/O 不需要超过 3 次。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

3.3 B-树和B+树的区别

B+树内节点不存储数据，所有数据存储在叶节点导致查询时间复杂度固定为 log n
B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)
B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等
~~B-树每个节点 key 和 data 在一起，则无法区间查找~~
B+树更适合外部存储(存储磁盘数据)。由于内节点无 data 域，每个节点能索引的范围更大更精确。

3.4 MongoDB 为什么使用B-树

B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。尽可能少的磁盘 IO 是提高性能的有效手段。MongoDB 是聚合型数据库，而 B-树恰好 key 和 data 域聚合在一起。

至于MongoDB为什么使用B-树而不是B+树，可以从它的设计角度来考虑，它并不是传统的关系性数据库，而是以Json格式作为存储的nosql，目的就是高性能，高可用，易扩展。首先它摆脱了关系模型，上面所述的优点2需求就没那么强烈了，其次Mysql由于使用B+树，数据都在叶节点上，每次查询都需要访问到叶节点，而MongoDB使用B-树，所有节点都有Data域，只要找到指定索引就可以进行访问，无疑单次查询平均快于Mysql。