每秒执行6000的简单SQL优化(一)(r10笔记第62天)

最近看到一个系统的负载比较高,引起了我的注意,查看AWR报告发现,竟然是因为两条很简单的SQL语句导致。 语句有多简单呢,就是下面的两个SQL语句。 select companyname from license select supdepid from hrmdepartment where id='' 突然发现以前也发现了这个问题,但是最后也是不了了之,还是因为单纯从数据库的层面调整要灵活快捷的多,从业务层面来推动还是有一定的难度和阻力。之前的分析:关于CPU使用率高的awr分析

表License的数据只有1行,表hrmdepartment对的数据有2000多行,id是主键,含有非空约束。 为什么这么小数量的表,含有主键,怎么还会导致严重的性能问题呢。 看执行计划第一个语句是全表扫描,里面只有1行记录,全表和索引扫描应该差别很小。

就是这样一个语句,在一个小时的时间里竟然执行了近2000多万次。

这样一条SQL的影响被无限放大,就导致了数据库的负载很高。 如此来看,每秒钟的执行频率极高,1秒钟差不多是6000多次的频率。什么系统有如此之高的业务需求。 和开发的同学交涉,原以为分析已经到位,剩下的就是快刀斩乱麻似的解决问题了。但是发现问题比我想象的要糟糕很多。 目前的情况是,大家都认为这是一个问题,但是让人很无奈的情况是这个系统是一个外部系统,目前还没有源码,所以也就意味着这是一个黑盒的环境了。 我看了下出问题的用户的结构信息,真让我大跌眼镜,大量的存储过程和触发器,表竟然有1万多个,我倒吸一口冷气,这个问题的情况确实比我想的难,准确的说是糟糕。 sh findobj.sh USERV6|grep TABLE|wc -l 11904 $ sh findobj.sh USERV6|grep TRIGG|wc -l 1580 $ sh findobj.sh USERV6|grep INDEX|wc -l 1542 $ sh findobj.sh USERV6|grep PROC|wc -l 2149 换句话说,从应用层面来调整SQL的可能性极小。碰到这种情况真是无语了。但是抱怨和牢骚解决不了问题。我一边和开发的同学沟通,一边想数据库层面能不能做点什么。 select companyname from license这样一个语句,不能动SQL还有什么优化空间了。目前来看有一个改进之处是索引,表里有10多个字段,输出只有一个字段,表里存在一行记录。所以也就尽可能提高访问的效率,10%的改进被无限放大也是一个很理想的值。 而且如果SQL语句能够修改的情况下,我有什么好的办法来改进,这都是我需要考虑的问题。 我用一个新的用户来做了一番测试。 create table license tablespace users as select * from USERV6.LICENSE ; exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE'); 可以看到执行计划是走了全表扫描 SQL> set autot trace exp stat

尝试1: 因为这个表只存在1行记录,而且从表结构信息来看数据是唯一的,于是我尝试创建一个唯一性索引。 create unique index index_lic_companyname on license(companyname); 重新收集统计信息 SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE'); 再次查看,发现CBO还是走了全表扫描。执行计划没有发生改变。

尝试2: 尝试添加hint,设置cardinality都是一样的结果,没有任何改变。 select /*+index( license index_lic_companyname)*/ companyname from license select /*+cardinality( license,1)*/ companyname from license 尝试3: 我想到了一个新的改进方法,就是sample,可以根据随机算法得到数据。但是尝试结果依旧没有任何变化 select companyname from license sample(1);

尝试4: 尝试rownum的形式,结果依旧。 SQL> select companyname from license where rownum<=1; 尝试5: 启用cache选项,把数据牢牢放入cache里面,减少被换出的概率,但是在这个极端场景下,还是没有任何的改进。 SQL> alter table license cache; 尝试6: 如果我启用了Unique Index的时候,即SQL语句改为下面的形式,结果不言而喻,肯定是非常理想了。 select companyname from license where companyname='xxxx'

但是改动成为上面的情况的可能性我几乎为0,想想挺美好,总是有巨大的差距。 尝试7: 既然在这个特殊场景中,我们需要查询的是companyname这个字段,有什么办法把索引和表结合起来呢。一种方式就是IOT,即索引组织表了。 重新创建表 CREATE TABLE "CYDBA"."LICENSE_IOT" ( "COMPANYNAME" VARCHAR2(100) primary key, ... "DOCUMENT" CHAR(1) ) organization index TABLESPACE "USERS" ; 插入1行数据。 SQL> insert into LICENSE_IOT select * from license; 1 row created. SQL> commit; 收集统计信息。 SQL>exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE_IOT'); 可以看到还是有了不小的改进。

这个结果让我还是充满信息,准备近期部署上去对比一下,希望看到鲜明的差距。第2条SQL继续优化,还是一块不好啃的骨头。

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

原文发表时间:2016-10-24

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

一道面试题引出的系列数据库性能,数据安全问题及解决方案

友情链接:http://enmotech.com/web/classify/43.html

11520
来自专栏牛客网

九月初,终于稳了一波,可以放松一下了嘛 附面经

最近几年的经历教会我一个道理,那就是,只要你努力,世界上没有什么事搞不砸的。——尼克·霍恩比

13110
来自专栏程序猿

周五推荐时间:博客3期

“ 已经推荐两期了,这是第三期,菜单可以查看往期推荐,以下博客有Java Web专题:SSM系列讲解;墙裂推荐;还有第三位,讲解了比较复杂的SQL查询是如何完成...

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

使用shell脚本抽取MySQL表属性信息

在最近抽取了数据库层级的信息之后,我们可以基于已有的数据做一些分析,比如那些业务属于僵尸业务,可以通过分析binlog的偏移量来得到一个初版的信息,如果在一个周...

12630
来自专栏黑泽君的专栏

MySQL各版本的区别

MySQL 的官网下载地址:https://www.mysql.com/downloads/

3.5K20
来自专栏性能与架构

建立索引后的代价到底有多大?

前几天写的文章“MySQL 性能优化案例:覆盖索引”,介绍了使用覆盖索引优化查询的方式,受到了一个网友的批评 批评的内容为: “直接从索引放回数据很快是个常...

38470
来自专栏华章科技

R语言怎么给中文分词?

Rwordseg包依赖于rJava包。由于Rwordseg包并没有托管在CRAN上面,而是在R-Forge上面,因此在在R软件上面直接输入install.pac...

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

闪回区报警引发的性能问题分析(r11笔记第11天)

自从有了Zabbix+Orabbix,很多监控都有了一种可控的方式,当然对于报警处理来说,报警是表象,很可能通过表象暴露出来的是一些更深层次的问题。这不又来一个...

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

融会贯通学习trigger(r2笔记第4天)

很多的东西在工作中用到的时候才能理解深刻,有些东西停留在理论层面而不去实践,就不会真正理解。 昨天写了一个很简单的trigger,但是中间也费了一些周折。 系统...

36960
来自专栏牛客网

猫眼测开一二三面面经,给口头offer

一面: 计算机网络: 面试官:浏览器输入URL地址到呈现页面给用户,中间到底发生了什么?用到了什么协议。 我:balabala,扯到了DNS 面试官:DNS的查...

72090

扫码关注云+社区

领取腾讯云代金券