最近一直在复习总结mysql，也越来越深刻认识到"学而知不足，思而得远虑"这句话的真谛了。回想起之前，总以为自己学会了，其实并未。很多知识点，自己还是浮于表面，很多知识点，自己无法灵活串联。哈哈，可能我还是个小菜鸡吧。一直以来想要做的事情就是将之前学的知识点及复习的模块汇总起来，融会贯通，但是发现还是挺难的，慢慢来吧。
常常会思考，既然是汇总，自然是简洁明了。那么，该顺着哪条线将不同的知识点串联起来，又该如何挑选重点，毕竟mysql中关键的模块那么多，比如说锁，比如说sql优化，优化器CBO,这些任意一个都能写一本书了，个人感觉是不适合作为串联的一环，思考了很久，最后，有了上图，以buffer pool与磁盘为主线，以事务执行涉及的关键知识点为分支，当然，不会每一个都会载入，例如，锁，这个肯定是要放到后面的学习笔记中单独总结的。
mysql涉及数据的两个载体无非是磁盘，内存，内存查询速度快，但是容量小，且断电数据就会丢失，磁盘容量大，除非硬件坏了，不然数据能永久保存，但是查询速度慢，由此内存和磁盘的纠缠不休的故事便开始了。而在mysql的内存这一块，buffer pool无疑是真正的王者，我们的主人公由此便出场了。
缓冲池的话可以从两部分来看，一部分是参数设置问题，如上，如果并发访问比较高且服务器内存充足的情况下，设置多个buffer pool实例能有效提高数据库的性能，对应的值便是innodb_buffer_pool_instances了。但是注意当innodb_buffer_pool_size值小于1G时候，即使设置多个buffer pool实例，Innodb也会将innodb_buffer_pool_instances值修改回为1。还有，buffer pool申请资源时不是一次性申请的，而是以chunk为单位，一个chunk代表一片连续的内存空间，包含若干的缓存页和对应的数据信息块(控制块，可以理解为控制块中存储缓存页的相关信息)。chunk块大小对应的参数为innodb_buffer_pool_chunk_size，默认为128M大小，只支持在服务器启动时指定。另外的话，buffer pool的总大小的设置也需要进行考虑，即innodb_buffer_pool_size的值，设置过大，容易导致OOM问题，可通过dmesg -T|grep OOM查看是否发生OOM,发生OOM后，一方面可以通过降低innodb_buffer_pool_size的值看是否能够解决，另外，不排除某些版本有内存泄漏的问题，例如5.7.26，此时最好的方法便是升级版本或者回退版本。设置过小的话，虽然不会导致OOM问题，但是会导致数据库性能变差，同时资源也浪费了。
从buffer pool组成来看，buffer pool实例由buf_pool_t结构体进行管理，主要分为free链表，flush链表，lru链表，mutex锁结构，chunks结构，对于刚初始化完成的buffer pool，所有的缓存页都是空闲的，每个缓存页的控制块会被加入到free链表中。然后当需要访问某个页时，Innodb中通过哈希表来维护缓存页的信息，其中key是表空间号+页号，value是缓存页，如果哈希表中有该页的信息，则直接使用该缓存页即可，否则从free链表中申请一个空闲的缓存页用于存储从磁盘加载的页。随着页加载到buffer pool，也就是内存中，则出现了两个问题，第一，如果数据页太多了怎么办呢？第二，如果页被修改了怎么办呢？对于这两个问题，Innodb中引入了lru链表和lru链表，先说第一个问题吧，对于数据页太多的问题，Innodb中引入了lru链表，lru链表基于的算法是在传统的lru算法上做了改造，对冷热数据进行了拆分，默认的前5/8是young区域，即热数据区域，后3/8是old区域，即冷数据区域，冷热数据是相对的，当页太多以至于buffer pool内存不够的情况下，会从冷数据区域淘汰一部分缓存页，当然，如果是脏页的话，则需要刷回磁盘。另外，Innodb针对LRU链表做了很多的优化，之前的笔记中讲述了一些，这里就不细说了。再说第二个问题，对于页被修改的问题，buffer pool中的缓存页被修改后就和磁盘上的页不一样了。这种缓存页称为脏页。在buffer pool实例中，所有被修改过且需要刷新到磁盘的页的控制块都会加入到flush链表中。然后根据一定的策略刷写到磁盘。根据刷新磁盘的方式又可称为buf_flush_lru和buf_flush_list,对于buf_flush_lru方式，会从lru的冷数据中刷新一部分页面回磁盘，而对于buf_flush_list方式，则会从flush链表中刷新页面到磁盘中。刷新脏页会降低服务的性能，如果某个时候你的服务查询很慢，可能此时系统就在刷脏页。不管free链表，flush链表，lru链表，更多的是从数据管理角度而言，mutex，chunks则是从存储和线程安全角度来设计，mutex的作用是用于控制访问buffer pool实例的线程数，限制一个实例同时只能由一个线程访问，这也和前面建议的在并发高的情况下设置多个buffer pool实例的说法不谋而合。另外，之前也说过，buffer pool申请内存时以chunk为单位，而chunks实际上是一个指针，指向当前buffer pool实例中第一个真正内存页面的首地址，因为页面都是连续存储的，通过chunks指针可以访问所有的其他页面。
缓冲池的部分大体流程熟悉了，从磁盘的角度也是可以做一些优化的，可以调整innodb_io_capacity和innodb_io_capacity_max参数。innodb_io_capacity变量定义每秒用于Innodb后台任务的I/O操作数，即IOPS，该值默认为200，合并插入缓冲时，每秒合并插入缓冲的数量为innodb_io_capacity值的5%，刷新脏页时，每秒刷新脏页的数量等于innodb_io_capacity的值。innodb_io_capacity_max表示在刷新活动落后时，系统会以比innodb_io_capactiy设定的值更高的iops速率进行刷新，但是不会超过innodb_io_capacity_max设定的值。这两个值都是针对写操作而言，不适用于读操作。对于低端ssd盘，设置为200便足够使用了，对于高端的总线连接类型的ssd,可以设置该值更高，例如1000及以上，但是不建议设置过高，例如20000以上，过高会导致从缓冲池删除数据，更改缓冲区的速度太快，以至于缓存失去意义。具体调整该参数建议通过运行sysbench，fio工具或者其他基准工具来进行基准测试。还有，在磁盘io能力强或者ssd场景下，可以考虑修改innodb_flush_method值为o_direct，可以提升写入性能。具体这个值的描述参考官网https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method 。
在mysql中，两阶段提交是一个很关键的知识点，其根本目的是为了数据库中数据的安全和一致性。二阶段提交依赖于binlog和redo log，当然也会用到undo log了，同时和事务密不可分，相辅相成。总体来说，两阶段可以分为prepare和commit两个大阶段，而其中的commit大阶段又可以细分为三个小阶段，flush阶段，sync阶段，commit阶段，这便也是对应着事务提交的三个步骤，相应的产生了flush队列，sync队列，commit队列。事务执行时，会依次加入flush 队列，sync队列，commit队列，最后完成提交。在高鹏大佬的书中有详细的事务提交流程图，但是比较繁杂，不利于记忆，根据关键信息自己进行了简略以作记录，便于后期复习。
对上图的详细补充如下：
1.在prepare阶段，会先将上次commit队列中的最大seq number写入本次事务的last commit。更改事务状态并写事务状态和xid到undo，生成xid_event写入binlog cache。
2.在flush，sync，commit阶段会有不同的锁，然后在下一阶段会释放上一阶段的锁。在flush阶段会获取lock log，然后到sync阶段会释放lock log,然后获取lock sync,而到了commit阶段，会释放lock sync，获取lock commit，在commit阶段的结尾，会释放lock commit。
3.在flush阶段生成gtid和seq number，结合prepare阶段写入的last commit生成gtid event，gtid_event是直接写入binlog中的，也是事务的第一个event，而其他event，例如query_event,map_event,dml_event,xid_event则是先写入到binlog cache中。
4.sync_binlog参数有两方面的作用，一方面控制队列中事务在sync阶段的末尾是否进行刷盘。另外一方面控制dump线程发送event的时机，如果sync_binlog!=1,则在事务其他event写入binlog cache后唤醒dump线程发送event，如果sync_binlog=1，则在sync阶段末尾刷盘后唤醒dump线程发送event。
5.在半同步模式下，如果是使用的after sync,则会在commit队列固化后进行ack确认，如果是after commit，则会在释放lock commit后进行ack确认。
6.在flush，sync，commit队列中，在队列为空的时候，第一个加入的事务担任队长，并在后续代表其他事务执行提交操作，只有上一组事务从某个队列退出了，下一组事务才能加入，否则需要等待。
7.在存储引擎层进行提交时，提交方式受binlog_order_commits参数影响，如果值为1或者on，则按照队列顺序进行提交，如果是0或者off，则各个线程在被leader线程唤醒后自行提交，提交时会写redo日志并修改事务的状态。
根据MVCC中涉及到的相关知识点，我将mvcc划分为以下四个部分，底座，依赖，原理，作用。如下图:
对于底座，容易理解，就像房子的地基一样，对于Innodb存储引擎，每条记录都有trx_id和roll_pointer两个隐藏字段，当事务对某条聚簇索引记录进行修改时，会将该事务的事务id值赋值给trx_id字段，然后roll_pointer列类似于一个指针，可以通过其寻找记录的历史信息，这些历史信息便是存储在undo日志中。
对于依赖，便是undo日志了，具体可以参考上面的undo日志的知识点，针对insert，update,delete，undo日志分别是进行了不同的操作的。
而对于mvcc的原理，核心便是readview了。但是对于read committed和repeatable read模式下，readview的生成时机是不同的，先了解下readview的组成吧。具体由四部分组成:
m_ids:该值表示生成readview时当前系统中活跃的读写事务的事务id列表，需要注意的是，只读事务中的事务id默认为0。
min_trx_id:该值表示生成readview时当前系统中活跃的读写事务中最小的事务id,即m_id中的最小值。
max_trx_id: 该值表示生成readview时系统应该分配给下一个事务的id值。
creator_trx_id:该值表示生成该readview的事务的事务id，需要注意的是，只有对表中记录做修改时，才会为事务分配事务id。
然后通过readview，在访问某条记录时，会按照以下规则进行判断:
对于上图补充如下:
1.如果被访问版本的trx_id与readview中的creator_trx_id值相同，则该版本是当前事务自己修改的记录。
2.如果被访问版本的trx_id小于readview中的creator_trx_id值，则该版本事务在当前事务生成readview前已经提交。
3.如果被访问版本的trx_id大于readview中的creator_trx_id值，则该版本事务在当前事务生成readview后才开启。
4.如果被访问版本的trx_id介于min_trx_id和max_trx_id之间且trx_id在m_ids中，则该版本事务在当前事务生成readview时还是活跃的。
5.如果被访问版本的trx_id介于min_trx_id和max_trx_id之间且trx_id不在m_ids中，则该版本事务在当前事务生成readview时已经提交。
6.如果某个版本数据不可见，则顺着ptr链表找到下一个版本数据按照上述进行再次判断直至最后一个版本。若最后一个版本也不可见，则查询结果不包含该记录。
然后对于read committed隔离级别和repeatable read隔离级别，生成readview的时机是不同的，在read committed隔离级别下，事务在每次查询开始时候会生成一个独立的readview，在repeatable read隔离级别下，事务仅在第一次执行快照读时生成readview，后续快照读复用之前的readview，此时不会产生幻读问题，但是如果两次快照读之前存在当前读，则会重新生成readview。
mysql是插件式数据库，可以同时支持多种存储引擎，binlog是server层的，而redo日志是innodb引擎特有的。mysql服务异常时，其他数据库需要通过binlog恢复，而且，在mysql中，使用两阶段提交来保证数据的一致性。如果不是先写binlog，在使用其他引擎时由于binlog未写入成功可能会导致数据无法恢复。另外，在进行实例恢复时，mysql会根据事务的状态来判断是否进行回滚，对于活跃状态的事务，可以直接进行回滚，如果事务是prepare状态，但是事务对应的binlog已经记录，则直接提交。
事务可以分为只读事务和读写事务。对于只读事务，通过start transaction read only开启，不能对普通表进行增删改操作，只能对临时表进行增删改操作，且只有在第一次对临时表进行增删改的时候才会分配事务id，对于读写事务，可以通过start transaction read write方式开启，或者使用begin，start transaction时也默认为读写事务。读写事务也只有第一次对某个表进行增删改操作时才会分配事务id。如果事务中全是查询语句，是不会分配事务id的。
mysql中的不同操作记录到binlog中对应着不同的event，当一个事务中包含大量数据记录变更时，因为每个event记录的数据大小有限，为8192字节左右，因此大事务中可能会包含多个dml event，每个dml event会在下一个dml生成前被写入binlog cache临时文件，以便减少内存损耗。而事务会在提交后才会清空对应自己的binlog cache文件，这有时会导致磁盘空间的使用率很高，而且通过普通ls等命令不一定能找到文件，可以通过lsof|grep delete命令观察到这种以MY开头的临时文件。
以上便是buffer pool与磁盘为主线，以事务执行为线索，结合redo,binlog,事务提交流程，两阶段提交，mvcc所做的整合了。来来回回磨蹭了很久，总是感觉有一些知识点还是欠缺。第一次尝试从点到面将知识点串联起来。个中辛酸，深有体会。整个过程怎么说呢，很痛苦，很艰难，熟悉一个点比较简单，但是形成一个面时你会觉得这个地方你不太熟悉，那一块有些理解不到位。好了，不说了，今天就到这吧，最近遇到一个不错的case，觉得很有意义，有空再总结出来分享给你们吧。最后，一句比较喜欢的话，送个每个拼搏的人，也送给我自己。我不去想是否能够成功，既然选择了远方，便只顾风雨兼程。
END
原文作者：DBA修行之路；如有侵权，请联系管理员删除。
