我爱模板

当前位置: 我爱模板网 > 网络编程 > Mssql数据库教程 > 网络技术 SQL优化

网络技术 SQL优化

 

在压力测试时发现,如果原来的查询在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

  1. 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等。

     

     

     

     

     

     

     

     

     

     

     

  • 推荐继续阅读的文章