MySQL：一条sql查询/更新语句是如何执行的

一、mysql架构示意图

mysql
	-- Server层：(覆盖mysql的大多数核心功能，以及内置函数，比如：存储过程、触发器、视图)
        	连接器：负责跟客户端建立连接、获取权限、维持和管理连接
        	查询缓存：先从缓存中进行查询，没有执行后面的流程
        	分析器：对输入的多个字符串和空格组成的mysql进行语法分析
    		优化器：有多个索引的时候，决定使用哪一个，多表查询的时候，决定各表的连接顺序
        	执行器：开始执行的时候判断对这个表是否有权限，没有权限就报错，有权限，就打开表继续执行，执行器会根据表的引擎定义，去使用这个引擎提供的接口
    
    -- 存储引擎: (负责数据的存储和提取，支持InnoDB，MyISAM，Memory，mysql5.5之后默认InnoDB)

二、mysql的查询流程

1.连接器

首先是是通过连接器进行连接，连接命令一般是：

mysql -h$ip -p$port -u$user -p$password

完成TCP握手后，连接器就开始认证你的身份，如果认证通过，连接器会在权限表中查询你拥有的权限，如果拥有权限，就意味着一个用户成功建立连接。连接完成之后如果，没有后续动作，这个连接就是空闲状态，可以用show processlist命令查看

下面我开启俩个命令窗，进行连接，其中的Command列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时，如果断开连接后，客户端在发请求就会报错。

mysql建议使用长连接，可是全部使用长连接之后，mysql的内存涨的很快，这是因为 MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

解决方案：

定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2.查询缓存

建立连接完成之后，mysql拿到一个查询结果，会先到查询缓存中看看，之前是否执行过这条语句，之前执行过的语句及结果可能会以key-value的形式，直接缓存在内存中。key的查询语句，value是查询结果。如果在查询缓存中没有找到，就继续执行后面的流程，执行完成之后，执行结果会放到查询缓存中
大多数情况下不建议使用查询缓存：

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

你可以将参数query_cache_type设置成 DEMAND，这样对于默认的SQL语句都不使用查询缓存

对于确定要使用查询缓存的语句可以使用SQL_CACHE,例如：

select SQL_CACHE * from T where ID=10

注意：MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了

3.分析器

如果没有命中查询缓存，就要开始执行真正的执行语句了。先对sql语句进行词法分析，把select关键字识别，把字符串T，识别为表名T，然后在做语法分析，根据语法规则，检查是否合法

4.优化器

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

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

5.执行器

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

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

举例：

select * from T where ID=10;

执行流程：

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

三、mysql的更新流程

更新流程跟查询流程差不多的，不同的是更新流程涉及俩个重要的日志模块：**redo log （重做日志）**和 binlog（归档日志）

redo log

在mysql中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后进行更新，整个过程IO成本，查找成本都很高，这里使用了WAL(Write-Ahead Logging)技术，他的关键点技术先写日志，在写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB引擎会先把记录写在redo log里面，更新内存，这个时候更新就算完成了，同时，InnoDB会在适当的时候，把这个操作更新到磁盘里面，更新的时候往往是系统比较空闲的时候。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是 1GB。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示

write pos：当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头

checkpoint：当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

write pos和checkpoint之间的是redo log上还空着的部分，可以用来记录新的操作。如果write pos 追上checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一下

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

binlog

redo log是 InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）

俩种日志的不同点：

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用
redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”
redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

更新一条sql的流程：

update T set c=c+1 where ID=2

执行器先找引擎取ID=2这一行行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务
执行器生成这个操作的binlog，并把binlog写入磁盘
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成
最后三步看上去有点“绕”，将redo log的写入拆成了两个步骤：prepare和 commit，这就是"两阶段提交"。

俩阶段提交

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

仍然用前面的update语句来做例子。假设当前ID=2的行，字段c的值是0：

先写redo log后写binlog：

假设在redo log写完，binlog还没有写完的时候，MySQL进程异 常重启，由于redo log写完，所有恢复后这一行c的值是1。可是由于binlog没有写完就发送异常，这个时候binlog没有记录这个语句，如果需要用binlog来恢复临时库的时候，由于binlog丢失，这个恢复临时库就会少异常更新，恢复出来你这一行c的值就是0，与原库的值不同

先写binlog后写redo log：

如果在binlog写完之后就发送异常，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同

四、总结

redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证 MySQL异常重启之后数据不丢失
sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失

MySQL：一条sql查询/更新语句是如何执行的