目录
数据库与实例
一、思维导图
二、笔记
1. 区分数据库与实例
数据库(Database)：物理操作系统文件和其他形式的文件类型的集合。（数据库 = 文件集合）实例(Instance)：MySQL 数据库由后台线程以及一个共享内存区组成。其中后台线程可以共享共享内存区 （实例 = 线程 + 一个共享内存区）
数据库实例才是真正用于操作数据库文件的。
正常情况下：一个数据库对应一个数据库实例
集群情况下：一个数据库对应若干个数据库实例
MySQL 是一个单进程多线程架构的数据库
2. 查看 MySQL 实例
MySQL 数据库实例在 Linux 下表现为一个进程
下列中进程号为 21350 的进程，即是已经启动的 MySQL 数据库实例
root@gongruiyang:~# ps -ef | grep mysql
root 21193 955 0 13:59 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 21350 21193 0 13:59 pts/1 00:00:02 /usr/sbin/mysqld
--basedir=/usr
--datadir=/var/lib/mysql
--plugin-dir=/usr/lib/mysql/plugin
--user=mysql
--skip-grant-tables
--log-error=/var/log/mysql/error.log
--pid-file=gongruiyang.pid
root 21658 21426 0 14:17 pts/0 00:00:00 grep --color=auto mysql
- basedir：根目录地址
- datadir：数据库（文件）所在路径
3. 配置文件读取顺序
当启动 MySQL 数据库实例时，MySQL会去读取配置文件，根据配置文件的参数来启动数据库实例
- Linux下读取配置文件顺序：
/etc/my.cnf->/etc/mysql/my.cnf->~/.my.cnf
root@gongruiyang:/etc/mysql# mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
- Windows下读取配置文件顺序：
C:\WINDOWS\my.ini-> C:\WINDOWS\my.cnf -> C:\my.ini ->C:\my.cnf->C:\Program Files\MySQL\MySQL Server 5.7\my.ini->C:\Program Files\MySQL\MySQL Server 5.7\my.cnf
C:\Users\1111>mysql --help
Default options are read from the following files in the given order:
C:\WINDOWS\my.ini
C:\WINDOWS\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.7\my.ini
C:\Program Files\MySQL\MySQL Server 5.7\my.cnf
InnoDB体系架构
一、思维导图
1. 后台线程
2. InnoDB 内存划分
3. 内存管理
二、笔记
1. 图解InnoDB体系架构
- InnoDB体系架构由三部分组成：后台线程 + 内存池 + 文件集合
- 后台线程：Master Thread、IO Thread、Purge Thread、Page Cleaner Thread
- 内存池：若干个内存块
- 文件集合：组成数据库
2. 后台线程
2.1 Master Thread
作用：负责将缓冲池中的数据异步刷新到磁盘中，保证数据的一致性，包括：脏页的刷新、合并插入缓冲、undo页的回收等
2.2 IO Thread
作用：用来对数据库文件做 IO 操作的线程
InnoDB 存储引擎中大量使用了 AIO 来处理写 IO 请求，其中的 IO Thread 的工作主要是负责这些 IO 请求的回调
AIO：异步非阻塞式IO，无需额外的线程轮询所有 IO 操作状态是否改变，若状态改变，系统会通知对应的线程来处理
IO Thread一共有四种：insert buffer thread、log thread、read thread、write thread
- 查看 Thread
MySQL [(none)]> show variables like 'innodb_version'\G
*************************** 1. row ***************************
Variable_name: innodb_version
Value: 5.7.31
1 row in set, 1 warning (0.00 sec)
MySQL [(none)]> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-11-08 22:17:22 0x5b6c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (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
349 OS file reads, 56 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
由此可知，在 5.7.X 版本下，一共有 10 个 IO Thread，分别是一个 insert buffer thread 和 一个 log thread 和 四个 read thread 和 四个 write thread
MySQL [(none)]> show variables like 'innodb_read_io_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
Value: 4
1 row in set, 1 warning (0.00 sec)
MySQL [(none)]> show variables like 'innodb_write_io_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_write_io_threads
Value: 4
1 row in set, 1 warning (0.00 sec)
2.3 Purge Thread
作用：在事务提交之后，其所使用的 undolog 可能不再需要，此时就需要 Purge Thread 来回收已经使用并分配的 undo 页
在 InnoDB 1.1 版本之前，回收操作仅在 Master Thread 中完成，之后，为了减轻 Master Thread 的工作，进而提高 CPU 的使用率以及提升存储引擎性能，将回收操作独立到单独的线程中进行，便出现了 purge thread
- 查看 purge Thread 线程数量
MySQL [(none)]> show variables like 'innodb_purge_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_purge_threads
Value: 4
2.4 Page Cleaner Thread
作用：用于脏页的刷新操作
本来脏页的刷新操作也是在 Master Thread 中完成的，为了减轻 Master Thread 的工作而独立到单独的线程中去。
- 查看 Page Cleaner Thread 线程的数量
MySQL [(none)]> show variables like 'innodb_page_cleaners'\G;
*************************** 1. row ***************************
Variable_name: innodb_page_cleaners
Value: 1
3. 内存
3.1 缓冲池
3.1.1 缓冲池简介
为什么需要缓冲池？
原因：由于 CPU 的 IO 频率与磁盘的 IO 频率之间差距较大，需要内存的频率来填补中间的频率差距，先将磁盘中的记录读取到位于内存上的缓冲池中，以备 CPU 使用
InnoDB 存储引擎是基于磁盘存储的，并将其中的数据记录按照页的方式进行管理，因此将其视为基于磁盘的数据库系统
缓冲池是一块内存区域，缓冲池中的数据记录是按照页的方式进行管理的
在数据库中进行
读取页的操作：首先将从磁盘中读到的页存放在缓冲池中，下一次再读取相同的页时，先判断改页是否存在于缓冲池中，若在，称改页在缓冲池中被命中，直接读取该页，否则，读取磁盘上的页。
在数据库中进行修改页的操作：首先修改位于缓冲池中的页，然后通过 Checkpoint 的机制刷新回磁盘。
由此可见，缓冲池的大小越大，数据库的整体性能应该是越好的。故缓冲池的大小对于数据库性能来说是非常重要的。
缓冲池中缓存的数据页类型由：索引页、数据页、undo 页、插入缓冲页、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等
- 图解InnoDB内存数据对象
3.1.2 缓冲池相关变量
- 缓冲池实例（innodb_buffer_pool_instances）
从 InnoDB 1.0.X 版本开始，允许有多个缓冲池实例，每个页根据哈希值平均分配到不同的缓冲池实例中去。好处是减少数据库内部资源竞争，增加数据库的并发处理性能。
mysql> show variables like 'innodb_buffer_pool_instances'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_instances
Value: 1
1 row in set, 1 warning (0.01 sec)
查看各个缓冲池状态（位于 information_schema 架构表innodb_buffer_pool_stats来观察）
mysql> select pool_id, pool_size, free_buffers, database_pages
-> from information_schema.innodb_buffer_pool_stats\G;
*************************** 1. row ***************************
pool_id: 0
pool_size: 512
free_buffers: 223
database_pages: 289
1 row in set (0.00 sec)
- 缓冲池单个实例大小（innodb_buffer_pool_size）
MySQL 5.7默认设置的缓冲池大小为 8MB
mysql> show variables like 'innodb_buffer_pool_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 8388608
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@innodb_buffer_pool_size/1024/1024;
+-------------------------------------+
| @@innodb_buffer_pool_size/1024/1024 |
+-------------------------------------+
| 8.00000000 |
+-------------------------------------+
1 row in set (0.00 sec)
3.2 LRU / Free / Flush List
3.2.1 LRU List
InnoDB 存储引擎是通过 LRU 算法来对缓冲池这个内存区域进行管理的。
思路是将最频繁使用的页放在 LRU 列表的前端，最少使用的页放在 LRU 列表的尾端，当缓冲池不能存放新读到的页时，将首先释放LRU列表的尾端的页
在 InnoDB 存储引擎中，缓冲池的页大小默认为 16KB，虽然是使用 LRU 算法进行页管理，但是与传统的 LRU 又稍有不同，在 InnoDB 存储引擎中，将新读取到的页并不是放在 LRU 列表的最前端，而是放在 midpoint 的位置上，在默认配置下，这个 midpoint 的位置在LRU 列表的 5/8 处，该位置可由变量innodb_old_blocks_pct控制。
midpoint 是将冷链和热链链接起来的连接点，是冷热数据页分界线。
一般为了保护热区数据页不被刷出内存（刷出去的概率降低），可以将 midpoint 的位置设置在百分之20的位置（即innodb_old_blocks_pct = 20）
为什么不将新的页放在 LRU List 的首部，而是放在 midpoint 位置呢？
原因：可能新插入进来的页并非是热点数据，仅仅是当前SQL需要访问到的数据页，若放在 LUR 列表的首部，可能会将真正的热点数据页给置换出去。
将数据页放到 midpoint 位置后，多久才将该页置换到 LRU 的热端呢？
答案：由 innodb_old_block_time 参数控制，即当该页经过 1s 未被刷出内存，就调入热链首部
page made young 操作：页从 old 部分加入到 new 部分
page not made young 操作：由于 innodb_old_block_time 还未达到，导致页仍然位于 old 部分
- 通过 information_schema 架构中表
innodb_buffer_pool_stats来观察各个缓冲池实例的运行状态
mysql> select pool_id, hit_rate, pages_made_young, pages_not_made_young
-> from information_schema.innodb_buffer_pool_stats\G;
*************************** 1. row ***************************
pool_id: 0
hit_rate: 0
pages_made_young: 0
pages_not_made_young: 0
1 row in set (0.00 sec)
- 通过 information_schema 架构中表innodb_buffer_page_lru 来观察LRU List中的数据页
mysql> select table_name, space, page_number, page_type
-> from information_schema.innodb_buffer_page_lru
-> where space = 57\G;
*************************** 1. row ***************************
table_name: `capital_m`.`tb_account`
space: 57
page_number: 3
page_type: INDEX
*************************** 2. row ***************************
table_name: NULL
space: 57
page_number: 1
page_type: IBUF_BITMAP
2 rows in set (0.00 sec)
LRU List 相关变量
- innodb_old_block_time ：1000ms = 1s
mysql> show variables like 'innodb_old_blocks_time'\G;
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_time
Value: 1000
1 row in set, 1 warning (0.00 sec)
- midpoint（innodb_old_blocks_pct）
mysql> show variables like 'innodb_old_blocks_pct'\G;
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_pct
Value: 37
1 row in set, 1 warning (0.00 sec)
- 页大小（innodb_page_size）
mysql> show variables like 'innodb_page_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_page_size
Value: 16384
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@innodb_page_size/1024;
+-------------------------+
| @@innodb_page_size/1024 |
+-------------------------+
| 16.0000 |
+-------------------------+
1 row in set (0.00 sec)
3.2.2 Free List
LRU List 是用来管理已经读取的页，但是当数据库刚刚启动时，没有任何页被读取时，LRU List 是空的，这时页都被存放在 Free List 中，当要读取数据页时，会将该页从 Free List 中删除，再增加到 LRU List 中去，以维持页的守恒。
查看 Free List 使用情况和状态：show engine innodb status\G;
free buffers表示 Free List
database pages表示 LRU List
modified db pages表示脏页的数量
Pages made young表示数据页从冷链到热链的累积量
not young表示数据页仍然保持在冷链的累积量
non-youngs/s表示每秒有多少次not young操作，若该值很高，表示有很高频率的物理读写
youngs/s表示每秒有多少次Pages made young操作，若该值很高，说明冷链很短或者innodb_old_blocks_time的值很小
Buffer pool hit rate表示缓冲池命中率，若该值大于95%表示缓冲池运行良好，否则，可能是由于全表扫描引起的 LRU List 污染的情况导致
unzip_LRU表示被压缩的页的数量，即将 16KB 的页压缩为1KB、2KB、4KB、8KB，对于非16KB的页由 unzip_LRU List来管理，LRU中的页包含了 unzip_LRU列表中的所有页
unzip_LRU是怎么样从缓存池中分配内存的呢？
对于大小不同的页，分别放到对应大小的 unzip_LRU List 中，通过伙伴算法进行内存的分配，例如需要从缓冲区申请页为4KB的大小，过程如下：
- 检查 4KB 的 unzip_LRU 列表，看看是否有可用的空闲页，若有则直接使用，
- 若没有，则检查 8KB 的 unzip_LRU 列表，若能获得一个空闲的 8KB 的页，则拆分成 2 个 4KB 的页存放至 4KB 的unzip_LRU 列表
- 若不能得到，则从 LRU 列表中申请到一个 16KB 的页，将其拆分成一个 8KB 的页和两个 4KB 的页，分别存放至对应的 unzip_LRU 列表中
查看 unzip_LRU 列表中的页
mysql> select *
-> from information_schema.innodb_buffer_page_lru
-> where compressed_size <> 0;
Empty set (0.00 sec)
3.2.3 Flush List
在 LRU 列表中的页被修改后，称改页为脏页，即缓冲池中的页上的数据与磁盘上对应的页上的数据产生了不一致。
Flush List 的作用就是将这些脏页进行组织管理。脏页既存在于 LRU 列表中，也存在与 Flush 列表中。
- 查看脏页信息
mysql> select *
-> from information_schema.innodb_buffer_page_lru
-> where oldest_modification > 0\G;
Empty set (0.00 sec)
3.3 重做日志缓冲区
InnoDB 存储引擎首先将重做日志信息写入重做日志缓冲区，再以一定的频率将日志信息从缓冲区中刷入重做日志文件中。
重做日志缓冲区的大小一般设置为一秒钟内产生日志量最大值即可，因为每一秒钟都会进行刷新至文件的操作。
触发刷新的条件有以下三种：
- Master Thread 每一秒钟都会将 redo log buffer 中的数据刷新至 redo log file 中
- 每个事务提交时都会将 redo log buffer 中的数据刷新至 redo log file 中
- 当 redo log buffer 剩余空间小 1/2 时就会将 redo log buffer 中的数据刷新至 redo log file 中
- 查看 redo log buffer 大小：1MB
mysql> show variables like 'innodb_log_buffer_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_log_buffer_size
Value: 1048576
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@innodb_log_buffer_size/1024/1024;
+------------------------------------+
| @@innodb_log_buffer_size/1024/1024 |
+------------------------------------+
| 1.00000000 |
+------------------------------------+
1 row in set (0.00 sec)
3.4 额外的内存池
作用：这些额外的内存池用于给数据结构体动态分配内存，其作用类似于堆内存，当这些额外的内存池被结构体申请耗尽后，会从缓冲池中进行申请。
例如：缓冲池中的帧缓冲及对应的缓冲控制对象，他们的数据结构的内存申请区域都是从额外内存池中申请的。
由此可知，当把缓冲池的内存设定的较大时，额外的内存池的大小也应该相应增加。
Checkpoint机制
一、思维导图
二、笔记
1. 脏页的产生与处理
缓冲池是用于平衡 CPU 和磁盘之间的速度差的，故，当页的操作也是在缓冲池中进行的，当一个DML语句发生后，例如 Update 或 Delete 语句改变了页中的数据记录，此时，页中的数据记录与磁盘上面的数据记录出现了不一致，为了保持这种一致性，数据库需要将新版本的数据页刷新至磁盘上。
思考问题：
- 如果每一次数据页都发生了改变都进行一次磁盘的读写，这样会导致大量的磁盘IO，给数据库造成极大的压力，导致数据库性能变差，怎么办？
- 如果在将数据页写入磁盘的过程中，数据库异常关闭，导致内存中的数据页丢失，那么数据就无法被恢复了，怎么办？
为了避免以上两种情况的出现，InnoDB 采取 Write Ahead Log 策略
2. Write Ahead Log 策略
Write Ahead Log 策略：即当事务提交的时候，先将改动写入重做日志（redo log）中，再修改页数据，再以一定的频率将脏页刷新至磁盘上。
由于改动被记录在日志中，所以有效的避免了数据丢失的问题，redo log是事务特性ACID中D(持久性)的实现方式
思考问题：
- 当出现宕机的时候，我们应该从重做日志的哪个地方进行恢复嘞？直接将所有的重做日志恢复一遍嘛？
显然将所有重做日志都恢复一遍这样是不可取的，如果重做日志内容非常的多，这要就会导致数据库的恢复时间过长，InnoDB中有一个名为 checkpoint的技术来解决该问题
3. Checkpoint
checkpoint标志着redo log最新写入位置.
-
当数据库发生宕机时，数据库并不需要重新把所有的
redo log做一遍，因为checkpoint之前的页都已经刷新回磁盘了，只需要将checkpoint之后的重做日志进行恢复即可，这样大大缩短了数据库恢复时间。 -
当数据库中的缓冲池不够用时，根据
LRU算法会置换最近最少使用的页，若该页是脏页，则会强制执行checkpoint，将脏页刷新至磁盘中
重做日志被设计成可以循环使用的，并不是让其无限增大，新的日志会覆盖以往不再需要的日志，不再需要是指数据库宕机恢复不需要的那部分日志，如果没有不再需要的日志用来覆盖，那么必须强制执行产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置，以这样的方式产生一部分不再需要的日志以供覆盖。
4. Log Sequence Number
4.1 LSN概念
对于 InnoDB 存储引擎而言，是通过 LSN ( Log Sequence Number ) 来标记数据状态的版本的，LSN是一个8字节的数字
- 每个页有LSN，重做日志有LSN，Checkpoint也有LSN
MySQL [(none)]> show engine innodb status\G;
---
LOG
---
Log sequence number 4093975
Log flushed up to 4093975
Pages flushed up to 4093975
Last checkpoint at 4093966
0 pending log flushes, 0 pending chkp writes
12 log i/o's done, 0.00 log i/o's/second
Log sequence number：指当前缓冲区上的 redo log buffer 中的 LSN
Log flushed up to：指刷磁盘上的 redo log file 中的 LSN
Pages flushed up to：指已经刷到磁盘数据页上的 LSN
Last checkpoint at：上一次检查点的 LSN
4.2 LSN 变化解析
- 事务提交 LSN 变化顺序
- 详解事务执行过程中的 LSN 流转变化
事务起始点：所有的 LSN 都初始化为 1
①：经过一个 update 语句，同时更新 data_page_in_buffer_lsn 和 redo_log_in_buffer_lsn 两个LSN 为 2
②：经过一个 delete 语句，并且此时距离事务起始点经过了一秒触发重做日志刷盘操作，同时更新 data_page_in_buffer_lsn 和 redo_log_in_buffer_lsn 和 redo_log_on_disk_lsn 三个LSN为3
③：经过一个 update 语句，同时更新 data_page_in_buffer_lsn 和 redo_log_in_buffer_lsn 两个LSN 为 4
④：此时出现 Checkpoint 机制，将所有 LSN 都更新至最新状态 4
⑤：此时距离事务起始点经过了两秒触发重做日志刷盘操作，由于刚刚经过Checkpoint刷盘，所有LSN都是最新状态，故无需做出任何改变
⑥：经过一个 insert 语句，同时更新 data_page_in_buffer_lsn 和 redo_log_in_buffer_lsn 两个LSN 为 5
⑦：此节点事务提交结束，事务提交动作，会触发日志刷盘操作，但是不会触发数据刷盘操作，故更新 redo_log_on_disk_lsn 为 5
⑧：此时出现 Checkpoint 机制，将所有 LSN 都更新至最新状态 5
5. Checkpoint分类
5.1 Sharp Checkpoint
Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘
由参数 innodb_fast_shutdown 控制
MySQL [(none)]> show variables like 'innodb_fast_shutdown'\G;
*************************** 1. row ***************************
Variable_name: innodb_fast_shutdown
Value: 1
由于Sharp Checkpoint 是将所有的脏页都刷新至磁盘中，所以如果在数据库运行时也使用该种机制，会导致数据库性能手打影响，故在数据库运行时使用 Fuzzy Checkpoint 机制进行脏页的刷新
5.2 Fuzzy Checkpoint
Fuzzy Checkpoint 是数据库在运行时，在一定的触发条件下，刷新一定的比例的脏页进磁盘中，并且刷新的过程是异步的。
有以下四种情况的 Fuzzy Checkpoint ：
- Master Thread Checkpoint
在 Master Thread 执行的 Loop 中，以每秒或每10秒的速度从缓冲池中的脏页列表中刷新一定比例的脏页回磁盘，这个过程是异步的，并不会阻塞用户查询线程。
- FLUSH_LRU_LIST Checkpoint
InnoDB 需要保证差不多有 innodb_lru_scan_depth个空闲页可以使用，在 MySQL 5.6 版本之后，在 Page Cleaner 线程（并不会阻塞用户的查询线程哦）中会进行 LRU 列表空闲页数量检查，若不够 innodb_lru_scan_depth 个空闲页，那么 InnoDB 存储引擎会根据 LRU 算法将列表尾端的页移除，若被移除的页恰好是脏页，这时就会强制执行 Checkpoint
MySQL [(none)]> show variables like 'innodb_lru_scan_depth'\G;
*************************** 1. row ***************************
Variable_name: innodb_lru_scan_depth
Value: 1024
- Async / Sync Flush Checkpoint
该种 Checkpoint 出现在重做日志不够用的情况下，这时需要强制将一些脏页列表中的脏页刷新回磁盘中。
若将已经写入到重做日志的 LSN 标记为 redo_lsn，将已经刷新会磁盘最新页的 LSN 标记为 checkpoint_lsn
则定义：checkpoint_age = redo_lsn - checkpoint_lsn
再定义：
async_water_mark = 75% * total_redo_log_file_size
sync_water_mark = 90% * total_redo_log_file_size
查看重做日志总大小
MySQL [(none)]> SELECT @@innodb_log_file_size/1024/1024;
+----------------------------------+
| @@innodb_log_file_size/1024/1024 |
+----------------------------------+
| 48.00000000 |
+----------------------------------+
1 row in set (0.00 sec)
- 当 checkpoint_age < async_water_mark 时，处于正常状态，无需任何刷新操作
- 当 async_water_mark < checkpoint_age 时，触发Async Flush 操作，从 Flush List 中刷新足够的脏页回磁盘，使得回到正常状态
- 当 checkpoint_age > sync_water_mark 时，触发Sync Flush操作，从 Flush List 中刷新足够的脏页回磁盘，使得回到正常状态
使用Async / Sync Flush Checkpoint是为了保证重做日志文件的循环可用性
- Dirty Page too much Checkpoint
当缓冲池中的脏页数量多于innodb_max_dirty_pages_pct比例时，会强制执行 Checkpoint，刷新一部分脏页进磁盘中
MySQL [(none)]> show variables like 'innodb_max_dirty_pages_pct'\G;
*************************** 1. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 75.000000
Master Thread 源码分析
一、思维导图
二、笔记
1. Master Thread 主循环
主循环主要是
while循环
Loop 循环：
- 判断数据库是否需要恢复，若需要恢复进入 suspend_thread 循环部分
- 睡眠一秒
- 判断数据库是否空闲，若空闲执行 srv_master_do_idle_tasks，反之执行 srv_master_do_active_tasks
suspend_thread 循环：
- 等待线程槽中事件
- 判断是否需要退出 Master Thread，若不需要退出则返回 loop 循环，反之退出所有线程
void* srv_master_thread(void* arg) {
while (Database running normally) {
loop:
// 数据库启动时，检查是否需要数据库恢复操作
if (srv_force_recovery >= SRV_FORCE_NO_BACKGROUND) {
goto suspend_thread;
}
sleep(1); // master thread sleep 1 second;
if (服务器是否空闲) {
srv_master_do_active_tasks(); // 服务器非空闲时
} else {
srv_master_do_idle_tasks(); // 服务器空闲时
}
suspend_thread:
//挂起 master thread 以在其线程槽 slot 中等待事件。
srv_main_thread_op_info = "suspending";
srv_suspend_thread(slot);
srv_main_thread_op_info = "waiting for server activity";
os_event_wait(slot->event);
if (不需要退出所有线程) {
goto loop;
}
my_thread_end();
os_thread_exit();
DBUG_RETURN(0);
}
}
2. active_tasks
当主线程在服务器处于
活动状态时执行srv_master_do_active_tasks，该任务函数中主要做了以下事情：
- 删除表只能发生在没有 SELECT queries 的时候
- 确保重做日志有足够的空间
- 合并 5%*srv_io_capacity 大小的 insert buffer
- 刷新一次日志至磁盘
- 若当前时间是 47 的整数倍则执行一次 LRU 缓存清理，扫描表长为一半
- 每当前时间是 7 的整数倍则执行一次设置新的 checkpoint，只向 log file 中写入 LSN，不做脏页刷新
/*
执行主线程在服务器处于活动状态时应该执行的任务。
有两种类型的任务:
第一类是在该功能的每次启动时执行的任务。我们假设当服务器处于活动状态时，该函数大约每秒调用一次。
第二类是按一定间隔执行的任务，例如：清除、dict_LRU清理等。
*/
static void srv_master_do_active_tasks(void)
{
/* 1. MySQL中的 alter table 要求Unix上的表处理程序可以在没有 select 查询后延迟删除表*/
srv_main_thread_op_info = "doing background drop tables";
row_drop_tables_for_mysql_in_background();
/* 2. 确保重做日志文件中有足够的可重用空间：如果不够，就刷新日志缓冲区或创建新的检查点 */
srv_main_thread_op_info = "checking free log space";
log_free_check();
/* 3. Do merge 5% * srv_io_capacity of insert buffer */
srv_main_thread_op_info = "doing insert buffer merge";
ibuf_merge_in_background(5);
/* 4. 每秒刷新一次日志 */
srv_main_thread_op_info = "flushing log";
srv_sync_log_buffer_in_background();
/* 现在，查看是否需要执行按 定义的时间间隔执行 的各种任务 */
if (cur_time % 47 == 0) {
/* 5 LRU缓存清理：通过逐出 未使用的表 在 LRU缓存 中腾出空间，最大扫描表长为百分之50 */
srv_main_thread_op_info = "enforcing dict cache limit";
srv_master_evict_from_table_cache(50);
}
if (cur_time % 7 == 0) {
/* 6 Make a new checkpoint ：只向 log file 中写入 LSN，不做脏页刷新 */
srv_main_thread_op_info = "making checkpoint";
log_checkpoint(TRUE, FALSE);
}
}
3. idle_tasks
当主线程在服务器处于空闲状态时执行srv_master_do_active_tasks，该任务函数中主要做了以下事情：
- 删除表只能发生在没有 SELECT queries 的时候
- 确保重做日志有足够的空间
- 合并 100%*srv_io_capacity 大小的 insert buffer
- 刷新一次日志至磁盘
- 执行一次 LRU 缓存清理，扫描表长为整个表
- 执行一次设置新的 checkpoint，只向 log file 中写入 LSN，不做脏页刷新
static void srv_master_do_idle_tasks(void) {
/* 1. MySQL中的 alter table 要求Unix上的表处理程序必须在没有 select 查询后删除表*/
srv_main_thread_op_info = "doing background drop tables";
row_drop_tables_for_mysql_in_background();
/* 2. 确保重做日志文件中有足够的可重用空间：如果不够，就刷新日志缓冲区或创建新的检查点 */
srv_main_thread_op_info = "checking free log space";
log_free_check();
/* 3. Do merge 100% * srv_io_capacity of insert buffer */
srv_main_thread_op_info = "doing insert buffer merge";
ibuf_merge_in_background(100);
/* 4. LRU内存清理：通过逐出 未使用的表 在 LRU缓存 中腾出空间，最大扫描表长为百分之100 */
srv_main_thread_op_info = "enforcing dict cache limit";
srv_master_evict_from_table_cache(100);
/* 5. 每秒刷新一次日志 */
srv_sync_log_buffer_in_background();
/* 6. 设置新的检查点：只向 log file 中写入 LSN，不做脏页刷新 */
srv_main_thread_op_info = "making checkpoint";
log_checkpoint(TRUE, FALSE);
}
4. 总结
- Master Thread 线程里的工作主要是刷新日志、清理LRU缓存、合并insert buffer、确保日志空间足够、设置新检查点，在数据库处于的状态不同时，这些任务的量不相同。
- 但是真正的刷脏页的操作并没有放在 Master Thread 中进行，而是单独放在了 Page Cleaner Thread 中进行了，这大大减轻了 Master Thread 的工作量。
Page Cleaner Thread 源码解析
一、Page Cleaner Thread 主循环
主要分为以下几部分：
- 给 page cleaner thread 分配线程空间
- 给 page cleaner 计数器加一
- 进入 while 循环，进入阻塞状态等待唤醒，线程被唤醒后进行脏页刷新 pc_flush_slot
- 退出 while 循环后进行线程退出处理
void* buf_flush_page_cleaner_worker(void* arg)
{
// 1. 给 page cleaner thread 分配空间
my_thread_init();
// 2. 增加 page cleaner work counter 数量
mutex_enter(&page_cleaner->mutex);
page_cleaner->n_workers++;
mutex_exit(&page_cleaner->mutex);
// 3. page cleaner thread loop
while (true) {
// 等待激活 page cleaner thread 的事件
os_event_wait(page_cleaner->is_requested);
// 判断是否需要退出 page cleaner thread
if (!page_cleaner->is_running) {
break;
}
// 进行 脏页刷新至磁盘
pc_flush_slot();
}
// 4. page cleanner 线程退出
mutex_enter(&page_cleaner->mutex);
page_cleaner->n_workers--;
mutex_exit(&page_cleaner->mutex);
my_thread_end();
os_thread_exit();
OS_THREAD_DUMMY_RETURN;
}
二、脏页刷新 pc_flush_slot 函数
主要做两件事：
- 从 LRU List 中进行脏页刷新
- 从 Flush List 中进行脏页刷新
static ulint pc_flush_slot(void)
{
// 1. 将指定的 buffer pool instance 中 LRU List 的末端开始刷新脏页至磁盘
// 1.1 将LRU尾部的可替换页面放入空闲列表
// 1.2 将LRU尾部的脏页刷新到磁盘
// 我们扫描每个缓冲池的深度由动态配置参数innodb_LRU_scan_depth控制
buf_flush_LRU_list(/* arg... */);
// 2. 将指定的 buffer pool instance 中 Flush List 中按照 批处理 刷新脏页至磁盘
buf_flush_do_batch(/* arg... */);
}
三、总结
- page cleanner thread 是唯一能够在 LRU List 中进行脏页刷新并释放出新的 free page 的线程
图解 InnoDB 关键特性
1、Insert Buffer
1.1 聚集索引 与 非聚集索引
概念
聚集索引（Clustered Index）：数据记录在物理磁盘上的物理顺序与索引列的值的逻辑顺序一致，又称为一级索引
非聚集索引（Unclustered Index）：数据记录在物理磁盘上的物理顺序与索引列的值的逻辑顺序不一致，又称为二级索引或辅助索引
分类
聚集索引有primary key ，该类索引的 B+ 树的叶子节点是数据结点
非聚集索引分普通索引、唯一索引、全文索引，该类索引的 B+ 树的叶子节点是索引结点，需要进行二次查询才能查到数据
其中 primary key 的选择有以下三种情况：
- 如果表中定义有主键，该主键索引就是聚集索引
- 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引
- 如果找不到第二种情况的列，InnoDB就自己产生一个row id列，它有六个字节，而且是隐藏的，使其作为聚集索引。
数据插入特性
在InnoDB存储引擎中，主键是行唯一的标识符。故行记录插入的顺序是按照主键递增的顺序进行插入的，因此，插入聚集索引一般就是按照顺序插入的，不需要磁盘的随机读取，故插入性能较高
当聚集索引与非聚集索引同时存在时，在进行插入操作时，数据页的存放还是按照主键（聚集索引）进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的，这时，就需要离散地访问非聚集索引页，由于随机读取的存在导致了插入性能的下降
故：聚集索引插入具有顺序性，非聚集索引插入具有离散性
1.2 非聚集索引的插入过程
对于非聚集索引的插入或更新操作：
- 判断待插入的非聚集索引页是否存在于缓冲池中
- 若在，则直接插入到辅助索引叶子节点上
- 若不再，则先放入到一个
Insert Buffer 对象中去，然后再以一定的频率和情况进行insert buffer和辅助索引叶子节点的合并（merge）操作
合并操作的好处：将多个对于同一个索引页的插入操作合并到一个操作中，大大提高了对于非聚集索引插入的性能
1.3 使用 Insert Buffer 的条件
使用 Insert Buffer 需要满足以下两个条件：
- 索引必须是辅助索引（非聚簇索引或二级索引）
- 索引不能是唯一的
之所以辅助索引不能是唯一的，是因为在插入缓冲时，数据库并不去查找索引页来判断插入记录的唯一性，如果去查找判断的话，就产生了离散读取的情况，从而导致了 insert buffer 的意义（因为唯一索引需要去查找判断插入记录的唯一性）
总结：insert buffer 就是为了避免 非聚集索引的插入 的离散性，进而提升插入效率
1.4 查看数据库 insert buffer 的信息
MySQL [(none)]> show engine innodb status\G;
-------------------------------------
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
- seg size 2：表示当前 insert buffer 大小为 2 * 16KB
- size 1：表示已经合并记录页的数量
- free list len 0：表示空闲列表的长度
1.5 delete buffer 和 purge buffer
InnoDB 存储引擎可以对DML操作（insert、delete、update）都进行缓冲，分别使用 insert buffer 、delete buffer、purge buffer，后面两种 buffer 统称为 change buffer
对一条记录进行 delete 操作可以分为两个过程：
- 将记录标记为删除，对应 delete buffer
- 真正将记录删除，对应 purge buffer
InnoDB 提供了参数 innodb_change_buffering 来开启各种 buffer 的选项。该参数可选的值为 insert \ delete \ purges \ changes \ all \ none
MySQL [(none)]> show variables like 'innodb_change_buffering'\G;
*************************** 1. row ***************************
Variable_name: innodb_change_buffering
Value: all
1 row in set, 1 warning (0.03 sec)
可以通过参数innodb_change_buffer_max_size来控制 change buffer 的大小
MySQL [(none)]> show variables like 'innodb_change_buffer_max_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_change_buffer_max_size
Value: 25
1 row in set, 1 warning (0.00 sec)
1.6 insert buffer 数据结构探究
insert buffer 对应的结构体：mysql-5.7.19\storage\innobase\include\ibuf0ibuf.ic
/** Insert buffer struct */
struct ibuf_t{
dict_index_t* index; /*!< insert buffer index */
ulint size; /*!< current size of the ibuf index tree, in pages */
ulint max_size; /*!< recommended maximum size of the ibuf index tree, in pages */
ulint seg_size; /*!< allocated pages of the file segment containing ibuf header and tree */
bool empty; /*!< true insert buffer tree is empty. */
ulint free_list_len; /*!< length of the free list */
ulint height; /*!< tree height */
ulint n_merges; /*!< number of pages merged */
ulint n_merged_ops[IBUF_OP_COUNT];
ulint n_discarded_ops[IBUF_OP_COUNT];
};
- max_size：
insert buffer index tree的大小，即 insert buffer 的最大占有内存 - index：插入缓冲索引
数据库启动时，初始化 insert buffer 时将最大设置为缓冲池内存的 1/4
#define CHANGE_BUFFER_DEFAULT_SIZE (25)
ibuf->max_size = (buf_pool_get_curr_size() / UNIV_PAGE_SIZE) * CHANGE_BUFFER_DEFAULT_SIZE / 100;
由源码的注释可以看出，insert buffer 其实是一颗 B+ 树，insert buffer B+ tree 的由叶子节点的非叶子节点组成，叶子节点就是索引页(index page)
当前版本中，全局只有一颗insert buffer B+ 树，负责对所有表的辅助索引做插入缓冲操作，这课树存放在共享表空间中，默认为 ibdata1 中
非叶子节点存放的是 search key ，其构造如图所示：
- space ：每个表有唯一的 space_id ，用来标识是哪张表
- marker：用于兼容老版本
- offset：表示页所在位置的偏移量，也叫 page_no
当一个辅助索引要插入到页（space, offset）时，如果这个页不再缓冲池中，那么 InnoDB 存储引擎首先会构造出来一个 search key ，接下来查询 insert buffer B+ 树具体插入位置，寻找到位置后，将这个 search key 构造成叶子节点结构后再插入，构造规则如下：
- metadata：低两字节存储当前记录进入 insert buffer 的顺序，第三个字节表示操作类型，第四个字节是额外标志
/* Offsets for the fields at the beginning of the fourth field */
#define IBUF_REC_OFFSET_COUNTER 0 /*!< Operation counter */
#define IBUF_REC_OFFSET_TYPE 2 /*!< Type of operation */
#define IBUF_REC_OFFSET_FLAGS 3 /*!< Additional flags */
metadata结构：
为了保证辅助索引页中的记录能够插入到 insert buffer B+ 树，需要有一个特殊的页来标记每个辅助索引页的可用空间，这个特殊的页为 insert buffer bitmap page
每个 insert buffer bitmap page 用来追踪 2^13 个辅助索引页，也就是 2^8 个区，每个辅助索引页占 4 位
这4位分别存储以下信息：
/** Offsets to the per-page bits in the insert buffer bitmap */
#define IBUF_BITMAP_FREE 0 /*!< Bits indicating the amount of free space */
#define IBUF_BITMAP_BUFFERED 2 /*!< TRUE if there are buffered changes for the page */
#define IBUF_BITMAP_IBUF 3 /*!< TRUE if page is a part of the ibuf tree, excluding the root page,
or is in the free list of the ibuf */
- IBUF_BITMAP_FREE：表示该辅助索引页中可用空间数量，0表示无剩余可用空间，1表示剩余空间大于1/32页，2表示剩余空间大于1/16页，3表示剩余空间大于1/8页
- IBUF_BITMAP_BUFFERED：表示该辅助页是否有记录被缓存在 insert buffer B+ 树中，1表示有
- IBUF_BITMAP_IBUF：表示该页是否为 insert buffer B+ 树的索引页，1表示是
1.7 从 B+ 树 Merge 至辅助索引页的时机
合适时机有以下几种情况：
- 辅助索引页被读取到缓冲池中时
- Insert Buffer Bitmap 页追踪该辅助索引页可用空间至少为1/32时，会强制读取辅助索引页
- Master Thread 每一段时间会进行一次 merge insert buffer 操作，具体数量根据活动状态确定
合并时页的选择：
InnoDB存储引擎会在 insert buffer B+树上随机选择一个页，读取该页的 space 及之后所需数量的页。这种算法可以很好的保证公平性，若读取到的表已经被删除了，此时可以直接丢弃数据记录
2、Double Write
2.1 Double Write 解决了什么问题
当 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，突然数据库宕机了，导致数据页一部分是新数据一部分是旧数据，产生了不完整的数据页，这种情况称为部分写失败（partial page write）
能否使用重做日志进行恢复呢？答案是不一定的，重做日志中记录的是对页的物理操作，如果被操作的页已经产生了损坏，再对其进行重做是没有任何意义的。（例如：在偏移量800处写’aaa’记录，在损坏页前后偏移量800附近的数据可能是不一样的）
所以，我们最好给数据页做一个备份，当出现数据页损坏时，用备份来恢复这个 损坏页，然后再进行重做日志，这就是 doublewrite
2.2 Double Write 工作原理
doublewrite 由两部分内存组成，一部分位于内存中的double write buffer 大小为 2 MB，另一部分位于磁盘的共享表空间中同样也是 2 MB
对缓冲池中的脏页进行刷新时：
- 先通过 memcopy 函数将脏页复制到 doublewrite buffer 中，将 buffer 写满
- 再将 doublewrite buffer 中的 2 MB 的脏页按照每次 1 MB 的大小分两次顺序写入磁盘上的共享表空间的 double write memory 上
- 再将 doublewrite buffer 中的 2 MB 的脏页以离散写的方式写入到脏页各自的表空间文件中去
- 当离散写完成，脏页数据固化后，再将 double write memory 中的数据标记为可覆盖，以供下一次写入
出现宕机时，损坏的数据页直接从磁盘上的 double write memory 中的备份恢复（recovery） 即可
2.3 查看数据库 doublewrite 运行情况
MySQL [(none)]> show global status like 'innodb_dblwr%'\G;
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
Value: 2
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
Value: 1
Innodb_dblwr_pages_written：表示 doublewrite 写入的总页数Innodb_dblwr_writes：表示 doublewrite 写入的次数
2.4 产生部分写失败的原因
一个数据页是 16K ，但是文件系统 IO 的最小单位是 4K ，故一个数据页需要写 4 次 IO 才能写完整
redo log 每次写入的单位是 512字节 ，也就是磁盘IO的最小单位，故不存在写入不完整的情况
3、Adaptive Hash Index
3.1 建立 AHI 的初衷
B+ 树的查找次数，取决于 B+ 树的高度，一般 B+ 树的高度为 3 ~ 4 层，故需要 3 ~ 4 次的查询才能完成一次查找
InnoDB 存储引擎会监控对于表上各个索引页的查询，自动根据访问频率和访问模式来自动为某些热点页建立哈希索引，使得查找的时间复杂度为O(1)
访问模式指的是查询条件格式
3.2 建立 AHI 的要求
建立 AHI 的要求如下：
- 以某个访问模式查询了 100 次
- 页通过该模式访问了 N 次，其中 N = 页中的记录数 * 1 / 16
哈希索引只能用来搜索等值的查询，例如：select * from table where id = ‘XXX’，对于范围查找来说，不能使用哈希索引
以下两种模式交替进行也不会建立 AHI，必须是一种模式：
- where a = xxx
- where a =xxx and b = xxx
3.3 查看数据库 AHI 的使用情况
MySQL [(none)]> show engine innodb status\G;
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
由 AHI 的信息可以看出：AHI的大小、每秒哈希查找次数、每秒非哈希查找次数
MySQL [(none)]> show variables like 'innodb_adaptive_hash_index'\G;
*************************** 1. row ***************************
Variable_name: innodb_adaptive_hash_index
Value: ON
1 row in set, 1 warning (0.01 sec)
4、AIO
异步IO：Asynchronous IO （AIO），即每次 IO 操作，不需要等待 IO 结束就能继续做其他操作
同步IO：synchronous IO，即每次 IO 操作，需要等待 IO 结束才能继续做其他操作
AIO的优势就是可以进行 IO merge 操作，即把多个 IO 合并为 1 个 IO 操作
例如：用户需要访问三个页：(8, 6) (8, 7) (8, 8)
解析：这三个页都位于同一个空间内，并且 page_no 是连续的，故 AIO 会发送一个 IO 请求，从 (8, 6) 开始，连续读取三个页大小的内存即可
控制 AIO 是否开启参数：innodb_use_native_aio
MySQL [(none)]> show variables like 'innodb_use_native_aio'\G;
*************************** 1. row ***************************
Variable_name: innodb_use_native_aio
Value: ON
1 row in set, 1 warning (0.00 sec)
5、Flush Neighbor Page
Flush Neighbor Page：即刷新邻接页，工作原理为：当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区的所有页，如果有脏页一起刷新
好处：通过AIO可以将连续的脏页都刷新了
扫一扫
1426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
