InnoDB存储引擎的磁盘结构和内存结构是怎样的?
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秒种的事务,是一种折中的策略。
- 可以通过
innodb_flush_log_at_trx_commit
参数指定日志刷盘策略。
4.3. 脏页什么时候刷盘?
缓冲池中的脏页会通过检查点(Checkpoint)机制进行脏页刷盘[11],即将脏页刷新到表空间中对应的数据页中。检查点进行脏页刷盘有以下几种情况:
- 后台线程会定时触发脏页刷盘操作。
- 当缓冲池中的脏页过多超过指定比例时。
- 当重做日志被用完时。
- 当数据库实例正常关闭时。
当数据库实例故障时,内存中缓冲池数据丢失,在数据库实例重新启动时,会根据重做日志恢复数据页到缓冲池中,再经过检查点机制进行脏页刷盘。
- 可以通过
innodb_max_dirty_pages_pct
参数指定脏页占缓冲池的最大比例。- 可以通过
innodb_flush_neighbors
参数开启邻页刷盘。
4.4. 一条更新语句的执行流程是怎样的?
- 判断要更新的数据页是否在缓冲池中,如果不在需要先从表空间中加载数据页到缓冲池中;
- 记录回滚日志;
- 更新缓冲池中的数据页;
- 在日志缓冲中记录对数据页的变更;
- 在二进制日志缓冲中记录二进制日志;
- 提交事务前进行日志落盘;
- 由检查点机制进行脏页刷盘。
5. 参考文档
- 1.MySQL :: MySQL 8.0 Reference Manual :: 17.6.3 Tablespaces ↩
- 2.MySQL :: MySQL 8.0 Reference Manual :: 7.4.3 The General Query Log ↩
- 3.MySQL :: MySQL 8.0 Reference Manual :: 7.4.2 The Error Log ↩
- 4.MySQL :: MySQL 8.0 Reference Manual :: 7.4.5 The Slow Query Log ↩
- 5.MySQL :: MySQL 8.0 Reference Manual :: 7.4.4 The Binary Log ↩
- 6.MySQL :: MySQL 8.0 Reference Manual :: 17.6.5 Redo Log ↩
- 7.MySQL :: MySQL 8.0 Reference Manual :: 17.5.1 Buffer Pool ↩
- 8.MySQL :: MySQL 8.0 Reference Manual :: 17.5.2 Change Buffer ↩
- 9.MySQL :: MySQL 8.0 Reference Manual :: 17.5.3 Adaptive Hash Index ↩
- 10.MySQL :: MySQL 8.0 Reference Manual :: 17.5.4 Log Buffer ↩
- 11.MySQL :: MySQL 8.0 Reference Manual :: 17.8.3.5 Configuring Buffer Pool Flushing ↩