存储引擎

• 事务的执行流程 ```javascript

  1. 客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配（max_connections，8小时超时）

  2.建立连接后客户端发送一条语句，mysql收到该语句后，通过命令分发器判断其是否是一条更新语句，如果是，则直接发送给分析器做语法分析。

  3.分析器阶段，MySQL需要知道到底要查哪些东西，如果语法不对，就会返回语法错误中断查询

  4.分析器的工作完成后，将语句传递给预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义等

  5.语句解析完成后，MySQL就知道要查什么了，之后会将语句传递给优化器进行优化（通过索引选择最快的查找方式），并生成执行计划。

  6.执行器根据生成的执行计划去open table，此时会先去查看该表上是否有元数据（MDL）排他锁（如果有元数据共享锁则无影响），如果有元数据排他锁，则事物被阻塞，进入等待状态（时间由lock_wait_timeout决定，默认是一年。。。。），等元数据锁被释放，继续执行。如果无元数据锁或者是有元数据共享锁，则该事务在表上加元数据共享锁（因为元数据共享读锁之间是不冲突的，如果表上有元数据共享锁，我们执行alter table这样的DDL语句时，会进入等待状态，因为DDL语句需要在表上加元数据排他锁）

  7.进入引擎层（默认innodb），去innodb_buffer_pool里面的data dictionary得到表得相关信息

  8.根据表信息去innodb_buffer_pool里面的lock info查看是否有相关的锁信息，如果有则等待（因为要加排它锁），如果没有则加排它锁，更新lock info。

  9.取读取相关数据页到innodb_buffer_pool中（如果数据页本身就在缓存中，则不用从硬盘读取）

10.将页中的原始数据（快照）保存到undo log buffer中（undo log buffer会以相关参数定义的规则进行刷盘操作写入到undo tablespace中）

11.在innodb_buffer_pool中将相关页面更新，该页变成脏页（脏页会以相关参数定义的规则进行刷盘操作写入所属表空间中）

12.页面修改完成后，会把修改后的物理页面保存到redo log buffer中，（redo log buffer会以相关参数定义的规则进行刷盘操作写入到redo tablespace中）

13.如果开启binlog，则更新数据的逻辑语句也会记录在binlog_cache中（binlog会以相关参数定义的规则进行刷盘操作写入到binlog file 中）

14.如果该表上有二级索引并且本次操作会影响到二级索引，则会把相关的二级索引修改写入到innodb_buffer_pool中的change buffer里（change buffer 会以相关参数定义的规则进行刷盘操作写入所属表空间中）

15.前期的准备工作到此已经做完了，之后便是事务的commit或者rollback操作。一般情况下执行的是commit操作

16.执行commit操作后（mysql默认开启自动提交，如果手动开始事务begin，则需要显示提交commit），由于要保证redolog与binlog的一致性，redolog采用2阶段提交方式。

17.将undo log buffer及redo log buffer刷盘（innodb_flush_log_at_trx_commit=1），并将该事务的redolog标记为prepare状态。

18.将binlog_cache数据刷盘（sync_binlog=1）

19.如果开启了主从结构，此时会将binlog_cache中的信息通过io线程发送给从机，如果开启了半同步复制则需要等待从机落盘（relay log）并反馈。如果是异步复制则无需等待(默认是异步复制)

20.待binlog落盘完成，再将redolog中该事务信息标记为commit，释放相关锁资源。此时一个更新事务的操作已经完成，返回给客户端成功更新提示。

21.标记undolog中该事务修改页的原始快照信息为delete，当无其他事务引用该原始数据时(MVCC)，再将其删除

22.如果此时触发了脏页刷盘操作，会先将脏页写入到double write buffer中（防止写入过程中出现断页，因为mysql页面默认为16K，linux操作系统最大为4K，如果写了8K时系统挂了，这个数据页将不完整，标记为损坏）然后再写到期所在表空间的相应位置。

- 什么时候合并change  buffer
```javascript
合并时机
1.索引页被拿到buffer pool中使用；
2.master thread会每隔一段时间做巡查，当数据库不忙的时候，也会做合并操作；
3.change buffer的空间不够时，也有可能会进行merge操作；（如果业务繁忙，又没空间了，可能就需要把change buffer的内容放到ibdata1里）
4.数据库正常关闭时；
5.redo log写满时（这种情况极少，因为redo log要是写满了整个数据库也没法用了）

• 哪种业务场景适合使用change buffer ```javascript 哪种业务场景适合使用change buffer 1）数据库大部分是非聚簇非唯一索引； 2）业务写多读少，或者不是写后立刻读取； 可以使用change buffer的功能，将原本每次写入都需要进行磁盘IO的SQL，进行优化定期批量写磁盘。

  比如日志，账单，流水账业务

哪种业务场景不适合 1）数据库都是唯一索引 2）写入一个数据后，立刻进行读取

这两类场景，在写操作进行时(进行后)，本身就需要进行页读取，相应的页面应该被拿到缓冲池，此时先写入change buffer反而增加了复杂度，变得多此一举了。

就是本来人家就要看这个页面，直接读取到buffer pool中修改是最好的，数据库反而先写到change buffer中了，然后马上又进行merge操作，这就等于增加了IO。

- change buffer作用
```javascript
#基本作用
change buffer缓存的是对一个索引页的DML操作（update，insert，delete）。
DML操作涉及到对非聚簇非唯一索引页进行修改时，会用到change buffer。
当要修改的索引页在buffer pool中时，DML语句会直接对该索引页进行修改。
如果该索引页不在buffer pool中，那么就会把此次操作先记录到change buffer中。等下次有业务访问到这个索引页，也就是该索引页被读取到buffer pool中时，change buffer中记录的操作会和此索引页进行合并操作；或是业务空闲时期，进行merge操作。

• DWB

8.0之前DWB是放在共享表空间里的，8.0以后独立出来了但是并没有配置空间大小的参数，我感觉应该是默认生成了2个文件然后轮询使用，旧的数据会被覆盖掉，8.0这个innodb_doublewrite_files文件数量也可以配置，最大不超过innodb buffer pool数量的两倍。那个2M指的只是每次从dwb内存刷到磁盘的数据大小而不代表dwb磁盘空间仅仅是2M,一般情况下不会满，特殊情况满了会dwb溢出，一般可以避免。说白了就是DWB有两部分——内存区域+磁盘区域，innodb buffer pool里的脏页先刷到DWB的内存区域（这个过程是顺序的），然后DWB内存区域数据在刷到磁盘区域（这个过程是离散的）这个过程中数据每次1M分两次刷也就是两兆两兆的刷，而DWB磁盘区域是远远不止2M的，而且2M对于数据库数据来说已经很大了，更别说动辄几百G的磁盘空间了，所以很难写满的。

• mysql线程总结 ```javascript

  master thread

主要职责

a.控制刷新脏页到磁盘（checkpoint）

b.控制日志缓冲刷新到磁盘（log buffer —-> redo）

c.undo页回收

d.合并插入缓冲（change buffer）

e.控制IO刷新数量

checkpoint

将脏页写入到磁盘上这个动作就叫做checkpoint（ckpt）

sharp checkpoint：

完全检查点，数据库正常关闭时，会触发把所有的脏页都写入到磁盘上（这时redo就没用了，因为脏页已经写到磁盘上了）

fuzzy checkpoint：

模糊检查点，部分页写入到磁盘

master thread checkpoint：

差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，不会阻塞用户查询。

每1秒一次操作涉及的行为

1，刷新redo日志缓冲到磁盘，不管是否提交（总是）
2，合并插入缓冲（可能），根据一秒之内发生的IO次数：
           if(过去1秒钟的ios<5%innodb_io_capacity）
           {    
               执行合并5%innodb_io_capacity个page的插入缓冲的操作
           }
3，刷新缓冲池中的脏页到磁盘（可能）：
           if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct)
                刷新脏页个数：100%*innodb_io_capacity
           if(buf_get_modified_ratio_pct<buf_max_dirty_pages_pct)
                刷新脏页个数：10%*innodb_io_capacity
4. checkpoint
5，如果当前没有用户活动，切换到background模式

每10秒一次操作涉及的行为

1，刷新缓冲池中的脏页到磁盘（可能）：
           if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct)
                刷新脏页个数：100%*innodb_io_capacity
           if(buf_get_modified_ratio_pct<buf_max_dirty_pages_pct)
                刷新脏页个数：10%*innodb_io_capacity
2，合并插入缓冲（可能），根据过去一秒之内发生的IO次数：
            if(ios<5%innodb_io_capacity）
            {    
                执行合并5%innodb_io_capacity个page的插入缓冲的操作
            }
3，日志缓存刷新到磁盘（总是）
4，删除无用的undo页（总是）
           根据参数：innodb_purge_batch_size
5. checkpoint

purge thread

事务在提交之前，通过undolog(回滚日志)记录事务开始之前的状态，当事务被提交后，undolog便不再需要，因此需要Purge Thread线程来回收已经使用并分配的undo页。可以在配置文件中添加innodb_purge_threads=1来开启独立的Purge Thread，等号后边控制该线程数量，默认为4个。也就是一个协调线程和三个工作线程。

purge线程基本工作过程原理

详情见https://blog.csdn.net/n88Lpo/article/details/110507415

一、基本工作

• 清理del flag标签的记录

• 清理undo的历史版本

• 如果需要进行undo tablespace截断。

2.协调线程循环检测变化
3.克隆最老的read view，因为清理undo需要根据当前最老的read view来清理，否则可能清理到正在读取需要的undo。
4.从可能需要清理的purge_queue中取出undo segment(简单理解为事务)
5.判断是否符合清理规则
6.每次清理默认为300个page
7.工作线程处理
8.默认每128次batch undo清理会进行undo history清理
9.清理undo history和undo空间

IO thread

IO 线程有read IO和write IO，比例可调整，两者相加不能超过CPU的个数

page cleaner

原本是master thread中的一部分，在innodb 1.2x之后从master thread分离出来，负责将内存脏页刷新到磁盘。

---


```javascript
 1. 客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配（max_connections，8小时超时）
 2.建立连接后客户端发送一条语句，mysql收到该语句后，通过命令分发器判断其是否是一条更新语句，如果是，则直接发送给分析器做语法分析。
 3.分析器阶段，MySQL需要知道到底要查哪些东西，如果语法不对，就会返回语法错误中断查询
 4.分析器的工作完成后，将语句传递给预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义等
 5.语句解析完成后，MySQL就知道要查什么了，之后会将语句传递给优化器进行优化（通过索引选择最快的查找方式），并生成执行计划。
 6.执行器根据生成的执行计划去open table，此时会先去查看该表上是否有元数据（MDL）排他锁（如果有元数据共享锁则无影响），如果有元数据排他锁，则事物被阻塞，进入等待状态（时间由lock_wait_timeout决定，默认是一年。。。。），等元数据锁被释放，继续执行。如果无元数据锁或者是有元数据共享锁，则该事务在表上加元数据共享锁（因为元数据共享读锁之间是不冲突的，如果表上有元数据共享锁，我们执行alter table这样的DDL语句时，会进入等待状态，因为DDL语句需要在表上加元数据排他锁）


 7.进入引擎层（默认innodb），去innodb_buffer_pool里面的data dictionary得到表得相关信息

 8.根据表信息去innodb_buffer_pool里面的lock info查看是否有相关的锁信息，如果有则等待（因为要加排它锁），如果没有则加排它锁，更新lock info。

 9.取读取相关数据页到innodb_buffer_pool中（如果数据页本身就在缓存中，则不用从硬盘读取）

10.将页中的原始数据（快照）保存到undo log buffer中（undo log buffer会以相关参数定义的规则进行刷盘操作写入到undo tablespace中）

11.在innodb_buffer_pool中将相关页面更新，该页变成脏页（脏页会以相关参数定义的规则进行刷盘操作写入所属表空间中）

12.页面修改完成后，会把修改后的物理页面保存到redo log buffer中，（redo log buffer会以相关参数定义的规则进行刷盘操作写入到redo tablespace中）

13.如果开启binlog，则更新数据的逻辑语句也会记录在binlog_cache中（binlog会以相关参数定义的规则进行刷盘操作写入到binlog file 中）

14.如果该表上有二级索引并且本次操作会影响到二级索引，则会把相关的二级索引修改写入到innodb_buffer_pool中的change buffer里（change buffer 会以相关参数定义的规则进行刷盘操作写入所属表空间中）

15.前期的准备工作到此已经做完了，之后便是事务的commit或者rollback操作。一般情况下执行的是commit操作

16.执行commit操作后（mysql默认开启自动提交，如果手动开始事务begin，则需要显示提交commit），由于要保证redolog与binlog的一致性，redolog采用2阶段提交方式。

17.将undo log buffer及redo log buffer刷盘（innodb_flush_log_at_trx_commit=1），并将该事务的redolog标记为prepare状态。

18.将binlog_cache数据刷盘（sync_binlog=1）

19.如果开启了主从结构，此时会将binlog_cache中的信息通过io线程发送给从机，如果开启了半同步复制则需要等待从机落盘（relay log）并反馈。如果是异步复制则无需等待(默认是异步复制)

20.待binlog落盘完成，再将redolog中该事务信息标记为commit，释放相关锁资源。此时一个更新事务的操作已经完成，返回给客户端成功更新提示。

21.标记undolog中该事务修改页的原始快照信息为delete，当无其他事务引用该原始数据时(MVCC)，再将其删除

22.如果此时触发了脏页刷盘操作，会先将脏页写入到double write buffer中（防止写入过程中出现断页，因为mysql页面默认为16K，linux操作系统最大为4K，如果写了8K时系统挂了，这个数据页将不完整，标记为损坏）然后再写到期所在表空间的相应位置。