mysql的四大线程_MySQL内存结构、日志及主要线程

实际上MySQL内存的组成和Oracle类似，也可以分为SGA(系统全局区)和PGA(程序缓存区)。

mysql>show variables like "%buffer%";

一、SGA(系统全局区)

1.innodb_buffer_bool

用来缓存Innodb表的数据、索引、插入缓冲、数据字典等信息。

2.innodb_log_buffer

事务在内存中的缓冲，即redo log buffer的大小

3.query cache

高速查询缓存，在生产环境中建议关闭。

4.key_buffer_size

用于MyISAM存储引擎，缓存MyISAM存储。

引擎表的索引文件(区别于innodb_buffer_poll数据和索引缓存)

5.innodb_additional_mem_pool_size

用来缓存数据字典信息和其它内部数据结构的内存池的大小，MySQL5.7.4中该参数取消。

二、PGA(程序缓存区)

1.sort_buffer_size

主要用于SQL语句在内存中的临时排序

2.join_buffer_size

表连接使用，用于BKA，MySQL5.6之后开始支持。

3.read_buffer_size

表顺序扫描的缓存，只能应用于MyISAM表存储引擎。

4.read_rnd_buffer_size

MySQL随机读缓冲区大小，用于做mrr,mrr是MySQL5.6之后才有的特性。

5.tmp_table_size

SQL语句在排序或分组时没有用到索引，就会使用临时表空间。

6.max_heap_table_size

管理heap,memory存储引擎表。

三、Buffer状态及其链表结构

page是InnoDB磁盘I/O的最小单位，数据存放在page中，对应到内存就是一个个buffer

buffer有三种状态：

free buffer：这个状态下的buffer没有被使用，是空闲的。但是在生产环境中，数据库很繁忙的情况下，free buffer基本不存在

clean buffer：内存中buffer里的数据和磁盘中page的数据一致

dirty buffer：内存中新写入的数据还没有刷新到磁盘，uffer里的数据和磁盘中page的数据不一致

buffer在内存中，需要被chain(链)组织起来

InnoDB是双向链表结构，由三种不同的buffer状态衍生出三条链表

free list：把free buffer都串联起来。数据库跑起来的时候，每次把page调到内存中，都会判断free buffer是否够用，不够用的话从lru list和flush list中释放free buffer

lru list：把与磁盘数据一致，并且最近最少被使用的buffer串联起来，释放出free buffer

flush list：把dirty buffer串联起来，方便刷新线程将脏数据刷到磁盘。规则是将那些最近最少被弄脏的数据串起来，刷新到磁盘后，释放出更多的free buffer

四、各大刷新线程及其作用

a)master thread线程

后台线程中的主线程，优先级别最高

内部有四个循环：

主循环loop

后台循环backgroud loop

刷新循环flush loop

暂定循环suspend loop

根据数据的运行状态在这四个循环之间进行切换

主循环loop中包含了每1s和每10s的操作

每1s操作：

1、日志缓冲刷新到磁盘，即使这个事务还没有被提交

2、刷新脏页到磁盘

3、执行合并插入缓冲的操作

4、产生checkpoint

5、清除无用的table cache

6、如果当前没有用户活动，就可能切换到background loop

每10s操作：

1、日志缓冲刷新到磁盘，即是事务还没有被提交

2、执行合并插入缓冲操作

3、刷新脏页到磁盘

4、删除无用的undo页

5、产生checkpoint

b)四大I/O线程

read/write thread：数据库的读写请求线程，默认值是4个，如果使用高转速磁盘，可适当调大该值

redo log thread：把日志缓存中的内容刷新到redo log文件中

change buffer thread：把插入缓存(change buffer)中的内容刷新到磁盘中

c)page cleaner thread

负责脏页刷新线程，可增加多个

d)purge thread

负责删除无用的undo页

由于进行DML语句操作都会生成undo，系统需要定期对undo页进行清理，这时就需要purge操作

purge默认线程个数是1个，最大可调整至32个

e)checkpoint线程

在redo log发生切换时，执行checkpoint。

redo log 发生切换或文件快写满时，会触发把脏页刷新到磁盘，确保redo log刷新到磁盘，实现真正的持久化，避免数据丢失

error monitor thread :负责数据库报错的监控线程

lock monitor thread :负责锁的监控线程

五、内存刷新机制

MySQL这种关系型数据库，讲究日志先行策略，就是一条DML语句进入数据库之后，先写日志，再写数据文件

a)redo log

redo log 又称重做日志文件，用来记录事务操作的变化，记录数据修改之后的值，不管事务是否被提交，都会被记录下来

在实例和介质失败时，如数据库掉电，这时候InnoDB存储引擎会使用重做日志，恢复到掉电前的时刻

默认情况至少有两个redo log文件，在磁盘用ib_logfile(0-N)来命名

redo log 写的方式是顺序写、循环写

顺序写、循环写是什么意思呢？

也就是说，第一个文件写满之后，写第二个文件，这样依次写到最后一个文件，直至最后一个文件写满之后，又重新写第一个文件，就这样循环

写满日志文件会产生切换操作，并执行checkpoint，触发脏页的刷新

数据库重启过程中，如果参数文件中的redo log值大小和当前redo log值大小不一致，那就删除当前redo log文件，按照参数文件中的redo log值大小，生成新的redo log文件在生成redo log之前，数据写在redo log buffer中

redo log buffer刷新到磁盘的条件：

(1)通过innodb_flush_log_at_trx_commit 参数来控制

0：redo log thread 每隔1s会将redo log buffer中的数据写入redo log中，同时进行刷盘操作

每次事务提交并不会触发redo log thread 将日志缓冲中的数据写入redo log中去

1：每次事务提交时，都会触发redo log thread 将日志缓冲中的数据写入redo log，并flush到磁盘

2：每次事务提交时，把redo log buffer的数据写入redo log中，不刷到磁盘

这三种模式：0的性能 最好，但不安全，有丢失1s的数据的风险。1的安全性最高，不会丢失任何已经提交的事务，但就是数据库的性能最慢。2介于两者之间

(2)master thread ：每秒进行刷新

(3)redo log buffer：使用超过一半的时候就触发刷新

b)binlog

MySQL的二进制文件，用于备份恢复和主从复制

从binlog cache刷新到磁盘binlog文件中所需条件：

sync_binlog=0时，当事务提交后，MySQL不做fsync之类的磁盘同步指令、将binlog_cache中的信息刷新到磁盘，而是让Filesystem自行决定什么时候来做同步，或cache满了之后，才同步到磁盘

sync_binlog=n时，每进行n次事务提交后，MySQL进行一次fsync之类的磁盘同步指令、将binlog_cache中的信息刷新到磁盘

sync_binlog设置为1，更安全

sync_binlog设置为0，性能最好

c)redo log 和 binlog的区别

redo log 和 binlog都记录了数据真实修改的语句，那他们为何要并存呢？

1、记录内容不同

binlog是逻辑日志，记录所有数据的改变信息

redo log是物理日志，记录所有InnoDB表数据的变化

2、记录内容的时间不同

binlog记录commit完毕之后的DML和DDL SQL语句

redo log记录事务发起之后的DML和DDL SQL语句

3、文件使用方式不同

binlog不是循环使用，在写满或者实例重启之后，会生成新的binlog文件

redo log是循环使用，最后一个文件写满之后，会重新写第一个文件

4、作用不同

binlog可以作为恢复数据使用，主从复制搭建

redo log作为异常宕机或者介质故障后的数据恢复使用

d)脏页刷新条件

1、重做日志ib_logfile文件写满之后，在切换的过程中会执行checkpoint，会触发脏页的刷新

2、innodb_max_dirty_pages_pct参数控制,其含义是在 buffer pool 中 dirty page 所占百分比，达到设置的值，会触发脏页的刷新

3、innodb_adaptive_flushing 参数控制，其影响每秒刷新脏页的数目，默认开启

组提交

看看如何保证 binlog 写入顺序和存储引擎提交顺序是一致的，并且能够进行 binlog 的组提交？5.6 引入了组提交，并将提交过程分成 Flush stage、Sync stage、Commit stage 三个阶段。

这样，事务提交时分为了如下的阶段：

InnoDB, Prepare SQL已经成功执行并生成了相应的redo和undo内存日志； Binlog, Flush Stage 所有已经注册线程都将写入binlog缓存； Binlog, Sync Stage binlog缓存将sync到磁盘，sync_binlog=1时该队列中所有事务的binlog将永久写入磁盘； InnoDB, Commit stage leader根据顺序调用存储引擎提交事务；

每个 Stage 阶段都有各自的队列，从而使每个会话的事务进行排队，提高并发性能。

如果当一个线程注册到一个空队列时，该线程就做为该队列的 leader，后注册到该队列的线程均为 follower，后续的操作，都由 leader 控制队列中 follower 行为。

leader 同时会带领当前队列的所有 follower 到下一个 stage 去执行，当遇到下一个 stage 为非空队列时，leader 会变成 follower 注册到此队列中；注意：follower 线程绝不可能变成 leader 。

binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个参数是用于故意拉长 binlog 从 write 到 fsync 的时间，以此减少 binlog 的写盘次数。在 MySQL 5.7 的并行复制策略里，它们可以用来制造更多的“同时处于 prepare 阶段的事务”。这样就增加了备库复制的并行度

也就是说，这两个参数，既可以“故意”让主库提交得慢些，又可以让备库执行得快些。在 MySQL 5.7 处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

undo log

在InnoDB存储引擎层面主要分为两种日志，一种是redo log 一种是undo log。InnoDB支持事物、支持MVCC多版本并发控制。InnoDB的多版本是通过undo 和回滚段来实现的。InnoDB是索引组织表，每行记录都包含DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR和DELETE_BIT几个隐藏字段。DB_TRX_ID：用来标识最近一次对本行记录做修改的事务的标识符，即最后一次修改本行记录的事务id。delete操作在内部来看是一次update操作，更新行中的删除标识位DELELE_BIT。

DB_ROLL_PTR：指向当前数据的undo log记录，回滚数据通过这个指针来寻找记录被更新之前的内容信息。DB_ROW_ID：包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。DELELE_BIT：用于标识该记录是否被删除。

对记录做变更操作时不仅会产生redo log，也会产生undo log记录(insert、update、delete)。但undo只记录变更之前的旧数据，undo记录默认被记录到系统表空间(ibdata1)中，但是从MySQL5.6起，就可以使用独立的undo表空间了。采用独立undo表空间，再也不用担心undo会把ibdata1文件弄大；也给我们部署不同I/O类型的文件位置带来便利，对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上。

redo log，又称重做日志，用于记录事物操作变化，记录的是事物被修改后的值，不管事物是否提交都会记录下来，在实例和介质失败时，如数据库掉电，就可以利用redo log 恢复到掉电前的时刻，以此来保证数据的完整性。

undo log，对记录的变更操作时不仅会产生redo记录，也会产生undo记录(insert、update、delete)。但undo只记录变更前的旧数据。通常可以使用undo日志来实现回滚操作，保证事务的一致性。