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

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

假设一个用户的请求是这样的：对于给定的一系列客户和物品，查找相关的订单, 这些订单的重量（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所示。

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

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

自适应查询处理

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

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

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

         查询计划缓存是系统级别针对全部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中已经实现的功能。

 

 

原文连接：http://www.mcpressonline.com/database/db2/db2-for-i-observing-learning-adapting-and-generating-faster-queries-automatically.html 

原文作者：Mike Cain

翻译：钟嘉田，王佳