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)限制结果集
要尽量减少返回的结果行,包括行数和字段列数。
返回的结果越大,意味着相应的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语句或商业逻辑,这样做有几个好处。
一是存储过程的执行计划可以被缓存在内存中较长时间,减少了重新编译的时间。
二是存储过程减少了客户端和服务器的繁复交互。
三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。
索引优化
创建索引的关键
不言而喻,索引的最大好处是它可以极大减少SQL语句的logical reads数目,从而极大减少语句的执行时间。
创建索引的关键是索引要能够大大减少语句的logical reads。一个索引好不好,主要看它减少的logical reads多不多。
set statistics io on
如果Logical reads很大,而返回的行数很少,也即两者相差较大,那么往往意味者语句需要优化。
Logical reads中包含该语句从内存数据缓冲区中访问的页数和从物理磁盘读取的页数。
而physical reads表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页。
Read-ahead reads是SQL Server为了提高性能而产生的预读。预读可能会多读取一些数据。
优化的时候我们主要关注Logical Reads就可以了。
注意如果physical Reads或Read-ahead reads很大,那么往往意味着语句的执行时间(duration)里面会有一部分耗费在等待物理磁盘IO上。
二、单字段索引,组合索引和覆盖索引
单字段索引是指只有一个字段的索引,而组合索引指有多个字段构成的索引。
1. 对出现在where子句中的字段加索引
set statistics profile on
set statistics profile命令将输出语句的执行计划。
也许你会问,为什么不用SET SHOWPLAN_ALL呢?使用SET SHOWPLAN_ALL也是可以的。
不过set statistics profile输出的是SQL 语句的运行时候真正使用的执行计划,
而SET SHOWPLAN_ALL输出的是预计(Estimate)的执行计划。
使用SET SHOWPLAN_ALL是后面的语句并不会真正运行。
用了Table Scan,也就是对整个表进行了全表扫描。全表扫描的性能通常是很差的,要尽量避免。
如果上面的select语句是数据库系统经常运行的关键语句, 那么应该对它创建相应的索引。
创建索引的技巧之一是对经常出现在where条件中的字段创建索引
Table Scan也变成了Index Seek,性能极大提高
设法避免Table scan或Index scan是优化SQL 语句使用的常用技巧。通常Index Seek需要的logical reads比前两者要少得多。
2.组合索引
组合索引中字段的顺序是非常重要的,越是唯一的字段越是要靠前。
另外,无论是组合索引还是单个列的索引,尽量不要选择那些唯一性很低的字段。
比如说,在只有两个值0和1的字段上建立索引没有多大意义。
所以如果对单字段进行索引,建议使用set statistics profile来验证索引确实被充分使用。logical reads越少的索引越好。
3.覆盖索引
覆盖索引能够使得语句不需要访问表仅仅访问索引就能够得到所有需要的数据。
因为聚集索引叶子节点就是数据所以无所谓覆盖与否,所以覆盖索引主要是针对非聚集索引而言。
执行计划中除了index seek外,还有一个Bookmark Lookup关键字。
也就是说为得到一行数据起码有两次IO,一次访问索引,一次访问基本表。
如果语句返回的行数很多,那么Bookmark Lookup操作的开销是很大的。
覆盖索引能够避免昂贵的Bookmark Lookup操作,减少IO的次数,提高语句的性能。
覆盖索引需要包含select子句和WHERE子句中出现的所有字段。Where语句中的字段在前面,select中的在后面。
logical reads,是大大减少了。Bookmark Lookup操作也消失了。所以创建覆盖索引是减少logical reads提升语句性能的非常有用的优化技巧。
实际上索引的创建原则是比较复杂的。有时候你无法在索引中包含了Where子句中所有的字段。
在考虑索引是否应该包含一个字段时,应考虑该字段在语句中的作用。
比如说如果经常以某个字段作为where条件作精确匹配返回很少的行,那么就绝对值得为这个字段建立索引。
再比如说,对那些非常唯一的字段如主键和外键,经常出现在group by,order by中的字段等等都值得创建索引。
问题1,是否值得在identity字段上建立聚集索引。
答案取决于identity 字段如何在语句中使用。如果你经常根据该字段搜索返回很少的行,那么在其上建立索引是值得的。
反之如果identity字段根本很少在语句中使用,那么就不应该对其建立任何索引。
如果表的80%以上的语句都是读操作,那么索引可以多些。但是不要太多。
特别是不要对那些更新频繁的表其建立很多的索引。很少表有超过5个以上的索引。
过多的索引不但增加其占用的磁盘空间,也增加了SQL Server 维护索引的开销。
一种原因是该语句返回的结果超过了表的20%数据,使得SQL Server 认为scan比seek更有效。
你可以使用命令UPDATE STATISTICS tablename with FULLSCAN来更新它。
只有同步的准确的statistics才能保证SQL Server 产生正确的执行计划。
过时的老的statistics常会导致SQL Server生成不够优化的甚至愚蠢的执行计划。
所以如果你的表频繁更新,而你又觉得和之相关的SQL语句运行缓慢,不妨试试UPDATE STATISTIC with FULLSCAN 语句。
问题5、什么使用聚集索引,什么时候使用非聚集索引
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语句或商业逻辑,这样做有几个好处。
一是存储过程的执行计划可以被缓存在内存中较长时间,减少了重新编译的时间。
二是存储过程减少了客户端和服务器的繁复交互。
三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。
索引优化
创建索引的关键
不言而喻,索引的最大好处是它可以极大减少SQL语句的logical reads数目,从而极大减少语句的执行时间。
创建索引的关键是索引要能够大大减少语句的logical reads。一个索引好不好,主要看它减少的logical reads多不多。
set statistics io on
如果Logical reads很大,而返回的行数很少,也即两者相差较大,那么往往意味者语句需要优化。
Logical reads中包含该语句从内存数据缓冲区中访问的页数和从物理磁盘读取的页数。
而physical reads表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页。
Read-ahead reads是SQL Server为了提高性能而产生的预读。预读可能会多读取一些数据。
优化的时候我们主要关注Logical Reads就可以了。
注意如果physical Reads或Read-ahead reads很大,那么往往意味着语句的执行时间(duration)里面会有一部分耗费在等待物理磁盘IO上。
二、单字段索引,组合索引和覆盖索引
单字段索引是指只有一个字段的索引,而组合索引指有多个字段构成的索引。
1. 对出现在where子句中的字段加索引
set statistics profile on
set statistics profile命令将输出语句的执行计划。
也许你会问,为什么不用SET SHOWPLAN_ALL呢?使用SET SHOWPLAN_ALL也是可以的。
不过set statistics profile输出的是SQL 语句的运行时候真正使用的执行计划,
而SET SHOWPLAN_ALL输出的是预计(Estimate)的执行计划。
使用SET SHOWPLAN_ALL是后面的语句并不会真正运行。
用了Table Scan,也就是对整个表进行了全表扫描。全表扫描的性能通常是很差的,要尽量避免。
如果上面的select语句是数据库系统经常运行的关键语句, 那么应该对它创建相应的索引。
创建索引的技巧之一是对经常出现在where条件中的字段创建索引
Table Scan也变成了Index Seek,性能极大提高
设法避免Table scan或Index scan是优化SQL 语句使用的常用技巧。通常Index Seek需要的logical reads比前两者要少得多。
2.组合索引
组合索引中字段的顺序是非常重要的,越是唯一的字段越是要靠前。
另外,无论是组合索引还是单个列的索引,尽量不要选择那些唯一性很低的字段。
比如说,在只有两个值0和1的字段上建立索引没有多大意义。
所以如果对单字段进行索引,建议使用set statistics profile来验证索引确实被充分使用。logical reads越少的索引越好。
3.覆盖索引
覆盖索引能够使得语句不需要访问表仅仅访问索引就能够得到所有需要的数据。
因为聚集索引叶子节点就是数据所以无所谓覆盖与否,所以覆盖索引主要是针对非聚集索引而言。
执行计划中除了index seek外,还有一个Bookmark Lookup关键字。
也就是说为得到一行数据起码有两次IO,一次访问索引,一次访问基本表。
如果语句返回的行数很多,那么Bookmark Lookup操作的开销是很大的。
覆盖索引能够避免昂贵的Bookmark Lookup操作,减少IO的次数,提高语句的性能。
覆盖索引需要包含select子句和WHERE子句中出现的所有字段。Where语句中的字段在前面,select中的在后面。
logical reads,是大大减少了。Bookmark Lookup操作也消失了。所以创建覆盖索引是减少logical reads提升语句性能的非常有用的优化技巧。
实际上索引的创建原则是比较复杂的。有时候你无法在索引中包含了Where子句中所有的字段。
在考虑索引是否应该包含一个字段时,应考虑该字段在语句中的作用。
比如说如果经常以某个字段作为where条件作精确匹配返回很少的行,那么就绝对值得为这个字段建立索引。
再比如说,对那些非常唯一的字段如主键和外键,经常出现在group by,order by中的字段等等都值得创建索引。
问题1,是否值得在identity字段上建立聚集索引。
答案取决于identity 字段如何在语句中使用。如果你经常根据该字段搜索返回很少的行,那么在其上建立索引是值得的。
反之如果identity字段根本很少在语句中使用,那么就不应该对其建立任何索引。
如果表的80%以上的语句都是读操作,那么索引可以多些。但是不要太多。
特别是不要对那些更新频繁的表其建立很多的索引。很少表有超过5个以上的索引。
过多的索引不但增加其占用的磁盘空间,也增加了SQL Server 维护索引的开销。
一种原因是该语句返回的结果超过了表的20%数据,使得SQL Server 认为scan比seek更有效。
你可以使用命令UPDATE STATISTICS tablename with FULLSCAN来更新它。
只有同步的准确的statistics才能保证SQL Server 产生正确的执行计划。
过时的老的statistics常会导致SQL Server生成不够优化的甚至愚蠢的执行计划。
所以如果你的表频繁更新,而你又觉得和之相关的SQL语句运行缓慢,不妨试试UPDATE STATISTIC with FULLSCAN 语句。
问题5、什么使用聚集索引,什么时候使用非聚集索引