在学习过程中对MySQL的底层原理产生极大的兴趣，动手做一个轮子项目-简易数据库，做的过程中伴随着 小林Coding的相关文章一起学习，实际理论相结合，学习效果很显著，并且把小林Coding的MySQL整理摘抄了一份，具体内容如下：
MySQL执行一条查询语句的流程：
当我们想要在书里面查找某一个知识点时，一页一页找就显得很愚蠢，根据目录来查找才是正确的思路，而索引在数据库中起到的就是这么一个作用，索引就是为了帮助存储引擎快速获取到数据的一种数据结构。
存储引擎，简单来说就是如何存储数据，如何为存储的数据建立索引和如何更新，查询数据的实现方法。MySQL存储引擎有MyISAM、InnoDB、Memory，其中InnoDB在MySQL5.5之后成为默认的存储引擎
B+tree索引、Hash索引、Full-text索引
在MySQL5.5之后InnoDB成为默认的存储引擎，B+tree索引类型也是MySQL存储引擎使用最多的索引类型
在创建表时，InnoDB存储引擎会根据不同的场景选择不同的列作为索引：
其他索引都属于辅助索引，也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是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就能找到数据
「女朋友问我：为什么 MySQL 喜欢 B+ 树？我笑着画了 20 张图（详解）
B+Tree相比于B树、二叉树或Hash索引结构的优势：
B+Tree只在叶子节点存储数据，而B树的非叶子节点也要存储数据，所以B+Tree的单个节点的数据量更小，在相同的磁盘I/O次数下，就能查询到更多的节点
另外，B+Tree叶子节点采用的是双链表连接（双向链表），适合MySQL中常见的基于范围的顺序查找，而B树无法做到这一点
对于有N个叶子节点的B+Tree，其搜索复杂度为O（logdN）,其中d表示节点允许的最大子节点个数为d个
在实际应用中，d是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree的高度依然维持在34层左右，也就是说一次数据查询操作只需要做34次的磁盘I/O操作就能查询到目标数据
而二叉树的每个父节点的儿子节点个数只能是2个，意味着其搜索复杂度为O（logN）,这已经比B+Tree高出不少，因此二叉树检索到目标数据所经历的磁盘I/O次数要更多
Hash在做等值查询的时候查询的效率非常高，搜索复杂度为O（1）
但是Hash表不适合做范围查询，它更适合做等值的查询，这也是B+Tree索引要比Hash表索引有着更广泛的适用场景的原因
聚簇索引（主键索引）、二级索引（辅助索引）
这两个的区别在前面也已经提到过：
所以，在查询时使用了二级索引，如果查询的数据在二级索引里查询到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引就能查询到数据量，这个过程就是回表
主键索引、唯一索引、普通索引、前缀索引
主键索引就是建立在主键字段上的索引，通常在创建表时一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值
唯一索引建立在UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值
普通索引就是建立在普通字段上的索引，既不要求字段为主键也不要求字段为UNIQUE
前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为char、varchar、binary、varbinary的列上。
使用前缀索引的目的是为了减少索引占据的存储空间，提升查询速率。
单列索引、联合索引（复合索引）
通过将多个字段组合成一个索引，该索引就被称为联合索引
在使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循【最左匹配原则】，联合索引就会失效，这样就无法利用到索引快速查询的特性。
比如，如果创建了一个 (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的情况
这种特殊情况就发生在范围查询，联合索引的最左匹配原则会一直向右匹配直到遇到【范围查询】就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
联合索引的最左匹配原则，在遇到范围查询（如>、=、（具体细节转到https://xiaolincoding.com/mysql/index/index_interview.html#%E6%8C%89%E5%AD%97%E6%AE%B5%E4%B8%AA%E6%95%B0%E5%88%86%E7%B1%BB）
现在我们知道，对于联合索引（a，b），在执行 select * from table where a > 1 and b = 2语句的时候，只有a字段能用到索引，那在联合索引的B+Tree找到的第一个满足条件的主键值（ID为2）后，还需要判断其他条件是否满足（看b是否等于2），那是在联合索引里判断？还是回主键索引去判断呢？
另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段就越有可能被更多的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排好序的，避免在文件排序，提高了查询效率。
索引最大的好处是提高查询速度，但索引也是有缺点的，比如：
所以，索引不是万能钥匙，是根据场景来使用的
常见优化索引的方法：
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那么为什么需要使用前缀来建立索引呢？
使用前缀索引是为了减小索引字段的大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度，在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小
不过，前缀索引有一定的局限性，例如：
覆盖索引是指SQL中query的所有字段，在索引B+Tree的叶子节点上都能找的到的那些索引，从二级索引中查询的到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作
假设我们只需要查询商品的名称、价格有什么方式可以避免回表呢？
我们可以建立一个联合索引，即【商品ID、名称、价格】作为一个联合索引。如果索引中存在这些数据，查询将不会再次检查主键索引，从而避免回表
所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的I/O操作
在建表时，都会默认将主键索引设置为自增的，具体为什么要这么做？有什么好处？
InnoDB 创建主键索引默认为聚簇索引，数据被存放在了B+Tree的叶子节点上。也就是说，每一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中
如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，将页面写满，就会自动开辟一个新页面。因为每次插入一条新纪录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会导致大量的内存碎片，导致索引结构不紧凑，从而影响查询效率
另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子结点越小（二级索引的叶子结点存放的数据是主键值），这样二级索引占用的空间也就越小。
引发索引失效的情况：
like %xx 或者 like %xx%这两种方式都会造成索引失效事务是由MySQL的引擎实现的，常见的InnoDB是支持事务的
不过并不是所有的引擎都支持事务，比如MySQL原生的MyISAM引擎就不支持事务，也正是如此，所有大多数MySQL的引擎都是用InnoDB
事务必须遵循的四个特性：
在InnoDB引擎中是如何保证事务的这四个特性的呢？
MySQL服务端是允许多个客户端连接的，这意味着MySQL会出现同时处理多个事务的情况
那么在同时处理多个事务的时候，就可能出现脏读、不可重复读、幻读的问题
如果一个事务【读到】了另一个【未提交事务修改过的数据】就意味着发生了【脏读】现象
假设有A和B两个事务同时自爱处理，事务A先开始从数据库中读取余额数据，然后再进行更新操作，如果此时事务A还没有提交事务，而此时事务B也正好从数据库中读取余额数据，那么事务B读取到的数据时刚才事务A更新后的数据，即使事务A没有提交事务
因为事务A是还没有提交事务的，也就是它随时可能发生回滚操作，如果在上面这种情况A事务A发生了回滚，那么事务B刚才读取到的就是过期的数据，这种现象就被称为脏读
在一个事务内多次读取同一个数据，如果出现前后两次读取到的数据不一样的情况，就意味着发生了【不可重复读】现象
假设有A和B这两个事务同时在处理，事务A先开始从数据库中读取余额数据，然后继续执行代码逻辑处理，在这过程中如果事务B更新了这条数据，并提交了事务，那么当事务A再次读取这条数据时就会发现前后两次读取到的数据是不一致的，这种现象就被称为不可重复读
在一个事务内多次查询某个符合条件的【记录数量】，如果出现前后两次查询到的记录数量不一致的情况，就意味着发生了【幻读】现象
假设有A和B这两个事务同时在处理，事务A先开始从数据库查询账户余额大于100万的记录，发现共有5条，然后事务B也按相同的搜索条件也是查询到5条记录，接下来事务A插入了一条余额超过100万的账号，并提交了事务，此时数据库中超过100万的账号个数就变为了6
然后事务B再次查询账户余额大于100万的记录，此时查询到的记录数量有6条，**发现和前一次读取到的记录数量不一样了，就感觉发生了幻觉一样，这种现象就称为
多个事务并发执行时可能会遇到【脏读、不可重复读、幻读】的现象，这些现象会对事务的一致性产生不同程度的影响
严重性排序如下：
SQL标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，四个隔离级别如下：
隔离水平高低排序：
针对不同的隔离级别，并发事务可能发生的现象也不同：
也就是说：
也就是说： 在「读未提交」隔离级别下，可能发生脏读、不可重复读和幻读现象； 在「读提交」隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象； 在「可重复读」隔离级别下，可能发生幻读现象，但是不可能脏读和不可重复读现象； 在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。
所以，要解决脏读现象，就要升级到【读提交】以上的隔离级别；要解决不可重复读现象就要升级到【可重复读】的隔离级别，要解决幻读现象不建议将隔离级别升级到【串行化】
不同数据库厂商对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执行过程中查询到的余额可能会不同：
这四种隔离级别具体是如何实现的呢？
注意，执行【开始事务】命令，并不意味着启动了事务，在MySQL有两种开启事务的命令：
对于使用InnoDB存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：
在创建Read View 后，我们可以将记录中的trx_id划分为下面的三种情况：
一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几中情况：
这种通过【版本链】来控制并发事务访问同一个记录的行为就叫做 MVCC（多版本并发控制）
可重复读隔离级别是启动事务时生成一个 Read View ，然后整个事务期间都在用这个 Read View
假设事务A（事务id为51）启动后，紧接着事务B（事务id为52）也启动了，这两个事务创建的 Read View 如下：
事务A和事务B的Read View 具体内容如下：
接着，在可重复读隔离级别下，事务A和事务B按顺序执行了以下操作：
具体分析：
事务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万这条记录
就是通过这样的方式实现了【可重复读】隔离级别下在事务期间读到的记录都是事务启动前的记录
读提交隔离级别是在每次读取数据时，都会生成一个新的Read View
也意味着，事务期间多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务
那么读提交隔离级别是如何工作的呢？沿用上面的例子
假设事务A（事务id为51）启动后，紧接着事务B（事务id为52）也启动了，接着按顺序执行以下操作：
前两次事务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，那么在事务期间的多次读取同一条数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务
事务是在+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锁。
在MySQL中，根据加锁的范围可以分为全局锁、表级锁和行锁三类。
要使用全局锁，则要使用下面这条命令：
flush tables with read lock
执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：
释放全局锁：
unlock tables
当然，当对话断开了，全局锁会自动释放
全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，从而出现备份文件的数据与预期的不一样
加上全局锁，意味着整个数据库都是只读状态
如果数据库里面有很多数据，备份就会花费很多时间，关键是备份期间，业务只能读取数据，而不能更新数据，这样会导致业务停滞
如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建Read View，然后整个事务执行期间都在用这个Read View，而且由于MVCC的支持，备份期间业务依然可以对数据进行更新操作
因为在可重复读隔离级别下，即使其他事务更新了表的数据，也不影响备份数据库时的Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据
备份数据库的工具是 mysqldump ，在使用 mysqldump 时加上 -single-transaction参数的时候，就会在备份数据库时先开启事务。这种方法只适用于支持【可重复读隔离级别】的存储引擎
InnoDB存储引擎默认的事务隔离级别就是可重复读，因此可以采用这种方式来备份数据库
但是，对于MyISAM这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法
MySQL中的表级锁：
如果我们想对学生表（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表进行写操作，这时候写操作会发生阻塞
释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：
unlock tables
当会话退出后，也会释放所有表锁
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式，不过尽量避免在使用InnoDB引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 厉害的地方在于实现了颗粒度更细的行级锁
对于元数据锁（MDL），我们不需要显示的使用MDL，因为当我们对数据库进行操作时，会自动给这个表加上MDL：
MDL是为了保证当用户对表执行CRUD操作时，防止其他线程对这个表结构做了变更。
当有线程在执行 select 语句（加MDL读锁）的期间，如果有其他线程执行了 CRUD操作（申请MDL读锁），那么就会被阻塞，直到表结构变更完成（释放MDL写锁）
MDL是在事务提交后才会释放，这意味着事务执行期间，MDL是一直持有的
如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没有提交），那在对表结构做变更操作的时候，可能会出现意想不到的事情，比如下面这个顺序的场景：
那么在线程C阻塞后，后续对该表的select语句就都会阻塞，如果此时有大量该表的select语句的请求到来，就会有大量的线程阻塞住，这时数据库的线程很快就会爆满了
这是因为申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现了MDL写锁等待，会阻塞后续该表的所有CRUD操作
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了MDL读锁，如果可以考虑kill掉这个长事务，然后再做表结构的变更
也就是，当执行插入、更新、删除操作，需要现对表加上【意向独占锁】，然后对该记录加独占锁，而普通的select是不会加行级锁的，普通的select是利用MVCC实现一致性读，无锁，不过select也是可以对记录加共享锁和独占锁的：
//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向独占锁，然后对读取的记录加独占锁
select ...for update
意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突
表锁和行锁是满足读读共享、读写互斥、写写互斥的
如果没有【意向锁】那么加【独占表锁】时，就需要遍历表里所有
记录，查看是否有记录存在独占锁，这样效率就会很慢
那么有了【意向锁】，由于在对记录加独占锁前，先回加上表级别的意向独占锁，那么在加【独占表锁】时，直接查询该表是否有意向独占锁，如果有就意味着表里已经有记录被家里独占锁，这样就不用去遍历表里的记录
所以，意向锁的目的是为了快速判断表里是否有记录被加锁
表里的主键通常都会设置为自增，这是通过对主键字段声明 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 = 2是性能最高的方式，但搭配binlog的日志格式是statement 一起使用时，在【主从复制的场景】中会发生数据不一致的问题
在插入数据时，会加一个表级别的AUTO_INC锁，然后被 AUTO_INCREMENT修饰的字段赋值递增的值，等插入语句执行完成后，才会把AUTO_INC锁释放掉
InnoDB是支持行级锁的，而MyISAM引擎并不支持行级锁
前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读
//对读取的记录加共享锁
select ... lock in share mode;
//对读取的记录加独占锁
select ... for update
上面这两条语句必须在一个事务，因为事务提交了，锁就会被释放，所以在使用这两条语句时，要加上begin、start transaction 、或者 set autocommit = 0
共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥，读写互斥
行级锁的类型需要有三类：
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有S锁和X锁之分的：
当事务执行commit 后，事务过程中生成的锁都会被释放
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
假设，表中有一个范围id为（3,5）间隙锁，那么其他事务就无法插入id = 4这条记录，这样就有效的防止幻读现象的发生
间隙锁虽然存在X型间隙锁和S型间隙锁，但是并没有什么区别，间隙锁直接是兼容的，即两个事务可以同时持有包含公共间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是为了防止插入幻影记录而提出来的
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 型的记录锁是冲突的。
一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务添加了间隙锁（next-key lock）也包含间隙锁
如果有的话，插入操作会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态
举例，假设事务A已经对表加了一个范围id为（3,5）间隙锁
当事务A还没提交的时候，事务B向该表插入一条id = 4 的新纪录，这时会判断带插入的位置已经被事务A加了间隙锁，于是事务B会生成一个插入意向锁，然后将锁的状态设置为等待状态（MySQL加锁时，是先生成锁结构，然后设置锁状态，如果锁状态是等待状态，并不是意味着事务成功获取到了 锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），这时事务B就会发生阻塞，直到事务A提交了事务
插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁
如果说间隙锁锁住的是一个区间，那么【插入意向锁】锁住的就是一个点，因而从这个角度来说，插入间隙锁确实是一种特殊的间隙锁
插入意向锁与间隙锁的另一个非常重要的差别是：尽管【插入意向锁】也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间的插入意向锁（当然插入意向锁如果不在间隙锁区间内还是可以的）
普通的select语句是不会对记录加行级锁的（除了串行化隔离级别），因为它属于快照读，是通过MVCC（多版本并发控制）实现的
如果要在查询时对记录加行级锁，可以使用下面的方式，这两种会加锁的语句称为锁定读
//对读取的记录加共享锁（S型锁）
select ... lock in share mode;
//对读的记录加独占锁（X型锁）
select ... for update;
上面俩条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候都要加上 begin 或者 start transaction 开启事务的语句
除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁（X型锁）
//对操作的记录加独占锁（X型锁）
update table ... where id = 1;
//对操作的记录加独占锁（X型锁）
delete from table where id = 1;
共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥，读写互斥
不同隔离级别下，行级锁的种类是不同的：
所以行级锁的种类主要有三类：
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有S锁和X锁之分的：
当事务执行commit 后，事务过程中生成的锁都会被释放
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
假设，表中有一个范围id为（3,5）间隙锁，那么其他事务就无法插入id = 4这条记录，这样就有效的防止幻读现象的发生
间隙锁虽然存在X型间隙锁和S型间隙锁，但是并没有什么区别，间隙锁直接是兼容的，即两个事务可以同时持有包含公共间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是为了防止插入幻影记录而提出来的
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 型的记录锁是冲突的。
行级锁的加锁规则比较复杂，不同的场景，加锁的形式是不同的
加锁的对象是索引，加锁的基本单位是 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;
其中id是主键索引（唯一索引），age是普通索引（非唯一索引），name是普通的列
当我们使用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：
具体说明：
假设事务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)
那么事务A会为id为1的这条记录加上 X型的记录锁
接下来，如果有其他事务，对id为1的记录进行更新或删除操作的话，这些操作都会被阻塞，因为更新或删除也会对记录加X型的记录锁，而X锁和X锁之间是互斥关系
比如下面这个例子：
因为事务A对id = 1的记录加了X型的记录锁，所以事务B在修改id = 1的记录会被阻塞，事务C在删除id = 1的记录时也会发生阻塞
【有什么命令可以分析加了什么锁】
可以通过 select * from performance_schema.data_locksG;这条语句，查看事务执行SQL过程中加了什么锁
以上面事务A为例：
可以看出一共加了两个锁：
图中LOCK_TYPE = RECORD 中的RECORD 表示行级锁，而不是记录锁
通过LOCK_MODE 可以确认是 next-key lock 还是间隙锁，还是记录锁
因此，此时事务A在 id = 1 记录的主键索引上加的是记录锁，锁住的范围是id为1的这条记录，这样其他的事务就无法对id为1这条记录进行更新和删除操作了
从这里我们可以得知，加锁的对象是针对索引，因为这里查询语句扫描的B+树是聚簇索引树，即主键索引树，所以是对主键索引加锁。将对应记录的主键索引加记录锁后，就意味着其他事务无法对该记录进行更新和删除操作了
【为什么唯一索引等值查询并且查询记录存在的场景下，该记录的next-key lock 会退化成记录锁】
原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题
幻读的定义就是，当一个事务前后两次查询的结果集不相同时，就认为发生了幻读。所以要避免幻读，就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新纪录，这样前后两次查询的结果集就不会出现不相同的情况
假设事务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)
接下来通过 select *from performance_schma.data_locksG;，这条语句，查看事务执行SQL过程中加了什么锁
可以看到一共加了两个锁：
因此，此时事务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会退化成【间隙锁】？
原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。
范围查询和等值查询的加锁规则是不同的
当唯一索引进行范围查询的时候，会对每一个扫描到的所有加next-key lock，然后如果遇到下面这些情况，会退化成记录锁或间隙锁：
情况一：针对【大于等于】的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中的，那么该记录的索引中的 next-key lock会退化成记录锁
情况二：针对【小于或者小于等于】的范围查询，要看条件值得记录是否存在于表中：
当我们用非唯一索引进行等值查询时，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，会同时对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁
针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：
非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 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)
事务A的加锁变化：
可以看到，事务A对主键索引和二级索引都加了X型的锁：
在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22记录的二级索引上的 next-key lock退化成记录锁？
因为 age 字段是非唯一索引，不具有唯一性，索引如果只加记录锁（记录锁无法防止插入，只能防止删除或更改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不同，出现了幻读现象
前面的案例，我们的查询语句都有使用索引查询，也就是查询记录的时候，是通过索引扫描的方式查询的，然后对扫描出来的记录进行加锁。
如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。
因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题
MySQL行级锁的加锁规则：
唯一索引等值查询：
非唯一索引等值查询：
当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于：
其实理解 MySQL 为什么要这样加锁，主要要以避免幻读角度去分析，这样就很容易理解这些加锁的规则了。
还有一件很重要的事情，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。
加锁流程图：
唯一索引（主键索引）加锁的流程图如下。（注意这个流程图是针对「主键索引」的，如果是二级索引的唯一索引，除了流程图中对二级索引的加锁规则之外，还会对查询到的记录的主键索引项加「记录锁」，流程图没有提示这一个点，所以在这里用文字补充说明下）
非唯一索引加锁的流程图：
执行一条查询语句的过程属于【读】一条记录的过程，如下图：
那么，执行一条update语句，期间发生了什么，比如下面这一条：
UPDATE t_user SET name = 'xaiolin' WHERE id = 1;
查询语句的那一套流程，更新语句也是会一样走一遍：
不过，更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志）、binlog（归档日志）这三种日志：
我们在执行一条“增删改”语句时，虽然没有输入 begin 开启事务和 commit 提交事务，但是MySQL会隐式开启事务来执行“增删改”语句，执行完就自动提交事务，这样就保证执行完“增删改”语句后，我们可以及时在数据库表中看到对应结果
执行一条语句是否自动提交事务，是由 【autocommit】 参数决定的，默认是开启的，所以执行一条 update 语句也是会使用事务的
那么当一个事务自爱执行过程中，还没有提交事务时，如果MySQL发生崩溃，如何回滚到事务之前的数据？
如果我们每次在事务执行过程中，都记录下来回滚时需要的信息到一个日志里，那么在事务执行途中发生了MySQL崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据
实现这一机制就是 undo log（回滚日志），它保证了事务的ACID特性中的原子性
undo log 是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到undo log 日志文件中，当事务回滚时，可以利用undo log来进行回滚：
每当InnoDB引擎对一条记录进行操作（增删改）时，要把回滚时需要的信息都记录到undo log里，比如：
在发生回滚时就读取undo log里的数据，做原先操作的相反操作
针对delete 和 update操作会有一些特殊的处理：
一条记录的每一次更新操作产生的undo log格式都有一个 roll_pointer指针和一个trx_id事务id：
另外，undo log还有一个作用，通过Read View + undo log 实现MVCC（多版本并发控制）
对于【读提交】和【可重复读】隔离级别的事务来说，它们的快照读（普通的select语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：
这两个隔离级别实现是通过【事务的Read View里的字段】和【记录中的两个隐藏列（trx_id和roll_pointer）】的对比，如果不满足可见性，就会顺着undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录的行为，这就叫做MVCC
因此，undo log 两大作用：
undo log 和数据页的刷盘策略是一样的，都需要通过redo log保证持久化
buffer pool 中有undo 页，对undo 页的修改也会记录到redo log。redo log会每秒刷盘，提交事务时也会刷盘，数据页和undo页都是靠这个机制保证持久化的
MySQL的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后再内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？
当然是缓存起来好，这样再有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘中获取数据了
为此，InnoDB存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能
有了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后，再通过页里的【页目录】去定位到某条具体的记录
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存储引擎的日志，它们的区别在于：
事务执行之前发生了崩溃（这里的崩溃不是宕机崩溃，而是事物执行错误，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这个问题我们有两个答案：
【产生的 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性能
刷盘时机：
【 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，这三个值分别代表的策略如下：
【 innodb_flush_log_at_trx_commit 为0和2的时候，什么时候才讲 redo log写入到磁盘？】
InnoDB 的后台线程每隔1秒：
加入了后台线程后，innodb_flush_log_at_trx_commit 的刷盘时机如下：
【这三个参数的应用场景是什么？】
这三个参数的数据安全性和写入性能比较如下：
所以，数据安全性和写入性能是不可兼得的，要么追求数据安全性，牺牲性能；要么追求性能，牺牲数据安全性
默认情况下，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，就意味着 redo log 文件满了，这时MySQL 不能再执行新的更新操作，也就是说MySQL会被阻塞（因此针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时 会停下来将Buffer Pool 中的脏页刷新到磁盘中，然后标记redo log哪些记录可以被擦除，接着对旧的 redo log记录进行擦除，等擦除完旧记录腾出来空间，等擦除完旧记录腾出来空间，checkpoint就会忘后移动（图中顺时针），然后MySQL恢复正常运行，继续执行新的更新操作
所以，一次checkpoint 的过程就是将脏页刷新到磁盘变成干净页，然后标记 redo log哪些记录可以被覆盖的过程
前面的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文件能恢复数据m？】
不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复
因为 redo log 文件是循环写，是会边写边擦除日志，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除
binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除，得用 binlog 文件恢复数据
MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库中
这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制binlog 的线程同步完成
MySQL 集群的主从复制过程梳理成3个阶段：
具体详细过程如下：
在完成主从复制之后，你就可以在写数据时只在主库，在读数据时只在从库，这样即使写请求会锁表或者锁记录，也不会影响请求的执行
【从库是不是越来越多？】
不是的。
因为从库数量增加，从库连接上来的I/O线程也比较多，主库也要创建同样多的log dump 线程来处理复制的请求，对主库资源消耗比较高， 同时还受限于主库的网络带宽
所以在实际使用中，一个主库一般跟 2~3个从库（1套数据库，1主2从1备主），这就是一主多从的MySQL集群结构
【MySQL主从复制还有哪些模型？】
事务执行过程中，先把日志写到 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 文件：
MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：
在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失
而当 sync_binlog 设置为1的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使主句发生异常重启，最多丢失一个事务的binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大
如果能容少量事务的 binlog 日志丢失的风险，为了提高写入性能，一般会 sync_binlog设置为100~1000中的某个数值
【三个日志说完，可以暂时小结一下update语句的执行过程】
当优化器分析出成本最小的执行计划后，执行器就按照计划开始进行更新操作
具体更新一条记录 UPDATE t_user SET name = 'xaiolin' WHERE id = 1;
流程如下：
事务提交成功后，redo log 和 binlog 都要持久化到磁盘，但这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致
举个例子，假设 id = 1 这行数据的字段 name 的值原本是 ‘jay’，然后执行 UPDATE t_user SET name = 'xaiolin' WHERE id = 1;，如果在持久化 redo log 和 binlog 两个日志的过程中，出现了半成功状态，那么就有两种情况：
可以看到，在持久化 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 ：
在两阶段提交的不同时刻，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次数很高】
binlog 和 redo log 在内存中都有对应的缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redo log buffer，它们持久化冬奥磁盘的时机分别由下面两个参数控制。一般我们为了避免日志的丢失，会将这两个参数设置为1：
可以看到，如果 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阶段拆分成三个过程：
上面的 每个阶段都有一个队列，每个阶段都有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 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 可以尽早的处理下一组事务，最大化组提交的效率
现在我们知道事务在提交的时候，需要将 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 就会被持久化到磁盘。innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:
虽然说MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里读取数据，这样性能是极差的，要想提升查询性能，加一个缓存就可以解决这个问题。所以，当数据从磁盘中取出后，缓存内存中，下一次查询同样的数据的时候，直接从内存中读取。
为此，InnoDB 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能
有了缓冲池后：
Buffer Pool 是在MySQL启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有128M
可以通过调整 innodb_buffer_pool_size 参数来设置Buffer Pool 的大小，一般建议设置成可用物理内存的 60% ~ 80%
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 后，再通过页里的页目录去定位到某条具体的记录。
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 算法的实现思路是这样的：
比如下图，假设 LRU 链表长度为 5，LRU 链表从左到右有 1，2，3，4，5 的页
如果访问了3号的页，因为3号页在 Buffer Pool里，所以把3号页移动到头部即可
而如果接下来访问了8号页，因为8号页不在 Buffer Pool里，所以需要先淘汰末尾的5号页，然后再将8号页加入到头部
到这里我们可以知道，Buffer Pool 里有三种也和链表来管理数据
图中：
简单的 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%";
可能这个查询出来的结果就几条记录，但是由于这条语句会发生索引失效，所以这个查询过程是全表扫描的，接着会发生如下的过程：
经过这一番折腾，原本 young 区域的热点数据都会被替换掉。 举个例子，假设需要批量扫描：21，22，23，24，25 这五个页，这些页都会被逐一访问（读取页里的记录）。
在访问这些数据的时候，会被逐一插入到young区域头部
可以看到原本在young区域的热点数据6和7号页都被淘汰了，这就是Buffer Pool污染的问题
怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题？
像前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。
LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。
MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。
具体是这样做的，在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：
这个间隔时间是由 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 拥有了崩溃恢复能力。
下面几种情况会触发脏页的刷新：
在我们开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。
如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。
Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。
Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。
Innodb 通过三种链表来管理缓页：
InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：
可以通过调整 innodb_old_blocks_pct 参数，设置 young 区域和 old 区域比例。
在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。
登录查看全部
参与评论
手机查看
返回顶部