sql执行原理,过程详述

来源:互联网 发布:ps淘宝店标图片制作 编辑:程序博客网 时间:2024/03/29 06:11

第一步:客户端把语句发给服务器端执行

第二步:语句解析

1. 查询高速缓存(librarycache)

服务器收到提交的sql语句后并非直接执行,而是会先在数据库的高速缓存中去查找,是否存在相同语句的执行计划。如果在数据高速缓存中,则服务器进程就会直接执行这个已有的 SQL 语句,省去后续的工作。所以,采用高速数据缓存的话,可以提高 SQL 语句的查询效率。

一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高,另一方面,也是因为这个语句解析的原因。

2. 语句合法性检查(data dictcache)

如果在高速缓存中找不到对应的 SQL语句时,则服务器进程中就会开始检查这条语句的合法性。这里主要是对 SQL语句的语法进行检查,看看其是否合乎语法规则。

3. 语言含义检查(data dictcache)。若 SQL语句符合语法上的定义的话,则服务器进程接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。

4. 获得对象解析锁(controlstructer)。当语法、语义都正确后,系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性,防止我们在查询的过程中,其他用户对这个对象的结构发生改变。

5. 数据访问权限的核对(data dictcache)。当语法、语义通过检查之后,客户端还不一定能够取得数据。服务器进程还会检查,你所连接的用户是否有这个数据访问的权限。数据库服务器进程先检查语法与语义,然后才会检查访问权限。

6. 确定最佳执行计划

当服务器进程的优化器确定这条查询语句的最佳执行计划后,就会将这条 SQL语句与执行计划保存到数据高速缓存(librarycache)

第三步:语句执行

语句解析只是对 SQL语句的语法进行解析,以确保服务器能够知道这条语句到底表达的是什么意思。等到语句解析完成之后,数据库服务器进程才会真正的执行这条 SQL 语句。这个语句执行也分两种情况。

一是若被选择行所在的数据块已经被读取到数据缓冲区的话,则服务器进程会直接把这个数据传递给客户端,而不是从数据库文件中去查询数据。

若数据不在缓冲区中,则服务器进程将从数据库文件中查询相关数据,并把这些数据放入到数据缓冲区中(buffercache)
第四步:提取数据


DBCC DROPCLEANBUFFERS
从缓冲池中删除所有清除缓冲区。
DBCC FREEPROCCACHE

从过程缓存中删除所有元素。
DBCC FREESYSTEMCACHE

从所有缓存中释放所有未使用的缓存条目
SQL
语句中的函数、关键字、排序等执行顺序:
1. FROM
子句返回初始结果集。
2. WHERE
子句排除不满足搜索条件的行。
3. GROUP BY
子句将选定的行收集到 GROUP BY 子句中各个唯一值的组中。
4.
选择列表中指定的聚合函数可以计算各组的汇总值。
5.
此外,HAVING 子句排除不满足搜索条件的行。
6.
计算所有的表达式;
7.
使用 order by 对结果集进行排序。
8.
查找你要搜索的字段。

二、SQL语句执行完整过程:
1.
用户进程提交一个 sql 语句:给服务器进程。
2.
服务器进程从用户进程把信息接收到后,在 PGA(program global area) 中就要此进程分配所需内存,存储相关的信息,如在会话内存存储相关的登录信息等。
3.
服务器进程把这个 sql 语句的字符转化为 ASCII 等效数字码,接着这个 ASCII 码被传递给一个HASH函数,并返回一个 hash,然后服务器进程将到shared pool中的 librarycache中去查找是否存在相同的 hash,如果存在,服务器进程将使用这条语句已高速缓存在 SHARED POOL library cache中的已分析过的版本来执行。
4.
如果不存在,服务器进程将在 CGA 中,配合 UGA 内容对 sql,进行语法分析,首先检查语法的正确性,接着对语句中涉及的表,索引,视图等对象进行解析,并对照数据字典检查这些对象的名称以及相关结构,并根据ORACLE选用的优化模式以及数据字典中是否存在相应对象的统计数据和是否使用了存储大纲来生成一个执行计划或从存储大纲中选用一个执行计划,然后再用数据字典核对此用户对相应对象的执行权限,最后生成一个编译代码。
5.ORACLE
将这条 sql 语句的本身实际文本、HASH 值、编译代码、与此语名相关联的任何统计数据和该语句的执行计划缓存在 SHARED POOL library cache中。服务器进程通过 SHARED POOL锁存(shared pool latch)来申请可以向哪些共享 PL/SQL区中缓存这此内容,也就是说被SHARED POOL锁存器锁定的 PL/SQL区中的块不可被覆盖,因为这些块可能被其它进程所使用。
6.
在 SQL 分析阶段将用到 LIBRARY CACHE,从数据字典中核对表、视图等结构的时候,需要将数据字典从磁盘读入 LIBRARY CACHE,因此,在读入之前也要使用LIBRARY CACHE锁存器(library cache pin,librarycache lock)来申请用于缓存数据字典。到现在为止,这个 sql 语句已经被编译成可执行的代码了,但还不知道要操作哪些数据,所以服务器进程还要为这个 sql 准备预处理数据。
7.
首先服务器进程要判断所需数据是否在 db buffer 存在,如果存在且可用,则直接获取该数据,同时根据LRU算法增加其访问计数;如果 buffer不存在所需数据,则要从数据文件上读取首先服务器进程将在表头部请求 TM(保证此事务执行过程其他用户不能修改表的结构),如果成功加 TM ,再请求一些行级锁(TX),如果 TMTX锁都成功加锁,那么才开始从数据文件读数据,在读数据之前,要先为读取的文件准备好buffer空间。服务器进程需要扫面 LRU list寻找 free dbbuffer,扫描的过程中,服务器进程会把发现的所有已经被修改过的 db buffer 注册到 dirty list,这些 dirty buffer会通过 dbwr的触发条件,随后会被写出到数据文件,找到了足够的空闲 buffer,就可以把请求的数据行所在的数据块放入到 db buffer的空闲区域或者覆盖已经被挤出 LRU list的非脏数据块缓冲区,并排列在 LRU list的头部,也就是在数据块放入 DBBUFFER之前也是要先申请 db buffer 中的锁存器,成功加锁后,才能读数据到 db buffer
8.
记日志 现在数据已经被读入到 db buffer 了,现在服务器进程将该语句所影响的并被读入 db buffer中的这些行数据的 rowid及要更新的原值和新值及 scn等信息从 PGA逐条的写入 redo log buffer中。在写入 redo log buffer之前也要事先请求 redo log buffer的锁存器,成功加锁后才开始写入,
写入达到 redo logbuffer 大小的三分之一或写入量达到 1M 或超过三秒后或发生检查点时或者 dbwr 之前发生,都会触发 lgwr进程把 redo logbuffer的数据写入磁盘上的 redo file文件中(这个时候会产生log file sync等待事件)已经被写入 redofile redo log buffer所持有的锁存器会被释放,并可被后来的写入信息覆盖,redo log buffer是循环使用的。Redo file 也是循环使用的,当一个 redo file写满后,lgwr进程会自动切换到下一 redo file(这个时候可能出现 logfileswitch(checkpoint complete)等待事件)。如果是归档模式,归档进程还要将前一个写满的 redo file 文件的内容写到归档日志文件中(这个时候可能出现 log file switch(archivingneeded)
9.
为事务建立回滚段 在完成本事务所有相关的 redo log buffer之后,服务器进程开始改写这个 db buffer
的块头部事务列表并写入 scn,然后 copy 包含这个块的头部事务列表及 scn 信息的数据副本放入回滚段中,将这时回滚段中的信息称为数据块的“前映像“,这个”前映像“用于以后的回滚、恢复和一致性读。(回滚段可以存储在专门的回滚表空间中,这个表空间由一个或多个物理文件组成,并专用于回滚表空间,回滚段也可在其它表空间中的数据文件中开辟。
10.
本事务修改数据块 准备工作都已经做好了,现在可以改写 db buffer 块的数据内容了,并在块的头部写入回滚段的地址。
11.
放入 dirty list 如果一个行数据多次 update 而未 commit,则在回滚段中将会有多个“前映像“,除了第
一个”前映像“含有 scn 信息外,其他每个“前映像“的头部都有 scn 信息和“前前映像”回滚段地址。一个update只对应一个 scn,然后服务器进程将在 dirty list中建立一
条指向此 db buffer 块的指针(方便 dbwr 进程可以找到 dirty list 的 db buffer 数据块并写入数据文件中)。接着服务器进程会从数据文件中继续读入第二个数据块,重复前一数据块的动作,数据块的读入、记日志、建立回滚段、修改数据块、放入 dirty list。当 dirty queue的长度达到阀值(一般是 25%),服务器进程将通知dbwr把脏数据写出,就是释放 db buffer上的锁存器,腾出更多的 free dbbuffer。前面一直都是在说明oracle一次读一个数据块,其实 oracle可以一次读入多个数据块(db_file_multiblock_read_count来设置一次读入块的个数)

说明:
在预处理的数据已经缓存在 db buffer 或刚刚被从数据文件读入到 db buffer 中,就要根据 sql 语句的类型来决定接下来如何操作。
1>
如果是 select 语句,则要查看 db buffer 块的头部是否有事务,如果有事务,则从回滚段中读取数据;如果没有事务,则比较 select scn db buffer块头部的 scn,如果前者小于后者,仍然要从回滚段中读取数据;
如果前者大于后者,说明这是一非脏缓存,可以直接读取这个 db buffer 块的中内容。
2>
如果是 DML 操作,则即使在 db buffer 中找到一个没有事务,而且 SCN 比自己小的非脏缓存数据块,服务器进程仍然要到表的头部对这条记录申请加锁,加锁成功才能进行后续动作,如果不成功,则要等待前面的进程解锁后才能进行动作(这个时候阻塞是 tx 锁阻塞)用户 commit rollback到现在为止,数据已经在 db buffer或数据文件中修改完,但是否要永久写到数文件中,要由用户来决定 commit(保存更改到数据文件) rollback撤销数据的更改)
1.
用户执行 commit 命令
只有当 sql 语句所影响的所有行所在的最后一个块被读入 db buffer 并且重做信息被写入 redo log buffer(仅指日志缓冲区,而不包括日志文件)之后,用户才可以发去 commit 命令,commit触发 lgwr进程,但不强制立即 dbwr来释放所有相应 db buffer 块的锁(也就是no-force-at-commit,即提交不强制写),也就是说有可能虽然已经 commit ,但在随后的一段时间内 dbwr还在写这条 sql语句所涉及的数据块。表头部的行锁并不在 commit之后立即释放,而是要等 dbwr进程完成之后才释放,这就可能会出现一个用户请求另一用户已经 commit的资源不成功的现象。
A .
从 Commit 和 dbwr 进程结束之间的时间很短,如果恰巧在 commit 之后,dbwr 未结束之前断电,因为commit之后的数据已经属于数据文件的内容,但这部分文件没有完全写入到数据文件中。所以需要前滚。由
commit 已经触发 lgwr,这些所有未来得及写入数据文件的更改会在实例重启后,由 smon 进程根据重做日志文件来前滚,完成之前 commit未完成的工作(即把更改写入数据文件)
B.
如果未 commit 就断电了,因为数据已经在 db buffer 更改了,没有 commit,说明这部分数据不属于数据文件,由于 dbwr之前触发 lgwr也就是只要数据更改,(肯定要先有 log)所有 DBWR,在数据文件上的修改都会被先一步记入重做日志文件,实例重启后,SMON进程再根据重做日志文件来回滚。其实 smon的前滚回滚是根据检查点来完成的,当一个全部检查点发生的时候,首先让 LGWR 进程将redo logbuffer中的所有缓冲(包含未提交的重做信息)写入重做日志文件,然后让 dbwr 进程将 db buffer提交的缓冲写入数据文件(不强制写未提交的)。然后更新控制文件和数据文件头部的 SCN,表明当前数据库是一致的,在相邻的两个检查点之间有很多事务,有提交和未提交的。

像前面的前滚回滚比较完整的说法是如下的说明:
A.
发生检查点之前断电,并且当时有一个未提交的改变正在进行,实例重启之后,SMON进程将从上一个检查点开始核对这个检查点之后记录在重做日志文件中已提交的和未提交改变,因为dbwr之前会触发 lgwr,所以 dbwr对数据文件的修改一定会被先记录在重做日志文件中。因此,断电前被DBWN写进数据文件的改变将通过重做日志文件中的记录进行还原,叫做回滚,

B. 如果断电时有一个已提交, dbwr动作还没有完全完成的改变存在,因为已经提交,提交会触发 lgwr进程,所以不管 dbwr动作是否已完成,该语句将要影响的行及其产生的结果一定已经记录在重做日志文件中,则实例重启后,SMON进程根据重做日志文件进行前滚.实例失败后用于恢复的时间由两个检查点之间的间隔大小来决定,可以通个四个参数设置检查点执行的频
:
Log_checkpoint_interval:

决定两个检查点之间写入重做日志文件的系统物理块(redo blocks)
的大小,默认值是 0,无限制。
log_checkpoint_timeout:

 两 个 检 查 点 之 间 的 时 间 长 度(秒)默 认 值 1800s。
fast_start_io_target:

决定了用于恢复时需要处理的块的多少,默认值是 0,无限制。
fast_start_mttr_target:

直接决定了用于恢复的时间的长短,默认值是 0,无限制(SMON 进程执行的前滚和回滚与用户的回滚是不同的,SMON是根据重做日志文件进行前滚或回滚,而用户的回滚一定是根据回滚段的内容进行回滚的。
在这里要说一下回滚段存储的数据,假如是 delete 操作,则回滚段将会记录整个行的数据,假如是 update,则回滚段只记录被修改了的字段的变化前的数据(前映像),也就是没有被修改的字段是不会被记录的,假如是insert,则回滚段只记录插入记录的 rowid这样假如事务提交,那回滚段中简单标记该事务已经提交;假如是回退,则如果操作是 delete,回退的时候把回滚段中数据重新写回数据块,操作如果是 update,则把变化前数据修改回去,操作如果是 insert,则根据记录的 rowid把该记录删除。
2.
如果用户 rollback。
则服务器进程会根据数据文件块和 DB BUFFER 中块的头部的事务列表和 SCN 以及回滚段地址找到回滚段中相应的修改前的副本,并且用这些原值来还原当前数据文件中已修改但未提交的改变。如果有多个“前映像”,服务器进程会在一个“前映像”的头部找到“前前映像”的回滚段地址,一直找到同一事务下的最早的一个“前映像”为止。一旦发出了 COMMIT,用户就不能rollback,这使得 COMMIT DBWR进程还没有全部完成的后续动作得到了保障。到现在为例一个事务已经结束了。
说明:
 TM
锁:符合 lock机制的,用于保护对象的定义不被修改。

TX :这个锁代表一个事务,是行
级锁,用数据块头、数据记录头的一些字段表示,也是符合 lock 机制,有 resourcestructure、lock structureenqueue算法。

0 0
原创粉丝点击
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防,我是瞎子 淘宝买家收货地址填写不全怎么办 护士电子注册账户未激活怎么办 您的邮件被退回怎么办 给国外发信被退怎么办 苹果8icloud满了怎么办 吃人参回奶了怎么办 邮箱被黑客黑了怎么办 传图识字有表格怎么办 手机qq收件箱图片打不开怎么办 腾讯企业邮箱一直被攻击怎么办 qq邮箱发送文件太大怎么办 苹果手机邮箱被删除了怎么办 亚马逊卖家登录邮箱被盗怎么办 邮箱名字被注册了怎么办 忘了注册的邮箱名字怎么办 大众车钥匙丢了怎么办 锁柜钥匙丢了怎么办 邮箱的储存空间太小怎么办 扣扣邮箱不支持打开文件怎么办 邮箱大师群发不了邮件怎么办 邮政uk密码忘了怎么办 dns配置错误网页打不开怎么办 手机邮箱收不到邮件怎么办 wifi的那个用户密码错了怎么办 天翼校园客户端连不上怎么办 公司不小心外发邮件怎么办 steam账号被盗邮箱被改怎么办 steam被盗绑定邮箱被改怎么办 简历邮件发错了怎么办 发了简历不回复怎么办 发了简历没回复怎么办 发了简历邮件没有回复怎么办 快手绑定的手机号不用了怎么办 绑定银行卡的手机号不用了怎么办 oppo账号密码忘了怎么办 魅族账号密码忘了怎么办 苹果手机账号密码忘了怎么办 oppo手机账号密码忘了怎么办 华为手机账号密码忘了怎么办 vivo手机账号密码忘了怎么办 金立s9密码忘了怎么办