MySQL-SQL运行机制
# MySQL-SQL运行机制
MySQL的运行机制如下图：
我们大致可以划分为建立连接、查询缓存、解析器解析、查询优化器优化、查询执行引擎执行 SQL 语句五个步骤。接下来描述各个步骤的过程：
# 建立连接
建立连接（Connectors&Connection Pool）指通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “半双工”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。
通讯机制：
- 全双工：能同时发送和接收数据。例如平时打电话。
- 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机。
- 单工：只能发送数据或只能接收数据。例如单行道。
线程状态：
# 查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的
show processlist;
线程信息表头说明：
| 表头 | 说明 |
|---|---|
| id | 线程ID，可以使用kill xx强制关闭线程。 |
| user | 启动这个线程的用户 |
| Host | 发送请求的客户端的IP和端口号 |
| db | 当前命令在哪个库执行 |
| Command | 该线程正在执行的操作命令 |
| Create DB：正在创建库操作 Drop DB：正在删除库操作 Execute：正在执行一个PreparedStatement Close Stmt：正在关闭一个PreparedStatement Query：正在执行一个语句 Sleep：正在等待客户端发送语句 Quit：正在退出 Shutdown：正在关闭服务器 | |
| Time | 表示该线程处于当前状态的时间，单位是秒 |
| State | 线程状态 |
| Updating：正在搜索匹配记录，进行修改 Sleeping：正在等待客户端发送新请求 Starting：正在执行请求处理 Checking table：正在检查数据表 Closing table : 正在将表中数据刷新到磁盘中 Locked：被其他查询锁住了记录 Sending Data：正在处理Select查询，同时将结果发送给客户端 | |
| Info | 一般记录线程执行的语句，默认显示前100个字符。想查看完整的使用 show full |
# 查询缓存(MySQL 8.0 版本后移除)
查询缓存（Cache&Buffffer），这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。
- 缓存Select查询的结果和SQL语句
- 执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
- 即使开启查询缓存，以下SQL也不能缓存：
- 查询语句使用
SQL_NO_CACHE - 查询的结果大于
query_cache_limit设置 - 查询中有一些不确定的参数，比如
now()
- 查询语句使用
# 查看查询缓存是否启用，空间大小，限制等
show variables like '%query_cache%';
# 查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等
show status like 'Qcache%';
一般在大多数情况下不推荐去使用查询缓存。在实际业务中满足查询缓存生效的条件较为苛刻，除非对于不经常更新的数据来说，使用缓存还是可以的。
总的来说，查询缓存在实际的应用场景比较少，多少有点吃力不讨好的感觉。以至于官方在MySQL 8.0 版本后删除了缓存的功能。
# 解析器解析
解析器（Parser）将客户端发送的SQL进行语法解析，生成"解析树"。
预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。
# 查询优化器优化
查询优化器（Optimizer）根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。常见优化场景：
等价变换策略：
5=5 and a>5 改成 a>5; a<b and a=5 改成 b>5 and a=5; 基于联合索引，调整条件位置等。优化count、min、max等函数
InnoDB引擎min函数只需要找索引最左边 InnoDB引擎max函数只需要找索引最右边 MyISAM引擎count(*)，不需要计算，直接返回提前终止查询：使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
in的优化：MySQL对in查询，会先进行排序，再采用二分法查找数据。比如
where id in (2,1,3)，变成in (1,2,3)
# 查询执行引擎执行
查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。
- 如果开启了查询缓存，先将查询结果做缓存操作。
- 返回结果过多，采用增量模式返回。
# 案例：一条SQL语句执行过程
# 查询语句
select * from tb_student A where A.age='18' and A.name='张三';
结合上面的说明，我们分析下这个语句的执行流程：
先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL 8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为tb_student，需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案
a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。 b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
# 更新语句
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 为提交状态。
- 更新完成。