IBM Support

DB2 for IBM i:自动观察、学习、调整并产生快速查询

Technical Blog Post


Abstract

DB2 for IBM i:自动观察、学习、调整并产生快速查询

Body

 

DB 2 for IBM i:自动观察、学习、调整并产生快速查询

有了AQP,你的查询计划中就有了“质量控制专家”。

根据维基百科,“自适应行为”是一种能够自动调整到另一种行为或者情况的行为。这种行为往往有以下特点,那就是能够将无效的或者具有破坏性的行为转变为有效的行为。

DB2 for IBM i作为集成在IBM i 操作系统中强大的数据库管理工具,就具有对SQL查询进行自适应处理的特性。自适应查询处理(Adaptive Query Processing)简称为AQP,已经在IBM i 产品最新的版本中实现了。多年来,DB2 for i以其简单易用,较低的总体拥有成本以及致力于解决业务问题的独特能力而闻名。IBM i 7.1版本的DB2扩展了这些优点,它为一些比较棘手的查询提供了保证措施,这些棘手的查询包括具有复杂谓词的连接查询、谓词看似简单却涉及偏斜(skewed)或非均匀连续分布的记录集的查询,或者在查询运行时无法获得比较准确的统计信息。

复核查询优化

简而言之,查询优化是指基于SQL语法、数据和环境来评估查询请求并确定最好的查询计划的行为。事实上,数据库在处理过程中会考虑很多的因素以及变量,如图1所示。

图像
          在这一过程中,查询优化器寻找策略和方法来最高效的利用CPUI/O 处理,也就是寻找最快的查询计划。为了达到这一目标,要考虑固定的消耗,比如处理器的数量和相关速度、内存可用容量、硬盘读写时间。优化器知道数据库对象的属性,比如表的大小、行数、索引的大小以及键的数量等。它还可以确定一些数据属性:每列数据的基数(cardinality),数据的分布、给定一个列比较时两个表各行之间的关系。数据库约束的存在同样重要。正是这些数据属性和关系有可能使一个好的查询计划效率降低。利用索引或者列统计信息确定一个表中各种颜色的数量是简单易行的,例如比较准确的估算出一个表中含有“BLUE”值的行数。但是当需要估算订单到达日期大于订单日期加上31天或者订单到达日期减去订单发货日期大于10天的情况时呢?基于 “数据相互依存关系”的查询在不实际运行时很难理解数据并确定答案。

     为了更好地说明AQP在查询优化中的作用,本文使用customersordersitems shipping metrics 四个表说明,见图2
图像
        平均每个客户有100个订单,每个物品有1,000个订单。当查看订单表的数据时,一般情况下每个客户会被重现100次,每个物品会被重现1,000次。也就是说,每100个订单都会有一个相同的客户,每1,000个订单会有一个相同的物品。此外,假设包含客户表和物品表的相应主表没有重复的标识符键值,并且在相关的主表中客户表和物品表的列值是唯一的。

假设一个用户的请求是这样的:对于给定的一系列客户和物品,查找相关的订单, 这些订单的重量(weight)要大于规定的阈值(运输成本高于正常的一种情况)

SELECT c.customer_state ,

c.customer_number,

c.customer_name,

o.order_number,

o.order_date,

o.order_quantity,

i.item_number,

i.item_brand,

i.item_description,

i.item_weight

FROM orders o

INNER JOIN customer c

ON o.customer_number=c.customer_number

INNER JOIN items i

ON o.item_number=i.tem_number

WHERE c.customer_state IN ('IA','IL','MN')

AND i.item_brand = 'BIG BAD MACHINES'

AND (i.item_weight / 2204.6) >=

(SELECT s.shipping_weight_threshold

FROM shipping_metrics s

WHERE s.destination_state = c.customer_state)

ORDER BY c.customer_state,

c.customer_name,

c.customer_number

OPTIMIZE FOR 20 ROW;

为了查找相关的订单,数据库的请求会包含为每个客户连接订单表中的100条(平均值)相关数据,或者为每个物品连接订单表中的1000条(平均值)相关数据,然后根据state连接航运指标表中的相关数据。但是如果有一个客户或者物品有100,000个订单呢?或者根本没有超过航运阈值的物品呢?优化器对关系和连接处理的推测也许在处理一些重要的订单之前完成,这样就会产生一个有重大缺陷的查询计划。也就是说当订单表的数据分布是偏斜(skewed)的,即部分关联客户表或物品表的特定值也许代表了很大的数据集,也有可能根本不存在相关数据。在查询优化过程中却不容易发现这种情况,除非优化器具有一些特殊的列值统计信息,或者优化器实际运行这个请求并根据结果来更好地理解数据。

说到理解数据,IBM i DB2 查询优化器在有索引和列统计信息存在时会很好的利用这些资源。如果索引和/或者列统计信息包含了每个表的连接列以及本地选择列,优化器确定并建立一个最优查询计划的概率就会很高。但是如果不存在索引,或者是本地的选择谓词太复杂以至于在运行这个请求之前无法计算和存储这个请求,查询优化器就会使用默认值和概率估算查询请求的选择度(selectivity)以及连接谓词。如果幸运的话,默认值和数据的属性匹配,那么这个查询计划就是适当的。否则,优化器确定的查询计划就是次优化的。减少这种情况最好的方法就是确定并使用适合的索引策略。

查询执行选项

鉴于上文中的SQL请求,最基本并且符合逻辑的连接如图3所示。

图像
          A中,先读客户表中的客户数据,根据WHERE 选择谓词选择出state符合'IA''IL' 或者'MN' 的客户数据。再根据查询出数据的customer_number 在表2中查找相关订单 。如果表2中有符合条件的订单,根据item_number 在表3中查找物品属性(brand and weight)。若要全部完成,还需要根据客户表的state连接航运指标表,用相应的item_weight 与表4中的shipping_weight_threshold进行比较。直到各表中所有的测试都执行完毕并正确返回,这个连接处理才能够返回一行数据。

C中,先读订单表(表1),由于该表中没有本地选择条件,因此要依次读取该表的每行数据与客户表(2)做连接。如果匹配行的客户来自指定的三个州之一,那么就根据订单表的item_number 读取物品表,如果匹配行的品牌符合指定条件,那么这个订单的总重量就要用来与表4中的shipping_weight_threshold进行比较,并决定是否选择。这个方案适用于预期选择大部分或者全部订单的情况。

最好并且最有效的连接顺序取决于第一组能最快减少查询结果集数量的连接表。也就是说,如果客户表中只有少量的customer_state等于'IA' 'IL' 或者'MN',并且在连接处理中先读取客户表,然后再连接订单表,那么这个查询效率就会很高。相反,如果先读订单表,再连接客户表,那么订单表中的所有记录都要被读取用来查找相关的客户,这时还要执行对state的测试。如果客户的state 不符合选择标准,这个请求就会要求更多的I/O 、处理器资源和时间,产生的有效数据却很少。但是如果客户的大部分的订单都来自来自IowaIllinois或者Minnesota, 那么先处理订单表,接下来是客户表或者是物品表就会非常高效。连接顺序是关键,即对表访问的相对顺序非常重要。获取正确的连接顺序取决于对相关where选择谓词的 选择度(selectivity)以及每个表连接列之间关系的理解。

自适应查询处理

如果在处理查询请求之前,优化器就有全部的相关信息和处理选择方案那将是非常理想的,这样就会在第一次运行时执行最好的查询计划,产生最好的表现。事实上,这对于许多复杂的SQL请求既不可能也是不现实的。因此我们期盼在第一次运行产生了一个糟糕的请求时,取消任何长时间无效的请求,尝试分析请求行为,并为优化器提供更多的信息。不幸的是,这意味着要为不好的查询计划付出代价,并依靠运行后的探索改善未来的运行情况。

如果数据库系统可以做部分或者全部这些工作呢?如果存在这样的数据库,它能够在运行一些复杂的查询请求的初期,识别出查询计划效率不高,就停止使用这一计划,那就太完美了。这确实就是AQP(自适应查询处理)所做的工作,DB2 for i通过观察、学习做出自我调整以适应数据和环境,它能够将一个低效的查询行为变为一个高效的查询行为。

AQP 有两个应用场景:在查询处理过程中和查询处理之后,这意味着在处理请求时,AQP 能够参与并提高处理效率,或者在查询处理之后,对运行时的一些度量指标和运行结果进行分析,提高今后的查询效率。见图4

图像

AQP 的组成部分包括:

1. 实时查询监视器

2. 查询计划缓存

3. 查询计划监视器

4. 全局统计数据缓存

实时查询监视器负责观察实时查询计划的执行,并决定何时以及如何反应。当查询被执行了至少2秒并且没有任何结果返回时才开始实时监视和分析。也就是说,一旦产生了查询结果并将控制权归还为应用程序,AQP 就不能再介入了,因为这样会改变结果。目前,实时AQP 主要适用于涉及多表的SQL查询,比如:连接、子查询、通用表表达式(common table expression)、派生表等。在查询过程中,AQP 产生(spawn)一个辅助线程来观察低效连接、不准确或误导性的行数估算值和任何新创建的可用的索引。经过一番分析和学习后,AQP 就会在当前查询继续时启动查询优化。我们可以把这当做一场比赛,设想在查询结果产生之前优化器能产生一个更好的计划吗?如果优化器判定新计划比当前的查询计划更好,并且当前的查询还没有返回任何结果,那么就会终止当前的查询计划,启动新的查询计划,所有工作都在同一个作业中进行。这个过程对于用户的应用程序来说完全是透明的,这个新的查询计划将会保存在查询计划缓存中。见图5和图6

图像
         查询计划缓存是系统级别针对全部SQL查询计划的自我管理的知识库。在查询优化后,查询计划会保存在查询计划缓存中,而各个查询计划运行时的参数信息也会在查询处理过程中以及处理后随各自的查询计划积累和保存下来。查询计划和参数信息都会用来减少今后查询的运行时间和资源耗费。

查询计划监视器会随着时间分析现存计划。该分析涉及估算的行数、运行时间和实际情况的比较、记录复杂谓词的计算值,以及特定的连接顺序的性能特征。特别棘手的计划会被标记并进行深层次的分析。查询计划监视的积极作用是基于先前查询计划的执行信息和系统行为学习并优化以后的查询。这也是IBM i DB2自我调节的一个例子。

全局统计数据缓存(GSC)是针对复杂统计信息的系统级自我管理资料库。随着AQP 观察实时查询计划或者监视缓存中的查询计划,相关信息被收集并记录在GSC中。这是自学习组件的一部分功能,用来随时间推移不断提高查询优化。见图7

图像
           AQP活动举例

 使用前面描述的四个表和SQL语句,在下面给出的几个场景中,AQP的价值可以通过长时间的查询,低效查询以及实时调整提升性能这样一个过程体现出来。

在第一个例子中,初始查询计划是一个不好的连接模式,它首先读取客户表,接下来读取订单表。这样不断地读取三个表却无法为下一步工作产生匹配行。这个方案“卡在”第一阶段的连接,并使随后的查询计划处于“饥饿”状态。这将导致长时间运行查询却不返回任何结果。见图8

图像
        2 秒之后,AQP 开始观察查询行为并决定用不同的连接顺序建立一个新的查询计划。这个新的计划首先选择物品表,接下来是订单表,第三个是客户表,这是由于匹配item_brand的订单数量较少。这样,在订单表中就会引用较少的行,并且这些行很可能会出现在结果集中。见图9
图像
         在第二个例子中,初始的查询计划由于使用了order by导致性能下降。该计划首先通过键值索引读取客户表,这些键分别是customer_state ,  customer_name ,  customer_number 。这样客户表数据就会保持一定的顺序,并在连接时维持该顺序。它有利于以20行结果集为优化目标的查询,即应用程序希望前20个结果能够尽快返回。不幸的是,这个连接顺序也许会导致整体查询性能的下降,即为了排序的效率牺牲了连接效率。见图10

图像
          2 秒之后,AQP 开始观察查询处理的行为。新的优化处理没有对客户表使用ORDER BY,从而得到了一个不同的连接顺序。由于物品表中符合item_brand 条件的行数较少,所以新的计划首先读取物品表,接下来是订单表,第三个是客户表。这样就提高了选择和连接处理的效率,并且次序是根据排序(sort)而不是索引完成。鉴于预期结果的数量比较少,整体的查询性能甚至是以优先返回前20行为优化目标的查询,性能都要提高很多。见图11
图像
          在第三个例子中,初始的查询计划是可以接受的,但是还没有到达到期望的目标。这是因为在订单表中的数据符合brand条件但是不符合state条件。如果能够同时符合brand条件和state条件,那么使用哪种连接顺序就不重要了。这个新的策略基于IBM i DB2发现订单表并对其应用本地选择,即使在SQL语句中订单表并不包括具体的本地选择谓词。通过对客户表和物品表使用“Look-ahead predicate generation”技术(简称为LPG),根据RRN列表读取订单表数据,这个RRN列表是通过键值分别为item_number customer_number 的索引产生的。这样意味着订单表中只有同时符合物品表和客户表条件的行才会被选择。实际上,可以将订单表放在第一位,这样选择和连接都会是最高效的。见图12
图像

AQP 可以做什么以及我们的期待

在实验室测试中,AQP 的积极作用在运行比较复杂的查询请求时通过调整索引表现出来。它也有利于当谓词中含有衍生and或者or计算,以及当数据集高度偏斜或者有隐含关系的情况。这些情况下,AQP 的干预能够使一个缓慢的查询转为一个快速的查询。对于简单或者已经充分调优过的查询,优化器可以很好地完成识别一个好的查询计划的工作,这时AQP 不常被调用,或者只起了一小部分作用。自适应查询处理可以认为是防止误解数据和意外行为的一个保证措施。

怎样才能够应用这一令人振奋并且有价值的功能呢?使用IBM i 7.1,它对于所有的SQL查询自动应用AQP,完全不需要用户干预或管理。如果AQP在查询处理时被调用了,那么它会被记录并且可以通过Visual Explain SQL Performance Monitor 等分析工具查看。

如果你还在回避SQL 查询或者希望通过原生记录级的编程(例如RPG)提高处理速度。那么想象这样一个画面:用户正在运行你的程序等待结果,等啊,等啊。而你作为程序员关注到那些低效率的程序,分析情况,重写逻辑,编译代码然后悄悄地应用到新程序,完全不用考虑用户的知识或用户干预。这正是自适应查询处理,一个在IBM i DB2中已经实现的功能。

 
 

原文作者:Mike Cain

翻译:钟嘉田,王佳

[{"Business Unit":{"code":"BU009","label":"Systems - Cognitive"}, "Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11145482