相差数十倍的SQL性能分析(r11笔记第98天)

今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的SQL,但是有一点不同的是,他们提供了两份文件,一份是一个id列表,大概有3000多个id值,另外一个份是个SQL文件。

之前也处理过几十万,上百万id值的情况,使得我原来开发中对于变动的敏感性依旧存在,所以我采用了另外一种灵活的方式,即外部表,外部表是数据库外的数据存在,在数据库依旧可以读取访问。

CREATE TABLE test_cn (cn varchar2(50) ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE ) LOCATION ('data.txt') );

而在这个基础上运行的SQL语句也很简短。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;

这样一来就达到了一种一劳永逸的效果,那就是后期如果开发同学继续提供另外一个查询,只要提供了id值,不管是多大,我都能轻松处理,不管是哪个业务的SQL我都能灵活套用。

但是问题来了,上面的SQL语句执行的时候,速度让我很不满意,因为持续了近2分钟。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ; Elapsed: 00:01:59.39

为什么很不满意,是因为这个“表”中的主键是基于字段uin的,竟然查询速度这么慢,实在不给面子。

INDEX_NAME COLUMN_NAME INDEX_TYPE UNIQUENES ------------------------------ ------------ --------------------- PK_USER_CERTIFICATION_INFO1 UIN NORMAL UNIQUE

对于这类问题我还是有不小的兴趣,毕竟能够顺手优化优化也是不错的体验。我尝试加了rownum,尽管这样不够严谨,但是输出结果和时间还是和开始的差不多。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) and rownum<=4000 ; Elapsed: 00:01:59.15

可见Oracle优化器早就看穿了我的心思,我怎么能够耍点小聪明呢。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn where rownum<=3200) ; Elapsed: 00:00:00.29

这样一个查询就能够达到非一般的速度。

这是为什么呢。要想得到一些更为细致的问题,那我们就开启trace来诊断一下,怎么诊断呢,一种比较自然的思路那就是10053事件。

10053事件诊断SQL

开启10053事件的步骤如下:

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; explain plan for select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ; ALTER SESSION SET EVENTS '10053 trace name context off';

其中能够看到不少细节的信息,我摘取出一小段来。

FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0) FPD: Current where clause predicates "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN") try to generate transitive predicate from check constraints for query block SEL$1 (#0) finally: "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN") 最后经过查询转换,得到的最终语句如下:

Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "TEST_CERTIFICATION_INFO"."UIN" "UIN",..... FROM "TEST"."TEST_CN" "TEST_CN", ( (SELECT "ACC00_TEST_CERTIFICATION_INFO"."UIN" ... "ACC35_TEST_CERTIFICATION_INFO")) "TEST_CERTIFICATION_INFO" WHERE "TEST_CERTIFICATION_INFO"."UIN"=TO_NUMBER("TEST_CN"."CN") kkoqbc: optimizing query block SEL$2 (#14)

可能看到这里就有些懵了,这个test_certification_info其实是个视图,里面包含有12个物化视图。其实这个简单的查询就好比是12个物化视图和一个外部表的关联查询。

那么为什么子查询使用了rownum之后,效率大大提升呢。这个可以从日志中看出端倪,我们可以清楚的看到优化器预估的时候这个外部表的数据条数是179950,和现在的3000多条想去甚远。

Table Stats:: Table: TEST_CN Alias: TEST_CN #Rows: 179950 #Blks: 462 AvgRowLen: 21.00 ChainCnt: 0.00 Access path analysis for TEST_CN

那么为什么优化器认为是179950条数据呢,这个和统计信息还是密切相关,尽管外部表不占用数据文件的存储,但是依然还是有一个基本的统计信息。

SQL> select num_rows from dba_tables where table_name='TEST_CN'; NUM_ROWS ---------- 179950

可能有很多同学说,那就收集统计信息,应该能够解决这个问题。SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',TABNAME=>'TEST_CN'); PL/SQL procedure successfully completed.

然后再次尝试,竟然还是很慢,查看执行计划发现里面始终是走了全表扫描。

这个问题的一种快速解决方式就是使用子查询中的rownum来限定,如果查询的数据缺失够多,走全表也不失为一种合理的方法。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-03-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Grace development

基于TP的每日签到代码

3043
来自专栏PingCAP的专栏

TiDB 助力客如云餐饮 SaaS 服务

客如云成立于 2012 年,是全球领先、 国内最大的 SaaS 系统公司。 目前面向餐饮、 零售等服务业商家, 提供软硬一体的新一代智能化前台、收银等 SaaS...

2234
来自专栏杨建荣的学习笔记

一条"简单"的sql语句和小兔子买面包的故事 (r3笔记第25天)

有时候开发人员写sql语句的时候,接触的性能问题越多,可能对sql语句的结构,性能考虑会多一些,这也是一件好事,不过如果考虑不当,本来原本想做的的一些优化却使得...

2745
来自专栏互联网开发者交流社区

SQL触发器实例(下)

1484
来自专栏猿人谷

【性能提升神器】Covering Indexes

可能有小伙伴会问,Covering Indexes到底是什么神器呢?它又是如何来提升性能的呢?接下来我会用最通俗易懂的语言来进行介绍,毕竟不是每个程序猿都要像D...

611
来自专栏java架构学习交流

java web轻量级开发面试教程读书笔记:建索引时我们需要权衡的因素

场景一,数据表规模不大,就几千行,即使不建索引,查询语句的返回时间也不长,这时建索引的意义就不大。当然,若就几千行,索引所占的空间也不多,所以这种情况下,顶多属...

20710
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1773
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4586
来自专栏禁心尽力

一次浴火重生的MySQL优化(EXPLAIN命令详解)

一直对SQL优化的技能心存无限的向往,之前面试的时候有很多面试官都会来一句,你会优化吗?我说我不太会,这时可能很多人就会有点儿说法了,比如会说不要使用通配符*...

2015
来自专栏杨建荣的学习笔记

一条insert语句导致的性能问题分析(一)(r8笔记第40天)

今天早上开发找我看一个问题,说他们通过程序连接去查一个表的数据的时候,只查到了8条记录,这个情况着实比较反常,因为从业务上的数据情况来说,不可能只有8条。 但是...

3195

扫码关注云+社区

领取腾讯云代金券