编码 DB2 SQL 以获得最佳性能

优质

小牛编辑

127浏览

2023-12-01

当要保证用 IBM DB2 ® Universal Database™(DB2 UDB) 和 Borland® 工具(如 Delphi™、C++Builder™ 或Kylix™)构建的企业应用程序拥有最优性能时，程序员可以利用 DB2 优化器的能力来处理即使是“难以处理的”SQL 语句并给出有效的存取路径。

尽管如此，拙劣编码的 SQL 和应用程序代码仍可能给您带来性能问题，通过学习几条基本准则可以轻易地避免这些问题。我将向您演示 DB2 优化器的工作方式，并提供编写能发挥优化器最大效率的 SQL 的准则。但即使拥有了 DB2 的优化能力，编写有效的 SQL 语句仍可能是一件复杂的事情。

如果程序员和开发人员还不熟悉关系数据库环境，这件事就尤其显得棘手。因此，在我们深入研究编码 SQL 以获得最佳性能的细节之前，先花一些时间来回顾 SQL 基础知识。

基础知识

由于 SQL 与过程化语言不同，它提供了更高的抽象级别，因此它可以让程序员把精力集中到他们需要什么样的数据，而不是如何检索数据。您不必使用嵌入式数据导航指令来编码 SQL。DB2 会分析 SQL，并“在幕后”制定数据导航指令。这些数据导航指令叫作存取路径。

让 DBMS 确定到数据的最优存取路径解除了程序员肩上沉重的负担。此外，数据库可以更好地理解它存储的数据的状态，从而可以生成到数据的更有效和动态的存取路径。其结果就是适当使用的 SQL 可以用于更快的应用程序开发。

另一个 SQL 特性是它不仅仅是一种查询语言。您还可以使用它来定义数据结构;控制对数据的访问;以及插入、修改和删除数据的发生。通过提供一种公共语言，SQL 简化了 DBA、系统程序员、应用程序员、系统分析员和最终用户之间的通信。当项目的所有参与者都使用同一种语言时，他们之间所建立起来的协作就可以减少整体系统开发时间。

历史证明，保证 SQL 成功的最重要的一个特性就是它使用类似英语的语法轻松地检索数据的能力。理解这种语言比理解数据页面的结构和程序源代码要容易得多：

SELECT LASTNAME
FROM EMP
WHERE EMPNO = '000010';

想想看：当访问文件中的数据时，程序员必须编码指令来打开文件、开始一个循环、读取记录、检查 EMPNO 字段是否等于适当的值、检查文件结尾、回到循环的开头等。

SQL 本来就是非常灵活的。它使用自由格式的结构，该结构可以让用户开发 SQL 语句来适合他们的需要。DBMS 在执行之前会分析每个 SQL 请求，以检查语法是否正确和优化该请求。SQL 语句不需要从任何给定的列中开始，您可以将它们串在一行中，或者把它们拆成几行。例如，以下这条单行的 SQL 语句与我前面使用的三行示例等价：

SELECT LASTNAME FROM EMP WHERE EMPNO = '000010';

SQL 的另一个灵活特性是您可以用许多形式不同但功能等价的方法来制定一个请求。例如：SQL 可以连接表或嵌套查询。您始终可以将嵌套查询转换成等价的连接。您可以在大量的函数和谓词中看到这一灵活性的其它示例。具有等价功能的特性的示例包括：

BETWEEN vs <= / >=
IN vs 一系列和 OR 配合的谓词
INNER JOIN vs FROM 子句中串在一起并用逗号分隔的表
OUTER JOIN vs 带有 UNION 的简单 SELECT 和相关的子查询
CASE 表达式 vs 复杂的 UNION ALL 语句

SQL 展示的这一灵活性并不总是称心的，因为形式不同但功能等价的 SQL 公式可以提供非常不同的性能。我将在本文的以后部分讨论该灵活性所造成的结果，并提供开发有效的 SQL 的准则。

如我所说的，SQL 指定了要检索或操作什么数据，但没有指定数据库如何完成这些任务。这就使 SQL 本身变得很简单。如果您能够记得关系数据库的一次处理一个集合(set-at-a-time)的特点，您就开始掌握 SQL 的本质和性质了。一条 SQL 语句可以作用于多行。作用于一组数据而不需要建立如何检索和操作数据的能力将 SQL 定义成非过程化语言

因为 SQL 是一种非过程化语言，所以一条语句可以代替一系列过程。同样，由于 SQL 使用集合级别的处理以及 DB2 优化查询来确定数据导航逻辑，所以这是可能的。有时，如果不使用 SQL 语句，一条或两条 SQL 语句可以完成的任务就需要完整的过程化程序来完成。

优化器

优化器是 DB2 的心脏和灵魂。它分析 SQL 语句并确定可以满足每条语句的最有效的存取路径(请参阅图 1)。DB2 UDB 通过解析 SQL 语句来确定必须访问哪些表和列，从而完成该操作。DB2 优化器然后查询存储在 DB2 系统目录中的系统信息和统计信息，以确定完成满足 SQL 请求所必需的任务的最佳方法。

优化器在功能上等价于一个专家系统。专家系统是一个标准规则集合，当与情境数据组合时，它返回一个“专家”意见。例如，医学专家系统采用一个规则集合，用来确定哪些药可以用于哪些疾病，将规则集与描述疾病症状的数据组合，并将知识库应用于输入症状的列表。DB2 优化器会根据存储在 DB2 系统目录中的情境数据和 SQL 格式的查询输入来生成对数据检索方法的专家意见。

在 DBMS 中优化数据访问的概念是 DB2 最强大的能力之一。请记住，您访问 DB2 数据时应告诉 DB2 要检索什么，而不是如何检索。无论数据实际上是如何存储和操作的，DB2 和 SQL 都可以访问该数据。从物理存储特征中分离出访问标准叫作物理数据独立性。DB2 的优化器是完成该物理数据独立性的组件。

如果您不要索引，DB2 仍然能够访问数据(尽管效率会降低)。如果将一列添加到正在被访问的表中，DB2 仍然可以在不更改程序代码的情况下操作数据。因为到 DB2 数据的物理存取路径并不是由程序员在应用程序中编码的，而是由 DB2 生成的，所以这种情况是完全有可能发生的。

这个特点与非 DBMS 系统非常不同，在那种系统中，程序员必须知道数据的物理结构。如果有索引，程序员就必须编写适当的代码来使用该索引。如果某人删除了索引，程序就不能工作，除非程序员进行更改。而使用 DB2 和 SQL 就不必如此。这一灵活性完全归功于 DB2 自动优化数据操作请求的能力。

优化器根据许多信息执行复杂的计算。要使优化器的工作方式直观化，可以将优化器想象成执行一个四步骤的过程：

接收并验证 SQL 语句的语法。
分析环境并优化满足 SQL 语句的方法。
创建计算机可读指令来执行优化的 SQL。
执行指令或存储它们以便将来执行。

这个过程的第二步是最有趣的。优化器怎样决定如何以它的方式执行您可以发送的大量 SQL 语句?

优化器有许多类型的优化 SQL 的策略。它如何选择在优化存取路径中使用这些策略中的哪一个?IBM 并没有发布优化器如何确定最佳存取路径的真正和深入的详细信息，但优化器是一个基于成本的优化器。这意味着优化器将始终尝试为每个查询制定减少总体成本的存取路径。

要实现这个目标，DB2 优化器会应用查询成本公式，该公式对每条可能的存取路径的四个因素进行评估和权衡：CPU 成本、I/O 成本、DB2 系统目录中的统计信息和实际的 SQL 语句。