Alexander Kuznetsov芝加哥，伊利诺斯州
2003 年 2 月 根据真实的生活经验，作者 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
|
正如我们所见，添加外键约束向优化器提供了一些非常有用的数据。优化器则向我们提供更有效的执行计划作为报答。
什么时候好的决策比快速的决策更好
以前当我在寻找新工作时，我曾无意中看到两个空缺职位，它们是同一家公司提供的，而且是针对同一个项目的。对于该项目，他们需要一个项目经理和一个技术负责人。在众多要求中，他们列出了：
- 对于项目经理：“能够做出 快速的决策。”
- 对于技术负责人：“能够做出 好的决策。”
确有其事！
对于低的优化级别，优化器必须动作迅速。无论我们打算提供什么样的最新和详细的统计信息，优化器也许没有足够的时间对它进行分析。前面几章中的所有示例都是在缺省优化级别 5 下运行的。如果我们在低优化级别 1 下重新考虑前面的示例，添加引用完整性约束将不会产生更好的计划。
如果您想要好的决策，而不是快速的决策，请相应地设置优化级别。
有关优化级别的更多信息，请参考 DB2 Administration Guide 中关于 性能和 实现（Implementation）的章节。
结束语
DB2 优化器是非常智能化的。但是，根据不正确的信息，它也许会得出优化程度较低的结论。我们已经知道了如何：
- 检测不正确或不完整的统计信息。
- 向优化器提供正确且完整的统计信息。
- 在测试环境中更新 SYSSTAT 模式的视图，并执行“假定方案”实验。
性能调优从来就不容易。在查询优化中没有一成不变的规则。只要有可能，就应检查优化级别，使统计信息保持最新，并确保业务规则作为约束实现。我希望本文在数据库开发人员处理许多问题时有所帮助。
祝您好运！
感谢
作者衷心感谢 Mike Pittinger 的帮助。 |
