【SQL必知必会】004-基础篇：学会用数据库的方式思考SQL是如何执行的

本文介绍了 sql 语句在 Oracle 和 MySQL 中的执行过程！

@[toc]

一、Oracle 中的 SQL 是如何执行的

1、执行过程图示

请添加图片描述

2、语法检查

检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。

3、语义检查

检查 SQL 中的访问对象是否存在。

比如：我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。

4、权限检查

看用户是否具备访问该数据的权限。

5、共享池检查

不错的文章：

《Oracle Database 11g : SQL 基础》

www.cnblogs.com/ios9/p/9245…

共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。

Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。

从缓存查找是否存在执行计划，存在则直接拿来执行，即软解析；不存在，则创建解析树进行解析，即硬解析。

在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 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 优化也会比较困难。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。库缓存区，它主要缓存 SQL 语句和执行计划。数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。

请添加图片描述

6、优化器

优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。

7、执行器

当有了解析树和执行计划之后，就知道了 SQL该怎么被执行，这样就可以在执行器中执行语句了。

二、MySQL 中的 SQL 是如何执行的

MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器端程序使用的 mysqld。

1、执行过程图示

请添加图片描述

2、连接层

建立连接 --> 发送 SQL 到服务端；

客户端和服务器端建立连接，客户端发送 SQL至服务器端；

3、SQL 层

对 SQL 语句进行查询处理；

SQL 层与数据库文件的存储方式无关，下面是 SQL 层的结构！

请添加图片描述

缓存查询

发现 ——> 返回；未发现 ——> 解析器；

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。

解析器

在解析器中对 SQL 语句进行语法分析、语义分析。

优化器

在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。

执行器

在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。

在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

4、存储引擎层

与数据库文件打交道，负责数据的存储和读取。

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

InnoDB 存储引擎

它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

MyISAM 存储引擎

在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

Memory 存储引擎

使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。

NDB 存储引擎

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。

Archive 存储引擎

它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

MySQL 的强大之处

不同的表可以采用不同的存储引擎！

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

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

1、查看 SQL 执行时间

SQL

-- 查看 profiling 是否开启
mysql> select @@profiling;
-- profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：
mysql> set profiling=1;

-- 接着执行一条 sql 语句
mysql> select * from xxx;

-- 查看 profiles (查看当前会话所产生的所有 profiles)
mysql> show profiles;
-- 查看上一条语句产生的 profile （这里有更详细的执行时间）
mysql> show profile;
-- 查询指定的 Query ID
mysql> show profile for query 2;
-- 在 8.0 版本之后，MySQL 不再支持缓存的查询

-- 查看当前 mysql 版本
mysql> select version();

部分截图

请添加图片描述

2、MySQL 特色

MySQL 的一大特色就是提供了各种存储引擎以供选择，不同的存储引擎有各自的使用场景，我们可以针对每张表选择适合的存储引擎。