极客时间已完结课程限时免费阅读

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

03丨学会用数据库的方式思考SQL是如何执行的-极客时间

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

2019-06-17 陈旸 来自北京
《SQL必知必会》
课程介绍

讲述：陈旸

时长10:19大小8.26M

通过上一篇文章对不同的 DBMS 的介绍，你应该对它们有了一些基础的了解。虽然 SQL 是声明式语言，我们可以像使用英语一样使用它，不过在 RDBMS（关系型数据库管理系统）中，SQL 的实现方式还是有差别的。今天我们就从数据库的角度来思考一下 SQL 是如何被执行的。
关于今天的内容，你会从以下几个方面进行学习：
Oracle 中的 SQL 是如何执行的，什么是硬解析和软解析；
MySQL 中的 SQL 是如何执行的，MySQL 的体系结构又是怎样的；
什么是存储引擎，MySQL 的存储引擎都有哪些？

Oracle 中的 SQL 是如何执行的

我们先来看下 SQL 在 Oracle 中的执行过程：
从上面这张图中可以看出，SQL 语句在 Oracle 中经历了以下的几个步骤。
语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
权限检查：看用户是否具备访问该数据的权限。
共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？
在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。
如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。
优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
执行器：当有了解析树和执行计划之后，就知道了 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 由三层组成：
连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层：对 SQL 语句进行查询处理；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。
其中 SQL 层与数据库文件的存储方式无关，我们来看下 SQL 层的结构：
查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
你能看到 SQL 语句在 MySQL 中的流程是：SQL 语句→缓存查询→解析器→优化器→执行器。在一部分中，MySQL 和 Oracle 执行 SQL 的原理是一样的。
与 Oracle 不同的是，MySQL 的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境。同时开源的 MySQL 还允许开发人员设置自己的存储引擎，下面是一些常见的存储引擎：
InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
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 的过程，欢迎你把它分享给你的朋友或者同事。
※注：本篇文章出现的图片请点击这里下载高清大图。
分享给需要的人，Ta购买本课程，你将得20元
生成海报并分享

赞 33

提建议

© 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
上一篇
02丨DBMS的前世今生
下一篇
04丨使用DDL创建数据库&数据表时需要注意什么？
unpreview
 写留言

精选留言(110)

  • Frank
    置顶
    2019-06-19
    老师，那两张，oracle，mysql 的大图。是哪儿的。有没有高清的啊。很多小字看不清楚。能否给个高清的链接。

    编辑回复: 请您点击这里进行下载：https://github.com/cystanford/SQL-XMind

    共 2 条评论
    14
  • FATMAN89
    置顶
    2019-06-18
    老师讲的挺好的，想请问老师，课程所用到的数据库在哪里可以获得呢，多谢

    编辑回复: 第五篇文章会给出下载链接～

    10
  • KaitoShy
    2019-06-17
    1. 绑定变量概念：sql语句中使用变量，通过不同的变量值来改变sql的执行结果 优点：减少硬解析，减少Oracle的工作量 缺点：参数不同导致执行效率不同，优化比较难做。 2.MyISAM的使用场景为读写分离的读库， 而InnoDB为写库 3. C共享池，看图中有个Shared SQL Area。
    99
  • 墨禾
    2019-06-17
    【回答3个问题】 1、oracle中的绑定变量指的是 sql语句在执行时，通过改变不同的变量值来改变sql的执行结果； 优点：避免硬解析，提高SQL语句执行的效率； 缺点：如果使用绑定变量，那么优化器就会忽略直方图的信息，在生成执行计划的时候可能不够优化。 2、MyISAN： 不支持事务、外键，速度快、资源占有少； InnoDB：支持实物、外键、聚集索引，5.6版本以后的mysql支持全文索引； 使用场景： 需要支持事物的场景考虑InnoDB； 以读为主的数据表用MyISAM； MyISAM奔溃后的系统的恢复较困难，没有要求的话可以用； 不知道选什么数据库合适的话，用InnoDB不会差【5.5版本以后的mysql默认的引擎是InnoDB】 3、oracle在共享池中进行缓存。 【学习总结】 1、get一个学习方法 培养抽象事物的能力，掌握学习要点，如sql知识，重点掌握sql执行的原理，因为在不同的数据库中，sql执行的原理大同小异，只是在执行的顺序上有所不同。 2、get一个oracle sql优化的技巧 通过绑定变量，优化sql结果 3、get一个oracle、mysql共同的执行sql的原理 SQL语句-》缓存查询-》解析器-》优化器-》执行器 【PS:老师后面能不能推荐一些实战项目练习，来巩固知识点呢？】
    展开
    共 4 条评论
    75
  • 刘桢
    2019-06-18
    今年考研必上北京邮电大学！

    作者回复: 加油~ 没问题的

    共 12 条评论
    49
  • 虫子的一天
    2019-06-17
    老师好，我原来用SQLSERVER比较多，经常会碰到参数化的SQL查询中，因为SQLSERVER已经缓存了查询计划，导致某些特定参数查询效率很低的事情(刚才文中也有提及Oracle也有类似问题)。 我刚听讲似乎MySQL是没这个机制的，是否MySQL就不会碰到类似问题? 另外如果不让SQLserver使用缓存的查询计划，每次都重新生成，又导致CPU高，MYSQL又是如何避免类似问题的 感谢
    展开
    28
  • Sam
    2019-06-17
    1.什么是绑定变量 Oracle的绑定变量即是把谓词条件的具体值更换成一个变量，这样Oracle在共享池做hash映射时，同一类SQL但谓词条件值不同的，可以共享执行计划，进行软解析而不需要硬解析，例如 select * from tab where id=1和select * from tab where id=10改成绑定量 select * from tab where id=:a; 2.绑定变量优缺点 优点：减少硬解析，加快SQL处理过程，减少系统开销； 缺点：绑定变量对应到不同的值，由于数据分布的问题，可能最佳的执行计划不一定是同一个，但是绑定变量在软解析时就固定对应到特定的执行计划上。 解析后的SQL语句存放在Oracle的共享池。 想请教老师一个问题，MySQL没有软硬解析的设计，那MySQL是怎么应对硬解析的低效呢？
    展开
    共 2 条评论
    17
  • 🍐这是一只大梨子
    2020-03-05
    数据库的数据储存就和我们人类的学习记忆一样。遇到一件事，如果先前没学过，则需要神经元间建立新的联系，就像Oracle的硬解析；而如果学过，则用之前的经验进行处理，类似软解析。
    共 3 条评论
    16
  • NO.9
    2019-06-30
    C,共享池。 讲的好系统啊，有种想学个花拳绣腿，结果教我九阳神功的感觉。

    作者回复: 很好 加油~

    14
  • 张驰皓
    2019-12-01
    感觉 MySQL 部分的第二张图（流程图）有点问题，“缓存查询”后“找到”分支的箭头应该不用再指向”缓存查询“吧？

    作者回复: 感谢认真提问，缓存查询后，如果找到了就直接输出结果。如果没有找到就执行 解析器=>优化器=>执行器的流程，然后可以将结果存储到 缓存中方便后续进行查询。所以箭头回向指的是这个

    13
  • 🄽🄸🅇🅄🅂
    2019-06-19
    老师, 我想提个建议, 可以吗? MySQL 的存储引擎是一大特色，其中 MyISAM 和 InnoDB 都是常用的存储引擎，这两个搜索引擎的特性和使用... 这里好像写错了, 前面是存储引擎, 后面变成搜索引擎了; 另外还有三个疑问, 希望老师可以答疑: 1. <<MySQL实战45讲>>中, 林老师讲到的是, MySQL可以分为两层: Server层和存储引擎层(连接器是属于Server层), 您这里讲的是, 分三层: 连接层, SQL层 和 存储引擎层, 这个分层是没有严格的定义的吗? 2. 上面那个图, https://static001.geekbang.org/resource/image/d6/a0/d6ab4e24003df69503bb055810a29fa0.jpg 缓存查询 这一过程结束后, 在最后 有两个箭头, 一个指向了 执行器, 另一个指向了 输出结果, 这个略感迷惑; 3. InnoDB是在5.5.5作为默认存储引擎的, 还是在5.5.8作为默认存储引擎的呢? 查了MySQL的网站, https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-5.html#mysqld-5-5-5-innodb 好像是5.5.5? 感谢老师的课程! 谢谢! 收获很大!
    展开
    10
  • qf年间
    2019-09-10
    文中多次提到执行计划，这是一个什么东西呢，可否具体讲解一下，或者举例说明

    作者回复: 最简单的方式，你可以使用explain来查看某一条SQL语句的执行计划，这样比较直观，比如 EXPLAIN SELECT * FROM player 你能看到在MySQL查询优化器中是如何执行SQL语句的

    8
  • 跳跳
    2019-06-17
    绑定变量：在SQL语句中使用变量，通过不同的变量取值来改变SQL的执行结果 优点是可以做软解析，避免创建解析树，生成执行计划，提高oracle的运算效率 缺点是可能会导致执行计划不够优化 InnoDB 存储引擎：最大特点是支持事务、行级锁定、外键约束等 MylSAM：不支持事务、也不支持外键，最大特点是速度快、占用资源少 选择题选C，共享池的主要作用是缓存SQL语句和该语句的执行计划
    展开
    7
  • allean
    2019-06-17
    共享池

    作者回复: 对的

    7
  • Demon.Lee
    2019-06-17
    另一篇文章说：5.5.5以上默认Engine是Innodb，这篇说是5.5.8，所以谁是对的？
    6
  • leslie
    2019-06-17
    再次听一遍不一样的东西还是会发现不一样的收货:这大概就是数据库用的多了有时代码层确实没啥 ，可是切换中的优化过程还是会疏漏某些分析细节。 explain已经用到了极致，忘了优化的极限其实是多种方式的相辅相成；profile早期用过，反而这几年用的很少很少；explain更加管用-在多种数据库中，反而忘了有时需要一些简单的手段辅以。

    作者回复: 加油~ 哈哈

    6
  • 吴青
    2019-06-21
    老师，mysql的sql层的鉴权为什么要放在第四步，语义分析完后是不是就可以鉴权了，省去了生成执行路径的开销。mysql为什么这么做呢
    共 1 条评论
    5
  • Ronnyz
    2019-06-29
    1、查看mysql存储引擎命令， 在mysql>提示符下搞入show engines; 字段 Support为:Default表示默认存储引擎 2、设置InnoDB为默认引擎： 在配置文件my.cnf中的 [mysqld] 下面加入 default-storage-engine=INNODB 一句 3、重启mysql服务器： mysqladmin -u root -p shutdown 或者service mysqld restart 登录mysql数据库
    展开
    3
  • redrain
    2019-06-17
    老师，类似状态，status这种有几个固定值的列，有必要建立索引吗，广泛流传不需要建立，但是我发现建立有提升查询效率，innodb引擎5.7
    3
  • Regis
    2019-06-17
    补充一下KaitoShy的，利用绑定变量，影响执行效率的原因，是由于每个表里面的使用绑定变量的列的数据分布不平均导致，如果列值分布差别不大的话就会避免这个问题
    2