【MySQL】InnoDB体系结构之后台线程

InnoDB作为MySQL的默认的核心存储引擎，其具有高性能、高可用、高可扩展性的功能，被包含于所有的MySQL二进制发行版本中。了解InnoDB的体系结构对于深入理解MySQL会有很大的帮助。
先来看下InnoDB存储引擎的体系结构图：

从图中可见，InnoDB存储引擎由多个内存块组成一个大的内存池，主要负责如下工作：
1、维护所有进程/线程需要访问的多个内部数据结构。
2、缓存磁盘数据及修改过的数据，便于快速读取/写入（buffer/cache）。
3、还有一部分作为重做日志缓冲（redo log buffer）。

一、InnoDB后台线程
InnoDB是多线程的模型，其后台包含多个线程，分别负责处理不同的任务。
1、Master Thread
一个非常核心的主线程，主要负责以下功能：

Master Thread
缓冲池数据异步刷新到磁盘
保证数据的一致性（刷新脏页、合并插入缓冲、UNDO页回收）

2、IO Thread
为提高数据库写入性能，InnoDB大量采用AIO（Async IO），此线程主要负责这些IO请求的回调处理。
分别包含4个类型的IO Thread。
read_io_threads和write_io_threads目前分别使用以下两个参数进行设置：

IO Thread 线程类型	默认线程数
insert buffer thread	1
log thread	1
innodb_read_io_threads	4
innodb_write_io_threads	4

mysql> SHOW VARIABLES LIKE 'innodb_version';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| innodb_version | 5.7.18 |
+----------------+--------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_%io_threads';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4     |
+-------------------------+-------+
2 rows in set (0.00 sec)

可以通过以下命令来观察IO Thread

mysql> show engine innodb status\G
*************************** 1. row ***************************
 Type: InnoDB
 Name: 
Status: 
=====================================
2017-09-11 22:30:48 0x7ff4e2df7700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 123697 srv_idle
srv_master_thread log flush and writes: 123700
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:

3、Purge Thread
事务被提交后，其所使用的undo log可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。
从InnoDB1.2开始支持多个Purge Thread，目的是进一步加快UNDO页的回收，同时为了离散地读取UNDO页，进一步利用磁盘的随机读取性能。

mysql> show variables like 'innodb_purge_threads';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_purge_threads | 4 |
+----------------------+-------+
1 row in set (0.00 sec)

4、Page Cleaner Thread
在InnoDB 1.2.x版本中引人。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

mysql> show variables like 'innodb_page_cleaners';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_page_cleaners | 4 |
+----------------------+-------+
1 row in set (0.00 sec)

参考：《MySQL技术内幕：InnoDB存储引擎》