第五章 优化器和SQL语句处理

用户以SQL语句的形式访问数据库。一个SQL语句可以有很多种执行方式，其最终的执行计划由优化器决定。优化器完成SQL语句的语法分析和翻译，并根据数据字典中信息决定SQL语句的最终执行计划。了解优化器对SQL语句的优化和分析过程，将帮助用户写出高效率的SQL语句、合理地维护和控制优化器的处理。

本章介绍了优化器的优化过程、优化器对SQL语句的处理方式，在此基础上提出了索引创建、SQL语句书写的一般性原则，并就优化器正常工作的保证措施进行了讲解。本章在最后对常用数据库系统的优化器管理进行了简单介绍。

5.1 SQL语言概述

SQL是结构化查询语言（structured query language）的缩写，是关系数据库系统的标准语言。它提供给用户一种表示方法，说明要查询的结果特性，至于如何查询、以及查询结果的形式都由数据库系统来完成。这种语言由于其功能丰富、方便易学而受到用户的广泛欢迎，1986年由美国国家标准局（ANSI）及国际化标准组织（ISO）公布，作为关系数据库系统的标准语言。

目前广泛使用的SQL标准是1992年制定的SQL-92（简称为SQL2）。SQL3是最新的SQL语言标准，在SQL2的基础上增加了许多新的特征。SQL标准的制定使得几乎所有的数据库厂家都采用SQL语言作为其数据库语言，但各个数据库厂家又结合自身需要在SQL标准的基础上进行了扩充。

SQL名为结构化查询语言，实际功能包括数据定义、数据操纵和数据控制。具体来说，包含以下几部分内容：

（1）数据模式定义语言（data definition language，DDL）。用来定义、修改或者删除基本关系表、视图、索引、完整性约束等数据库对象。

（2）数据操纵语言（data manipulation language，DML）。用来查询、插入、删除或者修改数据表中的记录。

（3）嵌入式SQL语言：被嵌入程序设计语言中，执行SQL语句处理。

（4）权限管理：用来管理和控制对数据库对象的访问。

有关SQL语言的详细描述，可以参看其它的数据库书籍，这里不再进一步介绍。对具体的数据库系统，在使用时可以阅读该系统的相关资料，了解其SQL语言的具体使用方法。

5.2 优化器概述

用户以SQL语句的形式，向数据库系统发出请求。系统在接收到用户的SQL语句请求后，由优化器进行语法分析和优化，找出SQL语句的执行计划，然后按照此执行计划运行，最后将处理结果返回用户。

一个SQL语句可能有很多种等价的执行方式。优化器需要对所有的执行方式进行分析，选择费用最低的执行方式，作为该SQL语句最终的执行计划。

5.2.1 为什么要使用优化器

我们知道，数据的存储和管理经历了三个阶段：人工管理阶段、文件系统阶段和数据库系统阶段。在人工管理和文件系统阶段，应用程序和数据紧密结合，应用程序随着数据结构的改变而改变，从而导致数据的利用率不高，数据不易管理，数据一致性很难维护等问题。

而在数据库系统阶段，数据库系统采用三级模式结构，由外到内依次为：外模式、模式和内模式，在外模式和模式、模式和内模式之间建立映象关系。这样一来，如果模式发生改变，只需要改变外模式和模式之间的映象，外模式不需要改变。同样，如果内模式发生改变，需要改变的只是模式和内模式之间的映象。

这种多模式结构，保证了数据的物理和逻辑独立性，减少了数据冗余，提高了数据利用率。同时，也使用户可以使用统一的接口访问数据库中的数据，由数据库系统来决定数据的处理方式，用户不用、也不必了解数据在数据库如何存放，从而极大地方便了用户的使用。

数据库技术的发展经历了层次数据库系统、网状数据库系统、关系数据库系统三个阶段。层次数据库系统使用层次数据模型，系统对用户请求的处理，就是从整个数据的顶层开始查找，一直找到要处理的数据为止。整个数据模型的实现已经定义了数据的处理方式。

网状数据库系统使用网状数据模型，任何相互关联的数据之间都要建立联系，系统对用户请求的处理，只要根据这些联系找到要处理的数据就可以了。该数据模型的实现也指定了数据的处理方式。

和层次数据库系统、网状数据库系统不同，关系数据库系统使用关系数据模型，就是使用二维表格表示数据以及数据之间的联系。整个数据模型没有给定数据的处理方式。对用户请求的处理，必须由系统根据数据字典信息来决定其处理方式，这就是优化器的工作。

一个用户的SQL语句请求可能有很多种等价的执行方式，优化器要决定SQL语句的最终执行计划。然而可不可以由用户来决定SQL语句的执行计划呢？

答案是肯定的。但由于数据库中的数据处于不断地变动之中，对一个SQL语句，用户提供的执行计划，只能反映数据及表结构的当前情况。在经过一段时间的运行之后，由于数据量及表结构的变化，该执行计划就不再合适，甚至可能很糟糕。而优化器动态地、根据数据库当前统计信息所生成的执行计划，就能够有效地适应数据量及表结构的这种变化。对用户来说，也不用为指定SQL语句的执行计划而详细地去了解数据库对象的定义、数据的分布等相关信息。

5.2.2 优化器的处理过程

在第2.2.7一节中，我们已经对优化器的处理过程有所了解，这里我们将作进一步的介绍。

数据库系统对SQL语句的处理，要经过以下三个步骤：

（1）语法分析与翻译

（2）优化

（3）执行

其具体执行过程可见图5-1。其中，语法分析与翻译、优化过程由优化器进程完成，而SQL语句的执行由代理进程实现。对一个SQL语句，如果系统能够在内存中找到它的最终执行计划，就直接使用，不会再去分析与优化。

1. 语法分析与翻译

优化器对SQL语句的语法分析，就是构造该SQL语句的语法分析树表示，验证SQL语句是否存在语法错误、发出请求的用户是否有执行权限等。然后基于语法分析树，将SQL语句翻译成一个关系代数表达式。最后，对这个关系代数表达式使用等价转换规则，找出SQL语句所有等价的关系代数表达式，每一个关系代数表达式就对应SQL语句的一个执行方式。

2. 优化

对表中数据的访问，可以使用表扫描，也可以使用索引。在从多个表中返回结果时，要执行表的连接，可以采用嵌套循环连接，也可以使用排序归并连接、散列连接，并且表和表之间的连接顺序可以是任意的。SQL语句的一个执行方式，就是各种可能执行途径的一种组合和编排方式，明确指定了语句执行的访问路径、连接方式以及连接顺序等等。

SQL语句的不同执行计划，在性能、资源使用上可能会有非常大的差异。优化器对SQL语句的优化处理，就是要从SQL语句所有的执行方式中找出费用最低的一个，作为SQL语句最终的执行计划。而所谓SQL语句的执行费用，就是执行SQL语句时，所需执行时间、CPU使用、内存空间使用、I/O操作数量、生成的中间结果集数量等多方面因素的综合。其中由于I/O操作比较慢，是影响SQL语句运行的关键因素。一些系统的优化器就单纯以I/O操作的数量来衡量SQL语句的费用。

为了找出费用最低的执行计划，而将所有的执行计划都执行一遍，这是不可取、也是不现实的。优化器是根据数据字典中的相关信息，通过估算来决定每一个执行计划的执行费用，尽管这种估算有时候并不完全正确。

优化器对SQL语句的优化处理，由以下三个步骤完成：

（1）根据关系代数表达式，使用等价转换规则，找出所有等价的执行计划。

（2）依据数据字典中表和索引的结构定义和相关的统计信息，分别估算每一种执行计划的费用。

（3）比较所有执行计划的费用，费用最低的那一个就是SQL语句最终的执行计划。

3. 优化器要使用的数据字典信息

在数据字典中，被优化器参照的表和索引信息，可以分为两大类：结构定义、数据库对象统计信息。对表、索引的结构定义，具体来说会包括以下这些：

（1）表中的字段定义（类型，长度等）、字段的完整性定义、表上是否存在主键和外键、表上是否存在索引，等等。

（2）索引建立在那些字段上、索引中字段的顺序（对复合索引而言）、索引的类型（唯一索引、非唯一索引、聚集索引），等等。

而对表、索引的统计信息，用户可以根据自己的需要进行选择、收集。大体来说，有以下的内容：

（1）对一个表来说，可以统计：表中的字段个数、表中记录的长度、表中的记录数、表的块因子（即一个数据库的数据块可以存放的记录数）、表使用的数据页数，等等。

（2）对表中的单个字段来说，可以统计其取值的最大值、最小值、不同取值的个数。可以设定字段取值区间的个数、区间中不同取值的个数，进而可以统计其在每一个取值区间内所包含的记录个数。也可以将多个字段合并在一起进行统计，等等。

（3）对索引来说，可以统计：索引的高度、叶索引页的数目、索引中记录的长度、索引的块因子，等等。

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

优化器的整个估算过程是复杂、耗时的，特别是对一些复杂的SQL语句，如：包含多个查询条件、需要多个表之间的连接、需要产生中间结果集、需要分组和排序等，将耗费更多的时间和系统资源。数据库系统会采取一些措施，来减少优化器对SQL语句的估算、优化次数，这一点我们会在下面讲到。

5.2.3 了解优化器处理过程的意义

SQL语言是用户访问数据库数据的通用接口。要达到同样的目的，可以有很多种不同的SQL语句书写方式。不同的SQL语句，尽管会产生相同的结果，但其执行过程、执行效率将会有明显的区别。

例如，要从员工表employee中返回所有员工的姓名，使用下面的SQL语句：

SELECT *

FROM employee

优化器将选用表扫描作为执行方式，通过扫描整个表返回结果，不管员工表employee上是否存在索引。而使用下面的SQL语句：

SELECT empy_name

FROM employee

如果列empy_name上存在索引，优化器将选用索引扫描作为该语句的执行方式。这种方式被称为索引覆盖。由于索引中每条记录包含较少的字段，一个索引页包含更多的记录，使用索引扫描，将使用更少的磁盘I/O操作。

对程序员、数据库管理员来说，清楚优化器对SQL语句的优化处理过程，能够大体上判断出一个SQL语句的执行计划，不但有助于写出好的、效率高的SQL语句，而且能够找出引起系统性能瓶颈的SQL语句，从而保证系统的正常、稳定运行。

5.3 优化器对SQL语句的处理

尽管找出最小成本的执行计划是优化器的职责，但对于糟糕的SQL语句，优化器也无能为力。深入了解优化器对SQL语句的优化处理，能够帮助用户写出高效的SQL语句。

优化器在确定SQL语句最小代价的执行计划时，无外乎就是确定数据的访问路径（索引访问或者表扫描）、表连接的方式及顺序，以及索引不可用时是否进行排序等。下面我们就从这几个方面进行讨论。

5.3.1 排序处理

系统对SQL语句的处理可能需要进行排序。这主要是由于以下两个原因引起的：

（1）在SQL语句中，指定要对结果集进行排序，或者包含需要排序的关键字。

（2）进行表连接时，系统选用排序归并连接、散列连接；或者系统虽然选用嵌套循环连接，但发现作为输入的表如果已排序，其实现效率会更高。有关表连接的详细信息，可参见第5.3.2一节。

如果在要排序的字段上存在索引，系统就直接使用该索引，顺序读取表，从而完成排序。如果在要排序的字段上不存在索引，或者尽管存在索引，但按照索引访问需要更多的系统开销时，系统就不会使用索引，只是在最后，基于此字段对结果集进行排序。

如果要排序的数据，可以一次在内存中完成，这种排序就称为内排序。如果由于内存空间限制，无法一次完成数据的排序，系统就按照可用内存的空间大小，将要排序数据分成多个部分、分别排序，中间结果被存放在磁盘上，最后将多个已排序结果集合并，形成最终的结果集，这种排序称为外排序。

很显然，内排序的执行效率很高。如果数据库中存在排序操作，我们当然希望所有的排序均能够一次在内存中完成。

在SQL语句中出现下列的关键字后，如果优化器没有选用相关的索引，就一定会进行排序操作。

1. order by

该关键字要求对结果集进行排序。如果表中的某些字段经常在order by中使用，在这些字段上建立索引将有助于避免数据库中的排序操作。

2. group by

该关键字要求对结果集进行分组。系统对分组的处理，是首先基于要分组的字段进行排序，然后对已排序的记录数据，从上到下采取以下的处理步骤，形成结果集：

（1）取出第一条记录，放入结果集。

（2）取出下一条记录。如果该条记录和结果集中最后一条记录在要分组字段上取值相等，就将该条记录和最后一条记录合并；如果不相等，就将该记录放在结果集的最后面。

（3）继续处理，直到所有的记录被处理完成。

例如，下面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）取出下一条记录。如果该条记录和结果集中最后一条记录相同，就放弃该条记录；如果不相同，就将该记录放在结果集的最后面。

（3）继续处理，直到所有的记录被处理完成。

例如，下面SQL语句从员工表employee中返回所有的部门号：

SELECT distinct dept_no

FROM employee

系统首先读取表employee中的所有记录，基于字段dept_no进行排序。然后对已经排序的记录集，按照字段dept_no的值，删除重复的记录。

4. 集合操作：union、intersect、minus

集合操作：union、intersect、minus，用来实现了两个记录集之间的并、交、差运算，其中：

（1）union：合并两个记录集，结果集中不能有重复的记录。

（2）intersect：返回同时出现在两个记录集中的记录。

（3）minus：返回出现在第一个记录集，而不在第二个记录集的所有记录。

对于这些集合运算，系统首先将两个记录集基于相同的字段或者字段组合，分别按照相同的方向排序，然后将这两个已经排序的记录集合从上到下进行比较，从而找到最终的结果集。

这里，我们需要注意关键字union和union all的区别。union all允许结果集中有重复的记录，因此优化器不会要求对记录集进行排序。

5.3.2 表连接的处理

在SQL语句的查询处理中，可能需要从多个表中返回数据，这就需要表的连接。在表的连接时，系统需要考虑的问题有：

（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,department))

((department,employee),account)、(account,(department,employee))

((employee,account),department)、(department,(employee,account))

((account,employee),department)、(department,(account,employee))

此外，两个表之间的连接可以使用以下的连接方式：嵌套循环连接、排序合并连接、散列连接。对这个SQL语句，优化器应当选用那种表连接顺序、表和表之间使用什么样的连接方式呢？

我们知道，一个SQL语句有很多种不同的执行方式，这些表连接顺序、连接方式的不同编排和组合，就包含在不同的执行方式中。优化器通过估算获取的最终执行计划，就给出了该SQL语句最合适的表连接顺序和连接方式。对两个表以上的表连接，由于要产生中间结果集，优化器在估算时还要考虑它所消耗的内存、磁盘空间资源。

下面我们将对表和表之间的连接方式进行简单介绍。

1. 嵌套循环连接

嵌套循环连接，由两个嵌套的循环语句组成。处于外层循环中的表称为外层表，处于内层循环中的表称为内层表。系统顺序地读取外层表中的每一条记录，将它和内层表中的每一条记录进行比较，如果满足条件就放入结果集。整个连接的处理过程见图5-2。

嵌套循环连接不要求表中存在索引，并且不管是什么条件，该算法都可以使用。然而嵌套循环连接算法的代价很大，因为该算法要逐一检查两个表中的每一条记录。对外层表中任一记录的处理，都要对内层表扫描一次。如果内层表能够存放在内存中，将极大地减少磁盘的操作次数。优化器在使用嵌套循环连接时，会考虑表中的记录数，将记录数较小的表作为内层表使用。

由于嵌套循环连接的处理效率比较低，人们已经对此算法进行了改进，这就是：块嵌套循环连接和索引嵌套循环连接。

2. 块嵌套循环连接

对于块嵌套循环连接，系统以块的方式而不是以记录的方式处理表的连接（所谓块，就是数据库系统的数据页，是对磁盘I/O操作的最小单位）。系统顺序地读取外层表中的每一个数据块，将块中每一条记录和外层表所有块中的每一条记录进行比较，如果满足条件就放入结果集。整个连接的处理过程见图5-3。

在要连接的两个表都不能放入内存时，表连接的处理不可避免要不停地以块为单位进行磁盘的读写。使用块嵌套循环连接，将极大地减少对磁盘的I/O操作。

3. 索引嵌套循环连接

在嵌套循环连接中，如果内层表在连接属性（表的属性也就是表的字段）上存在索引，系统就可以使用此索引访问内层表，从而取消内层循环对内层表的扫描，这种方法就称为索引嵌套循环连接。

使用表上的索引进行嵌套循环连接，一般来说会好于表的扫描。优化器在优化处理时，如果选用了嵌套循环连接，一般会把连接属性上存在索引的表作为内层表来处理，从而可以提高SQL语句的处理性能。

如果内层表的连接属性上不存在索引，但是优化器经过估算后，发现在连接属性上建立临时索引，将会降低SQL语句的执行成本。在这种情况下，优化器会在该SQL语句的最终执行计划中，要求首先建立内层表连接属性上的索引，然后再进行索引嵌套循环连接。

4. 排序归并连接

将参与连接的两个表按照连接属性，分别在相同的方向上进行排序，然后将两个表排序后的记录从上到下，一一进行比较，将满足条件的记录存放到结果集中，这种连接方式就称为排序归并连接。

如果参与连接的表已经按照连接属性排序，则该表就不用排序。当两个表的连接属性上存在索引时，这些索引可能会在排序归并连接中被使用。

5. 散列连接

散列连接要使用散列函数。如果一个表中的记录与另一个表中的记录满足连接条件，那么它们在连接属性上有相同的取值、相同的散列值。基于此原理，散列连接的基本思想是使用散列函数，把两个表分别按照连接属性，划分成一系列有相同散列值的记录集合。然后把具有相同散列值的记录集合，进行排序归并连接，最后将所有的中间结果集进行合并，形成最后的结果集。

我们知道，对大数据量表的排序，由于无法将数据全部装入内存，不能在内存中完成排序操作，整个排序过程分阶段进行，需要磁盘空间存放排序的中间结果，降低了排序的效率。而且随着数据量的增长，排序的效率将大幅度下降。因此对大数据量表之间的连接，直接使用排序归并连接，处理的效率会很低。

相对于排序归并连接，散列连接首先将大数据量表划分成许多小表，这些小表的排序可以在内存中完成，然后再执行排序归并连接，其连接的效率会得到提高。因此散列连接适合于大数据量表之间的连接处理。

5.3.3 访问路径的选择

对表中数据的访问，可以使用表扫描，也可以使用索引访问。对一个SQL语句来说，究竟采用哪种数据访问路径，最终由优化器决定。

使用索引访问表中数据时，系统首先要将索引页读入内存，根据索引记录中的指针，找到数据所在表中的数据页，然后再将数据页读入内存，进而找到所需数据。使用索引访问表中的一条记录，至少需要两次的I/O操作。

优化器通过估算I/O操作的多少，来决定表的访问路径。对建有索引的表，优化器并不总是按照索引访问表中的数据。有时候使用索引找到所需数据，会比单纯的表扫描需要更多的I/O操作。在下列情况下，优化器会使用表扫描而不会选择索引，来作为数据的访问路径：

（1）表中的记录较少，只使用很少的数据页。

在这种情况下，优化器采用表扫描方式访问数据。依据索引访问，反而会增加磁盘的I/O操作。同时我们也可以看出，在这种小数据量的表上建立索引，对数据的查询不会有所帮助，反而影响到表的更新操作。

（2）查询语句需要返回大量的记录。

如果一个查询语句需要从表中返回大量的记录，例如：返回记录占表中总记录的30%以上，使用索引将需要更多的I/O操作。这时表扫描会是更好的数据访问路径。

（3）查询语句的条件选择中包含多个条件。

如果查询语句中的多个条件是或的关系，或者可以使用索引的查询条件不能有效地降低记录的数目，在这种情况下系统就使用一次表扫描，同时检查所有的查询条件。将多个条件分开、使用索引查询，再进行记录集合并，将需要更多的I/O操作。

（4）表上没有索引，或者有索引，但无法使用。

为经常需要访问的表建立索引。索引的创建，应结合应用程序，根据查询条件中字段的使用频度，决定在那些字段上建立索引。至于索引创建所需要遵循的一些原则，可见第5.6.1一节。

（5）SQL语句的写法存在问题。

至于如何写好SQL语句，我们将在第5.6.2一节中说明。

5.4 优化器优化过程的改进

优化器的优化过程需要时间，需要耗费CPU、内存等系统资源。特别是复杂的SQL语句，所需时间及耗费的系统资源会更多。对存在成千上万个执行计划的SQL语句，把每一个执行计划都进行估算是不可行的。数据库系统需要采取各种措施，减少需要估算的执行计划个数，从而降低优化处理对系统资源的消耗。

5.4.1 启发式优化方法

我们前面所讲的优化方法，是通过估算来决定执行计划的成本，是一种基于代价的优化方法，也是数据库系统中最经常被使用的一种优化方法。它的主要缺点是优化本身也需要时间、需要消耗资源。优化器可以使用的另外一种优化方法称为启发式优化。

启发式优化方法，是一种基于规则的优化方法，就是事先在数据库系统中定义一系列的规则，每条规则都有不同的优先级别，规定了优化器对SQL语句的处理方式。优化器根据这些规则，按照优先级的高低，对SQL语句进行关系代数的等价转换，从而找到SQL语句最终的执行计划。

由于这些事先定义的规则是启发式的，并不总是有效。因此启发式优化方法的主要缺点是优化器并不能总是找到最优的执行计划，有时甚至是非常糟糕的执行计划。作为对这种优化方法的说明，下面是两条经常被使用到的启发式规则：

（1）尽早执行选择运算

这条启发式规则，要求对SQL语句的执行，应尽可能早地使用查询条件，去掉不符合条件的记录，从而减少要处理的记录数目。体现在优化器的执行计划选择上，就是使用等价转换，将选择运算放置在执行计划的最优先位置上。

（2）尽早执行投影运算

这条启发式规则，要求对SQL语句的执行，应尽可能早地使用投影运算，从数据表中选择结果集所需要的字段。优先执行投影运算，特别是对要产生中间结果集的SQL语句，将会减少内存空间的使用数量。

5.4.2 减少优化处理的措施

对于一个复杂的查询，等价于给定SQL语句的不同执行计划会很多。一般而言，对于n个表的连接，有(2 (n – 1))! / (n – 1)!个不同连接顺序。当n = 3时，有12种不同的连接顺序；当n = 5时，有1680种不同的连接顺序。由此我们可以看出，对一个大的、复杂的SQL语句，要估算所有可能的执行计划，进而找出代价最小的那一个，将是不可能的事情。

数据库系统通常使用基于代价的优化方法。为了减少优化器的估算次数，减少对系统资源的消耗，数据库系统采取了许多措施。这些措施大体上包括以下这些：

（1）结合启发式规则，首先找到最可能具有最小代价的执行计划，然后估算其代价，与此同时排除最不可能的执行计划。

（2）在对其它执行计划进行估算时，可以使用以下技术进一步减少或者排除要处理的执行计划：

① 找多个表的最佳连接顺序时，不是简单地考虑所有可能的连接顺序，而是首先为多个表的每个子集找出最佳连接顺序。对一个子集来说，在所有包含这个子集的执行计划中，只有使用了这个子集最佳连接顺序的执行计划，才需要被估算，其余的执行计划都可以被排除。使用这种方法能够大大减少需要检查的连接顺序总数。

② 如果检查一个执行计划的某部分后，发现这一部分的执行代价已经比先前已检查过的整个执行计划的最小代价要大，则可以终止对这个执行计划的检查。

③ 如果发现一个子表达式的最小代价，比先前已检查过的整个执行计划的最小代价还大，则没有必要对包含该子表达式的任何执行计划进行检查。

（3）系统将SQL语句经优化后的执行计划存放在内存或者数据字典中。这样，该SQL语句再次被请求时，系统就直接使用，不再进行优化处理，这一点我们已经在第3.2.4一节中作了介绍。对程序员而言，应尽可能在应用程序之间做到SQL语句的共享，减少优化处理操作。

5.5 有关优化器的日常维护

优化器对SQL语句执行计划的成本估算，需要使用数据字典信息。其中的数据库对象统计信息反映了数据库中用户数据的使用和分布状况，这些信息的准确性决定了SQL语句最终执行计划的正确性。

数据库系统不会自动维护这些统计信息，对它的维护是数据库管理员的职责。

1. 定期执行统计信息的收集

数据库系统的运行，要不断地改变表、索引中的记录，表、索引的磁盘空间使用也不断地发生变化。在一段时间之后，现有统计信息已不能正确反映表、索引中的数据状况。优化器使用这些统计信息，就无法正确找出SQL语句的最小代价执行计划。因此数据库管理员应当定期收集表、索引的统计信息，以反映表、索引中数据的实际情况。

如果对表进行了大数据量的增加、删除或者更改操作，就应当立即进行统计信息的收集。

2. 决定需要进行统计信息收集的字段或者字段组合

一个表可能包含很多字段，一个索引也可能建立在多个字段上。是不是表、索引中的所有字段都需要统计信息的收集呢？

我们知道，优化器使用查询条件、表连接中字段及其它们的统计信息，决定SQL语句执行计划的访问路径（表扫描或者索引扫描）、表连接的方式和顺序。因此，我们只要对这些字段进行统计信息的收集，就能够保证优化器作出合理的选择。对其它字段的信息收集，是不必要的。

一般来说，我们应当结合应用程序，选择下列字段进行统计信息的收集：

（1）表中用作查询条件的字段。

（2）表中用作表连接的字段。

（3）索引所使用字段。

（4）如果表中多个字段经常一起出现在查询条件中，并且表中数据量巨大，那么可以将它们放在一起进行信息统计。

（5）如果索引是复合索引，并且索引中记录数很多，那么可以根据需要，将索引的第一个字段和其它字段中的一些进行组合，进行统计信息的收集。

在决定要进行信息收集的字段之后，接下来需要确定信息的收集方式和准确程度。对字段的信息收集，可以统计其取值的最大值、最小值、不同取值的个数；可以设定字段取值区间，统计每一个取值区间内所包含的记录个数，等等。 这些统计信息的准确程度与处理的数据量有关，统计可以基于整个表、索引中的数据，也可以只在部分采样数据上进行。如果使用数据采样，采样的数据量越大，信息越准确。

所有这些工作应当在应用系统开发完成后，根据系统的使用情况来决定。

3. 在空闲状态下进行统计信息的收集

优化器生成SQL语句的执行计划后，要存放在内存缓冲区中。当SQL语句再次被使用时，就可以直接使用，从而提高了SQL语句的执行速度。

然而，SQL语句的执行计划和数据字典信息密切相关。一旦对数据库系统进行了维护，修改了数据字典信息，如：更改了表和索引的结构、重新收集了统计信息，存放在内存缓冲区中、与此相关的执行计划将失效，优化器必须再次对SQL语句进行优化。

此外，在业务高峰期对系统进行维护，不但要花费大量时间、占用大量资源，而且由于锁的使用，影响了系统的并发能力，从而对业务系统的运行产生非常大的影响。

因此为了避免执行计划的失效以及对业务系统的影响，数据库管理员不要在业务高峰期对系统进行维护，维护工作可以在系统处于空闲状态下进行，如：下班后或者夜间。

5.6 基于优化器处理的实际应用

我们在上面讨论了优化器的处理过程以及对SQL语句的处理方式。基于对这些知识的掌握，用户就可以判断SQL语句执行效率的高低，就可以判断是否需要为表建立索引、索引又如何创建等等。下面我们将就如何创建索引、如何写好SQL语句等提出一些建议和看法。

5.6.1 索引的创建原则

基于合理的数据库设计，经过深思熟虑后为表建立索引，是获得高性能数据库系统的基础。而未经合理分析便添加索引，则会降低系统的总体性能。索引虽然说提高了数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。

是否要为表增加索引、索引建立在那些字段上，是创建索引前必须要考虑的问题。解决此问题的一个比较好的方法，就是分析应用程序的业务处理、数据使用，为经常被用作查询条件、或者被要求排序的字段建立索引。基于优化器对SQL语句的优化处理，我们在创建索引时可以遵循下面的一般性原则：

（1）为经常出现在关键字order by、group by、distinct后面的字段，建立索引。

在这些字段上建立索引，可以有效地避免排序操作。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

（2）在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。

（3）为经常用作查询选择的字段，建立索引。

（4）在经常用作表连接的属性上，建立索引。

（5）考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

除了以上原则，在创建索引时，我们还应当注意以下的限制：

（1）限制表上的索引数目。

对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

（2）不要在有大量相同取值的字段上，建立索引。

在这样的字段（例如：性别）上建立索引，字段作为选择条件时将返回大量满足条件的记录，优化器不会使用该索引作为访问路径。

（3）避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。

由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。

（4）对复合索引，按照字段在查询条件中出现的频度建立索引。

在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。

因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

（5）删除不再使用，或者很少被使用的索引。

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

5.6.2 如何写好SQL语句

程序员在编写SQL语句时，不能仅仅着眼于它的正确性。SQL语句的执行效率，决定了数据库系统的性能，影响着应用系统的正常运行。基于优化器对SQL语句的优化处理，为了编写出高效的SQL语句，我们应当遵循下面的原则：

1. 不要返回不需要的字段数据

在SQL语句中返回不需要的数据，将浪费系统资源，导致性能的降低。这主要体现在：

（1）增加磁盘的I/O操作。

（2）将结果返回给客户端，增加了网络流量。

（3）增加了表锁定时间，影响了其它用户的访问，损害了系统的并发能力。

此外，返回不需要的字段数据还可能影响到优化器的优化处理。如本章第5.2.3一节所述，不需要的字段数据导致优化器使用表扫描，而不是索引覆盖。

为避免返回不需要的字段数据，在查询语句中，尽可能将结果集中需要的字段列出来，不要笼统地使用“*”代替。

2. 避免写一些复杂的SQL语句

对于复杂的SQL语句，优化器将花费很长的时间进行优化处理，而它的执行计划也很难在多个应用程序之间共享。这样的SQL语句会严重影响系统性能，在应用程序中应力求避免。

我们可以将复杂的SQL语句划分成多个简单语句，分开处理。如果必要的话，可以考虑使用临时表存放中间结果。

3. 书写SQL语句要规范

按照一定的规范书写SQL语句，主要是希望SQL语句能够在多个应用程序之间实现共享。这样一来，SQL语句的执行计划就可以为多个应用程序所使用，从而减少优化器的优化处理。

4. 避免在查询条件中对字段使用函数或者进行计算

数据库中的索引，按照根页、分支页、叶页的层次分级存放。使用索引查找键值时，数据库系统从根页开始，通过键值的匹配和比较，快速地找到键值所在的叶页，进而找到表中的数据页。

如果在查询条件中对字段使用函数或者进行计算，系统就无法执行键值的匹配和比较。即使这些字段上存在索引，系统也不会使用索引进行数据的访问。

要尽可能使用索引而不是表扫描访问数据，就不能在查询条件中对字段使用函数或者进行计算。其实绝大多数情况下，我们通过改写查询条件，完全可以做到这一点。例如下面的这些例子：

（1）要求字段取值最前面的一个字符为“m”：substring(col1,1,1) = ‘m’

可以替换为：col1 like ‘m%’

（2）对字段取值进行数值计算：col1 –1 = 5

可以替换为：col1 = 5 –1

（3）对字段取值小写转换后进行比较：lower(col1) = ‘tom’

可以替换为：col1 = ‘tom’ or col1 = ‘TOM’

（4）对字段取值进行否定操作：not col1 > 5

可以替换为：col1 <=5

（5）要求字段取值在2007年之前：year(col1) < 2007

可以替换为：col1 < '2007-01-01'

需要说明的是，由于索引可能在应用系统投入使用之后，根据需要进行添加，因此在应用程序开发时，就应当规范查询条件的写法，尽可能将字段和数值、处理函数分开放置在表达式的两边。

5. 只有必要时，才使用引起排序的关键字

用户对数据结果有什么要求，系统无法知道。它就是根据SQL语句本身，决定最终执行计划、返回结果。因此，只有在必要时，用户才应该在SQL语句中使用引起排序的关键字。这样可以有效地避免系统中出现不必要的排序处理。

我们知道，distinct、union关键字要求结果集中不能有重复的记录，order by关键字要求排序结果集，这些都可能引起系统的排序处理。如果用户不要求对结果集进行排序，允许重复记录的存在，就可以在SQL语句中：

（1）不使用distinct、order by关键字。

（2）使用union all代替union。

另外，即使结果集被要求排序或者不能有重复记录，但用户知道：

（1）结果集已经排序。在SQL语句中，就可以不使用order by关键字。

（2）不会出现重复的记录。在SQL语句中，就可以不使用distinct关键字，可以使用union all代替union。

6. 使用top、first等关键字，返回部分数据

如果查询只是找到满足条件的一条或者部分记录即可，并不要求返回所有满足条件的记录。在这种情况下，可以在SQL语句中，使用top、first关键字（对不同的数据库系统，这些关键字可能并不相同），优化器将使用最快的执行计划，在找到所需的记录后，就立即中断该操作，从而节省了系统资源。

7. 在连接属性、查询条件中，使用相同数据类型的字段

两个表中用作连接属性或者需要进行比较的字段，如果具有不兼容的数据类型，数据库系统在处理时就自动使用函数，实现数据类型的转换；即使两个字段具有兼容的数据类型（例如：整数型和浮点型），由于在计算机中存储方式的不同，数据库系统仍旧需要进行内部的处理。

在以上的情况下，数据库系统就不会使用索引。为了避免这种情况的发生，在数据库设计时，就应当根据应用程序的使用，使两个表中用作连接属性或者需要进行比较的字段，具有相同的数据类型。

8. 选择合适的操作符

在SQL语句中，尽可能避免使用下列这些操作符：

“!=”、“< >”、“!>”、“!<”、“not”、“not exists”、“not in”、“not like”

由于这些操作符均使用了否定操作，系统不知道满足条件的键值究竟在什么区间之内，从而无法使用索引。在此情况下，系统只能使用表扫描，逐条对每一记录进行检查、比较。

其实大多数情况下，这些否定操作可以被替换。我们可以使用下列操作符或者它们的组合，代替否定操作：

“>”、“>=”、“<”、“<=”、“like”、“in”

5.6.3 优化器的优化控制

优化器根据数据字典信息，通过估算找出SQL语句费用最低的执行计划。这种估算方式，并不能保证优化器找出的执行计划都是最优的。在下列情况下，优化器选择的执行计划可能会很糟糕：

（1）SQL语句非常复杂

（2）SQL语句的书写存在问题

（3）数据字典中没有足够的统计信息

为了避免这种情况的发生，我们希望通过一些设置，影响优化器的动作，进而限制执行计划的选择。毕竟用户对数据库中表、索引的结构非常了解，大体上知道SQL语句应当怎样被执行。这可以通过在SQL语句中加入指示信息（directive）来实现。

加入SQL语句的指示信息并不是SQL语句的一部分，它们相当于一些注释，告诉优化器应当采用什么样的动作、作出什么样的选择。具体来说，一些常用的、比较关键的指示信息有以下这些：

（1）是否使用表扫描

（2）是否使用索引以及使用那一个索引

（3）设定表的连接顺序以及连接方式

例如，可以使用下列SQL语句，从员工表employee中返回所有员工的姓名：

SELECT empy_name

FROM employee

如果用户希望优化器通过员工表索引empy_ind进行数据访问，可以在SQL语句中加入index指示。

SELECT --+index(empy_ind) empy_name

FROM employee

又如，可以使用下列SQL语句，返回月工资超过5000的所有员工姓名及所在部门：

SELECT a.empy_name,b.dept_name

FROM employee a, department b

WHERE a.dept_no = b.dept_no AND a.salary > 5000

用户可以在SQL语句中加入avoid_full指示，希望优化器不要使用表扫描访问员工表employee；加入ordered指示，让优化器根据员工表employee、部门表department在SQL语句中的顺序执行表的连接：

SELECT --+ordered, avoid_full(a) a.empy_name,b.dept_name

FROM employee a, department b

WHERE a.dept_no = b.dept_no AND a.salary > 5000

SQL语句中的指示信息，在影响优化器处理过程的同时，降低了优化处理所需要的工作量，是一种值得尝试的有用工具。然而对用户来说，在SQL语句中使用指示信息，除了增加工作量、要求对表和索引的结构以及表中的数据存放有深入的了解之外，还要求深入掌握系统对SQL语句的处理和执行方式，使用起来有相当大的难度。

需要说明的是，指示信息并不总是能带来好的结果，错误的指示信息只会使优化器做出错误的选择，进而影响SQL语句的执行。另外，在对表进行大量的数据处理、表中的数据存放已经发生了较大的变化之后，可能需要更改指示信息，以调整SQL语句的执行方式。

一般来说，用户应当了解SQL语句的指示信息，但不建议直接在SQL语句中使用。用户可以通过简化和优化SQL语句、定期地维护以及收集统计信息等更方便、更有效的方式，避免优化器作出不正确的选择。

5.6.4 SQL语句的执行计划分析

尽管SQL语句的执行计划由优化器生成，并交给系统执行，但用户可以对SQL语句的执行计划进行查看和分析。

每一个数据库系统都会提供一些工具，用来跟踪优化器的优化过程，显示SQL语句的执行计划。用户通过对这个过程的跟踪、对SQL语句最终执行计划的分析，可以解决许多系统中的问题：

（1）在编写完SQL语句时，通过显示它的执行计划，发现SQL语句书写上存在的问题，决定是否需要调整数据的结构定义。

（2）在系统运行过程中，对消耗大量资源的SQL语句进行分析，发现系统运行瓶颈，决定是否要进行统计信息的收集、数据存储的整理等等。

5.7 常用数据库系统的优化器管理

每一个关系数据库系统都有自己的优化器，用来生成SQL语句的执行计划。这些优化器都采用基于代价的优化方法。下面我们就常用数据库系统的优化器管理进行简单介绍。

为了帮助用户分析SQL语句的处理过程，数据库系统都会提供一些工具，可以显示和分析SQL语句的执行计划。我们这里不对这些工具的使用和描述进行介绍，感兴趣的用户可以参看具体数据库系统的相关文档。

5.7.1 DB2数据库系统

DB2系统使用基于代价的优化方法。在对一个SQL语句的所有执行计划进行费用估算时，根据数据库中的统计信息、系统参数配置，估算每一个执行计划的I/O处理次数、CPU工作量、需要的内存空间等资源。除此之外，还要结合计算机中CPU的个数和处理速度、网络通讯带宽等相关硬件资源，最终决定每一个执行计划的执行费用。

DB2系统的优化器功能相当强大，但也要消耗更多的系统资源。为了降低系统运行过程中优化器优化处理所消耗的系统资源，系统在应用程序编译时将生成程序中静态SQL语句的执行计划，并存放在数据字典中。这样，应用程序在运行时就可以直接使用这些已经被生成的执行计划，不需要重新优化。然而需要注意的是，如果用户更改了数据库结构、重新收集了数据库对象的统计信息，就需要让系统重新生成应用程序中SQL语句的执行计划。

对和优化器有关的系统配置，可以参看第2.6.1一节。

5.7.2 ORACLE数据库系统

ORACLE系统支持基于代价和基于规则的优化方法。早期的ORACLE系统版本，只提供基于规则的优化方法。现有系统版本对基于规则优化方法的支持，是为了兼容以前的应用系统。随着基于代价优化方法的功能完善和增强，ORACLE系统最终将会完全放弃基于规则优化方法。

ORACLE系统基于代价的优化方法，也和其它数据库系统一样，根据数据库中的统计信息、系统参数配置，估算一个SQL语句所有执行计划在I/O、CPU、内存等系统资源上的使用，进而计算出执行费用，找出最小费用的执行计划。

对和优化器有关的系统配置，可以参看第2.6.2一节。

5.7.3 INFORMIX数据库系统

INFORMIX系统使用基于代价的优化方法。对一个SQL语句，优化器从以下几个方面来评估所有执行计划的费用：

（1）需要进行的I/O处理次数

（2）找出满足条件的所有记录所需要的CPU工作量

（3）排序和分组数据所要求的资源

（4）查询处理可以使用的内存数量

在具体评估时，系统根据数据库中的统计信息、系统参数配置，找出每一个方面的估计值，然后依据系统设置好的权重，估算出执行计划的费用。

对和优化器有关的系统配置，可以参看第2.6.3一节。

5.7.4 SYBASE数据库系统

SYBASE系统使用基于代价的优化方法。由于I/O处理往往是系统的运行瓶颈，SYBASE系统优化器就使用I/O处理的多少来估算执行计划的费用。对SQL语句的一个执行计划，优化器估算它需要的物理I/O和逻辑I/O次数（物理I/O就是从磁盘读数据到内存中进行处理，而逻辑I/O是从内存中找到所需数据）。对物理I/O和逻辑I/O，系统分别设置其权重为18、2。这样一个执行计划的费用就可以通过以下公式计算出来：

执行费用 = 物理I/O次数 * 18 + 逻辑I/O次数 * 2

相对来说，SYBASE系统的费用估算算法有些简单，但也有效地降低了优化操作本身所花费的系统资源。

对和优化器有关的系统配置，可以参看第2.6.4一节。

5.8 本章小结

SQL语言是用户访问数据库数据的通用接口，用户以SQL语句的形式，向数据库系统发出请求。数据库系统在接收到用户的SQL请求后，由优化器进行语法分析和优化，从所有可能、等价的执行方式中，找出最终的执行计划，然后按照此执行计划运行，最后将处理结果返回用户。

数据的物理和逻辑独立性、数据的不断更新和变化，决定SQL语句的最终执行计划由优化器来完成。一个SQL语句的最终执行计划，是所有执行方式中执行费用最低的那一个。所谓SQL语句的执行费用，就是执行SQL语句时，所需执行时间、CPU使用、内存空间使用、I/O操作数量、生成的中间结果集数量等多方面因素的综合。

为了找出费用最低的执行计划，优化器根据数据字典中的相关信息，通过估算SQL语句每一个执行计划的费用，然后进行比较而得出。优化器所参照的数据字典信息，可以分为两大类：结构定义、数据库对象统计信息，用户可以根据自己的需要选择要收集的表、索引统计信息。

优化器选择SQL语句的执行计划，无外乎就是确定数据的访问路径（索引访问或者表扫描）、表连接的方式及顺序，以及索引不可用时是否进行排序等。SQL语句中的下列关键字会引起排序操作：order by、group by、distinct、union、intersect、minus。

用户应根据自己的需要，决定是否使用这些关键字。对多个表之间的关联操作，优化器需要决定表之间的连接顺序和连接方式。两个表之间的连接，可以使用的连接方式有：嵌套循环连接、排序合并连接、散列连接。

基于对优化器处理过程和方式的了解，用户应能够决定是否要为表增加索引、索引应建立在那些字段上，应能够编写出高效的SQL语句，本章就这些方面指出了一些指导性原则。用户可以在系统运行过程中通过检查SQL语句的执行计划来发现系统存在的问题。

为降低优化器自身对系统资源的消耗，数据库系统会采取一些措施，如：启发式优化方法、将SQL语句的最终执行计划存放在内存中等，减少优化器的优化次数。为保证优化器的正常运行，数据库管理员应当遵照以下的原则：定期执行统计信息的收集、在系统空闲状态下进行系统的维护。

常见的数据库系统都使用基于代价的优化方法，现有ORACLE系统版本还支持基于规则的优化方法。相对来说，DB2系统的优化器功能比较强大，而SYBASE系统的费用估算算法有些简单。