https://www.cnblogs.com/xingxia/p/mysql_operation.html。
https://www.cnblogs.com/wyq178/p/11576065.html

一、Mysql主要架构

image.png

Mysql主要分为Server层和存储引擎层

Server层

Server层： 主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。

存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块

连接器

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以key-value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器：
mysql 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：

第一步： 词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如：select，提出查询的表，提出字段名，提出查询条件等待，做完这些操作后，就会进入第二步 。

第二步：语法分析， 主要是判断你输入的sql是否正确，是否符合mysql的语法。

完成这2步之后，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器：

优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器：

当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

存储引擎

（1）加载缓存数据到缓冲池（Buffer Pool）
（2）写入数据的旧值到undo日志文件
（3）在缓冲池（Buffer Pool）中更新内存数据
（4）写redo log Buffer
(5) 写入binlog Buffer
（6） 准备提交事务
（7）redo日志刷入磁盘
（8） binlog日志写入磁盘

二、语句分析

1、查询语句

说了以上这么多，那么究竟一条sql语句是如何执行的呢？其实我们的sql可以分为2中，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

  1. select * from tb_student  A where A.age='18' and A.name='张三';

结合上面的说明，我们分析下这个语句的执行流程：

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在mysql8.0版本以前，会先查询缓存，以这条sql语句为key在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
  • 通过分析器进行词法分析，提取sql语句的关键元素，比如提取上面这个语句是查询select，提取需要查询的表名为tb_student,需要查询所有的列，查询条件是这个表的id=’1’。然后判断这个sql语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
  • 接下来就是优化器进行确定执行方案，上面的sql语句，可以有两种执行方案：

a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是18。 b.先找出学生中年龄18岁的学生，然后再查询姓名为“张三”的学生。

  • 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
  • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2、更新语句

以上就是一条查询sql的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql语句如下：

  1. update tb_student A set A.age='19' where A.name='张三';

我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，mysql 自带的日志模块式binlog（归档日志），所有的存储引擎都可以使用，我们常用的InnoDB引擎还自带了一个日志模块redo log，我们就以InnoDB模式下来探讨这个语句的执行流程。流程如下：

  • 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
  • 然后拿到查询的语句，把 age 改为19，然后调用引擎API接口，写入这一行数据，InnoDB引擎把数据 保存在内存中，同时记录redo log，此时redo log进入prepare状态，然后告诉执行器，执行完成了，随时可以提交。
  • 执行器收到通知后记录binlog，然后调用引擎接口，提交redo log 为提交状态。
  • 更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗？这就是之前mysql的模式了，MyISAM引擎是没有redo log的，那么我们知道它是不支持事务的，所以并不是说只用一个日志模块不可以，只是InnoDB引擎就是通过redo log来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么redo log 要引入prepare预提交状态？这里我们用反证法来说明下为什么要这么做？

  • 先写redo log 直接提交，然后写binlog，假设写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
  • 先写binlog，然后写redo log， 假设写完了binlog，机器异常重启了，由于没有redo log，本机是无法恢复这一条记录的，但是binlog又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用redo log两阶段提交的方式就不一样了，写完binglog后，然后再提交redo log就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设redo log处于预提交状态，binglog也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于Mysql处理机制了，mysql的处理过程如下：

  • 判断redo log是否完整，如果判断是完整的，就立即提交。
  • 如果redo log只是预提交但不是commit状态，这个时候就会去判断binlog是否完整，如果完整就提交redo log，不完整就回滚事务。

    3、查询语句补充：

    image.png

    (1)查询缓存

大多数情况下建议不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

(2)分析器

(1)词法分析

Mysql从你输入的“select”这个关键字识别出来，这是一个查询语句。它也要吧字符串“T”识别成“表明T”，把字符串“ID”识别成“列ID”

(2)语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足Mysql语法。

如果语句不对，就会收到“You have an error in your SQL syntax”的错误题型，比如这个语句select少打了一个“s”.

  2. mysql> elect * from t where ID=1;
  4. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

(3)优化器

经过分析器，Mysql就知道你要做什么了，在开始执行之前，还要经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的Join

  2. mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
  • 即可以先从表t1里面取出c=10的记录ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20.
  • 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择哪一个方案。

疑问？ 1、优化器如何开展工作，总不能是分别执行看一看那个执行效率快。 然后再进行选择执行吧？ 好奇如何分辨以及这个思路。 2、Mysql的优化器执行有没有前置条件？

（4）执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示（在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证，查询也会在优化器之前调用precheck验证权限）。

  2. mysql> select * from T where ID=10;
  4. ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1、调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3、执行器将上述遍历过程中所有满足的行组成的记录集作为结果集返回给客户端

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。我们后面会专门有一篇文章来讲存储引擎的内部机制，里面会有详细的说明。

**