洞悉MySQL底层架构：游走在缓冲与磁盘之间

茗茗妮

于 2021-04-20 20:48:51 发布

阅读量350

点赞数

文章标签：数据库 mysql innodb

原文链接：https://blog.csdn.net/chai471793/article/details/107079985

版权

洞悉MySQL底层架构：游走在缓冲与磁盘之间

本文转载自：https://blog.csdn.net/chai471793/article/details/107079985，感谢大神

看完文本文，您将了解到：

整体架构：InnoDB存储架构是怎样的 (1、MySQL架构)
工作原理：查询语句的底层执行流程是怎样的 (2、查询SQL执行流程)
IO性能：文件IO操作写磁盘有哪几种方式，有什么IO优化方式 (1.2、关于磁盘IO的方式)
缓存：InnoDB缓存(buffer pool, log buffer)的刷新方式有哪些（3.1.2.2、innodb_flush_method）
缓存：log buffer是在什么时候写入到磁盘的（3.10.2、如何保证数据不丢失 - 其中第四步log buffer持久化到磁盘的时机为）
缓存：为什么redo log prepare状态也要写磁盘？（3.10.2、如何保证数据不丢失 - 为什么第二步redo log prepare状态也要写磁盘？）
缓存：脏页写盘一般发生在什么时候（3.10.2、如何保证数据不丢失 - 其中第五步：脏页刷新到磁盘的时机为）
缓存：为什么唯一索引的更新不可以借助change buffer（3.2、Change Buffer）
缓存：log buffer的日志刷盘控制参数innodb_flush_log_at_trx_commit对写性能有什么影响（3.4.1、配置参数）
缓存：buffer pool的LRU是如何实现的，为什么要这样实现（3.1.1、缓冲池LRU算法）
表存储：系统表空间的结构，MySQL InnoDB磁盘存储格式，各种表空间(系统表空间，独立表空间，通用表空间)的作用和优缺点是什么，ibdata、ibd、frm文件分别是干嘛的（3.5、表空间）
行字段存储：底层页和行的存储格式（3.6、InnoDB底层逻辑存储结构）
行字段存储：varchar，null底层是如何存储的，最大可用存储多大的长度（3.6.3.1、MySQL中varchar最大长度是多少）
行字段存储：行记录太长了，一页存不下，该怎么存储？（3.6.3.2、行记录超过页大小如何存储）
索引：数据库索引的组织方式是怎样的，明白为什么要采用B+树，而不是哈希表、二叉树或者B树（3.7、索引 - 为什么MySQL使用B+树）
索引：索引组织方式是怎样的，为什么大字段会影响表性能(查询性能，更新性能)（3.7、索引）
索引：覆盖索引、联合索引什么情况下会生效（3.7.2、辅助索引）
索引：什么是索引下推，索引下推减少了哪方面的开销？（3.7.2、辅助索引 - 索引条件下推）
索引：Change Buffer对二级索引DML语句有什么优化（3.2、Change Buffer）
数据完整性：MySQL是如何保证数据完整性的，redo log、undo log和buffer pool数据完整性的关键作用分别是什么（3.10.2、如何保证数据不丢失）
MVCC：MVCC底层是怎么实现的，可重复读和读已提交是怎么实现的（3.11.2、MVCC实现原理）
双写缓冲区有什么作用（3.9、Doublewrite Buffer）
Redo Log在一个事务中是在什么时候写入的？binlog和Redo Log有什么区别？（3.10.1、Redo Log在事务中的写入时机）

1、MySQL架构

如下图为MySQL架构涉及到的常用组件：

2、查询SQL执行流程

有如下表格：

我们执行以下sql：

select * from t_user where user_id=10000;

2.1、MySQL客户端与服务器建立连接

如下图，建立过程：

客户端通过mysql命令发起连接请求；
经过三次握手后与服务端建立TCP连接；
连接器接收到请求之后使用用户密码进行身份验证；
验证通过之后，获取用户的权限信息缓存起来，该连接后面都是基于该缓存中的权限执行sql；

对于Java应用程序来说，一般会把建立好的连接放入数据库连接池中进行复用，只要这个连接不关闭，就会一直在MySQL服务端保持着，可以通过show processlist命令查看，如下：

注意，这里有个Time，表示这个连接多久没有动静了，上面例子是656秒没有动静，默认地，如果超过8个小时还没有动静，连接器就会自动断开连接，可以通过wait_timeout参数进行控制。

2.2、执行SQL

如下图，执行sql：

服务端接收到客户端的查询sql之后，先尝试从查询缓存中查询该sql是否已经有缓存的结果了，如果有则直接返回结果，如果没有则执行下一步；
分析器拿到sql之后会尝试对sql语句进行词法分析和语法分析，校验语法的正确性，通过之后继续往下执行；
优化器拿到分析器的sql之后，开始继续解析sql，判断到需要走什么索引，根据实际情况重写sql，最终生成执行计划；
执行器根据执行计划执行sql，执行之前会先进行操作权限校验；然后根据表存储引擎调用对饮接口进行查询数据，这里的扫描行数就是指的接口返回的记录数，执行器拿到返回记录之后进一步加工，如本例子：执行器拿到select * from t_user where user_id=10000的所有记录，在依次判断user_name是不是等于"arthinking"，获取到匹配的记录。

3、InnoDB引擎架构

如下图，为存储引擎的架构：

3.1、buffer pool

buffer pool（缓冲池）是主内存中的一个区域，在InnoDB访问表数据和索引数据的时候，会顺便把对应的数据页缓存到缓冲池中。如果直接从缓冲池中直接读取数据将会加快处理速度。在专用服务器上，通常将80%左右的物理内存分配给缓冲池。

为了提高缓存管理效率，缓冲池把页面链接为列表，使用改进版的LRU算法将很少使用的数据从缓存中老化淘汰掉。

3.1.1、缓冲池LRU算法

通过使用改进版的LRU算法来管理缓冲池列表。

当需要把新页面存储到缓冲池中的时候，将淘汰最近最少使用的页面，并将新页面添加到旧子列表的头部。

该算法运行方式：

默认 3/8缓冲池用于旧子列表；
当新页面如缓冲池时，首先将其插入旧子列表头部；
重复访问旧子列表的页面，将使其移动至新子列表的头部；
随着数据库的运行，页面逐步移至列表尾部，缓冲池中未被方位的页面最终将被老化淘汰。

3.2、Change Buffer

change buffer是一种特殊的数据结构，当二级索引页(非唯一索引)不在缓冲池中时，它们会缓存这些更改。当页面通过其他读取操作加载到缓冲池中时，再将由INSERT，UPDATE或DELETE操作（DML）产生的change buffer合并到buffer pool的数据页中。

为什么唯一索引不可以使用chage buffer？

针对唯一索引，如果buffer pool不存在对应的数据页，还是需要先去磁盘加载数据页，才能判断记录是否重复，这一步避免不了。

而普通索引是非唯一的，插入的时候以相对随机的顺序发生，删除和更新也会影响索引树中不相邻的二级索引树，通过使用合并缓冲，避免了在磁盘产生大量的随机IO访问获取普通索引页。

问题

当有许多受影响的行和许多辅助索引要更新时，change buffer合并可能需要几个小时，在此期间，I/O会增加，可能会导致查询效率大大降低，即使在事务提交之后，或者服务器重启之后，change buffer合并操作也会继续发生。相关阅读：Section 14.22.2, “Forcing InnoDB Recovery”

3.3、自适应哈希索引

自适应哈希索引功能由innodb_adaptive_hash_index变量启用，或在服务器启动时由--skip-innodb-adaptive-hash-index禁用。

3.4、Log Buffer

log buffer(日志缓冲区)用于保存要写入磁盘上的log file(日志文件)的数据。日志缓存区的内容会定期刷新到磁盘。

日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为16MB。较大的日志缓冲区可以让大型事务在提交之前无需将redo log写入磁盘。

如果您有更新，插入或者删除多行的事务，尝试增大日志缓冲区的大小可以节省磁盘I/O。

3.4.1、配置参数

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 变量控制如何将日志缓冲区的内容写入并刷新到磁盘。

该参数控制是否严格存储ACID还是尝试获取更高的性能，可以通过该参数获取更好的性能，但是会导致在系统崩溃的过程中导致数据丢失。

可选参数：

0，事务提交之后，日志只记录到log buffer中，每秒写一次日志到缓存并刷新到磁盘，尚未刷新的日志可能会丢失；
1，要完全符合ACID，必须使用该值，表示日志在每次事务提交时写入缓存并刷新到磁盘；
2，每次事务提交之后，日志写到page cache，每秒刷一次到磁盘，尚未刷新的日志可能会丢失；

innodb_flush_log_at_timeout

innodb_flush_log_at_timeout 变量控制日志刷新频率。可让您将日志刷新频率设置为N秒（其中N为1 ... 2700，默认值为1）

为了保证数据不丢失，请执行以下操作：

如果启用了binlog，则设置：sync_binlog=1;innodb_flush_log_at_trx_commit=1;

配置效果如下图所示：

3.5、表空间

一个InnoDB表及其索引可以在建在系统表空间中，或者是在一个独立表空间中，或在通用表空间。

当innodb_file_per_table启用时，通常是将表存放在独立表空间中，这是默认配置；
当innodb_file_per_table禁用时，则会在系统表空间中创建表；
要在通用表空间中创建表，请使用 CREATE TABLE ... TABLESPACE语法。有关更多信息，请参见官方文档 14.6.3.3 General Tablespaces。

表空间概览图：

表空间涉及的文件

相关文件默认在磁盘中的innodb_data_home_dir目录下：

frm文件

创建一个InnoDB表时，MySQL 在数据库目录中创建一个.frm文件。frm文件包含MySQL表的元数据(如表定义)。每个InnoDB表都有一个.frm文件。

与其他MySQL存储引擎不同， InnoDB它还在系统表空间内的自身内部数据字典中编码有关表的信息。MySQL删除表或数据库时，将删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。

因此，在InnoDB中，您不能仅通过移动.frm 文件来移动表。有关移动InnoDB 表的信息，请参见官方文档14.6.1.4 Moving or Copying InnoDB Tables。

ibd文件

对于在独立表空间创建的表，还会在数据库目录中生成一个 .ibd表空间文件。

在通用表空间中创建的表在现有的常规表空间 .ibd文件中创建。常规表空间文件可以在MySQL数据目录内部或外部创建。有关更多信息，请参见官方文档14.6.3.3 General Tablespaces。

ibdata文件

系统表空间文件，在 InnoDB系统表空间中创建的表在ibdata中创建。

3.6、InnoDB底层逻辑存储结构

在介绍索引之前，我们有必要了解一下InnoDB底层的逻辑存储结构，因为索引是基于这个底层逻辑存储结构创建的。截止到目前，我们所展示的都仅仅是物理磁盘中的逻辑视图，接下来我们就来看看底层的视图。

3.6.1、ibd文件组织结构

现在我们打开一个表空间ibd文件，看看里面都是如何组织数据的？

如下图，表空间由段(segment)、区(extent)、页(page)组成。

InnoDB最小的存储单位是页，默认每个页大小是16k。

而InnoDB存储引擎是面向行的(row-oriented)，数据按行进行存放，每个页规定最多允许存放的行数=16k/2 - 200，即7992行。

段：如数据段、索引段、回滚段等。InnoDB存储引擎是B+树索引组织的，所以数据即索引，索引即数据。B+树的叶子节点存储的都是数据段的数据。

3.6.2、数据页结构[14]

名称占用空间描述Fil Header38 byte页的基本信息，如所属表空间，上一页和下一页指针。Page Header56 byte数据页专有的相关信息Infimun + Supremum26 byte两个虚拟的行记录，用于限定记录的边界User Records动态分配实际存储的行记录内容Free Space动态调整尚未使用的页空间Page Directory动态调整页中某些记录的相对位置Fil Trailer8 byte校验页是否完整

关于Infimun和Supremum：首次创建索引时，InnoDB会在根页面中自动设置一个最小记录和一个最高记录，并且永远不会删除它们。最低记录和最高记录可以视为索引页开销的一部分。最初，它们都存在于根页面上，但是随着索引的增长，最低记录将存在于第一或最低叶子页上，最高记录将出现在最后或最大关键字页上。

3.6.3、行记录结构描述[15]

先来讲讲Compact行记录格式，Compact是MySQL5.0引入的，设计目标是高效的存储数据，让一个页能够存放更多的数据，从而实现更快的B+树查找。

名称描述变长字段长度列表字段大小最多用2个字节表示，也就是最多限制长度：2^16=65535个字节；字段大小小于255字节，则用1个字节表示；NULL标志位记录该行哪些位置的字段是null值记录头信息记录头信息信息，固定占用5个字节列1数据实际的列数据，NULL不占用该部分的空间列2数据...

记录头用于将连续的记录链接在一起，并用于行级锁定。

每行数据除了用户定义的列外，还有两个隐藏列：

6个字节的事务ID列；
7个字节的回滚指针列；
如果InnoDB没有指定主键，还会增加一个6个字节的rowid列；

而记录头信息表[16]含如下内容：

名称大小(bit)描述()1未知()1未知deleted_flag1该行是否已被删除min_rec_flag1如果该记录是预定义的最小记录，则为1n_owned4该记录拥有的记录数heap_no13索引堆中该条记录的排序号record_type3记录类型：000 普通，001 B+树节点指针，010 Infimum，011 Supremum，1xx 保留next_record16指向页中下一条记录

更详细的页结构参考官网：22.2 InnoDB Page Structure

更详细的行结构参考官网：22.1 InnoDB Record Structure

更详细的行格式参考官网：14.11 InnoDB Row Formats

根据以上格式，可以得出数据页内的记录组织方式：

3.6.3.1、MySQL中varchar最大长度是多少

上面表格描述我们知道，一个字段最长限制是65535个字节，这是存储长度的限制。

而MySQL中对存储是有限制的，具体参考：8.4.7 Limits on Table Column Count and Row Size

MySQL对每个表有4096列的硬限制，但是对于给定的表，有效最大值可能会更少；
MySQL表的每行行最大限制为65,535字节，这是逻辑的限制；实际存储的时候，表的物理最大行大小略小于页面的一半。如果一行的长度少于一页的一半，则所有行都将存储在本地页面内。如果它超过一页的一半，那么将选择可变长度列用于外部页外存储，直到该行大小控制在半页之内为止。

而实际能够存储的字符是跟编码有关的。

背景知识：

MySQL 4.0版本以下，varchar(10)，代表10个字节，如果存放UTF8汉字，那么只能存3个（每个汉字3字节）；

MySQL 5.0版本以上，varchar(10)，指的是10个字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放10个，最大大小是65532字节；

因此，Mysql5根据编码不同,存储大小也不同。

那么假设我们使用的是utf8编码，那么每个字符最多占用3个字节，也就是最多定义varchar(21845)个字符，如果是ascii编码，一个字符相当于一个字节，最多定义varchar(65535)个字符，下面我们验证下。

我们尝试创建一个这样的字段：

CREATE TABLE `t10` ( `id` int(11) NOT NULL, `a` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=ascii ROW_FORMAT=Compact; alter table t10 add `str` varchar(21845) DEFAULT NULL; alter table t10 add `str` varchar(65535) DEFAULT NULL;

发现提示这个错误：

mysql> alter table t10 add `str` varchar(65535) DEFAULT NULL; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

原因是按照以上的行格式介绍，变长字段长度列表记录也需要占用空间，占用2个字节，另外这里是允许为空字段，在8位之内，所以NULL标志位占用1个字节，所以我们总共可以存储的字符数是：

65535 - 2 - 2 - 4 - 4=65534

其中 -2 个字节表示变长字段列表，-1表示NULL标志位，两个-4表示两个int类型字段占用大小

所以实际上能够容纳的varchar大小为：65524，我们验证下：

3.6.3.2、行记录超过页大小如何存储

MySQL表的内部表示具有65,535字节的最大行大小限制。InnoDB 对于4KB，8KB，16KB和32KB innodb_page_size 设置，表的最大行大小（适用于本地存储在数据库页面内的数据）略小于页面的一半。如果包含可变长度列的InnoDB 行超过最大行大小，那么将选择可变长度列用于外部页外存储。

可变长度列由于太长而无法容纳在B树页面上，这个时候会把可变长度列存储在单独分配的磁盘页面上，这些页面称为溢出页面，这些列称为页外列。页外列的值存储在由溢出页面构成的单链接列表中。

InnoDB存储引擎支持四种行格式：REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。不同的行格式，对溢出的阈值和处理方式有所区别，详细参考：14.11 InnoDB Row Formats。

COMPACT行格式处理方式

使用COMPACT行格式的表将前768个字节的变长列值（VARCHAR， VARBINARY和 BLOB和 TEXT类型）存储在B树节点内的索引记录中，其余的存储在溢出页上。

如果列的值等于或小于768个字节，则不使用溢出页，因此可以节省一些I / O。

如果查过了768个字节，那么会按照如下方式进行存储：

DYNAMIC行格式处理方式

DYNAMIC行格式提供与COMPACT行格式相同的存储特性，但改进了超长可变长度列的存储能力和支持大索引键前缀。

InnoDB 可以完全在页外存储过长的可变长度列值（针对 VARCHAR， VARBINARY和 BLOB和 TEXT类型），而聚集索引记录仅包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段。

表中大字段引发的问题

如果一个表中有过多的可变长度大字段，导致一行记录太长，而整个时候使用的是COMPACT行格式，那么就可能会插入数据报错。

如，页面大小事16k，根据前面描述我们知道，MySQL限制一页最少要存储两行数据，如果很多可变长度大字段，在使用COMPACT的情况下，仍然会把大字段的前面768个字节存在索引页中，可以算出最多支持的大字段：1024 * 16 / 2 / 768 = 10.67，那么超过10个可变长度大字段就会插入失败了。

这个时候可以把row format改为：DYNAMIC。

3.7、索引

前面我们了解了InnoDB底层的存储结构，即：以B+树的方式组织数据页。另外了解了数据页中的数据行的存储方式。

而构建B+树索引的时候必须要选定一个或者多个字段作为索引的值，如果索引选择的是主键，那么我们就称为聚集索引，否则就是二级索引。

为什么MySQL使用B+树？

哈希表虽然可以提供O(1)的单行数据操作性能，但却不能很好的支持排序和范围查找，会导致全表扫描；B树可以再非叶子节点存储数据，但是这可能会导致查询连续数据的时候增加更多的I/O操作；而B+树数据都存放在叶子节点，叶子节点通过指针相互连接，可以减少顺序遍历时产生的额外随机I/O

更新详细解释: 为什么 MySQL 使用 B+ 树[17]

3.7.1、聚集索引

了解到上面的底层逻辑存储结构之后，我们进一步来看看InnoDB是怎么通过B+树来组织存储数据的。

首先来介绍下聚集索引。

聚集索引

主键索引的InnoDB术语。

下面我们创建一张测试表，并插入数据，来构造一颗B+树：

CREATE TABLE t20 ( id int NOT NULL, a int NOT NULL, b int, c int, PRIMARY KEY (`id`) ) ENGINE=InnoDB; insert into t20 values(20, 1, 2, 1); insert into t20 values(40, 1, 2, 5); insert into t20 values(30, 3, 2, 4); insert into t20 values(50, 3, 6, 2); insert into t20 values(10, 1, 1, 1);

可以看到，虽然我们是id乱序插入的，但是插入之后查出来的确是排序好的：

这个排序就是B+索引树构建的。

我们可以通过这个在线的动态演示工具来看看B+树的构造过程，最终结果如下：

实际存放在数据库中的模型因页面大小不一样而有所不同，这里为了简化模型，我们按照B+树的通用模型来解释数据的存储结构。

类似的，我们的数据也是这种组织形式的，该B+树中，我们以主键为索引进行构建，并且把完整的记录存到对应的页下面：

其中蓝色的是索引页，橙色的是数据页。

每个页的大小默认为16k，如果插入新的数据行，这个时候就要申请新的数据页了，然后挪动部分数据过去，重新调整B+树，这个过程称为页分裂，这个过程会影响性能。

相反的，如果InnoDB索引页的填充因子下降到之下MERGE_THRESHOLD，默认情况下为50％（如果未指定），则InnoDB尝试收缩索引树以释放页面。

自增主键的插入是递增顺序插入的，每次添加记录都是追加的，不涉及到记录的挪动，不会触发叶子节点的分裂，而一般业务字段做主键，往往都不是有序插入的，写成本比较高，所以我们更倾向于使用自增字段作为主键。

聚集索引注意事项

当在表上面定义了PRIMARY KEY之后，InnoDB会把它作为聚集索引。为此，为你的每个表定义一个PRIMARY KEY。如果没有唯一并且非空的字段或者一组列，那么请添加一个自增列；
如果您没有为表定义PRIMARY KEY，则MySQL会找到第一个不带null值的UNIQUE索引，并其用作聚集索引；
如果表没有PRIMARY KEY或没有合适的UNIQUE索引，则InnoDB 内部会生成一个隐藏的聚集索引GEN_CLUST_INDEX，作为行ID，行ID是一个6字节的字段，随着数据的插入而自增。

聚集索引查找

根据索引进行查找id=50的记录，如下图，沿着B+树一直往下寻找，最终找到第四页，然后把该页加载到buffer pool中，在缓存中遍历对比查找，由于里面的行记录是顺序组织的，所以很快就可以定位到记录了。

3.7.2、辅助索引

除了聚集索引之外的所有索引都称为辅助索引(二级索引)。在InnoDB中，辅助索引中每个记录都包含该行的主键列以及为辅助索引指定的列。

在辅助索引中查找到记录，可以得到记录的主键索引ID，然后可以通过这个主键索引ID去聚集索引中搜索具体的记录，这个过程称为回表操作。

如果主键较长，则辅助索引将使用更多空间，因此具有短的主键是有利的。

下面我们给刚刚的表添加一个组合联合索引

-- 添加多一个字段 alter table t20 add column d varchar(20) not null default ''; -- 添加一个联合索引 alter table t20 add index idx_abc(a, b, c);

添加之后组合索引B+树如下，其中索引key为abc三个字段的组合，索引存储的记录为主键ID：

覆盖索引(Using index)

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要回表去查询聚集索引中的记录，从而减少大量的IO操作。下面的查询既是用到了覆盖索引 idx_abc：

select a, b from t20 where a > 2;

执行结果如下：

可以发现，Extra这一列提示Using index，使用到了覆盖索引，扫描的行数为2。注意：这里的扫描行数指的是MySQL执行器从引擎取到两条记录，引擎内部可能会遍历到多条记录进行条件比较。

最左匹配原则

由于InnoDB索引式B+树构建的，因此可以利用索引的“最左前缀”来定位记录。

也就是说，不仅仅是用到索引的全部定义字段会走索引，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左n个字段。

索引条件下推(Using index condition)

索引条件下推 Index Condition Pushdown (ICP)，是针对MySQL使用索引从表中检索行的情况的一种优化。

为什么叫下推呢，就是在满足要求的情况下，把索引的条件丢给存储引擎去判断，而不是把完整的记录传回MySQL Server层去判断。

ICP（index condition pushdown）是mysql利用索引（二级索引）元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的where条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从server层下推到storage engine层。storage engine使用索引过过滤不相关的数据，仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能在storage engine层进行，而不是返回所有数据给server层，然后后再根据where条件进行过滤。使用ICP（mysql 5.6版本以前）和没有使用ICP的数据访问和提取过程如下（插图来在MariaDB Blog）：

优化器没有使用ICP时，数据访问和提取的过程如下：

1) 当storage engine读取下一行时，首先读取索引元组（index tuple），然后使用索引元组在基表中（base table）定位和读取整行数据。

2) sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。

3) 执行1），直到最后一行数据。

优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。数据访问和提取过程如下：

1) storage engine从索引中读取下一条索引元组。

2) storage engine使用索引元组评估下推的索引条件。如果没有满足wehere条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据。

3) 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。

4) server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。

而使用ICP时，如果where条件的一部分能够通过使用索引中的字段进行评估，那么mysql server把这部分where条件下推到storage engine（存储引擎层）。存储引擎通过索引元组的索引列数据过滤不满足下推索引条件的数据行。

索引条件下推的意思就是筛选字段在索引中的where条件从server层下推到storage engine层，这样可以在存储引擎层过滤数据。由此可见，ICP可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。

注意一下ICP的使用条件：

只能用于二级索引(secondary index)。
explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。

ICP的开启优化功能与关闭

MySQL5.6可以通过设置optimizer_switch([global|session],dynamic)变量开启或者关闭index_condition_push优化功能，默认开启。

mysql > set optimizer_switch=’index_condition_pushdown=on|off’

用explain查看执行计划时，如果执行计划中的Extra信息为“using index condition”，表示优化器使用的index condition pushdown。

3.8、InnoDB Data Directory

InnoDB数据字典(Data Directory)存放于系统表空间中，主要包含元数据，用于追踪表、索引、表字段等信息。由于历史的原因，InnoDB数据字典中的元数据与.frm文件中的元数据重复了。

3.9、Doublewrite Buffer

双写缓冲区(Doublewrite Buffer)是一个存储区，是InnoDB在tablespace上的128个页（2个区），大小是2MB[18

操作系统写文件是以4KB为单位的，那么每写一个InnoDB的page到磁盘上，操作系统需要写4个块。如果写入4个块的过程中出现系统崩溃，那么会导致16K的数据只有一部分写是成功的，这种情况下就是partial page write（部分页写入）问题。

InnoDB这个时候是没法通过redo log来恢复的，因为这个时候页面的Fil Trailer（Fil Trailer 主要存放FIL_PAGE_END_LSN，主要包含页面校验和以及最后的事务）中的数据是有问题的。

为此，每当InnoDB将页面写入到数据文件中的适当位置之前，都会首先将其写入双写缓冲区。只有将缓冲区安全地刷新到磁盘后，InnoDB才会将页面写入最终的数据文件。

如果在页面写入过程中发生操作系统或者mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从双写缓冲区中找到页面的完好副本用于恢复。恢复时，InnoDB扫描双写缓冲区，并为缓冲区中的每个有效页面检查数据文件中的页面是否完整。

如果系统表空间文件（“ ibdata文件 ”）位于支持原子写的Fusion-io设备上，则自动禁用双写缓冲，并且将Fusion-io原子写用于所有数据文件。

3.10、Redo Log

重做日志(Redo Log)主要适用于数据库的崩溃恢复，用于实现数据的完整性。

重做日志由两部分组成：

binlog主要是用于主从同步和数据恢复，Redo Log主要是用于实现事务数据的完整性，让InnoDB具有不会丢失数据的能力，又称为crash-safe。

binlog日志的两种记录形式：

混合日志记录默认情况下使用基于语句的日志记录，但根据需要自动切换到基于行的日志记录。

3.10.1.2、Redo Log在事务中的写入时机

简单的介绍完binlog，我们再来看看Redo Log的写入流程。

假设我们这里执行一条sql

update t20 set a=10 where id=1;

执行流程如下：

重做日志缓冲区 Log Buffer；
重做日志文件，重做日志文件在磁盘上由两个名为ib_logfile0和ib_logfile1的物理文件表示。
为了实现数据完整性，在脏页刷新到磁盘之前，必须先把重做日志写入到磁盘。除了数据页，聚集索引、辅助索引以及Undo Log都需要记录重做日志。

3.10.1、Redo Log在事务中的写入时机

在事务中，除了写Redo log，还需要写binlog，为此，我们先来简单介绍下binlog。

3.10.1.1、binlog

全写：Binary Log，二进制log。二进制日志是一组日志文件。其中包含有关对MySQL服务器实例进行的数据修改的信息。

Redo Log是InnoDB引擎特有的，而binlog是MySQL的Server层实现的，所有引擎都可以使用。

Redo Log的文件是循环写的，空间会用完，binlog日志是追加写的，不会覆盖以前的日志。

binlog主要的目的：
主从同步，主服务器将二进制日志中包含的事件发送到从服务器，从服务器执行这些事件，以保持和主服务器相同的数据更改；
某些数据恢复操作需要使用二进制日志，还原到某一个备份点。
基于SQL的日志记录：事件包含产生数据更改(插入，新增，删除)的SQL语句；
基于行的日志记录：时间描述对单个行的更改。

3.10.2、如何保证数据不丢失

前面我们介绍Log Buffer的时候，提到过，为了保证数据不丢失，我们需要执行以下操作：

如果启用了binlog，则设置：sync_binlog=1; innodb_flush_log_at_trx_commit=1;

sync_binlog=0：表示每次提交事务都只 write，不 fsync；sync_binlog=1：表示每次提交事务都会执行 fsync；sync_binlog=N(N>1) ：表示每次提交事务都 write，但累积 N 个事务后才 fsync。

这两个的作用相当于在上面的流程最后一步，提交事务接口返回Server层之前，把binlog cache和log buffer都fsync到磁盘中了，这样就保证了数据的落盘，不会丢失，即使奔溃了，也可以通过binlog和redo log恢复数据相关流程如下：

在磁盘和内存中的处理流程如下面编号所示：

其中第四步log buffer持久化到磁盘的时机为：

log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程主动写盘；
InnoDB后台有个线程，每隔1秒会把log buffer刷到磁盘；
由于log buffer是所有线程共享的，当其他事务线程提交时也会导致已写入log buffer但还未提交的事务的redo log一起刷新到磁盘

其中第五步：脏页刷新到磁盘的时机为：

系统内存不足，需要淘汰脏页的时候，要把脏页同步回磁盘；
MySQL空闲的时候；
MySQL正常关闭的时候，会把脏页flush到磁盘。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是 75%。

为什么第二步 redo log prepare状态也要写磁盘？

因为这里先写了，才能确保在把binlog写到磁盘后崩溃，能够恢复数据：如果判断到redo log是prepare状态，那么查看是否存XID对应的binlog，如果存在，则表示事务成功提交，需要用prepare状态的redo log进行恢复。

这样即使崩溃了，也可以通过redo log来进行恢复了，恢复流程如下：

Redo Log是循环写的，如下图:

writepos记录了当前写的位置，一边写位置一边往前推进，当writepos与checkpoint重叠的时候就表示logfile写满了，绿色部分表示是空闲的空间，红色部分是写了redo log的空间；

checkpoint处标识了当前的LSN，每当系统崩溃重启，都会从当前checkpoint这个位置执行重做日志，根据重做日志逐个确认数据页是否没问题，有问题就通过redo log进行修复。

LSN Log Sequence Number的缩写。代表日志序列号。在InnoDB中，LSN占用8个字节，单调递增，LSN的含义：

重做日志写入的总量；checkpoint的位置；页的版本；

除了重做日志中有LSN，每个页的头部也是有存储了该页的LSN，我们前面介绍页面格式的时候有介绍过。

该页中LSN表示该页最后刷新时LSN的大小。[19]

3.11、Undo Logs

上面说的redo log记录了事务的行为，可以通过其对页进行重做操作，但是食物有时候需要进行回滚，这时候就需要undo log了。[20]

关于Undo Log的存储：InnoDB中有回滚段(rollback segment)，每个回滚段记录1024个undo log segment，在每个undo log segment段中进行申请undo页。系统表空间偏移量为5的页记录了所有的rollback segment header所在的页。

3.11.1、undo log的格式

根据行为不同分为两种：

insert undo log

insert undo log：只对事务本身可见，所以insert undo log在事务提交后可直接删除，无需执行purge操作；

insert undo log主要记录了：

next记录下一个undo log的位置type_cmplundo的类型：insert or update*undo_no记录事务的ID*table_id记录表对象*len1, col1记录列和值*len2, col2记录列和值......start记录undo log的开始位置

假设在事务1001中，执行以下sql，t20的table_id为10：

insert into t20(id, a, b, c, d) values(12, 2, 3, 1, "init")

那么对应会生成一条undo log：

update undo log

update undo log：执行update或者delete会产生undo log，会影响已存在的记录，为了实现MVCC(后边介绍)，update undo log不能再事务提交时立刻删除，需要将事务提交时放入到history list上，等待purge线程进行最后的删除操作。

update undo log主要记录了：

next记录下一个undo log的位置type_cmplundo的类型：insert or update*undo_noundo日志编号*table_id记录表对象info_bits*DATA_TRX_ID事务的ID*DATA_ROLL_PTR回滚指针*len1, i_col1n_unique_index*len2, i_col2...n_update_fields以下是update vector信息，表示update操作导致发送改变的列*pos1, *len1, u_old_col1*pos2, *len2, u_old_col2...n_bytes_below*pos, *len, col1*pos, *len, col2...start记录undo log的开始位置

假设在事务1002中，执行以下sql，t20的table_id为10：

update t20 set d="update1" where id=60;

那么对应会生成一条undo log：

如上图，每回退应用一个undo log，就回退一个版本，这就是MVCC(Multi versioning concurrency control)的实现原理。

下面我们在执行一个delete sql：

delete from t20 where id=60;

对应的undo log变为如下：

如上图，实际的行记录不会立刻删除，而是在行记录头信息记录了一个deleted_flag标志位。最终会在purge线程purge undo log的时候进行实际的删除操作，这个时候undo log也会清理掉。

3.11.2、MVCC实现原理

如上图所示，MySQL只会有一个行记录，但是会把每次执行的sql导致行记录的变动，通过undo log的形式记录起来，undo log通过回滚指针连接在一起，这样我们想回溯某一个版本的时候，就可以应用undo log，回到对应的版本视图了。

我们知道InnoDB是支持RC(Read Commit)和RR(Repeatable Read)事务隔离级别的，而这个是通过一致性视图(consistent read view)实现的。

一个事务开启瞬间，所有活跃的事务(未提交)构成了一个视图数组，InnoDB就是通过这个视图数组来判断行数据是否需要undo到指定的版本：

RR事务隔离级别

假设我们使用了RR事务隔离级别。我们看个例子：

如下图，假设id=60的记录a=1

事务C启动的瞬间，活跃的事务如下图黄色部分所示：

也就是对于事务A、事务B、事务C，他们能够看到的数据只有是行记录中的最大事务IDDATA_TRX_ID<=11的，如果大于，那么只能通过undo进行回滚了。如果TRX_ID=当前事务id，也可以看到，即看到自己的改动。

另外有一个需要注意的：

在RR隔离级别下，当事务更新事务的时候，只能用当前读来获取最新的版本数据来更新，如果当前记录的行锁被其他事务占用，就需要进入所等待；
在RC隔离级别下，每个语句执行都会计算出新的一致性视图。

所以我们分析上面的例子的执行流程：

事务C执行update，执行当前读，拿到的a=1，然后+1，最终a=2，同时添加一个TRX_ID=11的undo log；
事务B执行select，使用快照读，记录的DATA_TRX_ID > 11，所以需要通过undo log回滚到DATA_TRX_ID=11的版本，所以拿到的a是1；
事务B执行update，需要使用当前读，拿到最新的记录，a=2，然后加1，最终a=3；
事务B执行select，拿到当前最新的版本，为自己的事务id，所以得到a=3；
事务A执行select，使用快照读，记录的DATA_TRX_ID > 11，所以需要通过undo log回滚到DATA_TRX_ID=11的版本，所以拿到的a是1。
如果是RC隔离级别，执行select的时候会计算出新的视图，新的视图能够看到的最大事务ID=14，由于事务B还没提交，事务C提交了，所以可以得到a=2：

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

explain执行计划包含的信息

其中最重要的字段为：id、type、key、rows、Extra

各字段详解

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
1、id相同：执行顺序由上至下

2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

1、SIMPLE：简单的select查询，查询中不包含子查询或者union
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
3、SUBQUERY：在select 或 where列表中包含了子查询
4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
6、UNION RESULT：从union表获取结果的select

type

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，最好能达到ref

1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const

3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

注意：ALL全表扫描的表记录最少的表如t1表

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

7、ALL：Full Table Scan，遍历全表以找到匹配的行

possible_keys

查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅出现在key列表中
key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

ref

显示索引的那一列被使用了，如果可能，是一个常量const。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

不适合在其他字段中显示，但是十分重要的额外信息

1、Using filesort ：
mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”
2、Using temporary：
使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by

3、Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作
覆盖索引（Covering Index）：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注意：
a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

4、Using where ：
使用了where过滤

5、Using join buffer ：
使用了链接缓存

6、Impossible WHERE：
where子句的值总是false，不能用来获取任何元祖

7、select tables optimized away：
在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

8、distinct：
优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作

综合Case

执行顺序
1（id = 4）、【select id, name from t2】：select_type 为union，说明id=4的select是union里面的第二个select。

2（id = 3）、【select id, name from t1 where address = ‘11’】：因为是在from语句中包含的子查询所以被标记为DERIVED（衍生），where address = ‘11’ 通过复合索引idx_name_email_address就能检索到，所以type为index。

3（id = 2）、【select id from t3】：因为是在select中包含的子查询所以被标记为SUBQUERY。

4（id = 1）、【select d1.name, … d2 from … d1】：select_type为PRIMARY表示该查询为最外层查询，table列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的select结果）。

5（id = NULL）、【 … union … 】：代表从union的临时表中读取行的阶段，table列的 “union 1, 4”表示用id=1 和 id=4 的select结果进行union操作。

注意：ALL全表扫描的表记录最少的表如t1表

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

茗茗妮

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
洞悉MySQL底层架构：游走在缓冲与磁盘之间

洞悉MySQL底层架构：游走在缓冲与磁盘之间本文转载自：https://blog.csdn.net/chai471793/article/details/107079985，感谢大神看完文本文，您将了解到：整体架构：InnoDB存储架构是怎样的 (1、MySQL架构) 工作原理：查询语句的底层执行流程是怎样的 (2、查询SQL执行流程) IO性能：文件IO操作写磁盘有哪几种方式，有什么IO优化方式 (1.2、关于磁盘IO的方式) 缓存：InnoDB缓存(buffer pool, log b
复制链接

扫一扫