MySQL 中一条语句是如何执行的

MySQL的基本架构示意图:
0d2070e8f84c4801adbfa03bda1f98d9
MySQL的逻辑架构
Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖大多数的核心服务功能,以及所有的内置函数;跨存储引擎的功能都在Server层实现,如存储过程、触发器、视图等;
存储引擎层负责数据的存储和提取。插件式的架构,支持InnoDB、MyISAM、Memory等多个存储引擎。

连接器

负责跟客户端建立连接、获取权限、维持和管理连接;
一个用户成功建立连接后,即使用户的权限做了修改,也不会影响已存在的连接的权限,只有新建立的连接才会使用新的权限设置;
连接完成后,如果没有后去操作,这个连接就处于空闲状态,show processlist命令中,Command列显示为sleep的这一行就是对应的那个空闲连接;
客户端如果长时间没有操作,连接器将会断开该客户端的连接,由wait_timeout参数进行控制,默认为8Hours;
连接断开后,如果客户端发送请求,将会受到Lost connection to MySQL server during query的提示;
使用长连接与MySQL建立连接后,有些时候,MySQL占用内存会涨的很快,因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的,这些资源在断开连接的时候才会释放,如果长连接累计下载,导致内存占用太大,被系统强行kill掉(OOM),从现象看就是MySQL异常重启了,解决方案:
1、定期断开长连接;
2、如果使用MySQL5.7+ 版本,可以在执行比较大的操作后,通过执行mysql_reset_connection来重新初始化连接资源;

查询缓存

连接建立完成后,可以执行select 语句了。
MySQL 收到一个查询请求后,会先到查询缓存中检查之前是否执行过该条SQL语句,之前执行过的SQL语句及其结果可能会议key-value对的形式,被直接缓存在内存中。如果SQL语句命中缓存的key,则value会直接返回给客户端。

但大多数情况下,不建议使用查询缓存

查询缓存失效非常频繁,只要对一个表的更新,这个表上所有的查询缓存都会清空。对于工薪压力很大的数据库来说,查询缓存命中率会非常低;
将参数query_cache_type设置为DEMAND,这样对于默认SQL 语句都不使用查询缓存,而对于确定使用查询缓存的语句,可以使用SQL_CACHE显式指定:
select SQL_CACHE * from table where id = 1
注:MySQL8.0已经取消查询缓存这个功能模块;

分析器

分析器先做词法分析,对语句进行识别,以select SQL_CACHE * from table where id = 1为例:
从输入的 select 关键词识别出是个查询语句;把字符串table识别为表名table;把字符串id识别为列id;
然后进行语法分析,判断输入的语句是否满足MySQL语法;
一般语法错误会提示第一个出现错误的位置,所以你要关注的是紧接“use near”的内容。

优化器

在表里面有多个索引的时候,决定使用哪个索引;或在一个语句有多表关联(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;
两种方法执行逻辑结果一样,但是执行效率会有不同,优化器的左右就是决定使用哪一个方案;

执行器

开始执行的时候,会先判断这个表有没有查询权限,如果没有则会报错

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

假如表T中,ID字段没有索引,那么执行器执行流程如下:
1、调用InnoDB引擎接口获取表的第一行,判断ID是否是10,如果不是则跳过,如果是则将这行存在结果集中;
2、调用引擎接口获取“下一行”,重复判断逻辑,直到取到这个表的最后一行;
3、执行器将上述便利过程中满足条件的行组成的结果集返回给客户端;

对于有索引的表,执行逻辑也差不多;

数据库的慢查询日志中rows_examined字段表示这个语句执行过程中扫描了所烧行,这个值就是在执行器每次调用引擎获取数据的时候累加的;

发表评论