注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

韬光养晦

路漫漫其修远兮,吾将上下而求索

 
 
 

日志

 
 

数据库SQL优化  

2009-07-11 22:19:02|  分类: MSE learning |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
    最近参加了一次针对于Sybase数据库SQL的优化培训。对于SQL优化有了初步的认识,但是我认为这些认知具有普遍性原则。因此我就另外查着了一些资料并且自己整理了一下,放在这里。
参考资料:
1 http://www.javaeye.com/topic/135579
2 数据库索引的作用和优点缺点(一),http://learning.javaeye.com/blog/334009
3 数据库索引的作用和优点缺点(二),http://learning.javaeye.com/blog/334012
4 数据库索引的作用和优点缺点(三),http://learning.javaeye.com/blog/334014

一、目的:(期望看过这篇文章你能达到的目的)
1 明白两个原则。
2 看到一条SQL语句的时候,能对它的效率和结果做出大致准确的估计。

二、两个原则:(当你写SQL语句的时候应当想到这两条原则)
1 数据库中的数据量是不断在增长的,不仅仅是现在的这几条数据。
2 不只是一个人在使用数据库,可能同时会有许多人。

三、两个主(重)要概念:(你应当对这些概念十分熟悉)
1 针对原则1中的情况,你应当明白什么是索引以及一些其他概念比如唯一性索引,键值索引,组合索引,主键,聚簇索引以及它们各自的特点。
2 针对原则2中的情况,你应当明白什么是以及一些其他概念比如共享锁和排他锁、行锁和页锁、隔离级别等。

四、SQL优化核心是要了解执行计划,下面就举例说明:
1 SELECT * FROM table_a WHERE id="123"; 执行计划:当执行时,首先看TABLE_A是否有索引,有的话在所有可用的索引中寻找宽度最小的索引,若为找出唯一值则对剩余结果采用宽度次短的索引继续查找,直到出现唯一值或空值,如果没有的话则进行遍历比较。
2 SELECT * FROM table_a WHERE substring(dt,0,4) = "2009"; 这样执行时因为有函数的存在不会用到dt上面的索引,而且会对整个dt列进行运算。优化方式为:不用函数或者不得已用函数的时候将函数放在等号右边。类似这样:SELECT * FROM table_a WHERE dt > "20090101" and dt < "20091231" 或者不得已用函数时:SELECT * FROM table_a WHERE "2009"=substring(dt,0,4).
3 SELECT a.*,b.* FROM table_a a,table_b b WHERE a.A = b.A.当a.A和b.A的宽度不一致的时候,会发生短长度向长长度扩展,这时的优化方式同上面类似。
4 SELECT * FROM table_a WHERE  name="zhangsan" or id="001"; 它的执行计划是这样的:先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。这个过程中并未像我们期望的那样分别取出每个字句的行,然后将它们相加,因而未利用到name和id上的索引。优化方式为:SELECT * FROM table_a WHERE name="zhangsan" union SELECT * FROM table_a WHERE id="001".注意:关键字in带来的操作同or是一样的。
5 SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b WHERE conditon).因为括号中的语句包含条件,因此括号中的结果就以结果集形式出现,而结果集无索引,这种情况下,数据库会做笛卡尔积
,因此效率不高。
6 SELECT a.A,b.B FROM table_a a,table_b b WHERE a.A=b.A; 在Sybase中,若a.A上有索引,则效率更高,对b.A倒是无要求。
7 临时表:在Sybase中使用临时表的时候,要将建表和创建索引的部分一起放在一个而存储过程中,而将对临时表的使用放在另外一个存储过程中。因为编译的时候,若他们都放在同一个存储过程中,索引尚未建立,因而执行速度很慢。
8 关于Order by:尚不完整。
9 尽可能返回小结果集,这样对于服务器buffer,网络io,客户端等造成的压力较小。对于大的结果集可以采取分成小结果集多次发送。
10 减少锁的使用。

五、表关联算法:(所有数据库都类似)
1 RID join:最常见的关联。表T1和T2关联,优化器决定T1为外表,T2为内表。则对应于T1中的每条记录,都扫描一次T2的所有记录。这样的复杂度为T1.length*T2.length = m*n.因而通常应当选择结果集小的表做外表,并尽可能选择包含索引的表做内表(想想上面6中提到的例子)。通常选择内外表的选择可由公式:外表中的匹配行数乘以内表的每一次平均查找次数)确定,乘积最小的为最佳方案。
2 Merge join:先将两个表进行排序即做索引,然后采用类似二路归并的算法进行扫描。
3 Hash join:不是很常用,请查阅更多资料。

六、附带一点数据库调优的经验:
1 方法论:诊断数据库瓶颈采取自低向上的方法。前提是数据库本身不存在错误。在这个方向上进行循环迭代调试,永远不可能达到最优。
2 自低向上指的是:硬盘-》网络、IO系统-》CPU-》RAM。在硬盘上的主要操作包括设备规划,分区索引等等。通常要对历史数据做索引,对更新比较多的数据少做索引。在网络IO上的操作主要是规划网络包和网络IO的大小,一句应用的不同在服务器端设置network package size参数。在CPU级别的操作主要是指定引擎的数量,有可能的话将查询操作分布到多个CPU上执行(在sql中强制并行)。在RAM上的操作主要有采取命名缓存,利用全部内存等。在经常用到的小表上进行命名缓存,这样在内存回收的时候,不会回收这些表的缓存。(还可以考虑采取更大的内存,拆表为小表,更改应用程序等等)

  评论这张
 
阅读(984)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018