Freigeben über

谈谈SQL 语句的优化技术 (2)

  • Artikel

三、优化SQL语句的系统设计原则

 

仅仅简化还是不够。SQL语句的优化在系统的设计阶段就要通盘考虑。系统设计越合理，需要优化或后期返工的地方就越少。系统逻辑流程如果不合理，那么常会导致本来需要一个操作就可以解决的问题却要作好几个操作才能实现，反映在数据库上就是发出过多或过复杂的SQL语句。所以数据库系统设计是系统高性能运行的首要关键。

 

很多系统开发者更关心功能的实现而不是数据库系统的整体运行性能。相当多的开发者对数据库性能优化技术不是很了解，写出来的SQL语句往往缺乏效率。下面让我们罗列一些在设计阶段就应该考虑的和性能密切相关的一些原则。

 

 

1）限制结果集

 

要尽量减少返回的结果行，包括行数和字段列数。返回的结果越大，意味着相应的SQL语句的logical reads 就越大，对服务器的性能影响就越甚。一个很不好的设计就是返回表的所有数据：

 

Select * from tablename

 

即使表很小也会导致并发问题。更坏的情况是，如果表有上百万行的话，那后果将是灾难性的。它不但可能带来极重的磁盘IO，更有可能把数据库缓冲区中的其他缓存数据挤出，使得这些数据下次必须再从磁盘读取。没有索引能够优化没有where子句的语句。在这样的语句运行的时候，大量别的小语句会出现超时或缓慢的现象。一个系统只要有几条这样的大语句不定时运行，你几乎肯定会注意到系统性能的不稳定性。所以，必须设计良好的SQL语句，使得其有where语句或TOP语句来限制结果集大小。它通常应该是这个样子的：

 

Select col1,col2,……from table1 where colx=… and coly=…

 

没有where语句，或不能预知where语句会返回多少行，是开发者常忽略的地方。程序测试的时候是没有问题的，因为那个时候表的数据还少，不能暴露性能问题。但随着程序部署到实际环境当中，表数据越来越多，问题就会越来越突出。一个稳定优秀的系统应该能够考虑到数据的增长而预知SQL语句会返回多少数据，进而作相应的处理。如果你实在不能知道SQL语句会返回多少数据，那么可以使用TOP n来限制结果集,比如：

 

Select TOP 100 col1,col2,……from table1 where colx=… and coly=…

 

其中n不要过大。我就看到有系统采用n＝20000的做法，似乎n大了点。你得想一想，我的程序确实需要返回这么多的数据吗？程序的使用者会看这么多的数据吗？

 

如果语句结果确实很多，可以考虑将结果集进行分页。分页是限制结果集的一种有效手段。比如说，先使用TOP n方式返回头100条数据。只有用户点击下一页的时候才再发出查询获取下100行。

 

2）合理的表设计

 

在表的设计中，比较关键的问题是如何处理表的历史数据。表数据会越来越大。你一定需要考虑表的数据增长问题。比如预先考虑一天，一个星期，或一个月内表的数据变化。常见的做法是安排作业定时把表的数据导出到别处，使得数据库保持一定的大小，从而获得稳定一致的性能。也有的系统根据时间设计表，比如说根据月份设计表，如2005一月表，2005二月表，2006三月表等。这样做的好处是每个月表的大小基本稳定一致，性能也可以保证。不好的地方是管理复杂些，程序也要设计成能够根据时间访问不同的月份表。

 

一个非常令人兴奋的消息是SQL Server 2005将支持表分区技术。利用表分区技术可以实现数据表的流动窗口功能。在流动窗口中可以轻易的把历史数据移出，把新的数据加入，从而使表的大小基本保持稳定。

 

另外，表的设计未必需要非常范式化。有一定的字段冗余可以增加SQL语句的效率，减少JOIN的数目，提高语句的执行速度。

 

3）OLAP和OLTP模块要分开

 

OLAP和OLTP类型的语句是截然不同的。前者往往需要扫描整个表做统计分析，索引对这样的语句几乎没有多少用处。索引只能够加快那些如sum，group by之类的聚合运算。因为这个原因，几乎很难对OLAP类型的SQL语句进行优化。而OLTP语句则只需要访问表的很小一部分数据，而且这些数据往往可以从内存缓存中得到。为了避免OLAP 和OLTP语句相互影响，这两类模块需要分开运行在不同服务器上。因为OLAP语句几乎都是读取数据，没有更新和写入操作，所以一个好的经验是配置一台standby 服务器，然后OLAP只访问standby服务器。

 

常常有客户咨询我说数据库系统一到月底或一个月的某个时段就变得很慢。你猜猜为什么呢？原来月底是系统生成OLAP报表的时候。报表意味着对几乎全部表数据进行扫描统计，服务器负担自然很重，系统当然比平时慢。我听说某些ERP系统生成一个报表需要好几个小时呢。

4）使用存储过程

 

可以考虑使用存储过程封装那些复杂的SQL语句或商业逻辑，这样做有几个好处。一是存储过程的执行计划可以被缓存在内存中较长时间，减少了重新编译的时间。二是存储过程减少了客户端和服务器的繁复交互。三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。

 

四、结束语

读完本文，你应该知道简化SQL语句的技术以及系统设计要考虑到的一些原则。应用这些技术能够提高数据库系统的整体性能。数据库系统优化是个很大的话题，本文只是罗列一些有用的经验，更多的需要你的实践。

Comments

  • Anonymous
    October 19, 2013
    受教了