细致入微 | 让 SQL 优化再多飞一会儿
第一章 细致入微 | 让 SQL 优化再多飞一会儿
云和恩墨 | 2016-04-12 20:54
怀晓明
云和恩墨性能优化专家
本文来自于本周四云和恩墨大讲堂怀晓明老师的分享。
内容：作为 DevOps 的最佳落地方式之一的 SQL 审核，如何才能做好？这是一件很有挑战性的事情，他将通过两个具体案例，来展现 SQL 审核工作如何才能做得更好，更有价值。简言之就是八个字——“细致入微，方显价值 ”。
我们都知道，细致认真，可以将一件事情做得尽可能完美，在 SQL 审核与优化中，同样需要细致认真，才能做好这项工作。细致入微，透过现象看本质，让 SQL 优化走的更深一些。
DevOps 理念逐渐为大众认可，SQL 审核作为其中协同开发和运营工作的一项服务，或说一种工作手段，也显得异常重要。因为 SQL 审核的价值已经深入人心——SQL 审核可以提前消除数据库隐患，为未来系统更稳定的运行打下良好的基础。
今天给大家分享两个有点点特别的 SQL 优化案例，说是特别，是因为它们的优化并不仅仅是表面上的 SQL 问题，还能够发掘出更深入的问题根源，让我们的 SQL 优化旅程开始飞吧......
不仅仅是 NULL 的问题
1问题发现
在某系统中，通过 Z3，我们发现一条违反审核规则的语句：
（注：Z3 是云和恩墨自主研发的 SQL 审核工具，试用信息见文末。）
delete from publckrec where RecKey = null
该语句非常简单，违反的是“索引全扫”这一条规则。
2问题分析与处理
首先，首先我们看一下执行计划：
我们需要知道的是，Oracle 在执行原 SQL 的时候，并不会真正去做全索引扫描。
为什么呢？
因为执行计划中的第二步的 filter，其断言是 NULL IS NOT NULL，这是永远不会成立的条件，所以 Oracle 是不会去执行全索引扫描。
回到这个 SQL 的书写上，如果你对 Oracle 的 NULL 有一定的了解，那么你一定能知道，该语句的正确的写法应该如下：
delete from publckrec where RecKey IS null
这是因为在 Oracle 中，对 NULL 的比较必须使用 IS NULL 或者 IS NOT NULL.
有关 NULL 的更多有趣的事儿和更深入的解析，推荐大家阅读杨廷琨老师的文章：
Oracle 中的 NULL 值解析
甚至还有人利用 NULL 来钻漏洞：
据说他姓氏改成 Null 之后，酒店/租车/购物各种不花钱了（http://www.cnbeta.com/articles/488673.htm）
对于普通工程师而言，事情到这里就结束了。
然而，作为一名合格的 SQL 审核工程师，我们应当能发现执行计划第三步用到的索引的名字并不是用户自定义的名字，而是系统自动生成的约束的名字。
然而，作为一名合格的 SQL 审核工程师，我们应当能发现执行计划第三步用到的索引的名字并不是用户自定义的名字，而是系统自动生成的约束的名字。
然而，作为一名合格的 SQL 审核工程师，我们应当能发现执行计划第三步用到的索引的名字并不是用户自定义的名字，而是系统自动生成的约束的名字。
以上重复的都是重点！
而 Oracle 只会自动为主键和唯一键这两种约束添加同名的索引，如果这是唯一键约束，那么改写就是最终的方案，如果是主键约束，那该语句就是一个无用的语句。
通过 Z3，我们发现，这是一个主键约束：
因为该表的 RecKey 字段为主键，不会存在NULL值，所以从数据库的角度看，此 SQL 总是删除0条记录，实质上是一为无用语句。
然而，事情至此并未结束，我们还要问，既然无用，为什么还会有此语句被审核到？这连单元测试都通不过，开发不是应该在开发阶段就消灭掉这种错误的么？
当问题反馈给开发后，得到开发的反馈是前台 JavaScript 有 bug，导致传递的键值变成了 null，所以出现了如上问题。
所以最终的解决方案并不是不执行该 SQL，而是修正前台 JavaScript 的 bug，并采用绑定变量的方式编码。
意想不到的优化方式
1问题发现
在某电信客户，通过 Top SQL 抓取，发现一消耗资源过高的问题语句如下：
SELECT *
FROM
(SELECT *
FROM
(SELECTj.order_id,
j.jms_xml
FROMjms_cent j,
om_order oo
WHEREoo.service_id IN (355, 597)
ANDj.order_id = oo.id
ANDj.state = '-1'
ORDER BYj.in_time ASC
)
WHERErownum <= (10 -
(SELECTCOUNT(1)
FROM
(SELECT j.order_id,
j.jms_xml
FROMjms_cent j,
om_order oo
WHEREoo.service_id NOT IN (355, 597)
ANDj.order_id = oo.id
ANDj.state = '-1'
ANDrownum <= 10
ORDERBY j.in_time ASC
)
))
)
UNION
SELECT *
FROM
(SELECT *
FROM
(SELECTj.order_id,
j.jms_xml
FROMjms_cent j,
om_order oo
WHEREoo.service_id NOT IN (355, 597)
ANDj.order_id = oo.id
ANDj.state = '-1'
ORDER BYj.in_time ASC
)
WHERErownum <=
(SELECTCOUNT(1)
FROM
(SELECT j.order_id,
j.jms_xml
FROMjms_cent j,
om_order oo
WHEREoo.service_id NOT IN (355, 597)
ANDj.order_id = oo.id
ANDj.state = '-1'
ANDrownum <= 10
ORDERBY j.in_time ASC
)
)
)
在生产环境上，该 SQL 平均每次消耗10万逻辑读：
其执行计划如下图（测试环境，生产环境的执行计划与之类似）：
2问题分析与处理
如果你有一定的开发经验，当看到该 SQL 及其执行计划，你的第一反应，应该是该 SQL 存在写法不当的问题（ORDER BY 与 rownum 用法不当），并且可以通过 WITH 来改写 SQL，改写后的 SQL 逻辑读，能降到原先的1/4左右。
但云和恩墨为这个客户做了一段时间的 SQL 审核与优化，我们已经熟悉了开发商的开发风格。
该系统的绑定变量编码其实一直做得不错，为什么出现了 oo.service_id NOT IN(355, 597)和 j.state = '-1' 这样没有用绑定变量方式编码的代码呢？
实际上，jms_cent.state 上的数据分布非常不均匀：
经过与开发沟通，确认 state 为'-1'的数据的确总是很少数，并且根据业务，该语句总是要查 state 为'-1'的值。然而该列上并没有索引，于是我们在测试环境上我们新建一个索引：
create index idx_jms_state_new on jms_cent(state);
创建索引后，再看此时 SQL 的执行计划：
可见建立索引后 cost 大幅下降。
如果我们没有深入分析，而是直接就上手改写 SQL，cost 大约会在10000左右，和现在加索引后的22相比，还是天壤之别的差距。
所以，在 SQL 审核与优化工作中，找对改进或优化的方向是更为重要的事。
而我更想说的是，事情到此其实才刚开了一个头，精彩在后面……
同时我们注意到，该表上建立了好几个索引，其中一个索引为：
索引列只有一列，但命名是 SYS_NC00007$，这意味着什么呢？
答案是：'SYS_NC' 的前缀暗示着这很可能是一个函数索引。
使用 DBMS_METADATA.GET_DDL 获得该索引的创建语句核心部分为：
CREATE INDEX IND_JMS_CENT ON JSM_CENT('STATE');
此刻，真相大白了，原来并不是该列上没有索引，而是当初建立索引时由于一个疏忽，将一个普通索引建立成了一个函数索引！！！
所以我们的优化改进方案，即实际应执行的脚本为：
DROP INDEX IND_JMS_CENT;
CREATE INDEX IND_JMS_CENT ON JSM_CENT(STATE);
3问题延伸处理
案例2至上一节就完满结束了。
但作为一家专业的 SQL 审核与优化服务提供商，我们还会继续做一项检查——查询库内是否还有类似的将普通索引错建成函数索引的情况。
通过对库内所有索引进行检查，并未发现其他索引有类似问题存在，所以最终在正式库上执行的优化改进脚本就是上述脚本。
总结
今天，通过两个很有意思的案例，来和大家分享一下 SQL 审核工作要如何才能做得更好。
要做好这项工作，细致认真是必不可少的，然而工具也是极其重要的，所谓“工欲善其事，必先利其器”，有了 Z3 的帮助，大大提高了我们做 SQL 审核与优化服务的效率。
SQL 审核工作如果需要做到完美，那并不是一项简单的事。我们需要“由点及面”，找出问题的真正原因，才能真正将这项工作做好。
做好 SQL 审核，可以让技术和运营团队形成更紧密的协作关系，有助于提高应用系统的稳定和效率，保障业务顺畅进行，而这也是 DevOps 的落地的最佳方式之一。
附录
1分享人简介
怀晓明，ID 是 lastwinner，全国 SQL 大赛的评委。目前主要负责 SQL 审核与优化相关的业务。
2Z3 简介
【云和恩墨业务介绍】之 SQL 审核服务
Z3 试用申请：
marketing@enmotech.com
010-59003186
3课堂提问
1. 加个引号有什么作用？（指的是函数索引）
答：引号加上后，常规的一个列就变成了一个字符串常量，相应的索引就是一个常量索引。而对 Oracle来说，常量索引是函数索引的一种，所以你在 Oracle 里看到该索引就是一个函数索引。
2. 类似问题：
❶ 问题：请问建索引的时候把state字段加上单引号的效果是什么，相当于所有字段的指都to_char了吗？
❷ 问题：请问建索引的时候把state字段加上单引号的效果是什么，相当于所有字段的指都 to_char 了吗？
答：不是，相当于是索引了一个常量字符串——'STATE'，可认为是一个常量索引，其实并没有什么实际作用。
3. 第二个 sql 能否采用临时表，因为大部分查询都是重复的?
答：用 with 生成的临时表，是可以的，但是我在分享中也提到了，用了索引，是最有效的手段。如果再加上改写，效率还会再提高，但提高不会太多了。
4. 问题：
❶ Z3 内置的规则是通用的吗，区分 oltp 和 olap 吗？
❷ 在 sql 审核方面，基于 oltp 和 olap 我们需要注意些什么呢？
❸ 测试环境与生产环境数据量不是一个数量级的时候，Z3 的内置规则审核的问题 sql 有差异吗？
❹ 在 olap 中经常用到并行，对于在 olap 中我们使用并行需要注意些什么呢？
答：
❶ Z3 内置的规则是可以修改阈值的，OLAP 和 OLTP 可以分别选取不同的规则并设置不同的阈值进行审核
❷ 在 SQL 审核层面，OLAP 和 OLTP 的差异不是很大
❸ 因为 SQL 审核的规则的阈值可以配置，比方说全表扫描，在测试环境上我可以设置只要大于 8M 的表上发生了全表扫描，就认为触发了规则，而在正式环境上，我会将该值设置为 80M 甚至 200M（根据需要）
❹ OLAP 中的并行一般问题不大，最重要的是要避免并发的并行
5. jms_cent.state 上的数据分布非常不均匀，直方图不就是解决数据分布不均匀的情况吗？此处为什么还是新建索引呢？
答：光有直方图没有索引是没用的。
6.（追问）直方图的使用是建立在有索引的基础上的，可以这样理解吧？
答：数据分布不均匀的列上若建立了索引，当列上存在直方图时，Oracle 可以更精准的根据条件值来判断到底该不该走这个索引。
7. 表是否必须建主键？建与不建有何深层次影响？
答：大部分情况下，表是应当建主键的。当然，也有情况可以允许不建主键，比如多表关联查询的结果暂存在一张中间表中，这样的情况就可以不为这张中间表建主键。不建主键就可能导致表内数据重复，一旦出现重复就可能导致业务出错。
8. 第二个案例中的 SQL 是要实现什么目的呢？UNION 的前面当满足条件的记录小于等于5条时，就返回这5条，若大于5条，则返回 10-N 条。比如有8条记录时，就只返回前两条。而 UNION 的后面则返回最前面的N条，（N 小于等10）。由于 UNION 有去重的作用，所以，最后返回的最终结果似乎是：只返回满足条件的前10条？
答：这个 SQL 比较绕，一两句话描述不清楚。看这个 SQL，你需要注意条件里有 in 和 not in ，还有这个 SQL 平均每次返回不到3条记录。
9. 'state' 列加单引号就会成函数索引吗？
答：你的问题应该是“ state 列加单引号就会成函数索引吗？”，回答是，是的。
10. 如果产品上线或者要上新功能，有大量的 sql 需要 review，如果没有像恩墨 Z3 这样的神器，我们 dba 应该怎么操作，难道只能艰难的看开发的 xml 文件吗？
答：如果你们没有使用 Z3 这样的产品，那么 SQL 审核的工作会非常辛苦，作为一名 DBA，你大部分精力就会消耗在这些可用工具帮你做的事情上，而你的智慧和精力实际应该用在更有价值的地方，比如根据 Z3 审查出的疑似问题的地方，依据你对业务的了解进行确认。
另外需要提醒的是，XML 的配置有很多种，有的是直接写 SQL，有的是将一个 SQL 分成可拼接的几部分，如果遇到后者，那会很麻烦的。
11. 'SYS_NC' 的前缀暗示着这很可能是一个函数索引。这个前缀有几种？
答：这个前缀有几种我没细研究过，抱歉现在无法给你一个满意的答复。
12. 案例二只查状态为-1的情况么？
答：是的，经过与开发确认，这个 SQL 就总是查 state 为-1的数据，这是业务决定的。
13. 这个 SQL 审核软件 Z3 可以共享一下用吗？
答：试用需要先和我公司联系。不过，我们很快就会放出一版云上的 Z3，供大家体验。
Z3 试用申请：
marketing@enmotech.com
010-59003186
14. 类似这样巨大的执行计划应该怎么去分析，是按规则一行一行的看？还是只看关键的位置？
答：这需要就事论事，有时候，优化 SQL 不仅仅是要执行计划，而有时候，优化 SQL 又根本不需要看执行计划。这类案例我以后会和大家分享。
对于复杂巨大的执行计划，首先要打好基本功，知道各种执行计划是什么含义，在什么场景下适用于什么执行计划，其次是需要多加锻炼，经验了多了之后，面对一个执行计划你能很快就看出问题所在。
15. 想问一下建索引时加一个 desc 是什么作用，我上网查说是降序索引，能不能讲讲这种索引的应用场合呢？
答：适用于 order by ... desc 的场景。
16. 避免并发的并行-例如一个调度里面包含了100 条 sql，假如每条 sql 里面都使用了并行，这就是并发的并行对吧，如果这些并发的 sql 里面的并行度之和没有超过我的并行 server 最大值呢，也不建议用吗，再如果我的并发 sql 失败了造成大量的并行事物回滚，如何避免这种情况呢，设置串行回滚貌似也是有代价的。
答：如果是在一个调度里，在没有开启多线程的情况下，应该是串行的而不是并行的。
17. 做好审计，需要关注哪几方面内容？
答：做好 SQL 审核，需要在技术、工具、业务等多方面都要做到位，才能做好 SQL 审核。
18. 是否可以这样理解，对于一个严重分布不均匀的列（类似例子中的 state 列），如果某个或某几个值非常少，而 SQL 中又经常对这些稀少值进行查询，也是可以建索引来提高查询性能的。
答：是的，这样理解完全没问题。
19. 如果表的列上建了复合索引，又建了普通单列索引，是优先使用复合索引吗？这种一个列上建了多种索引会有问题吗？
答：这个问题不好具体回答，Oracle 的规则是选择代价小的索引，而代价大小，是要先确定查询条件才能定的。一个列有可能被多索引所选用，这是很正常的现象，设计得当是不会有问题的。
20. 建索引的时候写法（"status"）和（'status'）是不是一样会有函数转换？都转成字符类型？
答：不是转换，至于单引号和双引号的区别，你自己试验一下就知道了，还是挺有意思的，我这里就不点破了。
21. 请问函数索引是如何提高函数的执行速度的，是什么原理？和普通列上的索引机制一样么？
答：原理是一样的，都是 B-Tree 索引。
22. 一个十万和百万级别的表关联查询，关联字段都见了索引，查看执行计划只走了百万级别表的索引，这是不是已经是最优的执行计划了。
答：没有具体的 SQL、执行计划和索引情况，那是不好说的。
23.（追问）select * from a，b where a.id=b.ida 表十万级，b表百万级，两个表的 ID 都是 B-tree 索引，查看计划只走了百万的b表的 ID 索引，就是这种简单场景。
答：那有可能，因为你要查表里的所有字段，走完索引再回表代价可能会太大，所以还不如将小表全表扫一遍。
2016 ACOUG Asia Tour 4月份合肥、南京、济南和上海站火热报名中，扫描下方二维码填写报名信息，或者复制链接（https://jinshuju.net/f/Lqq7H4）到浏览器打开。
posted @ 2016-04-20 21:01 ^_^小麦苗^_^ 阅读( ...) 评论( ...) 编辑 收藏