内容


通过 Optim Query Tuner 调优 SQL,第 1 部分

了解访问路径

了解如何监视并调整查询和工作负荷以改进应用程序性能

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: 通过 Optim Query Tuner 调优 SQL,第 1 部分

敬请期待该系列的后续内容。

此内容是该系列的一部分:通过 Optim Query Tuner 调优 SQL,第 1 部分

敬请期待该系列的后续内容。

介绍

SQL 在某种意义上来说是一种在程序中只描述感兴趣的数据的声明性语言,而不是为获得数据的算法。因此,经常有多种方法来满足给定的 SQL 语句。这些不同的方法就是众所周知的访问路径访问计划。为简单起见,本文引用了一种满足 SQL 语句的流行方法作为访问路径。虽然相同 SQL 语句的不同访问路径会产生相同的结果集,但是它们很有可能不会通过同样的性能级别来执行任务。SQL 编译器使用查询优化来为任何给定的 SQL 语句选择表现最好的访问路径(当然,这要在合理的时间内)。

如果您是一名开发人员、DBA 或查询调优专家,了解访问路径的基础知识以便您可以在查询和查询工作负荷在生产中导致问题之前精确地调整它们,这对您来说是至关重要的。此基本知识,再加上由 Optim 查询调优解决方案提供的可视化和调优建议,可帮助您更有效地完成此任务。

介绍完访问路径和查询执行以后,本文描述了最常见的访问方法并加入由 DB2® for z/OS 支持的方法。下一步,本文描述了通过 DB2 for z/OS 优化器选择的访问路径并通过访问路径图演示关于访问路径选择的关键信息。本文使用一个示例场景作为结束语来演示如何使用查询注释,通过直接在 SQL 语句中提供重要的统计信息,Optim Query Tuner 的功能会有助于您查询分析。

如果您想有机会亲自与访问路径图互动,则它们包含在示例项目文件中,您可以从本文的 下载部分 获得。您可以导入项目文件到 Data Studio(带有 Fix Pack 1 或更高版本的独立软件包)或任何 Optim Query Tuner 产品。您无需连接数据库来与分析结果互动,这些分析结果在本文中被标记上相应的数字号码。

要导入示例项目需执行以下操作:

  1. 打开独立的 Data Studio 或 Optim Query Tuner 产品的 Data Perspective。
  2. 选择 File > Import....
  3. 在 Import Wizard 中,导航到 Query Tuner > Projects,然后单击 Next
  4. 单击 Browse... 并选择包含已下载 zip 文件的目录。这样一系列项目将会显示在 Projects 窗口中。
  5. 选择 sampleaccesspathproject,并单击 Finish
  6. 示例项目现在应该出现在您的 Project Explorer 中。如果您无法看到 Project Explorer 窗口,请确保您正在使用 Data Perspective 并选择 Window > Reset Perspective。或者,您可以选择 Window > Show View > Project Explorer

关于 Optim 查询调优解决方案

Optim 查询调优解决方案提供一个环境以便通过那些帮助指导您获得解决方案的顾问和工具来识别和调整表现不佳的 SQL 语句。查询调优功能在以下产品中出现:

  • 在独立的 Data Studio 2.2.0.1 版本上可用的基本的、单查询的调优和查询格式功能。本产品可免费用于 DB2 for z/OS 和 DB2 for Linux®, UNIX®, and Windows®。请注意本系列文章中的信息将解释如何使用 Data Studio 来解释访问路径图,并非所有描述的功能都可用于 Data Studio。
  • 可用于 Optim Query Tuner 的单查询调优和查询格式、以及较大的顾问集。此产品可用于 DB2 for z/OS 和 DB2 for Linux, UNIX, and Windows。
  • 可用于 Optim Query Workload Tuner 的查询工作负荷调优、单查询调优和全套顾问。此产品只可用于 DB2 for z/OS(本文编写时)。

为了简便起见,本文章系列使用 Optim Query Tuner 名称来指提供 Optim 查询调优解决方案的顾问和工具集。如果适用,在描述不可用在所有上面列出的产品的功能时将提供特定产品名称。

如何读取访问路径图

访问路径图不但描述 “操作细节”,而且描述数据如何流动。访问路径图的叶节点是代表查询执行计划(图 1 显示每一个这些节点类型的示例)中数据源的表节点、工作文件节点或索引节点。数据从下往上流动(如访问路径图所示)并通过访问路径图上的操作节点处理。表 1 描述通常出现在 Optim Query Tuner 访问路径图上的输入、输出和几个操作节点的功能。

表 1. 显示在 Optim Query Tuner 上的典型操作
操作节点第一个输入第二个输入输出功能描述
IXSCAN索引节点N/A一系列合格的记录标识符 (RID)在给定的索引重点范围内扫描索引以检索合格的记录标识符。
FETCH一系列 RID 表节点一系列合格的记录 提取数据记录和基于 RID 的相应数据页面。如果有任何数据记录和页面,则应用谓词。
TBSCAN表节点 N/A一系列合格的记录按顺序扫描目标表空间以提取数据页面,如果有任何数据页面,则应用谓词。
SORT一系列记录或 RIDN/A 一系列排序的记录或 RID将输入按页码顺序(适用于 RID)或排序键顺序(适用于记录)分类。
WFSCAN工作文件节点 N/A一系列记录工作文件扫描。按顺序扫描目标工作文件表空间以提取数据页面,如果有任何数据页面,则应用谓词。
NLJOIN一系列记录一系列记录一系列记录嵌套循环联接。由于每一个合格的记录都来自于第一个输入(外部),因此扫描第二个输入(内部)以查找匹配的记录并返回该联接记录。
MSJOIN 一系列排序的记录一系列排序的记录一系列记录合并扫描联接。扫描两个输入以查找匹配的记录并返回该联接记录。

访问路径和查询执行

描述访问路径最容易的方法是使用示例。清单 1 中的 SQL 语句联接了三个表以产生销售报告。销售金额的合计基于客户的性别和婚姻状况。

清单 1. 示例三路联接以说明如图 1 所示的访问路径图
select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
from cust_customer c, cust_order_header oh, cust_order_detail od
where c.cust_code = oh.cust_code
  and oh.cust_order_number = od.cust_order_number
  and c.cust_prov_state_code = 'CA'
  add od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by c.gender_code, c.marital_status_code

直观地说,访问路径是查询执行的程序说明,其包括三个组件:

  • 表的联接序列
  • 被扫描的表的算法(访问方法)
  • 被执行的联接操作的算法(联接方法)

图 1 是通过清单 1 所示的适用于查询的 Optim Query Tuner 生成的访问路径图。

图 1. 示例清单 1 的访问路径图(三路联接)
三路联接的访问路径图。图下的文本将提供详细描述。
三路联接的访问路径图。图下的文本将提供详细描述。

(请查看图 1 的 放大版本。)

在查询中被引用的三个表中的每一个表在访问路径图中显示为带有相应表名称的表节点。

在联接序列中领先的表是 CUST_ORDER_DETAIL 表,其通过在 PRODUCT_NUMBER 列上已定义的索引进行扫描。访问路径图中的索引是通过索引节点标示索引名称为 SQT01_CUST_ORDER_DETAIL 来表现的。

直接在 SQT01_CUST_ORDER_DETAIL 索引节点上方的是 IXSCAN 节点。这意味着该索引被禁止获得合格的记录标识符 (RID)。然后通过 FETCH 节点获取合格的 RID 以提取来自表格空间的相应记录和数据页面到 DB2 缓冲池。

联接序列中的第二个表是 CUST_ORDER_HEADER 表,其通过在 CUST_ORDER_NUMBER 列上的索引进行扫描。该访问方法适用于 CUST_ORDER_HEADER,即索引扫描,其与 CUST_ORDER_DETAIL 表使用相同的表现方法。

联接序列中的最后一个表是 CUST_CUSTOMER 表,其也是通过 CUST_CODE 列上已定义的索引进行扫描。

现在您有了一个关于表和如何访问信息(在这种情况下通过索引)的概述,您已准备好仔细查看正在使用的联接方法。访问路径图中的联接节点显示 DB2 优化器在 CUST_ORDER_DETAIL 和 CUST_ORDER_HEADER 之间为第一个联接操作选择了一个嵌套循环联接 (NLJOIN)。联接采用第一个表的结果集 (CUST_ORDER_DETAIL) 和第二个表的结果集 (CUST_ORDER_HEADER) 作为输入。该联接操作的结果就成为第一个联接操作输入到第二个联接操作(也是一个嵌套循环联接)。其在联接序列中联接最后一个表的结果集,即 CUST_CUSTOMER。

了解数据流很重要,因为就查询执行的查询性能方面来说,这是最有影响力的因素。本节的其余部分再一次以数据流的观点向您介绍访问路径图以便您可以在查询执行的性能方面获得充分的理解。

每一个节点上显示的数字都是优化器对基础数据源或操作节点的基数的估计。例如,在索引节点上用于 SQT01_CUST_ORDER_DETAIL 的数字 273 就说明这里有 273 个不同的索引键。CUST_ORDER_HEADER_PK 是一个独特的索引;因此,在相应的表中索引键的数量与记录的数量 (539,526) 是完全匹配的。这对于 IDX_CUST_CUSTOMER 索引和 CUST_CUSTOMER 表同样成立,它们分别带有 31,284 个索引键和记录。

对于那些不是唯一的索引,索引键的数量要少于记录的数量。而且,记录的数量与索引键之间的比例说明,通常,RID 的数量与每个索引键都有关联。在访问路径图中的三个表节点上显示的数字从记录的数量方面说明了表的大小。

以下是在执行嵌套循环联接的第一步时将发生的操作:

  1. 索引 BQT01_CUST_ORDER_DETAIL 上的索引扫描 (IXSCAN) 操作先行,因为它是联接序列中第一个表的第一个操作。基于优化器的估计,在目标索引键范围内有 8 个合格的 RID。
  2. 在表空间上通过提取节点来定位相应的记录和页面以使用这 8 个合格的 RID,其包括 560,273 条记录。因为这里没有通过提取操作应用附加的谓词,所以提取节点的输出基数也是 8 个。这意味着通过提取操作将产生 8 条合格的记录。
  3. 然后用作第一个联接操作的这 8 条合格的记录将立即输入到在 FETCH 节点之上的 NLJOIN 节点。因为嵌套循环联接的工作方式,所以每隔一个外部表记录执行第二步(共 4 个节点)。也就是说,联接操作的内部表将被扫描 8 次,来自外部输入的 8 个合格的记录每个都将被扫描一次。

NLJOIN 节点的第二步类似于第一个表的第二步:

  1. 优化器选择唯一的索引 (CUST_ORDER_HEADER_PK) 来访问 CUST_ORDER_HEADER 表上的记录。
  2. 正如所指出的那样,在 IXSCAN 节点和 FETCH 节点上的数目,每次在 CUST_ORDER_HEADER 上发现记录时都会执行索引扫描。因此,总共 8 条记录将通过 NLJOIN 节点产生。

同样,访问路径图的最后一步要评估 8 次,通过第一个 NLJOIN 节点产生的每条记录都要评估一次。因为唯一的索引选择了 IXSCAN 节点 (IDX_CUST_CUSTOMER),所以只有一条记录将与 CUST_CUSTOMER 表的每一次扫描相匹配。因此,总共 8 条记录会在最终的结果集上产生。

从性能的角度来看,一个良好表现的访问路径相对于最终的结果集应该触及最少的数据量。在上面的例子中,从每个表中提取的记录数量都保持在最小。对于 CUST_ORDER_DETAIL 表,从使用索引扫描的表空间处只能提取 8 条记录。此外,每个进入到两个 NLJOIN 操作的内部输入的探测只与一条记录相匹配。因此,如果优化器的估计是准确的,则此访问路径应该是一个非常有效的访问路径。(本文的 使用查询注释的案例研究 部分描述了如何确定估计是否准确。)

其他访问方法和联接方法

在之前的部分您已经了解了一种类型的访问方法,即索引扫描 (IXSCAN),和一种类型的联接方法,即嵌套循环联接 (NLJOIN)。本部分涵盖了更多的访问方法、联接方法和排序操作。每一个操作的描述都包括一个解释其操作语义的 Query Tuner 访问路径图。

使用阶段 1 和阶段 2 谓词的表空间扫描 (TBSCAN)

清单 2 中的 SQL 语句创建如图 2 上的 Optim Query Tuner 截图右侧所示的访问路径图。

清单 2. 示例查询以说明在图 2 上所示的表扫描
select count(*)
from cust_order_header
where cust_total_quantity > 3
  and cust_sales_tax > cust_total * 0.03
图 2. 使用阶段 1 和阶段 2 谓词的表扫描 (TBSCAN) 访问路径图
使用阶段 1 和阶段 2 谓词的表扫描访问路径图。图下的文本将提供详细描述。
使用阶段 1 和阶段 2 谓词的表扫描访问路径图。图下的文本将提供详细描述。

(请查看图 2 的 放大版本。)

图 2 显示了一个如何从访问路径图显示两个节点(CUST_ORDER_HEADER 表和表扫描 (TBSCAN) 操作)的详细描述的示例。您只要在节点上单击右键并从上下文菜单中选择 Show Description 就可从该访问路径图上的任何节点处获得详细描述。在图 2 中,表节点的描述符和 TBSCAN 节点的描述符显示在访问路径图的左侧。SQL 语句显示在图的底部。

访问路径显示在表 CUST_ORDER_HEADER 上执行一个 TBSCAN 操作。这意味着 DB2 按顺序在表空间上提取每一个数据页面到缓冲池。然后其在缓冲池上扫描每一条记录以便应用阶段 1 谓词。通过阶段 2 谓词过滤被复制到私人内存池的合格的记录(只是有关列)。作为聚合功能的 COUNT 适用于在阶段 2 谓词过滤以后保留的记录。

有关数据流的细节可在 TBSCAN 描述符的属性部分中找到。通过在缓冲池中的 DB2 可扫描 539,526 条记录(TBSCAN 描述符上的 Input Cardinality 值)。DB2 优化器估计 473,785.44 条记录将存在阶段 1 谓词(请查看描述符上的 Stage 1 Returned Rows 属性),同时 4,737.9688 条记录将存在阶段 2 谓词(请查看描述符上的 Stage 2 Returned Rows 属性)。因为 COUNT 功能,所以只产生一条记录。描述符上的 Prefetch 属性包含值 S,这说明顺序预取将在执行时开始以改进从表空间提取数据页面的 I/O 性能。

使用分区修剪的表扫描 (TBSCAN)

清单 3 中的 SQL 语句创建如图 3 所示的访问路径图

清单 3. 示例查询以说明在图 3 中所示的使用分区修剪的表扫描
select count(*)
from cust_order_header
where cust_total_quantity > 3 
  and cust_sales_tax > cust_total * 0.03
  and cust_order_number between 100000 and 580000
图 3. 使用分区修剪的表扫描 (TBSCAN) 的访问路径图
使用分区修剪的表扫描的访问路径图。图下的文本将提供详细描述。
使用分区修剪的表扫描的访问路径图。图下的文本将提供详细描述。

(请查看图 3 的 放大版本。)

正如在表节点描述符上所显示的,在表空间上有 10 个分区,且用于九个分区的极限键是 625,000 个。因为有一个谓词,cust_order_number 处于 100000 到 580000 之间,在分区列上,优化器能够限制 TBSCAN 到头九个分区。在 TBSCAN 描述符中描述了此优化。Page_Ranges 属性包含一个范围,即范围 1,其涵盖分区 1 到分区 9。在执行时,为获得更好的性能,分区 10 被完全跳过。

使用匹配谓词的索引扫描 (IXSCAN)

清单 4 中的 SQL 语句创建如图 4 所示的访问路径图。

清单 4. 示例查询以说明在图 4 中所示的匹配索引扫描
select crdt_method_code, cust_total_quantity, count(*)
from cust_order_header 
where cust_total_quantity - 2 > 1 
  and crdt_method_code > 20 
  and cust_order_date > '2007-01-01-01.24.58.017000' 
group by crdt_method_code, cust_total_quant
图 4. 使用匹配谓词的索引扫描 (IXSCAN) 的访问路径图
使用匹配谓词的索引扫描的访问路径图。图下的文本将提供详细描述。
使用匹配谓词的索引扫描的访问路径图。图下的文本将提供详细描述。

(请查看图 4 的 放大版本。)

在访问路径图中,一个索引扫描通常作为一组四个节点显示在 Query Tuner 上。如图 4 所示,通过一个与使用索引 BQT01_CUST_ORDER_HEADER 相匹配的索引扫描来访问 CUST_ORDER_HEADER,其被定义在列 CRDT_METHOD_CODE 上(通过扩展 IXSCAN 描述符您可以看到比图 4 所示的更广泛一点)。IXSCAN 描述符细节显示这里有一个伴有 7.32% 过滤因子的匹配谓词,其导致在总共 1,558 个索引页面和 539,526 个 RID 中分别有 114 个合格的索引页面和 39,471 个合格的 RID。

FETCH 节点使用这些 RID 来定位表空间内的对应数据页面。一旦提取到 DB2 的缓冲池,将扫描在这些页面上的记录并应用阶段 1 谓词。FETCH 节点的详细描述显示估计有 18,515.602 条记录存在阶段 1 谓词。阶段 2 谓词在这些记录的有关列被复制到 DB2 中的私人缓冲区以后应用。最后,通过 FETCH 节点返回 6,172.508 条记录。

非匹配索引扫描 (IXSCAN)

清单 5 中的 SQL 语句创建如图 5 所示的访问路径图。

清单 5. 示例查询以说明在图 5 中所示的非匹配索引扫描
select count(*) as count
from cust_order_header
图 5. 非匹配索引扫描 (IXSCAN) 的访问路径图
非匹配索引扫描的访问路径图。图下的文本将提供详细描述。
非匹配索引扫描的访问路径图。图下的文本将提供详细描述。

(请查看图 5 的 放大版本。)

索引扫描可以是非匹配的,这意味着在基础索引的扫描上没有限制。也就是说,DB2 扫描每一个单一的叶级页。通常情况下,在基础索引提供好处(如唯一索引访问、避免排序或一些类似的好处)时,可通过优化器选择非匹配索引扫描。

在图 5 所示的例子中,由于聚合功能的原因优化器选择利用非匹配索引扫描,即 count(*),其仅通过扫描索引来评估。此外,索引通常比表小,因此扫描时更有效。由于缺乏匹配的谓词,IXSCAN 节点详细描述显示所有被扫描的 1,680 个叶级页。因为其是一个唯一索引扫描,所以在访问路径图中不显示 FETCH 或表节点。

嵌套循环联接 (NLJOIN)

清单 6 中的 SQL 语句创建如图 6 所示的访问路径图。

清单 6. 示例查询以说明在图 6 中所示的嵌套循环联接
select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
from cust_order_header oh, cust_order_detail od
where oh.cust_order_number = od.cust_order_number
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by oh.cust_code
图 6. 嵌套循环联接 (NLJOIN) 的访问路径图
嵌套循环联接的访问路径图。图下的文本将提供详细描述。
嵌套循环联接的访问路径图。图下的文本将提供详细描述。

(请查看图 6 的 放大版本。)

嵌套循环联接 (NLJOIN) 采取两个数据源作为输入,将外部的(左侧)的记录与内部的(右侧)匹配记录连结在一起使其具有联接谓词的资格,而后将已联接记录作为输出返回。要了解嵌套循环联接的更多信息,请参考 参考资料 部分中针对您的 DB2 平台的适当的信息中心链接。

如图 6 上示例查询的访问路径图所示,来自于外部表 (CUST_ORDER_DETAIL) 的合格记录联接到使用了 NLJOIN 和 EQUAL 联接谓词的内部表 (CUST_ORDER_HEADER) 的合格记录。在执行时,对于从联接外部上的 FETCH 节点流动来的每一条记录,在索引 CUST_ORDER_HEADER_PK 上使用索引扫描 (IXSCAN) 来扫描内部表。

NLJOIN 描述符上的细节信息(也显示在图 6 中)显示优化器的估计:

  • 在应用本地谓词以后,有 8 条合格的记录(外部输入基数值是 8)。
  • 一条内部表的记录将与来自外部表的每条合格的记录相匹配(内部输入基数值是 1)。
  • NLJOIN 节点将最终产生 8 条已联接的记录作为输出(输出基数值是 8)。

从性能的角度来看,这被认为是一个非常有效的联接操作,因为通过贯穿唯一索引的唯一访问扫描内部表的成本是最优的。

使用复合排序的嵌套循环联接 (NLJOIN)

请注意清单 7 中的 SQL 语句与前面部分的清单 6 中的 SQL 语句相同。然而,对于此示例则选择了一个不同的访问路径,如图 7 所示。

清单 7. 示例查询以说明在图 7 中所示的使用复合排序的嵌套循环联接
select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
from cust_order_header oh, cust_order_detail od
where oh.cust_order_number = od.cust_order_number
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by oh.cust_code
图 7. 使用复合排序的嵌套循环联接 (NLJOIN) 的访问路径图
使用复合排序的嵌套循环联接的访问路径图。图下的文本将提供详细描述。
使用复合排序的嵌套循环联接的访问路径图。图下的文本将提供详细描述。

(请查看图 7 的 放大版本。)

在图 7 上图 6 与图 7 之间所不同的是,一个 SORT 节点被添加到联接的外部。这种类型的访问路径被称为使用复合排序 的嵌套循环联接 (NLJOIN)。

以下解释了为什么优化器将添加 SORT 节点。请注意在图 7 中联接的外部 Fetch 节点上所显示的基数之间有一个显著的不同。图 6 上的示例只有 8 条记录,而在图 7 上,优化器估计的合格记录的数量是 8,708.988 条。基数越高,进入内部表、每一条合格的(外部)记录的探测就越多。利用内部表的索引恰好是一个聚集索引。因此,连续探测内部表的顺序可能会显著地影响内部表的 I/O 性能。更确切地说,如果内部表的连续探测是按 CUST_ORDER_NUMBER 顺序进行的,则将跨不同探测顺序地扫描内部索引,反过来,将会给予按顺序的内部表扫描。从更有效的 I/O 处获取的好处超过了大约 8,708 条记录排序的成本。因此,在图 7 中优化器选择排序嵌套循环联接的复合物。

一般来说,如果内部索引是良好的集群且预测内部表被多次探测,则使用复合排序的嵌套循环联将受到优化器的青睐。

使用内部和外部排序的合并扫描联接 (MSJOIN)

清单 8 中的 SQL 语句创建如图 8 所示的访问路径图。

清单 8. 示例查询以说明在图 8 中所示的合并扫描联接
select *
from cust_order_header oh, cust_order_detail od
where oh.cust_total_quantity = od.cust_quantity
  and oh.cust_total = od.cust_unit_price
  and oh.cust_order_date > '2009-01-16-01.00.00.000000'
  and od.product_number > 150000
图 8. 使用内部和外部排序的合并扫描联接 (MSJOIN) 的访问路径图
使用内部和外部排序的合并扫描联接的访问路径图。图下的文本将提供详细描述。
使用内部和外部排序的合并扫描联接的访问路径图。图下的文本将提供详细描述。

(请查看图 8 的 放大版本。)

合并扫描联接也被称为排序合并联接。缩写 MSJOIN 用于 Optim Query Tuner 以表示此联接算法。要了解有关合并扫描联接的更多信息,请参考 参考资料 部分中针对您的 DB2 平台的适当的信息中心链接。

一个合并联接总是涉及一个或多个 Equal 联接谓词。图 8 说明有两个 Equal 联接谓词利用示例查询的合并扫描联接。为了合并扫描联接正确地执行,按照联接列的顺序预计有两个输入。更确切地说,对于联接的外部表 (CUST_ORDER_DETAIL) 来说,按照列 CUST_QUANTITY 和 CUST_UNIT_PRICE 的顺序假设合格的记录。同样地,按照 CUST_TOTAL_QUANTITY 和 CUST_TOTAL 的顺序假设内部表的合格记录。因为通过 TBSCAN 扫描内部表,即不能对 MSJOIN 保证预期的顺序,所以添加 SORT 节点来执行顺序。

如图 8 的 IXSCAN 描述符所示,通过优化器利用的索引的领先列是 PRODUCT_NUMBER,其不为合并扫描联接提供所需的顺序。因此,也要添加 SORT 节点到联接的外部来执行顺序。

如前所述,按顺序的输入对于合并扫描联接是必不可少的,但其无需嵌套循环联接。因此,当与嵌套循环联接比较时,合并扫描联接可能对于排序输入产生成本。然而,合并扫描联接的好处是不必反复地扫描内部表因为其为嵌套循环联接工作。因此,在决定选择哪种联接方法时,优化器为内部表扫描平衡排序成本和性能优势以便促进决策。一件值得有关合并扫描联接注意的事情是通过保持跟踪数据的 “顺序属性”,优化器将积极避免 SORT。例如,如果通过使用 CUST_TOTAL 和 CUST_TOTAL_QUANTITY 作为领先列的索引扫描内部表,则内部表上的 SORT 可能被避免。通常情况下,在外部输入的基数显然不小且对于内部表没有一种有效的访问方法时,优化器会选择合并扫描联接。

使用查询注释的案例研究

本部分说明 Query Tuner 查询注释和访问路径图如何帮助您分析访问路径和 SQL 性能。清单 9 包含相同的三路联接 SQL 语句,其用在 清单 1 上以介绍访问路径和查询执行的概念。

清单 9. 示例查询以说明在图 9 中所示的查询注释功能
select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
from cust_customer c, cust_order_header oh, cust_order_detail od
where c.cust_code = oh.cust_code
  and oh.cust_order_number = od.cust_order_number
  and c.cust_prov_state_code = 'CA'
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by c.gender_code, c.marital_status_code

图 9 说明在 Optim Query Tuner 上的格式化 SQL 语句的查询注释功能以便 FROM 子句上的每个表引用和 WHERE 子句上的每个谓词都采用一个新的行。重新排序和重新组合谓词是基于谓词的种类(本地或联接)和表引用。

图 9. 查询格式和注释示例
显示格式化和注释功能的访问路径图。图下的文本将提供详细描述。
显示格式化和注释功能的访问路径图。图下的文本将提供详细描述。

(请查看图 9 的 放大版本。)

图 9 演示格式化和注释功能如何使基础语句更容易理解。三个表引用(CUST_ORDER_DETAIL、CUST_CUSTOMER 和 CUST_ORDER_HEADER)显示在三个不同的行上。4 个谓词中的每一个也显示在不同的行上,两个本地谓词在两个联接谓词之前显示。

除了方便阅读格式化,您还可以访问提供了关键统计信息的注释以便于您的 SQL 性能的分析。每个表引用的右边是表基数(依据记录的数量和页面的数量)以及合格行的估计数量。

与 QUALIFIED_ROWS 相关的数代表将使基础表引用的所有 本地谓词合格的估计的记录数量。例如,在应用本地谓词以后,CUST_ORDER_DETAIL 表估计将从 560,273 条记录中返回 8.000001 条记录。同样地,31,284 条记录中只有 729.99976 条记录将使本地谓词具有表 CUST_CUSTOMER 的资格。因为没有本地谓词适用于 CUST_ORDER_HEADER,所以所有 539,526 行都将有效。此信息使您了解如何在联结序列上选择一个领导表 — 合格行数量越小,优化器越有可能选择此表作为联接序列上的领导表。

图 1 所示,通过优化器选择表 CUST_ORDER_DETAIL 作为联接序列的领导表的原因是其假设在应用本地谓词以后只产生 8 条记录。在访问路径中,通过完全匹配的唯一索引访问联接序列上的第二个表引用 (CUST_ORDER_HEADER) 和第三个表引用 (CUST_CUSTOMER)。也就是说,进入第二个表引用和第三个表引用的探测只将有效地定位匹配联接条件的记录。因此,只要第一个表引用 (CUST_ORDER_DETAIL) 的 QUALIFIED_ROWS 估计是准确的,则此访问路径实际上可能成为最佳执行之一。

为了获得优化器如何计算 QUALIFIED_ROWS 和如何使其准确的概念,请再次参考 图 9。请注意这里只有一个用于 CUST_ORDER_DETAIL 的本地谓词,其是一个位于列 PRODUCT_NUMBER 上的 IN 清单谓词,在清单中伴有 6 个元素(od.product_number 是 (154110、129170、129150、129110、129140、130130))。

此谓词的查询注释显示谓词过滤因子 (FF) 是 0.000014,或 0.0014%。也就是说,将限定此谓词的行的估计百分比是 0.0014%。用表基数(560,273 条记录)与谓词过滤因子 (0.0014%) 相乘就获得了 QUALIFIED_ROWS 的估计值。

为了了解优化器如何最终以 0.0014% 作为过滤因子,首先要查看在 IN 清单谓词上的 6 个元素。谓词的查询注释显示在列 PRODUCT_NUMBER 上有 273 个不同的值。假设这些值均匀分布,优化器一般应得出 2.197%(即 6/273 = 0.02197)的记录将符合此谓词的结论。但是相反,其使用了 0.0014%。这表明谓词选择性比其表现出来的还要好!

要理解这一点,查看在此谓词的查询注释上的 MAXFREQ。在必要的统计信息被收集并可用时,MAXFREQ 在列上显示最常见显示值的频率。在这个例子中,其值是 3.32%,即高于每个值 (1/273 = 0.366%) 的平均百分比。这表明 273 个值中的一些可能更低。此列上现有的统计信息可帮助揭示实际发生的是什么。您可以从基础访问路径图中的表节点的表描述符处获得该统计信息。

为了查看表描述符,请右键单击访问路径图上的表节点。图 10 显示 CUST_ORDER_DETAIL 的表描述符。

图 10. 图 1 上所示的访问路径图片段
三路联接的访问路径图片段。图下的文本将提供详细描述。
三路联接的访问路径图片段。图下的文本将提供详细描述。

(请查看图 10 的 放大版本。)

通过深入在表描述符上的列统计信息,您可以发现在 IN 清单上的 6 个元素恰好是频率统计信息上的最后 6 个值。添加了这些频率值,您最终确切地以相同的(估计的)0.0014% 作为 IN 清单谓词的过滤因子,因为这将解释优化器如何确定过滤因子为 0.0014%。

此外,请注意显示在表描述符底部的统计信息收集的时间戳,其显示了一个相对于本次查询时间的最新日期。只要数据分布自上一次 RUNSTATS 以后没有改变,则优化器的估计可能非常接近实际值。假设这是事实,基础访问路径会被认为具有最佳的高可信度。

结束语

本文描述了访问路径的基本概念以及如何解读访问路径图。Optim Query Tuner 的访问路径图和查询注释功能用于审查现实世界的查询并解释 DB2 优化器如何以及为何选择特定访问路径的原因。此信息可为您提供您开始调优查询所需的构建基块。本系列后续文章将提供有关适用于查询调优方法的更多信息。


下载资源


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=569944
ArticleTitle=通过 Optim Query Tuner 调优 SQL,第 1 部分: 了解访问路径
publish-date=11042010