深化了解SQL原理:SQL查询句子是怎么履行的?

本篇文章将经过一条 SQL 的实行进程来介绍 MySQL 的根底架构。

首要有一个 user_info 表,表里有一个 id 字段,实行下面这条查询句子:

select * from user_info where id = 1;

回来成果为:

+----+----------+----------+--------+------+---------------------+---------------------+标签19
| id | username | password | openid | role | create_time | update_time |
+----+----------+----------+--------+------+---------------------+---------------------+
| 1 | 武培轩 | 123 | 1 | 1 | 2019-08-29 00:29:08 | 2019-08-29 00:29:08 |
+----+----------+----------+--------+------+---------------------+---------------------+

下面给出 MySQL 的根本架构示意图,能够看出 SQL 句子在 MySQL 的各个模块中的实行进程。

MySQL 根本架构

大体上,MySQL 分为 Server 层和存储引擎层两部分。

Server 层包含衔接器、查询缓存、剖析器、实行器等,以及一切的内置函数(如标签17日期、时刻、数学和加密函数等)和跨存储引擎的功用(如存储进程、触发器、视图)。

存储引擎层担任数据的存储和提取,支撑 InnoDB、MyISAM、Memory 等多个存储引擎。MySQL 标签195.5.5 版别后默许存储存储引擎是 InnoDB。

1.衔接器(Connector)

在查询 SQL 句子前,肯定要先树立与 MySQL 的衔接,这便是由衔接器来完结的。衔接器担任跟客户端树立衔接、获取权限、保持和办理衔接。衔接指令为:

mysql -h$ip -P$port -u$user -p 

输入暗码,验证经往后,衔接器会到权限表里边查出你具有的权限,之后这个衔接里边的权限判别逻辑,都将依赖于此刻读到的权限,一个用户成功树立衔接后,即便办理员对这个用户的权限做了修正,也不会影响现已存在衔接的权限,修正完后,只需再新建的衔接才会运用新的权限设置。

衔接完结后,假如你没有后续的动作,这个衔接就处于闲暇状况,你能够在 show processlist 指令中看到它。成果如下:

+----+------+----------------+------------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------+------------------+---------+------+----------+------------------+
|深化了解SQL原理:SQL查询句子是怎样实行的? 3 | root | localhost:2790 | NULL | Sleep | 5878 | | NULL |
| 4 | root | localhost:2深化了解SQL原理:SQL查询句子是怎样实行的?791 | springcloud_sell | Sleep | 5838 | | NULL |
| 7 | root | localhost:2900 | springcloud_sell | Sleep | 5838 | | NULL |
| 10 | root | localhost:3627 | springcloud_sell | Query | 0 | starting | show processlist |
+----+------+----------------+------------------+---------+------+----------+------------------+

客户端假如太长时刻没动静,衔接器就会主动将它断开;这个时刻是由参数 wait_timeout 操控的,默许值是8小时。假如在衔接被断开之后,客户端再次发送恳求的话,就会收到一个过错提示:Lost connection to MySQL server during query。

长衔接和短衔接

  • 数据库里边,长衔接是指衔接成功后,假如客户端持续有恳求,则一向运用同一个衔接。
  • 短衔接则是指每次实行完很少的几回查询就断开衔接,下次查询再从头树立一个。

树立衔接的进程通常是比较杂乱的,建深化了解SQL原理:SQL查询句子是怎样实行的?议在运用中要尽量削减树立衔接的动作,尽量运用长衔接。可是悉数运用长衔接后,有时分 MySQL 占用内存涨得特别快,这是由于 MySQL 在实行进程中暂时运用的内存是办理在衔接目标里边的。这些资源会在衔接断
开的时分才开释。所以假如长衔接累积下来,或许导致内存占用太大,被体系强行杀掉(OOM),从现象看便是 MySQL 反常重启了。

怎样处理这个问题呢?能够考虑以下两种计划:

  1. 定时断开长衔接。运用一段时刻,或许程序里边判别实行过一个占用内存的大查询后,标签17断开衔接,之后要查询再重连。
  2. MySQL 5.7 以上版别,能够在每次实行一个比较大的操作后,经过实行 mysql_reset_connection 来从头初始化衔接资源。这个进程不需求重连和从头做权限验证,可是会将衔接康复到刚刚创立完时的状况。

2.查询缓存(Query Cache)

在树立衔接后,就开端实行 select 句子了,实行前首要会查询缓存。

MySQL 拿到查询恳求后,会先查询缓存,看是不是实行过这条句子。实行过的句子及其成果会以 key-value 对的办法保存在必定的内存区域中。key 是查询的句子,value 是查询的成果。假如你的查询能够直接在这个缓存中找到 key,那么这个
value 就会被直接回来给客户端。

假如句子不在查询缓存中,就会持续后边的实行阶段。实行完结后,实行成果会被存入查询缓存中。假如查询射中缓存,MySQL 不需求实行后边的杂乱操作,就能够直接回来成果,会提高功率。

可是查询缓存的失效十分频频,只需有对一个表的更新,这个表上一切的查询缓存都会被清空。关于更新压力大的数据库来说,查询缓存的射中率会十分低。假如事务中需求有一张静态表,很长时刻才会标签1更新一次。比方,一个体系配置表,那这张表上的查询才合适运用查询缓存。MySQL 供给了这种按需运用的办法。能够将参数 query_cache_type 设置成 DEMAND,关于默许的 SQL 句子都将不运用查询缓存。而关于你确认要运用查询缓存的句子,能够用 SQL_CACHE 显式指定,如下:

mysql深化了解SQL原理:SQL查询句子是怎样实行的?> select SQL_CACHE * from user_info where id = 1;

MySQL 8.0 版别将查询缓存的功用删除了。

3.剖析器(Analyzer)

假如查询缓存未射中,就要开端实行句子了。首要,MySQL 需求对 SQL 句子进行解析。

剖析器先会做词法剖析。SQ标签17L 句子是由多个字符串和空格组成的,MySQL 需求辨认出里边的字符串别离是什么,代表什么。MySQL 从你输入的 select 这个关键字辨认出来,这是查询标签20句子。它也要把字符串 user_info 辨认成表名,把字符串 id 辨认成列名。之后就要做语法剖析。依据词法剖析的成果,语法剖析器会依据语法规矩,判别输入的 SQL 句子是否满意 MySQL 语法。

假如你 SQL 句子不对,就会收到 You have an error in your SQL syntax 的过错提示,比方下面这个句子 from 写成了 form。

mysql> select * form user_info where id = 1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form user_info where id = 1' at line 1

一般语法过错会提示榜首个呈现过错的方位,所以要重视的是紧接 use near 的内容。

4.优化器(Optimiz标签10er)

经过剖析器的词法剖析和语法剖析后,还要经过优化器的处理。

优化器是在表里边有多个索引的时分,决议运用哪个索引;或许在一个句子有多表相关(join)的时分,决议各个表的衔接次序。比方你实行下面这样的句子,这个句子是实行两个表的 join:

mysql> SELECT * FROM order_maste标签5r JOIN order_detail USING (order_id) WHERE order_master.pay_status = 0 AND order_detail.detail_id = 1558963262141624521;

既能够先从表 order_master 里边取出 pay_status = 0 的记载的 order_id 值,再依据 order_id 值相关到表 order_detail,再判别 orde标签1r_detail 里边 detail_id 的值是否等于 1558深化了解SQL原理:SQL查询句子是怎样实行的?963262141624521。

也能够先从表 order_detail 里边取出 detail_id = 1558963262141624521 的记载的 order_id 值,再依据 order_id 值相关到 order_master,再判别 order_master 里边 pay_status 的值是否等于 0。

这两种实行办法的逻辑成果是相同的,可是实行的功率会有不同,而优化器的效果便是决议挑选运用哪一个计划。优化器阶段完结后,这个句子的实行计划就确认下来了,然后进入实行器阶段。

5.实行器(Actuator)

MySQL 经过剖析器深化了解SQL原理:SQL查询句子是怎样实行的?知道了要做什么,经过优化器知道了该怎样做,所以就进入了实行器阶段,开端实行句子。

开端实行的时分,要先判别一下你对这个表 user_info 有没有实行查询的权限,假如没有,就会回来没有权限的过错,如下所示 (假如射中查询缓存,会在查询缓存回来成果的时分,做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

mysql> select * from user_info where id = 1;
ERROR 1142 (42000): SELECT command denied to user 'wupx'@'localhost' for table 'user_info'

假如有权限,就翻开表持续实行。翻开表的时分,实行器就会依据表的引擎界说,去运用这个引擎供给的接口。比方咱们这个比如中的表 user_info 中,id 字段没有索引,那么实行器的实行流程是这样的:

  1. 调用 InnoDB 引擎接口取这深化了解SQL原理:SQL查询句子是怎样实行的?个表的榜首行,判别 id 值是不是 1,假如不是则越过,假如是则将这行存在成果会集;
  2. 调用引擎接口取下一行,重复相同的判别逻辑,直到取到这个表的最终一行。
  3. 实行器将上述遍历进程中一切满意条件的行组成的记载集作为成果集回来给客户端。

关于有索引的表,榜首次调用的是取满意条件的榜首行这个接口,之后循环取满意条件的下一行这个接口。

数据库的慢查询日志中有 rows_examined 字段,表明这个句子实行进程中扫描了多少行。这个值便是在实行器每次调用引擎获取数据行的时分累加的。在有些场景下,实行器调用一次,在引擎内部则扫描了多行,因而引擎扫描行数跟 rows_examined 并不是完全相同的。

总结

首要经过对一个 SQL 句子完好实行进程进行解说,介绍 MySQL 的逻辑架构,MySQL 首要包含衔接器、查询缓存、剖析器、优化器、实行器这几个模块。


作者:一入码坑深似海
链接:https://www.jianshu.com/p/d6ba84081652
来历:简书

发表评论

电子邮件地址不会被公开。 必填项已用 *标注