Technical Blog Post
Abstract
神奇的前瞻性谓词生成技术
Body
原文链接：
http://www.mcpressonline.com/database/db2/the-power-and-magic-of-lpg.html
自IBM i V5R3版本开始，SQL 查询引擎（简称SQE）开始支持一种强大的策略，这一策略能够最小化输入/输出（I/O）以及最大化地提高查询性能。这个新策略像是具有魔法一样使优化器 能够在没有谓词条件的情况下生成谓词，并重写查询。本文会探讨前瞻性谓词生成技术（look-ahead predicate generation 简称LPG）及其所带来的好处。
提到查询优化，最小化甚至消除对无效数据的读取和处理是提高查询性能的关键，因为I/O操作是相对缓慢的 操作。查询优化器的工作就是选择合适的数据访问方法，建立尽可能快并且有效的访问和处理数据的策略，若有大量的策略可供选择，优化器通常能够选择并建立合 适的访问计划，满足用户对响应时间的要求。
在一个查询中，本地选择谓词用来指定哪些行需要选择处理。一种排除数据（这些行不会再进一步使 用）的方法是读取表的数据并测试数值。另外一种技术不是通过读取表数据，而是依赖于数学原则和其它数据库对象来避免测试表中的行。 例如，有一个带有本地选择条件的查询请求，该本地选择条件会从1亿行中选出一行数据，数据库引擎既可以通过读取并且测试每行数据（1亿次测试）完成查询， 也可以使用索引读取符合条件的这一行数据，这就消除了读取并测试每行数据的操作。虽然这两种执行方法的结果相同，但它们在性能上的差异是非常显著的。
另外一个可能引起大量读（性能不好）操作的查询场景就是表连接。一个连接通常会导致数据库引擎读取一个表的数据，然后在另一个表中执行读操作查找所有匹配的行。根据定义，连接能够减 少结果集，但是这需要测试每一行数据。这些读操作以及最终大量数据的不匹配会使这种查询存在严重的性能问题。如果优化器能够使用某些特定的策略，在连接之 前就消除一些行，那么查询性能就会大大提高，连接所需要的系统资源也会减少很多。
DB2 for i 支持很多方法以及策略来减少需要读取的行数和处理的数据，其中一项技术就是前瞻性谓词生成技术（look-ahead predicate generation 简称LPG），这个强大的工具能够大大减少查询时间。
下面是一个简单的连接案例，用它来解释为什么LPG能够对查询性能有这样积极的影响。
两个连接方法
设想有两个表：小表A 相对小一些，大表B相对较大。 一个查询请求涉及这两个表，在小表A上有本地选择条件，大表B上没有本地选择，表A的本地选择的条件选择度（Selectivity）很高（例如，它确定了小表A上的两行）。
SELECT *
FROM SmallTableA A,
LargeTableB B
WHERE A.Join_Col = B.Join_Col
AND A.Col1 IN (112358, 132134)
如果大表B上不存在索引，你会有几种访问方式以及连接组合：
一种策略是用大表B连接小表A, 对大表B进行全表扫描，对表B的每一行与通过索引或者哈希表访问的A表进行连接，如图1所示：
图1：大表B连接小表A
由于大表B上没有本地选择条件，从大表B中选出来的每一行都要用来与小表A连接，这个组合会产生大量的读操作。
另一个策略是使用小表A连接大表B，通常优化器会全表扫描大表B生成的临时索引或临时哈希表，如图2所示：
图2：小表A连接大表B
由于在大表B上没有本地选择条件用来减少需要处理的记录，所产生的临时索引或者临时哈希表要代表大表B中的所有数据，这两种临时数据结果都会花费相当长的一段时间生成，这些临时数据结果相对来说也很大。
两个较好的方法
应用了DB2的LPG以及查询重写技术，连接方案会大大提高。
如果在大表B上能够定义一些本地选择，并且这个本地选择具有一定的选择度（例如，能够减少选择和处理的记录数），那么数据库引擎就会使用这种能够减少处理记录数的方法。这就引入了LPG！查询优化器具有“预测未来”的能力，它能够从查询请求的其他表中生成本地选择条件并将该本地选择条件转移到另一个表上。如果这个本地选择是可用的，引用了索引技术的一些方法就会被优化器选择使用，提高查询效率（点击这里查看更多关于索引策略的信息）
使用上文的例子，小表A上的本地选择不仅用来确定小表A中需要处理的记录，还能够确定相关连接列的值。例如，小表A中包含A.Col1=112358 的记录同样包含了A.Join_Col=Value1（例如，这个相应连接列的值）。
这些从小表A上得到连接列的不同的值就会用来生成大表B上的本地选择条件（即本文前面所用的术语：前瞻性谓词生成）。现在，大表B有了本地选择，就可以应用一些技术来减少或者消除读取大量记录的操作了。
下面是重写后的查询请求：
SELECT *
FROM SmallTableA A,
LargeTableB B
WHERE A.Join_Col = B.Join_Col
AND A.Col1 IN (112358, 132134)
AND B.Join_Col IN (Value1, Value2)
这个新的本地选择条件使得优化器可以考虑更多的连接策略。如果大表B上（生成）的本地选择条件能够显著的减少需要处理的记录数，那么大表B就可能放在连接的第一个（主要）位置，并且通过索引访问。
另一个策略是大表B放在连接的第二位置。如果对大表B的读取是通过临时Hash 表，那根据新生成的本地选择条件就能大大减少临时hash表的数据。如果能够根据本地选择条件生成的索引来创建临时Hash表，则会更快。图3解释了这种策略。
图3：大表B在连接的第二个位置
当有一个或者多个表连接一个大表的时候，LPG的魔法会更加明显。
假设有四个表：三个相对较小(小表A、小表B、小表C)和一个较大的表（大表D）。一个查询请求同时涉及大表以及所有的小表，并且在小表上有本地选择条件，但在大表上没有本地选择，小表上的本地选择条件具有很高的选择性（例如：它指定了小表A、小表B、小表C上的少量记录）。
SELECT *
FROM SmallTableA A,
SmallTableB B,
SmallTableC C,
LargeTableD D
WHERE A.Join_Col = D.Join_Col1
AND B.Join_Col = D.Join_Col2
AND C.Join_Col = D.Join_Col3
AND A.Col1 IN (112358, 132134)
AND B.Col6= 'ABC'
AND C.Col4= 2005
AND C.Col5= 'January'
对第一组连接有多种数据表访问方法以及连接组合：
小表A连接大表D
小表B连接大表D
小表C连接大表D
如图4所示：小表A连接由全表扫描大表D产生一个临时索引或者临时hash表，再连接另外两个小表。
图4：小表A连接大表D，再连接另外两个小表
现在，考虑以下连接处理组合：
大表D连接小表A
大表D连接小表B
大表D连接小表C
如图5所示：通过全表扫描大表D生成临时索引或者临时hash表或者直接对所有记录，对大表D的所有记录依次连接三个小表。
图5：大表D中的所有行连接三个小表
使用前面四表查询的例子，小表A、小表B和小表C上的本地选择条件不仅用来选择相应表上的记录，还能确定各自表上连接列的值。根据LPG，这些不同的连接的列值（分别来自小表A、小表B、小表C）就会用来生成大表D的本地选择条件 。如图6所示：
图6：不同的连接的列值使用LPG生成大表D的本地选择
这个查询可以重写为：
SELECT *
FROM SmallTableA A,
SmallTableB B,
SmallTableC C,
LargeTableD D
WHERE A.Join_Col = D.Join_Col1
AND B.Join_Col = D.Join_Col2
AND C.Join_Col = D.Join_Col3
AND A.Col1 IN (112358, 132134)
AND B.Col6= 'ABC'
AND C.Col4= 2005
AND C.Col5= 'January'
AND D.Join_Col1 IN (Value1, Value2)
AND D.Join_Col2 IN (Value1, Value2, ... Value n)
AND D.Join_Col3 IN (Value1, Value2, ... Value n)
随着大表D上本地选择谓词的确定，就能够生成并使用相应的索引来减少（或者消除）大量无用数据的读取。
当然，另一个DB2 for i的新功能也能够处理这些新生成的本地选择—那就是索引合并（Index ANDing ）技术。这一技术是指优化器和数据库引擎能够根据多于一个索引来确定一个给定表上需要处理的记录。利用大表D上的多个单列索引，数据库引擎能够合并由每一个索引所产生的相应列的记录列表，并生成最终的符合条件的记录列表，数据库根据最终生成的列表访问数据。合并三个本地选择所生成的列表就会大大减少需要处理大表D的记录数，从而使数据库引擎避免读取大量无效的数据，使查询更高效、更快。见图7。
图7：索引合并技术使数据库避免读取大量数据记录
优化器还能够对具有一对一连接关系的普通查询使用LPG技术。这时，LPG能够回归预测并依次利用相邻表产生本地选择条件，它的巨大好处体现在优化器在确定连接顺序时具有更多的选择，而且，这个技术能够大大减少某一特定连接所带来较差查询性能的风险。
假设有四个大小相似的表：表A、表B、表C的表D 。查询请求涉及这四个表，并具有一对一的关系（A 对B，B 对C，C 对D），而且在表A上具有本地选择条件。
SELECT *
FROM TableA A,
TableB B,
TableC C,
TableD D
WHERE A.Join_Col1 = B.Join_Col1
AND B.Join_Col2 = C.Join_Col2
AND C.Join_Col3 = D.Join_Col3
AND A.Col5 = 'ABC'
确定最好性能的查询计划可能是有问题的，取决于表A上本地选择的选择度以及连接中表A的位置。如果每个表上都有本地选择条件，优化器在确定连接顺序就会有很多选择，因此就会有更多的机会提供多种高效的查询计划。
对于上面四表连接的例子，表A上的本地选择不仅能够用来确定A表上的记录，还能够用来确定相应连接列的值，从表A上产生的连接列的不同值就能够生成表B上的本地选择条件。
生成的表B的本地选择会确定表B上的记录以及相应连接列的值，这些连接列的不同值（由表B所派生出）就能够用来确定表C的本地选择条件。
生成的表C的本地选择会确定表C上的记录以及相应连接列的值，这些连接列的不同值（表C生成）就能够用来产生表D的本地选择条件。
现在，这个查询就能够根据对这四个表的本地选择来重写并优化（图8）。
图8：根据四个表的本地选择优化查询请求
所重写的查询请求为：
SELECT *
FROM TableA A,
TableB B,
TableC C,
TableD D
WHERE A.Join_Col1 = B.Join_Col1
AND B.Join_Col2 = C.Join_Col2
AND C.Join_Col3 = D.Join_Col3
AND A.Col5 = 'ABC'
AND B.Join_Col1 IN (Value1, Value2, ... Value n)
AND C.Join_Col2 IN (Value1, Value2, ... Value n)
AND D.Join_Col3 IN (Value1, Value2, ... Value n)
了解以及规划使用LPG，我们可以建立相应的索引提高连接查询的效率。如果没有这些技术以及相应的索引，也许就不能产生高效的查询计划。为LPG自动生成的本地选择谓词而建立索引的策略和用户依据自己的本地选择条件创建索引的策略是一样的。确定连接列是确定在哪个列上创建索引的线索。
查询魔法
就像一位优秀的魔法师，优化器会使用许多用户看不到的技术，用户只能看到最终结果。鉴于用户看不到查询重写技术以及LPG的发生，如果用户能够在这些技术应用时得到一些反馈那将是很好的。一个简单的技术就是当表上没有用户指定的本地选择条件时，观察表上的估计选择度，如果没有使用LPG，估计选择的行数会等于表的总行数，如果估计选择行数少于总数，那么就是应用了某些本地选择条件。
另一个更加准确的技术依赖于iSeries Navigator-Visual Explain 来图形化展示查询计划。一旦查询计划显示出来，应用了LPG技术的节点会突出显示（绿色显示）。要确定LPG，只需在Visual Explain窗口的视图菜单下选择“突出显示 LPG”。
图9：iSeries Navigator – Visual Explain 渲染查询计划
最大化地使用某种优化技术的关键是将其融合到正常的查询优化流程中—即将其普遍化。通常，复杂的优化技术会依赖对查询请求的分析以及查询环境，确定何时以及何处使用何种特定的技术。如果经过计算验证某一技术可被成功使用，那么这项技术就可以应用到查询中，否则，这项技术就是无用的甚至是有害的。
虽然DB2 for IBM i 的LPG技术看起来只是针对某些特定情况而很少使用的技术，它确实是优化器魔术包中普遍以及强有力的技术。文中的例子解释了这一技术的应用场景，但这不是LPG应用的唯一领域，其他一些领域如星形模型、雪花模型，还有带有子查询、公用表表达式（common table expression）以及派生表的查询都可能应用这一技术。
作者：Mike Cain
翻译：王佳
UID
ibm11144924