【聊聊MySQL】二. InnoDB体系结构

发表于 更新于 分类于 ， ， 本文字数： 4.9k 阅读时长 ≈ 18 分钟

一.InnoDB存储引擎

自从 InnoDB 被 Heikki Tuuri 发明出来以后，可以说安装 MySQL 肯定默认的引擎就是设置 InnoDB，因为其功能强大，实用性强，基本很多业务需求不要太过纠结的话都可以使用 InnoDB 进行存储（当然现在看来，当你的表不需要事务的时候可以使用 MyISAM 来进行存储）。 InnoDB 相比其他的存储引擎，拥有以下几个特点：

  1. 支持完整 ACID 事务；
  2. 行锁设计，可提高并发；
  3. 支持 MVCC 可以说是数据行的版本控制，利用他来避免 幻读 的产生；
  4. 支持外键；
  5. 优秀的 B+ 索引。

    二.InnoDB体系架构

InnoDB 维护了类似于上图的一个内存块，内存块中存在一些线程，负责维护数据结构、缓存数据、刷写数据、redo log 等等。保证读取数据的快速，以及保证缓存数据的准确性。当数据库异常退出时还保证数据库能够恢复正常运行状态。

2.1 后台线程

MySQL 默认拥有一些后台线程，来做一些事情：

  1. 10 个 IO Thread：（8 个读写线程、1 个 insert buffer thread、1 个 log thread）
  2. 1 个 Master Thread：执行必要的操作
  3. …

我们可以通过 show engine innodb status\G; 来获取后台线程的一些状态：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-08-31 16:16:34 0x70000adcf000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
-----------------
BACKGROUND THREAD 【后台线程的执行情况】
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 58089 srv_idle
srv_master_thread log flush and writes: 58083
----------
SEMAPHORES
【这一块描述有多少线程在等待（自旋），以及大概需要等待锁的时间。
大量线程可能在等待硬盘IO或者连接，】
----------
OS WAIT ARRAY INFO: reservation count 1
OS WAIT ARRAY INFO: signal count 1
RW-shared spins 0, rounds 3, OS waits 1
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 3.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS【应用发生锁争抢情况】
------------
Trx id counter 3331
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479450789680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
【FileIO 线程】
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (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
242 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
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX【缓冲区信息，显示写入缓冲区的用量】
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG【日志信息：显示日志长度，多少被刷新到硬盘，以及最后日志记录的检查点】
---
Log sequence number 2625594
Log flushed up to   2625594
Pages flushed up to 2625594
Last checkpoint at  2625585
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY【缓冲内存用量，读取写入多少页的信息】
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 100382
Buffer pool size   8192
Free buffers       7945
Database pages     247
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 213, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 247, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS【显示主线程在干嘛，包括每种行类型的操作的性能以及数量】
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=99, Main thread ID=123145479176192, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 8
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.04 sec)

ERROR:
No query specified

2.2 内存使用

在 MySQL 中，使用内存可以分几个部分：

  1. 缓冲池（Buffer Pool）查询： show variables like 'innodb_buffer_pool_size'\G；
  2. 重做日志缓冲池（Redo Log Buffer）查询： show variables like 'innodb_log_buffer_size'\G；
  3. 额外内存池（Additional Memory Pool）查询： show variables like 'innodb_additional_mem_pool_size'\G；

缓冲池是占用内存最大的一块，通常用来存储查询的缓存以及存储修改的数据页，如果发生修改，会先修改这里面的数据，然后按照一定频率刷新到硬盘。每个 Buffer Frame 是 16k，所以按照上节中查询出来的数据：8192 * 16 / 1024 = 128k 说明当前分配了 128m 的缓冲池。 而上面查询到另外一个参数 Free buffers 则表示当前空闲的缓冲区，Database pages 则表示已经使用的缓冲区，所以当前两个值：7945 + 147 <= 8192 Modified db pages 则表示已经被修改的页的数量（其实为啥要翻译成脏页，是因为被修改了，跟硬盘不同步，所以脏了吗….脑洞好大） Old database pages 大概意思是 jvm 中的老年代分区，即老年代存放了多少页 Pages made young 19, not young 0 则表示移动到新生代的有多少页以及没有移动的有多少个。 怎么查看压力是否大，就看当前空闲的缓冲区还剩下多少。我拿个生产的来看看。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
...
---BUFFER POOL 7
Buffer pool size   24573
Free buffers       9549
Database pages     14578
Old database pages 5391
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10966, created 3612, written 82315
0.00 reads/s, 0.00 creates/s, 0.64 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14578, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...

383m 的缓冲区，空闲的有 149m，已经缓冲的有 227m，不存在被修改的页…好像压力很小（真的有点丢人）。 缓冲池还存储着其他的信息：插入缓冲（Insert Buffer）、自适应哈希索引（Adaptive Hash Index）、锁信息（Lock Info）、数据字典信息等等。不过，数据页 和 索引页 一般占用最大的容量。 日志缓冲一般存储重做日志（后面聊聊）然后按照一定频率（一般每一秒）刷新到硬盘。 额外内存池则是当某些操作需要大量内存的时候，会先从这里申请，如果不足则从缓冲池申请（这时候会使用 LRU 规则淘汰一些数据）所以当缓冲区占用比较大的时候（缓存比较多），则应该尽量的加大该区的容量。

三. 一个一直在循环的主线程

MySQL 存在着一个主要线程，循环的做着一些重要的功能，比如刷新缓存、刷新日志等。那现在就来看看这个 Master Thread 的主要事情。 首先，Master Thread 他总是在自循环的，类似于 Java 中启动一个线程，而 run 方法里面放的代码是：

1
2
3
while (true) {
    // 一些任务.
}

然后，这个循环里面还有个 for 循环来分割一些任务的执行频率：

1
2
3
4
5
6
7
8
while (true) {
    for (int i = 0; i < 10; i++) {
    // 一秒循环一次的任务.
    Thread.sleep(1000);
  }

  // 十秒循环一次的任务.
}

其实不慌，MySQL 也是通过 sleep 函数来实现这个停顿的，所以，准确的说，每一秒这个说法并不是绝对准确，而是会有点误差。

3.1 每一秒做的事情

那接下来我们分开来康康，每一秒都在做什么事情：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
volatile boolean noUserAct = false;
while (true) {
    for (int i = 0; i < 10; i++) {
    // 一秒循环一次的任务.

    // 刷新日志.
    flushLogs();

    if (当前一秒内的IO次数 < 5) {
        // 合并插入缓冲.
      mergeInsertBuf();
    }

    if (脏页比例 > 配置的阈值) {
        // 刷新数据页到硬盘.
      flushPageToDisk();
    }

    if (无用户活动) {
        // 切换到 background loop（因为Java没有goto我就是用标记来表示了）
      noUserAct = true;
    }

    Thread.sleep(1000);
  }

  // 十秒循环一次的任务.


    while (noUserAct) {
    // 后台活动
  }
}

一个一个来看看吧：

  1. 刷新日志：主要是 redo log （这是一个记录了一个事务中主要做了什么修改的日志），无论事务有没有提交，MySQL 都会在每秒钟将日志刷新到硬盘，所以即使是一个很大的事务，永远可以很快的进行提交；
  2. 合并插入缓冲：会根据当前一秒内的 IO 次数来决定，就是说不是每一次都会做合并插入缓冲区；这里我感觉得先小声BB插入缓冲区是什么：就是 MySQL 对插入的数据要更新 非聚簇索引 时，因为通常来说这种索引都不是唯一的，所以如果大量的更新，则需要大量的随机读硬盘，那么 MySQL 数据库会先把这部分插入的数据以及数据页，放在插入缓冲区，然后再以一定的频率写入硬盘，也就是这个 合并插入缓冲；
  3. 刷新数据页：上面说的是刷新 非聚簇索引，而现在则需要将真正的数据页刷新到硬盘，当然也不是每一秒都发生，而是脏页的比例 buf_get_modified_radio_pct 超过了配置文件的 innodb_max_dirty_pages_pct 时，才刷新 100 个脏页到硬盘。

3.2 每十秒做的任务

接下来继续康康每十秒的操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
volatile boolean noUserAct = false;
while (true) {
    for (int i = 0; i < 10; i++) {
    // 一秒循环一次的任务.

    // 刷新日志.
    flushLogs();

    if (当前一秒内的IO次数 < 5) {
        // 合并插入缓冲.
      mergeInsertBuf();
    }

    if (脏页比例 > 配置的阈值) {
        // 刷新数据页到硬盘.
      flushPageToDisk();
    }

    if (无用户活动) {
        // 切换到 background loop（因为Java没有goto我就是用标记来表示了）
      noUserAct = true;
    }

    Thread.sleep(1000);
  }

  // 十秒循环一次的任务.
  if (当前十秒内的IO次数 < 200) {
    // 刷新100个脏页.
    flush100DirtyPages();
  }
  // 刷新至多5个插入缓冲.
  merge5InsertBuf();
  // 刷新日志.
  flushLogs();
  // 删除无用的undo日志（至多20个）.
  delUndoLogUseLess();
  // 删除100或10个脏页.
  flush5or10DirtyPages();// 如果脏页比例 > 70% 刷新100个脏页，否则刷新10个脏页.
  // 产生检查点.
  createCheckPoint();

    while (noUserAct) {
    // 后台活动
  }
}

好了，跳过上面已经说过的 刷新脏页、刷新插入缓冲、刷新日志，我们来看看剩下的两个 删除无用的undo日志、插入检查点 无用的undo页：我们知道，MySQL 通过行版本控制默认事务的 幻读，那 undo页 指的是当用户发生 update delete 两个操作的时候，会产生一些”无用”（注意双引号）的行信息，但是由于其他事物读取的这些行，所以这些行还不是真正的无用，只有当所有事务都不需要这些版本的行信息的时候，才可以说这些行信息是 无用的undo页。那么删除 无用的undo页 指的就是删除这些无用的不同版本（但绝对不是当前版本）的行信息。 插入检查点：我们知道，MySQL 做什么事情都有日志，但是当日志很大的时候，不仅不利于 IO 也不利于存储空间的利用。那么插入检查点就相当于做了一个标记，标记我上面做的 刷新缓冲页 删除undo日志 到达了哪里，这样在 MySQL 发生问题重启后需要恢复数据的时候，只需要检查这个检查点后面的数据即可。这样，可以有效提高 MySQL 发生问题重启恢复数据的速度。 参考： mysql的checkpoint 官方文档

3.3 后台活动线程

最后来看看没有用户活动的时候，后台循环做的事情：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
// 后台线程完整伪代码
volatile boolean noUserAct = false;
while (true) {
    for (int i = 0; i < 10; i++) {
    // 一秒循环一次的任务.

    // 刷新日志.
    flushLogs();

    if (当前一秒内的IO次数 < 5) {
        // 合并插入缓冲.
      mergeInsertBuf();
    }

    if (脏页比例 > 配置的阈值) {
        // 刷新数据页到硬盘.
      flushPageToDisk();
    }

    if (无用户活动) {
        // 切换到 background loop（因为Java没有goto我就是用标记来表示了）
      noUserAct = true;
    }

    Thread.sleep(1000);
  }

  // 十秒循环一次的任务.
  if (当前十秒内的IO次数 < 200) {
    // 刷新100个脏页.
    flush100DirtyPages();
  }
  // 刷新至多5个插入缓冲.
  merge5InsertBuf();
  // 刷新日志.
  flushLogs();
  // 删除无用的undo日志（至多20个）.
  delUndoLogUseLess();
  // 删除100或10个脏页.
  flush5or10DirtyPages();// 如果脏页比例 > 70% 刷新100个脏页，否则刷新10个脏页.
  // 产生检查点.
  createCheckPoint();

    while (noUserAct) {
    // 后台活动

    // 删除所有无用的undo日志
    delUndoLogUseLess();
    // 刷新至多20个插入缓冲.
    merge20InsertBuf();

    // 如果没有其他需要做的事情了 回到主线程
    if (没有需要做的任务) {
      noUserAct = false
    } else {

      for (;;) {
        // 刷新100个脏页.
        flush100DirtyPages();
        if (脏页比例 < 配置的阈值) {
            break;
        }
      }

      noUserAct = false
      // 休眠 等待唤醒 继续主线程
      wait();

    }
  }
}

其实做的事情和之前的差不多，就是因为没有用户在使用了，所以线程变得十分狂野，能刷新就刷新，能删除就删除，不限制次数和数量的做这些任务。最后，休眠线程，等待其他事件的唤醒，重新开始后台线程的执行。

3.4 5.7Innodb的后台线程

从上面这些可以看到，我们的后台线程十分忙碌，而且刷新脏页的工作十分的多，导致后台线程会有很大的负载（就是为了刷脏总是拖了很多时间），所以在 MySQL 5.62 开始引入一个新的线程负责刷写脏数据这项伟大的任务，而后台线程则减轻了负担。5.7.4 开始，提升为多线程刷新线程。 参考：多线程刷脏

四. Double Write

MySQL 中有一个保证数据安全的特性：Double Write。 怎么理解这个玩意儿呢，就是说，当我们在修改一个数据页的时候（刚开始修改一部分，还没修改完成），这时候一个突然，你养的爱猫抓掉了你的电源线。这时候，你的这个页已经被损坏了，就算准备好了重做日志，也无法恢复之前的状态。 那怎么解决呢，这时候就需要在开始修改数据页之前，对这个页进行备份。当发生上面的不幸的时候，MySQL 如果判断到你的数据页被损坏了，则使用先前备份的数据页进行恢复，然后再使用重做日志对这个数据页进行修改。

PS：图片来自《MySQL技术内幕：InnoDB存储引擎》

当缓冲页需要刷新的时候，先通过脏页拷贝到内存中的 Doublewrite Buffer，然后内存中的 Doublewrite Buffer 再通过两次每次 1m 的大小写入到硬盘中的共享表空间（因为基本是连续硬盘写，所以效率损失不会特别大）。再将 Doublewrite Buffer 中的数据页写入各个表空间的文件中。 查看 Doublewrite Buffer 的情况可以通过下面的 SQL 来查看：

1
2
3
4
5
6
7
8
9
10
11
mysql> show global status like 'innodb_dblwr%'\G;
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
        Value: 1865308
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
        Value: 248718
2 rows in set (0.02 sec)

ERROR:
No query specified

OK，这是我们公司线上的数据库情况：一共写了 1865308 个页，实际写入次数 248718。远远小于 64:1 的比例，说明还是有压力的。

五. 自适应哈希索引

哈希思想，基本做程序的都不会陌生，即通过某种算法，将输入的对象/文件/其他一切东西转换成一串拥有固定规则的均匀的代码，然后使用这串代码来做定位或者其他用途，大大压缩了内存的使用。例如 Java 最典型的 HashMap。 而 MySQL 则会监控表上索引的查找，如果判断到访问的频率以及模式达到一定的阈值，则会为这些列建立 哈希索引。哈希索引 的简历是通过缓冲池中的 B+树 建立而来的，因此效率大大的好。 但是！注意 哈希索引 只能用在等值搜索的查询上，像 LIKE 范围查找 搜索用不了 哈希索引。 查询 哈希索引 情况：show engine innodb status\G;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 4985 merges
merged operations:
 insert 7415, delete mark 197, delete 110
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 796871, node heap has 691 buffer(s)
Hash table size 796871, node heap has 112 buffer(s)
Hash table size 796871, node heap has 307 buffer(s)
Hash table size 796871, node heap has 481 buffer(s)
Hash table size 796871, node heap has 463 buffer(s)
Hash table size 796871, node heap has 281 buffer(s)
Hash table size 796871, node heap has 410 buffer(s)
Hash table size 796871, node heap has 1493 buffer(s)
2741.97 hash searches/s, 42.04 non-hash searches/s

可以看到使用 哈希索引 以及不使用的效率。

六. MySQL_InnoDB启动关闭行为的配置

6.1 innodb_fast_shutdown

该参数影响着关闭数据库所做的行为，可以设置的值有 0 1 2。 0：代表关闭数据库时，需要昨晚所有的 full purge 和 merge insert buffer 操作，直接感受就是 MySQL 关闭会变得很慢。一般需要做软件升级的时候，才开启这个选项，使其做好一切关闭准备。 1：默认值，代表不需要昨晚上面选项的所有行为，但是会刷新脏页到硬盘。 2：不做任何事情，只记录日志文件，下次启动会执行恢复动作。

PS：如果非正常关闭数据库比如宕机，则需要将该参数值改成 2 让 MySQL 完整恢复数据再启动。

6.2 innodb_force_recovery

该值配置启动数据库时的恢复方式。默认值是 0，表示需要恢复上次关闭的所有日志。 但是当我们知道怎么恢复而且恢复需要很长时间的时候，我们可以把该值设置成 6 不让数据库进行恢复。 其他值： 0：默认恢复方式； 1：(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。 2：(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。 3：(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。 4：(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。 5：(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。 6：(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。 大于 0 的方式可以对标进行 CREATE SELECT DROP 而不允许 UPDATE INSERT DELETE

五.总结

大概了解 Inno_DB 存储引擎的架构以及后台的执行线程。