Mysql查询和更新时的执行流程
Mysql基本架构示意图
mysql大体可以分为Server层和存储引擎层两部分。
Server层包括包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎负责存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
//查看连接状态 show processlist;
注意事项
- 客户端太久没动静，连接器会断开。参数wait_timeout控制，默认8小时。Connect_timeout指连接过程中的等待时间。interactive_timeout是关联的时间，和wait_timeout尽量设置相同，值取决于业务。
- 长连接指连接成功后，客户端持续有请求，则一直使用同一个连接。
- 短连接指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
- 建立连接的过程通常非常复杂，建议使用中尽量减少连接的动作，尽量使用长连接。
- 全部使用长连接mysql非常占用内存，因为mysql执行过程中临时使用内存来管理在连接对象里边的。这些资源会在连接断开的时候才释放。如果长连接累积会导致内存占用太大，被系统强行杀掉（OOM），从现象看就是Mysql异常重启了。
- 解决一：定期断开长连接。使用一段时间，或者程序里判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
- 解决二：Mysql5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限校验，但是会将连接恢复到刚刚创建完时的状态。
查询缓存
第一步是建立连接，第二步就是查询缓存。
mysql拿到一个查询请求后，会先查询缓存看看(mysql 8.0将查询缓存的整块功能删掉了没有该功能)。如果能够查询到则直接返回给客户端，不需要执行后面的复杂操作，直接返回结果，这个效率会很高。
如果命中查询缓存，会在查询缓存返回结果的时候做权限校验。调用precheck验证权限。
大多数情况下不建议使用查询缓存
- 查询缓存的失效非常频繁，只要有一个表的更新，这个表上所有的查询缓存都会被清空。还没用就被一个更新清空了。
- 对于更新压力大的数据库来说，查询缓存的命中率会非常低。
- 如果业务是一张静态表，很长时间会更新一次，如配置表，那这张表上查询适合使用查询缓存。
- 按需使用，将参数query_cache_type设置成DEMAND，这样默认sql语句都不使用查询缓存。对于确定使用查询缓存的语句，用SQL_CACHE显示指定。
分析器
没有命中缓存，执行sql语句，分析器会先做“词法分析”，识别关键字，然后做“语法分析”，语句错误会提示第一个错误出现的位置报错。(如表中没有某个列会在这里报错：Unknown column ‘xxx’ in ‘where clause)
优化器
优化器是在表里有多个索引的时候，决定使用哪个索引；或者决定关联表的连接顺序
执行器
首先判断是否有执行的权限，没有返回权限错误
有权限，继续执行，执行器根据表的引擎定义提供接口。
执行流程
- 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
- 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
- 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
- 如果有索引的表，第一次调用的是”取满足条件的第一行“这个接口，之后循环取”满足条件的下一行“这个接口，这些接口都是引擎中已经定义好的。
- 在数据库的慢查询日志中可以看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
- 在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数根rows_examined并不完全相同。
查询执行完毕！
一条更新sql语句
更新与查询不一样的是：更新流程还涉及两个重要的日志模块，redo log(重做日志)和binlog(归档日志)。
日志模块：redo log
- WAL技术(Write-Ahead Logging)，先写日志，再写磁盘。
- 当有一条记录需要更新的时候，InnoDB引擎会把记录写到redo log里面，并更新内存，这个时候就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，系统比较空闲的时候做。
- InnoDB的redo log是固定大小，比如可以配置一组4个文件，每个文件大小1GB，那么共可以记录4GB的操作。从头开始写，写到末尾回到开头循环写。
- write pos是当前记录的位置，一边写一边后移，checkpoint是当前要擦除的位置，往后推移并循环，擦除记录前要把记录更新到数据文件。
- write pos和checkpoint之间空的部分用来记录新的操作。如果write pos追上checkpoint，不能执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。
- 有了 redo log，InnoDB可以保证即使数据库发生异常重新，之前提交的记录都 不会丢失，这个能力成为crash-safe。
日志模块：binlog
Mysql一层是Server层负责功能层面，一层是引擎负责存储相关。redo log是InnoDB引擎特有的日志，而Server层自己的日志成为binlog（归档日志）。
两种日志的区别
- redo log是InnoDb引擎特有的；binlog是Mysql的Server层实现，所有引擎都可以使用；
- redo log是物理日志，记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志，记录的是这个语句的原始逻辑
- redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写入”指binlgo文件写到一定大小会切换到下一个，并不会覆盖以前的日志。
update流程
- 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。
redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化道磁盘。这个参数建议设置成1，这样可以保证msql异常重启之后数据不丢失。
sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化道磁盘。参数建议设置成1，这样可以保证msql异常重启之后binlog不丢失。
总结：
1. prepare阶段 2. 写binlog 3. commit
- 当在2之前崩溃时，重启恢复：发现没有commit，回滚。备份恢复：没有binlog。一致
- 当在3之前崩溃时，重启恢复：虽然没有commit，但满足prepare和binlog完整，所以重启后回自动commit。备份：有binlog。 一直
原文链接：https://time.geekbang.org/column/article/68319
