最近的工作一直围绕着如何减轻一个老服务器数据库负载的问题展开。整个team都在寻求着更为有效的解决方案,作为项目新人的我也只能在项目中所学到的东西来做点总结,以便以后可以使用到。

    现在的项目是一个产品管理系统的升级/更新项目,由于客户端所涉及到的数据繁多,所以数据表也就成为了一个大数据量的表,由于对整个系统的业务逻辑不是过于了解,所以不能判断这种设计是否存在不合理性。再点,自己也是个菜鸟,所以只能就team leader提出的解决方案的学习做点学习总结。

    由于表的结构限制,以及义务相关的限制,数据库中存在一些大数据量的表,而这个数量级也是自己在校时并没有去考虑过的,现在面对如此大的数据量,确实有点不知所措。

    此次需要优化的部分涉及到了后台数据库中数据的检索及提取,然后到客户端画面的显示。而更为重要的就是减轻数据库的负载,以便单个的业务操作不会占用太多的资源,而导致其余业务处理无法被满足。根据leader的暂行解决方案,对于数据库管理系统级别的优化就先不在考虑范围之内,首要是解决SQL代码中的处理,以及如何将部分运算转移至客户端,以减轻服务器数据库的负载量。

    首先,要考虑的是大数据量的表,如何优化针对大数据量表的查询等操作,总结大概有一下一些。

    由于数据表的数据量很大,那么在查询中遍历每条数据进行判断几乎是不可能的,所以数据在表中的结构就应该得到优化,也就是在数据存入表中,或者在表中时应该有一些为了以后查询等处理所作出的准备工作。

    此类优化首选是加入索引(index),而加入索引也并非简单的对关键字段建立索引就好。需要考虑到此次加入的索引对于以后的操作是否有消极的影响。建立索引的对象应该是极少变动或者是不变动的字段,而这些字段都应该是拥有大量重复的字段,此时的索引将会有很大的表现,查询效率也将提升不小。此处应该建立的是聚集索引,而建立的过程中应该可以明显体会到此段SQL很慢,是因为在建立聚集索引的时候,表中的数据会根据你所建立的聚集索引的相应字段来排序,而这种排序将有利于你以后对该表的查询操作。使用SQL Server中的执行计划来看,你会发现,每个表的查询都会建立一个主键索引,而这种索引执行速度很快,但却没有对查询做什么优化,如果查询语句中包含GROUP BY等时,那么在执行完主键索引的时候,还会有更具GROUP BY的一个sort步骤,而这个sort过程也会根据数据量的增加而增加,那么可以通过建立聚集索引来替代这个sort构成,虽然聚集索引的执行时间和cost将会增加,但相比之前的操作,明显有提升。

    当然,索引也不是想怎么加就怎么加的,加入过多的索引,虽然在查询时提高了效率,但在对表的维护上,会投入更大的成本。比如,往表中插入数据,而此条数据刚好包含了已经建立的索引的字段,那么索引会因为你新插入了数据而重新对表中的数据进行排序,而这个排序工作将和数据量有着极为密切的关系,想想海量数据的后果吧!

    再继续谈谈关于索引失效的问题。

    建立了索引的字段中,是不能包含有NULL值的,因为任何包含NULL值的列都会被排除在索引之外,那么这个索引也就失去了效果,反而会占用更多的空间。所以,任何在WHERE子句中使用is null或者是is not null的语句,优化器是不允许使用索引的。

    既然说到了WHERE子句,那么在WHERE子句中写的SQL语句是否对索引会有影响呢,答案当然是肯定的。比如,查询id大于10的记录时,可能会写WHERE id > 10,如果没有对id字段建立索引,那当然没什么,但如果对id字段建立了索引,那么这种写法将会使索引失效,因为根据这条语句,还是会去判断每条数据的id是否大于10,而和索引没有直接的关系。那么,如何来写才能使得索引有用呢?

此时,可以写WHERE id >= 11,貌似也没有什么特别,但是此时索引会直接找到id为11的相关记录,然后在这些记录中再去判断其他的条件,此时,效率就有明显的提升。

    谈完了索引相关的小结之后,谈谈查询时数据过滤的问题。

    在数据查询时,WHERE子句中的条件还有顺序之说。也就是强弱条件的区分。这种说法我倒是第一次听说,不过讲到具体的原理,应该是个很浅显易懂的东西。

    作为查询条件,那么它最为直观的功能就是过滤掉那些“没用”的数据。而这个过滤就是这个问题的关键。如果一个查询条件加在那里,但却没有过滤掉一条数据,那么这种条件叫做弱条件;反之,如果一个条件可以过滤掉一大半或者90%以上的数据,那么这个条件就是强条件。就因为过滤的条件有强弱之分,所以它们的顺序也至关重要。

    强过滤条件应该首先被执行,那么之后的比对次数就会减少很多了,效率就是从这些小细节中体现出来的。如何让强条件先执行呢?就是把它放在WHERE子句的最后就是了,也就是说,弱条件紧跟WHERE关键字,而越是强的条件越靠后,则它们就会被先执行(具体是因为什么,也许是SQL语句解析器的原因,也没再深究,如果大家有了解,可以告诉我)。

    再呢,其实就是SQL代码的优化,把不必要的处理都去掉,比如使用过多的临时表,虽然在某种角度来讲,可以提高可与读性,但读与数据库来说,那是个不小的负荷,所以在可能的前提下,将那些没有必要的代码统统去掉。

    先将这么多吧,很少写总结,估计不是很好,多多提意见及建议。

 

    另外,从网上找到些关于SQL优化的原则:作者:yashi 来源:赛迪网

 


[摘要] 优化数据库的方法及SQL语句优化的原则:关键字段建立索引、备份数据库和清除垃圾数据等方式优化数据库;使用索引、海量查询时尽量少用格式转换来优化SQL语句。
[关键字] 优化数据库 SQL
  优化数据库的方法: 

  1、关键字段建立索引。 

  2、使用存储过程,它使SQL变得更加灵活和高效。 

  3、备份数据库和清除垃圾数据。 

  4、SQL语句语法的优化。(可以用Sybase的SQL Expert,可惜我没找到unexpired的序列号) 

  5、清理删除日志。 

  SQL语句优化的原则: 

  ◆1、使用索引来更快地遍历表 

  缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说:①.有大量重复值、且经常有范围查询(between, > ,< ,> =,< =)和order by、group by发生的列,可考虑建立群集索引;②.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;③.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。 

  ◆2、IS NULL 与 IS NOT NULL 

  不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 

  ◆3、IN和EXISTS 

  EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。 

  ◆4、在海量查询时尽量少用格式转换。 

  ◆5、当在SQL SERVER 2000中,如果存储过程只有一个参数,并且是OUTPUT类型的,必须在调用这个存储过程的时候给这个参数一个初始的值,否则会出现调用错误。 

  ◆6、ORDER BY和GROPU BY 

  使用ORDER BY和GROUP BY短语,任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值,Optimizer将无法优化。 

  ◆7、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。 

  ◆8、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。 

  ◆9、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。 

  DBCC(DataBase Consistency Checker)是一组用于验证 SQL Server 数据库完整性的程序。 

  ◆10、慎用游标 

  在某些必须使用游标的场合,可考虑将符合条件的数据行转入临时表中,再对临时表定义游标进行操作,这样可使性能得到明显提高。 

  总结: 

  优化就是WHERE子句利用了索引,不可优化即发生了表扫描或额外开销。经验证,SQL Server性能的最大改进得益于逻辑的数据库设计、 

  索引设计和查询设计方面。反过来说,最大的性能问题常常是由其中这些相同方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,以上这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。