当我们执行一条SQL后表面会得到结果,但是你有没有想过内部会发生什么?接下来我们来探讨下。
MySQL架构图
通常我们将MySQL分为Server层和存储引擎层两部分。
Server层
Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
存储引擎层
存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory
等多个存储引擎。现在最常用的存储引擎是InnoDB
,它从MySQL 5.5.5版本开始成为了默认存储引擎。
SQL执行顺序
1.连接器
第一步我们需要连接到客户端来获取权限、维持和管理连接。
通常连接命令是这样写的:
mysql -h$ip -P$port -u$user -p
在通过TCP建立连接之后,需要认证user
和password
接着会出现以下两种情况:
- 认证失败,用户名或密码不对,返回一个
Access denied for user
的错误,然后客户端程序结束执行。 - 认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。
需要注意的是,此时已经拿到了权限,即便管理员赋予权限,也无法立即拥有,需要重新连接才能获取权限。
当连接客户端后长时间没有响应,MySQL会自动断开连接。MySQL提供一个连接参数wait_timeout
,默认是8小时,我们可以通过修改参数来改变我们的断开时间。
当断开连接后我们再次访问时,会返回一个错误提示Lost connection to MySQL server during query
,此时我们需要重新连接才能继续访问。
2.查询缓存
当建立连接后,就需要执行SQL进行查询了。
之前执行的SQL会以key-value的形式缓存在内存中(key作为查询语句,value作为查询的结果),如果key在缓存中找到了,会直接返回查询结果,反之将会进入分析器。
通常情况下不推荐使用查询缓存,因为查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说,查询缓存的命中率会非常低。但是对于像系统配置表这样的静态表适合使用查询缓存(Mybatis框架也有缓存机制)
MySQL提供了这种“按需使用”的方式。我们可以将参数query_cache_type
设置成DEMAND
,这样对于默认的SQL语句都不使用查询缓存。对于需要使用查询缓存的语句,可以用SQL_CACHE
显式指定:select SQL_CACHE * from student where id=1001;
MySQL 8.0移除了该按需使用查询缓存的功能。
3.分析器
当没有命中缓存,就需要分析器对SQL语句进行解析。
首先需要执行“词法分析”,将关键字和其它字符串识别出来,紧接着进行“语法分析”,判断SQL是否满足SQL语法。
如果不符合SQL语法,将会返回一个You have an error in your SQL syntax
的错误提醒,通常语法错误会提示第一个出现错误的位置。
4.优化器
执行完分析器就到了优化器,在数据库的表中通常有很多的索引,或者是不同的关联语句(join),优化器的作用就是决定表的执行顺序来选择效率更高的方案。
5.执行器
执行器将会执行我们的SQL语句,首先会对用户的权限进行判断,如果没有权限,将会返回没有权限错误。
如果拥有权限,将会根据表的引擎定义,去使用这个引擎提供的接口,从该表的第一行依次执行,直到执行到该表的最后一行,将所有符合的记录作为结果返回。
对于有索引的表,第一次调用的是“取满足条件的第一行”这个接口,之后循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的。