13

oracle优化案例 | 从执行计划定位sql查询问题 -利来国际app

13494


引入 | 记一次从执行计划定位sql问题及性能优化的思考过程

一、问题复现:

写在前面的话,童鞋遇到了这样一个问题-sql慢查询,在30万数据中查询,某个接口响应耗时,导致页面直接挂掉,其中,sql引入了视图,比较复杂,这里暂时就不贴代码了。

下面列举几个示例,在实际项目中,当数据量一上来,就发现索引其实是多么的重要,怎么排查视图中的表有没有建立索引,其中索引有没有失效,哪些索引没有利用上,又是如何失效的,耗时分析等,或许这能给我们在实际项目中作sql优化,在整体思路上有着及其重要的启发!


二、假设猜想:

1、在sql查询条件中,未对条件字段建立索引

2、在view中,表的访问方式连接顺序以及连接方式是否合理?

3、在已建立的索引中,索引是否命中,是否存在失效的情况,走全表扫描


三、思考过程:

从页面发起请求到后端服务接口响应耗时至页面直接挂掉:

1、首先,通过f12查阅页面加载耗时以及对后端接口加入耗时日志,得以分析最长耗时卡在dao层,即sql业务逻辑查询-结果响应。

2、接着,由于这里是远程排查,我们可以通过堡垒机的方式登入或者其他远程方式,然后打开oracle客户端pl/sql developer,拷贝日志中打印的sql语句在客户端查询。

3、然后,笔记本电脑的话可通过fn f5快捷键,在客户端打开当前sql的查询计划进行具体排查,作进一步分析。



图一


图二


图三

这里,先对执行计划中一些概念作下详细说明

资源成本耗费 (cost)

全表扫描(table access full)

索引扫描 (index scan)

嵌套循环(nested loops)

哈希连接(hash join)

排序-合并连接(sort merge join)

其中,每一个执行步骤都有对应的cost,可以从单步cost的高低,以及单步的估计结果集-对应rows/基数,来分析表的访问方式、连接顺序以及连接方式是否合理。


我们从执行计划中可知,其中可看到走了全表扫描-table access full的表,且扫描的行数rows以及cost也占居最高。分析到了这里,问题的根源离我们又近了一步。但是当我们查看打印出来的sql语句并没有查到当前表名,那会不会是存在view视图中呢?


我们在pl/sql客户端通过每个表的仔细查阅,终于在view视图中找到了走全表扫描的表,这下问题就可以随之迎刃而解?


将复杂的sql语句作查询步骤化解-"以大化小,以小化无",拆解成多个单步骤sql片段,对每个sql片段进行查询计划分析。但这里可优先对走全表扫描的表作进一步排查,分析之后,果然验证了之前的猜想。


上述全表扫描的表这里分别以a、b表昵称,在a、b表中分别有两个字段作为查询的条件属性,其中a表走模糊查询like '%a'而未命中索引,另一个b表是走条件查询未建立索引。针对上述排查后的结果,分别对a表作like 'a%'(可通过oracle函数达到like效果,但模糊匹配,不管是前缀还是后缀最终还是要扫描行数,其实还是可以更细粒度的优化滴~暂不详述),对b表查询条件字段建立index索引,类型为normal即可。


重点说明:本文重点在各种优化的思路,例如百度的搜索引擎,海量数据中检索有时候也是需要产品作出一部分妥协的,分页限制,滚动分页等,上千万亿级的数据不分库分表,可以上elasticsearch分布式全文搜索引擎,参考->https://www.modb.pro/db/166356



图四

ok,now,我们继续看一下select的效果,页面正常请求后端服务接口,完成数据加载渲染,页面终于跑起来了,后端服务接口响应耗时日志打印,也从之前的请求耗时大幅缩短,继续在oracle客户端查看执行计划,也未出现走全表扫描的情况。


四、利来手机国际的解决方案:

针对失效的索引或查询条件字段未建立的索引,根据查询计划定位其问题及根源,并同时对参与条件查询的字段,调整并建立合适的索引及类型。


五、反思总结:

当然优化的点有很多,上述只是通过查询计划对索引作了合理的调整,当数据量基数非常之大的时候,关系型数据库可作分库分表非关系型数据库可对数据作建模设计,选取elastic search-分布式全文检索-倒排索引或mongodb等等,根据合适的业务场景择优选取,具体也可参考小编:

https://blog.csdn.net/yxd179

引出这样一个小问题-思考:当你去图书馆借书或者书店的时候,想必都有用到过通过书的类型,书的名称去缩小你需要查找的范围,那为什么通过目录,索引搜索就这么快?什么情况下索引会失效?oracle、mysql、es索引底层又是怎么实现的呢?^_^

当然,我们也可以在工作之余去看看底层的数据结构,b树,b 树,hash索引,倒排索引......


在sql优化这一块,若大家有需要,欢迎在文末留言分享:

根据大家的需求度,后续考虑在上开源一款sql插件,支持插拔式-需要时开启,可自动将复杂sql填充参数打印sql语句,并打印其执行计划及耗时,助力于生产环境分析sql,排查问题,性能优化

引出这样一个小案例-思考:

现有这样一个场景,在某张单表中数据量在1000-2000万,怎么样能够通过程序快速把这样1000-2000万数据读取,并同步到一些其他的大数据组件作下一步数据分析。

或许这就是一点不一样的idea应对不一样的场景,深度分页越往后,查询的效率会下降。

这里也给出oracle跟mysql在上千万级数据作深度分页优化的idea:

1、oracle非主键字段实现自增

select count(*) from yd_info;
(1)alter table yd_info add info_id int;
select * from yd_info;
(2)update yd_info set info_id=rownum;
(3)commit;
select * from yd_info;


2、mysql非主键字段实现自增

(1)先添加字段,设置字段类型等基本属性: 
    alter table yd_info add info_id int(11) not null; 
(2)为该字段添加任意key: 
    alter table yd_info add key `info_id_key`(`info_id`);
(3)将该字段修改为自增属性:
    alter table yd_info modify `info_id` int(11) auto_increment;

上面就此建立了info_id辅助字段,当然索引是需要存储的,内存换时间,择优选取,当你想要设置的区间(start,end)跟info_id作判断即可,想必我们在sql分页中怎么避免深度分页的问题啦!

附注:mark一下之前的思考过程,坚持不易-自我驱动,由于时间等原因,阐述不一定俱全。本文暂时就到这里。对于sql性能调优这块,方式有很多,希望对各位读者,在数据库sql问题定位过程中能够有所帮助,欢迎提出宝贵建议^_^


「 往期文章 」


数仓进阶 | 记一次olap分析引擎演进思考过程

鲲鹏认证 | 多数据库切换之oracle迁移至mysql篇

开源数据库 | 记一次基于鲲鹏欧拉操作系统opengauss实践过程





最后修改时间:2021-12-21 13:18:16
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论