在学习过程中对MySQL的底层原理产生极大的兴趣，动手做一个轮子项目-简易数据库，做的过程中伴随着 小林Coding的相关文章一起学习，实际理论相结合，学习效果很显著，并且把小林Coding的MySQL整理摘抄了一份，具体内容如下：
MySQL八股
目录
- MySQL八股
- 一、基础篇
- 二.索引篇
- 1.什么是索引
- 2.索引的分类
- 1. 按数据结构分类
- 为什么MySQL InnoDB选择B+tree作为索引的数据结构：
- 1.B+Tree vs B Tree
- 2.B+Tree vs 二叉树
- 3.B+Tree vs Hash
- 2.按物理存储分类
- 3.按字段特性分类
- 1.主键索引
- 2.唯一索引
- 3.普通索引
- 4.前缀索引
- 4.按字段个数分类
- 联合索引
- 联合索引范围查询
- 索引下推
- 索引区分度
- 联合索引进行排序
- 3.什么时候需要/不需要创建索引
- 1.什么时候适用索引？
- 2.什么时候不需要创建索引
- 4.有什么优化索引的方法
- 1.前缀索引优化
- 2.覆盖索引优化
- 3.主键索引最好是自增的
- 4.索引最好设置为NOT NULL
- 5.防止索引失效
- 三、事务篇
- 1.事务有哪些特性
- 2.并行事务会引发什么问题
- 3.事务的隔离级别有哪些
- 4.Read View 在MVCC里如何工作
- 需要了解俩个知识：
- 1.Read View有四个重要的字段：
- null
- 2.聚簇索引中的隐藏列
- 5.可重复读是如何工作的？
- 6.读提交是如何工作的？
- 7.总结：
- 四、锁篇
- 1.MySQL有哪些锁
- 1.全局锁
- 1.1全局锁如何使用？
- 1.2全局锁的应用场景是什么？
- 1.3全局锁的缺点
- 1.4 如何避免全局锁使用时对业务的影响
- 2.表级锁
- 1.表锁
- 2.元数据锁（MDL）
- 1.MDL不需要显示调用，那什么时候释放
- 2.为什么线程C申请不到MDL写锁，后续的申请读锁的查询操作也会阻塞
- 3.意向锁
- 4.AUTO-INC锁
- 3.行级锁
- 1.Record Lock
- 2.Gap Lock
- 3.Next-Key Lock
- 4.插入意向锁
- 2.MySQL是如何加锁的
- 1.什么SQL语句会加行级锁？
- 2.行级锁有哪些种类
- 1.Record Lock
- 2.Gap Lock
- 3.Next-Key Lock
- 3.MySQL是怎么加行级锁的？
- 1.唯一索引等值查询
- 2.唯一索引范围查询
- 3.非唯一索引等值查询
- 4.非唯一索引范围查询
- 5.没有加索引的查询
- 4.总结
- 五、日志篇
- MySQL日志：undo log、redo log、binlog有什么用？
- 1.为什么需要undo log？
- 2.为什么需要Buffer Pool？
- 3.为什么需要redo log？
- redo log 什么时候刷盘？
- redo log 写满了怎么办？
- 4.为什么需要binlog？
- redo log 和 binlog 有什么区别？
- 1.适用对象不同：
- 2.文件格式不同：
- 3.写入方式不同：
- 4.用途不同：
- 主从复制怎么实现
- binlog什么时候刷盘？
- 5.为什么需要两阶段提交？
- 两阶段提交的过程是怎样的？
- 异常重启会出现什么问题？
- 两阶段提交有什么问题
- 6.MySQL磁盘I/O很高，有什么优化的办法？
- 7.总结
- 六、内存篇
- 揭开Buffer Pool 的面纱
- 1.为什么要有 Buffer Pool
- Buffer Pool有多大？
- Buffer Pool缓存什么？
- 2.如何管理 Buffer Pool？
- 如何管理空闲页？
- 如何管理脏页？
- 如何提高缓存命中率？
- 脏页什么时候会被刷入磁盘？
- 3.总结
一、基础篇
MySQL执行一条查询语句的流程：
二.索引篇
1.什么是索引
当我们想要在书里面查找某一个知识点时，一页一页找就显得很愚蠢，根据目录来查找才是正确的思路，而索引在数据库中起到的就是这么一个作用，索引就是为了帮助存储引擎快速获取到数据的一种数据结构。
存储引擎，简单来说就是如何存储数据，如何为存储的数据建立索引和如何更新，查询数据的实现方法。MySQL存储引擎有MyISAM、InnoDB、Memory，其中InnoDB在MySQL5.5之后成为默认的存储引擎
2.索引的分类
1. 按数据结构分类
B+tree索引、Hash索引、Full-text索引
在MySQL5.5之后InnoDB成为默认的存储引擎，B+tree索引类型也是MySQL存储引擎使用最多的索引类型
在创建表时，InnoDB存储引擎会根据不同的场景选择不同的列作为索引：
- 如果有主键，默认使用主键作为聚簇索引的索引键（key）
- 如果没有主键，就会选择第一个不包括NULL 值的唯一列作为聚簇索引的索引键（key）
- 在上面两个都没有的情况下，InnoDB将自动生成一个隐式自增id列作为聚簇索引的索引键（key）
其他索引都属于辅助索引，也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是B+Tree索引
B+Tree是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据都是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表
B+Tree 存储千万级的数据只需要3-4层高度就可以满足，这意味着从千万级的表查询目标数据最多需要3-4次磁盘I/O，所以 B+Tree 相比于B树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况下，查询一个数据的磁盘I/O依然维持在3-4次
主键索引的B+Tree 和二级索引的B+Tree区别：
- 主键索引的B+Tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里
- 二级索引的B+Tree的叶子节点存放的是主键值，而不是实际数据
二级索引查询过程：
- 先检索二级索引中的B+Tree的索引值，找到对应的叶子节点，获取主键值，
- 通过主键索引中的B+Tree查询到对应的叶子节点，然后获取整行数据。这个过程叫做回表，也就是说要查俩个B+树才能查到数据
当查询的数据是在二级索引的B+Tree的叶子节点里可以查询到，这时就不用再通过主键索引查
这种在二级索引的B+Tree就能查询到结果的过程就叫做覆盖索引，也就是只需要查一个B+Tree就能找到数据
为什么MySQL InnoDB选择B+tree作为索引的数据结构：
「女朋友问我：为什么 MySQL 喜欢 B+ 树？我笑着画了 20 张图（详解）
B+Tree相比于B树、二叉树或Hash索引结构的优势：
1.B+Tree vs B Tree
B+Tree只在叶子节点存储数据，而B树的非叶子节点也要存储数据，所以B+Tree的单个节点的数据量更小，在相同的磁盘I/O次数下，就能查询到更多的节点
另外，B+Tree叶子节点采用的是双链表连接（双向链表），适合MySQL中常见的基于范围的顺序查找，而B树无法做到这一点
2.B+Tree vs 二叉树
对于有N个叶子节点的B+Tree，其搜索复杂度为O（logdN）,其中d表示节点允许的最大子节点个数为d个
在实际应用中，d是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree的高度依然维持在34层左右，也就是说一次数据查询操作只需要做34次的磁盘I/O操作就能查询到目标数据
而二叉树的每个父节点的儿子节点个数只能是2个，意味着其搜索复杂度为O（logN）,这已经比B+Tree高出不少，因此二叉树检索到目标数据所经历的磁盘I/O次数要更多
3.B+Tree vs Hash
Hash在做等值查询的时候查询的效率非常高，搜索复杂度为O（1）
但是Hash表不适合做范围查询，它更适合做等值的查询，这也是B+Tree索引要比Hash表索引有着更广泛的适用场景的原因
2.按物理存储分类
聚簇索引（主键索引）、二级索引（辅助索引）
这两个的区别在前面也已经提到过：
- 主键索引的B+Tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里
- 二级索引的B+Tree的叶子节点存放的是主键值，而不是实际数据
所以，在查询时使用了二级索引，如果查询的数据在二级索引里查询到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引就能查询到数据量，这个过程就是回表
3.按字段特性分类
主键索引、唯一索引、普通索引、前缀索引
1.主键索引
主键索引就是建立在主键字段上的索引，通常在创建表时一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值
2.唯一索引
唯一索引建立在UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值
3.普通索引
普通索引就是建立在普通字段上的索引，既不要求字段为主键也不要求字段为UNIQUE
4.前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为char、varchar、binary、varbinary的列上。
使用前缀索引的目的是为了减少索引占据的存储空间，提升查询速率。
4.按字段个数分类
单列索引、联合索引（复合索引）
- 建立在单列上的索引称为单列索引，比如主键索引；
- 建立在多列上的索引称为联合索引
联合索引
通过将多个字段组合成一个索引，该索引就被称为联合索引
在使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循【最左匹配原则】，联合索引就会失效，这样就无法利用到索引快速查询的特性。
比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：
where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；
需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
where b=2；
where c=3；
where b=2 and c=3；
上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。利用索引的前提是索引里的key是有序的。
联合索引范围查询
联合索引有一些特殊情况，并不是查询过程中使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的B+Tree，部分字段没有用到联合索引的B+Tree的情况
这种特殊情况就发生在范围查询，联合索引的最左匹配原则会一直向右匹配直到遇到【范围查询】就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
<strong>联合索引的最左匹配原则，在遇到范围查询（如>、=、 1 and b = 2语句的时候，只有a字段能用到索引，那在联合索引的B+Tree找到的第一个满足条件的主键值（ID为2）后，还需要判断其他条件是否满足（看b是否等于2），那是在联合索引里判断？还是回主键索引去判断呢？
- 在MySQL5.6之前，只能从ID2（主键值）开始一个个回表，到【主键索引】上找出数据行，再对比b字段值
- 而MySQL5.6引入的索引下推，可以在联合索引遍历过程中，对联合索引中包含的字段优先做判断，直接过滤掉不满足条件的记录，减少回表次数。
索引区分度
另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段就越有可能被更多的SQL使用到
区分度就是某个字段column不同值的个数除以表的总行数，计算公式如下：
区分度 = distinct(column)/count(*)
联合索引进行排序
针对下面这条SQL，该如何通过索引来提高查询效率
select * from order where status = 1 order by create_time asc
单独给status建立索引
更好的方式是给status 和 create_time 列建立一个联合索引，因为这样可以避免MySQL数据库发生文件倒序
因为在查询时，如果只用到status的索引，但是这条语句换药对create_time排序，这时就要用文件排序filesort，也就是在SQL执行计划中，Extra列会出现Using filesort。
所以，要利用索引的有序性，在status 和 create_time列建立联合索引，这样根据status筛选后的数据就是按照create_time排好序的，避免在文件排序，提高了查询效率。
3.什么时候需要/不需要创建索引
索引最大的好处是提高查询速度，但索引也是有缺点的，比如：
- 需要占用物理空间，数量越多，占用空间越大
- 创建索引和维护索引要耗费时间，这种时间随数据量的增加而增大
- 会降低表的增删改的效率，因为每次增删改索引，B+树为了维护索引有序性，都需要进行动态维护
所以，索引不是万能钥匙，是根据场景来使用的
1.什么时候适用索引？
- 字段有唯一性限制的，比如商品编码
- 经常用于 WHERE查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引
- 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做异常排序了，因为我们都已经知道了建立索引之后再B+Tree中的记录都是排序好的
2.什么时候不需要创建索引
- WHERE 条件，GROUP BY ， ORDER BY里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的
- 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为MySQL还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描
- 表数据太少的时候，不需要创建索引
- 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的
4.有什么优化索引的方法
常见优化索引的方法：
- 前缀索引优化
- 覆盖索引优化
- 主键索引最好是自增的
- 防止索引失败
1.前缀索引优化
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那么为什么需要使用前缀来建立索引呢？
使用前缀索引是为了减小索引字段的大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度，在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小
不过，前缀索引有一定的局限性，例如：
- order by 就无法使用前缀索引
- 无法把前缀索引用作覆盖索引
2.覆盖索引优化
覆盖索引是指SQL中query的所有字段，在索引B+Tree的叶子节点上都能找的到的那些索引，从二级索引中查询的到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作
假设我们只需要查询商品的名称、价格有什么方式可以避免回表呢？
我们可以建立一个联合索引，即【商品ID、名称、价格】作为一个联合索引。如果索引中存在这些数据，查询将不会再次检查主键索引，从而避免回表
所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的I/O操作
3.主键索引最好是自增的
在建表时，都会默认将主键索引设置为自增的，具体为什么要这么做？有什么好处？
InnoDB 创建主键索引默认为聚簇索引，数据被存放在了B+Tree的叶子节点上。也就是说，每一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中
如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，将页面写满，就会自动开辟一个新页面。因为每次插入一条新纪录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会导致大量的内存碎片，导致索引结构不紧凑，从而影响查询效率
另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子结点越小（二级索引的叶子结点存放的数据是主键值），这样二级索引占用的空间也就越小。
4.索引最好设置为NOT NULL
- 索引列存在NULL 就回到导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为NULL的列会使索引、索引统计和值比较都更加复杂，比如进行索引统计时，count会忽略值为NULL的行
- NULL值是一个没有意义的值，但它会占用物理空间，所以会带来存储空间的问题，因为InnoDB存储记录的时候，如果表中存在允许为NULL的字段，那么行格式中至少会用1字节空间存储NULL值列表
5.防止索引失效
引发索引失效的情况：
- 当使用左或者左右模糊匹配的时候，也就是like %xx 或者 like %xx%这两种方式都会造成索引失效
- 当在查询条件中对索引列做了计算、函数、类型转换操作，这些情况都会造成索引失效
- 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效
- 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效
- 当字段类型为字符串 WHERE 时一定要用括号括起来，否则会因为隐式类型转换导致索引失效
- MySQL优化器估计使用全表扫描要比使用索引快，则不使用索引，索引失效
三、事务篇
1.事务有哪些特性
事务是由MySQL的引擎实现的，常见的InnoDB是支持事务的
不过并不是所有的引擎都支持事务，比如MySQL原生的MyISAM引擎就不支持事务，也正是如此，所有大多数MySQL的引擎都是用InnoDB
事务必须遵循的四个特性：
- 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会回滚到事务开始前的状态，就像这个事务没有执行过一样
- 一致性：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会互相干扰，每个事务都有一个完整的事务空间，对其他并发事务是隔离的
- 持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失
在InnoDB引擎中是如何保证事务的这四个特性的呢？
- 持久性是通过 redo log（重做日志）来保证的
- 原子性是通过 undo log （回滚日志）来保证的
- 隔离性是通过MVCC（多版本并发控制）或锁机制来保证的
- 一致性是通过持久性+ 原子性+隔离性来保证的
2.并行事务会引发什么问题
MySQL服务端是允许多个客户端连接的，这意味着MySQL会出现同时处理多个事务的情况
那么在同时处理多个事务的时候，就可能出现脏读、不可重复读、幻读的问题
1.脏读
如果一个事务【读到】了另一个【未提交事务修改过的数据】就意味着发生了【脏读】现象
假设有A和B两个事务同时自爱处理，事务A先开始从数据库中读取余额数据，然后再进行更新操作，如果此时事务A还没有提交事务，而此时事务B也正好从数据库中读取余额数据，那么事务B读取到的数据时刚才事务A更新后的数据，即使事务A没有提交事务
因为事务A是还没有提交事务的，也就是它随时可能发生回滚操作，如果在上面这种情况A事务A发生了回滚，那么事务B刚才读取到的就是过期的数据，这种现象就被称为脏读
2.不可重复读
在一个事务内多次读取同一个数据，如果出现前后两次读取到的数据不一样的情况，就意味着发生了【不可重复读】现象
假设有A和B这两个事务同时在处理，事务A先开始从数据库中读取余额数据，然后继续执行代码逻辑处理，在这过程中如果事务B更新了这条数据，并提交了事务，那么当事务A再次读取这条数据时就会发现前后两次读取到的数据是不一致的，这种现象就被称为不可重复读
3.幻读
在一个事务内多次查询某个符合条件的【记录数量】，如果出现前后两次查询到的记录数量不一致的情况，就意味着发生了【幻读】现象
假设有A和B这两个事务同时在处理，事务A先开始从数据库查询账户余额大于100万的记录，发现共有5条，然后事务B也按相同的搜索条件也是查询到5条记录，接下来事务A插入了一条余额超过100万的账号，并提交了事务，此时数据库中超过100万的账号个数就变为了6
然后事务B再次查询账户余额大于100万的记录，此时查询到的记录数量有6条，**发现和前一次读取到的记录数量不一样了，就感觉发生了幻觉一样，这种现象就称为
3.事务的隔离级别有哪些
多个事务并发执行时可能会遇到【脏读、不可重复读、幻读】的现象，这些现象会对事务的一致性产生不同程度的影响
- 脏读：读取到其他事务未提交的数据
- 不可重复读：前后读取的数据不一致
- 幻读：前后读取的记录数量不一致
严重性排序如下：
SQL标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，四个隔离级别如下：
- 读未提交：指一个事务还没提交时，它做的变更就能被其它事务看到
- 读提交：指一个事务提交之后，它做的变更才能被其它事务看到
- 可重复读：指一个事务执行过程中看到的数据，一直跟着这个事务启动时看到的数据是一致的，MySQL InnoDB引擎的默认隔离级别
- 串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等待前一个事务执行完成
隔离水平高低排序：
针对不同的隔离级别，并发事务可能发生的现象也不同：
也就是说：
- 在「读未提交」隔离级别下，可能发生脏读、不可重复读和幻读现象；
- 在「读提交」隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象；
- 在「可重复读」隔离级别下，可能发生幻读现象，但是不可能脏读和不可重复读现象；
- 在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。
也就是说： 在「读未提交」隔离级别下，可能发生脏读、不可重复读和幻读现象； 在「读提交」隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象； 在「可重复读」隔离级别下，可能发生幻读现象，但是不可能脏读和不可重复读现象； 在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。
所以，要解决脏读现象，就要升级到【读提交】以上的隔离级别；要解决不可重复读现象就要升级到【可重复读】的隔离级别，要解决幻读现象不建议将隔离级别升级到【串行化】
不同数据库厂商对SQL标准中规定的4中隔离级别的支持不一样，有的数据库只实现了其中几种隔离级别，我们讨论的MySQL虽然支持4种隔离级别，但与SQL标准中规定的隔离级别允许发生的现象却有些出入
MySQL 在【可重复读】隔离级别下，可以很大程度避免幻读现象的发生（主要是很大程度，不是彻底避免），所以MySQL并不会使用【串行化】隔离级别来避免幻读现象的发生，因为使用【串行化】隔离级别会影响性能
MySQL InnoDB引擎的默认隔离级别虽然是【可重复读】，但是它很大程度上避免幻读现象（）并不是完全解决（详解https://xiaolincoding.com/mysql/transaction/phantom.html），解决方案有两种：
- 针对快照读（普通select语句），是通过MVCC方式解决了幻读，因为可重复读隔离级别下事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题
- 针对当前读（select ... for update），是 通过next-key lock(记录锁+间隙锁)方式解决了幻读因为当执行select...for update语句的时候，会加上
next-key lock，如果有其他事务在next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以很好的避免了幻读问题
接下来举一个具体的例子来说明这四种隔离级别，有一张账户余额表，里面有一条账户余额为100万的记录。然后有两个并发的事务，事务A只负责查询余额，事务B则会会将我的余额改为200万，下面是按照时间顺序两个事务的行为;
在不同隔离级别在，事务A执行过程中查询到的余额可能会不同：
- 在【读未提交】隔离级别下，事务B修改余额之后，虽然没有提交事务，但是此时的余额已经可以被事务A看见了，于是事务A中余额V1查询到的值是200万，余额V2，V3自然也是200万
- 在【读提交】隔离级别下，事务B修改完余额后，因为没有提交事务，所以事务A中余额V1还是100万，等事务B提交完后，最新的余额数据才能被事务A看到，因此余额V2，V3还是200万
- 在【可重复读】隔离级别下，事务A只能看见启动事务时的数据，所以余额V1，余额V2的值都是100万，当事务A提交事务后，就能看见最新的余额数据量，所以余额V3是200万
- 在【串行化】隔离级别下，事务B在执行将余额100万修改为200万时，由于此前事务A执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务A提交后，事务B才可以继续执行，所以从A的角度看，余额V1,V2的值是100万，余额V3的值是200万
这四种隔离级别具体是如何实现的呢？
- 对于【读未提交】隔离级别的事务来说，因为可以读取到位提交事务修改的数据，所以直接读取最新的数据就好
- 对于【串行化】隔离级别的事务来说，通过加读写锁的方式来避免并行访问
- 对于【读提交】和【可重复读】隔离级别的事务来说，它们是通过Read View来实现的，它们的区别在于创建Read View的时机不同，可以把Read View理解为一个数据快照，就像相机拍照那样，定格某一时刻的风景。【读提交】隔离级别是在【每个语句执行前】都会重新生成一个Read View，而【可重复读】隔离级别是【启动事务时】生成一个Read View，然后整个事务期间都在用这个Read View
注意，执行【开始事务】命令，并不意味着启动了事务，在MySQL有两种开启事务的命令：
- begin/start transaction命令
- 执行之后并不代表事务启动了，只有在执行了这个命令后，执行了第一条select语句，才是事务真正启动的时机
- start transaction with consistent snapshot 命令
4.Read View 在MVCC里如何工作
需要了解俩个知识：
- Read View中四个字段作用
- 聚簇索引记录中两个跟事务有关的隐藏列
1.Read View有四个重要的字段：
- m_ids：指的是在创建Read View时，当前数据库中【活跃事务】的事务id列表，注意是一个列表，“活跃事务”指的就是启动了但还没提交的事务
- min_trx_id：指的是在创建Read View时，当前数据库【活跃事务】中事务id最小的事务，也就是m_ids的最小值
- max_trx_id：这个并不是m_ids的最大值，而是创建Read View时当前数据库中应该给下一个事务的id值，也就是全局事务中最大的事务id值+1
- creator_trx_id：指的是创建该 Read View 的事务的事务id
2.聚簇索引中的隐藏列
对于使用InnoDB存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：
- trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务id记录在trx_id隐藏列里
- roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录
在创建Read View 后，我们可以将记录中的trx_id划分为下面的三种情况：
一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几中情况：
- 如果记录的trx_id值小于Read View中的 min_trx_id 值，表示这个版本的记录是在创建Read View 前已经提交的事务生成的，所以该版本对当前事务可见
- 如果记录的trx_id 大于等于Read View 中的max_trx_id 值，表示，这个版本的记录是在创建Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见
- 如果记录的trx_id 值在Read View 的 min_trs_id 和 max_trs_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然在活跃着（还没有提交事务），所以该版本的记录对当前事务不可见
- 如果记录的 trx_id 不在 m_ids 列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的的记录对当前事务可见
这种通过【版本链】来控制并发事务访问同一个记录的行为就叫做 MVCC（多版本并发控制）
5.可重复读是如何工作的？
可重复读隔离级别是启动事务时生成一个 Read View ，然后整个事务期间都在用这个 Read View
假设事务A（事务id为51）启动后，紧接着事务B（事务id为52）也启动了，这两个事务创建的 Read View 如下：
事务A和事务B的Read View 具体内容如下：
- 在事务 A 的 Read View 中，它的事务 id 是 51，由于它是第一个启动的事务，所以此时活跃事务的事务 id 列表就只有 51，活跃事务的事务 id 列表中最小的事务 id 是事务 A 本身，下一个事务 id 则是 52。
- 在事务 B 的 Read View 中，它的事务 id 是 52，由于事务 A 是活跃的，所以此时活跃事务的事务 id 列表是 51 和 52，活跃的事务 id 中最小的事务 id 是事务 A，下一个事务 id 应该是 53。
接着，在可重复读隔离级别下，事务A和事务B按顺序执行了以下操作：
- 事务 B 读取小林的账户余额记录，读到余额是 100 万；
- 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
- 事务 B 读取小林的账户余额记录，读到余额还是 100 万；
- 事务 A 提交事务；
- 事务 B 读取小林的账户余额记录，读到余额依然还是 100 万；
具体分析：
事务B第一次读取小林的账户余额记录，在找到记录后，会先看这条记录的trx_id，此时发现trx_id为50，比事务B 的Read View 中的min_trx_id(51)还小，这意味着修改这条记录的事务在事务B之前提交了，所以该版本的记录对事务B 是可见的，也就是说事务B 可以获取到这条记录
接着，事务A通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改为200万，这时MySQL会记录相应的 undo log，并以链表的方式串联起来，形成版本链，如下图：
可以在【记录的字段】看到，由于事务A修改了该记录，以前的记录就变成了旧记录，于是最新记录和旧版本记录通过链表的方式串起来，而且最新记录的trx_id是事务A的事务id（trx_id = 51）
然后事务B第二次去读取该记录，发现这条记录的trx_id值为51，在事务B的Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 是否在m_ids范围内，判断的结果是在的，那说明这条记录是被还未提交的事务修改的，这时事务B并不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到找到trx_id【小于】事务B的 Read View中的min_trx_id值的第一条记录，所以事务B 能读取到的是trx_id为50的记录，也就是小林余额是100万这条记录。
最后，当事务A提交事务后，由于隔离级别是【可重复读】，所以事务B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事务A将小林的余额修改为200万并提交了事务，事务B第三次读取记录时，读到的记录都是小林余额是100万这条记录
就是通过这样的方式实现了【可重复读】隔离级别下在事务期间读到的记录都是事务启动前的记录
6.读提交是如何工作的？
读提交隔离级别是在每次读取数据时，都会生成一个新的Read View
也意味着，事务期间多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务
那么读提交隔离级别是如何工作的呢？沿用上面的例子
假设事务A（事务id为51）启动后，紧接着事务B（事务id为52）也启动了，接着按顺序执行以下操作：
- 事务 B 读取数据（创建Read View），读到余额是 100 万；
- 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
- 事务 B 读取小林的账户余额记录（创建Read View），读到余额还是 100 万；
- 事务 A 提交事务；
- 事务 B 读取小林的账户余额记录，读到余额依然还是 200 万；
前两次事务B每次读取数据时创建的Read View如下：
对于事务B第二次读数据时，读不到事务A（还未提交事务）修改数据的原因：
事务B在找到小林这条记录时，会看这条记录的trx_id是51，在事务B的Read View的min_trx_id 和 max_trx_id 之间，那么说明这条记录是被还未提交的事务修改的，这时事务B不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到 找到trx_id小于事务B中Read View 中的 min_trx_id 值的第一条记录，所以事务B能读到的是 trx_id 为50的记录，也就是余额为100万的这条记录
事务A提交后，事务B可以读到书屋A修改的数据：
在事务A提交后，由于隔离级别是篇【读提交】，所以事务B在每次读数据时，会重新创建Read View，此时事务B第三次读取数据时创建的Read View如下：
事务B在找到小林这条记录的时候，会发现这条记录的trx_id是51，比事务B的Read View 中的 min_trx_id(52)小，这意味着修改这条记录的事务在创建Read View前已经提交，所以该版本的记录对事务B是可见的
正是因为在读提交隔离级别下，事务每次读取数据时都会重新创建Read View，那么在事务期间的多次读取同一条数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务
7.总结：
事务是在+MySQL+引擎层实现的，我们常见的+InnoDB+引擎是支持事务的，事务的四大特性是原子性、一致性、隔离性、持久性，我们这次主要讲的是隔离性。
当多个事务并发执行的时候，会引发脏读、不可重复读、幻读这些问题，那为了避免这些问题，SQL+提出了四种隔离级别，分别是读未提交、读已提交、可重复读、串行化，从左往右隔离级别顺序递增，隔离级别越高，意味着性能越差，InnoDB+引擎的默认隔离级别是可重复读。
要解决脏读现象，就要将隔离级别升级到读已提交以上的隔离级别，要解决不可重复读现象，就要将隔离级别升级到可重复读以上的隔离级别。
而对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差。MySQL+InnoDB+引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，详见这篇文章https://xiaolincoding.com/mysql/transaction/phantom.html+），解决的方案有两种：
- 针对快照读（普通+select+语句），是通过+MVCC+方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
- 针对当前读（select+...+for+update+等语句），是通过nextkey+lock（记录锁+间隙锁）方式解决了幻读，因为当执行select...for update语句的时候，会加上next-key lock，如果有其他事务在next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通Read View 来实现的，它们的区别在于创建 Read View 的时机不同：
- 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
- 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。
这两个隔离级别实现是通过「事务的Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫MVCC（多版本并发控制）。
在可重复读隔离级别中，普通的select语句就是基于MVCC实现的快照读，也就是不会加锁的。而select+...for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上next-key lock锁。
四、锁篇
1.MySQL有哪些锁
在MySQL中，根据加锁的范围可以分为全局锁、表级锁和行锁三类。
1.全局锁
1.1全局锁如何使用？
要使用全局锁，则要使用下面这条命令：- flush tables with read lock
- 对数据的增删改操作，比如insert、delete、 update等语句
- 对表结构的更改操作，比如 alter table 、 drop table
释放全局锁：当然，当对话断开了，全局锁会自动释放
1.2全局锁的应用场景是什么？
全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，从而出现备份文件的数据与预期的不一样
1.3全局锁的缺点
加上全局锁，意味着整个数据库都是只读状态
如果数据库里面有很多数据，备份就会花费很多时间，关键是备份期间，业务只能读取数据，而不能更新数据，这样会导致业务停滞
1.4 如何避免全局锁使用时对业务的影响
如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建Read View，然后整个事务执行期间都在用这个Read View，而且由于MVCC的支持，备份期间业务依然可以对数据进行更新操作
因为在可重复读隔离级别下，即使其他事务更新了表的数据，也不影响备份数据库时的Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据
备份数据库的工具是 mysqldump ，在使用 mysqldump 时加上 -single-transaction参数的时候，就会在备份数据库时先开启事务。这种方法只适用于支持【可重复读隔离级别】的存储引擎
InnoDB存储引擎默认的事务隔离级别就是可重复读，因此可以采用这种方式来备份数据库
但是，对于MyISAM这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法
2.表级锁
MySQL中的表级锁：
- 表锁
- 元数据锁（MDL）
- 意向锁
- AUTO-INC锁
1.表锁
如果我们想对学生表（t_student）加表锁，可以使用以下命令：、、- //表级别的共享锁，也就是读锁
- //允许当前会话读取被锁定的表，但阻止其他会话对这些表这些写操作
- lock tables t_student read;
- //表级别的独占锁，也就是写锁
- //允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）
- lock table t_student write;
例如：
如果某个线程A中执行 lock tables t1 read, t2 write,也就是对t_test表加了表级别的共享锁。此时本线程（会话）可以读t_test表的数据，但不能写t_test的数据同时本线程不能访问其他表，其他线程可以对t_test表进行读操作，但是也不能对t_test表进行写操作，这时候写操作会发生阻塞
释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：当会话退出后，也会释放所有表锁
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式，不过尽量避免在使用InnoDB引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 厉害的地方在于实现了颗粒度更细的行级锁
2.元数据锁（MDL）
对于元数据锁（MDL），我们不需要显示的使用MDL，因为当我们对数据库进行操作时，会自动给这个表加上MDL：
- 对一张表进行CRUD操作时，加的是MDL读锁
- 对一张表做结构变更的时候，加的是MDL写锁
MDL是为了保证当用户对表执行CRUD操作时，防止其他线程对这个表结构做了变更。
当有线程在执行 select 语句（加MDL读锁）的期间，如果有其他线程执行了 CRUD操作（申请MDL读锁），那么就会被阻塞，直到表结构变更完成（释放MDL写锁）
1.MDL不需要显示调用，那什么时候释放
MDL是在事务提交后才会释放，这意味着事务执行期间，MDL是一直持有的
如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没有提交），那在对表结构做变更操作的时候，可能会出现意想不到的事情，比如下面这个顺序的场景：
- 首先，线程A先启用了事务（但是一直不提交），然后执行一条select语句，此时就对该表加上MDL读锁
- 然后，线程B也执行了同样的select语句，此时并不会阻塞，因为【读读】并不冲突
- 接着，线程C修改了表字段，此时由于线程A的事务并没有提交，也就是MDL读锁还在占用着，这时候线程C就无法申请到MDL写锁，就会被阻塞
那么在线程C阻塞后，后续对该表的select语句就都会阻塞，如果此时有大量该表的select语句的请求到来，就会有大量的线程阻塞住，这时数据库的线程很快就会爆满了
2.为什么线程C申请不到MDL写锁，后续的申请读锁的查询操作也会阻塞
这是因为申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现了MDL写锁等待，会阻塞后续该表的所有CRUD操作
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了MDL读锁，如果可以考虑kill掉这个长事务，然后再做表结构的变更
3.意向锁
- 在使用InnoDB引擎的表里对某些记录加上【共享锁】，需要先在表级别加上一个【意向共享锁】
- 在使用InnoDB引擎的表里对某些记录加上【独占锁】，需要先在表级别加上一个【意向独占锁】
也就是，当执行插入、更新、删除操作，需要现对表加上【意向独占锁】，然后对该记录加独占锁，而普通的select是不会加行级锁的，普通的select是利用MVCC实现一致性读，无锁，不过select也是可以对记录加共享锁和独占锁的：- //先在表上加上意向共享锁，然后对读取的记录加共享锁
- select ... lock in share mode;
- //先表上加上意向独占锁，然后对读取的记录加独占锁
- select ...for update
表锁和行锁是满足读读共享、读写互斥、写写互斥的
如果没有【意向锁】那么加【独占表锁】时，就需要遍历表里所有
记录，查看是否有记录存在独占锁，这样效率就会很慢
那么有了【意向锁】，由于在对记录加独占锁前，先回加上表级别的意向独占锁，那么在加【独占表锁】时，直接查询该表是否有意向独占锁，如果有就意味着表里已经有记录被家里独占锁，这样就不用去遍历表里的记录
所以，意向锁的目的是为了快速判断表里是否有记录被加锁
4.AUTO-INC锁
表里的主键通常都会设置为自增，这是通过对主键字段声明 AUTO_INCREMENT属性实现的，之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过AUTO_INC锁实现的
AUTO_INC锁是特殊的表锁机制，锁不是在一个事务提交后再释放，而是在执行完插入语句后就会立即释放，在插入数据时，会加一个表级别的AUTO_INC锁，然后被 AUTO_INCREMENT修饰的字段赋值递增的值，等插入语句执行完成后，才会把AUTO_INC锁释放掉
那么，一个事务在持有AUTO_INC锁的过程中，其他事务如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是AUTO_INC锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。
因此，在MySQL 5.1.22版本开始，InnoDB存储引擎提供了一种轻量级的锁来实现自增。一样也是自爱插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，不需要等到整个插入语句执行完成后才释放锁
InnoDB存储引擎提供了innodb_autoinc_lock_mode的系统变量，是用来控制选择用 AUTO_INC锁，还是轻量级的锁
- 当innodb_autoinc_lock_mode = 0 ，就采用AUTO_INC锁，语句执行结束才释放锁
- 当innodb_autoinc_lock_mode = 2,就采用轻量级锁，申请自增主键后就释放锁，并不需要等待语句执行完成后才释放
- 当innodb_autoinc_lock_mode = 1:
- 普通 insert 语句，自增锁在申请之后就马上释放
- 类似 insert...select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放
当innodb_autoinc_lock_mode = 2是性能最高的方式，但搭配binlog的日志格式是statement 一起使用时，在【主从复制的场景】中会发生数据不一致的问题
在插入数据时，会加一个表级别的AUTO_INC锁，然后被 AUTO_INCREMENT修饰的字段赋值递增的值，等插入语句执行完成后，才会把AUTO_INC锁释放掉
3.行级锁
InnoDB是支持行级锁的，而MyISAM引擎并不支持行级锁
前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读- //对读取的记录加共享锁
- select ... lock in share mode;
- //对读取的记录加独占锁
- select ... for update
共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥，读写互斥
行级锁的类型需要有三类：
- Record Lock：记录锁，也就是仅仅把一条记录锁上
- Gap Lock:间隙锁，锁定一个范围，但是不包括记录本身
- Next-Key Lock: Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身
1.Record Lock
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有S锁和X锁之分的：
- 当一个事务对一条记录加了S型记录锁后，其他事务也可以继续对该记录加S型记录锁（S锁和S锁兼容），但是不可以对该记录加X型记录锁（S型与X型不兼容）
- 当一个事务对一条记录加了X型记录锁后，其他事务既不可以对该记录加S型记录锁（S型与X型不兼容），也不可以对该记录加X型记录锁（X型和X型不兼容）
当事务执行commit 后，事务过程中生成的锁都会被释放
2.Gap Lock
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
假设，表中有一个范围id为（3,5）间隙锁，那么其他事务就无法插入id = 4这条记录，这样就有效的防止幻读现象的发生
间隙锁虽然存在X型间隙锁和S型间隙锁，但是并没有什么区别，间隙锁直接是兼容的，即两个事务可以同时持有包含公共间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是为了防止插入幻影记录而提出来的
3.Next-Key Lock
Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身
假设，表中有一个范围id为（3,5】的next-key lock，那么其他事务即不能插入id = 4这条记录，也不能修改id = 5这条记录
所以，next-key lock 既能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中
next-key lock 是包含间隙锁 + 记录锁的，如果一个事务获取了X型的next-key lock，那么另一个事务自爱获取相同范围的X型的next-key lock时，会被阻塞的
比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。
虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。
4.插入意向锁
一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务添加了间隙锁（next-key lock）也包含间隙锁
如果有的话，插入操作会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态
举例，假设事务A已经对表加了一个范围id为（3,5）间隙锁
当事务A还没提交的时候，事务B向该表插入一条id = 4 的新纪录，这时会判断带插入的位置已经被事务A加了间隙锁，于是事务B会生成一个插入意向锁，然后将锁的状态设置为等待状态（MySQL加锁时，是先生成锁结构，然后设置锁状态，如果锁状态是等待状态，并不是意味着事务成功获取到了 锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），这时事务B就会发生阻塞，直到事务A提交了事务
插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁
如果说间隙锁锁住的是一个区间，那么【插入意向锁】锁住的就是一个点，因而从这个角度来说，插入间隙锁确实是一种特殊的间隙锁
插入意向锁与间隙锁的另一个非常重要的差别是：尽管【插入意向锁】也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间的插入意向锁（当然插入意向锁如果不在间隙锁区间内还是可以的）
2.MySQL是如何加锁的
1.什么SQL语句会加行级锁？
普通的select语句是不会对记录加行级锁的（除了串行化隔离级别），因为它属于快照读，是通过MVCC（多版本并发控制）实现的
如果要在查询时对记录加行级锁，可以使用下面的方式，这两种会加锁的语句称为锁定读- //对读取的记录加共享锁（S型锁）
- select ... lock in share mode;
- //对读的记录加独占锁（X型锁）
- select ... for update;
除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁（X型锁）- //对操作的记录加独占锁（X型锁）
- update table ... where id = 1;
- //对操作的记录加独占锁（X型锁）
- delete from table where id = 1;
2.行级锁有哪些种类
不同隔离级别下，行级锁的种类是不同的：
- 在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上
- 在可重复读隔离级别下，行级锁的种类，除了有记录锁，还有间隙锁（目的是为了避免幻读）
所以行级锁的种类主要有三类：
- Record Lock : 记录锁，也就是仅仅把一条记录锁上
- Gap Lock : 间隙锁，锁定一个范围，但是不保护记录本身
- Next-Key Lock : Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身
1.Record Lock
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有S锁和X锁之分的：
- 当一个事务对一条记录加了S型记录锁后，其他事务也可以继续对该记录加S型记录锁（S锁和S锁兼容），但是不可以对该记录加X型记录锁（S型与X型不兼容）
- 当一个事务对一条记录加了X型记录锁后，其他事务既不可以对该记录加S型记录锁（S型与X型不兼容），也不可以对该记录加X型记录锁（X型和X型不兼容）
当事务执行commit 后，事务过程中生成的锁都会被释放
2.Gap Lock
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
假设，表中有一个范围id为（3,5）间隙锁，那么其他事务就无法插入id = 4这条记录，这样就有效的防止幻读现象的发生
间隙锁虽然存在X型间隙锁和S型间隙锁，但是并没有什么区别，间隙锁直接是兼容的，即两个事务可以同时持有包含公共间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是为了防止插入幻影记录而提出来的
3.Next-Key Lock
Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身
假设，表中有一个范围id为（3,5】的next-key lock，那么其他事务即不能插入id = 4这条记录，也不能修改id = 5这条记录
所以，next-key lock 既能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中
next-key lock 是包含间隙锁 + 记录锁的，如果一个事务获取了X型的next-key lock，那么另一个事务自爱获取相同范围的X型的next-key lock时，会被阻塞的
比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。
虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。
3.MySQL是怎么加行级锁的？
行级锁的加锁规则比较复杂，不同的场景，加锁的形式是不同的
加锁的对象是索引，加锁的基本单位是 next-key lock ，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间
但是 next-key lock 在一些场景（在能使用记录锁或间隙锁就能避免幻读现象的场景）下会退化成记录锁或间隙锁
本次讲解使用表结构以及记录：- CREATE TABLE `user` (
- `id` bigint NOT NULL AUTO_INCREMENT,
- `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
- `age` int NOT NULL,
- PRIMARY KEY (`id`),
- KEY `index_age` (`age`) USING BTREE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1.唯一索引等值查询
当我们使用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：
- 当查询的记录是【存在】的，在索引树上定位到这一条数据后，将该记录中的 next-key lock 会 退化成【记录锁】
- 当查询到的记录是【不存在】的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 退化成【间隙锁】
具体说明：
1.记录存在的情况
假设事务A执行了这条等值查询语句，查询的记录是【存在】于表中的- mysql> begin;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from user where id = 1 for update;
- +----+--------+-----+
- | id | name | age |
- +----+--------+-----+
- | 1 | 路飞 | 19 |
- +----+--------+-----+
- 1 row in set (0.02 sec)
接下来，如果有其他事务，对id为1的记录进行更新或删除操作的话，这些操作都会被阻塞，因为更新或删除也会对记录加X型的记录锁，而X锁和X锁之间是互斥关系
比如下面这个例子：
因为事务A对id = 1的记录加了X型的记录锁，所以事务B在修改id = 1的记录会被阻塞，事务C在删除id = 1的记录时也会发生阻塞
【有什么命令可以分析加了什么锁】
可以通过 select * from performance_schema.data_locks\G;这条语句，查看事务执行SQL过程中加了什么锁
以上面事务A为例：
可以看出一共加了两个锁：
图中LOCK_TYPE = RECORD 中的RECORD 表示行级锁，而不是记录锁
通过LOCK_MODE 可以确认是 next-key lock 还是间隙锁，还是记录锁
- 如果LOCK_MODE 为 X ，说明是 next-key lock
- 如果 LOCK_MODE 为 X，REC_NOT_GAP, 说明是记录锁
- 如果 LOCK_MODE 为 X，GAP， 说明是间隙锁
因此，此时事务A在 id = 1 记录的主键索引上加的是记录锁，锁住的范围是id为1的这条记录，这样其他的事务就无法对id为1这条记录进行更新和删除操作了
从这里我们可以得知，加锁的对象是针对索引，因为这里查询语句扫描的B+树是聚簇索引树，即主键索引树，所以是对主键索引加锁。将对应记录的主键索引加记录锁后，就意味着其他事务无法对该记录进行更新和删除操作了
【为什么唯一索引等值查询并且查询记录存在的场景下，该记录的next-key lock 会退化成记录锁】
原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题
幻读的定义就是，当一个事务前后两次查询的结果集不相同时，就认为发生了幻读。所以要避免幻读，就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新纪录，这样前后两次查询的结果集就不会出现不相同的情况
- 由于主键具有唯一性，所以其他事务插入 id = 1的时候，会因为主键冲突，导致无法插入 id = 1的新纪录。这样事务A在多次查询 id = 1 的记录的时候，不会出现前后两次查询到的结果集不同，也就避免了幻读的问题
- 由于对id = 1加了记录锁，其他事务无法删除该记录，这样事务A在多次查询 id = 1的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题
2.记录不存在的情况
假设事务A执行了这条等值查询语句，查询的记录是【不存在】于表中的- mysql> begin;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from user where id = 2 for update;
- Empty set (0.03 sec)
可以看到一共加了两个锁：
因此，此时事务A在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是（1,5）
接下来，如果有其他事务插入id值为2、3、4这一些记录的话，这些插入语句都会发生阻塞
注意，如果其他事务插入的id = 1或者 id = 5 的记录的话，并不会发生阻塞，而是报主键冲突的错误，因为表中已经出现了 id = 1和 id = 5的记录了
比如：
因为事务A在 id = 5的主键索引上加上了范围为（1,5）的X型间隙锁，所以事务B在插入一条id = 3的记录会被阻塞住，即无法插入 id = 3 的记录
间隙锁的范围（1,5），是怎么确定的？
如果LOCK_MODE 是 next-key lock 或者间隙锁，那么LOCK_DATA就表示锁的范围的右边界，此次的事务A的LOCK_DATA是5
然后锁范围的【左边界】是表中id为5的上一条记录的id值，即1
因此，间隙锁的范围（1,5）
为什么唯一索引等值查询并且查询记录【不存在】的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录索引中的 next-key lock会退化成【间隙锁】？
原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。
- 为什么 id = 5记录上的主键索引的锁不可以是 next-key lock ？如果是 next-key lock，就意味着其他事务无法删除id = 5这条记录，但是这次案例是查询id = 2的记录，只要保证前后两次查询id = 2的结果集相同，就能避免幻读的问题了，所以即使id = 5被删除，也不会有什么影响，那就没有必要加next-key lock，因此只需要在id = 5加间隙锁，避免其他事务插入id = 2的新纪录就可以
- 为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景在查询的记录是不存在的，自然就没办法锁住这条不存在的记录。
2.唯一索引范围查询
范围查询和等值查询的加锁规则是不同的
当唯一索引进行范围查询的时候，会对每一个扫描到的所有加next-key lock，然后如果遇到下面这些情况，会退化成记录锁或间隙锁：
- 情况一：针对【大于等于】的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中的，那么该记录的索引中的 next-key lock会退化成记录锁
- 情况二：针对【小于或者小于等于】的范围查询，要看条件值得记录是否存在于表中：
- 当条件值得记录不在表中，那么不管是【小于】还是【小于等于】条件的范围查询，扫描到种植范围查询的记录时，该记录的索引的 next-key lock 会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加next-key lock
- 当条件值得记录在表中，如果是【小于】条件的范围查询，扫描到的记录都是在这些记录的索引上加 next-key lock ；如果【小于等于】条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key lock会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key lock；如果【小于等于】条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key lock 不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key lock
3.非唯一索引等值查询
当我们用非唯一索引进行等值查询时，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，会同时对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁
针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：
- 当查询的记录【存在】时，由于不是唯一索引，所以存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key lock，而对于第一个不符合条件的二级索引记录，该二级索引的next-key lock 会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁
- 当查询的记录【不存在】时，扫描到第一条不符合条件的二级索引记录，该二级索引的next-key lock 会退化成间隙锁。因为不存在满足查询条件的值，所以不会对主键索引加锁
4.非唯一索引范围查询
非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会退化成间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录都是加 next-key lock- mysql> begin;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from user where age >= 22 for update;
- +----+-----------+-----+
- | id | name | age |
- +----+-----------+-----+
- | 10 | 山治 | 22 |
- | 20 | 香克斯 | 39 |
- +----+-----------+-----+
- 2 rows in set (0.01 sec)
- 最先开始找的第一行是 age = 22，虽然范围查询语句包含等值查询，但是这里不是唯一索引范围查询，所以是不会发生退化锁的现象，因此对该二级索引的记录加 next-key lock，范围是（21，22】。同时，对age = 22这条记录的主键索引加记录锁，及对 id = 10这一行记录的主键索引加记录锁
- 由于是范围查询，接着继续扫描已经存在的二级索引记录。扫描的第二行是 age = 39 的二级索引记录，于是对该二级索引加 next-key lock，范围是（22,39】，同时，对 age = 39这条记录的主键索引加记录锁，即对 id = 20这一行的主键索引加记录锁
- 虽然我们看到表中最后一条二级索引记录是age = 39 的记录，但是实际在InnoDB存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremumpseudo-record ，所以扫描第二行时，也就扫描到这个特殊记录的时候，会对该二级索引记录加的是范围为（39， +∞】的next-key lock
- 停止查询
可以看到，事务A对主键索引和二级索引都加了X型的锁：
- 主键索引（id列）：
- 在id = 10这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10这一行记录
- 在id = 20这条记录的主键索引上，加了记录锁，意味着其他事物无法更新或者删除id = 20这一行记录
- 二级索引（age列）：
- 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于是否可以插入 age = 21 和 age = 22 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
- 在 age = 39 这条记录的二级索引上，加了范围为 (22, 39] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 39 的这一些记录，也无法插入 age 值为 23、24、25、...、38 的这一些新记录。不过对于是否可以插入 age = 22 和 age = 39 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
- 在特殊的记录（supremum pseudo-record）的二级索引上，加了范围为 (39, +∞] 的 next-key 锁，意味着其他事务无法插入 age 值大于 39 的这些新记录。
在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22记录的二级索引上的 next-key lock退化成记录锁？
因为 age 字段是非唯一索引，不具有唯一性，索引如果只加记录锁（记录锁无法防止插入，只能防止删除或更改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不同，出现了幻读现象
5.没有加索引的查询
前面的案例，我们的查询语句都有使用索引查询，也就是查询记录的时候，是通过索引扫描的方式查询的，然后对扫描出来的记录进行加锁。
如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。
因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题
4.总结
MySQL行级锁的加锁规则：
唯一索引等值查询：
- 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
- 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。
非唯一索引等值查询：
- 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
- 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于：
- 唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
- 非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。
其实理解 MySQL 为什么要这样加锁，主要要以避免幻读角度去分析，这样就很容易理解这些加锁的规则了。
还有一件很重要的事情，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。
加锁流程图：
唯一索引（主键索引）加锁的流程图如下。（注意这个流程图是针对「主键索引」的，如果是二级索引的唯一索引，除了流程图中对二级索引的加锁规则之外，还会对查询到的记录的主键索引项加「记录锁」，流程图没有提示这一个点，所以在这里用文字补充说明下）
非唯一索引加锁的流程图：
五、日志篇
MySQL日志：undo log、redo log、binlog有什么用？
执行一条查询语句的过程属于【读】一条记录的过程，如下图：
那么，执行一条update语句，期间发生了什么，比如下面这一条：- UPDATE t_user SET name = 'xaiolin' WHERE id = 1;
- 客户端先通过连接器建立连接，连接器自会判断用户身份
- 因为这是一条 update 语句，所以不需要进过查询缓存，但是表上有更新语句，是会把整个表的查询缓存清空的，所以说查询缓存很鸡肋，在MySQL8.0就被移除这个功能了
- 解析器会通过词法分析识别出关键字update，表名等等，构建出语法书，接着还会做语法分析，判断输入的语句是否符合MySQL语法
- 预处理器会判断表和字段是否存在
- 优化器确定执行计划，因为where 条件中 id 是主键索引，所以决定要用id这个索引
- 执行器负责具体执行，找到这一行，然后更新
不过，更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志）、binlog（归档日志）这三种日志：
- undo log：是InnoDB存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC
- redo log ： 是InnoDB存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复
- binlog ： 是Server层生成的日志，主要用于数据备份和主从复制
1.为什么需要undo log？
我们在执行一条“增删改”语句时，虽然没有输入 begin 开启事务和 commit 提交事务，但是MySQL会隐式开启事务来执行“增删改”语句，执行完就自动提交事务，这样就保证执行完“增删改”语句后，我们可以及时在数据库表中看到对应结果
执行一条语句是否自动提交事务，是由 【autocommit】 参数决定的，默认是开启的，所以执行一条 update 语句也是会使用事务的
那么当一个事务自爱执行过程中，还没有提交事务时，如果MySQL发生崩溃，如何回滚到事务之前的数据？
如果我们每次在事务执行过程中，都记录下来回滚时需要的信息到一个日志里，那么在事务执行途中发生了MySQL崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据
实现这一机制就是 undo log（回滚日志），它保证了事务的ACID特性中的原子性
undo log 是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到undo log 日志文件中，当事务回滚时，可以利用undo log来进行回滚：
每当InnoDB引擎对一条记录进行操作（增删改）时，要把回滚时需要的信息都记录到undo log里，比如：
- 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键对应的记录删除就好了
- 在删除一条记录的时候，要把这条记录中的内容都记下来，这样之后回滚再把由这内容组成的记录插入到表中就好了
- 在更新一条记录时，要把被更新的旧值记下来，这样之后回滚时再把这些值更新为旧值就好
在发生回滚时就读取undo log里的数据，做原先操作的相反操作
针对delete 和 update操作会有一些特殊的处理：
- delete 操作实际上是不会立即直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的
- update分为两种操作：update的列是否是主键列
- 如果不是主键列，在undo log中直接反向记录是如何 update，即update是直接进行的
- 如果是主键列，update分两步执行：先删除该行，再插入一条目标行
一条记录的每一次更新操作产生的undo log格式都有一个 roll_pointer指针和一个trx_id事务id：
- 通过trx_id可以知道该记录是被哪一个事物修改的
- 通过roll_pointer指针可以将这些undo log串成一个链表，这个链表就被称为版本链
另外，undo log还有一个作用，通过Read View + undo log 实现MVCC（多版本并发控制）
对于【读提交】和【可重复读】隔离级别的事务来说，它们的快照读（普通的select语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：
- 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
- 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。
这两个隔离级别实现是通过【事务的Read View里的字段】和【记录中的两个隐藏列（trx_id和roll_pointer）】的对比，如果不满足可见性，就会顺着undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录的行为，这就叫做MVCC
因此，undo log 两大作用：
- 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了ROLLBACK语句，MySQL可以利用undo log中的历史数据将数据恢复到事务开始之前的状态
- 实现MVCC（多版本并发控制）关键因素之一:MVCC是通过Read View + undo log实现的。undo log为每条记录保存多分历史数据。MySQL在执行快照读（普通select语句）的时候，会根据事务的 Read View 里的信息，顺着undo log 的版本链找到满足其可见性的记录
undo log 如何刷盘（持久化到磁盘）的？
undo log 和数据页的刷盘策略是一样的，都需要通过redo log保证持久化
buffer pool 中有undo 页，对undo 页的修改也会记录到redo log。redo log会每秒刷盘，提交事务时也会刷盘，数据页和undo页都是靠这个机制保证持久化的
2.为什么需要Buffer Pool？
MySQL的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后再内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？
当然是缓存起来好，这样再有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘中获取数据了
为此，InnoDB存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能
有了Buffer Pool后：
- 当读取数据时，如果数据存在于Buffer Pool中，客户端就会直接读取Buffer Pool中的数据，否则再去磁盘中读取
- 当修改数据时，如果数据存在于Buffer Pool中，那直接修改 Buffer Pool中数据所在的页，然后将其设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘中
Buffer Pool 缓存什么？
InnoDB会把存储的数据划分为若干个【页】，，以页作为磁盘和内存交互的基本单位，一个页的默认大小为16KB，因此Buffer Pool同样需要按【页】来划分
在MySQL启动的时候，InnoDB会为 Buffer Pool 申请一篇连续的内存空间，然后按照默认的 16KB 的大小划分出一个个的页，Buffer Pool中的页就叫做缓冲页.此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到Buffer Pool中
所以，MySQL刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发页中断，申请物理空间内存，接着将虚拟地址和物理地址建立映射关系
Buffer Pool 除了缓存【索引页】和【数据页】，还包括了Undo 页，插入缓存，自适应哈希索引，锁信息等等
【Undo 页记录的是什么】
开启事务后，InnoDB层更新前，首先要记录相应的undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条undo log，undo log会写入Buffer Pool中的Undo 页面
【查询一条记录，就只需要缓存一条记录吗？】
不是的
当我们查询一条记录时，InnoDB是会把整个页的数据加载到Buffer Pool中，将页加载到Buffer Pool后，再通过页里的【页目录】去定位到某条具体的记录
3.为什么需要redo log？
Buffer Pool是提高了读写效率没错，但是问题来了，Buffer Pool是基于内存的，而内存总是不可靠的，万一断电重启，还没来得及落盘的脏页数据就会丢失
为了防止断电导致的数据丢失的问题，当有一条记录需要更新时，InnoDB引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以redo log的形式记录下来，这时候更新就算完成了
后续，InnoDB引擎会在适当的时候，由后台线程将缓存在Buffer Pool的脏页刷新到磁盘里，这就是 WAL（Write-Ahead Logging）技术
WAL技术指的是，MySQL的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时机再写到磁盘上
过程如下：
【什么是redo log】
redo log是物理日志，记录了每个数据页做了什么修改，比如对XXX表中的YYY数据页ZZZ偏移量的位置做了AAA更新，每当执行一个事务就会产生这样一条或者多条物理日志
在事务提交时，只要先将redo log持久化到磁盘即可，可以不需要等到将缓存在Buffer Pool里的脏页数据持久化到磁盘
当系统崩溃时，虽然脏页数据没有持久化，但是redo log已经持久化，就这MySQL重启后，可以根据redo log的内容，将所有数据恢复到最新的状态
【被修改 Undo log页面，需要记录对应redo log 吗】
需要的
开启事务后，InnoDB层更新记录前，首先要记录相应的undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log会写入Buffer Pool中的Undo 页面
不过，在内存修改该Undo 页面后，也是需要记录对应的redo log，因为undo log也要实现持久性的保护
【redo log和 undo log 区别在哪】
这两种日志是属于InnoDB存储引擎的日志，它们的区别在于：
- redo log记录了此次事务【修改后】的数据状态，记录的是更新之后的值，主要用于事物崩溃恢复，保证了事务的持久性
- undo log 记录了此次事务【修改前】的数据状态，记录的是更新之前的值，主要用于事物回滚，保证事务的原子性
事务执行之前发生了崩溃（这里的崩溃不是宕机崩溃，而是事物执行错误，mysql还是正常运行，如果是宕机崩溃的话，其实就不需要通过 undo log 回滚了，因为事务没有提交，事务的数据并不会持久化，还是在内存中，宕机崩溃了数据就丢失了，反正事物都没有提交成功，所以数据本身就是无语的，丢失了就丢失了），重启后会通过 undo log 回滚事务
事务提交之后发生了崩溃（这里的崩溃指的是宕机崩溃），重启后会通过 redo log恢复事务：
所以有了 redo log ，再通过WAL技术，InnoDB就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来 ， redo log 保证了事务四大特性中的持久性
【redo log 要写到磁盘中，数据也要写磁盘，为什么要多此一举？】
写入 redo log 的方式使用了追加操作，索引磁盘操作是 顺序写，而写入数据需要先找到写入位置，然后才能写到磁盘，所以磁盘操作是 随机写
磁盘的【顺序写】比【随机写】高效的多，因此 redo log 写入磁盘的开销更小
针对磁盘的【顺序写】为什么比【随机写】高效的多，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写得快的多
可以说这是WAL技术的另一个有点： Mysql 的写操作从磁盘的【随机写】变成了【顺序写】，提升语句的执行性能。这是因为MySQL的写操作并不是立刻更新到磁盘上，而是先记录到日志上，然后再合适的时间再更新到磁盘上
至此，针对为什么需要 redo log这个问题我们有两个答案：
- 实现事务的持久性，让MySQL有 crash-safe的能力，能够保证MySQL在任何时间段突然崩溃，重启后之前已经提交的记录都不会丢失
- 将写操作从【随机写】变成了【顺序写】，提升MySQL写入磁盘的性能
【产生的 redo log 是直接写入磁盘的吗？】
不是的
实际上，执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘中的，因为这样会产生大量的 I/O操作，而且磁盘的运行速度远慢于内存
所以，redo log 也有自己的缓存 —— redo log buffer，没产生一条redo log 时，会先写入到 redo log buffer，后续再持久化到磁盘：
redo log buffer 默认大小 16MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让MySQL处理【大事务】是不必写入，进而提升写IO性能
redo log 什么时候刷盘？
刷盘时机：
- MySQL正常关闭时
- 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘
- InnoDB的后台线程每隔1秒，将 redo log buffer 持久化到磁盘
- 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）
【 innodb_flush_log_at_trx_commit 参数控制的是什么】
单独执行一个更新语句的时候，InnoDB引擎会自己启动一个事务，在执行更新语句的过程中，生成的 redo log 先写到 redo log buffer 中，然后等到事务提交的时候，再将缓存在 redo log buffer 中的 redo log 按组的方式 【顺序写】到磁盘
上面的这种 redo log 刷盘时机是在事务提交的时候，这个是默认的行为
除此之外，InnoDB还提供了另外两种策略，由参数 innodb_flush_log_at_trax_commit 参数控制，可取的值有：0,1,2，默认值为1，这三个值分别代表的策略如下：
- 当设置的 参数为0时，表示每次事务提交时，还是 将 redo log 刘子昂 redo log buffer中，该模式下载事务提交时不会主动触发写入磁盘的操作
- 当设置该 参数为1时，表示每次在事务提交时，都 将缓存在 redo log buffer 里的redo log直接持久化到磁盘，这样可以保证MySQL异常重启时数据不会丢失
- 当设置该 参数为2时，表示每次事务提交时，都 只是将缓存在 redo log buffer 里的 redo log 写入到 redo log 文件，注意写到【redo log文件】，并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cacha，Page Cacha 是专门用来缓存文件数据的，所以写入【redo log文件】意味着写入到了操作系统中的文件缓存
【 innodb_flush_log_at_trx_commit 为0和2的时候，什么时候才讲 redo log写入到磁盘？】
InnoDB 的后台线程每隔1秒：
- 针对参数0：会把缓存在 redo log buffer 中的redo log ，通过调用 writer（） 写到操作系统的 Page Cacha，然后调用 fsync（）持久化到磁盘。所以参数为0的策略，MySQL进程的崩溃会导致上一秒钟所有的事务数据丢失
- 针对参数2：调用 fsync，将缓存在操作系统中 Page Cacha 里的 redo log 持久化到磁盘，所以参数为2的策略，较取值为0情况下更安全，因为MySQL进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事物数据才可能丢失
加入了后台线程后，innodb_flush_log_at_trx_commit 的刷盘时机如下：
【这三个参数的应用场景是什么？】
这三个参数的数据安全性和写入性能比较如下：
- 数据安全性：参数1 > 参数2 > 参数0
- 写入性能：参数0 ＞ 参数2 ＞参数1
所以，数据安全性和写入性能是不可兼得的，要么追求数据安全性，牺牲性能；要么追求性能，牺牲数据安全性
- 在一些对数据安全性要求比较高的场景，显然 innodb_flush_log_at_trx_commit 参数需要设置为1
- 在一些可以容忍数据库崩溃时丢失1s数据的场景中，我们可以将该值设置为0，这样可以明显地减少日志同步到磁盘的I/O操作
- 安全性和性能折中的方案就是参数2，虽然参数2没有参数0的性能高，但是数据安全性方面比参数0强，因为参数2是要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方面比参数1高
redo log 写满了怎么办？
默认情况下，InnoDB 存储引擎有1个重做日志文件组（redo log Group），【重做日志文件组】由两个 redo log 文件组成，这两个 redo 日志的文件名叫： ib_logfile0 和 ib_logfile1
在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是1GB，那么总共就可以记录 2GB的操作
重做日志文件组是以 循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形
所以InnoDB存储引擎会先写 ib_logfile0 文件，当该文件被写满的时候，会切换至 ib_logfile1 文件，当这个文件也被 写满时，会切换回 ib_logfile0
我们知道 redo log 是为了防止 Buffer Pool 中的脏页面丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出新的空间记录新的更新操作
redo log，是循环写的方式，相当于一个环形，InnoDB用 write pos 表示 redo log当前记录写到的位置，用 checkpoint 表示当前要擦除的位置：
图中的：
- write pos 和 checkpoint 的移动都是顺时针方向
- write pos ~ checkpoint 之间的部分*图中的红色部分，用来记录新的更新操作
- check point ~ write pos 之间的部分（图中的蓝色部分）；待落盘的脏数据页记录
如果write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时MySQL 不能再执行新的更新操作，也就是说MySQL会被阻塞（因此针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时 会停下来将Buffer Pool 中的脏页刷新到磁盘中，然后标记redo log哪些记录可以被擦除，接着对旧的 redo log记录进行擦除，等擦除完旧记录腾出来空间，等擦除完旧记录腾出来空间，checkpoint就会忘后移动（图中顺时针），然后MySQL恢复正常运行，继续执行新的更新操作
所以，一次checkpoint 的过程就是将脏页刷新到磁盘变成干净页，然后标记 redo log哪些记录可以被覆盖的过程
4.为什么需要binlog？
前面的undo log 和 redo log 都是基于InnoDB存储引擎生成的
MySQL在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有binlog 统一写入 binlog 文件
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如SELECT 和 SHOW 操作
【为什么有了binlog 还要有redo log？】
这个问题跟 MySQL的时间线有关系
最开始 MySQL 里并没有 InnoDB 引擎，MySQL自带的MyISAM,但是MyISAM没有crash-safe 的能力， binlog 日志只能用来归档
而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠 binlog 是没有crash-safe 能力的，所以InnoDB 使用redo log 来实现 crash-safe能力
redo log 和 binlog 有什么区别？
有四个区别：
1.适用对象不同：
- binlog 是MySQL 的Server 层实现的日志，所有存储引擎都可以使用
- redo log 是InnoDB存储引擎实现的日志
2.文件格式不同：
- binog 有3种格式类型，分别是 STATEMENT(默认格式)，ROW、MIXED，区别如下：
- STATEMENT：每一条修改数据的 SQL 都会被记录到binlog中（相当于记录了逻辑操作，所以针对这种格式，binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致赋值的数据不一致
- ROW :记录行数据最终被修改成什么样子了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量update语句，更新多少行数据就会产生多少条记录，使binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已
- MIXED ：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用ROW模式 和 STATEMENT 模式
- redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX表空间中的 YYY数据页 ZZZ偏移量的地方做了 AAA更新
3.写入方式不同：
- binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志
- redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页数据
4.用途不同：
- binlog 用于备份恢复、主从复制
- redo log 用于掉电等故障恢复
【如果不小心整个数据库的数据被删除了，能使用 redo log文件能恢复数据m？】
不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复
因为 redo log 文件是循环写，是会边写边擦除日志，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除
binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除，得用 binlog 文件恢复数据
主从复制怎么实现
MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库中
这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制binlog 的线程同步完成
MySQL 集群的主从复制过程梳理成3个阶段：
- 写入Binlog ：主库写 binlog 日志，提交事务，并更新本地存储数据
- 同步Binlog ： 把binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中
- 回放 Binlog ：回放 binlog，并更新存储引擎中的数据
具体详细过程如下：
- MySQL主库在客户端提交事务的请求之后，会写入 binlog ，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应
- 从库会创建一个专门的I/O线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息 写入 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性
在完成主从复制之后，你就可以在写数据时只在主库，在读数据时只在从库，这样即使写请求会锁表或者锁记录，也不会影响请求的执行
【从库是不是越来越多？】
不是的。
因为从库数量增加，从库连接上来的I/O线程也比较多，主库也要创建同样多的log dump 线程来处理复制的请求，对主库资源消耗比较高， 同时还受限于主库的网络带宽
所以在实际使用中，一个主库一般跟 2~3个从库（1套数据库，1主2从1备主），这就是一主多从的MySQL集群结构
【MySQL主从复制还有哪些模型？】
- 同步复制 ：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：
- 一是性能很差，因为要复制到所有节点才返回响应
- 二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务
- 异步复制 （默认模型）： MySQL主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失
- 半同步复制 ： MySQL5.7版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种 半同步复制的方式，兼顾了异步复制和同步复制到优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险
binlog什么时候刷盘？
事务执行过程中，先把日志写到 binlog cacha（Server层的cacha），事务提交的时候，再把 binlog cacha 写到 binlog 文件中
一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。这是因为有一个线程只能同时有一个事务在执行的设定，所以每当执行一个 begin/start trasaction 的时候，就会默认提交上一个事务，这样如果一个事务的 binlog 被拆开的时候，在备库执行就会被当做多个事务分段执行，这样破坏了原子性，是有问题的
MySQL给每个线程分配了一片内存用于缓冲 binlog，该内存叫 binlog cacha，参数 binlog_cacha_size 用于控制单个线程内 binlog cacha 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘
【什么时候binlog cacha会写到 binlog文件？】
在事务提交的时候，执行器把 binlog cacha 里的完整事务写入到 binlog 文件中，并清空 binlog cacha：
虽然每个线程有自己的 binlog cacha，但最终都写到同一个 binlog 文件：
- 图中的 write ，指的就是把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cacha 里，write 的写入速度还是比较快的，因为不涉及磁盘I/O
- 图中的 fsync ， 才是将数据持久化到磁盘的操作，这里就会涉及磁盘I/O，所以频繁的 fsync 会导致磁盘I/O升高
MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：
- sync_binlog = 0 的时候，表示每次提交事务都只 write ，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘
- sync_binlog = 1 的时候，表示每次提交事务都会 write ， 然后马上执行 fsync
- sync_binlog = N（N > 1） 的时候，表示每次提交事务都 write ，但累积 N 个事务后才 fsync
在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失
而当 sync_binlog 设置为1的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使主句发生异常重启，最多丢失一个事务的binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大
如果能容少量事务的 binlog 日志丢失的风险，为了提高写入性能，一般会 sync_binlog设置为100~1000中的某个数值
【三个日志说完，可以暂时小结一下update语句的执行过程】
当优化器分析出成本最小的执行计划后，执行器就按照计划开始进行更新操作
具体更新一条记录 UPDATE t_user SET name = 'xaiolin' WHERE id = 1;
流程如下：
- 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索 id = 1这一行记录
- 如果id = 1这一行所在的数据页本来就在 buffe pool 中，就直接返回给执行器更新
- 如果记录不在 buffer pool，将数据页从磁盘读到 buffer pool，返回记录给执行器
- 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样就不进行后续更新流程
- 如果不一样的话就把更新前的记录和更新后的记录都当做参数传给 InnoDB层，让InnoDB 真正的执行更新记录的操作
- 开启事务，InnoDB层更新记录前，首先有记录相应的嗯undo log，因为这是更新操作，需要把被更新的旧值记下来，也就是生成一条undo log，undo log会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log
- InnoDB层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log里面，这时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上
- 至此，一条记录更新完了
- 在一条更新语句执行完成后，然后开始记录该语句对于的 binlog，此时记录的 binlog 会被保存到 binlog cacha，并没有刷新到磁盘上的binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到磁盘
- 事务提交后，剩下的就是【两阶段提交】
5.为什么需要两阶段提交？
事务提交成功后，redo log 和 binlog 都要持久化到磁盘，但这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致
举个例子，假设 id = 1 这行数据的字段 name 的值原本是 ‘jay’，然后执行 UPDATE t_user SET name = 'xaiolin' WHERE id = 1;，如果在持久化 redo log 和 binlog 两个日志的过程中，出现了半成功状态，那么就有两种情况：
- 如果在将 redo log 刷入到磁盘之后，MySQL突然宕机了，而 binlog 还没有来得及写入。MySQL重启后，通过 redo log 能将 Buffer Pool 中 id = 1这行数据的 name 字段恢复到新值 xaiolin， 但是 binlog 丢失了这条更新语句，从库这一行 name 字段是旧值 jay，与主库的发生了数据不一致
- 如果在将 binlog 刷入到磁盘之后，MySQL 突然宕机了，而redo log还没有来得及写入。由于redo log还没写，崩溃恢复以后这个事务无效，所以 id = 1这行数据的name 字段还是旧值jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xaiolin，与主库的值不一致
可以看到，在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态，就会造成主从环境的数据不一致。这是因为 redo log 影响主库的数据， binlog 影响从库数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致
MySQL为了避免出现两份日志之间逻辑不一致的问题，使用了【两阶段提交】来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要么全部成功，要么全部失败，不会出现半成功的状态
两阶段提交把单个事物的提交拆分成了2个阶段，分别是【准备（Preoare）阶段 和【提交（Commit）阶段】】，每个阶段都由协调者和参与者共同完成，注意不要把提交阶段和 commit 语句混淆了，commit语句执行的时候，会包含提交阶段
举个拳击比赛的例子，两位拳击手（参与者）开始比赛之前，裁判（协调者）会在中间确认两位拳击手的状态，类似于问你准备好了吗？
- 准备阶段：裁判（协调者）会依次询问两位拳击手（参与者）是否准备好了，然后拳击手听到后做出应答，如果觉得自己准备好了，就会跟裁判说准备好了；如果没有自己还没有准备好（比如拳套还没有带好），就会跟裁判说还没准备好。
- 提交阶段：如果两位拳击手（参与者）都回答准备好了，裁判（协调者）宣布比赛正式开始，两位拳击手就可以直接开打；如果任何一位拳击手（参与者）回答没有准备好，裁判（协调者）会宣布比赛暂停，对应事务中的回滚操作。
两阶段提交的过程是怎样的？
在MySQL 的InnoDB存储引擎中，开启binlog的情况下，MySQL会维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个事物的一致性，MySQL使用了内部XA事务，内部XA事务由 binlog 作为协调者，存储引擎是参与者
当客户端执行 commit 语句或者在自动提交的情况下，MySQL内部开启一个XA事务，分两阶段来完成XA事务的提交，如下：
从图中可以看出，事务的提交有两个过程，就是将 redo log 的写入拆成了两个步骤： prepare 和 commit，中间再穿插写入 binlog ：
- prepare 阶段：将XID（内部XA事务的ID）写入到redo log，同时将 redo log对应的事物状态是指为 prepare，然后将 redo log持久化到磁盘（innodb_flush_log_at_trx_commit = 1的作用）
- commit阶段：把XID 写入到binlog ，然后将 binlog持久化到磁盘（sync_binlog = 1的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit ，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要binlog写磁盘成功，就算 redo log的状态还是 prepare也没有关系，一样会被认为事务已执行成功
异常重启会出现什么问题？
在两阶段提交的不同时刻，MySQL异常重启会出现什么现象：
不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。
在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：
- 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
- 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。
可以看到，对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。
所以说，两阶段提交是以 binlog 写成功为事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中查找到与 redo log 相同的 XID。
【 处于 prepare 阶段的 redo log 加上完整 binlog，重启就提交事务，MySQL 为什么要这么设计?】
binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。 所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。
【事务没提交的时候，redo log 会被持久化到磁盘吗？】
会的。
事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。
也就是说，事务没提交的时候，redo log 也是可能被持久化到磁盘的。
有的同学可能会问，如果 mysql 崩溃了，还没提交事务的 redo log 已经被持久化磁盘了，mysql 重启后，数据不就不一致了？
放心，这种情况 mysql 重启会进行回滚操作，因为事务没提交的时候，binlog 是还没持久化到磁盘的。
所以， redo log 可以在事务没提交之前持久化到磁盘，但是 binlog 必须在事务提交之后，才可以持久化到磁盘。
两阶段提交有什么问题
两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要头两个方面的影响：
- 磁盘I/O次数高：对于“双1”配置，每个事务都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘
- 锁竞争激烈：两阶段提交虽然能保证【单事务】两个日志的内容一致，但在【多事务】的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的基础流程上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致
【为什么两阶段提交的磁盘I/O次数很高】
binlog 和 redo log 在内存中都有对应的缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redo log buffer，它们持久化冬奥磁盘的时机分别由下面两个参数控制。一般我们为了避免日志的丢失，会将这两个参数设置为1：
- sync_binlog = 1的时候，表示每次提交事务都会将 binlog cache 里的 binlog 直接持久化到磁盘
- 当 innodb_flush_log_at_trx_commit = 1时，表示每次有事务提交时，都将缓存在 redo log buffer里的 redo log 直接持久化到磁盘
可以看到，如果 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置为1，那么在每个事务提交过程中，都会至少调用2次刷盘操作，一次是redo log刷盘，一次是binlog 落盘，所以这会成为性能瓶颈
【为什么锁竞争激烈？】
在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获取到锁时才能进入 prepare阶段，一直到commit阶段结束才能释放锁，下个事务才可以继续进行 prepare 操作
通过加锁虽然完美地解决了顺序一致性问题，但在并发量大的时候，就会导致对锁的争用，性能不佳
组提交
MySQL引入了binlog组提交（group commit）机制，当有多个事务提交的时候，会将多个binlog刷盘操作合并成一个，从而减少磁盘I/O的次数，如果说10个事务一次排队刷盘的时间成本是10，那么将这10个事务一次性一起刷盘的时间成本则近似于1
引入了组提交机制后， prepare 阶段不变，只针对 commit 阶段，将commit阶段拆分成三个过程：
- flush阶段： 多个事务按进入的顺序将binlog从cache写入文件（不刷盘）
- sync 阶段：对binlog文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）
- commit阶段：各个事务按顺序做 InnoDB commit 操作
上面的 每个阶段都有一个队列，每个阶段都有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader ， leader 领导所在队列的所有事物，全权负责整队的操作，完成后通知队内其他事务操作结束
对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。
【有 binlog 组提交，那有 redo log 组提交吗？】
这个要看 MySQL 版本，MySQL 5.6 没有 redo log 组提交，MySQL 5.7 有 redo log 组提交。
在 MySQL 5.6 的组提交逻辑中，每个事务各自执行 prepare 阶段，也就是各自将 redo log 刷盘，这样就没办法对 redo log 进行组提交。
所以在 MySQL 5.7 版本中，做了个改进，在 prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是推迟到组提交的 flush 阶段，也就是说 prepare 阶段融合在了 flush 阶段。
这个优化是将 redo log 的刷盘延迟到了 flush 阶段之中，sync 阶段之前。通过延迟写 redo log 的方式，为 redolog 做了一次组写入，这样 binlog 和 redo log 都进行了优化。
接下来介绍每个阶段的过程，注意下面的过程针对的是“双 1” 配置（sync_binlog 和 innodb_flush_log_at_trx_commit 都配置为 1）。 flush 阶段 第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower ：
【flush阶段】
第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower
接着，获取队列中的事务组，由绿色事务组的 Leader 对 redo log做一次 write+fsync，即一次将同组事务的 redo log刷盘
完成了 prepare阶段后，将绿色这一组事务执行过程中产生的 binlog 写入 binlog文件（调用write，不会调用 fsync，所以不会刷盘，binlog缓存在操作系统的文件系统中）
从上面这个过程，可以知道 flush 阶段队列的作用是 用于支撑 redo log的组提交
如果在这一步完成后数据库崩溃，由于 binlog 中没有该组事务的记录，所以 MySQL会在重启后回滚该组事务
【sync 阶段】
绿色这一组事务的 binlog 写入到 binlog 文件后，并不会马上执行刷盘的操作，而是会等待一段时间，这个等待的时长由 Binlog_group_commit_sync_delay 参数控制， 目的是为了组合更多事务的binlog，然后再一起刷盘
不过在等待过程中，如果事务的数量提前达到了 Binlog_group_commit_sync_no_delay_count参数设置的值，就不用继续等待，将马上进行 binlog 刷盘
从上面的过程，可以知道sync阶段队列的作用是 用于支持 binlog 的组提交
如果想提升 binlog 组提交的效果，可以设置下面两个参数来实现：
- binlog_group_commit_sync_delay = N,表示在等待N微秒后，直接调用 fsync，将处于文件系统中 page cache中的binlog刷盘，也就是将【binlog 文件】持久化到磁盘
- binlog_group_commit_sync_no_delay_count = N,表示如果队列中的事务数达到N个，就忽视 binlog_group_commit_sync_delay的设置，直接调用 fsync，将处于文件系统中 page cache 中的binlog 刷盘
如果在这一步完成后数据库崩溃，由于 binlog 中已经有了事务记录，MySQL会在重启后通过 redo log 刷盘的数据继续进行事务的提交
【commit 阶段】
最后进入 commit 阶段，调用引擎的提交事务节后，将 redo log 状态设置为 commit
commit 阶段队列的作用是承接 sync 阶段的事务，完成最后的引擎提交，使得 sync 可以尽早的处理下一组事务，最大化组提交的效率
6.MySQL磁盘I/O很高，有什么优化的办法？
现在我们知道事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，我们可以通过控制以下参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率：
- 设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
- 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
- 将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。
7.总结
具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:
- 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
- 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
- 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
- InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
- 至此，一条记录更新完了。
- 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
- 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交：
- prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
- commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
- 至此，一条更新语句执行完成。
六、内存篇
揭开Buffer Pool 的面纱
1.为什么要有 Buffer Pool
虽然说MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里读取数据，这样性能是极差的，要想提升查询性能，加一个缓存就可以解决这个问题。所以，当数据从磁盘中取出后，缓存内存中，下一次查询同样的数据的时候，直接从内存中读取。
为此，InnoDB 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能
有了缓冲池后：
- 当读取数据时，如果数据存在于Buffer Pool 中，客户端就会直接读取Buffer Pool中的数据，否则再去磁盘中读取
- 当修改数据时，首先是修改Buffer Pool中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘
Buffer Pool有多大？
Buffer Pool 是在MySQL启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有128M
可以通过调整 innodb_buffer_pool_size 参数来设置Buffer Pool 的大小，一般建议设置成可用物理内存的 60% ~ 80%
Buffer Pool缓存什么？
InnoDB 会把存储的数据划分为若干个【页】，以页作为磁盘和内存交互的基本单位，一个页的默认大小为16KB，因此，Buffer Pool 同样需要按【页】来划分
在 MySQL 启动的时候， InnoDB会为Buffer Pool 申请一篇连续的内存空间，然后按照默认的 16KB的大小划分出一个个的页，Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中
所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等
为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。
控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，如下图：
上图中控制块和缓存页之间的灰色部分称为碎片空间
为什么会有碎片空间呢？
你想想啊，每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到喽，这个用不到的那点儿内存空间就被称为碎片了。
当然，如果你把 Buffer Pool 的大小设置的刚刚好的话，也可能不会产生碎片。
查询一条记录，就只需要缓冲一条记录吗？
不是的。 当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。
2.如何管理 Buffer Pool？
如何管理空闲页？
Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。
那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页吧，这样效率太低了。
所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。
Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。
Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页。
有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除
如何管理脏页？
设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。
那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。
有了Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘
如何提高缓存命中率？
Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。
要实现这个，最容易想到的就是 LRU（Least recently used）算法。
该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。
简单的 LRU 算法的实现思路是这样的：
- 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
- 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。
比如下图，假设 LRU 链表长度为 5，LRU 链表从左到右有 1，2，3，4，5 的页
如果访问了3号的页，因为3号页在 Buffer Pool里，所以把3号页移动到头部即可
而如果接下来访问了8号页，因为8号页不在 Buffer Pool里，所以需要先淘汰末尾的5号页，然后再将8号页加入到头部
到这里我们可以知道，Buffer Pool 里有三种也和链表来管理数据
图中：
- Free Page（空闲页），表示此页未被使用，位于 Free 链表；
- Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
- Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。
简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题：
什么是预读失效？
先来说说 MySQL 的预读机制。程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。
所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。
但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。
如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。
如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。
怎么解决预读失效而导致缓存命中率降低的问题？
我们不能因为害怕预读失效，而将预读机制去掉，大部分情况下，局部性原理还是成立的。
要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。
那到底怎么才能避免呢？
MySQL 是这样做的，它改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域 和 young 区域。
young 区域在 LRU 链表的前半部分，old 区域则是在后半部分，如下图：
old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pct 参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。
划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。
接下来，给大家举个例子。
假设有一个长度为 10 的 LRU 链表，其中 young 区域占比 70 %，old 区域占比 30 %。
现在有一个编号为20的页被预读了，这个页只会被插到 old 区域头部，而old区域末尾的页（10号）就会被淘汰掉
如果 20 号页一直不会被访问，它也没有占用到 young 区域的位置，而且还会比 young 区域的数据更早被淘汰出去。
如果 20 号页被预读后，立刻被访问了，那么就会将它插入到 young 区域的头部，young 区域末尾的页（7号），会被挤到 old 区域，作为 old 区域的头部，这个过程并不会有页被淘汰。
虽然通过划分old区域和young区域可以避免预读失效带来的影响，但是还有个问题无法解决，那就是Buffer Pool污染的问题
什么是 Buffer Pool 污染？
当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。
注意， Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool 污染。
比如，在一个数据量非常大的表，执行了这条语句：- select * from t_user where name like "%xiaolin%";
- 从磁盘读到的页加入到 LRU 链表的 old 区域头部；
- 当从页里读取行记录时，也就是页被访问的时候，就要将该页放到 young 区域头部；
- 接下来拿行记录的 name 字段和字符串 xiaolin 进行模糊匹配，如果符合条件，就加入到结果集里；
- 如此往复，直到扫描完表中的所有记录。
经过这一番折腾，原本 young 区域的热点数据都会被替换掉。 举个例子，假设需要批量扫描：21，22，23，24，25 这五个页，这些页都会被逐一访问（读取页里的记录）。
在访问这些数据的时候，会被逐一插入到young区域头部
可以看到原本在young区域的热点数据6和7号页都被淘汰了，这就是Buffer Pool污染的问题
怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题？
像前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。
LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。
MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。
具体是这样做的，在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：
- 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
- 如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；
这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms。
也就是说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题 。
另外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。
脏页什么时候会被刷入磁盘？
引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。
因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。
可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？
这个不用担心，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。
下面几种情况会触发脏页的刷新：
- 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
- Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
- MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
- MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；
在我们开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。
如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。
3.总结
Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。
Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。
Innodb 通过三种链表来管理缓页：
- Free List （空闲页链表），管理空闲页；
- Flush List （脏页链表），管理脏页；
- LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；
InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：
- 将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。 当「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。
可以通过调整 innodb_old_blocks_pct 参数，设置 young 区域和 old 区域比例。
在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。
转载：https://xiaolincoding.com/mysql/
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
