SQL从入门到放弃系列——SQL执行过程[三]

2019年6月19日03:07:21 发表评论 65
摘要

本文是Oracle和MySQL中SQL执行流程。
硬解析和软解析概念。
MySQL体系结构。
存储引擎概念,MySQL存储引擎。

Oracle中SQL是如何执行的

SQL在Oracle中的执行过程

  1. 语法检查:检查语法是否有误。
  2. 语义检查:检查SQL中访问对象是否存在。
  3. 权限检查:检查用户是否有访问数据权限。
  4. 共享池检查:一块内存池,主要用于缓存SQL语句和该语句的执行计划。Oracle通过检查内存池是否存在SQL语句执行计划,判断进行软解析还是硬解析。(软解析:Oracle对SQL语句进行hash运算,根据hash值在库缓存中查找是否存在SQL执行计划,如果存在就直接执行,进入执行器环节,就是软解析。如果没有找到SQL执行计划,Oracle需要创建解析树进行解析,生成执行计划,进入优化器环节,就是硬解析。)
  5. 优化器:创建解析树,生成执行计划。
  6. 执行器:执行SQL语句。

如何避免硬解析,尽量使用软解析呢

SQL执行过程中创建解析树,生成执行计划很消耗资源,所以应尽量避免硬解析。

例如:

select * from player where player_id = 10010;

绑定变量:

select * from player where player_id = :player_id

这两种查询效率在Oracle中完全不同。绑定变量的方式会在查询后,在共享池中缓存执行计划,也就是软解析。

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

 

MySQL中的SQL如何执行的

MySQL是CS架构,服务端程序使用的mysqld。由三层组成:

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

SQL语句在MySQL中的流程是:SQL语句=》缓存查询=》解析器=》优化器=》执行器。有一部分和Oracle中的SQL执行原理是一样的。不同的是,MySQL存储引擎采用插件的形式,每个存储引擎都面向一种特定的数据库应用环境。同时MySQL还允许开发人员设置自己的存储引擎。

 

常见的存储引擎

InnoDB存储引擎

它是MySQL5.5.8之后默认的存储引擎,支持事务、行级锁定、外键锁定等。

MyISAM存储引擎

它是5.5.8之前默认的存储引擎,不支持事务、不支持外键,但速度快、占用资源少。

Memory存储引擎

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

NDB存储引擎

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

Archive存储引擎

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

注意

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

 

数据库管理系统(DBMS)

Oracle和MySQL的结构图

SQL从入门到放弃系列——SQL执行过程[三]

 

SQL从入门到放弃系列——SQL执行过程[三]

 

关键点是SQL执行原理

不同的DBMS的SQL执行原理是相通的,只是在不同的软件中,实现路径不同。

既然一条SQL语句会经历不同的模块,那在不同的模块中,SQL执行所用的资源(时间)是怎样的。

如何对MySQL中对一条SQL语句的执行时间进行分析

开启profiling可以让MySQL收集在SQL执行时所使用的资源情况。命令:

mysql>select @@profiling;

profiling=0代表关闭,设置为1即为打开。命令:

mysql>set profiling=1;

然后执行任意SQL:

mysql>select * from wucai.heros;

查看当前回话所产生的所有profiles,命令:

mysql>show profiles;

SQL从入门到放弃系列——SQL执行过程[三]

查看上一次查询的执行时间,命令:

mysql>show profile;

查询指定的Query ID(例如ID为2的查询),命令:

mysql>show profile for query 2;

结果是一样的。

 

Oracle和MySQL的异同

相同点

它们都是通过解析器=》优化器=》执行器这样的流程来执行SQL的。

异同点

软件实现层面的差异。

Oracle提出了共享池概念,通过共享池来判断是进行软解析还是硬解析。

MySQL在8.0版本后不再支持查询缓存,而是直接执行解析器=》优化器=》执行器的流程。各种存储引擎也是MySQL的一大特色,针对不同场景可以选择不同的存储引擎,可以对每张表选择适合的存储引擎。

 

为什么MySQL8.0之后不再支持缓存查询

因为一旦数据表有更新,缓存都将清空,因此只有数据表是静态的时候,或者数据表很少发生变化时,使用缓存查询才有价值,否则数据表经常更新,反而增加了SQL的查询时间。

 

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: