学习笔记-sql的执行过程MVCC与BufferPool

一条SQL在MySql中的执行过程

Server层

  主要包括连接器、查询缓存、分析器、优化器、执行器等。覆盖了大多数MySql的核心服务功能，以及所有的内置函数，所有跨存储过程的功能都是这一层实现的，例如：存储过程、视图触发器等。

连接器

  我们知道MySql是开源的，有很多的连接客户端工具，例如Navicat、IDEA、JDBC等。这些客户端要向MySql通信必须要先跟Server层建立连接，而建立连接的工作是由连接器来完成的。
  你连接上数据的时候，等待你的是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接等。

mysql -h host -u root -p password -P 3306

  连接命令命中的MySql是客户端工具。用来跟服务器建立连接，在完成TCP握手后，就要开始校验你的身份，这个时候用的就是你输入的用户和密码。连接完成后，如果没有后续的动作，这个连接就是处于空闲状态，如果长时间没有后续的动作就会断开。

查询缓存

  当你执行select语句的时候，MySql拿到一个查询请求后，会先去查询缓存中看看，之前有没有执行过该语句，就会去查询缓存中查找对应的key，如果命中对应的key ，那么就会给这个value直接返回给客户端。如果没有命中就会执行后面的阶段。

大多数情况查询缓存都是鸡肋

  因为往往查询缓存都是弊大于利，查询缓存失效非常频繁，只要对一个表进行更新，这个表中的所有查询缓存都会失效。

分析器

  该阶段会进行词法分析和语法分析，首先会进行词法分析，一般我们写的写的sql由字符串和空格组成，MySql需要识别对应的字符串分别代表什么“select”，“update”等。
  做完词法分析后悔做语法分析， 语法分析器会根据语法规则，判断你输入的语法是否满足MySql的语法规则

语法分析原理

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

优化器

  经过了分析器，MySql就知道你要做什么了，在开始之前之前，还要经过优化器的处理。优化器在表里面有多个索引的时候，决定使用哪个索引；或者有多个表关联查询的时候，会决定各个表的连接顺序。

执行器

  在开始执行之前，要先判断你对这个表是否有权限，如果没有权限就会返回权限的相关报错提示。如果有权限，就打开表继续执行，执行器会根据表的引擎定义，去使用这个引擎的提供的接口。

Store层

  存储引擎层负责数据的存储和提取。其架构师插件式的，支持多种存储引擎。

MVCC多版本并发控制机制

  MySql在可重复读隔离级别下如何保证，事务较高的隔离性，这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和协写两个操作默认是不会通过加互斥锁来保证隔离性，避免频繁加互斥锁，而在串行化隔离级别为了保证交个的隔离性就是通过将所有的操作来加互斥锁来实现的。

undo日志版本链与redo view机制

  undo日志版本链是指一行数据被多个事务依次修改后，在这个事务修改完后，MySql会保存修改前的数据。undo回滚日志中有两个隐藏的字段trx_id和roll_pointer指针,这些undo日志串联起来形成了一个历史记录版本链。
  在可重复读的隔离级别，当事务开启事务，执行查询sql时会生成事务的一致性视图read-view，该视图在事务结束止之前都不会发生变化。这个视图由执行查询时所有未提交事务组的id数组和已经创建的最大事务组成，事务里的任务sql查询结果需要从对应版本链里面的最新数据开始逐条执行，更redo-view做对比得到最终的结果。

总结

  MVCC机制的实现就是通过read-view机制与redo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链的不同版本数据。

InnoDB引擎sql执行的BufferPool缓存机制

1. 加载缓存数据，把对应id的整页数据到BufferPool中
2. 写入更新更新数据的便于回滚
3. 更新内存数据
4. 写入redo日志
5. 准备提交事务redo日志写入磁盘
6. 准备提交事务binlog日志写入磁盘
7. 写入commit标记到redo日志文件里，提交事务完成后，该标记为了保证事务提交后redo与binlog数据一致。
8. 随机写入磁盘以page为单位写入。

注意：数据库的增删查改都是操作BufferPool，一般设置为内存的60%

为什么MySql不能直接更新磁盘上的数据，而是设置一套复杂的机制

1. 一个请求直接对磁盘文件随机读写，然后更新磁盘文件里的数据，性能相当差。
2. 直接更新磁盘文件，磁盘文件不能抗住很高的并发。
3. MySql这套机制，它可以保证每个更新请求都是更新内存BufferPool，然后顺序写入日志文件，同时还有保证各种异常情况下的数据是一致的。
4. 更新内存性能极高，然后顺序写入磁盘文件的日志的性能也是非常高的，要远远高于随机磁盘文件。

public class OrderAndRandomRead {

    public static void main(String[] args) {
        int[][] arr = new int[50000][50000];
        int sum = 0;
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < arr.length; i++) {
            for (int j = 0; j < arr[0].length; j++) {
                sum += arr[i][j];
            }
        }
        System.out.println("按行顺序访问耗时：" + (System.currentTimeMillis() - startTime) + " ms");
        sum = 0;
        startTime = System.currentTimeMillis();
        for (int i = 0; i < arr.length; i++) {
            for (int j = 0; j < arr[0].length; j++) {
                sum += arr[j][i];
            }
        }
        System.out.println("按列随机访问耗时：" + (System.currentTimeMillis() - startTime) + " ms");
    }
}

  由上图可以知道顺序和随机问磁盘的差别还是非常大的，我们在实际开发中如果能进行顺序访问进行一定要采用顺序访问。