【IT168 服务器学院】当我们认为优化器没有正常工作时:简介

　　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 的帮助。

　　关于作者

　　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 新闻组与他联系。