MySQL数据库引擎

zijikanwa

已于 2022-04-20 20:33:08 修改

阅读量638

点赞数

分类专栏： Database 文章标签： MySQL数据库引擎

于 2018-05-17 16:07:40 首次发布

本文链接：https://blog.csdn.net/zijikanwa/article/details/80352432

版权

Database 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

文章目录

1 B-树和B+树
2 内存和磁盘存取原理
3 数据库索引
4 MySQL数据库引擎
参考资料

写作说明：1,2,3章是第4章（MySQL数据库引擎）的基础概念。

1 B-树和B+树

B-树和B+树是多叉平衡树。

1.1 B-树

B-树就是B树，中间的横线并不是减号。
B-树主要用作文件的索引。

1.1.1 B-树的定义

一棵m阶的B-Tree,或者为空树，或者满足下列特性：

树中每个结点至多有m棵子树；
若根结点不是叶子结点，则至少有两棵子树；
除根节点之外的所有非终端结点至少有 $\lceil m/2 \rceil$ 棵子树；
所有非终端结点中包含下列信息数据： $(n, A_0, K_1, A_1, K_2, A_2,…, K_n, A_n) $

其中，n为关键字的数目；
$K_i(i=1,..,n)$ 为关键字，且 $K_i< K_{i+1}$ ；
$A_i(i=0,...,n)$ 为指向子树根结点的指针。 $A_i$ 所指子树中所有结点的关键字均小于 $K_{i+1}$ ，且均大于 $K_i$ ，即 $K_i,K_{i+1})$ ；

5.所有叶子结点都出现在同一层次上；

1.1.2 B-Tree的查找过程

举例：如下图所示，是一个4阶的B-树。
查找关键字47。

首先，从根结点开始，根据根结点指针t找到*a结点，因为47大于 *a 结点的关键字35，所以会去A1指针指向的 *c结点继续寻找，因为 *c的关键字 43 < 要查找的47 < *c结点的关键字78，所以去 *c结点A1指针指向的 *g结点去寻找，结果在 *g结点中找到了关键字47，查找成功。

1.2 B+树

B+树是应文件系统所需而出的一种B-Tree的变型树。
B+树上有两个头指针，一个（root）指向根节点，另一个（sqt）指向关键字最小的叶结点。
对B+树的查找（两种方式）
1. 从最小关键字起顺序查找；
2. 从根节点开始，进行随机查找。

1.2.1 B+树的定义

其定义与B-树基本相同，除了：

非叶子结点的子树指针与关键字个数相同；
非叶子结点的子树指针 $A_i$ ，指向关键字值属于 $K_{i-1}, K_i]$ 的子树（B-树是开区间）；
为所有叶子结点增加一个链指针；
所有关键字都在叶结点出现；

1.2.2 B+树的示意图

其中，叶节点的data域存放的是数据记录的地址

1.2.3 B-树 & B+树

B-树：具有n棵子树的结点中含有n-1个关键字；B+树：具有n棵子树的结点中含有n个关键字；。
B-树：叶结点之间没有连接；B+树：叶结点本身依关键字的大小自小而大顺序连接；**。
B-树：所有结点都是存储关键字的数据层；B+树：非叶子结点相当于是叶子节点的索引，叶子节点才是存储所有关键字数据的数据层。
B-树：查找时可以在非叶结点命中；B+树：查找时只能在叶结点命中（即使非叶结点上的关键字等于给定值，也不终止，而是继续向下直到叶结点）。

2 内存和磁盘存取原理

2.1 主存存取原理

2.1.1 主存概述

目前计算机使用的主存基本都是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂，这里抽象出一个十分简单的存取模型来说明RAM的工作原理。

2.1.2 主存的内部结构

主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。如下图展示了一个4 x 4的主存模型。

2.1.3 主存的存取过程

读主存
1. 当系统需要读取主存时，将地址信号放到地址总线上传给主存；
2. 主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。
写主存
1. 系统将要写入单元地址和数据分别放在地址总线和数据总线上；
2. 主存读取两个总线的内容，做相应的写操作。
总结
1. 主存存取的时间仅与存取次数呈线性关系。
2. 两次存取的数据的“距离”不会对时间有任何影响。
  - 原因：不存在机械操作。
  - 举例：先取A0再取A1和先取A0再取D3的时间消耗是一样的。

2.2 磁盘存取原理

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。
磁盘I/O存在机械运动耗费

2.2.1 磁盘结构

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动。
每个盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。

2.2.2 磁盘存取原理

为了简单起见，我们假设磁盘只有一个盘片和一个磁头。
读取磁盘
1. 当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。
2. 为了读取这个扇区的数据，需要将磁头放到这个扇区上方。为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

2.2.3 局部性原理与磁盘预读

2.2.3.1 背景

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。

局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用；程序运行期间所需要的数据通常比较集中。
磁盘预读：磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。
当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后返回，程序继续运行。

3 数据库索引

3.1 索引的概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。因此，索引本质上是一种数据结构。

3.2 查询算法

数据库查询是数据库的主要功能之一。

3.2.1 顺序查找（linear search）

顺序查找是最基本的查询算法，时间复杂度为O(n)，因此在数据量很大时效率很低。

3.2.2 二分查找（binary search）

查找效率有所提高，但要求被检索数据有序。

3.2.3 二叉树查找（binary tree search）

查找效率有所提高。
缺点：二叉树查找只能应用于二叉查找树上，但二叉查找树不可能满足所有列的组织需求，它只能维护一列的索引需求（例如，不可能同时将两列都按顺序进行组织），因此二叉查找树不能作为数据本身的组织结构。
解决：在数据外，增加索引。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这种数据结构就是索引。
举例：二叉查找树可以作为一种索引方式来获取相应的数据

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意:逻辑上相邻的记录在磁盘上并不一定物理相邻）。
维护索引：为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在 $O(\log_{2}n)$ 的复杂度内获取到相应数据。

3.3 数据库中常用索引（B-树和B+树）

3.3.1 数据库中选用B-树和B+树这种数据结构的原因

因为使用B树查找时，所用的磁盘IO操作次数比二叉查找树更少，效率也更高。
- 二叉查找树：在大规模数据存储中，二叉查找树结构由于树的高度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下。
- B-树和B+树可以保持较低的高度，从而达到有效减少磁盘IO操作次数。

3.3.2 数据库中B-树和B+树作为索引的检索性能分析

B-树和B+树的设计：每次新建结点时，直接申请一个页面的空间，这样可以保证一个结点的大小等于一个页面；此外，计算机存储分配都是按页对齐的，就实现了一个结点只需一次I/O。
性能：B-Tree中一次检索最多需要 $h - 1$ 次I/O（根结点常驻内存），渐进复杂度为 $O(h)=O(\log_dn)$ ，其中 $d$ 是分支树。因此用B-树或B+树作为索引结构效率是非常高的。
B+树的性能：性能更好。
1. 索引的性能与内结点出度d有关，d越大索引的性能越好；
2. 出度的上限取决于结点内key和data的大小，公式如下：
  $dmax=\lfloor(pagesize/(keysize+datasize+pointsize))\rfloor$
3. 由于B+Tree内结点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

3.4 聚集索引（clustered）和非聚集索引

3.4.1 聚集索引 / 聚簇索引

定义
1. 数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同。一个表中只能拥有一个聚集索引。
2. 从数据结构来描述：索引的叶节点就是数据节点。
举例

地址 id username score
0x01 1 小明 90
0x02 2 小红 80
0x03 3 小华 92
… … … …
0xff 256 小英 70

地址	id	username	score
0x01	1	小明	90
0x02	2	小红	80
0x03	3	小华	92
…	…	…	…
0xff	256	小英	70

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。
聚集索引的表现：如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。

聚集索引的示意图
1. 索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列数据。
MySQL里主键就是聚集索引。
1. 最好在创建表的时候添加聚集索引。由于聚集索引的物理顺序上的特殊性，因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序，会非常地耗费时间以及性能。

3.4.2 非聚集索引

定义
1. 该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
2. 非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
非聚集索引的类别：除了聚集索引以外的索引都是非聚集索引。如普通索引、唯一索引、全文索引。
非聚集索引的示意图
非聚集索引的二次查询问题
1. 如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么还需要进行第二次的查询，查询叶节点上对应的数据行的数据。
2. 举例：如聚集索引是id列，非聚集索引是username
  1. 查询以下语句不需要进行二次查询，可以直接从叶结点获取到查询列的数据。
```
select id, username from t1 where username = '小明' 
select username from t1 where username = '小明'
```
  2. 查询以下语句需要进行二次查询，来获取原数据行的score。
```
select username, score from t1 where username = '小明'
```
  3. 如何解决非聚集索引的二次查询问题？
    1. 复合索引（覆盖索引）：建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如 index(col1, col2) ，执行下面的语句：
```
select col1, col2 from t1 where col1 = '213';
```
    2. 注意：使用复合索引需要满足最左侧索引的原则，即如果where条件里面没有最左边的一到多列，索引就不会起作用。

3.4.3 总结

使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

4 MySQL数据库引擎

数据库引擎概述
1. 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，或分析数据。包括：创建表，查看和管理数据库对象（如索引、视图和存储过程等）。
2. 数据库引擎取决于MySQL在安装时是如何被编译的。

4.1 MySQL数据库引擎的索引结构

MySQL数据库引擎主要有两种结构：Hash索引和B+树索引。

1. Hash索引

在MySQL中，Hash索引是Memory引擎的默认索引类型。当然也可以使用B+Tree索引。
只有Memory（Memory表只存在内存中，断电会消失，适用于临时表）存储引擎显示支持Hash索引。
Hash索引的特点
1. 查找某一条记录的时候，速度非常快。因为hash结构的每个键对应一个值。
2. 它并不支持范围查找和排序等功能。因为Hash以散列的方式分布。

2. B+树索引

B+树是mysql使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎模式的索引类型。
B+树的特点
1. B+树更适合排序、顺序读取等操作。
2. B+树在查找单条记录的速度比不上Hash索引。

4.2 MySQL引擎的索引结构

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式不同，MySQL主要包括MyISAM和InnoDB两个存储引擎。

1. MyISAM引擎的索引结构

概述
1. MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址。
2. MyISAM的索引是非聚集索引。
MyISAM索引的原理图
1. MyISAM的索引文件仅仅保存数据记录的地址。
MyISAM索引的检索算法
1. 首先，按照B+Tree搜索算法搜索索引。如果指定的Key存在，则取出其data域的值；
2. 然后，以data域的值为地址，二次查询并读取相应数据记录。
建立某表的MyISAM的主索引（Primary key）和辅助索引（Secondary key）
1. 主索引：Col1
2. 辅助索引：Col2
3. 主索引和辅助索引的相同和区别
  1. 相同：它们在结构上没有任何区别。
  2. 区别：主索引要求key是唯一的，而辅助索引的key可以重复。
4. 主索引和辅助索引的示意图

2. InnoDB引擎的索引结构

概述
1. InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
2. InnoDB的主索引（同时也是数据文件）是聚集索引，辅助索引是非聚集索引。
3. InnoDB要求表必须有主键（MyISAM可以没有）。
  1. 如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键；
  2. 如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
InnoDB & MyISAM
1. InnoDB的数据文件本身就是索引文件（主索引）。
  1. MyISAM：索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
  2. InnoDB：数据文件本身就是按B+Tree组织的一个索引结构，其叶结点的key保存了数据表的主键，data域保存了完整的数据记录。
2. InnoDB的辅助索引data域存储相应记录主键的值，而不是地址。
InnoDB的主索引和辅助索引
1. 由于主索引属于聚集索引，使得按主键的搜索十分高效；
2. 其辅助索引一般需要二次查询：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
3. 举例：id为主索引，name为辅助索引。示意图如下：
注意
1. InnoDB不建议使用过长的字段作为主键。因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
2. InnoDB建议使用自增字段作为主键。因为InnoDB数据文件本身是一颗B+Tree，如果使用非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效。

4.3 Mysql常用的数据库引擎

总结：同一个数据库可以使用多种存储引擎的表。
1. 如果一个表要求比较高的事务处理，可以选择InnoDB存储引；
2. 如果一个表要求查询比较高，可以选择MyISAM存储引；
3. 如果数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

1. ISAM

概述：ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。
优点
- 执行读取操作的速度很快，而且不占用大量的内存和存储资源。
缺点
1. 不支持事务处理；
2. 不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。

2. MyISAM

概述：MyISAM是ISAM扩展格式，是MySQL的默认数据库引擎（5.5版之前）。它提供了ISAM没有的索引和字段管理功能，以及一些扩展工具：如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的MyISAMPACK工具。
特点
- 快速读取操作
- 不支持事务处理
- 使用表锁定的机制，来优化多个并发的读写操作。其代价是需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。
- 存储了表的行数。SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。
- 不能在表损坏后恢复数据

3. InnoDB

概述
1. InnoDB数据库引擎是MySQL灵活性的技术的直接产品，这项技术就是MYSQL+API。MySQL5.5版本开始Innodb已经成为Mysql的默认引擎。
2. 它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。
特点
1. 支持事务处理和故障恢复
  - 故障恢复：利用事务日志进行数据恢复，速度比较快
2. 提供行级锁和外键约束
  - 行级锁说明：写操作不会锁定全表，因此并发效率较高。但使用行级锁也不是绝对的，如果在执行一个SQL语句时不能确定要扫描的范围，InnoDB表同样会锁全表。
3. 不支持FULLTEXT类型的索引
4. 没有保存表的行数。当 SELECT COUNT(*) FROM TABLE时需要扫描全表。

4. MEMORY

概述：MEMORY是一类特殊的存储引擎。
MEMORY结构
1. 使用存储在内存中的数据来创建表；
2. 每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型；文件中只存储表的结构，而其数据文件都存储在内存中。
特点
- 利于数据的快速处理，提高整个表的效率。
- 只使用在相对较小的数据库表
注意
1. 服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。
2. 基于MEMORY的表的生命周期很短，一般是一次性的。
3. MEMORY用的很少。因为它是把数据存到内存中，如果内存出现异常就会影响数据
MEMORY默认使用哈希索引，其速度比使用B型树索引快，当然如果想用B型树索引，可以在创建索引时指定。

4.4 MySQL数据库引擎MyISAM和InnoDb对比总结

存储结构
- MyISAM：每个MyISAM在磁盘上存储成三个文件。
  1. 文件1：frm文件，存储表定义。文件名字以表的名字开始，扩展名为frm
  2. 文件2：数据文件。扩展名为.MYD (MYData)。
  3. 文件3：索引文件。扩展名是.MYI (MYIndex)。
- InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件）。InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
存储空间
- MyISAM：可被压缩，存储空间较小。支持三种存储格式：静态表、动态表、压缩表。
- InnoDB：需要更多的内存和存储。它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
故障恢复
- MyISAM：不能在表损坏后恢复数据。因此需要及时备份表数据。
- InnoDB：支持故障恢复。利用事务日志进行数据恢复，速度比较快。
事务支持
- MyISAM：不提供事务支持。它强调的是性能，每次查询具有原子性，因此其执行数度比InnoDB类型更快。
- InnoDB：提供事务支持，外部键等高级数据库功能。具有事务、回滚和崩溃修复能力的事务安全型表。
表锁差异
- MyISAM：只支持表级锁。select，update，delete，insert语句都会给表自动加锁。
- InnoDB：支持行级锁。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
全文索引
- MyISAM：支持FULLTEXT类型的全文索引
- InnoDB：不支持FULLTEXT类型的全文索引。但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
表主键
- MyISAM：允许没有任何索引和主键的表存在。索引都是保存行的地址。
- InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键。数据是主索引的一部分，附加索引保存的是主索引的值。
表的总行数
- MyISAM：保存了表的总行数。select count(*) from table; 会直接取出出该值。
- InnoDB：没有保存表的总行数，select count(*) from table;会遍历整个表。但是在加了wehre条件后，myisam和innodb处理的方式都一样。
CURD操作
- MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
  - 对于insert和update操作：写操作需要锁定整个表，效率低。
- InnoDB：如果你的数据执行大量的INSERT或UPDATE，应该使用InnoDB表
外键
- MyISAM：不支持
- InnoDB：支持

参考资料

聚集索引与非聚集索引
 MySQL索引实现
 漫画算法：什么是 B 树？
漫画算法：什么是 B+ 树？
mysql 数据库引擎
 Mysql常用的三种数据库引擎比较
 mysql数据库引擎Myisam和InnoDb对比
 什么是B+树？（详细图解）

zijikanwa

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库引擎

1 B-树和B+树1.1 B-树1.1.1 B-树的定义1.1.2 B-Tree的查找过程1.2 B+树1.2.1 B+树的定义1.2.2 B+树的示意图1.2.3 B-树 &amp; B+树2 内存和磁盘存取原理2.1 主存存取原理2.1.1 主存概述2.1.2 主存的内部结构2.1.3 主存的存取过程2.2 磁盘存取原理2.2.1 磁盘结构2....
复制链接

扫一扫