select语句和update语句分别是怎么执行的吗

本文由 简悦 SimpRead) 转码， 原文地址 mp.weixin.qq.com)

1 select 语句

总结：连接器；检索内存中是否缓存、分析器、优化器、执行器；存储引擎
当执行一条查询的 SQl 的时候大概发生了以下的步骤：

1. 客户端发送查询语句给服务器。服务器首先进行用户名和密码的验证以及是否拥有执行表的权限进行校验。
2. 然后会检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。（在 Mysql 8 版本中的时候把缓存这一块给砍掉了。）
3. 接着进入分析器，对 SQl 的解析、语法检测和预处理，
4. 再由优化器生成对应的执行计划。（如是否使用索引，多表查询顺序问题等）
5. Mysql 的执行器根据执行计划调用存储引擎的接口进行查询，将查询的结果返回客户端。

执行的流程

注意：在 Mysql 8 版本中的时候把缓存这一块给砍掉了。

这里以一个实例进行说明 Mysql 的的执行过程，新建一个 User 表，如下：

// 新建一个表  
DROP TABLE IF EXISTS User;  
CREATE TABLE `User` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(10) DEFAULT NULL,  
  `age` int DEFAULT 0,  
  `address` varchar(255) DEFAULT NULL,  
  `phone` varchar(255) DEFAULT NULL,  
  `dept` int,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;  
// 并初始化数据，如下  
INSERT INTO User(name,age,address,phone,dept)VALUES('张三',24,'北京','13265543552',2);  
INSERT INTO User(name,age,address,phone,dept)VALUES('张三三',20,'北京','13265543557',2);  
INSERT INTO User(name,age,address,phone,dept)VALUES('李四',23,'上海','13265543553',2);  
INSERT INTO User(name,age,address,phone,dept)VALUES('李四四',21,'上海','13265543556',2);  
INSERT INTO User(name,age,address,phone,dept)VALUES('王五',27,'广州','13265543558',3);  
INSERT INTO User(name,age,address,phone,dept)VALUES('王五五',26,'广州','13265543559',3);  
INSERT INTO User(name,age,address,phone,dept)VALUES('赵六',25,'深圳','13265543550',3);  
INSERT INTO User(name,age,address,phone,dept)VALUES('赵六六',28,'广州','13265543561',3);  
INSERT INTO User(name,age,address,phone,dept)VALUES('七七',29,'广州','13265543562',4);  
INSERT INTO User(name,age,address,phone,dept)VALUES('八八',23,'广州','13265543563',4);  
INSERT INTO User(name,age,address,phone,dept)VALUES('九九',24,'广州','13265543564',4);

现在针对这个表发出一条 SQl 查询：查询每个部门中25岁以下的员工个数大于3的 员工个数和部门编号，并按照人工个数降序排序和部门编号升序排序 的前两个部门。

SELECT
    dept,
    COUNT( phone ) AS num 
FROM
USER 
WHERE
    age < 25 
GROUP BY dept 
HAVING num >= 3 
ORDER BY
    num DESC,
    dept ASC 
    LIMIT 0,2;

连接器

开始执行这条 sql 时，首先会校验你的用户名和密码是否正确，若是不正确会返回错误信息："Access denied for user"；

若是用户名和密码校验通过，然后就会到权限表获取当前用户拥有的权限，会检查该语句是否有权限，若是没有权限就直接返回错误信息，有权限会进行下一步。

检索缓存

检查在缓存区域看看这个 sql 与否履行过，若是之前执行过，它的执行结果会以Key-Value的形式缓存于内存中，Key 是执行的sql，Value 是结果集。

假如，缓存中 key 遭击中，便会直接将结果返回给客户端，假如没命中，便会履行后续的操作，完工之后亦会将结果缓存起来以便再次查询获取，当下一次进行查询的时候也是如此的循环操作。

注意：Mysql 中的缓存比较适合于那些静态的表，更新不频繁的表，因为只要当前表有数据更新，有关于该表的缓存就会失效，若是表更新频繁缓存频繁的失效，这样维护缓存的消耗的性能远大于使用缓存带来的性能优化，这样就会得不偿失，严重影响 Mysql 的性能，所以在 Mysql 8 版本中的时候把缓存这一块给砍掉了。

在个人的观点中对于缓存这一块的看法是，没必要砍掉，可以设置成默认关闭缓存，需要的时候再设置开启，并且可以通过配置参数指定特定的表使用缓存，那些表不使用缓存，这样或许使用缓存更有效。

分析器

分析器主要有两步：（1）词法分析（2）语法分析

词法分析主要执行提炼关键性字，比如 select，提交检索的表，提交字段名，提交检索条件，确定该语句是 select 还是 update 或者是 delete 语句。

语法分析主要检查输出的sql与否准确，是否合乎mysql的语法，若是不符合 sql 语法就会抛出：You have an error in your SQL syntax。

优化器

查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。

例如：在查询语句中有多个索引的时候，优化器决定使用哪一个索引，或者有多表关联的时候，决定表的连接顺序等这些操作都是在优化器决定的。

生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。如果在一条 SQL 语句执行的过程中将该语句对应的最终执行计划进行缓存。

当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过 SQL 语句生成执行计划的整个过程，进而可以提高语句的执行速度。
MySQL 使用基于成本的查询优化器。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最少的一个。

执行器

优化器生成得执行计划，交由执行器进行执行，执行器中循环的调用存储引擎的读接口，以此换取满足条件的数据行，并把它放在一个结果集中，遍历并获取了所有满足条件的数据行，最后将结果集返回，结束整个查询得过程。

2 update 语句

select 语句的执行过程会经过 连接器、检索内存中是否缓存、分析器、优化器、执行器、存储引擎，同样的 update 语句也会同样走一遍 select 语句的执行过程。

但是和 select 最大不同的是，update 语句会涉及到两个日志的操作redo log（重做日志） 和binlog （归档日志）

mysql的redo log使用了两阶段提交的方式。
会先写入redo log，此时redo log是预处理阶段，然后通知执行器提交事务，mysql将数据写入binlog日志中，提交事务，然后redo log的状态为已提交。

redo log 重做日志-顺序写入

redo log重做日志属于innodb引擎层面。

大家都是知道 Mysql 是关系型数据库，用来存储数据的，在访问数据库量大的时候，Mysql 读写磁盘访问的效率是非常低的，加上 sql 中的条件对数据的筛选过滤，那么效率就更低了。

这也是为什么引入非关系型数据库作为作为数据缓存原因，例如：Redis、MongoDB等，就是为了减少 sql 执行期间的数据库 io 操作。

同样的道理，若是每次执行 update 语句都要进行磁盘的 io 操作、以及数据的过滤筛选，小量的访问和数据量数据库还可以撑住，那么访问量一大以及数据量一大，这样数据库肯定顶不住。

基于上面的问题于是出现了redo log日志，redo log 日志也叫做 WAL 技术（Write- Ahead Logging），他是一种先将更新记录写入日志，最后再更新磁盘的技术

redo log记录的内容的是数据页的更改，这个页 “做了什么改动”。如：add xx 记录 to Page1，向数据页 Page1 增加一个记录。
redo log是顺序写，哪个数据页做了什么改动都顺序记录下来，然后在写入数据库磁盘，这个就是随机写了，因为需要将数据页对应的更改，在数据库磁盘中进行修改增加，就需要找到对应的位置

redo log 的特点就是：redo log 是固定大小，是物理日志，属于 InnoDB 引擎的，并且写 redo log 是环状写日志的形式：

如上图所示：若是四组的redo log文件，一组为 1G 的大小，那么四组就是 4G 的大小，其中write pos是记录当前的位置，有数据写入当前位置，那么 write pos 就会边写入边往后移。

而check point是擦除的位置，因为 redo log 是固定大小，所以当 redo log 满的时候，也就是 write pos 追上 check point 的时候，需要清除 redo log 的部分数据，清除的数据会被持久化到磁盘中，然后将 check point 向前移动。

redo log 日志实现了即使在数据库出现异常宕机的时候，重启后之前的记录也不会丢失，这就是 crash-safe （奔溃-安全）能力。

binlog （归档日志）

binlog 称为归档日志，是逻辑上的日志，它属于 Mysql 的 Server 层面的日志，记录着 sql 的原始逻辑，主要有两种模式，一个是 statement 格式记录的是原始的 sql，而 row 格式则是记录行内容。

那么这样看来 redo log 和 binlog 虽然记录的形式、内容不同，但是这两者日志都能通过自己记录的内容恢复数据，那么为什么还要这两个日志同时存在呢？只要其中一个不就行了嘛

因为刚开 Mysql 自带的引擎 MyISAM 就没有 crash-safe 功能的，并且在此之前 Mysql 还没有 InnoDB 引擎（redo log重做日志是innodb的），Mysql 自带的 binlog 日志只是用来归档日志的，所以 InnoDB 引擎也就通过自己 redo log 日志来实现 crash-safe 功能。

update 执行过程

update user set age=age+1 where id =2；

上面说过 select 语句走过的流程 update 语句也会走一遍，当来到执行器的时候：

1. 执行器会调用引擎的读接口，然后找到 id=2 的数据行，因为 id 是主键索引，索引按照树的搜索找到这一行，若是数据行已经存在于内存的数据页中就会立即将结果返回，若是不在内存中，就会从磁盘中进行加载到内存中，然后将查询的结果返回。
2. 然后，执行器将返回的结果的 age 字段 + 1，并调用引擎的写接口写入更新后的数据行。
3. 引擎获取到更新后的数据行 更新到内存和redo log中，并告诉执行器可以随时提交事务，此时的redo log处于prepare阶段。
4. 执行器收到引擎的告知后，生成binlog日志，并且调用引擎的接口提交事务，引擎将redo log的状态修改为commit状态，这样这个更新操作算是完成。

两阶段提交

上面详细的说了 update 语句的执行流程，提到了 redo log 的 prepare 和 commit 两个阶段，这就是两阶段提交，两阶段提交的目的是为了保证 redo log 日志与 binlog 日志保持数据的一致性。(redo log 刚开始是prepare准备状态，写入binlog日志后才，才提交redo log的内容)

若是 redo log 写成功 binlog 写失败，或者 redo log 写失败 binlog 写成功，最后使用这两者日志进行数据恢复得到的结果数据都是不一致性的，所以为了保证两个日志逻辑上的一致，使用两阶段进行提交。

redo log 与 binlog 的总结

redo 是物理的，binlog 是逻辑的，redo 的大小固定，并且以环状的形式写入数据，数据满的时候需要将 redo 日志中擦除数据，并且将擦除的数据持久化到磁盘中。

而 binlog 以追加日志的形式写入，也就是当日志写到一定大小后，就会切换到下一个，并不会覆盖以前写的日志。

binlog 是在 Mysql 的 Server 层中使用，因为 binlog 没有 crash-safe 功能，所以 InnoDB 引擎自己实现了 redo log 日志的 crash-safe 的功能，为了保证这两个日志逻辑上的一致使用两阶段提交。

在使用 redo 和 binlog 这两种日志的时候，可以将参数innodb_flush_log_at_trx_commit和sync_binlog都设置为 1，它表示每次事务提交的时候，都会将日志持久化到磁盘中。