• 技术文章 >数据库 >mysql教程

    mysql学习之select查询语句到底是怎么执行的?

    长期闲置长期闲置2022-01-06 17:17:23转载106
    本篇文章给大家带来了关于mysql中select语句执行的相关知识,其中包括了连接器、分析器、优化器和执行器,希望对大家有帮助。

    mysql 作为一个关系型数据库,在国内使用应该是最广泛的。也许你司使用 Oracle、Pg 等等,但是大多数互联网公司,比如我司使用得最多的还是 Mysql,重要性不言而喻。

    执行select * from table,数据库底层到底发生了啥?从而我们得到数据呢?

    假设现在我有张 user 表,只有两列,一列 id 自增的,一列 name 是 varchar 类型。建表语句是这样的:

    CREATE TABLE IF NOT EXISTS `user`(
       `id` INT UNSIGNED AUTO_INCREMENT,
       `name` VARCHAR(100) NOT NULL,
       PRIMARY KEY ( `id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

    问题就是下面这个语句的执行过程。

    select * from user where id = 1;

    01 mysql 架构概览

    要想理解这个问题就必须要知道 mysql 的内部架构。为此,我画了张 mysql 的架构图(你也可以理解为 sql 查询语句的执行过程),如下所示:

    36.png

    首先 msql 分为 server 层和存储引擎层两个部分。server 层包括四个功能模块,分别是:连接器、查询缓存、优化器、执行器。这一层负责了 mysql 的所有核心工作,比如:内置函数、存储过程、触发器以及视图等。

    而存储引擎层则是负责数据的存取。注意,存储引擎在 mysql 是可选的,常见的还有:InnoDB、MyISAM 以及 Memory等,最常用的就是 InnoDB。现在默认的存储引擎也是它(从 mysql 5.5.5 版本开始),大家可以看到我上面的建表语句就是指定了 InnoDB 引擎。当然,你不指定的话默认也是它。

    由于存储引擎是可选的,所以 mysql 中,所有的存储引擎其实是共用一个 server层的。回到正题,我们就以这张图的流程来解决一下小胖的问题。

    1.1 连接器

    首先,数据库要执行 sql,肯定要先连接数据库吧。这部分工作就是由连接器完成。它负责校验账户密码、获取权限、管理连接数,最终与客户端建立连接等工作。mysql 链接数据库是这样写的:

    mysql -h 127.0.0.1 -P 3306 -u root -p
    # 127.0.0.1 : ip 3306 : 端口 root : 用户名

    运行命令之后需要输入密码,当然也可以跟在 -p 后面。不过不建议这么做,会有密码泄露的风险。

    输入命令后,连接器根据你的账户名密码验证身份。这是会出现两种情况:

    注意,我说的是此时查到的权限。就算你用管理员账号修改了当前用户的权限,此时已连接上的当前用户不受影响,必须要重启 mysql 新的权限才会生效。

    1.1.1 查看连接状态

    连接完成,如果后续没有做任何事情,这个连接就处于空闲状态。你可以用 show processlist; 命令查看 mysql 的连接信息,如下图,我的数据库连接都是 Sleep 状态的,除了执行 show processlist 操作的连接。

    01.png

    1.1.2 控制连接

    如果客户端太长时间没有操作,此连接将会自动断开。这个时间默认是 8 小时,由参数 wait_timeout 控制。如果断开以后继续操作就会收到 "Lost connection to MySQL server during query"的错误。这时就必须重连才能执行请求。

    数据库里面有长短连接之分,长连接:连接成功后不断有请求,就会一直使用同一连接。短连接:每次执行完几次请求就断开连接,下次需要再建立。

    由于建立连接是比较耗时的操作,所以建议使用长连接。但这会有个问题长连接一直连着就会导致内存占用过大,被系统强行沙雕。从而导致 MySQL 异常重启。如何解决呢?两个方法:

    1.2 查询缓存

    连接建立以后可以执行 select 语句了。这就会来到第二步:查询缓存。

    查询缓存中存储的数据是 key-value 的形式,key 是查询语句,value 是查询的结果。逻辑是这样的:先看看查询缓存有没该语句对应的 value?有则直接取出返回客户端,无则继续到数据库执行语句。查出结果后会放一份到缓存中,再返回客户端。

    你可能发现缓存真的香,但是并不建议使用查询缓存,因为有弊端。查询缓存的失效非常频繁,只有某个表有更新。它马上失效了,对于经常更新的表来说,命中缓存的概率极低。它仅仅适用于那些不经常更新的表。

    而 MySQL 似乎也考虑到这点了。提供了 query_cache_type 参数,把它设置为 DEMAND 就不再适用韩村。而对于要使用缓存的语句则可用 SQL_CACHE 显示指定,像这样:

    select SQL_CACHE * from user where id = 1;

    PS:MySQL 8.0 及以上版本把查询缓存删掉了,之后再也没有这块功能了。

    1.3 分析器

    如果没有命中缓存就进入分析器,这里就是对 sql 进行分析。分析器会做词法分析。你输入的 sql 是啥,由啥组成,MySQL 都需要知道它们代表什么。

    首先根据 "select" 识别出这是查询语句。字符串"user"识别成"表名 user"、字符串"id"识别成"列名id"。

    之后进行语法分析,它会根据输入的语句分析是不是符合 MySQL 的语法。具体表现就是 select、where、from 等关键字少了个字母,明显不符合 MySQL 语法,这次就会报个语法错误的异常:它一般会提示错误行数,关注"use near"后面即可。

    02.png

    1.4 优化器

    过了分析器,就来到了优化器。MySQL 是个聪明的仔,再执行之前会自己优化下客户端传过来的语句,看看那种执行起来不那么占内存、快一点。比如下面的 sql 语句:

    select * from user u inner join role r on u.id = r.user_id where u.name = "狗哥" and r.id = 666

    它可以先从 user 表拿出 name = "狗哥" 记录的 ID 值再跟 role 表内连接查询,再判断 role 表里面 id 的值是否 = 666

    也可以反过来:先从 role 表拿出 id = 666 记录的 ID 值再跟 user 表内连接查询,在判断 user 表里面的 name 值是否 = "狗哥"。

    两种方案的执行结果是一样的,但是效率不一样、占用的资源也就不一样。优化器就是在选择执行的方案。它优化的是索引应该用哪个?多表联查应该先查哪个表?怎么连接等等。

    1.5 执行器

    分析器知道了做啥、优化器知道了应该怎么做。接下来就交给执行器去执行了。

    开始执行,判断是否有相应的权限。比如该账户对 user 表没权限就返回无权限的错误,如下所示:

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

    PS:如果命中缓存没走到执行器这里,那么在返回查询结果时做权限验证。

    回到正题,如果有权限,继续打开表执行。执行器会根据表定义的引擎去使用对应接口。比如我们上面的 sql 语句执行流程是这样的:

    至此,整个 SQL 的执行流程完毕,

    推荐学习:mysql视频教程

    以上就是mysql学习之select查询语句到底是怎么执行的?的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:掘金,如有侵犯,请联系admin@php.cn删除
    专题推荐:mysql
    上一篇:mysql要如何删数据库 下一篇:一文总结Mysql数据库应用及常见问题

    相关文章推荐

    • php mysql怎么随机获取几条数据• mysql如何设置大小写不敏感• mysql中的不等于符号是什么• mysql内连接和外连接有什么区别• mysql要如何删数据库

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网