--本文摘自klyuan
在压力测试时发现,如果原来的查询在0.1秒之内,那么在40个并发时,平均速度是3秒。目标要求是所有的查询必须要在7秒之内。有好几条SQL都是在7秒左右,肯定不能通过压力测试,必须要优化到2秒之内才有希望通过压力测试。
(注:主要数据表的记录数都会超过60W条),所以只能是在项目组内进行SQL优化。
(因为某些原因,案例所使用的SQL都是经过笔者处理过的,已经不是最原始的SQL了。所以不便于贴出详细的执行计划)
1、索引很重要
当查询的记录数小于表记录总数的10%时,索引的效果是非常明显的。
优化前要看执行计划,去掉不必要的全表扫描,找出花费时间的SQL。然后加上适当的索引。
2.注意复合索引失效
- create index n1 on ht(code,status,ht) ;
create index n1 on ht(code,status,ht) ;
在ht表上的ht,code,status三列上已经存在复合索引n1
- select * from ht ,qt where ht.ht= qt.ht
select * from ht ,qt where ht.ht= qt.ht
发现这个查询需要20秒左右。
查看执行计划时发现,n1这个索引并没有被使用。
于时,使用提示 index
- select index h(n1) from ht h , qt q where h.ht = q.ht
select index h(n1) from ht h , qt q where h.ht = q.ht
使用提示后,查询只需要0.01秒了。
为什么n1这个索引没有被使用呢?
原因就是复合索引的问题。
对于复合索引,复合索引的第一列必须出现在WHERE条件中,复合索引才会被使用。
其实按照ORACEL的说法,“复合索引只有索引的所有列都作为查询条件时,索引才会被使用”。看来并非如此。
因为当时系统还没有上线,所以结合实际情况。因为ht列都会出现在查询的WHERE条件中。所以可以重新建立索引,调整索引列的顺序。
- create index n1 on ht(hth,code,status)
create index n1 on ht(hth,code,status)
这样,查询只需要0.01秒了。
3.not exists并不是最快的
有一些SQL优化经验的程序员都知道。总是该用not exists来代替not in.好像not exists就应该是最快的了。其实不尽然。
使用not exists时一定是要进行关联子查询。如果是非关联子查询,not exists是没有意义的。
- select * from ht a where not exists ( select id from bills where ht = a.ht and bill_type =1)
select * from ht a where not exists ( select id from bills where ht = a.ht and bill_type =1)
因为ht表的数据量非常大。运行执行计划时发现对ht表的开销非常大。同时还发现ht表的索引失效了。
考虑如果用外关联来代替可能会获得一个更好的性能
- select * from ht a, bills b where a.ht = b.ht(+)
-
and b.ht is null
-
and b.type = 1
select * from ht a, bills b where a.ht = b.ht(+)
and b.ht is null
and b.type = 1
再次执行执行计划时,发现两个表是通过 hash join outer方式进行访问。
原来的SQL需要20秒,优化后的SQL只需要0.02秒。
4.合理使用提示
有时使用提示,可以使查询效率提升。
h表和q表的记录数都是60w以上。
- select h.a ,h.b,q.a,q.b, q.c from q, h where h.a = q.a and h.b = '' and h.c = ''
select h.a ,h.b,q.a,q.b, q.c from q, h where h.a = q.a and h.b = '' and h.c = ''
在h表的a表有索引。
执行计划发现对h表的a列上的索引进行了index fast full scan访问
对q表进行index range scan 和table access by index rowid访问。
然后进行hash join连接
因为h表都非常大,索引也非常大。就算是对索引进行完全访问,开销也很大。
这两个表的连接方式是比较慢的。
因为在h表是有其它的条件限制,可以过滤掉大部分数据,得到一个小的结果集,再与q表进行nested loop访问。这样性能就会有显著的提升。
所以可以加上提示,要求SQL按照指定的顺序访问,并且使用nested loop进行连接。
- select /* + ordered use_nl(h)/ h.a, h.b, q.a, q.b, q.c from q,h where h.a = q.a
-
and h.b = ''
-
and h.c = ''
select /* + ordered use_nl(h)/ h.a, h.b, q.a, q.b, q.c from q,h where h.a = q.a
and h.b = ''
and h.c = ''
原来的SQL需要7秒,优化后的SQL只需要0.7秒。
性能提升10倍
总结:
SQL优化是个技术活,也是个体力活。需要耐心,不断的试验。
首先要会看执行计划,迅速的定位性能慢的SQL片段。
其次就是对建立适当的索引,尽量减少全表扫描
再次就是要对SQL进行优化,对优化前和优化后进行对比
最后就是可以使用提示试试
如果不行就需要对环境进行调优化了。如sort_area等。
(因为商业的原因不能把真实的SQL语句和执行计划分析贴出来)
-----------------------拷你的文章,纪念你,一路走好
分享到:
相关推荐
SQL优化 SQL优化软件 SQL优化工具 很好用的工具,可以分析优化TSQL语句,oracle数据库语句优化工具
跟着乐于分享的数据库大师梁敬彬抓住表象背后的SQL本质 有人就有江湖,有江湖就有IT系统,有IT系统就有数据库,有数据库就有SQL,SQL..., 随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包
第2章 风驰电掣——有效缩短SQL优化过程 24 2.1 SQL调优时间都去哪儿了 25 2.1.1 不善于批处理频频忙交互 25 2.1.2 无法抓住主要矛盾瞎折腾 25 2.1.3 未能明确需求目标白费劲 26 2.1.4 没有分析操作难度乱调优...
1. SQL优化 1 1.1. 优化实战 1 1.1.1. 策略1.尽量全值匹配 1 1.1.2. 策略2.最佳左前缀法则 2 1.1.3. 策略3.不在索引列上做任何操作 2 1.1.4. 策略4.范围条件放最后 3 1.1.5. 策略5.覆盖索引尽量用 3 1.1.6. 策略6.不...
随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题,...
本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在sql语句执行的...
sql优化的几种方法sql优化的几种方法sql优化的几种方法sql优化的几种方法sql优化的几种方法
基于Oracle的SQL优化
深入揭示OracleSQL优化与调优的原理、核心技术与思想方法 盖国强鼎力推荐! Oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是SQL性能问题。本书是作者十年磨一剑的成果之一...
SQL Server SQL优化
基于Oracle的SQL优化
sql优化sql优化sql优化sql优化
本书是作者十年磨一剑的成果之一,深入分析与解剖Oracle SQL优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在SQL语句执行的生命...
Oracle_SQL优化脚本_完整实用资源,请下载 。
《基于Oracle的SQL优化》PDF版本下载
, 现在《收获,不止SQL优化——抓住SQL的本质》开始带你抛除烦恼,走进优化的可乐世界!, 首先教你SQL整体优化、快速优化实施、如何读懂执行计划、如何左右执行计划这四大必杀招。整这些干嘛呢?答案是,传授一个先...
崔华的《基于Oracle的SQL优化》一书配套脚本,纯手打