InnoDB——线程

后台线程

InnoDB存储引擎是多线程的，因此后台有多个不同的后台线程，负责处理不同的任务

Master Thread

核心后台线程，主要负责将缓冲池中的数据异步刷新到磁盘上，保证数据的一致性，包括脏页的刷新，合并插入缓存（Insert BUffer），undo页回收等。

IO Thread

InnoDB存储引擎中大量使用AIO（Async IO）来处理写请求，可以极大的提高数据库性能。而IO Thread线程主要负责这些IO请求的回调（call back）处理。

IO Thread主要可以分为write，read以及insert buffer，在InnoDB1.0.x后可以使用innodb_read_io_threads和innodb_write_io_threads进行设置，IO Thread 0是insert buffer thread，IO Thread 1为log thread，根据参数 innodb_read_io_threads和innodb_write_io_threads 设置读写线程，并且读线程的ID总是小于写线程的ID。

查看方式如下：（例为MySQL8.0.15）

mysql> show variables like 'innodb_version';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| innodb_version | 8.0.15 |
+----------------+--------+
1 row in set (0.00 sec)
mysql> show engine innodb status\G
....
--------
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 (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1159 OS file reads, 226 OS file writes, 51 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

（例为MySQL5.7.31）

mysql> show variables like 'innodb_version';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| innodb_version | 5.7.31 |
+----------------+--------+
1 row in set (0.00 sec)
mysql> show engine innodb status \G
....
--------
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:
Pending flushes (fsync) log: 0; buffer pool: 0
244 OS file reads, 53 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

Purge Thread

事务被提交以后，其所使用的undolog可能就不再被需要了，因此需要使用Purge Thread 进行回收已经使用的undo页，在InnoDB1.1版本前，purge操作只能在InnoDB存储引擎中的Master Thread中进行，在InnoDB1.1以后purge操作可以在单独的线程中进行，来减轻master thread的工作压力，从而提高CPU的使用率和存储引擎的性能，用户可以在数据库的配置文件中添加如下命令来启动独立的purge thread：

[mysqld]
innodb_purge_threads=1

注意：在InnoDB1.1版本中，即使将innodb_purge_threads设置为大于1，InnoDB存储引擎在启动时也会将其设置为1，在InnoDB1.2版本开始，开始支持多个purge thread，这样可以加快undo页的回收，并且purge thread需要离散的去读undo页，这样可以进一步利用磁盘的随机读取性能。在MySQL8中默认为4个

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

Page Cleaner Thread

这个线程是在InnoDB1.2后产生的，其作用是将脏页刷新操作放入到单独的线程中来完成，同样这个动作之前是通过master thread实现的，这样做也是为了减轻master thread的工作压力和用户查询线程的阻塞，进一步提高InnoDB的性能。