SQL查询处理过程解析

2023-11-01 理论教育版权反馈

【摘要】：图2-1在SQL Tool上运行Query语句如上图2-1所示，可以得出直观的良好结果。如果是以往未运行过的SQL语句，则会进行硬解析过程。② 变更的SQL和变更前的SQL提取相同数据，变更 SQL是为了最大限度地减少处理范围以提高性能。③这是为相应SQL提供附加条件，以减少处理范围。2）制定执行计划为实际处理指定的SQL语句，Oracle通过多个步骤执行数据集的访问和处理操作。在数据库中制定SQL语句的执行计划与计划乘坐几号线到达目的地具有相同的意义。

例如，在 ORANGE的［SQL Tool］上，按以下方式运行 SELECT * FROM SCOTT.EMP；的Query语句，如图2-1所示。

pagenumber_ebook=35,pagenumber_book=24

图2-1　在SQL Tool上运行Query语句

如上图2-1所示，可以得出直观的良好结果。以下开始介绍该SELECT语句通过哪些过程得出图上所示的结果值。

将按分析SELECT * FROM SCOTT.EMP；语句的语法—制定并运行执行计划—得出结果的顺序进行。

1）语法分析（syntax analysis）

首先介绍的是语法分析。语法分析中处理的内容包括用户输入的QUERY语句以往是否执行过、语法是否错误、输入的对象（表、用户等）是否真实存在等。语法分析可细分为以下 6个步骤：

① SEARCH（搜索）；

② SYNTAX（语法检查）；

③ SEMANTIC（语义检查）；

④ QUERY TRANSFORMATION（查询转换）；

⑤权限确认；

⑥ TM （table management） LOCK。

语法分析步骤从①搜索是否有相同SQL的信息开始。在SGA的Shared Pool内的库缓存（library cache）中，如果相同的SQL含有SQL执行计划等信息，则会重复使用该内容。因此，省略语法分析以后的过程，直接转至运行步骤，被称为软解析（soft parsing）。如果是以往未运行过的SQL语句，则会进行硬解析（hard parsing）过程。硬解析是指语法分析、制定执行计划这两个过程都重新进行。因此，硬解析太多会导致性能下降，最好避免进行硬解析。有些人可能会问如何能避免硬解析，其中典型方法就是使用绑定变量，随后本书将进行介绍这项内容。重新查看①搜索内容时，搜索后发现不是过去运行过的SQL语句，则会转至②语法检查步骤检查 QUERY语句的语法。如果没有语法错误，则会转至③SEMANTIC步骤检查QUERY语句中使用的表和列等是否在数据库中确实存在。如果使用的是不存在的表或列，用户会收到错误消息。如果表和列存在，则会转至④查询转换步骤。查询转换步骤中不会检查语法，而是转换查询方式，得出与用户输入的查询语句相同的结果值的同时有效进行表达。在此过程中，优化器尽可能多地检查事件个数的访问路径，以便找出更适合的执行计划。查询转换按以下方式进行：

（1）视图合并（MERGING）：

①仅在内嵌（inline）视图或即使合并视图和主查询仍得出与现有SQL相同结果值时才执行。

②执行内嵌视图或视图与主查询的整合的步骤。

③ 如果在视图中使用OUTER JOIN、GROUP BY、所有AGGREGATE FUNCTION、OWNUM、DISTINCT等，则无法合并视图。因为如果视图内部含有其他条件，则输出的结果与原来的值不同，违反了完整性。

④为合并视图，应始终检查执行计划。

参考以下例子：

pagenumber_ebook=36,pagenumber_book=25

输入这些查询语句时，将按以下方式合并视图：

pagenumber_ebook=36,pagenumber_book=25

按这种方式合并视图可解决访问较多的情况。

（2）子查询合并（MERGING）：

① 删除子查询或变更为EXISTS子句或NOT EXISTS子句的现象。

② 变更的SQL和变更前的SQL提取相同数据，变更 SQL是为了最大限度地减少处理范围以提高性能。

③ 子查询合并有时会导致性能不良，必须检查执行计划。

（3）传递（transitivity）：

① 简单来说就是自然转移。（如果a=b， b=c，则a=c。）

②在此步骤中，执行将无逻辑问题的条件添加至相应SQL的操作。

③这是为相应SQL提供附加条件，以减少处理范围。

参考以下例子：

pagenumber_ebook=37,pagenumber_book=26

输入以上查询语句时，会在最后部分加上 AND E.DEPTNO =10。此外还有 OR EXPANSION步骤、QUERY REWRITE步骤。

查询转换步骤结束后将确认语法分析的第5步⑤权限。权限确认是确认运行SQL语句的用户是否有各个表的访问权限，以及是否能执行SQL语句的其他权限。完成权限确认步骤后对表施加表管理（table management）锁。在运行最终完成语法分析的SQL语句之前，其他用户无法删除和变更（为了避免使用DML语句）该表，是对全部表执行 LOCK以控制访问的步骤。该步骤是数据完整性（数据不矛盾）的必要步骤。语法分析结束后将转至制定执行计划步骤。

2）制定执行计划（execution plan）

为实际处理指定的SQL语句，Oracle通过多个步骤执行数据集的访问和处理操作。以层次结构表示一系列此类操作步骤的方式即称为执行计划。(www.chuimin.cn)

pagenumber_ebook=37,pagenumber_book=26

图2-2　地铁线路和SQL执行计划

例如，想乘坐上海地铁从上海站去世纪大道站，有很多实现方法，分为多次换乘但较为快速、时间较长但少换乘的方法等，如图2-2所示。在数据库中制定SQL语句的执行计划与计划乘坐几号线到达目的地具有相同的意义。执行计划中包括按哪种顺序对基表执行访问，按哪种方式访问各个数据集，按哪种方式执行表和表、数据集和数据集之间的JOIN的信息。用户将通过优化器分析SQL语句创建的执行计划来确保调优的关键和基本信息，但执行计划也存在缺点。执行计划是在实际运行SQL语句之前通过统计信息和优化器的计算创建的计划（以后要做事情的步骤），因此并不总能创建全面优化的计划。因此要意识到实际上有可能应用其他执行计划处理SQL语句后再解析执行计划。

使用DB开发者常用的Orange （Oracle DB Tool）确认以下例子的执行计划。确认方法有在［Plan Tool］中查看执行计划的方法和在［SQL Tool］中查看执行计划的方法。

参考以下例子：

pagenumber_ebook=38,pagenumber_book=27

3）解析执行计划（execution plan）

在执行计划的各个步骤处理的行将转发给下一个步骤，这种在一个步骤处理后转发的行集称为row source。各个步骤从数据对象直接提取行或从之前的步骤输入 row source。在图2-3所示 Plan Tool中确认的执行计划中，步骤 3、4、5通过实际访问对象导入行，步骤1、2则通过输入row source进行操作（执行计划的左列为row source key，旁边的列为row source parent key。上述说明为row source key标准）。在图2-4中，对于全表扫描这种可能会降低性能的计划，可以查看设置为红色的文字部分。

pagenumber_ebook=39,pagenumber_book=28

图2-3　在Plan Tool中确认执行计划

pagenumber_ebook=39,pagenumber_book=28

图2-4　在 SQL Tool中确认执行计划

4）执行步骤

简单正确地解析执行计划的方法为“从缩进最多部分开始从上往下解析”。如图2-3中的“Execution Plan”区块所示，从缩进最多的4-3开始解析，用pk_emp扫描索引，还有行相同的3-2和5-2。在这种情况下则从上面开始解析。3-2是用emp的ROWID扫描表，5-2是用 SALGRADE扫描全表。在 2-1进行连接，在 1-0运行 HASH（GROUP BY）。按顺序解析了执行计划。下面将重点介绍各个顺序所代表的含义。

①4-3： INDEX RANGE SCAN。索引范围扫描是索引相关执行计划中使用最多、创建最多的执行计划。在WHERE条件下使用如LIKE、BETWEEN、＜、＞等先决条件时生成索引列。访问索引时，扫描不满足条件的一个值后结束扫描。

索引范围扫描的缺点主要是为了处理大范围而不使用大部分数据，仅使用某些数据提取结果时会降低性能。为解决此问题，可采取将WHERE条件的先决条件变更为点条件以缩小扫描范围，或在合并列索引顺序中将点条件作为前导列运行，以缩小扫描范围的方法。

② 3-2： ROWID扫描。该方法作为使用ROWID扫描表的方式，是Oracle中可以最快访问相应记录的方法。ROWID是数据库中各行的唯一标识符。ROWID具有物理地址，可迅速查找要通过SINGLE BLOCK ACCESS查找的行，因此速度最快。

ROWID由以下部分组成：

pagenumber_ebook=39,pagenumber_book=28

（1） 6位：数据对象号（data object number）——对象的唯一号码。

（2） 3位：相对文件号（relative file number）——各个数据文件分配到的号码。

（3） 6位：数据块号（block number）——指出数据块位置的号码。

（4） 3位：数据块内的行号（row number）——指出保存在Oracle数据块头中的行目录槽位置的唯一号码。

但不推荐将ROWID设置为搜索条件。ROWID是对用户没有任何意义的字符串，因此很难记住，可以通过导出、导入、表移动等更改 ROWID。图 2-5所示为在 SQL Tool中查找 ROWID。

pagenumber_ebook=40,pagenumber_book=29

图2-5　在SQL Tool中查找ROWID

③5-2： FULL-TABLE SCAN。全表扫描是确认整个表中是否有与条件相同值的一种扫描方法。许多人认为进行全表扫描是因为索引选择不正确，认为索引扫描优于全表扫描。索引扫描确实会改善性能，但对DML语句、查找结果值多的数据以及Hash连接等使用全表扫描，性能会比应用索引时更加良好。因此，相比考虑使用哪种方法扫描，考虑是否会造成性能下降更为重要。

FULL-TABLE SCAN的特点包括：

（1）可使用并行处理（Parallel Processing）。一般情况下，表只使用一个进程访问，因此即使有大量资源，也无法全部使用。但在FULL-TABLE SCAN中一个操作可以使用多个进程，因此可以最大限度地利用该系统的资源，快速访问大容量表。Parallel Processing使用大量资源，因此在线使用时需注意，访问大容量数据时也应慎重使用。

（2）多重数据块I/O：在全表扫描过程中若执行一次磁盘I/O，则会同时读取与DB_FILE MULTI BLOCK_READ COUNT参数指定的值相同的DB数据块。因此处理对象集数量相同时，相比执行SINGLE数据块I/O的一般索引扫描，执行全表扫描时性能更优秀。

④ 2-1：嵌套循环连接（NESTED-LOOP JOIN）。扫描外部（outer）表时若满足条件，使用连接列相应的值扫描内部（inner）表时会搜索相同的数据，并按外部表的结果数反复扫描内部表。大部分在线程序，为了以特定表的条件为标准读取所需信息，将会以与其他表连接的运行计划，但某些时候会指定采用 NESTED-LOOPS方式的运行计划。NESTEDLOOP JOIN是按处理范围执行循环的连接，适合于处理较少行的连接。但如果错误地使用NESTED-LOOP JOIN可能会严重降低性能。因此应先了解NESTED-LOOP JOIN的正确结构再使用。

将在本书的后半部“连接的种类和顺序”中再次介绍此内容。

A.driving（outer）表——在连接对象表中先扫描的表。

B.inner表——在连接对象表中后扫描的表。NESTED-LOOP JOIN分为先扫描的表和后扫描的表，因此连接顺序有很大影响。

⑤ 1-0： HASH （GROUP BY）

从Oracle到9i为止执行 GROUP BY时一般会进行排序。但优化器在版本升级（version up）完毕的最新版本中，可创建HASH（GROUP BY）执行计划。为避免因排序造成性能下降，逐渐呈现省略排序的趋势。SORT （GROUP BY）由用于GROUP BY子句的列提取排序的值，但 HASH（GROUP BY）不会提取排序的值。当然，HASH的速度快得多。但使用HASH （GROUP BY）时不会执行排序，要与现有使用GROUP BY语句执行排序的SQL比较时应慎重。

制定执行计划时，Oracle提供基于规则（rule-based）和基于成本（cost-based）的两种优化技术。基于规则的优化器（rule-based optimizer， RBO）根据条件语句的syntax（语法）预先确定条件范围，以此为基础制定执行计划，基于成本的优化器（cost-based optimizer，CBO）则以对象的data dictionary统计信息为基础算出条件语句的成本（cost）后制定执行计划。CBO从Oracle 7版本开始就已推出并且持续改善其功能，按Oracle的策略，以后将废除RBO。此外，Oracle 8版本后新推出的分区、M-VIEW等许多功能只能在CBO下使用，因此最好默认使用CBO，必要时通过SQL或会话（session）单元选择RBO使用是明智的方式。

SQL查询处理过程解析

相关推荐