mysql笔记：16. InnoDB存储引擎

InnoDB是MySQL默认的存储引擎，它主要由三部分组成，分别是存储结构、内存结构和线程结构。

一、InnoDB的存储结构

1. 逻辑存储结构

InnoDB存储引擎的逻辑存储结构和Oracle大致相同，所有创建的表都存放在一个空间中，称为表空间（tablespace）。表空间又由段（segment）、区（extend）、页（page）组成。InnoDB存储引擎的逻辑存储结构如下：

1.1. 表空间

表空间可以看作是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下，InnoDB存储引擎有一个共享表空间ibdata1，用于存放撤销（Undo）信息、系统事务信息、二次写缓冲（Double Write Buffer）数据等。

# 查看表空间对应的物理文件
$ ll /usr/local/mysql/data/ibdata1

mysql> show variables like 'innodb_file_per_table';

如果启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间中。该参数默认启用。

启用innodb_file_per_table参数时，每张表的表空间内存放的只是数据、索引和插入缓冲的数据，而撤销信息、系统事务信息、二次写缓冲数据等还是存放在原来的共享表空间内。即使启用了innodb_file_per_table参数，共享表空间还是会不断增加大小。

1.2. 段

表空间由各个段组成。常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是用索引组织的（Index Organized）。因此，数据即索引，索引即数据。
与Oracle不同的是，InnoDB存储引擎对于段的管理是由引擎本身完成的。这和Oracle的自动段空间管理（ASSM）类似，没有手动段空间管理（MSSM）方式，这从一定程度上简化了DBA的管理。

并不是每个对象都有段。准确的说：表空间是由分散的页和段组成。

1.3. 区

区是由连续的页组成，是物理上连续分配的一段空间，每个区的大小固定是1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。

1.4. 页

InnoDB存储引擎的最小物理存储分配单位是页，默认大小是16KB。查看方式：

mysql> show variable like 'innodb_page_size';

常见的页类型有：

• 数据页（B-Tree Node）
• Undo页（Undo Log Page）
• 系统页（System Page）
• 事务数据页（Transaction System Page）
• 插入缓冲位图页（Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List）
• 未压缩的二进制大对象页（Uncompressed BLOB Page）
• 压缩的二进制大对象页（Compressed BLOB Page）

1.5. 行

InnoDB存储引擎是面向行的（row-oriented），也就是说数据的存放是按行进行。每个页存放的行记录也是有硬性定义的，最多允许存放16*1024/2-200行的记录，即7992行记录。

2. 物理存储结构

MySQL通过逻辑存储结构来管理物理存储结构，即管理磁盘上存储的各种文件。

2.1 数据文件

数据文件包括 .ibd 文件和 ibdata 文件，它们都是存放InnoDB数据的文件。之所以有两种文件来存放InnoDB数据（包括索引），是因为InnoDB的数据存储方式能够通过配置来决定使用共享表空间存储数据，还是使用独享表空间存储。

使用InnoDB存储引擎时，如果在配置文件中没有启用参数innodb_file_per_table，默认使用InnoDB存储引擎的表都将数据存在ibdata1文件中。如果开启了该参数，则表示每个InnoDB表将单独使用一个目录来存放表的数据文件。

2.2. 重做日志文件

重做日志（Redo log）文件是InnoDB存储引擎生成的日志，主要为了保证数据的可靠性和事务的持久性。每个文件默认大小是1GB，可由参数innodb_log_file_size参数配置，而文件存放路径由参数innodb_log_group_home_dir参数配置。

mysql> show variable like 'innodb_log_file_size';
mysql> show variable like 'innodb_log_group_home_dir';
mysql> show variable like 'innodb_log_files_in_group';

# Redo log文件与数据文件默认存放在同一个目录，例如，下面命令执行后可以看到两个ib_logfile开头的文件，它们就是log group中的Redo log file。
$ ll /usr/local/mysql/data/ib_logfile*

MySQL与Oracle一样都采用重做日志组的方式来管理Redo log文件。一个组内由多个大小完全相同的Redo log file组成，组内Redo log file的数量由innodb_log_files_in_group配置，默认是2。

2.3. 撤销日志文件

撤销日志（Undo log）文件中记录的是旧版本的数据，当用户对记录做了变更操作时就会产生undo记录。当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其要求的数据记录。
从MySQL 8.0版本开始，MySQL默认对undo进行了分离操作。即不需要在初始化中手动配置参数，默认会在MySQL数据目录下生成两个10MB的undo表空间文件undo_001和undo_002，并且可以在线增加和删除undo表空间文件进行动态扩容和收缩。

2.4. 参数文件

MySQL启动时，数据库先去读一个参数配置文件，用来寻找数据库的各种文件所在位置及指定某些初始化参数。在默认情况下，MySQL会按照一定的顺序在指定的位置进行读取，通过下面的语句可以查看读取参数文件的顺序：

mysql --help | grep my.cnf

MySQL参数有两类：

• 动态参数
  MySQL在运行的过程中可以对参数进行在线修改。可以通过命令set global或set session在数据库中完成。
• 静态参数
  无法在线修改参数

2.5. 错误日志

MySQL的错误日志文件对MySQL的启动、运行、关闭过程中出现的问题进行了记录。

mysql> show variables like 'log_error';

2.6. 二进制日志

二进制日志（binlog）文件记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作（因为这类操作不修改数据本身）。binlog的主要作用如下：

• 可以完成主从复制。在主服务器上把所有修改数据的操作记录到binlog中，通过网络发送给从服务器，从而达到主从同步的目的。
• 进行恢复操作。数据可以通过binlog文件，使用mysqlbinlog命令，实现基于时间点和位置的恢复操作。

binlog有三种模式：

• STATEMENT模式（SBR）
  每一条修改数据的SQL语句会记录到binlog中。优点是并不需要记录每一条SQL语句和每一行的数据变化，减少了binlog日志量，节约I/O，提高性能。缺点是在某些情况下会导致主从复制中的数据不一致。
• ROW模式（RBR）
  不记录每条SQL语句的上下文信息，仅需要记录哪条数据被修改了，修改成什么样了，而且不会出现某些特定情况下的存储过程、存储函数或者触发器的调用问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
• MIXED模式（MBR）
  以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

binlog与redo log相似，但又不同。

binlog	redo log
binlog是MySQL数据库的上层应用产生的，并且binlog不仅仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生binlog	redo log是InnoDB存储引擎产生的
binlog是逻辑日志，其对应的是SQL语句	InnoDB存储引擎层面的redo log是物理日志
binlog只在事务提交完成后一次写入	redo log在事务进行中不断地被写入，并且日志不是随事务提交的顺序进行写入的
binlog不是循环使用的，在写满或者重启之后，会生成新的binlog文件	redo log是循环使用的
binlog可以作为恢复数据使用，主从复制的搭建	redo log作为异常宕机或介质故障后的数据恢复使用

2.7. 慢查询日志

慢查询日志可以把超过参数long_query_time时间的所有SQL语句记录进来，帮助DBA人员优化有问题的SQL语句。

# 查看慢查询日志功能配置
mysql> show variables like '%slow_query%';

# 临时启用慢查询日志功能
mysql> set global slow_query_log='ON';
mysql> set session long_query_time=2;

如果需要在服务启动时启用慢查询日志，可以修改配置文件/etc/mysql.cnf，增加下面的内容：

[mysqld]
slow_query_log=ON
slow_query_log_file=./slow.log
long_query_time=2

2.8. 全量日志

全量日志（general log）会记录MySQL数据库所有操作的SQL语句，包含select和show。

# 查看全量日志功能配置
mysql> show variables like '%general_query%';

# 临时启用全量日志功能
mysql> set global general_log='ON';

2.9. 中继日志

主从复制中，中继日志是从服务器上一个很重要的文件。主从复制的工作原理分为以下3个步骤：

1. 主服务器把数据更改记录到二进制日志中
2. 从服务器把主服务器的二进制日志复制到自己的中继日志中
3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

mysql> show variables like '%relay%';

2.10. PID文件

MySQL实例启动时，会将自己的进程ID写入一个文件中，该文件为PID文件。它由参数pid_file配置，默认位于数据库目录下。

mysql> show variables like 'pid_file';

2.11. Socket文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字文件，由参数socket配置。

mysql> system cat /usr/local/mysql/data/mysql.pid

mysql> show variables like 'socket';

2.12. 表结构文件

在MySQL8以前的版本中，数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论采用哪种存储引擎，MySQL都有一个以frm为后缀名的文本文件，它记录了该表的表结构定义。frm可以存放视图的定义。

二、InnoDB的内存结构

MySQL内存可以分为系统全局区（SGA）和程序缓存区（PGA）。

mysql> show variables like '%buffer%';

1. SGA与PGA中的缓冲区

SGA缓冲区	作用
innodb_buffer_pool_size	缓存InnoDB表的数据、索引，以及数据字典等信息
innodb_log_buffer_size	事务在内存中的缓冲
query_cache	高速查询缓存，在生产环境建议关闭

PGA缓冲区	作用
innodb_sort_buffer_size	主要用于SQL语句在内存中的临时排序
join_buffer_size	表连接使用，用于优化索引。
read_buffer_size	表顺序扫描的缓冲，只能应用于MyISAM存储引擎
read_rnd_buffer_size	MySQL随机读取缓冲区大小，用于减少磁盘的随机访问

2. Buffer缓冲区的状态

页是InnoDB磁盘的最小单位，数据都存放在页中，对应到内存中就是一个个Buffer。Buffer的不同状态值含义也不一样。

Buffer状态	含义
freeBuffer	该Buffer未被使用
cleanBuffer	内存中的Buffer与磁盘中页的数据一致
dirtyBuffer	内存中的数据还未被刷新到磁盘，和磁盘中的数据不一致
Buffer由链表来管理。不同的Buffer状态，产生不同的链表，因此一共有3种不同的链表。
链表类型	作用
—	—
free list	把freeBuffer串联起来。如果使用时不够用，将从lru list和flush list中释放出freeBuffer
lru list	把最近少访问到的cleanBuffer串联起来
flush list	把dirtyBuffer串联起来，方便线程将数据刷新到磁盘

3. 内存的刷新机制

3.1 MySQL检查点的类型

MySQL采用检查点（checkpoint）的方式来刷新内存。在InnoDB存储引擎中，有两种checkpoint：

• Sharp Checkpoint（完全检查点）
  将内存中所有脏页全部写入磁盘就是完全检查点，比如数据库实例关闭时。
• Fuzzy Checkpoint（模糊检查点）
  将部分脏页写入磁盘就是模糊检查点，数据库实例运行过程产生的检查点基本上就是这种类型的检查点。

3.2 MySQL的模糊检查点

MySQL的模糊检查点会在以下4种条件下被触发。

1. 每隔1秒或者每隔10秒，将强制执行模糊检查点。这个过程是周期性异步的，不会阻塞用户查询，不影响业务；但每次执行检查点刷新的脏页量比较小，具体由参数innodb_io_capacity配置每次刷新脏页的数量，默认是200。
2. 当LRU队列的列表中空闲页不足时，将强制执行模糊检查点。可以通过innodb_lru_scan_depth配置LRU列表中可用页的数量，默认值是1024。如果LRU队列中不满足这一条件，InnoDB引擎将会移除LRU列表尾端的页。如果这些页中有脏数据，则执行模糊检查点。
3. 当重做日志redo log不够用时，将强制执行模糊检查点。重做日志有两个水位：

• 异步水位：75% x innodb的总大小
• 同步水位：90% x innodb大小
  当这两个事件中的任何一个发生时，都会记录到error log中。一旦error log出现这种日志提示，一定需要加大日志文件的大小。

4. 系统中整体脏页达到一定比例，将强制执行模糊检查点。使用参数innodb_dirty_page_pct来配置内存Buffer中脏数据的比例，默认值是90%。

三、InnoDB的线程结构

InnoDB的线程结构主要分为主线程结构、I/O线程结构和其他线程结构。

1. 主线程结构

后台线程中的主线程（master thread），优先级别最高。
主线程内部有4个循环：主循环（loop）、后台循环（background loop）、刷新循环（flush loop）、和暂定循环（suspend loop）。其中最主要的就是主循环，它分为每1秒操作和每10秒操作两种情况。

主循环操作	操作行为
每1秒操作	日志刷新到磁盘，即使事务还没提交。 刷新脏页到磁盘。 执行合并插入缓冲操作。 产生检查点。 删除无用的表缓存。 当前没有操作切换到后台循环
每10秒操作	日志刷新到磁盘，即使事务还没有提交。 刷新脏页到磁盘。 执行合并插入缓冲操作。 产生检查点。 删除无用的undo

2. I/O线程结构

MySQL有4大I/O线程。

I/O线程	线程作用
read thread	数据库的读请求线程，默认4个
write thread	数据库的写请求线程，默认4个
redo log thread	把日志缓存中的内容刷新到redo log文件中
change buffer thread	把插入缓存（change buffer）中的数据刷新到磁盘的数据文件中

mysql> show variables like 'innodb_read_io_threads';
mysql> show variables like 'innodb_write_io_threads';

3. 其他线程结构

InnoDB存储引擎中还有一些线程，作用也不同。

线程名称	线程作用
page clean thread	将脏数据写入磁盘，脏数据写入磁盘后相应的redo就可以覆盖，然后达到redo循环使用的目的。
purge thread	负责删除无用的undo页。由于DML语句操作都会生成undo，系统需要定期对undo页进行清理，这时就需要purge操作。默认线程数是4，最大可调整至32。
error monitor thread	负责数据库报错的线程
lock monitor thread	负责监控锁的线程

mysql> show variables like 'innodb_page_cleaners';
mysql> show variables like 'innodb_purge_threads';