案例详解DB2执⾏计划的各个要点（附：DB2实⽤技巧）
对于DB2数据库，很多⼈并不能理解执⾏计划所表达的含义，在此推荐⼀篇社区专家的⽂章，通过实际案例详细探讨DB2数据库中执⾏计划的各个要点。⽂后附上IBM技术团队出品的DB2 实⽤技巧8招，相信对⼤家会很有帮助。
《DB2 执⾏计划浅析》作者：洪烨，专注于并擅长数据库领域。主要负责哈尔滨银⾏的服务器、存储、数据库的管理与维护⼯作。
在数据库调优过程中，SQL语句往往是导致性能问题的主要原因，⽽执⾏计划则是解释SQL语句执⾏过程的语⾔，只有充分读懂执⾏计划才能在数据库性能优化中做到游刃有余。
常见的关系型数据库中，虽然执⾏计划的表⽰⽅法各⾃不同，但执⾏原理却⼤同⼩异。在我看来，SQL语句的执⾏过程中总共包含两个关键环节：
只要掌握这两个关键环节，我们就可以迅速识别SQL语句在当前数据库中的执⾏逻辑，发现执⾏计划中存在的问题及隐患。由于不同数据库之间对于执⾏计划的表⽰⽅法各不相同。对于DB2数据库，很多⼈并不能理解执⾏计划所表达的含义，接下来我们就通过实际案例详细探讨DB2数据库中执⾏计划的各个要点。
1
读取数据的⽅式
谈到读取数据就离不开数据库中的物理对象：表及索引。因此谈到数据读取的⽅式，只需理解表扫描及索引扫描，就能基本把握数据的来源。
2
全表扫描
在DB2 LUW中可以通过四种⽅式获取语句的执⾏计划（db2expln⼯具），虽然⼯具不同，但展⽰的内容基本相似，最⼤的区别就是详细程度。详细程度由⼤到⼩排序分别是：。为了⽅便展⽰，我们在这⾥只讨论通过db2expln所展⽰的执⾏计划信息。⾸先我们来看⼀个db2expln抓取的完整的执⾏计划。
db2expln展⽰的信息总共分为三部分，分别是当前SQL 语句，执⾏计划详细信息及执⾏计划图⽰（需添加-g参数才能展⽰），第⼀部分是当前采集执⾏计划的语句（见图1-1）：
这部分内容基本⼀⽬了然，需要注意的就是⾥⾯:HV … :HI… 的信息是DB2将参数改写的信息（基本可以忽略）。
接下来我们来看最关键的部分，执⾏计划的详细信息（见图1-2）：
这部分⾸先包括当前环境的字符集（codepage），下⾯是根据统计信息评估出的成本及返回⾏数。在这个例⼦中执⾏成本为124470，返回⾏数为1⾏。从第6⾏开始的位置内容⽐较重要，我们采取逐⾏解释：
第三部分为执⾏计划图，我们可以通过执⾏计划图快速直接地看出SQL语句的执⾏过程，执⾏计划图的阅读顺序是从下往上，从左往右，按照编号从⼤到⼩的顺序进⾏阅读。⽐如在这个例⼦中，⾸先看第三步，显⽰对表进⾏表扫描操作（TBSCAN），然后对扫描的结果进⾏group by操作并将最终结果返回，这条SQL语句就执⾏完毕了。
3
索引扫描
接下来来看个索引扫描的例⼦，为了快速理解这个执⾏计划，我们直接先看执⾏计划图，可以看到这个SQL语句⾸先读取索引，获取RID后到表⾥获取其他数据，进⾏group by后将结果返回。
其他部分和上⼀个例⼦差不多，就不⼀⼀详细介绍了，主要看索引扫描的相关细节。从下⾯的信息可以看出⽤到的索引中包含4个字段，但这条SQL只⽤到了⼀个字段。其他三个字段都没⽤使⽤。如果该表上有其他索引包含这条SQL所使⽤的更多的字段时，这个索引肯定不是最佳选择。
数据读取的⽅式还有更多的细节，这⾥暂时不⼀⼀讨论了，但不论对数据采⽤何种⽅式读取，最核⼼的
内容还是数据从哪⾥读取，简单来说就是有没有更好的索引可以替代当前的扫描策略，所以，当我们对SQL语句进⾏优化时，第⼀步就是需要考虑当前的读取⽅式是否⾜够有效。
4
表连接的⽅式
接下来我们来谈表之间的连接，写过SQL的童鞋都知道，写SQL时Join⽅式可以有很多种情况：inner join,left join,right join,full join等，还包括⼀些⼦查询，⽐如exist 或者In等⽅式。对于星型查询，DB2 10以后还⽀持ZZJOIN。
5
Nest Loop(NLJOIN)
Nest Loop是最简单的⼀种连接⽅式，数据库会根据表中的记录数选择内表及外表，在定义内外表后，⾸先会对外表进⾏全表扫描，然后重复扫描内表并与外表中的每⼀条记录进⾏匹配，最终返回程序所需的结果集。
因此NLJOIN的总成本⼤约为外表扫描的成本+外表返回的⾏数×内表扫描的成本。NLJOIN作为使⽤场景
最多的连接⽅式，当外表匹配⾏数较少或内外表⾏数差距较⼤时效率较⾼，但也正因为NLJOIN的运⾏⽅式，也经常会发⽣性能隐患.
6
Merge Join(MSJOIN)
合并连接是为了解决Nest Loop中存在的⼀些问题所采⽤的⼀种连接⽅式，MSJOIN会将需要连接的两张表进⾏排序，并将排序后的结果集按照交叉⽅式匹配，最终返回连接后的结果。
MSJOIN的总成本⼤约为单次外表扫描的成本＋单次内表扫描的成本＋排序成本。MSJOIN常见的场景通常是SQL需要返回排序结果，亦或者主外表都⽐较⼤的情况，此外MSJOIN只能应⽤于SQL语句中包含唯⼀连接谓词的情况，当主外表数量级都⽐较⼤，且连接谓词上都存在索引时，MSJOIN的效率较⾼（避免了排序成本），通常MSJOIN⽐较稳定，即使统计信息估算错误，也不会导致执⾏效率出现较⼤的偏差。
7
Hash join(HSJOIN)
HSJOIN是⼀种⽐较⾼级的连接⽅式，进⾏连接前⾸先会将外表根据连接谓词进⾏哈希产⽣哈希表，然后将哈希表与内表进⾏连接并返回结果。与MSJOIN类似，HSJOIN也只对内外表分别进⾏⼀次扫描，同时HSJOIN也⽀持多连接谓词。在两张⼤表通过多连接谓词进⾏连接时效率很⾼。
HSJOIN的扫描成本约为内表扫描成本＋外表扫描成本。但需要注意的是，⽣成的哈希表会存放在排序堆中，⼀旦排序堆内存溢出，会额外产⽣⼤量的物理IO，这点需要特别注意。
8
半连接（semi-join）
半连接属于⼀种⽐较奇怪的连接⽅式，在很多资料⾥并没有将其划分到连接⽅式中，因为有的时候，从执⾏计划中根本看不到连接操作符，⽐如下⾯这个SQL：
这是⼀个典型的⼦查询，我们可以从SQL语句中猜出⼤概逻辑，⾸先会读取⼦查询中的表，然后根据返回的内容与外部表进⾏匹配并返回结果。但从执⾏计划图中并不能看到任何关于连接的信息。
执⾏计划图中并没有显⽰任何join的信息，只是多个对象进⾏了fetch，但从⽂字描述中可以看到更详细的内容。
数据流1⾸先会对内部表进⾏全表扫描（ANY／ALLSubquery），读取后的结果与外部表进⾏匹配，匹配到结果后不继续扫描⽴刻返回结果（EXISTS Subquery）。
9
多表间的连接顺序选择
不论在同⼀条SQL语句中包含了多少张表连接，在同⼀时刻只有两张表进⾏连接，但多表间的连接顺序也是决定性能的主要原因。数据库对于表的顺序的选择，往往根据两个表之间连接后得出的⾏数进⾏排序，如果统计信息与实际情况偏差较⼤，有可能会导致由于连接顺序不当⽽导致的性能问题。
10
总结
通过对执⾏计划的解析，我们讲解了SQL执⾏过程中对于性能影响较⼤的各个要点，但如何在⽣产上保持SQL的⾼效稳定，还需对执⾏计划进⾏更深⼊的理解。再解答⼀些常见的疑问：
Q & A
Q1：在查询时，有⼀个驱动表，通常是from后的第⼀个表，后⾯⼀堆左连接右连接，这个驱动表如何选择？对性能有影响么？⾃⼰⼈为该顺序不会影响执⾏计划？
A1：在数据库中，会根据当前表的情况进⾏内外表的选择，SQL语句中的写法只能从⼀定程度上决定连接次序，但不会做连接中内外表的决策。
举个例⼦，selectfrom a,(selectfrom b,c where b.id=c.id)where……，⽐如这个SQL，在写法中指明了需要先将b c表连接，再与a表连接，但在连接时候的⽅式以及连接时候内表外表的选择，都由数据库决定。
-----------
Q2：关于连接⽅式的选择，是由连接的两个表和连接的字段是否排序决定的？
A2：这个不绝对，但是会作为选择的因素之⼀。
-----------
Q4：访问某表的access plan改变了，统计信息没变，是什么情况？这是优化器⾃动调整了吗？可是优化器根据统计信息⽣成访问计划，按道理应该是不会变的啊？
A4：执⾏计划的选择会根据数据库参数，统计信息作为参考，但在编译过程中数据库还会收集⼀些物理信息。⽐如数据的物理分布可能会对扫描的⽅式产⽣影响。
-----------
Q5：这个物理信息是什么，是表空间信息吗？
A5：表在物理中存放的情况。
-----------
Q6：有什么⼿段跟踪⼀个SQL完整的执⾏过程，包括你说的动态收集物理信息？
A6：可以抓trace或者stack。db2trc，和db2pd –stack。
-----------
Q7：⽼师，db2的优化器是对越复杂的sql⽀持的越好吗？有这个说法吗？
A7：db2的优化器对复杂SQL的⽀持在关系型数据库⾥应该是最好的，但是对于联机交易系统来讲，我觉得SQL的稳定性⽐较重要。但复杂SQL牵扯到的变化因素太多，任何⼀个表的统计信息改变都有可能
导致SQL性能下降，所以在联机交易系统不太推荐写复杂SQL。
-----------
Q8：那我们写sql时该怎么注意呢？NL join类似笛卡尔集，时间复杂度最⾼，其次是merge。我觉得从sql上避免不了，因为选择了那个列，就基本确定了连接类型。
A8：在编写SQL的时候很难决定⽤什么连接⽅式，但有些需要注意的地⽅，⽐如避免多张⼤表连接，这些在开发过程中还是可以办到的。
-----------
Q9：hash连接，如果探测表很⼤，内建表很⼩，hash的成本显⽰很⾼，因为探测表做了表扫描，没有⽤到索引，这种如何优化，只能减少探测表的返回集吗？
A9：可以在探测表上创建适当索引。
-----------
Q10：对表做完统计更新后需要做rbind吗？
A10：这个需要取决于你的应⽤是静态SQL还是动态SQL。静态SQL的话执⾏计划在bind的时候保持在数据库中，统计信息更新后建议rebind，但动态的就没必要了。
-----------
Q11：通常谓词出现在索引的第⼀个字段应该就是有效索引，可有时候这个索引存在，但是个复合索引，跑db2advis时却建议在这个谓词上创建新的单⼀的索引，为什么数据库不⽤现有的复合？
A11：复合索引并不⼀定⾼效，这个需要根据数据分布来判断，如果单⼀索引的Clusterration⾮常好（也就是和表存放的顺序匹配度⾮常⾼），这样可以⽤到⼤量预取操作，性能会⽐同步读好很多。
-----------
Q12：嵌⼊式C、C++、COBOL的包BND（包括静态SQL），要绑，⽤户SP也建议绑定吧？
A12：UDI的成本其实很⼤程度上和表设计有关。⽐如在做DML语句的时候发⽣⾏溢出和页重组，带来的消耗远⼤于插⼊索引。相关信息可以看db2pd -tcb或者snapshot for table。
-----------
Q13：请问⼀下对于表压缩有什么建议？⽐如要做⼤表的压缩，有没有⼀些量化指标供参考，因为有⼀些表开了压缩批次插⼊较多记录时候影延长了批次1/3的时间。
A13：对于压缩，需要分析当前数据库的瓶颈在哪。压缩是以cpu为代价降低磁盘io，如果瓶颈在磁盘io上，肯定会有帮助，但如果瓶颈在cpu上只会雪上加霜。
-----------
Q14：调整APPENDON呢？有没有量化的⼀些指标？
A14：这个不是很好量化。对于磁盘io瓶颈，可以先从索引，语句甚⾄表设计⼊⼿。如果都已经调整到很好了但还是存在iO瓶颈，同时CPU使⽤率⼜⽐较低（30-40以下）。可以考虑压缩。
（本⽂由作者授权发布）
DB2 实⽤技巧⼋招
作者：CoreDB / 华南IBM⼤数据⽀持团队
1. 意外删除⽇志⽂件
所有的关系型数据库都依赖于⽇志⽂件保证数据库的⼀致性和完整性。DB2的⽇志⽂件分为活动⽇志⽂件和归档⽇志⽂件。当意外删除活动⽇志⽂件时，很可能会造成数据库宕机并且⽆法再激活。
如果你真的误删了活动⽇志⽂件并且数据库服务器还没有宕机，那就赶紧⽤export命令将重要的数据先导出来；
如果不幸数据库已经down机了，安全的做法⾃然是到以前做的备份并进⾏恢复；
如果你还有IBM的售后服务，可以让IBM的售后帮你重置控制⽂件的位置以跳过数据库启动时所需的前滚和回滚；
最不济的做法就是你⾃⼰⽤db2dart /ddel将重要的数据dart出来。
但不管使⽤哪种⽅式进⾏恢复，丢失了⽇志⽂件基本不可能恢复到最近的时间点，所以还是会丢失部分的数据
2. 实例出现意外情况
DB2的实例和数据库是⼀个松耦合的关系，你强⾏把实例删除了，但是数据库⾥存储的内容是不受影响的。
access是什么意思啊了如果你碰到以前⼀个正常运⾏的实例不能启动了，你⼜不想花时间去研究是什么问题导致的（很可能是某⼈修改了DB2实例某个⽂件的属性），那你可以先试试使⽤db2iupdt –k 命令进⾏修复。
如果修复不成功，那就将db2set –all ，db2 get dbm cfg，db2 list db directory的内容保存下来后，⽤db2icrt命令重建实

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。