1. MySQL逻辑架构
1. 1.1. MySQL的逻辑架构由哪些部分组成？
2. 1.2. 一条查询语句的执行流程是怎样的？
2. InnoDB存储引擎的磁盘结构
1. 2.1. InnoDB存储引擎的磁盘结构由哪些部分组成？
2. 2.2. 表空间有哪些部分组成？
3. InnoDB存储引擎的内存结构
4. 脏页
5. 参考文档

> MySQL 总结图解

InnoDB存储引擎的磁盘结构和内存结构是怎样的？

作者：徐梦旗，发布于：2024-01-14 17:20，字数：2.6k，预计阅读：9分钟

1. MySQL逻辑架构

1.1. MySQL的逻辑架构由哪些部分组成？

MySQL的逻辑架构由服务层和存储引擎层组成。其中服务层有以下几部分：

连接器：管理权限，验证连接。
查询缓存：命中缓存直接返回结果（在MySQL8.0版本移除）。
分析器：词法分析，语法分析。
优化器：生成执行计划，选择合适的索引。
执行器：调用存储引擎接口，返回结果。

其中存储引擎层有InnoDB，MyISAM和MEMORY等存储引擎。

可以通过show engines命令查看MySQL支持的存储引擎。

1.2. 一条查询语句的执行流程是怎样的？

客户端将一条查询语句发送给MySQL服务端时，会经过以下几个步骤：

客户端通过连接器与MySQL服务端建立连接；
判断查询缓存是否命中，如果命中则直接返回结果（在MySQL8.0版本移除），否则进入下一步；
由分析器对查询语句进行词法分析和语法分析；
由优化器生成查询语句的执行计划，并选择合适的索引；
由执行器调用存储引擎提供的接口，查询数据并返回结果给客户端。

可以通过show processlist命令查看当前建立的连接。

2. InnoDB存储引擎的磁盘结构

2.1. InnoDB存储引擎的磁盘结构由哪些部分组成？

InnoDB存储引擎的磁盘结构由表空间^[1]（Tablespace）和日志文件（Log）组成。其中表空间有以下几种：

系统表空间（System Tablespace）：用来存储数据字典，修改缓冲，回滚日志以及双写缓冲等。
独立表空间（File-Pre-Table Tablespace）：用来存储单个表的数据和索引。
通用表空间（General Tablespace）：用来平衡系统表空间和独立表空间。
回滚表空间（Undo Tablespace）：用来记录回滚日志。
临时表空间（Temporary Tablespace）：用来存储临时表。

其中日志文件有以下几种：

通用日志^[2]（General Log）：用来记录用户执行的所有SQL语句。
错误日志^[3]（Error Log）：用来记录数据库运行过程中的错误日志，方便排查数据库问题。
慢查询日志^[4]（Slow Query Log）：用来记录执行慢的SQL语句，方便排查SQL性能问题。
二进制日志^[5]（Binary Log）：用来记录DDL和DML对数据库做的变更，用于主从复制和备份。
重做日志^[6]（Redo Log）：用来记录对数据页的修改，提高修改的速度并保证安全性，采用循环写机制。

表空间相关参数：

可以通过datadir参数指定数据目录。

可以通过innodb_file_per_table参数开启独立表空间。

可以通过innodb_data_file_path参数指定系统表空间的路径。

可以通过innodb_temp_data_file_path参数指定临时表空间的路径。

日志文件相关参数：

可以通过general_log参数开启并指定通用日志的路径。

可以通过log_error参数开启并指定错误日志的路径。

可以通过slow_query_log参数开启慢查询日志。

可以通过long_query_time参数指定慢查询的耗时阈值。

可以通过log_queries_not_using_indexes参数开启慢查询日志记录不使用索引的查询。

可以通过log_bin参数开启并指定二进制日志的路径。

可以通过sync_binlog参数指定二进制日志的刷盘策略。

可以通过binlog_format参数指定二进制日志的格式。

可以通过innodb_redo_log_capacity参数指定重做日志的大小。

可以通过log_output参数指定日志格式。

2.2. 表空间有哪些部分组成？

表空间（Tablespace）：表空间由若干段组成，如数据段，索引段和回滚段等。
段（Segment）：段是逻辑上的概念，一个段包含多个区。
区（Extend）：区是数据库向操作系统申请资源的基本单位，大小为1MB，一个区包含64个页。
页（Page）：页是InnoDB存储引擎操作的基本单位，默认大小为16KB，一个页包含多个行。
行（Row）：行是数据存储的基本单位。

可以通过innodb_page_size参数指定页的大小。

3. InnoDB存储引擎的内存结构

3.1. InnoDB存储引擎的内存结构由哪些部分组成？

InnoDB存储引擎的内存结构由缓冲池（Buffer Pool）和日志缓冲（Log Buffer）组成。其中缓冲池有以下几个部分：

LRU列表。
修改缓冲（Change Buffer）。
自适应哈希索引（Adaptive Hash Index）等。

3.2. 缓冲池Buffer Pool的作用是什么？

缓冲池^[7]（Buffer Pool）用来加速对热点数据页的访问和修改，可以减少磁盘I/O，提高数据库的性能和响应速度。

从图中我们可以看到，当访问的数据页也不在缓冲池中时，会先通过磁盘I/O将表空间中的数据页加载到缓冲池中，再进行访问和修改；当访问的数据页在缓冲池中时，无需通过磁盘I/O访问表空间，直接操作内存来完成数据的访问和修改。

可以通过innodb_buffer_pool_size参数指定缓冲池的大小。

3.3. 修改缓冲Change Buffer的作用是什么？

修改缓冲^[8]（Change Buffer）用来加速对非唯一二级索引的修改，通过离散写改为顺序写的方式提高写入效率；在一定的条件下，对非唯一二级索引的修改会被merge到磁盘中：

当原始数据页被加载到缓冲池中时。
后台线程会定时触发merge操作。
当数据库实例正常关闭时。

修改缓冲适用于读少写多的场景，在读多写少的场景下对非唯一二级索引修改后再对原始数据页进行访问会触发修改缓冲的merge操作，使得离散写改为顺序写的收益并不明显。另外修改缓冲不适用于唯一二级索引的修改，因为修改唯一二级索引时需要将数据页加载到缓冲池中进行唯一约束校验。

可以通过innodb_change_buffering参数开启修改缓冲。

可以通过innodb_change_buffer_max_size参数指定修改缓冲大小占缓冲池大小的比例。

3.4. 自适应哈希索引Adaptive Hash Index的作用是什么？

自适应哈希索引^[9]（Adaptive Hash Index，AHI）会为频繁访问的索引创建哈希表，使得其可以快速定位到数据行，以提高查询效率。

可以通过innodb_adaptive_hash_index参数开启自适应哈希索引。

可以通过innodb_adaptive_hash_index_parts参数指定自适应哈希索引的分区个数。

3.5. 日志缓冲Log Buffer的作用是什么？

日志缓冲^[10]（Log Buffer）用来加速日志文件的写入，通过离散写改为顺序写的方式提高写入效率，并通过日志刷盘策略保证持久性。

可以通过innodb_log_buffer_size参数指定日志缓冲的大小。

可以通过innodb_flush_log_at_trx_commit参数指定日志刷盘策略。

4. 脏页

4.1. 什么是脏页？

缓冲池中的数据页被修改后和磁盘中的数据页不一致，称缓冲池中的数据页被称为脏页。

4.2. 如何提高修改的效率并保证安全性？

InnoDB存储引擎采用日志先行（Write Ahead Log，WAL）的机制，在进行修改时，会先在日志缓冲中记录日志，再对缓冲池中的数据页进行修改，并且为保证持久性，在事务提交前会进行日志刷盘。可以通过innodb_flush_log_at_trx_commit参数可以指定日志刷盘策略：

为0时，每秒会将日志flush到磁盘上，其性能最好，当数据库崩溃时会丢失1秒种的事务。
为1时，每次事务提交前会将日志flush刷新到磁盘上，其安全性最高，但性能较差。
为2时，每次事务提交前会将日志write到操作系统缓冲（OS Buffer）中，由操作系统决定flush的时机，当操作系统崩溃时会丢失1秒种的事务，是一种折中的策略。