|
优化过程 优化可分为两个步骤： 改写查询
生成计划
改写查询 有时只需通过对查询做些修改并确保两个查询（最初的和改写的）产生的行集合保持相同，就可以减少查询的响应时间。服务器在内部确定是否有可能改写查询并且使改写后查询的输出方式与最初查询的输出相同。这是优化过程中的第一步。这一步骤完全是基于试探法，并且此时还没有出现成本计算。
以下几个示例说明了基于试探法的查询改写的重要性。 示例 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;
将产生以下输出： c1 c2
10 10
20 20
ANSI 外连接特性使您能使用局部 ANSI 外连接语法，包括 ON 子句支持（它使查询能够指定在连接后的过滤器之前求值的过滤器）；跟在它后面的 WHERE 子句（如果有的话）使查询能指定连接后的过滤器。还支持使用左外连接子句的查询。 SELECT *
FROM t1, outer t2
ON (t1.c1 = t2.c2)
WHERE t1.c1 < 30;
以上查询将产生下列输出： c1 c2
10 10
20 20
连接的实现 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; |
