MySQL底层架构：游走在缓冲与磁盘之间2w字（详细的不能再详细）

 

前言

提起MySQL，其实网上已经有一大把教程了，为什么我还要写这篇文章呢，大概是因为网上很多网站都是比较零散，而且描述不够直观，不能系统对MySQL相关知识有一个系统的学习，导致不能形成知识体系。为此我撰写了这篇文章，试图让这些底层架构相关知识更加直观易懂：

• 尽量以图文的方式描述技术原理；
• 涉及到关键的技术，附加官网或者技术书籍来源，方便大家进一步扩展学习；
• 涉及到的背景知识尽可能做一个交代，比如讨论到log buffer的刷盘方式，延伸一下IO写磁盘相关知识点。

好了，MySQL从不会到精通系列马上就要开始了（看完之后还是不会的话..请忽略这句话）。

 

可能会有同学问：为啥不直接学更加先进的TiDB，或者是强大的OceanBase。

其实，MySQL作为老牌的应用场景广泛的关系型开源数据库，其底层架构是很值得我们学习的，吸收其设计精华，那么我们在平时的方案设计工作中也可以借鉴，如果项目中用的是MySQL，那么就能够把数据库用的更好了，了解了MySQL底层的执行原理，对于调优工作也是有莫大帮助的。本文我重点讲述MySQL底层架构，涉及到：

• 内存结构：buffer pool、log buffer、change buffer，buffer pool的页淘汰机制是怎样的；
• 磁盘结构：系统表空间、独立表空间、通用表空间、undo表空间、redo log；
• 以及IO相关底层原理、查询SQL执行流程、数据页结构和行结构描述、聚集索引和辅助索引的底层数据组织方式、MVCC多版本并发控制的底层实现原理，以及可重复读、读已提交是怎么通过MVCC实现的。

 

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

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

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

 

其实内存中的结构不太好直接观察到，不过磁盘的还是可以看到的，我们找到磁盘中MySQL的数据文件夹看看：

cd innodb_data_home_dir 查看MySQL 数据目录：

|- ib_buffer_pool // 保存缓冲池中页面的表空间ID和页面ID，用于重启恢复缓冲池 |- ib_logfile0 // redo log 磁盘文件1 |- ib_logfile1 // redo log 磁盘文件2，默认情况下，重做日志存在磁盘的这两个文件中，循环的方式写入重做日志 |- ibdata1 // 系统表空间文件 |- ibtmp1 // 默认临时表空间文件，可通过innodb_temp_data_file_path属性指定文件位置 |- mysql/ |- mysql-bin.000001 // bin log文件 |- mysql-bin.000001 // bin log文件 ... |- mysql-bin.index // bin log文件索引 |- mysqld.local.err // 错误日志 |- mysqld.local.pid // mysql进程号 |- performance_schema/ // performance_schema数据库 |- sys/ // sys数据库 |- test/ // 数据库文件夹 |- db.opt // test数据库配置文件，包含数据库字符集属性 |- t.frm // 数据表元数据文件，不管是使用独立表空间还是系统表空间，每个表都对应有一个 |- t.ibd // 数据库表独立表空间文件，如果使用的是独立表空间，则一个表对应一个ibd文件，否则保存在系统表空间文件中 

innodb_data_home_dir[1]

ib_buffer_pool[2]

ib_logfile0[3]

ibtmp1[4]

db.opt[5]

接下来我们逐一来介绍。

3.1、buffer pool

 

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

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

3.1.1、缓冲池LRU算法

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

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

 

该算法运行方式：

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

相关优化参数：

• innodb_old_blocks_pct：控制LRU列表中旧子列表的百分比，默认是37，也就是3/8，可选范围为5~95；
• innodb_old_blocks_time ：指定第一次访问页面后的时间窗口，该时间窗口内访问页面不会使其移动到LRU列表的最前面。默认是1000，也就是1秒。

innodb_old_blocks_time很重要，有了这1秒，对于全表扫描，由于是顺序扫描的，一般同一个数据页的数据都是在一秒内访问完成的，不会升级到新子列表中，一直在旧子列表淘汰数据，所以不会影响到新的列表的缓存。

3.1.2、关于磁盘IO的方式