【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。)正如我们所见,使统计信息保持最新是必要的。
但我们不要认为:如果存在某种适当的索引,使用索引始终是较好的选择。