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 为提交状态。
- 更新完成。