级别： 初级 Samar Desai, 研发工程师
Nita Dembla, 研发工程师
2002 年 12 月 01 日 这篇长达30页的论文让你对IDS 优化器有一个基本的认识，包括：查询重写，计划生成，统计角色及优化器指示等。
简介
IBM® Informix®Dynamic Server 优化器组件的作用是对可能的查询执行计划进行评估，然后根据试探性规则以及成本来确定最好的计划。
优化器用“智能猜测”来确定首先扫描哪个表，这种猜测基于查询中出现的表的行数，以及这些表的索引的可用性。然后，它从余下可能的表中确定哪一个将与第一个表连接（join），接下来将哪个表与这两个表连接，依此类推，直到每个表的连接顺序都已确定为止。
优化器是如何与 SQL 引擎配合的
可以用图来表示优化器组件，如下所示：
优化器是 SQL 引擎的核心部分。当用户启动内含 SQL 语句的应用程序时，该语句被发送到服务器。在到达服务器时，该语句首先经过解析器，解析器用一组语法规则检查语句的语法是否正确。解析器会为该语句构建解析树，然后把该树传递给语义分析器。
为了将解析树转换为 SQL 结构，后端会读取数据库系统目录以检索查询中每个表的信息。这一结构会被输入到优化器。优化器生成用于处理该查询的计划。该计划包括应使用哪些索引，扫描表的顺序以及是否需要临时表以备排序或分组之用。
一般的想法认为优化器所选择的计划是处理查询的可行方法中最快的。实际上，这并不总是正确，正确的程度取决于优化算法的完善程度和统计信息的可用性及准确性。
基本术语
要理解优化过程，对要用到的术语有一个基本理解是有帮助的。
元组
元组是一行列值，它有时被称为行或记录。
扫描
扫描是读取数据的实际操作。在执行查询中可能会用到几种扫描类型。使用哪种类型的决定取决于一些具体信息，如是否使用索引以及读取数据的方式。
以下是扫描类型：
连接
现在让我们考虑当对多个表进行查询时会怎样。假设我们有两个表，t1 和 t2。表 t1 有列 c11 和 c12，表 t2 有列 c21 和 c22。以下是我们的查询：
SELECT t1.c11, t2.c22
FROM t1, t2
WHERE t1.c11 = “John” AND t1.c12 = t2.c22;
|
当查询包含多个表时，通常用查询中的过滤器把各个表连接在一起。第一个要扫描的表通常被称为“外表（outer table）”，为获得与从外表读取的行相匹配的行，而要搜索的第二个表被称为“内表（inner table）”。图 2 演示了该过程。
有以下几种类型的连接：
-
嵌套循环连接— 数据库服务器扫描外表；对于每个满足表级别过滤器的行，它会扫描内表，以搜索与之匹配的行。可以对外表使用表扫描或索引扫描。对内表则更适于使用索引扫描，因为内表将被访问的次数可能非常多。
-
动态散列连接— 散列连接分为两个阶段：构建阶段和探测阶段。在构建阶段，数据库服务器会扫描较小的表，并通过对应用了任何合适的表级别过滤器之后所产生的行的键应用散列函数，来构建散列表。键是用来连接表的列。当两个连接表中至少有一个在连接列上没有索引时，或当必须从两个表读取大量行时，优化器通常选择散列连接。
-
笛卡儿积连接— 数据库服务器将内表的所有行与外表的每一行相连接，从而产生所有可能的行组合。当任何过滤器的连接列的索引不可用时，或当自动或动态索引的成本大于笛卡儿积连接成本时，以及当动态散列连接方法的成本大于笛卡儿积连接的成本时，就会使用这种连接方法。
过滤器
过滤器是查询的搜索条件，用于确定哪些行将被查询选中。有以下几种类型的过滤器：
下面将该模式用于查询：
SELECT col1, col2, col3
FROM t
WHERE col1 >= 10 AND (col1 > 10 OR (col2 > 20 OR (col2 = 20 AND col3
>= 30))
ORDER BY col1 ,col2 ,col3;
|
以上查询等价于以下 ANSI 语法：
SELECT col1, col2, col3
FROM t
WHERE (col1, col2, col3) >= (10,20,30)
ORDER BY col1, col2, col3;
|
要满足该查询，必须从索引 t(col1,col2,col3) 的 (10,20,30) 处开始进行索引扫描。要实现这一点，扫描的下限索引过滤器可以是：
col1 >= 10, col2 >= 20, col3 >= 30
|
这些过滤器被称为键起始过滤器，并且只用于设置索引扫描的起始点。对于实际数据检索，可应用最初的过滤器：
col1 >= 10 AND (col1 > 10 OR (col2 > 20 or (col2 = 20 and col3 >= 30))
|
存取路径
数据库服务器读表的方法被称为存取路径。有三种类型的存取路径：
-
顺序路径— 数据库服务器使用顺序扫描来读表。
-
索引路径— 数据库服务器使用索引从表读取数据。
-
自动索引路径— 如果数据库服务器需要多次根据某个列探测一个大型表，而在这个列上没有现成索引，那么优化器可能会选择实时创建一个索引以便访问表中的行。执行相关查询时也常常倾向于使用自动索引路径。
表
表是为了从用户的角度出发逻辑地安排数据而定义的数据库对象。
首行表是设置了首行特性的表。该特性表示：在查询表时，仅当发现第一个匹配的行时才会扫描该表。首行表通常是半连接中的内表。半连接是连接的一种，在这种连接中，一旦外表的行匹配内表行的连接条件时，连接处理会移到外表的下一个值以进行条件匹配。因此，实际上连接使用内表的第一个匹配行，并且拒绝重复的匹配值。
可将首行表表示为 A(T1,T2,...,Tn)，其中 A 是首行表，T1,T2,...,Tn 是优先顺序表，必须在连接首行表之前就以连接顺序对它们进行了连接。请考虑以下示例：
SELECT t1.col1 FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.col2 = t1.col3)
|
以上查询可重新写成：
SELECT t1.col1 FROM t1, t2 (FR, t1)
WHERE t2.col2 = t1.col3
|
在上述例子中，只有发现了 col3 中每个值的首个匹配，才会探测 t2 中 col2 的值与 t1 中 col3 的值是否匹配。请注意：首行表表示法 t2(FR, t1) 是内部表示法；用户不能使用该语法。
优化过程
优化可分为两个步骤：
- 改写查询
- 生成计划
改写查询
有时只需通过对查询做些修改并确保两个查询（最初的和改写的）产生的行集合保持相同，就可以减少查询的响应时间。服务器在内部确定是否有可能改写查询并且使改写后查询的输出方式与最初查询的输出相同。这是优化过程中的第一步。这一步骤完全是基于试探法，并且此时还没有出现成本计算。
以下几个示例说明了基于试探法的查询改写的重要性。
示例 1：
SELECT *
FROM a
WHERE a.c1 > ALL (SELECT c1 FROM b);
|
在示例 1 中，对于外部查询中表 a 的每一行，都将进行 N 次比较，其中 N 等于表 b 中的行数。该查询可转换为以下查询：
SELECT *
FROM a
WHERE a.c1 > (SELECT MAX(c1) FROM b);
|
在这个例子中，对于表 a 的每一行，仅需与子查询进行一次比较。
示例 2：
SELECT c1
FROM product p
WHERE EXISTS (SELECT c2
FROM order o
WHERE o.prodid = p.prodid
AND o.prodid = 2001)
|
通过子查询“扁平化”（改写）过程，将上面的查询改写为：
SELECT p.c1
FROM product p, order o(FR, p)
WHERE p.prodid = o.prodid AND o.prodid=2001
|
将相关子查询转换为非相关子查询
将某些相关子查询转换为非相关子查询是有可能的。相关子查询的特点是：针对父语句处理的每一行都会对它进行一次求值。父语句可以是 SELECT、UPDATE 或 DELETE 语句。
相关子查询示例：
SELECT *
FROM tab1
WHERE tab1.c1 = 10 and tab1.c2 =
(SELECT max (tab2.c2)
FROM tab2
WHERE tab2.c1 = tab1.c1)
|
通过进行名为断言提升（predicate promotion）的处理，可以把这一相关子查询转换成非相关子查询：
SELECT * FROM tab1
WHERE tab1.c1 = 10 and tab1.c2 = (SELECT max(tab2.c2)
FROM tab2
WHERE tab2.c1 = 10)
|
避免使用相关子查询的好处是子查询只需执行一次，而不是针对 tab1.c1 中的每一行都执行一次。这是因为改写使服务器能够在高速缓存中存储子查询的结果。
子查询“扁平化”
目前，服务器能够“扁平化”或改写不含聚合的 ANY、EXISTS、SOME 及 IN 子查询。改写过程由两个步骤组成：
- 将 ANY、SOME 和 IN 改写为 EXISTS 子查询
- 将 EXISTS 子查询“扁平化”到其父语句块中。
例如，将查询：
SELECT t1.c1 FROM t1
WHERE t1.c2 > ANY (SELECT t2.c2
FROM t2
WHERE t2.c3 = t1.c3)
|
首先转换为：
SELECT t1.c1 FROM t1
WHERE EXISTS (SELECT t2.c2
FROM t2
WHERE t2.c3 = t1.c3 and t2.c2 < t1.c2)
|
然后转换为：
SELECT t1.c1 FROM t1, t2 (FR, t1)
WHERE t2.c3 = t1.c3 and t2.c2 < t1.c2
|
ANSI 查询改写
ANSI 查询改写通常由以下步骤组成：
- 将 ANSI 外连接转换成 ANSI 内连接
- 将 ANSI 内连接转换成 Informix 内连接。
有关连接的背景知识
在外连接中，结果包括第一个表（左外连接）或第二个表（右外连接）的所有行，以及满足连接条件的表的行组合。如果对应的输入表没有匹配的行，那么结果行中的字段将是 NULL。
让我们首先看看查询使用左外连接时的结果：
SELECT *
FROM t1, outer t2
WHERE t1.c1 = t2.c2;
|
在左外连接中，结果保留了主表（t1）中由于没有在从表（t2）发现匹配行而原本应丢弃的行。如果主表行没有匹配的从表行，则接收从表的列上 NULL 值。
如果 t1.c1 包含行 (10,20,30,40)，并且 t2.c2 包含行 (10,20,50,60)，则以上查询的输出为：
c1 c2
10 10
20 20
30 NULL
40 NULL
|
另一方面，内连接是简单的连接，其结果只包含满足连接条件的表的行组合。不满足连接条件的行则被丢弃。
考虑表 t1 和 t2 中同样的数据行，则以下查询：
SELECT *
FROM t1, t2
WHERE t1.c1 = t2.c2;
|
将产生以下输出：
ANSI 外连接特性使您能使用局部 ANSI 外连接语法，包括 ON 子句支持（它使查询能够指定在连接后的过滤器之前求值的过滤器）；跟在它后面的 WHERE 子句（如果有的话）使查询能指定连接后的过滤器。还支持使用左外连接子句的查询。
SELECT *
FROM t1, outer t2
ON (t1.c1 = t2.c2)
WHERE t1.c1 < 30;
|
以上查询将产生下列输出：
连接的实现
Informix 连接可表示为左深树（left deep tree）。例如，如果有一个查询连接了四个表：A、B、C 和 D，则 Informix 连接树看起来如下：
上图对左深树的意思作了说明：
- 表 A 与表 B 连接
- A-连接-B 的结果随后与表 C 连接。
- 结合在一起的表 A、B 和 C 的连接结果随后与表 D 连接。
因此，我们的结论是 Informix 连接的右表始终是基表。
相反，ANSI 连接可表示为丛生树（bushy tree），如下所示：
- 表 A 与表 B 连接。
- 表 C 与表 D 连接
- A 和 B 的连接结果随后与 C 和 D 的连接结果连接。
在 ANSI 连接的情况中，连接的左部和右部可能是基表或连接集。这一类型的树被称为茂密树。
ANSI 外连接向 ANSI 内连接的转换
在这一改写中，服务器尽可能地将 ANSI 外连接转换为 ANSI 内连接。结果是，查询中只有数量较少的 ANSI 外连接。尽可能使用内连接的好处在于：消除了对首先连接哪个表的限制，并有了更多连接顺序的组合。
例如，服务器可将以下查询：
SELECT *
FROM (t1 LEFT JOIN t2 ON (t1c1=t2c2))
WHERE t2.c3=10
|
转换为
SELECT *
FROM (t1 JOIN t2 ON (t1c1=t2c2))
WHERE t2.c3=10
|
请注意：由于“拒绝空”（NULL rejecting）过滤器 t2.c3=10 的存在，在上面的示例中转换查询是有可能的。拒绝空连接后的过滤器将丢弃那些由于不匹配主表行而被填充 NULL 值的从表行。因此，只有那些在从表的任何列上都有拒绝空过滤器的外连接查询才能转换成内连接。
ANSI 内连接向 Informix 内连接的转换
当查询只包含 ANSI 内连接时，我们可以把所有的 ANSI 内连接都转换为 Informix 内连接。因为我们没有并行地执行 ANSI 连接的茂密查询计划树，所以转换可由于并行性而提高性能。另一个好处在于连接顺序优化。Informix 内连接可以有更多的连接顺序选择，这将产生更好的最终查询计划。
例如，服务器可以将以下查询：
SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 = 100 and t1.c3 = t2.c3;
|
转换为
SELECT *
FROM t1, t2
WHERE t1.c1 = t2.c1 and t1.c2 = 100 and t1.c3 = t2.c3;
|
生成计划
我们使用的优化算法是很常用的穷举搜索法。顾名思义，优化器通过查找处理查询的所有可能的方法，穷举搜索计划空间。
在这里，需要对选择率（selectivity）这一术语加以说明。过滤器的选择率是满足条件的行在表或连接的总行数中所占的比例。
请考虑下面这个查询：
SELECT *
FROM tab
WHERE col = 50;
|
假设表 tab 有 1000 行。子句 col = 50 被称为过滤器。过滤器是查询中的条件，用来从表或连接中抽取满足条件的行。假设表 tab 中有 200 行是满足子句 col = 50 的。因此该过滤器的选择率就是：
200 （满足条件的行数）
Selectivity = ------------------------------------- = 0.2
1000 （总行数）
|
请考虑另一个查询：
SELECT *
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
|
假设表 tab1 有 100 行，表 tab2 有 200 行。有 150 行满足连接过滤器。则这个例子中的连接选择率是：
150
Selectivity = ------------ = 0.01
100 * 200
|
选择率在估计查询成本方面起着非常重要的作用。当在查询所涉及的列中存在统计数据时，优化器就获得计算选择率所需的最准确的信息。如果没有统计数据，那么优化器将采用一组试探性缺省值来决定过滤器的选择率。
一旦确定了查询中所有过滤器选择率的值，就找到了扫描查询中每个表的所有可能的方法。如果索引是可用的，假设索引扫描的成本小于顺序扫描的成本，则认为索引优先于整个表的顺序搜索。
表扫描或索引扫描的成本估计源自使用存储在系统目录中的统计信息。这些统计信息是用 UPDATE STATISTICS 语句来更新的。当把查询提交给优化器时，就计算处理 ORDER BY 或 GROUP BY 的成本。如果一个便于使用的索引是可用的，并且它的键与 ORDER BY 或 GROUP BY 中的列匹配，那么就会考虑使用那个索引，而且我们可能会避免一次排序，因为已经对使用该索引所获取的记录进行了排序。
控制优化器的命令
SET OPTIMIZATION
可以使用 SET OPTIMIZATION 命令设置优化的级别，如下所示：
HIGH— 优化器检查所有可能的路径。
LOW— 优化器在每个级别选择成本最低的路径而不考虑其它可能的路径。
FIRST_ROWS— 优化器选择不包括分块操作（如排序或构建散列表）的查询计划。而选择有最小用户响应时间的计划。
ALL_ROWS— 优化器选择执行时间最少且返回所有行的查询计划。
HIGH 和 ALL_ROWS 是缺省优化级别。
通常建议使用 HIGH 这一优化级别，因为它使优化器有完全的控制权来为最适宜的查询性能决定所有可能的计划。但如果查询需要过长的时间才能完成，则建议使用 LOW 这一优化级别。
如果用户响应时间（数据库服务器返回一整屏数据所需时间）在整个查询时间中占较大比例，那么应使用 FIRST_ROWS 优化。
UPDATE STATISTICS
优化器确定执行 SQL 查询的最有效策略。优化器用系统目录表的信息来确定最佳查询策略。
使用 UPDATE STATISTICS 语句更新系统目录。该语句确保提供给优化器的信息是最新的。当表被修改或删除时，数据库服务器并不自动更新系统目录中的相关统计数据。由于收集统计信息对性能的影响，您要决定何时以及如何收集那些信息并且让数据库服务器重新计算 SYSTABLES、SYSDISTRIB、SYSCOLUMNS 和 SYSINDEXES 系统目录表中的数据。对于 MEDIUM 或 HIGH 方式的 UPDATE STATISTICS，SYSDISTRIB 系统目录表保存着已更新数据分布的数据。
UPDATE STATISTICS 也可用来为 SYSPROCPLAN 系统目录表中的过程更新已优化的执行计划。每当过程执行时，如果过程中引用的任何对象更改了，则数据库服务器会重新优化它的执行计划。
当为数据库、表、列或存储过程更新统计信息时，服务器会用查询优化器确定检索查询数据的最低成本存取路径所需的所有信息，来填充系统目录表。
若没有 UPDATE STATISTICS 命令提供的信息，优化器用来做决定的数据就是不准确和不完整的。
可以对整个数据库（包括它所有的表和存储过程）、对单个数据库中的表、仅对指定的表甚至对表中指定的一个或一组列运行 UPDATE STATISTICS：
- UPDATE STATISTICS [LOW|MEDIUM|HIGH] — 更新整个数据库的统计信息。
- UPDATE STATISTICS [LOW|MEDIUM|HIGH] FOR TABLE [tabname] — 更新特定表及其索引的统计信息，如果没有指定表名，则更新所有表和索引的统计信息。
- UPDATE STATISTICS [LOW|MEDIUM|HIGH] FOR TABLE tabname (colname) — 更新指定的具体列的统计信息。
UPDATE STATISTICS 的方式
有三种方式的 UPDATE STATISTICS：
- LOW — 收集表、索引和列的统计信息，但不构建分布。
- MEDIUM — 收集与 LOW 相同的统计信息；还收集表、索引和列的统计信息并且构建表示值采样准确率为 85% 到 99% 的分布块（bin）。
- HIGH — 收集与 LOW 相同的统计信息；还收集表、索引和列的统计信息并且构建表示确切数据值的数据分布块。
当 UPDATE STATISTICS 运行于 HIGH 或 MEDIUM 方式时，数据库服务器检查指定列的内容并将它们分解为块，这些块反映数据的百分比。例如，一个块可能保存百分之二的数据，这意味着 50 个块可以保存所有数据。要设置块的大小，可使用 RESOLUTION 百分比参数。
RESOLUTION 以百分比的形式表示值。例如，RESOLUTION 为 0.5 表示每个块中的数据在整个数据中占 0.5%。因此我们总共用 200 个块表示数据，每个块保存 0.5% 的数据。
如果没有在 UPDATE STATISTICS 命令中指定 RESOLUTION 子句，那么缺省百分比是 0.5。
UPDATE STATISTICS MEDIUM 和 UPDATE STATISTICS HIGH 语句创建列的数据分布。在 HIGH 方式中，读取的是表中所有的行，而在 MEDIUM 方式中，行是作为样本被读取的。MEDIUM 语句将对一组列值的样本排序，然后将分布数据填充到代表该样本的 SYSDISTRIB 表中。HIGH 语句将对所有的列值排序，并在执行时用表中所有值的准确分布信息来填充 SYSDISTRIB 表。对于大型表，HIGH 方式在执行 UPDATE STATISTICS 时使用的资源和时间比 MEDIUM 方式的采样方法更多。但是，MEDIUM 方式不及 HIGH 方式准确。
不对 TEXT 或 BYTE 列创建分布。
Update Statistics 指导原则
在执行 UPDATE STATISTICS 命令时，为了确保获得最佳的统计信息以取得最适宜的查询性能并使开销最低，应按顺序遵守以下指导原则：
运行 UPDATE STATISTICS MEDIUM……只对每个表使用分布。
- 对每个索引的第一列运行 UPDATE STATISTICS HIGH。不要忘记主键和外键约束是通过索引实现的。对每一列单独执行 UPDATE STATISTICS 语句。接下来的步骤是区分多列索引所需的。
- 如果两个多列索引以同一列子集开始，则对首个不同的列运行 UPDATE STATISTICS HIGH。例如，假设有以下表和索引定义：
CREATE TABLE TAB1 (a INT, b INT, c INT, d INT, e INT);
CREATE INDEX ix1 ON TAB1 (a, b, c, d);
CREATE INDEX ix2 ON TAB1 (a, b, e, f);
|
则应对列 c 和 e 运行 UPDATE STATISTICS HIGH。在步骤 2 中，可对每一列执行一个 UPDATE STATISTICS 语句。
- 对每个多列索引中的所有列运行 UPDATE STATISTICS LOW。可以在一个 UPDATE STATISTICS 语句中包含所有的列。可能要省略步骤 3 中更新的列，因为 HIGH 语句会填充将由 LOW 语句生成的统计信息。
要点：这些语句的顺序很重要。如果在 UPDATE STATISTICS HIGH 命令之后运行 UPDATE
STATISTICS MEDIUM，则 MEDIUM 分布将覆盖 HIGH 分布。
用于执行 UPDATE STATISTICS 的步骤旨在尽可能有效地使用机器资源以取得最快的性能。因为更新小型表的开销非常小，所以不需要太多地考虑性能和资源利用。对于相对较小的表，您会发现只执行一个 UPDATE STATICTIS HIGH 语句是可接受的，例如：
UPDATE STATISTICS HIGH FOR TABLE small_table; |
有关使用 UPDATE STATISTICS 的更多信息，请参阅 John F. Miller III 撰写的标题为“Understanding and Tuning Update Statistics”的 Informix Developer 专区文章，其 URL 为
http://www7b.software.ibm.com/dmdd/zones/informix/library/techarticle/miller/0203miller.html。
使用 DBSCHEMA 显示分布信息
使用 DBSCHEMA 实用程序来显示分布和溢出信息。要显示为数据库中某个表所存储的分布信息，可使用 –hd 选项加上该表的名称。如果指定关键字 ALL 而不是表名，则显示数据库中所有表的分布信息。
dbschema -hd [owner.]tablename |
DBSCHEMA 的输出
以下是 DBSCHEMA 实用程序的输出示例：
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.30.UC3
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number RDS#N000000
{
Distribution for samar.t1.c1
Constructed on 02/17/2000
High Mode, 10.000000 Resolution
--- DISTRIBUTION ---
( 22)
1: ( 6, 4, 28)
2: ( 6, 3, 31)
3: ( 6, 5, 40)
4: ( 6, 4, 56)
5: ( 4, 3, 60)
--- OVERFLOW ---
1: ( 5, 25)
2: ( 4, 32)
3: ( 4, 50)
4: ( 5, 52)
5: ( 6, 55)
6: ( 4, 60)
}
|
读取 DBSCHEMA 输出
分布信息的 DBSCHEMA 输出有以下几个部分：
- 分布描述
DBSCHEMA 输出的第一部分描述了为指定表创建了哪些数据分布。
- 分布信息
分布信息描述了为分布创建的块，表和每个块中值的范围以及每个块中不同值的数目。
- 溢出信息
DBSCHEMA 输出的最后部分显示了重复出现多次的值。指明的值的重复次数必须大于某个临界数量，这个临界数量大约是行数乘以 RESOLUTION 的 25%。
使用 SET EXPLAIN 了解优化路径
使用 EXPLAIN 实用程序来了解优化器所选择的路径。执行 SET EXPLAIN ON 语句后，数据库服务器生成优化器为执行查询所选择的路径，并将结果存储在文件 $PWD/sqexplain.out 中。如果当前数据库是远程的，则 sqexplain.out 文件存储在远程主机上该用户的 home 目录中。SET EXPLAIN 缺省设置为 OFF。
SQEXPLAIN 文件的各个部分
SET EXPLAIN 输出包含以下信息。为清楚起见，我们在这里将它分为不同的部分：
第 I 部分
第 I 部分包含以下内容，它们的顺序与以下列出的顺序相同：
- 用于查询的 SQL 语句。
- 以优化器用来比较计划的单位表示的查询成本估计值。这些单位代表查询执行的相对时间，每个单位大约相当于一次典型的磁盘存取时间。优化器选择某个查询计划是因为执行这个计划的估计成本在所有评估的计划中是最低的。
- 期望查询产生的行数估计。
- 执行查询所派生的最大数量的线程（如果设置了 PDQPRIORITY 的话）。
- 用于执行 ORDER BY 和／或 GROUP BY （如果需要的话）的临时文件可选项。
第 II 部分
表在这里是按访问它们的顺序列出的。对于每个表，列出了所应用的过滤器。
- 已访问表的名称
- 数据库服务器读取表所采取的存取计划 — 顺序扫描、索引路径和自动索引。另外，如果表是分段的，则在这里列出对于这一特定查询所要访问的活动分段。
- 列出了每对表的连接计划：嵌套循环连接或动态散列连接。对于动态散列连接也列出了执行散列连接所用到的过滤器。
第 III 部分
根据所选存取计划的不同，这一部分的内容会有所不同。对于第 II 部分中的每一个表，这一部分会出现一次。
对于顺序扫描
本部分包含要应用的过滤器（如果有的话）。如果子查询是过滤器的一部分，会在这里扩展它，它会象主查询一样包括所有的部分。
对于索引扫描和自动索引扫描
对于索引和自动索引扫描，这一部分包含以下信息：
- 一些索引键，将对它们应用过滤器，跟着是以下项中的一个或全部：
- 仅键项，如果它是只用到键的索引扫描。
- 聚合项，如果查询聚合了索引键。
- 键优先项，如果对索引键应用键优先过滤器。
- 下限索引过滤器（如果有的话）。如果子查询是过滤器的一部分，会在这里扩展它，它会象主查询一样包括所有的部分。
- 上限索引过滤器（如果有的话）。如果子查询是过滤器的一部分，会在这里扩展它，它会象主查询一样包括所有的部分。
- 要应用的键优先过滤器（如果有的话）。如果子查询是过滤器的一部分，会在这里扩展它，它会象主查询一样包括所有的部分。
EXPLAIN 输出的示例
示例 1.在这一示例中，empname 上没有索引；需要一个临时文件来处理排序：
QUERY:
----------
select * from employee order by empname
Estimated Cost: 3
Estimated # of Rows Returned: 12
Temporary Files Required For: Order By
1) informix.employee: SEQUENTIAL SCAN
|
示例 2.在这一示例中，断言在一个有索引的列上，设置了起始键（下限索引过滤器），并且索引出现在 locnum 上。不需要用于排序的临时文件。
QUERY:
------
select * from location where locnum > 50 order by locnum
Estimated Cost: 10
Estimated # of Rows Returned: 4
1) informix.location: INDEX PATH
(1) Index Keys: locnum
Lower Index Filter: informix.location.locnum = 50
|
示例 3.这一示例演示了仅键索引搜索，因为投影的所有列都在索引中，并且索引是按聚合的列排序的：
QUERY:
------
select max(deptnum) from department
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.department: INDEX PATH
(1) Index Keys: deptnum (Key-Only) (Aggregate)
|
示例 4.这一示例是关于分段表的，该分段表在一个列中有重叠和非连续的分段。一个等号断言消除了所有分段：
QUERY:
----------
select * from fragtab2 where col = 180
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.fragtab2: SEQUENTIAL SCAN (Serial, fragments: NONE)
Filters: informix.fragtab2.col = 180
|
示例 5.这是一个查询路径，它是为一个带子查询的更新语句生成的，并且断言在未使用索引的列上。
QUERY:
----------
update location set locnum = (select max(deptnum) from department)
where locname = "Chicago"
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.location: SEQUENTIAL SCAN
Filters: informix.location.locname = 'Chicago'
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.department: INDEX PATH
(1) Index Keys: deptnum (Key-Only) (Aggregate)
|
示例 6.这一示例是关于分段表的，该分段表在消除分段的单一列范围断言上且有不重叠的分段。
QUERY:
----------
select * from fragtab where col >= 50 and col <= 150
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) informix.fragtab: SEQUENTIAL SCAN (Serial, fragments: 0, 1)
Filters: (informix.fragtab.col >= 50 AND informix.fragtab.col <= 150
)
|
示例 7.这一示例中有一个分段表，该分段表在消除分段 2 的单一列范围断言上且有不重叠的分段。分段的扫描是并行完成的，因为 PDQPRIORITY >0。PDQPRIORITY 不影响分段的消除。Maximum Threads 指明要生成的线程数（在本例中，两个分段要用到两个扫描线程）。请注意并行情况的成本与顺序情况的成本有何不同。
QUERY:
----------
select * from fragtab where col < 150
Estimated Cost: 2
Estimated # of Rows Returned: 3
Maximum Threads: 2
1) informix.fragtab: SEQUENTIAL SCAN (Parallel, fragments: 0, 1)
Filters: informix.fragtab.col < 150
|
示例 8.在本示例中，我们将一个分段表（在单一列中且有不重叠分段）与另一个分段表（在单一列中有重叠和非连续分段）连接。在第一个表中有范围断言。需要一个临时文件来处理 Group By 排序，处理是并行执行的，生成了五个扫描线程来读取第一个表的两个分段和第二个表的三个分段
QUERY:
----------
select a.col, count(*) from fragtab a,fragtab2 b
where a.col < 150 and a.col = b.col group by a.col
Estimated Cost: 7
Estimated # of Rows Returned: 1
Maximum Threads: 5
Temporary Files Required For: Group By
1) informix.a: SEQUENTIAL SCAN (Parallel, fragments: 0, 1)
Filters: informix.a.col < 150
2) informix.b: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: informix.b.col < 150
DYNAMIC HASH JOIN
Dynamic Hash Filters: informix.a.col = informix.b.col
|
示例 9.这一示例演示了一个嵌套循环连接（外表的索引扫描和内表的索引扫描）：
QUERY:
----------
select empname,deptname from employee e,department d
where e.empdept = d.deptnum and e.empnum >= 100 and d.deptnum <= 80
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.e: INDEX PATH
Filters: informix.e.empdept <= 80
(1) Index Keys: empnum
Lower Index Filter: informix.e.empnum >= 100
2) informix.d: INDEX PATH
(1) Index Keys: deptnum
Lower Index Filter: informix.d.deptnum = informix.e.empdept
|
示例 10.这一查询将由远程服务器处理：
QUERY:
----------
select * from tempdb@online50:temptab
Estimated Cost: 2
Estimated # of Rows Returned: 10
1) tempdb@online50:informix.temptab: REMOTE PATH
Remote SQL Request:
select x0.col from tempdb:"informix".temptab x0
|
优化器伪指令
优化器伪指令段指定了一些关键字，可以用它们部分或完全地指定优化器的查询计划。优化器伪指令在 SQL 查询中位于 SELECT、UPDATE 或 DELETE 关键字之后。
以下是用于指定伪指令的语法：
- 双破折号（--）前缀。
- 用大括号（{}）括起。
- C 语言风格的注释，正斜线和星号（/* */）。
要指定多个伪指令，可用空格、逗号或任何字符来分隔它们。伪指令注释中的第一个字符始终是加号（+）。
伪指令类型
为了影响优化器所作的查询计划选择，可改变查询的四个方面：
存取计划伪指令
存取计划是数据库服务器用来访问表的方法。数据库服务器可以按顺序读取表（全表扫描）或使用表的任何一个索引。以下伪指令会影响存取计划：
-
INDEXindexname[,indexname] — 用这个伪指令所指定的索引来访问表。如果伪指令列出了多个索引，优化器会选择产生的成本最低的索引。
-
AVOID_INDEXindexname[,indexname] — 不要使用由该伪指令列出的任何索引。可与 AVOID_FULL 伪指令一起使用。
-
FULL— 执行完全表扫描。
-
AVOID_FULL— 不要对所列出的表执行全表扫描。可与 AVOID_INDEX 伪指令一起使用。
在有些情况下，强制某种访问方法可以改变优化器选择的连接方法。例如，使用 AVOID_INDEX 伪指令排除某个索引可以让优化器选择散列连接而不是嵌套循环连接。
连接顺序伪指令
连接顺序伪指令 ORDERED 强制优化器按照 SELECT 语句所列的顺序来连接表。
连接顺序对于连接计划的影响
指定连接顺序不只对如何连接表有影响。例如，请考虑下面的查询：
SELECT --+ORDERED, AVOID_FULL(e)
* FROM employee e, department d
WHERE e.dept_no = d.dept_no AND e.salary > 5000
|
在这一示例中，优化器选择用散列连接来连接表。但是，如果所安排的顺序中第二个表是 employee（而且必须通过索引访问），那么使用散列连接是行不通的。
SELECT --+ORDERED, AVOID_FULL(e)
* FROM department d, employee e
WHERE e.dept_no = d.dept_no AND e.salary > 5000;
|
优化器在这一示例中选择的是嵌套循环连接。
使用视图时的连接顺序
当使用视图时，有两种情况会影响连接顺序：
- ORDERED 伪指令位于视图内。
在视图内的 ORDERED 伪指令只会影响视图内的表的连接顺序。必须连续地连接视图中的表。请考虑以下的视图和查询：
CREATE VIEW emp_job_view as
SELECT {+ORDERED}
emp.job_num, job.job_name
FROM emp, job
WHERE emp.job_num = job.job_num;
SELECT * from dept, emp_job_view, project
WHERE dept.dept_no = project.dept_num
AND emp_job_view.job_num = project.job_num;
|
ORDERED 伪指令指定表 emp 在表 job 之前。该伪指令不会影响表 dept 和表 project 的顺序。因此，以下是所有可能的连接顺序：
q emp, job, dept, project
q emp, job, project, dept
q project, emp, job, dept
q dept, emp, job, project
q dept, project, emp, job
q project, dept, emp, job
|
- ORDERED 伪指令位于包含视图的查询内。
如果 ORDERED 伪指令出现在包含视图的查询中，那么查询中表的连接顺序与它们在 SELECT 语句中列出的顺序相同。对于视图内的表则按照它们在视图内列出的顺序进行连接。
在以下查询中，连接顺序是 dept、project、emp 和 job：
CREATE VIEW emp_job_view AS
SELECT emp.job_num, job.job_name
FROM emp, job
WHERE emp.job_num = job.job_num;
SELECT {+ORDERED}
* FROM dept, project, emp_job_view
WHERE dept.dept_no = project.dept_num
AND emp_job_view.job_num = project.job_num;
|
这一规则有例外，即当视图无法包括到查询中时，如下面的例子所示：
CREATE VIEW emp_job_view2 AS
SELECT DISTINCT
emp.job_num, job.job_name
FROM emp,job
WHERE emp.job_num = job.job_num;
|
在这一示例中，数据库服务器执行查询并将结果放置在一个临时表中。该查询中表的顺序是 dept、project 和 temp_table。
连接计划伪指令
连接计划伪指令影响数据库服务器连接查询中两个表的方式。
以下伪指令会影响两个表之间的连接计划：
优化目标伪指令
可以针对以下任意一种情况用优化目标伪指令来优化查询：
-
FIRST_ROWS— 选择这样的计划：对只查找满足查询的第一行这一过程进行优化。
-
ALL_ROWS— 选择这样的计划：对查找满足查询的所有行（缺省行为）这一过程进行优化。
使用 FIRST_ROWS 伪指令，如果查询计划中包含非常耗时的预先活动，则优化器可能会放弃该查询计划。例如，散列连接可能要花去过多的时间来创建散列表。如果只有几行是必须返回的，那么优化器可能会选择嵌套循环连接。
在以下的示例中，假设数据库在 employee.dept_no 上有索引，但在 department.dept_no 上没有索引。如果没有伪指令，则优化器会选择散列连接。
SELECT *
FROM employee, department
WHERE employee.dept_no = department.dept_no
|
但是，如果使用 FIRST_ROWS 伪指令，由于创建散列表需要较高的初始开销，优化器会选择嵌套循环连接。
SELECT {+first_rows} *
FROM employee, department
WHERE employee.dept_no = department.dept_no
|
分布式查询处理
分布式处理所需要的基本操作是：
- 远程目录信息的读取
- 元组的远程获取
- 更新、插入和删除的远程执行
- 函数和过程的远程执行。
当在查询中首次引用远程表时，要从远程系统目录而不是本地系统目录中读取消息表、列、索引和权限信息。优化器随后可以使用这些信息来优化查询。
分布式查询优化器在两个方面与常规查询优化器有所不同。首先，它考虑了涉及到网络操作这一事实。因此，它把通过网络移动数据的成本包括在它的成本公式中。这一成本是基于必须发送的数据量以及发送数据所需信息包的数目计算出来的。
分布式查询优化器的第二个不同之处在于执行远程连接的能力。下表概括了各种优化选择：
- 外表与内表都是本地的。这里唯一合理的选择是在本地执行连接。
- 外表是本地的，内表是远程的。这里有两种选择。优化器对两者都加以考虑，然后选择成本较低的一种。
- 将内表移到本地位置的临时表中，然后在本地执行连接。
- 将外表的连接值发送到远程位置，然后远程执行连接。
- 外表是远程的，内表是本地的。将数据实时地从外表移到本地位置，然后在本地执行连接。
- 外表与内表都在远程位置。
- 一种选择是将两个表都移到本地位置，然后在本地执行连接。必须将内表写到临时表中。
- 另一种选择是将外表实时地移到本地位置，将合适的连接值发送到远程位置，然后远程地执行连接。
- 如果两个表都在同一远程位置，则第三种选择是远程地执行连接。
视图处理
视图是基于处理它们的方法分类的。视图可以是简单的，也可以是复杂的。
简单视图
简单视图是那些不包含聚合、GROUP BY 子句、DISTINCT 子句或外连接的视图。通过将视图的组件包括到引用该视图的 SQL 语句来处理这些视图。例如：
CREATE VIEW view1(vcol1, vcol2) AS
SELECT col1, col2 FROM tab2
WHERE col3 = 10;
SELECT t.tcol1, v.* FROM tab1 t, view1 v
WHERE t.tcol2 = v.vcol2;
|
如下所示，视图被包括到 SELECT 语句：
SELECT t.tcol1, v.col1, v.col2 FROM tab1 t, tab2 v
WHERE t.tcol2 = v.col AND v.col3 = 10;
|
视图内的表被包括到查询的 FROM 子句。视图的相关列被包括到查询的投影列表，视图的 WHERE 子句被包括到查询的 WHERE 子句。
复杂视图
复杂视图是那些在投影列表中包含聚合、有外连接、有 GROUP BY 子句或有 UNION 子句的视图。只有通过执行视图定义中的查询、在临时表中对结果排序、然后在查询的余下部分引用临时表，才能对复杂视图进行选择和处理。例如：
CREATE VIEW view1(vcol1, vcol2) AS SELECT col1, SUM(col3) FROM tab2
WHERE col2 > 10
GROUP BY tab2.col1;
SELECT t.tcol1, v.* FROM tab1 t, view1 v
WHERE t.tcol2 = v.vcol1;
|
在这一示例中，视图定义中的 SELECT 语句得以执行，而且结果被存储在临时表中，然后在选择查询中引用该临时表。
分段消除
可基于分布模式跨数据库空间（dbspace）对表及其索引进行分段。分段使引擎能支持查询的并行执行。分段分布模式包括：
- 按轮循分段
- 按表达式分段
- 按散列分段
- 按多种模式的混合分段
根据分段策略和查询的选择标准，有可能确定从查询执行中可以消除哪些分段。以下示例演示了如何创建按表达式分段的表以及从该表进行选择：
CREATE TABLE tab1
(col1 INT ,
col2 INT)
FRAGMENT BY EXPRESSION
col1 < 100 IN dbspace1,
col1 >= 100 IN dbspace2,
col1 >= 200 IN dbspace3;
SELECT * FROM tab1
WHERE col1 > 150;
|
在上面的示例中，只需要扫描 dbspace2 和 dbspace3 中表的分段。消除了 dbspace1 分段，因为它不符合选择查询标准。此外，如果启用了 PDQ，那么可通过多个扫描线程来并行完成分段的扫描。
关于分段表和索引的成本计算基于以下三点假设：
- 如果设置了 PDQ，则并行（即使用并行线程）扫描表或索引的所有分段。如果没有设置 PDQ，则按顺序扫描所有必需的分段，并且将象处理非分段表那样处理分段表（除了可以在分段表中进行分段消除以外）。
- 成本主要由扫描最大分段的成本来决定；也就是说，扫描分段表或索引所用时间就是扫描最大分段所用时间。换句话说，扫描的调用者必须等待，直到对最大分段扫描的完成。
- 实际的分段模式（轮循或表达式）无关紧要。一旦完成分段消除并确定了最大分段的大小，优化器就会忽略分段模式以便进行成本估计。
结束语
本文旨在对 IBM Informix Dynamic Server 优化器做一个概述。有关调优与优化器有关的不同参数的更多信息，请参阅 Performance Guide for IBM Informix Dynamic Server。该手册较好地描述了如何提高系统总体吞吐量以及如何改进 SQL 查询的性能。
作者简介  | | |
Samar DesaiSamar Desai 作为研发工程师在 Informix Software（印度）工作了 3 年。他作为研发工程师在 Informix Software（美国）工作了 6 个月，在过去一年半的时间里他作为研发工程师为 IBM 工作。目前他正在从事 IBM Informix Dynamic Server SQL 组件的研发工作，主要在 SQL 优化器方面。
|
| | |
Nita Dembla
Nita Dembla 作为研发工程师在 Informix Software（印度）工作了一年半。她作为研发工程师在 Informix Software（美国）工作了 6 个月，在过去一年半的时间里她作为研发工程师为 IBM 工作。目前她正在从事 IBM Informix Dynamic Server SQL 组件的研发工作，主要在 SQL 优化器方面。
|
对本文的评价
| 
