1. 架构概述
当执行一条SQL语句,例如:
mysql> select * from T where ID=10;
MySQL底层会发生什么?
这就要谈到MySQL的架构了,MySQL从大的方面可以分为两层:Server层和存储引擎层。
- Server层中包括连接器、查询缓存、分析器、优化器和执行器等组件。MySQL大多数核心服务功能都在这一层,比如内置函数,还有一些跨存储引擎的功能(视图、存储过程、触发器)。
- 存储引擎层负责数据的存储和提取。其架构模式是插件式的,常见的存储引擎有:InnoDB、MyISAM、MeMory等。MySQL5.5版本之后默认的存储引擎是InnoDB。
下面我们来解决开头的那个问题,并依次来了解Server层各个组件的作用。
2.连接器
我们想操作数据库,第一步就是要连接数据库。当你输入:ip、port、username和password这几个信息后,连接器就开始来服务你了。连接器负责跟客户端建立连接、管理权限、维持和管理连接。
具体步骤如下:
- 连接:经典的TCP三次握手;
- 认证:握手成功后验证你的用户名和密码;
- 授权:去权限表中查询该用户所拥有的权限,此后这个连接里的权限判断都依赖于此时此刻获得的权限。这意味着一个用户建立连接后,管理员对这个用户权限进行了修改,但是也不影响已连接的权限;
- 连接完成;
连接完成后,如果你没有后续的操作,这个连接将处于【空闲】状态。
客户端如果太长时间没操作,连接器会自动断开它,这个时间由wait_timeout
控制的,默认为8小时。
不建议将wait_timeout设置的时间过短,因为建立一个连接时很消耗资源的。
但是长连接也有一个问题,MySQL在执行过程中临时使用的内存都是在连接对象里面的。这些资源在断开时才释放。所以如果长连接过多可能导致内存占用过大,被系统强行杀掉(OOM),在我们看来就是MySQL异常重启了。
怎么解决这个问题呢?
- 定期断开长连接。使用一段时间或者执行过一个大的查询后就断开连接,之后查询再重连接。
- 在MySQL5.7之后,可以使用
mysql_reset_connection
来**重新初始化连接资源。**这个过程不需要重连或者重新做验证,但是会把连接恢复到刚刚创建完的状态。
3.查询缓存
建立完连接后就来到了第二步:查询缓存。
MySQL会将之前查询的结果以key-value的形式缓存在直接内存中。其中,key是查询语句,value是对应的查询结果。当MySQL拿到一条查询语句时,会去缓存中查找有没有对应的key,如果有的话直接返回查询结果,不用执行后面复杂的操作,效率很高。
但是我们不建议使用查询缓存,为什么呢?
- 因为查询缓存的失效非常频繁,只要有对一个表的更新,那么在这张表上的所有查询缓存都会失效。
这对于更新压力比较大的数据来说,查询缓存的命中率非常低。
除非你有一张静态表,比如系统配置表等,这张表的更新频率非常低,这个时候才适合使用查询缓存。 - 还有一个原因是在查询缓存中,SQL语句的hash作为键。这就导致了
select * from user
和SELECT * from user
是两条不同的语句。
好在MySQL提供了按需使用查询缓存的功能。query_cache_type
控制着查询缓存的开启或关闭。
MySQL8.0之后彻底删除了查询缓存这个功能。
我觉得在高层做缓存比较好一点
4.分析器
如果查询缓存没有命中,那么就要开始执行SQL语句了。首先,MySQL要知道你要干什么,因此要对你的SQL进行解析。
分析器首先会做**词法分析。**我们输入的SQL语句是由空格和字符串组成的。MySQL要识别出SQL中有什么字符串,代表什么含义。
比如说将"SELECT"识别成查询语句,"T"识别成表T,"ID"识别成字段ID…
词法分析后是语法分析。
根据词法分析的结果,分析器会根据语法规则判断这条SQL语句是否满足MySQL语法。
如果我们的语句错误,我们会收到You have an error in your SQL syntax
的错误提示。
5.优化器
经过分析器的分析,MySQL已经知道我们要做什么了。但是在真正开始执行之前,MySQL还需要对我们的SQL进行优化。
**优化器是在表中有多个索引的时候,决定使用哪个索引。或者一条语句有多表关联的时候,决定各个表的连接顺序。**比如:
SELECT
*
FROM
t1
JOIN t2 USING ( ID )
WHERE
t1.c = 10
AND t2.d = 20;
在执行过程中既可以用t1来驱动t2,也可以使用t2来驱动t1。虽然这两种方案的效果是一样的,但是执行效率有可能天差地别。优化器就是决定选择使用那种方案。
6.执行器
MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做。于是就进入了执行器阶段,开始执行语句。
开始执行的时候,需要检查该用户有没有**对该表相应的操作权限。**如果没有则抛出异常。
如果有权限,就打开表继续执行。执行器会根据这个表的引擎定义,去使用这个存储引擎提供的接口。
还是以开头那个问题为例,假设在字段ID上没有创建索引,那么执行流程应该是这样的:
- 调用InnoDB引擎接口取这个表的第一行,判断ID是否为10。如果是就存入结果集,否则直接跳过;
- 调用存储引擎接口读取下一行,重复(1)的判断逻辑。直到取到这个表的最后一行;
- 执行器将最终的结果集返回给客户端;
至此,这个查询语句就结束了。
对于有索引的表,执行过程也差不多。第一次调用的是"取满足条件的第一行"这个接口,之后循环取"满足条件的下一行"这个接口,这些接口都是引擎中定义好的。
7.问题
如果表 T 中没有字段 k,而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误: “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢?
权限校验为什么不在优化器之前做呢?