中亦科技黄远邦技术人生（9）——SQL优化之基于SQL特征的改写.pdf-资料库

dbaxiaoy-9956965-16359647456187592872.pdf-第1页.png

第1页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第2页.png

第2页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第3页.png

第3页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第4页.png

第4页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第5页.png

第5页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第6页.png

第6页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第7页.png

第7页 / 共11页

dbaxiaoy-9956965-16359647456187592872.pdf-第8页.png

第8页 / 共11页

小 y 黄远邦技术人生（9） ——SQL 优化之基于 SQL 特征的改写周末老 K 宅在家观战了两局精彩的“人狗”大战。老 K 既算不上科技迷，也算不上围棋迷，不过对此颇有感触：阿尔法狗不过是通过左右互博的方式不断学习围棋，然而依赖其最优的学习算法（学习方法）却能再短短的数月之内达到人类围棋水平的最顶端；而李世石在却是依赖其已有的经验结合人类特有的灵感下出“神之一手”，人类终究还是可以战胜拥有超强计算能力的阿尔法狗。这些不禁让老 K 想起了自己在工作过程中的最有艺术性的部分 ---“SQL tunning”，一方面要不断学习积累运用不同的优化方法，同时在必要时多一分想象力和灵感，这样面对不同的 SQL 问题，我们才能下出自己的“神之一手”。好了，今天老 K 与大家分享的案例是 SQL 调优的案例，但老 K 更希望大家能从中体会到 SQL tunning 过程中的优化方法和思维方式，真正做到它山之石，可以攻玉。同时，大家如果觉得老 K 的方法还不错，不妨轻轻的转发一下，分享给身边更多的 ORACLE 技术爱好者。今天分析的问题是客户 DBA 给过来的一条 SQL 语句，已经困扰其一段时间了，希望老 K 一起来分析解决。解决这个问题对老 K 来说并不是特别难，不过在这个问题的分析过程中，老 K 给出了几种优化的方向，最终选择了不论是对整个系统还是对该条 SQL 都可谓最佳的一种方式，最后在测试环境执行效果非常不错。 1、摆问题、列信息对于 SQL tunning，老 K 上手最先关注的是 SQL 文本、执行计划和执行统计信息，当然也不要忘了关注一下系统 / 数据库版本。 1.1 环境介绍操作系统 AIX 6.1 数据库 ORACLE 11.2.0.3 两节点 RAC

1.2 SQL 文本 1.3 执行计划 1.4 执行统计信息信息都在这了，我们要关注些什么呢？老 K 的经验是，先找特征，再根据不同的特征来进一步提取自己需要的信息。

2、找特征、补信息 2.1 SQL 文本特征 >> exists 子句 (part1) 和 update set 部分 (part2) 的 sql 代码基本相同，如下图； >> part1 部分中，标量子查询的结果作为 set 列的目标值，说明从业务逻辑上能保证该部查询返回记录数最多为 1； 2.2 执行计划的特征 >> 该执行计划各过程均使用 filter >> 结合 sql 文本及 predicate information 可以看到，对目标表 TARGET_BIG_TABLE 经过滤条件 POST_DATE=:V1 后，返回记录数预估为 623K 条。

2.3 补充信息收集之表统计信息 >> TARGET_BIG_TABLE 大约 2G 大小，SOURCE_SMALL_TABLE 大约 3M 大小； >> TARGET_BIG_TABLE 表中记录数约 250W 左右，统计信息估算 POST_DATE 过滤后返回 623K 条记录，注意：这是预估值，实际值会随着传入的变量 V1 而变化。 >> SOURCE_SMALL_TABLE 表中记录数约 12W 左右，ad02_acct_no 列的选择度比较高； 2.4 补充信息收集之执行计划解读注：TARGET_BIG_TABLE 简称为 T 表 SOURCE_SMALL_TABLE 简称为 S 表另注：解读关键 ---- 理解执行计划中的 filter >> 执行计划分开成两部分来看，其中 ID2-7 步表示对应 SQL 文本的 part2 部分，ID8-12 步对应 SQL 文本的 part1 部分； >> part2 部分的过程：使用 POST_DATE 过滤 T 表，将过滤后的记录迭代入 EXISTS 子查询（T 表的结果集此时作为变量传入子查询），在子查询执行的过程中，如果前面的关联条件符合，再次迭代入第二层子查询（select max() 部分）进行匹配； >> part1 部分的过程：针对 ID2-7 步过滤出的结果集，逐条 update，而 update 的目标值，同样是通过类似 2-7 步过程中的逐步迭代查询而来； >> 在各步骤单表访问方式均为全表扫描； >> 从执行计划中可以看到，在第 3 步对表 T 表进行过滤之后结果集估算为 623K（rows 列），其后对 S 表过滤后均为 1； >> 由此可以估算执行过程中表访问的情况应为：（老 K 建议在本分享中记住下面的公式，暂且称之为 “ 访问公式 ” 吧）过滤过程的表访问 =（T 表全扫 + 623K 次 ×（S 表全扫 +（0 或者 1 次）×（S 表全扫）））修改过程的表访问 =（需要修改的记录数 ×（S 表全扫 + （0 或者 1 次）×（S 表全扫）））总的访问过程 = 过滤过程的表访问次数 + 修改过程的表访问注意：此处的（0 或者 1 次）×（S 表全扫）表示的是第二层子查询的情况，如果在第一层子查询过程中关联条件就不符合，则不再需要迭代入第二层，即 0 次 S 表全扫，否则即是 1 次 S 表全扫；所以过滤过程对 S 表最少需要做 623K 次全扫，最多需要做 1246K 次全扫；修改过程同理。 2.5 执行统计信息特征 >> SQL 单次执行平均逻辑读为 355,245,774（block 数） >> SQL 单次执行平均时间约 2000 秒 >> SQL 单次平均修改记录数约为 0 条

3、思考吧 DBA 好了，信息收集完成了，进入老 K 的既定思考轨道，其实对于任何一个 SQL tunning 的问题，老 K 都会提出下面的三个问题，这个也不用例外； 3.1 老 K 的例行思考 >> 这个执行计划是否为当前 SQL 语句下最优的执行计划？（选择优化目标） >> 我们想要的执行计划是什么样的？（确定优化目标） >> 我们怎么来让 SQL 跑出我们想要的执行计划？（实现优化目标）如果可以，正在阅读此文的你，也许也可以思考一下上面的三个问题，或者回忆一下当你面对 SQL tunning 的问题时你有没有思考过这三个问题，亦或者你会思考 / 思考过什么呢。综合前期的分析思考片刻之后，老 K 郑重地给出了自己的答案： 3.2 老 K 的答案 ---- 不是最优的计划老 K 先查看过该 SQL 的历史执行计划，只有这一个，但这并不意味着着就是该 SQL 的最优执行计划；在执行计划解读部分，老 K 给出了这个执行计划的“访问公式”，从公式中可以知道其实 S 表虽小，但其实际上是整个执行计划的关键，整个过程中最多可能需要对 S 表进行 1246K×2 次访问呢，那我们可不可以提高对 S 表的访问效率呢？当然可以，从执行计划中的估算可以知道对 S 表的访问大约返回 1-2 条记录（这里老 K 还单独验证过），说明整体选择度比较高，我们只有创建合适的索引，就可以就可以大大将提高 S 表的访问效率。我们简单来估算一下使用索引的情况下的执行效率是怎样的。原来对 S 表全扫所需的逻辑读数为 3M（表大小）÷8192=375 次，使用索引后预估对 S 表一次访问最多所需逻辑读数为：（2 次索引块访问 + 2 次数据块访问）=4 次；所以说，使用索引的逻辑读约为使用全扫的的 1%，估算创建索引后该语句单次执行平均逻辑读约在 350w 左右。那么，新建索引，将 S 表的全扫都变为索引扫描，这就是老 K 想要的执行计划吗？显然不是，这样的执行计划只是原执行计划的一个升级版而已，其过程还是一个迭代的过程，这样执行的时间 / 消耗的时间基本都会随着原计划中第 3 步返回的数据量（还记得 623K 这个值吗，就是它！它是可变的，可能随着传入的）变化而线性变化；所以这个执行计划虽然较原执行计划预计会有非常大的改善，但仍然不是老 K 想要的执行计划。

3.3 老 K 的答案 ---- 想要的计划 SQL 文本告诉我们，其实 SQL 做的就是使用 exists 方式将 T 表和 S 表进行关联更新，老 K 想要的执行计划应该是使用 NL 或者 hash join 的方式来连接两表，而不是使用 filter 迭代的方式，这样就能保证 SQL 执行过程中只需要对 T 表和 S 表进行极少的一次或几次扫描，从而降低 SQL 执行的逻辑读。 3.4 老 K 的答案 ---- 如何生成漂亮的执行计划要回答这个问题，我们首先要思考为什么 SQL 当前没有跑出我们想要的执行计划，是因为统计信息不准？索引设计不合理？还是列类型不匹配？都不是！我们再次回到 SQL 语句本身，来看看 SQL 语句的特别之处。在这里，我们看到了问题的关键，正是因为最外层的 T 表与两层子查询均有关联关系，导致 ORACLE 无法自动改写 SQL，最终生成执行计划时无法使用 T 表与 S 表进行 JOIN，只能生成使用 filter 方式的执行计划。所以，最终思考的结果已经出来： >> 因为两层子查询的原因导致 ORACLE 无法使用 JOIN 的方式关联 T 表和 S 表 >> 要想生成较好的执行计划必须改写语句 >> 改写后的语句不应该存在类似的最外层表涉及第二层子查询的情况其中最后一点，指出了我们改写的关键点。 4、改写吧 DBA 依据老 K 的经验，SQL 语句的改写通常要求改写者对 SQL 涉及业务非常了解，通过业务特征重构出合理的 SQL 语句，才能更好的做到既不改变 SQL 的业务逻辑，又有效提高 SQL 性能；不过针对这个 SQL，我们已经知道了导致其执行计划不优的根本原因，老 K 相信可以在不考虑业务特性的情况，利用数据库的特性来进行有效的改写。 4.1 改写的花絮基于 SQL 特性中，part1 和 part2 基本相同的特性，老 K 先随性的对 SQL 做了如下改写（当然没有针对前面提到的改写关键点）；

这一改写方式的几个关键点： >> 先把 post_date 字段的过滤条件直接提取出来，与原逻辑一致 >> 基于 part1 和 part2 基本相同，使用了 nvl 函数代替了原来的 exists 子句 >> 如果 select 部分能查到记录（类似原来的 exists 子句成立），则用查询出的结果更新 chq_ pay_name 字段 >> 如果 select 部分不能查到记录，则用原记录自身进行更新（set chq_pay_name=chq_pay_ name），更新前后该记录的数据不变以上几点保证了改写后的 SQL 与原 SQL 逻辑一致，不过有一点不一样的非常值得注意，原 SQL 只修改极少的几条记录，新 SQL 却修改了 623K 条记录，只是其中绝大多数是冗余的修改。我们再看改写后的 SQL 执行计划：与原 SQL 执行计划类似，不过少了原执行计划的 part1 部分。新的执行计划，老 K 又问了自己一句： 4.2 这样改写真的好吗？大家是否还记得原执行计划解析过程中老 K 给出的“访问公式”：总的访问过程 = 过滤过程的表访问次数 + 修改过程的表访问那么，在这个执行计划下，因为去掉了冗余的一部分，公式就变成了：总的访问过程 = 过滤过程的表访问次数实际上就可以理解为，SQL 在修改数据的过程中可以重用过滤过程中生成的数据；不过针对这个语句，我们从执行统计信息里知道，每次语句执行最终修改的数据量都非常少，也就是说这样改写所减少的“修改过程的表访问”对整体执行效率影响并不大。

这样改写会带来什么坏处吗？会！根本原因就在于上面提到的新 SQL 实际修改的记录数是 623K 条： >> 持有行锁范围变大，可能大量导致其他对该表进行 DML 操作的会话被阻塞 >> 如果修改列上有索引，索引维护的时间将大大增加，导致新 SQL 执行效率更低综上，针对这条 SQL 语句，这种改写方式并不合适。不过，如果原 SQL 在执行过程中修改的数据量接近 623K 条，那么这种改写方式的收益就要高非常多，而其带来的坏处也就不复存在了，这种改写方式只是不适合这种业务环境下（每次只修改极少几条记录），然而却有一定的普遍性，所以老 K 也把这部分分享给大家，最重要的是解决问题过程中的思路和方法。 4.3 继续改写前面我们已经分析出改写的关键点：改写后的语句不应该存在类似的最外层表涉及第二层子查询的情况；下面我们就朝着这个目标去改写我们的 SQL 语句。改写前信息补充：改写思路在老 K 脑中酝酿好后，老 K 又补查了 T 表的信息，确认 T 表存在主键约束，主键列为 ACCT_NO 和 JRNL_NO; 4.4 增加冗余 >> 在 exists 子句中增加一个冗余的 T 表，别名为 d >> 增加 d 表和 a 表的关联关系，其中 jrnl_no 列和 acct_no 列组合为 T 表的主键，其他冗余列的关联主要为下一步继续的改写作铺垫； >> 整个 SQL 语句中没有使用 d 表与其他表进行关联； >> 由于 d 表和 a 表使用的是主键进行关联，所以能确保对 a 表的每条记录，都能从 d 中找到且只能找到一条记录符合语句中的关联关系；综上，可以知道上述增加冗余完全不改变 SQL 的逻辑关系。

资料库

中亦科技黄远邦技术人生（9）——SQL优化之基于SQL特征的改写.pdf

相关推荐

数据库

热门标签

最新资料