中国 [选择]

本文内容包括：
	简介
	为什么两个几乎相同的查询的运行方式却大相径庭
	为什么有时表空间扫描比索引扫描更可取
	为什么有时计算 MIN 比计算 MAX 快很多
	消除不必要的连接
	什么时候好的决策比快速的决策更好
	结束语
	感谢
	关于作者
	对本文的评价

相关链接：
	Information Management 技术文档库

developerWorks 中国 > Information Management >

当我们认为优化器没有正常工作时

文档选项

未显示需要 JavaScript 的文档选项

级别：初级

Alexander Kuznetsov, 芝加哥，伊利诺斯州

2003 年 2 月 01 日

根据真实的生活经验，作者 Alexander Kuznetsov 向您展示了一些提示，以确保优化器为您的查询做出好的决策。

简介

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

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

回页首

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

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