第16章 利用索引改善性能

第17章 改善数据库性能

第16章 利用索引改善性能

本章的重点包括：

索引如何工作

如何创建索引

不同类型的索引

何时使用索引

何时不使用索引

本章介绍如何通过创建和使用索引来改善 SQL语句的性能，首先介绍 CREATE INDEX命令，然后介绍如何使用表里的索引。

16.1 什么是索引

简单来说，索引就是一个指针，指向表里的数据。数据库里的索引与图书中的索引十分类似。举例来说，如果想查阅书中关于某个主题的内容，我们首先会查看索引，其中会以字母顺序列出全部主题，告诉我们一个或多个特定的书页号码。索引在数据库里也起到这样的作用，指向数据在表里的准确物理位置。实际上，我们被引导到数据在数据库底层文件里的位置，但从表面上来看，我们是在引用一个表。

在查找信息时，逐页寻找快呢，还是查看索引来了解准确页码快呢？当然，使用索引是最有效的方法。当书很厚时，这样做会节省大量时间。假设书只有几页，那么直接查找信息可能会比先看索引再返回到某页更快一些。当数据库没有索引时，它所进行的操作通常被称为全表扫描，就像是逐页翻看一本书。关于全表扫描的具体介绍请见第17章。

索引通常与相应的表是分开保存的，其主要目的是提高数据检索的性能。索引的创建与删除不会影响到数据本身，但会影响数据检索的速度。索引也会占据物理存储空间，而且可能会比表本身还大。因此在考虑数据库的存储空间时，需要考虑索引要占用的空间。

16.2 索引是如何工作的

索引在创建之后，用于记录与被索引字段相关联的位置值。当表里添加新数据时，索引里也会添加新项。当数据库执行查询，而且WHERE条件里指定的字段已经设置了索引时，数据库会首先在索引里搜索WHERE子句里指定的值。如果在索引里找到了这个值，索引就可以返回被搜索数据在表里的实际位置。图16.1展示了索引的工作过程。

假设执行了如下查询：

图16.1 使用索引访问表

如图16.1所示，这里引用了索引NAME来寻找‘SMITH’的位置；在找到了位置之后，数据就能迅速地从表里检索出来。在索引里，数据（本例中是姓名）是按字母顺序排序的。

注意：索引的不同创建方式

在某些实现里，可以在创建表的过程中创建索引。但大多数实现提供了一个单独的命令来创建索引，其详细语法请参考具体的文档。

如果表里没有索引，在执行同样这个查询时，数据库就会进行全表扫描，也就是说表里的每行数据都会被读取来获取NAME字段等于‘SMITH’的记录。

索引通常以一种树形结构保存信息，因此速度比较快。假设我们对一个书名列表设置了索引，这个索引具有一个根节点，也就是每个查询的起始点。根节点具有分支，在本例中可以有两个分支，一个代表字母A到L，另一个代表字母M到Z。如果要查询以字母M开头的书名，我们就会从根节点进入索引，并且立即转到包含字母M到Z的分支。这种方式可以消除大约一半的可能性，从而用更短的时间找到准确的书名。

16.3 CREATE INDEX命令

像SQL里的其他语句一样，创建索引的语句在不同关系型数据库实现里也是不同的，大多数实现使用CREATE INDEX语句：

不同厂商的CREATE INDEX语句在选项方面有不少差别，有些实现允许指定存储子句（像CREATE TABLE语句）、允许排序（DESC||ASC）、允许使用簇。详细语法请查看具体实现的文档。

16.4 索引的类型

数据库里的表可以创建多种类型的索引，它们的目标是一样的：通过提高数据检索速度来改善数据库性能。本章介绍单字段索引、组合索引和唯一索引。

16.4.1 单字段索引

提示：最有效的单字段索引

如果某个字段经常在WHERE子句作为单独的查询条件，它的单字段索引是最有效的。适合作为单字段索引的值有个人标识号码、序列号或系统指派的键值。

对单个字段的索引是索引中最简单、最常见的形式。显然，单字段索引是基于一个字段创建的，其基本语法如下所示：

举例来说，如果想对表EMPLOYEE_TBL里雇员的姓创建索引，相应的命令如下所示：

16.4.2 唯一索引

唯一索引用于改善性能和保证数据完整性。唯一索引不允许表里具有重复值，除此之外，它与普通索引的功能一样。其语法如下所示：

如果想对表EMPLOYEE_TBL里雇员的姓创建唯一索引，相应的命令如下所示：

这个索引唯一需要注意的问题是，表EMPLOYEE_TBL里每个人的姓都必须是唯一的，这通常是不现实的。但是，像个人社会保险号码这样的字段可以设置为唯一索引，因为每个人的这个号码都是唯一的。

有人也许会问，如果雇员的社会保险号码是表的主键，那应该怎么办呢？当我们定义表的主键时，一个默认的索引就会被创建。但是，公司会使用自己编制的号码作为雇员ID，同时使用雇员的SSN用于纳税。通常我们会对这个字段设置索引，确保它在每条记录里都具有唯一的值。

对于类似索引这种对象，一个比较可取的方法是，在创建数据库结构的同时，基于空白表来创建索引。这样做可以确保后续输入的数据完全满足用户的要求。如果要在既有数据中创建索引，就必须进行相应的分析工作，来确定是否需要调整数据以便符合索引的要求。

16.4.3 组合索引

组合索引是基于一个表里两个或多个字段的索引。在创建组合索引时，我们要考虑性能的问题，因为字段在索引里的次序对数据检索速度有很大的影响。一般来说，最具有限制的值应该排在前面，从而得到最好的性能。但是，总是会在查询里指定的字段应该放在首位。组合索引的语法如下所示：

组合索引的范例如下所示：

在这个范例里，我们基于表ORDERS_TBL里的两个字段（CUST_ID和PROD_ID）创建组合索引。这是因为我们认为这两个字段经常会在查询的WHERE子句里联合使用。

注意：唯一索引的相关规则

唯一索引只能用于在表里没有重复值的字段。换句话说，如果现有表已经包含被索引关键字的记录，就不能再对它创建唯一索引了。此外，允许NULL值的字段上也不能创建唯一索引。如果不满足上述规则，那么创建语句就无法运行成功。

在选择是使用单字段索引还是组合索引时，要考虑在查询的WHERE子句里最经常使用什么字段。如果经常只使用一个字段，单字段索引就是最适合的；如果经常使用两个或多个字段，组合索引就是最好的索引。

16.4.4 隐含索引

隐含索引是数据库服务程序在创建对象时自动创建的。比如，数据库会为主键约束和唯一性约束自动创建索引。

为什么给这些约束自动创建索引？从一个数据库服务程序的角度来看，当用户向数据库添加一个新产品时，产品标识是表里的主键，表示它必须是唯一值。为了有效地检查新值在数以百计甚至是数以千计的记录里是唯一的，表里的产品标识必须被索引。因此，在创建主键或唯一性约束时，数据库会自动为它们创建索引。

提示：最有效的组合索引

对于经常在查询的WHERE子句里共同使用的字段，组合索引是最有效的。

16.5 何时考虑使用索引

唯一索引隐含地与主键共同实现主键的功能。外键经常用于与父表的结合，所以也适合设置索引。一般来说，大多数用于表结合的字段都应该设置索引。

经常在ORDER BY和GROUP BY里引用的字段也应该考虑设置索引。举例来说，如果根据个人姓名进行排序，对姓名字段设置索引会大有好处。它会对每个姓名自动按字母顺序排序，简化了实际的排序操作，提高了输出结果的速度。

另外，具有大量唯一值的字段，或是在WHERE子句里会返回很小部分记录的字段，都可以考虑设置索引。这主要是为了测试或避免错误。就像代码和数据库结构在投入使用之前需要反复进行测试一样，索引也是如此。我们应该用一些时间来尝试不同的索引组合、没有索引、单字段索引和组合索引。索引的使用没有什么固定的规则，需要对表的关系、查询和事务需求、数据本身有透彻的了解才能最有效地使用索引。

16.6 何时应该避免使用索引

注意：要有事先规划

表和索引都应该进行事先的规划。不要认为使用索引就能解决所有的性能问题，索引可能根本不会改善性能（甚至可能降低性能）而只是占据磁盘空间。

虽然使用索引的初衷是提高数据库性能，但有时也要避免使用它们。下面是使用索引的方针。

索引不应该用于小规模的表。因为查询索引会增加额外的查询时间。对于小规模的表，让搜索发动机进行全表搜索，往往比先查询索引的速度更快。

当字段用于WHERE子句作为过滤器会返回表里的大部分记录时，该字段就不适合设置索引。举例来说，图书里的索引不会包括像the或and这样的单词。

经常会被批量更新的表可以具有索引，但批量操作的性能会由于索引而降低。对于经常会被加载或批量操作的表来说，可以在执行批量操作之前去除索引，在完成操作之后再重新创建索引。这是因为当表里插入数据时，索引也会被更新，从而增加了额外的开销。

不应该对包含大量NULL值的字段设置索引。索引对在不同记录中包含不同数据的字段特别有效。字段中过多的NULL值会严重影响索引的运行效率。

经常被操作的字段不应该设置索引，因为对索引的维护会变得很繁重。

从图16.2可以看出，像性别这样的字段设置索引就没有什么好处。举例来说，向数据库提交如下查询：

从图16.2 可以看出，在运行上述这个查询时，表与索引之间有一个持续的行为。由于WHERE GENDER = ‘FEMALE’（或‘MALE’）子句会返回大量记录，数据库服务程序必须持续地读取索引、然后读取表的内容、再读取索引、再读取表，如此反复。在这个范例里，由于表里的大部分数据肯定是要被读取的，所以使用全表扫描可能会效率更高。

图16.2 低效索引的例子

警告：索引也会带来运行问题

对于特别长的关键字创建索引时要十分谨慎，因为大量I/O开销会不可避免地降低数据库性能。

一般来说，当字段作为查询里的条件会返回表里的大部分数据时，我们不会对它设置索引。换句话说，不要对像性别这样只包含很少不同值的字段设置索引。这通常被称为字段的基数，或数据的唯一性。高基数意味着很高的唯一性，比如像身份号码这样的数据。低基数的唯一性不高，比如像性别这样的字段。

16.7 修改索引

创建索引后，也可以对其进行修改。其语法结构与CREATE INDEX类似。能够修改的内容在不同的数据库实现中有所不同，但基本上修改的都是字段、顺序等内容。其语法如下所示：

对生产系统进行修改时需要特别小心。大部分情况下，对索引进行的修改操作会被马上执行，引起系统资源的额外消耗。此外，大部分数据库实现在进行索引修改的时候无法进行查询操作，从而会对系统的运行产生影响。

16.8 删除索引

删除索引的方法相当简单，具体语法请参考相应的文档，但大多数实现使用DROP命令。在删除索引时要谨慎，因为性能可能会严重降低（或提高！）。其语法如下所示：

MySQL中的语法结构稍有不同，需要同时指定创建索引的表格：

删除索引的最常见原因是尝试改善性能。记住，在删除索引之后，我们还可以重新创建它。有时重建索引是为了减少碎片。在探索如何让数据库具有最佳性能时，调整索引是个必要的过程，其中可能包括创建索引、删除它、最后再重新创建它（经过修改或不修改）。

提示：小心使用索引

索引对于提高性能大有帮助，但在有些情况下也会降低性能。我们应该避免对只包含很少不同值的字段创建索引，比如性别、州名等。

注意：删除索引的语法差异

MySQL使用ALTER TABLE命令删除索引。也可以使用DROP INDEX命令， MySQL会将其映射为适当的ALTER TABLE命令。再次提醒，不同的SQL实现在语法方面可能会有所不同，特别是在处理索引和数据存储的时候。

16.9 小结

索引可以用于改善查询和事务的整体性能。数据库索引（有点像图书里的索引）可以迅速地从表里引用特定的数据。创建索引的最常用方法是使用CREATE INDEX命令。在不同的实现里有多种不同类型的索引，包括单字段索引、唯一索引和组合索引。在判断使用什么类型的索引时需要考虑多方面的因素，才能让它最好地满足数据库的需要。有效地使用索引通常需要有一定的经验、全面了解表的关系和数据，以及一点实践，设置索引时的一点点耐心可能会为以后的工作节约几分钟、几小时，甚至几天的时间。

16.10 问与答

问：索引是否像表一样占据实际的空间？

答：是的。索引在数据库里占据物理空间。实际上，索引可能比所在的表更大。

问：如果为了让批处理工作更快地完成而删除了索引，需要多长时间才能重新创建索引？

答：这取决于多个因素，比如索引的大小、CPU利用率和计算机的性能。

问：全部索引都必须是唯一索引吗？

答：不是。唯一索引不允许存在重复值，而在表里有时是需要有重复值的。

16.11 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

16.11.1 测验

1．使用索引的主要缺点是什么？

2．组合索引里的字段顺序为什么很重要？

3．具有大量NULL值的字段是否应该设置索引？

4．索引的主要作用是去除表里的重复数据吗？

5．判断正误：使用组合索引主要是为了在索引里使用汇聚函数。

6．基数是什么含义？什么样的字段可以被看作是高基数的？

16.11.2 练习

1．判断在下列情况下是否应该使用索引，如果是，请选择索引的类型。

a．字段很多，但表的规模相对较小。

b．中等规模的表，不允许有重复值。

c．多个字段，大规模的表，多个字段用在WHERE子句作为过滤器。

d．大规模表，很多字段，大量数据操作。

2．编写 SQL 语句，为表 EMPLOYEE_PAY_TBL 的 POSITION 字段创建名为 EP_POSITION的索引。

3．修改练习2所创建的索引，将其变成唯一索引。要为SALARY字段创建唯一索引，需要做些什么？编写并依次运行这些命令。

4．研究本书里使用的表，根据用户可能对表进行的检索方式，判断哪些字段适合设置索引。

5．在表 ORDERS_TBL 上创建一个多字段索引，包含下列字段：CUST_ID、PROD_ID和ORD_DATE。

6．在表里创建其他一些索引。

第17章 改善数据库性能

本章的重点包括：

什么是SQL语句调整

数据库调整与SQL语句调整

格式化SQL语句

适当地结合表

最严格的条件

全表扫描

使用索引

避免使用OR和HAVING

避免大规模排序操作

本章介绍如何使用一些非常简单的方法调整SQL语句来获得最好的性能。

17.1 什么是SQL语句调整

SQL语句调整是优化生成SQL语句的过程，从而以最有效和最高效的方式获得结果。首先是查询里元素的基本安排，因为简单的格式化过程就能够在语句优化中发挥很大作用。

SQL语句调整主要涉及调整语句的FROM和WHERE子句，因为数据库服务程序主要根据这两个子句执行查询。前面的课程已经介绍了FROM和WHERE子句的基础知识，现在就来介绍如何细致地调整它们来获得更好的结果，让用户更加满意。

17.2 数据库调整与SQL语句调整

在继续介绍SQL语句调整之前，先要理解数据库调整与SQL语句调整之间的差别。

数据库调整是调整实际数据库的过程，包括分配内存、磁盘、CPU、I/O 和底层数据库进程，还涉及数据库结构本身的管理与操作，比如表和索引的设计与布局。另外，数据库调整通常会包括调整数据库体系来优化硬件的使用。实际上，在调整数据库时还要考虑其他很多因素，但这些任务通常是由数据库管理员（DBA）与系统管理员合作完成的。数据库调整的目标是确保数据库的设计能够最好地满足用户对数据库操作的需要。

SQL调整是调整访问数据库的SQL语句，这些语句包括数据库查询和事务操作，比如插入、更新和删除。SQL语句调整的目标是利用数据库和系统资源、索引，针对数据库的当前状态进行最有效的访问，从而减少对数据库执行查询所需的开销。

注意：两种调整缺一不可

为了在访问数据库时达到优化结果，数据库调整和SQL语句调整都需要进行。一个调整很差的数据库会极大地抵消SQL调整所付出的努力，反之亦然。在理想状态下，最好首先调整数据库，确保必要的字段都具有索引，然后再调整SQL代码。

17.3 格式化SQL语句

格式化SQL语句听上去是个很显然的事情，但也值得一提。一个新手在构造SQL语句时很可能会忽略很多方面，下面的小节将进行讨论，它们有些是很明显的，有些则不是。

为提高可读性格式化SQL语句。

FROM子句里表的顺序。

最严格条件在WHERE子句里的位置。

结合条件在WHERE子句里的位置。

17.3.1 为提高可读性格式化SQL语句

注意：一切以最优化为目的

大多数关系型数据库实现里有一个名为“SQL 优化器”的东西，它可以执行SQL语句，并且基于SQL语句的构成方式和数据库里可用的索引来判断执行语句的最佳方式。这些优化器并不是都相同，具体情况请查看相应的文档，或是联系数据库管理员来了解优化器如何读取SQL代码。理解优化器的工作方式有助于有效地调整SQL语句。

为提高可读性格式化SQL语句是件很显然的事情，但很多SQL语句的书写方式并不那么整洁。虽然语句的整洁程度并不会影响实际的性能（数据库并不关心语句的外观是否整洁），但仔细地使用格式是调整语句的第一步。当我们以调整的眼光看待一个SQL语句时，让它具有很好的可读性总是首先要考虑的。如果语句很难看清，又如何能够判断它是否正确呢？

让语句具有良好可读性的基本规则如下所示。

每个子句都以新行开始。举例来说，让FROM子句位于与SELECT子句不同的行里，让WHERE子句位于与FROM子句不同的行里，以此类推。

当子句里的参数超过一行长度需要换行时，利用制表符（TAB）或空格来形成缩进。

以一致的方式使用制表符和空格。

当语句里使用多个表时，使用表的别名。在这种语句里使用表的全名来限定每个字段会让语句迅速变得冗长，让可读性降低。

如果SQL实现里允许使用注释，应该在语句里有节制地使用。注释是很好的文档，但过多的注释会让语句臃肿。

如果在SELECT语句里要使用多个字段，就让每个字段都从新行开始。

如果在FROM子句里要使用多个表，就让每个表名都从新行开始。

让WHERE子句里每个条件都以新行开始，这样就可以清晰地看到语句的所有条件及其次序。

下面是一个可读性很差的SQL语句：

下面是格式化之后的语句，可读性明显提高：

这两个语句完全一样，但第二个语句具有更好的可读性。通过使用表的别名（在FROM子句里定义），第二个语句得到了极大的简化。同时使用空格对齐每个子句里的元素，让每个子句十分明显。

注意：在使用多个表的同时确保性能

当 FROM 子句里列出了多个表时，请查看具体实现的文档来了解有关提高性能的技巧。

再强调一次，虽然提高语句的可读性并不会直接改善它的性能，但这样会帮助我们更方便地修改和调整很长和很复杂的语句。现在我们可以轻松地看到被选择的字段、所使用的表、所执行的表结合和查询的条件。

17.3.2 FROM子句里的表

FROM子句里表的安排或次序对性能有很大影响，取决于优化器如何读取SQL语句。举例来说，把较小的表列在前面，把较大的表列在后面，就会获得更好的性能。有些经验丰富的用户发现把较大的表列在FROM子句的最后面可以得到更好的效率。

下面是FROM子句的一个范例：

注意：创建编码标准

在多人编程环境里，创建编码标准是特别重要的。如果全部代码具有一致的格式，就可以更好地管理共享代码及修改代码。

17.3.3 结合条件的次序

第 13 章曾经介绍过，大多数结合使用一个基表链接到具有一个或多个共有字段的其他表。基表是主表，查询里的大多数或全部表都与它结合。在WHERE子句里，来自基表的字段一般放到结合操作的右侧，要被结合的表通常按照从小到大的次序排列，就像FROM子句里表的排列顺序一样。

如果没有基表，那表就应该从小到大排列，让最大的表位于WHERE子句里结合操作的右侧。结合条件应该位于WHERE子句的最前面，其后才是过滤条件，如下所示：

提示：严格限制结合操作的条件

由于结合操作通常会从表里返回大部分数据，所以结合条件应该在更严格的条件之后再生效。

在这个范例里，TABLE3是基表，TABLE1和TABLE2结合到TABLE3。

17.3.4 最严格条件

最严格条件通常是 SQL 查询达到最优性能的关键因素。什么是最严格的条件？它是WHERE子句里返回最少记录的条件。与之相反，最宽松的条件就是语句里返回最多记录的条件。在这里我们重点关注最严格的条件，因为它对查询返回的数据进行了最大限度的过滤。

我们应该让SQL优化器首先计算最严格条件，因为它会返回最小的数据子集，从而减小查询的开销。最严格条件的位置取决于优化器的工作方式，有时优化器从WHERE子句的底部开始读取，因此需要把最严格条件放到WHERE子句的末尾，从而让优化器首先读取它。下面的例子展示了如何根据约束条件来构造 WHERE 子句，以及如何根据表的体积来构造FROM子句。

提示：对WHERE子句进行测试

如果不知道具体实现的SQL优化器如何工作、DBA也不知情、也没有足够的文档资料，我们可以执行一个需要一定时间的大型查询，然后重新排列WHERE子句里的条件，记录每次查询执行所需的时间。采取这种方法，不用几次测试就可以判断出优化器读取WHERE子句的方向。为了在测试中获得更准确的结果，最好在测试时关闭数据库缓存。

下面是一个虚构表的测试范例：

下面是第一个查询：

下面是第二个查询：

假设第一个查询用了20秒，第二个查询用10秒。由于第二个查询速度比较快，而且在它的WHERE子句里，最严格条件位于最后的位置，所以我们可以认为优化器从WHERE子句的底部开始读取条件。

注意：使用索引字段

从实践总结出来的经验表明，最好使用具有索引的字段作为查询里的最严格条件。索引通常会改善查询的性能。

17.4 全表扫描

在没有使用索引时，或是SQL语句所使用的表没有索引时，就会发生全表扫描。一般来说，全表扫描返回数据的速度要明显比使用索引慢。表越大，全表扫描返回数据的速度就越慢。查询优化器会决定在执行SQL语句时是否使用索引，而大多数情况会使用索引（如果存在）。

有些实现具有复杂的查询优化器，可以决定是否应该使用索引。这种判断基于从数据库对象上收集的统计信息，比如对象的规模、索引字段在指定条件下返回的记录数量等。关于优化器的这种判决能力请查看具体实现的文档。

在读取大规模的表时，应该避免进行全表扫描。举例来说，当读取没有索引的表时，就会发生全表扫描，这通常会需要较长的时间才能返回数据。对于大多数大型表来说，应该考虑设置索引。而对于小型表来说，就像前面已经说过的，即使表里有索引，优化器也可能会选择全表扫描而不是使用索引。对于具有索引的小型表来说，可以考虑删除索引，从而释放索引所占据的空间，使其可以用于数据库的其他对象。

提示：简单方法避免全表扫描

除了确保表里存在索引之外，避免全表扫描的最简单、最明显方法是在查询的WHERE子句里设置条件来过滤返回的数据。

下面是应该被索引的数据：

作为主键的字段；

作为外键的字段；

在结合表里经常使用的字段；

经常在查询里作为条件的字段；

大部分值是唯一值的字段。

注意：全表扫描也有好处

有时全表扫描也是好的。对小型表进行的查询，或是会返回表里大部分记录的查询应该执行全表扫描。强制执行全表扫描的最简单方式是不给表创建索引。

17.5 其他性能考虑

在调整SQL语句里还有其他一些性能考虑，后面的小节将讨论如下概念：

使用LIKE操作符和通配符；

避免OR操作符；

避免HAVING子句；

避免大规模排序操作；

使用存储过程；

在批加载时关闭索引。

17.5.1 使用LIKE操作符和通配符

LIKE 操作符是个很有用的工具，它能够以灵活的方式为查询设置条件。在查询里使用通配符能够消除很多可能返回的记录。对于搜索类似数据（不等于特定值的数据）的查询来说，通配符是非常灵活的。

假设我们要编写一个查询，从表EMPOYEE_TBL里选择字段EMP_ID、LAST_NAME、FIRST_NAME和STATE，获得姓为Stevens的雇员ID、姓名和所在的州。下面3个范例使用了不同的通配符。

第一个查询：

第二个查询：

下面是第三个查询：

这些SQL语句并不是必须返回同样的结果。更可能的情况是，查询1利用了索引的优势，返回的记录比其他两个查询少。查询2和查询3没有明确指定要返回的数据，其检索速度要比查询1慢。另外，查询3应该比查询2更快，因为它指定了搜索字符串的开头字符（而且字段LAST_NAME很可能具有索引），因此它能够利用索引。

注意：说明数据存在的差别

查询1可能会返回姓为Stevens的全部雇员，但难道Stevens不能有其他拼写方式了吗？查询2会返回姓为Stevens及其他拼写方式的全部雇员。查询3返回姓以St开头的全部雇员，这是确保获取全部姓Stevens（或Stephens）的记录的唯一方式。

17.5.2 避免使用OR操作符

在SQL语句里用谓词IN代替OR操作符能够提高数据检索速度。SQL实现里有计时工具或其他检查工具，可以反应出OR操作符与谓词IN之间的性能差别。下面的一个范例将展示如何用IN代替OR来重新构造SQL语句。

注意：如何使用OR和IN

关于OR操作符和谓词IN请参见第8章。

下面是使用OR操作符的查询：

下面是同一个查询，使用了谓词IN：

这两个SQL返回完全相同的数据，但通过测试可以发现，用IN代替OR后，检索数据的速度明显提高了。

17.5.3 避免使用HAVING子句

HAVING子句是很有用的，可以减少GROUP BY子句返回的数据，但使用它也要付出代价。HAVING子句会让SQL优化器进行额外的工作，也就需要额外的时间。这样的查询既要对返回的结果集进行分组，又要根据HAVING子句的限制条件对结果集进行分析。看下面的例子：

在这个例子中，我们需要找到对某个产品的总计消费超过 25 元的客户。这个查询很简单，而且我们的示例数据库也很小，但HAVING子句的使用仍然增加了额外的工作，尤其当HAVING子句包含了复杂的逻辑而又应用于大量数据的时候。在可能的情况下，尽量不要在SQL语句中使用HAVING子句，如果需要使用，则最好尽可能地使其中的限制条件简单化。

17.5.4 避免大规模排序操作

大规模排序操作意味着使用ORDER BY、GROUP BY和HAVING子句。无论何时执行排序操作，都意味着数据子集必须要保存到内存或磁盘里（当已分配的内存空间不足时）。数据是经常需要排序的，排序的主要问题是会影响SQL语句的响应时间。由于大规模排序操作不是总可以避免的，所以最好把大规模排序在批处理过程里，在数据库使用的非繁忙期运行，从而避免影响大多数用户进程的性能。

17.5.5 使用存储过程

我们可以为经常运行的SQL语句（特别是大型事务或查询）创建存储过程。所谓存储过程就是经过编译的、以可执行格式永久保存在数据库里的SQL语句。

一般情况下，当SQL语句被提交给数据库时，数据库必须检查它的语法，并且把语句转化为可以在数据库里执行的格式（称为解析）。语句被解析之后就保存在内存里，但这并不是持久的。也就是说，当其他操作需要使用内存时，语句就会被从内存里释放。而在使用存储过程时，SQL语句总是处于可执行格式，并且一直会保存在数据库里，直到像别的数据库对象一样被删除。关于存储过程的详细介绍请见第22章。

17.5.6 在批加载时关闭索引

当用户向数据库提交一个事务时（INSERT、UPDATE或DELETE），表和与这个表相关联的索引里都会有数据变化。这意味着如果表EMPLOYEE里有一个索引，而用户更新了表EMPLOYEE，那么相关索引也会被更新。在事务环境里，虽然对表的每次写入都会导致索引也被写入，但一般不会产生什么问题。

然而在批量加载时，索引可能会严重地降低性能。批加载可能包含数百、数千或数百万操作语句或事务，由于规模较大，批加载需要较长的时间才能完成，而且通常安排在非高峰期使用，一般是在周末或夜晚。为了优化批加载的性能——需要12小时完成的批加载可能缩短为6小时——最好在加载过程中关闭相应表的索引。当相应的索引被删除之后，对表所做的修改会在更短的时间内完成，整个操作也会更快地完成。当批加载结果之后，我们可以重建索引。在索引的重建过程中，表里适当的数据会被填充到索引。虽然对于大型表来说，创建索引需要一定的时间，但从整体来看，先删除索引再重建它所需要的时间要更少一些。

在批加载操作的前后删除并重建索引的方法还有另一个优点，就是可以减少索引里的碎片。当数据库不断增长时，记录被添加、删除和更新，就会产生碎片。对于不断增长的数据库来说，最好定期地删除和重建索引。当索引被重建时，构成索引的物理空间数量减少了，也就减少了读取索引所需的磁盘I/O，用户就会更快地得到结果，皆大欢喜。

17.6 基于成本的优化

用户可能经常会遇到需要进行SQL语句调整的数据库。这类系统在任何一个时间点上往往都有数千条SQL语句正在执行。要优化进行调整所花费的时间，需要首先确定需要调整的查询类型。这就是我们所关注的，基于成本的优化试图确定什么样的查询造成了系统资源的额外消耗。例如，如果我们用运行时间来作为衡量标准的话，如下两个查询会获得相应的运行时间：

简单来看，第1条语句似乎就是我们需要进行优化的查询。但是，如果第2条语句每小时执行1000次，而第1条语句每小时仅执行10次，情况又怎么样呢？结果完全相反。

基于成本的优化根据资源消耗量对SQL语句进行排序。根据查询的衡量方法（如执行时间、读库次数等）以及给定时间段内的执行次数，可以方便地确定资源消耗量：

总计资源消耗 = 衡量方法×执行次数

使用这种方法，可以最大程度地获得调整收益。在上面的例子中，如果我们能够将每条语句的运行时间减半，就可以很方便地看出所节省的时间：

这样就很容易理解，为什么要把宝贵的时间花在第2条语句上了。这不仅优化了数据库，也同时优化了用户的时间。

17.7 性能工具

很多关系型数据库具有内置的工具用于SQL语句和数据库性能调整。举例来说，Oracle有一个名为EXPLAIN PLAN的工具，可以向用户显示SQL语句的执行计划。还有一个工具是TKPROF，它可以测量SQL语句的实际执行时间。在SQL Server里有一个Query Analyzer，可以向用户提供估计的执行计划或已执行查询的统计参数。关于可以使用的工具请询问DBA或查看相应的文档。

17.8 小结

本章介绍了在关系型数据库里调整SQL语句的含义，介绍了两种基本的调整类型：数据库调整和SQL语句调整，它们对于提高语句的执行效率都是很重要的。它们具有同等的重要性，只调整一个无法达到优化目的。

本章介绍了调整SQL语句的方法，首先是语句的可读性，虽然它不能直接改善性能，但有助于程序员开发和管理语句。SQL语句性能中一个重要因素是索引的使用，有时需要使用， 有时则需要避免。对于任何用于改善SQL语句性能的方法来说，最重要的是要理解数据本身、数据库设计和关系以及用户的需求。

17.9 问与答

问：通过遵循本章所介绍的规则，以数据检索时间来说，在实际应用中能够获得多大的性能提升呢？

答：在实际应用中，检索时间可能缩短几分之一秒，或是几分钟、几小时，甚至是几天。

问：如何测试SQL语句的性能？

答：每个SQL实现都应该有一个工具或系统来测试性能。本书中使用了Oracle7来测试SQL语句，它有多个工具可以测试性能，包括EXPLAIN PLAN、TKPROF和SET命令。每个实现里的具体工具及其使用请参考相应的文档。

17.10 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

17.10.1 测验

1．在小规模表上使用唯一索引会带来什么好处吗？

2．当执行查询时，如果优化器决定不使用表上的索引，会发生什么呢？

3．WHERE子句里的最严格条件应该放在结合条件之前还是之后呢？

17.10.2 练习

1 ．改写下面的 SQL 语句来改善性能。使用如下所示的表 EMPLOYEE_TBL 和表EMPLOYEE_PAY_TBL。

a.

b.

c.

2．添加一个名为EMPLOYEE_PAYHIST_TBL的表，用于存放大量的支付历史数据。使用下面的表来编写SQL语句，解决后续的问题。

首先思考，用什么方法能够确定所写的查询可以正确执行？

a．查询正式员工（salaried employee）和非正式员工（nonsalaried employee）在付薪第一年各自的总人数。

b．查询正式员工和非正式员工在付薪第一年各自总人数的差异。其中，非正式员工全年无缺勤（PAY_RATE * 52 * 40）。

c．查询正式员工现在和刚入职时的薪酬差别。同样，非正式员工全年无缺勤。并且，员工的薪水在EMPLOYEE_PAY_TBL和EMPLOYEE_PAYHIST_TBL两个表中都有记录。在支付历史表中，当前支付记录的END_DATE字段为NULL值。