sql语句的优化

性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。

为了获得稳定的执行性能，SQL语句越简单越好。对复杂的SQL语句，要设法对之进行简化。

常见的简化规则如下：
 
1）不要有超过5个以上的表连接（JOIN）
2）考虑使用临时表或表变量存放中间结果。
3）少用子查询
4）视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
 

连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。

最好是把连接拆开成较小的几个部分逐个顺序执行。

优先执行那些能够大量减少结果的连接。

拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。

如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。

 

连接是outer join，非常不好。因为outer join意味着必须对左表或右表查询所有行。

如果表很大而没有相应的where语句，那么outer join很容易导致table scan或index scan。

要尽量使用inner join避免scan整个表。

优化建议：
 
1）使用临时表存放t1表的结果,能大大减少logical reads（或返回行数）的操作要优先执行。

 仔细分析语句，你会发现where中的条件全是针对表t1的，所以直接使用上面的where子句查询表t1，然后把结果存放再临时表＃t1中：
 
Select t1….. into #tt1 from t1 where…(和上面的where一样)
 
2）再把＃tt1和其他表进行连接:
 
Select #t1…
Left outer join …
Left outer join…
 
 
3）修改 like 程序，去掉前置百分号。like语句却因为前置百分号而无法使用索引
4）从系统设计的角度修改语句，去掉outer join。
5）考虑组合索引或覆盖索引消除clustered index scan。
 
上面1和2点建议立即消除了worktable，性能提高了几倍以上，效果非常明显。

 

 

 

1）限制结果集
 
要尽量减少返回的结果行，包括行数和字段列数。

返回的结果越大，意味着相应的SQL语句的logical reads 就越大，对服务器的性能影响就越甚。

一个很不好的设计就是返回表的所有数据：
 
Select * from tablename
 
即使表很小也会导致并发问题。更坏的情况是，如果表有上百万行的话，那后果将是灾难性的。

它不但可能带来极重的磁盘IO，更有可能把数据库缓冲区中的其他缓存数据挤出，使得这些数据下次必须再从磁盘读取。

必须设计良好的SQL语句，使得其有where语句或TOP语句来限制结果集大小。

2）合理的表设计
 
SQL Server 2005将支持表分区技术。利用表分区技术可以实现数据表的流动窗口功能。

在流动窗口中可以轻易的把历史数据移出，把新的数据加入，从而使表的大小基本保持稳定。
 
另外，表的设计未必需要非常范式化。有一定的字段冗余可以增加SQL语句的效率，减少JOIN的数目，提高语句的执行速度。

3）OLAP和OLTP模块要分开
 
OLAP和OLTP类型的语句是截然不同的。前者往往需要扫描整个表做统计分析，索引对这样的语句几乎没有多少用处。

索引只能够加快那些如sum，group by之类的聚合运算。因为这个原因，几乎很难对OLAP类型的SQL语句进行优化。

而OLTP语句则只需要访问表的很小一部分数据，而且这些数据往往可以从内存缓存中得到。

为了避免OLAP 和OLTP语句相互影响，这两类模块需要分开运行在不同服务器上。

因为OLAP语句几乎都是读取数据，没有更新和写入操作，所以一个好的经验是配置一台standby 服务器，然后OLAP只访问standby服务器。

4）使用存储过程
 
可以考虑使用存储过程封装那些复杂的SQL语句或商业逻辑，这样做有几个好处。

一是存储过程的执行计划可以被缓存在内存中较长时间，减少了重新编译的时间。

二是存储过程减少了客户端和服务器的繁复交互。

三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。