SQL优化器和语句处理性能分析过程.docx

1、SQL优化器和语句处理性能分析过程5.1 SQL语言概述SQL是结构化查询语言（structured query language）的缩写，是关系数据库系统的标准语言。它提供给用户一种表示方法，说明要查询的结果特性，至于如何查询、以及查询结果的形式都由数据库系统来完成。这种语言由于其功能丰富、方便易学而受到用户的广泛欢迎，1986年由美国国家标准局（ANSI）及国际化标准组织（ISO）公布，作为关系数据库系统的标准语言。目前广泛使用的SQL标准是1992年制定的SQL-92（简称为SQL2）。SQL3是最新的SQL语言标准，在SQL2的基础上增加了许多新的特征。SQL标准的制定使得几乎所有的数

2、据库厂家都采用SQL语言作为其数据库语言，但各个数据库厂家又结合自身需要在SQL标准的基础上进行了扩充。SQL名为结构化查询语言，实际功能包括数据定义、数据操纵和数据控制。具体来说，包含以下几部分内容：（1）数据模式定义语言（data definition language，DDL）。用来定义、修改或者删除基本关系表、视图、索引、完整性约束等数据库对象。（2）数据操纵语言（data manipulation language，DML）。用来查询、插入、删除或者修改数据表中的记录。（3）嵌入式SQL语言：被嵌入程序设计语言中，执行SQL语句处理。（4）权限管理：用来管理和控制对数据库对象的访问。

3、有关SQL语言的详细描述，可以参看其它的数据库书籍，这里不再进一步介绍。对具体的数据库系统，在使用时可以阅读该系统的相关资料，了解其SQL语言的具体使用方法。5.2 优化器概述 用户以SQL语句的形式，向数据库系统发出请求。系统在接收到用户的SQL语句请求后，由优化器进行语法分析和优化，找出SQL语句的执行计划，然后按照此执行计划运行，最后将处理结果返回用户。一个SQL语句可能有很多种等价的执行方式。优化器需要对所有的执行方式进行分析，选择费用最低的执行方式，作为该SQL语句最终的执行计划。5.2.1 为什么要使用优化器 我们知道，数据的存储和管理经历了三个阶段：人工管理阶段、文件系统阶段和数

4、据库系统阶段。在人工管理和文件系统阶段，应用程序和数据紧密结合，应用程序随着数据结构的改变而改变，从而导致数据的利用率不高，数据不易管理，数据一致性很难维护等问题。而在数据库系统阶段，数据库系统采用三级模式结构，由外到内依次为：外模式、模式和内模式，在外模式和模式、模式和内模式之间建立映象关系。这样一来，如果模式发生改变，只需要改变外模式和模式之间的映象，外模式不需要改变。同样，如果内模式发生改变，需要改变的只是模式和内模式之间的映象。这种多模式结构，保证了数据的物理和逻辑独立性，减少了数据冗余，提高了数据利用率。同时，也使用户可以使用统一的接口访问数据库中的数据，由数据库系统来决定数据的处理

5、方式，用户不用、也不必了解数据在数据库如何存放，从而极大地方便了用户的使用。数据库技术的发展经历了层次数据库系统、网状数据库系统、关系数据库系统三个阶段。层次数据库系统使用层次数据模型，系统对用户请求的处理，就是从整个数据的顶层开始查找，一直找到要处理的数据为止。整个数据模型的实现已经定义了数据的处理方式。网状数据库系统使用网状数据模型，任何相互关联的数据之间都要建立联系，系统对用户请求的处理，只要根据这些联系找到要处理的数据就可以了。该数据模型的实现也指定了数据的处理方式。和层次数据库系统、网状数据库系统不同，关系数据库系统使用关系数据模型，就是使用二维表格表示数据以及数据之间的联系。整个数

6、据模型没有给定数据的处理方式。对用户请求的处理，必须由系统根据数据字典信息来决定其处理方式，这就是优化器的工作。一个用户的SQL语句请求可能有很多种等价的执行方式，优化器要决定SQL语句的最终执行计划。然而可不可以由用户来决定SQL语句的执行计划呢？答案是肯定的。但由于数据库中的数据处于不断地变动之中，对一个SQL语句，用户提供的执行计划，只能反映数据及表结构的当前情况。在经过一段时间的运行之后，由于数据量及表结构的变化，该执行计划就不再合适，甚至可能很糟糕。而优化器动态地、根据数据库当前统计信息所生成的执行计划，就能够有效地适应数据量及表结构的这种变化。对用户来说，也不用为指定SQL语句的执

7、行计划而详细地去了解数据库对象的定义、数据的分布等相关信息。5.2.2 优化器的处理过程 在第2.2.7一节中，我们已经对优化器的处理过程有所了解，这里我们将作进一步的介绍。数据库系统对SQL语句的处理，要经过以下三个步骤：（1）语法分析与翻译（2）优化（3）执行其具体执行过程可见图5-1。其中，语法分析与翻译、优化过程由优化器进程完成，而SQL语句的执行由代理进程实现。对一个SQL语句，如果系统能够在内存中找到它的最终执行计划，就直接使用，不会再去分析与优化。1. 语法分析与翻译优化器对SQL语句的语法分析，就是构造该SQL语句的语法分析树表示，验证SQL语句是否存在语法错误、发出请求的用户

8、是否有执行权限等。然后基于语法分析树，将SQL语句翻译成一个关系代数表达式。最后，对这个关系代数表达式使用等价转换规则，找出SQL语句所有等价的关系代数表达式，每一个关系代数表达式就对应SQL语句的一个执行方式。2. 优化对表中数据的访问，可以使用表扫描，也可以使用索引。在从多个表中返回结果时，要执行表的连接，可以采用嵌套循环连接，也可以使用排序归并连接、散列连接，并且表和表之间的连接顺序可以是任意的。SQL语句的一个执行方式，就是各种可能执行途径的一种组合和编排方式，明确指定了语句执行的访问路径、连接方式以及连接顺序等等。SQL语句的不同执行计划，在性能、资源使用上可能会有非常大的差异。优化

9、器对SQL语句的优化处理，就是要从SQL语句所有的执行方式中找出费用最低的一个，作为SQL语句最终的执行计划。而所谓SQL语句的执行费用，就是执行SQL语句时，所需执行时间、CPU使用、内存空间使用、I/O操作数量、生成的中间结果集数量等多方面因素的综合。其中由于I/O操作比较慢，是影响SQL语句运行的关键因素。一些系统的优化器就单纯以I/O操作的数量来衡量SQL语句的费用。为了找出费用最低的执行计划，而将所有的执行计划都执行一遍，这是不可取、也是不现实的。优化器是根据数据字典中的相关信息，通过估算来决定每一个执行计划的执行费用，尽管这种估算有时候并不完全正确。优化器对SQL语句的优化处理，由

10、以下三个步骤完成：（1）根据关系代数表达式，使用等价转换规则，找出所有等价的执行计划。（2）依据数据字典中表和索引的结构定义和相关的统计信息，分别估算每一种执行计划的费用。（3）比较所有执行计划的费用，费用最低的那一个就是SQL语句最终的执行计划。3. 优化器要使用的数据字典信息在数据字典中，被优化器参照的表和索引信息，可以分为两大类：结构定义、数据库对象统计信息。对表、索引的结构定义，具体来说会包括以下这些：（1）表中的字段定义（类型，长度等）、字段的完整性定义、表上是否存在主键和外键、表上是否存在索引，等等。（2）索引建立在那些字段上、索引中字段的顺序（对复合索引而言）、索引的类型（唯一索

11、引、非唯一索引、聚集索引），等等。而对表、索引的统计信息，用户可以根据自己的需要进行选择、收集。大体来说，有以下的内容：（1）对一个表来说，可以统计：表中的字段个数、表中记录的长度、表中的记录数、表的块因子（即一个数据库的数据块可以存放的记录数）、表使用的数据页数，等等。（2）对表中的单个字段来说，可以统计其取值的最大值、最小值、不同取值的个数。可以设定字段取值区间的个数、区间中不同取值的个数，进而可以统计其在每一个取值区间内所包含的记录个数。也可以将多个字段合并在一起进行统计，等等。（3）对索引来说，可以统计：索引的高度、叶索引页的数目、索引中记录的长度、索引的块因子，等等。正是依据这些结构

12、定义和统计信息，优化器能够估算出SQL语句一个执行计划的执行成本。例如：根据一个查询条件，优化器可以检查相应字段的统计信息。如果查询条件中的数值大于字段最大值、或者小于字段最小值，优化器就可以判断符合条件的记录数为零；如果查询条件中的数值在字段的某个取值区间之中，优化器就根据该取值区间中不同取值的个数、所包含的记录个数，估算出符合条件的记录数，再根据表的块因子，估算存放这些记录需要多少数据页、读入内存需要多少次的I/O操作，进而估算出需要CPU、内存资源的数量，等等。优化器的整个估算过程是复杂、耗时的，特别是对一些复杂的SQL语句，如：包含多个查询条件、需要多个表之间的连接、需要产生中间结果集

13、、需要分组和排序等，将耗费更多的时间和系统资源。数据库系统会采取一些措施，来减少优化器对SQL语句的估算、优化次数，这一点我们会在下面讲到。5.2.3 了解优化器处理过程的意义 SQL语言是用户访问数据库数据的通用接口。要达到同样的目的，可以有很多种不同的SQL语句书写方式。不同的SQL语句，尽管会产生相同的结果，但其执行过程、执行效率将会有明显的区别。例如，要从员工表employee中返回所有员工的姓名，使用下面的SQL语句：SELECT * FROM employee优化器将选用表扫描作为执行方式，通过扫描整个表返回结果，不管员工表employee上是否存在索引。而使用下面的SQL语句：S

14、ELECT empy_name FROM employee如果列empy_name上存在索引，优化器将选用索引扫描作为该语句的执行方式。这种方式被称为索引覆盖。由于索引中每条记录包含较少的字段，一个索引页包含更多的记录，使用索引扫描，将使用更少的磁盘I/O操作。对程序员、数据库管理员来说，清楚优化器对SQL语句的优化处理过程，能够大体上判断出一个SQL语句的执行计划，不但有助于写出好的、效率高的SQL语句，而且能够找出引起系统性能瓶颈的SQL语句，从而保证系统的正常、稳定运行。5.3 优化器对SQL语句的处理 尽管找出最小成本的执行计划是优化器的职责，但对于糟糕的SQL语句，优化器也无能为力。

15、深入了解优化器对SQL语句的优化处理，能够帮助用户写出高效的SQL语句。优化器在确定SQL语句最小代价的执行计划时，无外乎就是确定数据的访问路径（索引访问或者表扫描）、表连接的方式及顺序，以及索引不可用时是否进行排序等。下面我们就从这几个方面进行讨论。5.3.1 排序处理 系统对SQL语句的处理可能需要进行排序。这主要是由于以下两个原因引起的：（1）在SQL语句中，指定要对结果集进行排序，或者包含需要排序的关键字。（2）进行表连接时，系统选用排序归并连接、散列连接；或者系统虽然选用嵌套循环连接，但发现作为输入的表如果已排序，其实现效率会更高。有关表连接的详细信息，可参见第5.3.2一节。如果在

16、要排序的字段上存在索引，系统就直接使用该索引，顺序读取表，从而完成排序。如果在要排序的字段上不存在索引，或者尽管存在索引，但按照索引访问需要更多的系统开销时，系统就不会使用索引，只是在最后，基于此字段对结果集进行排序。如果要排序的数据，可以一次在内存中完成，这种排序就称为内排序。如果由于内存空间限制，无法一次完成数据的排序，系统就按照可用内存的空间大小，将要排序数据分成多个部分、分别排序，中间结果被存放在磁盘上，最后将多个已排序结果集合并，形成最终的结果集，这种排序称为外排序。很显然，内排序的执行效率很高。如果数据库中存在排序操作，我们当然希望所有的排序均能够一次在内存中完成。在SQL语句中出

17、现下列的关键字后，如果优化器没有选用相关的索引，就一定会进行排序操作。1. order by该关键字要求对结果集进行排序。如果表中的某些字段经常在order by中使用，在这些字段上建立索引将有助于避免数据库中的排序操作。2. group by该关键字要求对结果集进行分组。系统对分组的处理，是首先基于要分组的字段进行排序，然后对已排序的记录数据，从上到下采取以下的处理步骤，形成结果集：（1）取出第一条记录，放入结果集。（2）取出下一条记录。如果该条记录和结果集中最后一条记录在要分组字段上取值相等，就将该条记录和最后一条记录合并；如果不相等，就将该记录放在结果集的最后面。（3）继续处理，直到所有

18、的记录被处理完成。例如，下面SQL语句返回各部门中员工的月工资总额：SELECT dept_no, sum(empy_salary) FROM employee group by dept_no系统首先对员工表employee基于部门号dept_no进行排序。然后对已经排序的记录集，按照字段dept_no的值，将字段empy_salary中的值相加。3. distinct该关键字要求结果集中不能有重复的记录。系统对distinct的处理，类似于对group by的处理。系统首先按照结果集中要求的字段进行排序，然后按照以下步骤剔除重复的记录。（1）取出第一条记录，放入结果集。（2）取出下一条记录

19、。如果该条记录和结果集中最后一条记录相同，就放弃该条记录；如果不相同，就将该记录放在结果集的最后面。（3）继续处理，直到所有的记录被处理完成。例如，下面SQL语句从员工表employee中返回所有的部门号：SELECT distinct dept_no FROM employee系统首先读取表employee中的所有记录，基于字段dept_no进行排序。然后对已经排序的记录集，按照字段dept_no的值，删除重复的记录。4. 集合操作：union、intersect、minus集合操作：union、intersect、minus，用来实现了两个记录集之间的并、交、差运算，其中： （1）unio

20、n：合并两个记录集，结果集中不能有重复的记录。（2）intersect：返回同时出现在两个记录集中的记录。（3）minus：返回出现在第一个记录集，而不在第二个记录集的所有记录。对于这些集合运算，系统首先将两个记录集基于相同的字段或者字段组合，分别按照相同的方向排序，然后将这两个已经排序的记录集合从上到下进行比较，从而找到最终的结果集。这里，我们需要注意关键字union和union all的区别。union all允许结果集中有重复的记录，因此优化器不会要求对记录集进行排序。5.3.2 表连接的处理 在SQL语句的查询处理中，可能需要从多个表中返回数据，这就需要表的连接。在表的连接时，系统需要

21、考虑的问题有：（1）多个表之间，按照什么样的顺序执行表的连接。（2）采用什么样的方式，实现两个表之间的连接。例如，下列SQL语句返回部门中所有员工的帐户余额：SELECT b.dept_company,a.empy_name,c.acct_balance FROM employee a, department b, account c WHERE a.dept_no=b.dept_no and a.empy_no=c.empy_no在执行表的连接时，可以使用以下八种表连接顺序：(employee,department),account)、(account,(employee,departmen

22、t)(department,employee),account)、(account,(department,employee)(employee,account),department)、(department,(employee,account)(account,employee),department)、(department,(account,employee)此外，两个表之间的连接可以使用以下的连接方式：嵌套循环连接、排序合并连接、散列连接。对这个SQL语句，优化器应当选用那种表连接顺序、表和表之间使用什么样的连接方式呢？我们知道，一个SQL语句有很多种不同的执行方式，这些表连接顺序、

23、连接方式的不同编排和组合，就包含在不同的执行方式中。优化器通过估算获取的最终执行计划，就给出了该SQL语句最合适的表连接顺序和连接方式。对两个表以上的表连接，由于要产生中间结果集，优化器在估算时还要考虑它所消耗的内存、磁盘空间资源。下面我们将对表和表之间的连接方式进行简单介绍。1. 嵌套循环连接嵌套循环连接，由两个嵌套的循环语句组成。处于外层循环中的表称为外层表，处于内层循环中的表称为内层表。系统顺序地读取外层表中的每一条记录，将它和内层表中的每一条记录进行比较，如果满足条件就放入结果集。整个连接的处理过程见图5-2。for 表1中的每一条记录 dobegin for 表2中的每一条记录 do

24、 begin 测试表1和表2当前的记录是否满足连接条件 如果满足，就加入结果集 endend 图 5-2 嵌套循环连接嵌套循环连接不要求表中存在索引，并且不管是什么条件，该算法都可以使用。然而嵌套循环连接算法的代价很大，因为该算法要逐一检查两个表中的每一条记录。对外层表中任一记录的处理，都要对内层表扫描一次。如果内层表能够存放在内存中，将极大地减少磁盘的操作次数。优化器在使用嵌套循环连接时，会考虑表中的记录数，将记录数较小的表作为内层表使用。由于嵌套循环连接的处理效率比较低，人们已经对此算法进行了改进，这就是：块嵌套循环连接和索引嵌套循环连接。2. 块嵌套循环连接对于块嵌套循环连接，系统以块的

25、方式而不是以记录的方式处理表的连接（所谓块，就是数据库系统的数据页，是对磁盘I/O操作的最小单位）。系统顺序地读取外层表中的每一个数据块，将块中每一条记录和外层表所有块中的每一条记录进行比较，如果满足条件就放入结果集。整个连接的处理过程见图5-3。for 表1中的每一数据块 dobegin for 表2中的每一数据块 do begin for 表1当前块中的每一条记录 dobegin for 表2当前块中的每一条记录 do begin 测试表1和表2当前的记录是否满足连接条件 如果满足，就加入结果集 endend endend 图 5-3 块嵌套循环连接在要连接的两个表都不能放入内存时，表连接

26、的处理不可避免要不停地以块为单位进行磁盘的读写。使用块嵌套循环连接，将极大地减少对磁盘的I/O操作。3. 索引嵌套循环连接在嵌套循环连接中，如果内层表在连接属性（表的属性也就是表的字段）上存在索引，系统就可以使用此索引访问内层表，从而取消内层循环对内层表的扫描，这种方法就称为索引嵌套循环连接。使用表上的索引进行嵌套循环连接，一般来说会好于表的扫描。优化器在优化处理时，如果选用了嵌套循环连接，一般会把连接属性上存在索引的表作为内层表来处理，从而可以提高SQL语句的处理性能。如果内层表的连接属性上不存在索引，但是优化器经过估算后，发现在连接属性上建立临时索引，将会降低SQL语句的执行成本。在这种情

27、况下，优化器会在该SQL语句的最终执行计划中，要求首先建立内层表连接属性上的索引，然后再进行索引嵌套循环连接。4. 排序归并连接将参与连接的两个表按照连接属性，分别在相同的方向上进行排序，然后将两个表排序后的记录从上到下，一一进行比较，将满足条件的记录存放到结果集中，这种连接方式就称为排序归并连接。如果参与连接的表已经按照连接属性排序，则该表就不用排序。当两个表的连接属性上存在索引时，这些索引可能会在排序归并连接中被使用。5. 散列连接散列连接要使用散列函数。如果一个表中的记录与另一个表中的记录满足连接条件，那么它们在连接属性上有相同的取值、相同的散列值。基于此原理，散列连接的基本思想是使用散

28、列函数，把两个表分别按照连接属性，划分成一系列有相同散列值的记录集合。然后把具有相同散列值的记录集合，进行排序归并连接，最后将所有的中间结果集进行合并，形成最后的结果集。我们知道，对大数据量表的排序，由于无法将数据全部装入内存，不能在内存中完成排序操作，整个排序过程分阶段进行，需要磁盘空间存放排序的中间结果，降低了排序的效率。而且随着数据量的增长，排序的效率将大幅度下降。因此对大数据量表之间的连接，直接使用排序归并连接，处理的效率会很低。相对于排序归并连接，散列连接首先将大数据量表划分成许多小表，这些小表的排序可以在内存中完成，然后再执行排序归并连接，其连接的效率会得到提高。因此散列连接适合于

29、大数据量表之间的连接处理。5.3.3 访问路径的选择 对表中数据的访问，可以使用表扫描，也可以使用索引访问。对一个SQL语句来说，究竟采用哪种数据访问路径，最终由优化器决定。使用索引访问表中数据时，系统首先要将索引页读入内存，根据索引记录中的指针，找到数据所在表中的数据页，然后再将数据页读入内存，进而找到所需数据。使用索引访问表中的一条记录，至少需要两次的I/O操作。优化器通过估算I/O操作的多少，来决定表的访问路径。对建有索引的表，优化器并不总是按照索引访问表中的数据。有时候使用索引找到所需数据，会比单纯的表扫描需要更多的I/O操作。在下列情况下，优化器会使用表扫描而不会选择索引，来作为数据

30、的访问路径：（1）表中的记录较少，只使用很少的数据页。在这种情况下，优化器采用表扫描方式访问数据。依据索引访问，反而会增加磁盘的I/O操作。同时我们也可以看出，在这种小数据量的表上建立索引，对数据的查询不会有所帮助，反而影响到表的更新操作。（2）查询语句需要返回大量的记录。如果一个查询语句需要从表中返回大量的记录，例如：返回记录占表中总记录的30%以上，使用索引将需要更多的I/O操作。这时表扫描会是更好的数据访问路径。（3）查询语句的条件选择中包含多个条件。如果查询语句中的多个条件是或的关系，或者可以使用索引的查询条件不能有效地降低记录的数目，在这种情况下系统就使用一次表扫描，同时检查所有的查询条件。将多个条件分开、使用索引查询，再进行记录集合并，将需要更多的I/O操作。（4）表上没有索引，或者有索引，但无法使用。为经常需要访问的表建立索引。索引的创建，应结合应用程序，根据查询条件中字段的使用频度，决定在那些字段上建立索引。至于索引创建所需要遵循的一些原则，可见第5.6.1一节。（5）SQL语句的写法存在问题。至于如何写好SQL语句，我们将在第5.6.2一节中说明。5.4 优化器优化过程的改进 优化器的优化过程需要时间，需要耗费CPU、内存等