通过 UPDATE STATISTICS 充分利用 Informix Dynamic Server 优化器
资料简介：
通过 IBM® Informix® Dynamic Server（IDS）中的 UPDATE STATISTICS 语句充分利用数据库优化器。阅读本文对这个 SQL 语句的简述，了解如何用它解决各种不同的问题。发现更新统计信息的重要性，并了解如何收集统计信息。最后，浏览本文最后的 FAQ 小节，寻找您对这个重要的 SQL 语句所存疑问的答案。
简介
UPDATE STATISTICS 是一个专用 Informix SQL 命令，它通过分析数据并将该信息存储在系统编目中，更新关于每个表以及它的列的实际信息，这些信息可用于估计随后查询的成本。要真正理解 UPDATE STATISTICS 的重要性，您需要理解当用户输入一个要执行的 SQL 查询时，到底会发生什么事情。输入的每个 SQL 查询都必须被解析、优化和执行。
优化器是用于准备查询计划的组件。理想情况下，查询计划是执行给定查询的最佳计划 — 也就是说，它会确定抓取数据的最佳方式。为此，它使用一个统计数据集合；然而，这种统计数据并不一定是准确的。这种数据的准确性取决于很多因素，例如采用的抽样算法的类型、抽样的数量和数据的歪斜情况。
查询优化器不会自动重新计算表的配置文件。在某些情况下，收集统计信息需要的时间可能比执行查询的时间更长。为确保优化器选择的查询计划能够最好地反映表的当前状态，应定期运行 UPDATE STATISTICS。
UPDATE STATISTICS 的工作原理
初次装载数据和创建索引之后，应该运行 UPDATE STATISTICS。此外，每当对数据库表作出重大更改，包括大规模的插入、更新或删除时，也应该运行该命令。如果没有运行 UPDATE STATISTICS，则优化器只能使用不准确的数据来确定访问路径。
现在，学习该命令的工作原理。 查询优化器根据从每个表检索的行数估计查询成本。而估算的行数取决于 WHERE 子句中使用的每个条件表达式的选择率。过滤器 是一个条件表达式，用于选择行。选择率是介于 0 到 1 之间的一个值，表示表中能通过过滤器的行所占的百分比。对于只通过很少行的选择率过滤器，它的选择率趋向于 0，对于能通过几乎所有行的过滤器，它的选择率趋向于 1。
优化器可以使用数据分布来计算查询中过滤器的选择率。但是，如果没有数据分布，则数据库服务器根据表索引计算不同类型的过滤器的选择率。
选择率估值的准确性对每个执行计划的成本有很大影响。因此，获得最佳计划的准确性完全取决于关于查询中所涉及的数据库对象的最新统计信息。
每当运行 UPDATE STATISTICS 查询时，以下系统编目表都会被刷新。每个标题下列出了表中的列。 在 SYSTABLES 中nindexes - 表中索引的数量nrows - 表中的行数npused - 用于存储 Tupule 的 ‘nrows’ 的页数在 SYSCOLUMNS 中colmin - 列的次小值colmax - 列的次大值在 SYSINDEXES 和 SYSFRAGMENTS 中levels - B-树中的级数leaves - B-树中包含的叶子的数量nunique - 不同值的数量clust - 与表相关的集群度在 SYSDISTRIB 中包含用户数据表的值的分布信息的行。（因此所有列都受影响）它还维护分布的分辨率和置信度
UPDATE STATISTICS 的模式
有三种模式可用于更新统计数据：low、medium 和 high。
low 模式 只填充表的标量统计值（也就是说，没有分布信息）。这种模式存储诸如 B-树索引的级数、表所占用的页数、一个列中不同值的数量之类的信息。
在medium 模式 下，除了 low 模式下的统计信息外，该语句还存储一组列值样本，并将分布数据填充到表示该样本的 sysdistrib 系统编目表中。它的置信度通常介于 85% 到 99%。
在high 模式 下，除了 low 模式下的统计信息外，该语句还对所有列值进行排序，并将执行时表中所有值上的确切分布信息填充到 sysdistrib 表中。看上去，以 high 模式使用 UPDATE STATISTICS 总是不错，然而事实并非如此。由于其分辨率只有 0.5，high 模式需要使用非常多的容器才能获得较高的准确性。因此，它需要消耗大量的磁盘空间，当表比较大时这一点尤为明显。而且，在连续运行的生成系统中，以 high 模式运行 UPDATE STATISTICS 显得过于密集。此外，high 模式并非总能保证得到完美的估计，因为既然计划是以估计为基础的，就不能保证执行计划是 100% 最优的。
UPDATE STATISTICS 和直方图
Informix 使用直方图存储数据的分布信息。直方图用于计算谓词的选择率。它们被证明可以产生低误差的估计，并且几乎不占用运行时开销。
直方图以图形的方式总结和显示一个数据集的分布。Informix 使用带溢出桶（overflow bucket）的高度平衡直方图（即等高/等深），而不是宽度平衡直方图（等宽）。高度平衡意味着每个桶中的值的数量是相同的。根据这些值确定一个范围，一个桶代表一个范围。下面是直方图的一个例子：
直方图
假设一个表中有 1000 行。如果桶的数量固定为 10，则比例为 1000/10；因此每个桶中有 100 行。这个 100 表示高度。当使用高度平衡直方图时，这个值（100）是固定不变的。
服务器为表中的各个列生成数据分布，这些数据分布被以编码直方图的形式存储在 sysdistrib 系统编目的 encdat 列中。如果列中的数据是均匀地分布在各个值域的，那么默认的容器数量可能就足够了。但是，如果数据高度歪斜，那么就需要更多的容器（更小的分辨率），以确保数据不会太歪斜。
UPDATE STATISTICS 的魔术
基本上，只要以 medium 或 high 模式运行 UPDATE STATISTICS 命令，都会创建两种类型的容器，即分布容器和溢出容器。容器中的每个条目由以下内容组成： 分布容器容器中的行数容器中不同值的数量容器中的最大数据值溢出容器数据值数据值出现的频率
简介
UPDATE STATISTICS 是一个专用 Informix SQL 命令，它通过分析数据并将该信息存储在系统编目中，更新关于每个表以及它的列的实际信息，这些信息可用于估计随后查询的成本。要真正理解 UPDATE STATISTICS 的重要性，您需要理解当用户输入一个要执行的 SQL 查询时，到底会发生什么事情。输入的每个 SQL 查询都必须被解析、优化和执行。
优化器是用于准备查询计划的组件。理想情况下，查询计划是执行给定查询的最佳计划 — 也就是说，它会确定抓取数据的最佳方式。为此，它使用一个统计数据集合；然而，这种统计数据并不一定是准确的。这种数据的准确性取决于很多因素，例如采用的抽样算法的类型、抽样的数量和数据的歪斜情况。
查询优化器不会自动重新计算表的配置文件。在某些情况下，收集统计信息需要的时间可能比执行查询的时间更长。为确保优化器选择的查询计划能够最好地反映表的当前状态，应定期运行 UPDATE STATISTICS。
UPDATE STATISTICS 的工作原理
初次装载数据和创建索引之后，应该运行 UPDATE STATISTICS。此外，每当对数据库表作出重大更改，包括大规模的插入、更新或删除时，也应该运行该命令。如果没有运行 UPDATE STATISTICS，则优化器只能使用不准确的数据来确定访问路径。
现在，学习该命令的工作原理。 查询优化器根据从每个表检索的行数估计查询成本。而估算的行数取决于 WHERE 子句中使用的每个条件表达式的选择率。过滤器 是一个条件表达式，用于选择行。选择率是介于 0 到 1 之间的一个值，表示表中能通过过滤器的行所占的百分比。对于只通过很少行的选择率过滤器，它的选择率趋向于 0，对于能通过几乎所有行的过滤器，它的选择率趋向于 1。
优化器可以使用数据分布来计算查询中过滤器的选择率。但是，如果没有数据分布，则数据库服务器根据表索引计算不同类型的过滤器的选择率。
选择率估值的准确性对每个执行计划的成本有很大影响。因此，获得最佳计划的准确性完全取决于关于查询中所涉及的数据库对象的最新统计信息。
每当运行 UPDATE STATISTICS 查询时，以下系统编目表都会被刷新。每个标题下列出了表中的列。 在 SYSTABLES 中nindexes - 表中索引的数量nrows - 表中的行数npused - 用于存储 Tupule 的 ‘nrows’ 的页数在 SYSCOLUMNS 中colmin - 列的次小值colmax - 列的次大值在 SYSINDEXES 和 SYSFRAGMENTS 中levels - B-树中的级数leaves - B-树中包含的叶子的数量nunique - 不同值的数量clust - 与表相关的集群度在 SYSDISTRIB 中包含用户数据表的值的分布信息的行。（因此所有列都受影响）它还维护分布的分辨率和置信度
UPDATE STATISTICS 的模式
|
|
有三种模式可用于更新统计数据：low、medium 和 high。
low 模式 只填充表的标量统计值（也就是说，没有分布信息）。这种模式存储诸如 B-树索引的级数、表所占用的页数、一个列中不同值的数量之类的信息。
在medium 模式 下，除了 low 模式下的统计信息外，该语句还存储一组列值样本，并将分布数据填充到表示该样本的 sysdistrib 系统编目表中。它的置信度通常介于 85% 到 99%。
在high 模式 下，除了 low 模式下的统计信息外，该语句还对所有列值进行排序，并将执行时表中所有值上的确切分布信息填充到 sysdistrib 表中。看上去，以 high 模式使用 UPDATE STATISTICS 总是不错，然而事实并非如此。由于其分辨率只有 0.5，high 模式需要使用非常多的容器才能获得较高的准确性。因此，它需要消耗大量的磁盘空间，当表比较大时这一点尤为明显。而且，在连续运行的生成系统中，以 high 模式运行 UPDATE STATISTICS 显得过于密集。此外，high 模式并非总能保证得到完美的估计，因为既然计划是以估计为基础的，就不能保证执行计划是 100% 最优的。
UPDATE STATISTICS 和直方图
Informix 使用直方图存储数据的分布信息。直方图用于计算谓词的选择率。它们被证明可以产生低误差的估计，并且几乎不占用运行时开销。
直方图以图形的方式总结和显示一个数据集的分布。Informix 使用带溢出桶（overflow bucket）的高度平衡直方图（即等高/等深），而不是宽度平衡直方图（等宽）。高度平衡意味着每个桶中的值的数量是相同的。根据这些值确定一个范围，一个桶代表一个范围。下面是直方图的一个例子：
直方图
假设一个表中有 1000 行。如果桶的数量固定为 10，则比例为 1000/10；因此每个桶中有 100 行。这个 100 表示高度。当使用高度平衡直方图时，这个值（100）是固定不变的。
服务器为表中的各个列生成数据分布，这些数据分布被以编码直方图的形式存储在 sysdistrib 系统编目的 encdat 列中。如果列中的数据是均匀地分布在各个值域的，那么默认的容器数量可能就足够了。但是，如果数据高度歪斜，那么就需要更多的容器（更小的分辨率），以确保数据不会太歪斜。
UPDATE STATISTICS 的魔术
基本上，只要以 medium 或 high 模式运行 UPDATE STATISTICS 命令，都会创建两种类型的容器，即分布容器和溢出容器。容器中的每个条目由以下内容组成： 分布容器容器中的行数容器中不同值的数量容器中的最大数据值溢出容器数据值数据值出现的频率
2010-01-07
浏览1600
下载2