mysql优化七：mysql内部执行流程和mvcc机制

mysql内部执行流程和mvcc机制

MySQL的内部组件结构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。
Server层
主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数 （如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层
负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在 最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定 表的存储引擎类型,默认会给你设置存储引擎为InnoDB。

server 层组件分析

连接器
由于MySQL是开源的，有非常多种类的客户端：navicat,mysql front,jdbc,SQLyog等非常丰富的客户端。这些客户端要向mysql发起通信都必须先跟Server端建立通信连接，而建立连接的工作就是有连接器完成的。
连接命令一般是这么写的： mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306
查询缓存
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找 到 key，那么这个 value 就会被直接返回给客户端。
但实际上呢，是个鸡肋，何为鸡肋。三国杨修给了答案：食之无味弃之可惜。
因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。 因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率 会非常低。
mysql8.0已经移除了查询缓存功能
分析器
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
分析器分成6个主要步骤完成对sql语句的分析。具体每个步骤如何分析的就不去研究了

1. 词法分析
2. 语法分析
3. 语义分析
4. 构造执行树
5. 生成执行计划
6. 计划的执行

优化器
经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。顾名思义，就是对sql的内部优化。之前讲过trace工具对sql进行分析。第一阶段sql格式化，就是分析器进行了分析。第二阶段优化就是优化器进行优化。内部索引选择啊，join驱动表的分析等等。
执行器
执行真正开始执行sql，从数据库中获取数据，返回结果集。

存储层

mysql的server层了解即可，基本不会有太大的变化。但是存储层就不一样，每个存储引擎有着不同执行逻辑。上面讲到的执行器，就是调用了不同的存储引擎接口来获取数据。例如innodb支持行锁，支持事务。myisam就不支持事务和行锁，myisam存储引擎的表的总行数会被 mysql存储在磁盘上，count查询不需要计算，效率就很高。因此不同存储引擎，它们的接口是不一样的。下面会针对Innodb进行深入学习。

MVCC多版本并发控制机制

Mysql在可重复读隔离级别下如何保证事务较高的隔离性，在优化六案例中，同样的sql查询语句在一个事务 里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。
这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认 是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥。
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。说到mvcc机制就必须了解undo日志版本链与read view机制。
undo日志
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。trx_id是事务id，roll_pointer是指向上一个undo日志的指针。

一致性视图read-view
在可重复读隔离级别，当事务开启，首次执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)。这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
举个例子，比如有个事务，在首次进行select时就会生成reda-view。这个read-view在这个事务结束前都不会改变(在可重复的隔离级别情况下)。此时如果有事务id=100的，事务id=120，的事务id=80，事务id=200的事务未提交，有最大的事务id=300的已提交。那么read-view就是 [80,100,120,200],300。其中80就是min_id，300就是max_id， 而[80,100,120,200]这一部分呢就是未提交数组。
上述的例子，如果在生成reda-view时最大的事务id 300也是未提交，那么那么read-view就是 [80,100,120,200,300]
版本链比对规则
1 如果undo日志的trx_id<min_id，表示这个版本是已提交的事务生成的，这个数据是可见的；
2 如果undo日志的trx_id>max_id，表示这个版本是由将来启动的事务生成的，是不可见的
3.如果undo日志的trx_id 就是当前自己的事务是可见的；
4 如果undo日志的trx_id(min_id <=trx_id<= max_id)，那就包括两种情况

• 若undo日志的trx_id在未提交数组中，表示这个版本是由还没提交的事务生成的，不可见；
• 若 row 的 trx_id 不在未提交数组中，表示这个版本是已经提交了的事务生成的，可见

根据undo日志版本链的图举个案例：

1. 假设有事务id=80的事务插入了id=1，name=lilei的数据。并已提交，那么就会生成一个undo日志就是上图的最后一条数据，并且roll_pointer是没有指向的。因为这是lilei这条数据的起始版本。
2. 开启事务id=100和id=200的事务，但还未做操作。有事务id=300的事务执行update account set name = 'lilei300' where id = 1;并且提交。那么此时undo日志就会如上图的倒数第二条，并且roll_pointer指向最后一条。开启一个事务id=220用于查询,执行select name from account where id = 1; 那么首次执行select就会生成 --readview:[100,200,220], 300 根据版本链比对规则，此时最新的undo日志版本就是trx_id=300的。300不在未提交事务组里面，所以可见，因此查询到的结果为 lilei300；
3. 然后事务id=100的事务执行了
   update account set name = ‘lilei1’ where id = 1;
   update account set name = ‘lilei2’ where id = 1;
   那么此时undo日志版本链如上图的第三第四条。最新的undo日志版本链就是name = 'lilei2’那一条undo日志。
4. 然后事务id=220再次查询：select name from account where id = 1;由于read-view不可变 --readview:[100,200,220], 300 。最新的undo日志版本是ame = 'lilei2’的数据，trx_id = 100。根据版本链比对规则，100在未提交数组里面所以不可见，那就根据roll_pointer指针找上一条数据，找到了name = 'lilei1’的数据，但是trx_id = 100，仍然在未提交数组里面，所以不可见，在往上找，找到了name = 'lilei300’的数据，trx_id=300，不在未提交事务组里面，所以可见，因此查询到的结果为 lilei300；
5. 然后事务id=200执行了
   update account set name = ‘lilei3’ where id = 1;
   update account set name = ‘lilei4’ where id = 1;
   此时undo日志就如上图。
6. 然后事务id=220再次查询：select name from account where id = 1;由于read-view不可变 --readview:[100,200,220], 300 。根据上述过程一次次进行比较。查到的结果仍然是 lilei300；这就是在可重复读的隔离级别下，一个事务多次查询结果仍然相同的原因。

在读已提交的情况下，mvcc机制基本相同，不同在于每次select查询时都会生成一个read-view。而不是像可重复度那样read-view是不变的。因此读已提交可以读取其他已提交事务修改的数据。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被 删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数 据。
注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句， 事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。
所以说上面案例事务id=220的那个事务是不存在事务id的，因为还未执行修改操作。
总结： MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

Innodb引擎SQL执行到写入磁盘过程

整个执行过程如上图。server层执行过程都是一样。不同存储引擎执行的过程不一样。这里了以最常用的innodb引擎为例。执行一条update语句时，先后经过连接器、分析器、优化器最后有执行器调用innodb引擎。这里主要分析innodb引擎的执行过程。

1. 先从磁盘中加载对应要修改记录的一页数据到BufferPool(缓存池，一块内存)，缓存池每次加载数据都是一页或者多页加载，不会只加载单条记录。
2. 加载完记录后，将要修改的记录写一份undo日志。便于事务提价失败后回滚。这个undo日志在mvcc机制中也说过。
3. 通过执行器调用innodb引擎将数据更新到缓存池中。再次写入undo日志，形成undo日志版本链
4. 写一份redo日志。redo日志记录就是更新的数据内容。并且redo日志也是先存放在一快内存中
5. redo日志从缓存准备写入磁盘中，此时就是事务的提交，mysql如果不开启事务就是自动commit，如果开启了事务就是等待客户端执行commit。当客户端执行了commit，就是准备从缓存中写入磁盘。
6. 当写完redo日志并准备从缓存中写入磁盘时，server层开始写入binlog日志到磁盘中
7. binlog日志写入磁盘后，写入commit标记到redo日志里面，redo日志写入磁盘。这样保证了事务提交后redo日志和binlog日志数据内容是一致的
8. 内部会有IO线程以页为单位，将缓存池的数据写入磁盘。这个写入的时间是不确定的。

这个过程中可能会有几个疑问？

1. redo日志、undo日志、binlog日志关系。
   redo日志和undo日志是innodb引擎独有的。binlog日志是server层写的，也就是说如果换成myisam，就没有redo和undo，binlog一样会有。binlog日志和redo日志记录内容大致一样。
2. 这几个日志的作用
   redo日志：如果事务提交成功，binlog日志也写入完成，此时客户端就认为数据已经写入完成了。但是数据其实还在缓存池中，如果此时数据库宕机。那么数据丢失。如果有了这个redo日志，在重启数据库的时候就会根据redo日志将数据写入缓存池。这样就保证了缓存池的数据不丢失。
   binlog日志：如果数据库的数据被删除，开启了binlog功能的话，每次对数据库的增删改都会记录到binlog日志中，就可以通过binlog日志恢复数据。 binlog日志恢复的是磁盘数据。redo恢复的是缓存池日志。
   undo日志：用于mvcc机制和事务回滚
3. 为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
   因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。磁盘随机读写的性能是非常差的
   更新磁盘文件是不能让数据库抗住很高并发的。 Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能 保证各种异常情况下的数据一致性。 更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。 正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。