跳转至

03丨学会用数据库的方式思考SQL是如何执行的

通过上一篇文章对不同的DBMS的介绍,你应该对它们有了一些基础的了解。虽然SQL是声明式语言,我们可以像使用英语一样使用它,不过在RDBMS(关系型数据库管理系统)中,SQL的实现方式还是有差别的。今天我们就从数据库的角度来思考一下SQL是如何被执行的。

关于今天的内容,你会从以下几个方面进行学习:

  1. Oracle中的SQL是如何执行的,什么是硬解析和软解析;
  2. MySQL中的SQL是如何执行的,MySQL的体系结构又是怎样的;
  3. 什么是存储引擎,MySQL的存储引擎都有哪些?

Oracle中的SQL是如何执行的

我们先来看下SQL在Oracle中的执行过程:


从上面这张图中可以看出,SQL语句在Oracle中经历了以下的几个步骤。

  1. 语法检查:检查SQL拼写是否正确,如果不正确,Oracle会报语法错误。
  2. 语义检查:检查SQL中的访问对象是否存在。比如我们在写SELECT语句的时候,列名写错了,系统就会提示错误。语法检查和语义检查的作用是保证SQL语句没有错误。
  3. 权限检查:看用户是否具备访问该数据的权限。
  4. 共享池检查:共享池(Shared Pool)是一块内存池,最主要的作用是缓存SQL语句和该语句的执行计划。Oracle通过检查共享池是否存在SQL语句的执行计划,来判断进行软解析,还是硬解析。那软解析和硬解析又该怎么理解呢?

在共享池中,Oracle首先对SQL语句进行Hash运算,然后根据Hash值在库缓存(Library Cache)中查找,如果存在SQL语句的执行计划,就直接拿来执行,直接进入“执行器”的环节,这就是软解析。

如果没有找到SQL语句和执行计划,Oracle就需要创建解析树进行解析,生成执行计划,进入“优化器”这个步骤,这就是硬解析。 5. 优化器:优化器中就是要进行硬解析,也就是决定怎么做,比如创建解析树,生成执行计划。 6. 执行器:当有了解析树和执行计划之后,就知道了SQL该怎么被执行,这样就可以在执行器中执行语句了。

共享池是Oracle中的术语,包括了库缓存,数据字典缓冲区等。我们上面已经讲到了库缓存区,它主要缓存SQL语句和执行计划。而数据字典缓冲区存储的是Oracle中的对象定义,比如表、视图、索引等对象。当对SQL语句进行解析的时候,如果需要相关的数据,会从数据字典缓冲区中提取。

库缓存这一个步骤,决定了SQL语句是否需要进行硬解析。为了提升SQL的执行效率,我们应该尽量避免硬解析,因为在SQL的执行过程中,创建解析树,生成执行计划是很消耗资源的。

你可能会问,如何避免硬解析,尽量使用软解析呢?在Oracle中,绑定变量是它的一大特色。绑定变量就是在SQL语句中使用变量,通过不同的变量取值来改变SQL的执行结果。这样做的好处是能提升软解析的可能性,不足之处在于可能会导致生成的执行计划不够优化,因此是否需要绑定变量还需要视情况而定。

举个例子,我们可以使用下面的查询语句:

SQL> select * from player where player_id = 10001;

你也可以使用绑定变量,如:

SQL> select * from player where player_id = :player_id;

这两个查询语句的效率在Oracle中是完全不同的。如果你在查询player_id = 10001之后,还会查询10002、10003之类的数据,那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变量,那么在第一次查询之后,在共享池中就会存在这类查询的执行计划,也就是软解析。

因此我们可以通过使用绑定变量来减少硬解析,减少Oracle的解析工作量。但是这种方式也有缺点,使用动态SQL的方式,因为参数不同,会导致SQL的执行效率不同,同时SQL优化也会比较困难。

MySQL中的SQL是如何执行的

Oracle中采用了共享池来判断SQL语句是否存在缓存和执行计划,通过这一步骤我们可以知道应该采用硬解析还是软解析。那么在MySQL中,SQL是如何被执行的呢?

首先MySQL是典型的C/S架构,即Client/Server架构,服务器端程序使用的mysqld。整体的MySQL流程如下图所示:


你能看到MySQL由三层组成:

  1. 连接层:客户端和服务器端建立连接,客户端发送SQL至服务器端;
  2. SQL层:对SQL语句进行查询处理;
  3. 存储引擎层:与数据库文件打交道,负责数据的存储和读取。

其中SQL层与数据库文件的存储方式无关,我们来看下SQL层的结构:

  1. 查询缓存:Server如果在查询缓存中发现了这条SQL语句,就会直接将结果返回给客户端;如果没有,就进入到解析器阶段。需要说明的是,因为查询缓存往往效率不高,所以在MySQL8.0之后就抛弃了这个功能。
  2. 解析器:在解析器中对SQL语句进行语法分析、语义分析。
  3. 优化器:在优化器中会确定SQL语句的执行路径,比如是根据全表检索,还是根据索引来检索等。
  4. 执行器:在执行之前需要判断该用户是否具备权限,如果具备权限就执行SQL查询并返回结果。在MySQL8.0以下的版本,如果设置了查询缓存,这时会将查询结果进行缓存。

你能看到SQL语句在MySQL中的流程是:SQL语句→缓存查询→解析器→优化器→执行器。在一部分中,MySQL和Oracle执行SQL的原理是一样的。

与Oracle不同的是,MySQL的存储引擎采用了插件的形式,每个存储引擎都面向一种特定的数据库应用环境。同时开源的MySQL还允许开发人员设置自己的存储引擎,下面是一些常见的存储引擎:

  1. InnoDB存储引擎:它是MySQL 5.5版本之后默认的存储引擎,最大的特点是支持事务、行级锁定、外键约束等。
  2. MyISAM存储引擎:在MySQL 5.5版本之前是默认的存储引擎,不支持事务,也不支持外键,最大的特点是速度快,占用资源少。
  3. Memory存储引擎:使用系统内存作为存储介质,以便得到更快的响应速度。不过如果mysqld进程崩溃,则会导致所有的数据丢失,因此我们只有当数据是临时的情况下才使用Memory存储引擎。
  4. NDB存储引擎:也叫做NDB Cluster存储引擎,主要用于MySQL Cluster分布式集群环境,类似于Oracle的RAC集群。
  5. Archive存储引擎:它有很好的压缩机制,用于文件归档,在请求写入时会进行压缩,所以也经常用来做仓库。

需要注意的是,数据库的设计在于表的设计,而在MySQL中每个表的设计都可以采用不同的存储引擎,我们可以根据实际的数据处理需要来选择存储引擎,这也是MySQL的强大之处。

数据库管理系统也是一种软件

我们刚才了解了SQL语句在Oracle和MySQL中的执行流程,实际上完整的Oracle和MySQL结构图要复杂得多:



如果你只是简单地把MySQL和Oracle看成数据库管理系统软件,从外部看难免会觉得“晦涩难懂”,毕竟组织结构太多了。我们在学习的时候,还需要具备抽象的能力,抓取最核心的部分:SQL的执行原理。因为不同的DBMS的SQL的执行原理是相通的,只是在不同的软件中,各有各的实现路径。

既然一条SQL语句会经历不同的模块,那我们就来看下,在不同的模块中,SQL执行所使用的资源(时间)是怎样的。下面我来教你如何在MySQL中对一条SQL语句的执行时间进行分析。

首先我们需要看下profiling是否开启,开启它可以让MySQL收集在SQL执行时所使用的资源情况,命令如下:

mysql> select @@profiling;


profiling=0代表关闭,我们需要把profiling打开,即设置为1:

mysql> set profiling=1;

然后我们执行一个SQL查询(你可以执行任何一个SQL查询):

mysql> select * from wucai.heros;

查看当前会话所产生的所有profiles:


你会发现我们刚才执行了两次查询,Query ID分别为1和2。如果我们想要获取上一次查询的执行时间,可以使用:

mysql> show profile;


当然你也可以查询指定的Query ID,比如:

mysql> show profile for query 2;

查询SQL的执行时间结果和上面是一样的。

在8.0版本之后,MySQL不再支持缓存的查询,原因我在上文已经说过。一旦数据表有更新,缓存都将清空,因此只有数据表是静态的时候,或者数据表很少发生变化时,使用缓存查询才有价值,否则如果数据表经常更新,反而增加了SQL的查询时间。

你可以使用select version()来查看MySQL的版本情况。

总结

我们在使用SQL的时候,往往只见树木,不见森林,不会注意到它在各种数据库软件中是如何执行的,今天我们从全貌的角度来理解这个问题。你能看到不同的RDBMS之间有相同的地方,也有不同的地方。

相同的地方在于Oracle和MySQL都是通过解析器→优化器→执行器这样的流程来执行SQL的。

但Oracle和MySQL在进行SQL的查询上面有软件实现层面的差异。Oracle提出了共享池的概念,通过共享池来判断是进行软解析,还是硬解析。而在MySQL中,8.0以后的版本不再支持查询缓存,而是直接执行解析器→优化器→执行器的流程,这一点从MySQL中的show profile里也能看到。同时MySQL的一大特色就是提供了各种存储引擎以供选择,不同的存储引擎有各自的使用场景,我们可以针对每张表选择适合的存储引擎。


今天的内容到这里就结束了,你能说一下Oracle中的绑定变量是什么,使用它有什么优缺点吗?MySQL的存储引擎是一大特色,其中MyISAM和InnoDB都是常用的存储引擎,这两个存储引擎的特性和使用场景分别是什么?

最后留一道选择题吧,解析后的SQL语句在Oracle的哪个区域中进行缓存?

A. 数据缓冲区
B. 日志缓冲区
C. 共享池
D. 大池

欢迎你在评论区写下你的思考,我会在评论区与你一起交流,如果这篇文章帮你理顺了Oracle和MySQL执行SQL的过程,欢迎你把它分享给你的朋友或者同事。

※注:本篇文章出现的图片请点击这里下载高清大图。

精选留言(15)
  • 美妙的代码 👍(14) 💬(2)

    老师,那两张,oracle,mysql 的大图。是哪儿的。有没有高清的啊。很多小字看不清楚。能否给个高清的链接。

    2019-06-19

  • FATMAN89 👍(10) 💬(1)

    老师讲的挺好的,想请问老师,课程所用到的数据库在哪里可以获得呢,多谢

    2019-06-18

  • 刘桢 👍(50) 💬(13)

    今年考研必上北京邮电大学!

    2019-06-18

  • NO.9 👍(15) 💬(1)

    C,共享池。 讲的好系统啊,有种想学个花拳绣腿,结果教我九阳神功的感觉。

    2019-06-30

  • 张驰皓 👍(13) 💬(1)

    感觉 MySQL 部分的第二张图(流程图)有点问题,“缓存查询”后“找到”分支的箭头应该不用再指向”缓存查询“吧?

    2019-12-01

  • qf年间 👍(8) 💬(1)

    文中多次提到执行计划,这是一个什么东西呢,可否具体讲解一下,或者举例说明

    2019-09-10

  • allean 👍(7) 💬(1)

    共享池

    2019-06-17

  • leslie 👍(6) 💬(1)

    再次听一遍不一样的东西还是会发现不一样的收货:这大概就是数据库用的多了有时代码层确实没啥 ,可是切换中的优化过程还是会疏漏某些分析细节。 explain已经用到了极致,忘了优化的极限其实是多种方式的相辅相成;profile早期用过,反而这几年用的很少很少;explain更加管用-在多种数据库中,反而忘了有时需要一些简单的手段辅以。

    2019-06-17

  • firstblood 👍(1) 💬(1)

    MyISAM 和InnoDB的比较参考https://www.jianshu.com/p/a957b18ba40d 这个文章

    2019-09-25

  • Geek_d3a509 👍(0) 💬(1)

    Oracle中绑定变量:SQL语句中相同的操作对不同的变量值用一个变量来代替,使得Oracle中要做硬解析变成软解析,以减少Oracle在解析上花费的时间 优点:减少了很多不必要的硬解析,将由软解析代替,大大降低数据库花费在SQL解析上的资源开销(时间与空间的浪费)。 缺点:绑定了变量之后优化比较困难,使用绑定之后可能对执行计划有一定的影响,导致执行计划的出错。比如很多重复的语句使用一个变量代替,那么可能就第一条解析正确,余下的就不能正确执行。 InnoDB的特性与使用场景(mysql5.5及以后版本默认存储引擎):事务型存储引擎,支持ACID特性;支持行级锁。适用大多数OLTP应用 MyISAM的特性与使用场景:不支持事务,表级锁。适用(1)非事务性应用 (2)只读型应用 (3)空间型应用 选择题选C

    2019-12-04

  • 大海 👍(0) 💬(1)

    对于代码部分提个建议,复制出来的内容最好时可以直接使用的。 mysql> show profile for query 2; 现在复制出来的是这样,需要自己把前面的部分去掉,才能用。

    2019-10-02

  • 止戈 👍(0) 💬(1)

    老师请问什么时候会讲到具体使用方法?

    2019-09-23

  • Geek_c76f38 👍(0) 💬(1)

    SQL> select * from player where player_id = 10001; 代码在手机上看用显示不完整,难道每条都要复制出来才能看?有没有什么办法解决一下

    2019-06-25

  • 时间是最真的答案 👍(0) 💬(1)

    C. 共享池 文中提到过

    2019-06-24

  • 鱼🐟 👍(0) 💬(1)

    你们的服务器是不是挂了,经常不能访问

    2019-06-24