IBM®DB2® Universal Database™ 附带了一个非常智能化的优化器，但是有时它的选择也许看来有些不称职。无论优化器多复杂，它也只不过是一个用来处理输入数据（如物理数据库结构和统计信息）并生成执行计划的程序。如果我们认为优化器没有正常工作，我们可以尝试向它提供一些更好的输入，看看会发生什么。也许优化器的选择最终是正确的（它通常是正确的）。本文提供了一些示例，在这些示例中收集了当前和完整的统计信息、添加了适当的约束并设置了适当的优化级别导致更好的执行计划。

为什么两个几乎相同的查询的运行方式却大相径庭

　　让我们考虑一个非常典型的方案：查询 SELECT * FROM CUSTOMER WHERE STATE = 'IN' 的运行速度非常慢。一个非常相似的查询 SELECT * FROM CUSTOMER WHERE STATE = 'MI' 反复运行的速度却要快 10 多倍。我们首先检查显而易见的情况：STATE 列上是否有索引？有的。下一步，我们检查每个州的客户数量是否差别很大。以下查询显示了每个州的客户数量看上去相差不多：

SELECT STATE, COUNT(*) AS NUM_CUST FROM CUSTOMER GROUP BY STATE WHERE STATE IN('IN', 'MI')

STATE NUM_CUST

----- ----------

IN 19071

MI 18554

SELECT COUNT(*) AS NUM_CUST FROM CUSTOMER

NUM_CUST

---------

2007931

当我们研究执行计划时，我们发现较慢的查询是作为表空间扫描来执行的，而较快的查询使用了索引。两者的区别在哪里呢？优化器为什么不为这两个查询选择同一个有效的计划呢？

让我们仔细研究与该表相关的统计信息。在 DB2 中，通过 SYSCAT 和 SYSSTAT 模式中的系统视图可以披露统计信息。（有关统计信息的更多详细信息，请参考 DB2 Administration Guide 中关于性能（Performance）的章节。）在我们的示例中，统计信息并不是最新的（在以下的清单中，请参阅 STATS_TIME，它是两个月之前的）。此外，自上次收集统计信息（在下面的清单中，请参阅 CARD）之后，记录的总数（现在是 2007931）已经大大增加了：

SELECT STATS_TIME, CARD FROM SYSCAT.TABLES WHERE TABNAME = 'CUSTOMER'

STATS_TIME CARD

-------------------------- ------

2002-10-01-08.49.49.117405 59616

虽然表已经超过了两百万行，但是优化器查找统计信息并且估计表中的记录仍然少于 60000 条。另外，STATE 列中值的分布也已经有很大的改变（TYPE = 'F' 代表最频繁出现的值）：

SELECT TYPE,SEQNO, VALCOUNT, CAST(COLVALUE AS CHAR(30))

AS COLVALUE FROM SYSSTAT.COLDIST WHERE TABSCHEMA='DB2INST1'

AND TABNAME='CUSTOMER_DATA' AND COLNAME='STATE' AND TYPE = 'F'

TYPE SEQNO VALCOUNT COLVALUE

---- ------ -------------------- --------------------------

F 1 19071 'IN'

F 2 18554 'SC'

F 3 11061 'CA'

F 4 5857 'TN'

F 5 2741 'KY'

F 6 1065 'MO'

F 7 220 'IL'

F 8 90 'WI'

F 9 26 'MI'

F 10 4 'FL'

（该查询检索该列中最频繁出现的 10 个值。）注：上一次收集统计信息时，印地安那州的客户（STATE='IN'）在所有客户中超过了 30％，而密歇根州的客户（STATE='MI'）只占 0.05％。让我们刷新该统计信息，包括分布：

RUNSTATS ON TABLE MYSCHEMA.CUSTOMER WITH DISTRIBUTION AND DETAILED INDEXES ALL

在刷新之后，优化器使用索引访问来执行原来比较慢的查询，现在该查询的运行速度快多了。（有关 RUNSTATS 命令的完整语法，请参考 Command Reference。）正如我们所见，使统计信息保持最新是必要的。

但我们不要认为：如果存在某种适当的索引，使用索引始终是较好的选择。

为什么有时表空间扫描比索引扫描更可取

考虑相同的查询 SELECT * FROM CUSTOMER WHERE STATE = ?。不管您是否相信，有时执行表空间扫描比通过索引访问记录更有效。听起来让人吃惊吧？是的，也许要进行一些分析来得出这个结论。让我们从一些基准测试开始；然后加以说明。

CUSTOMER 表中约 18％ 的记录与条件 WHERE STATE='IL' 匹配。通过对查询 SELECT * FROM CUSTOMER WHERE STATE = 'IL' 进行分析，优化器选择表空间扫描来执行它。让我们将该查询保存到名为 select.sql 的文件中，使用基准测试工具（db2batch）来测量执行该查询的实际代价。

db2batch -d MY_DB -f select.sql -r benchmark.txt -o p3

Number of rows retrieved is: 19998

Number of rows sent to output is: 19998

Elapsed Time is: 5.540 seconds

Locks held currently = 0

Lock escalations = 0

Total sorts = 0

Total sort time (ms) = 0

Sort overflows = 0

Buffer pool data logical reads = 2721

Buffer pool data physical reads = 2580

Buffer pool data writes = 0

Buffer pool index logical reads = 0

Buffer pool index physical reads = 0

（有关 db2batch 的更多详细信息，请参阅［Command Reference］。）

现在，让我们欺骗优化器，让它选择索引扫描来执行相同的查询。让我们使统计信息看上去象有一个虚构的州（STATE='IM'），而且在这个州里有许多客户，再让我们使伊利诺斯州的客户数量（如果有的话）变得很小。因为 SYSSTAT 模式中的视图是可更新的，让我们更新其中一个：

UPDATE SYSSTAT.COLDIST SET COLVALUE='IM' WHERE

TABSCHEMA='DB2INST1' AND TABNAME='CUSTOMER_DATA' AND COLNAME='STATE'

--AND TYPE = 'F'

AND COLVALUE='''IL'''

提示：在 WHERE 子句中，必须用引号括起列值（COLVALUE='''IL'''）。

现在，根据这些统计信息，优化器推断出只有很少的记录可能拥有 STATE='IL'。因此，它选择使用 STATE 上的索引的存取方案（请参阅较小的估计基数）：

SQL Statement:

SELECT *

FROM CUSTOMER_DATA

WHERE STATE='IL'

Estimated Cost = 50

Estimated Cardinality = 1

Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5

| #Columns = 13

| Index Scan: Name = DB2INST1.CUST_STATE ID = 5

| | Index Columns:

| | | 1: STATE (Ascending)

| | #Key Columns = 1

| | | Start Key: Inclusive Value

| | | | 1: 'IL'

| | | Stop Key: Inclusive Value

| | | | 1: 'IL'

| | Data Prefetch: None

| | Index Prefetch: None

现在，让我们使用 db2batch 来执行第二个基准测试：

Number of rows retrieved is: 19998

Number of rows sent to output is: 19998

Elapsed Time is: 5.976 seconds

Locks held currently = 0

Lock escalations = 1

Total sorts = 0

Total sort time (ms) = 0

Sort overflows = 0

Buffer pool data logical reads = 19998

Buffer pool data physical reads = 2614

Buffer pool data writes = 0

Buffer pool index logical reads = 138

Buffer pool index physical reads = 28

显然，欺骗并没有给我们带来任何好处。在这种情况下，使用表空间扫描所耗费的时间实际上比使用索引扫描更少。

重要事项：我们已经手工更新了统计信息来对测试数据库执行一些“假定方案（what if）”分析。这对 SYSSTAT 模式的可更新视图是完全合理的用法。但是，在生产数据库中，我们绝对不应在正常的情况下更新统计信息。

现在，让我们解释发生了什么。我曾经听一个 5 岁的男孩说：“在监狱里待一秒钟不会有什么不良影响，所以在监狱里待两秒钟也不会有什么不良影响，那么在监狱里待三秒钟也不会有什么不良影响……”同样，通过索引读取一条记录会快一点，通过索引读取两条记录也会快一点，依此类推，但最多只能到某个数量，不能再多了。

根据统计信息，优化器估计 18％ 的记录将匹配条件 STATE='IL'。它还预期这些记录在整个表中差不多是均匀分布的，因为 STATE 上索引的群集比率是非常低的，小于 0.1。（有关群集比率的更多信息，请参考 DB2 Administration Guide 中关于性能的章节。）这意味着：无论如何，几乎表中的每一页都至少有一条匹配的记录。表空间扫描使用预取，这意味着数据库引擎在一次有效的读操作中会读取几个相邻的页面。表空间扫描是读取表中所有页面的最有效的方法。无论索引扫描可能会多么有效，仍然存在扫描索引的额外工作。

有关预取的更多信息，请参考：

SQL Reference 中 CREATE TABLESPACE 语句的语法及其 PREFETCHSIZE 选项。

DB2 Administration Guide 中关于性能的章节中缺省预取大小（DFT_PREFETCH_SZ）配置参数。

因此，无论看起来有多令人吃惊，优化器选择表空间扫描最终是正确的。我们已经了解了在这种情况下，索引访问肯定效率比较低。

为什么有时计算 MIN 比计算 MAX 快很多

查询 SELECT MIN(TOTAL_AMOUNT) FROM CUSTOMER 查找 TOTAL_AMOUNT 上的现有索引中的值，并立即返回答案。但是，一个非常相似的查询 SELECT MAX(TOTAL_AMOUNT) FROM CUSTOMER 却需要耗费多得多的时间。执行计划指出优化器选择了扫描整个索引来计算 MAX。为什么？

在这种特殊情况下，没有更好的选择。TOTAL_AMOUNT 上的索引不允许反向扫描：

SELECT REVERSE_SCANS FROM SYSCAT.INDEXES WHERE

INDNAME = 'CUSTOMER_AMT'

REVERSE_SCANS

-------------

N

在删除索引并用选项 ALLOW REVERSE SCANS 重新创建它之后，这两个查询开始运行得一样快了。

CREATE INDEX CUSTOMER_AMT ON CUSTOMER(TOTAL_AMOUNT) ALLOW REVERSE SCANS

RUNSTATS ON TABLE MYSCHEMA.CUSTOMER FOR INDEX MYSCHEMA. CUSTOMER_AMT

缺省情况下，DB2 索引不允许反向扫描。

提示：每当您在 CREATE TABLE 语句中创建 PRIMARY KEY、FOREIGN KEY 或 UNIQUE 约束时，就会隐式地创建一个索引。该索引不允许反向扫描。

您可以覆盖缺省行为：

创建一个没有约束的表（或删除现有约束）。

创建适当的索引。

使用 ALTER TABLE SQL 语句创建约束。

例如：

ALTER TABLE CUSTOMER DROP PRIMARY KEY;

--or create a table not defining a primary key

CREATE UNIQUE INDEX CUSTOMER_ID ON CUSTOMER(ID) ALLOW REVERSE SCANS;

ALTER TABLE CUSTOMER ADD PRIMARY KEY(ID);

DB2 会给出一条警告，并重用第 2 步中创建的索引。

正如我们所见，在一些十分常见的情况下，允许反向扫描的索引是必要的。

消除不必要的连接

让我们考虑以下视图：

CREATE VIEW CUSTOMER_ORDER_LIST

AS

SELECT

CUSTOMER_ORDER.CUSTOMER_ID

CUSTOMER.LAST_NAME

CUSTOMER.FIRST_NAME

CUSTOMER.PHONE

CUSTOMER.EMAIL

CUSTOMER_ORDER.ORDER_DT

CUSTOMER_ORDER.AMOUNT

CUSTOMER_ORDER.STATUS

FROM CUSTOMER JOIN CUSTOMER_ORDER

ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID

CUSTOMER_ORDER 表中的所有记录在 CUSTOMER 表中都有父记录。该业务规则是由触发器维护的，而不是由外键约束维护的。（不要问我为什么。我能说的就是我在生产数据库中已经很多次看到它了。）

考虑查询：

SELECT CUSTOMER_ID, ORDER_DT, AMOUNT, STATUS FROM CUSTOMER_ORDER_LIST

您可能会认为根本不需要访问 CUSTOMER 表，因为所有必需的信息都在 CUSTOMER_ORDER 表的视图中，对吗？

事实并非这样。出于某些原因，优化器选择访问 CUSTOMER 表上的索引：

Estimated Cost = 25693

Access Table Name = DB2INST1.CUSTOMER ID = 2,5

| #Columns = 1

| Index Scan: Name = SYSIBM.SQL021126111001110 ID = 3

| | Index Columns:

| | | 1: ID (Ascending)

| | #Key Columns = 0

| | | Start Key: Beginning of Index

| | | Stop Key: End of Index

| | Index-Only Access

| | Index Prefetch: Eligible 199

| Lock Intents

| | Table: Intent Share

| | Row : Next Key Share

Merge Join

| Access Table Name = DB2INST1.CUSTOMER_ORDER ID = 2,6

（这只是部分输出。）

究竟为什么要访问 CUSTOMER 表呢？优化器的选择实际上非常有道理：您可能轻易地删除了触发器，将一条违反引用完整性的记录插入 CUSTOMER_ORDER 表中，并重新创建了触发器。记录将保留在 CUSTOMER_ORDER 表中，这意味着存在这种情况：触发器不保证引用完整性。这就意味着优化器必须假设 CUSTOMER_ORDER 表中可能有一些记录在 CUSTOMER 表中没有匹配的记录，因此查找 CUSTOMER 表上的记录是必要的。

现在，让我们创建适当的约束，看看会发生什么：

ALTER TABLE CUSTOMER_ORDER ADD FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(ID)

如果有任何记录违反了该约束，那么这条语句就会失败。现在，优化器能消除不必要的连接，而且查询可以运行得更快：

Estimated Cost = 18067

Access Table Name = DB2INST1.CUSTOMER_ORDER ID = 2,6

| #Columns = 1

| Relation Scan

| | Prefetch: Eligible

| Lock Intents

| | Table: Intent Share

| | Row : Next Key Share

| Return Data to Application

| | #Columns = 1

Return Data Completion

正如我们所见，添加外键约束向优化器提供了一些非常有用的数据。优化器则向我们提供更有效的执行计划作为报答。

什么时候好的决策比快速的决策更好

以前当我在寻找新工作时，我曾无意中看到两个空缺职位，它们是同一家公司提供的，而且是针对同一个项目的。对于该项目，他们需要一个项目经理和一个技术负责人。在众多要求中，他们列出了：

　　◆ 对于项目经理：“能够做出快速的决策。”

　　◆对于技术负责人：“能够做出好的决策。”

　　◆确有其事！

对于低的优化级别，优化器必须动作迅速。无论我们打算提供什么样的最新和详细的统计信息，优化器也许没有足够的时间对它进行分析。前面几章中的所有示例都是在缺省优化级别 5 下运行的。如果我们在低优化级别 1 下重新考虑前面的示例，添加引用完整性约束将不会产生更好的计划。

如果您想要好的决策，而不是快速的决策，请相应地设置优化级别。

有关优化级别的更多信息，请参考 DB2 Administration Guide 中关于性能和实现（Implementation）的章节。

结束语

DB2 优化器是非常智能化的。但是，根据不正确的信息，它也许会得出优化程度较低的结论。我们已经知道了如何：

检测不正确或不完整的统计信息。

向优化器提供正确且完整的统计信息。

在测试环境中更新 SYSSTAT 模式的视图，并执行“假定方案”实验。

性能调优从来就不容易。在查询优化中没有一成不变的规则。只要有可能，就应检查优化级别，使统计信息保持最新，并确保业务规则作为约束实现。我希望本文在数据库开发人员处理许多问题时有所帮助。

祝您好运！

感谢

作者衷心感谢 Mike Pittinger 的帮助。

相关信息

Command Reference 下载地址：

ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2n0e80.pdf

Administration Guide: Implementation 下载地址：

ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2d2e80.pdf

Administration Guide: Performance 下载地址：

ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2d3e80.pdf

页面顶部

关于作者

Alexander Kuznetsov 在软件设计、开发和数据库管理方面已有 15 年的经验。目前，他正在设计 DB2 UDB EEE 中多 TB 级群集数据库。Alexander 是 IBM 认证高级技术专家（DB2 群集）（IBM Certified Advanced Technical Expert (DB2 Cluster)）和 IBM 认证解决方案专家（数据库管理和应用程序开发）（IBM Certified Solutions Expert (Database Administration and Application Development)）。可以通过 alkuzo@mindspring.com 和 comp.databases.ibm-db2 新闻组与他联系。