MySQL深入学习---(1)一条SQL查询和更新语句是如何执行的？你们真的了解吗？

一、一条SQL查询语句到底是如何执行的

• 下面从一条最简单的查询语句开始：

select * from T where ID=10；

• MySQL的sql语句执行流程图：

1、连接器：

• 连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

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

• 指定IP地址，port端口号，用户名，密码
  
• 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它
  
• 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

长连接和短连接：
长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

注意点：
建立连接的过程通常是比较复杂的，所以尽量使用长连接，但是全部使用了全连接之后，MySql占用内存涨得特别快，这时因为MySQL在执行语句的时候临时使用的内存是管理在连接对象里面的，这些资源只有在连接断开才会释放，所以会导致占用内存太大。

解决方案：
1、定期断开长连接
2、执行一次较大的操作后，可以通过执行 mysql_reset_connection 来重新初始化连接资源。

查询缓存：

• 连接建立完成后，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。
• 如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果该查询语句不在缓存中，则会将执行结果存入查询缓存中去。

注意点：
查询缓存往往弊大于利，因为只要对一张表的更新，这张表上所有的查询缓存都会被清空。

解决方案：
可以将参数query_cache_type设置为DEMAND，这样对于默认的SQL语句都不使用查询缓存，而对于你确定要使用查询缓存的语句，就可以用SQL_CACHE显示指定，例如：

select SQL_CACHE * from T where ID=10；

2、分析器（要做什么）

• 即对SQL语句做解析；

• 词法分析：MySQL从输入的“select”这个关键字识别出来，明确这是一个查询语句。同时也把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

• 语法分析：接下来会判断输入的SQL语句是否满足MySQL语法规则。

分析器组成：

• 词法分析：将查询分解为多个元素，如：操作关键字、列表/表名和条件关键字等。
• 语法分析：语法规则校验，生成语法分析树(也叫"解析树")。
  验证是否使用错误关键字，关键字顺序是否正确，引号是否正确匹配等。
• 预处理器：根据mysql规则进一步验证解析树合法性，并生成新的解析树。
• 语义检查：根据语法分析树和系统元信息(information schema)进行语义检查，对语法分析树上的各个节点进行语义分析，判断对象是否存在、是否重名。
  (数据表和数据列是否存在就在这个阶段)；解析名字和别名，看看他们是否存在歧义。
• 权限验证：验证用户是否有执行权限

3、优化器（该怎么做）

• 当表里面有多个索引的时候，决定使用哪一个索引；或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。例如：

 select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

1. 这时可以先从表t1里取出c=10的记录的ID值，然后再根据ID值关联到表t2，再判断t2里面的d的取值是否等于20。
2. 也可以先从表t2里面取出d=20的记录的ID值，然后再根据ID值关联到表t1，再判断t1里面的c的值是否等于10。

• 综上所述：这两种执行方法的逻辑结果是一样的，但是执行的效率会不一样，而这时候优化器的作用就是决定选择使用哪一种方案。

4、执行器

• 执行的时候，执行器会先判断一下该用户对这张表是否有查询的权限，若没有则会返回没有权限的错误。若有权限，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。例如：

select * from T where ID=10;

1、该表T中，ID字段没有索引，那么执行器的流程是这样的：

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

2、如果该表T有索引，则执行器的流程是这样的：
（1）调用InnoDB引擎接口取“满足条件的第一行”
（2）循环取“满足条件的下一行”
（3）将所有满足条件的行组成的记录集作为结果返回给客户端

5、总结：

• （1）客户端连接到服务端，获取到权限等信息， 然后在连接的有效时长内(interactive_timeout和wait_timeout参数控制， 5.7版本会断开可以自动重连)对sql进行处理。

• （2）首先会判断查询缓存是否开启，如果已经开启，会判断sql是select还是update/insert/delete，对于select，尝试去查询缓存，如果命中缓存直接返回数据给客户端， 如果缓存没有命中，或者没有开启缓存， 会进入到下一步分析器。

• （3）分析器进行语法分析、词法分析，检查sql的语法顺序等得到解析树， 然后预处理器对解析树进一步分析，验证数据表、字段是否存在，通关之后sql进入下一步优化器，所以课后问题一定是分析器阶段了。

• （4）优化器对sql执行计划分析， 得到最终执行计划，得到优化后的执行计划之后交给执行器。

• （5）执行器调用存储引擎api执行sql，得到响应结果， 将结果返回给客户端，如果缓存是开启状态， 会更新缓存。

6、提出疑问：

• 1、为什么对权限的检查不在优化器之前做？

答：有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的。

• 2、如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误：“Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答：分析器，首先分析器的词法分析器会将k识别为一列。但会不会在语法分析器，判断在T表中找不到这一列，而报错。因为下一步是进入到优化器，在优化器之前sql语句应该不会有问题，否则怎么去进行优化。

二、一条SQL更新语句是如何执行的

• 下面从最简单的一条更新语句开始：

update T set c=c+1 where ID=2;（其中这里ID为主键，c为整型字段）

• 当一张表有更新的时候，跟这张表有关的查询缓存都会失效，其它执行流程和查询语句执行的流程差不多，但是不一样的地方是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

1、重要的日志模块：redo log（InnoDB引擎的日志）

• 这里其实就是MySQL里经常说到的WAL技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

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

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

• write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件，也就是把记录更新到磁盘文件上，这个事情是在系统比较空闲的时候去做的。
• 里面write pos 和 checkpoint 之间还空着的部分可以用来记录新的操作。但是如果write pos 追上checkpoint，则表示不能再执行新的更新操作，的停下来先擦掉一些记录，把checkpoints推进一下。

crash-safe：即InnoDB引擎重要功能可以保证数据库发生异常，也会保证之前提交的记录都不会丢失。

2、重要的日志模块：binlog（Server层的日志）

• binlog日志只能用于归档，并没有crash-safe的能力。但是binlog是不能去掉的，因为redolog只有InnoDB有，别的引擎没有。而且redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备的。
• binlog有两种模式：statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

3、两种日志的不同点

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

4、小结

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