【SQL Server 优化性能的几个方面】(转)

转自：http://blog.csdn.net/feixianxxx/article/details/5524819

 

    SQL Server 优化性能的几个方面

 

(一).数据库的设计

   可以参看最近论坛上出现一个精华帖http://topic.csdn.net/u/20100415/10/a377d835-acbd-4815-8bcb-b367f88ac8b5.html?92227
   数据库设计包含物理设计和逻辑设计:
   物理设计上可以通过使用RAID硬件架构。
   简单说下使用策略：
a.RAID0可以用在只读的数据库数据表，或者是经过复制过来的数据库上，如果你对数据丢失不敏感的话也可以使用，总之这个level下是高性能无冗余；
b.RAID 5 与RAID1 不同之处就是多了奇偶校验，所有的奇偶校验的信息会遍布各个磁盘，性能上要比RAID1高些，但是一旦发生磁盘I/O失败，就会造成性能急剧下降，同时这种方法也在RAID0 与RAID1间折了中，是比较通用的做法。
c.RAID 10 就是RAID0 与 RAID1的组合，它提供了高性能，高可用性，性能上要比RAID5好，特别适合大量写入的应用程序，但是就是成本比较高无论是多少块磁盘你都是将损失一半的磁盘存储

  逻辑设计上可以通过使用数据库的一些组件比如表、约束等，在这里首先提下文件组的使用。当你经费不足,无法购买一个完整的RAID系统的时候,你可以考虑文件组。
  文件和文件组体系结构一些小节：http://blog.csdn.net/feixianxxx/archive/2010/01/28/5267290.aspx
  它的原理就是多硬盘同时读取，减少磁盘空间争夺，提高读取效率。

 

我在这再稍微说几点：

a.范式和数据表的分割和合并
  我们在设计数据库的时候一般会追求规范，一般会至少达到3NF.在这之后，也许会因为表中某些经常用于存储查询的字段而把它们从表中分割，否则大量用户同时大量访问一个热门表,效率会变差。

  当然有时候也会进行合并表的设计，这样可以用少量的冗余换来减少过多连接的运算。
  具体关于这个设计表到什么程度，是否需要分割和合并都是因业务需求和系统承载力而定的,可以参看更多这方面的书籍。

 

b.主键与外键
  作为表与表之间的联系，最直接的就是通过主外键进行联系。主键对于一个表来说是非常重要的，它能在表中作为行的唯一标识存在，要求：唯一性,不可为NULL和最小性及容易获取。

  其中最小性指的是作为主键一定要窄。如果键值过大，由于经常存取，它会让数据库系统变得没有效率，而且它需要维护。一般使用1-4字节的字段作为主键。它在作为集群索引时也要保持字段的长度适宜，否则索引过长。

  定义了外键的约束不需要索引，但是由于连接运算的需要，我们通常需要在建立完外键后手动加上必要的索引。

 

c.表字段的取舍
  尽量让你的表字段类型“适可而止”，即在符合一般需求长度的前提下，最多稍微增加长度，不要过多浪费。字段存储直接体现在页中。行长越短,一页就可以存储越多的记录行。
  当我们从硬盘中取数据的时候以页为单位，一页中包含的记录越多，代表放入缓存区的记录就越多，吞吐量就越大。
  1.如果字段的内容比较固定 比如性别 身份证号码 手机号码等 就是用定长的 char 或者nchar
  2.如果字段的内容变化幅度比较大 则介意使用 varchar或者nvarchar
  3.一般不要让字段可以为NULL，这样会带来一些处理上不必要的消耗，可以使用默认值代替它
  4.尽可能的使用约束来维护数据完整性，不要过分依赖触发器或者存储过程来维护,切忌有些简单的记录规则不要用前端程序代码判断。

  5.大型字段如nvarchar(max)，char(max)，binary(max)，text等字段应该切割到独立表，并用主键跟本来的源表相互关联，因为这些数据类型会造成访问的缓慢，修改等操作的锁定，而我们在查询的时候又用不到这些字段一般

  6.表字段不要太多，一般一行的长度不要过长，这样会引起每页存储的记录太少，有必要的时候可以切割有些字段，将一个表分成2表 甚至更多表存储。
  
d.日常维护计划安排
  数据维护计划,比如备份,重建索引,复制数据等批处理消耗资源的操作尽量在系统空闲的时候进行。

 

e.前端程序对基础表的存取
  尽量让程序通过据库中的存储过程，视图，函数来存取数据，不要让其直接在基础表上进行操作。

 

f.将在线分析和在线事务的访问分开

2者的区别很大，具体可以参考MSDN。简单来说前者适合查询，后者多用于插入更新删除以及部分查询。

 

g.做好归档的计划

将什么归档 归档周期是什么 归档后如何抽取查询 归档的安全性等问题都需要考虑。

 

(二).数据库查询的优化（T-SQL优化）
  这个内容太大了，就挑着点来说
  
1.尽量多的使用查询参数（SARG）
  它的一般格式：字段 部分的运算符 《常数或者变量》
  这里的部分运算符包括：= > < >= <= BETWEEN AND 还有LIKE 后面不是以%开头的。

  非SARG的语法，索引一般不起作用。下面列举一些违反SARG语法的做法：

  a.对数据字段进行了运算
    我直接举例子(测试过的)： where col_1+col_2='ab' 是不可以用到索引的 ;
                   where col1='a' adn col_2='b'则可以用到字段上的索引;
                   where col_3+1=4 不可以用到；where col_3=4-1 可以用到

  b.不要对字段使用逆运算符
    不要对字段使用某些逆运算符,比如   not in not like  (2000的书上说<> != !>等也是会屏蔽索引的 我在08上是可以使用索引的 欢迎有2000环境的朋友测试)
     例子（测试过的）:where id not like '1%' 或者where id not in(1,2,3) 都是不能用到索引的
  
 c.不要对字段使用函数
   使用比如 substring left datediff 等等函数 但是你可以巧妙的用SARG方式来替代某些函数的功能
   比如 left(col,2)='av' 可以用 col like 'av%' 或者 abs(col-1)>100 可以用 col>101 or col<-99

 

 d.不要使用OR运算符
   使用了OR运算符，多个条件中的字段只要有一个没有合适的索引，其他字段的索引都失去索引效果，都将整表扫描。（注意索引扫描和表扫描效率差不多）
 
 但是注意在where中使用非SARG也不一定就用不到索引。有时候包含非SARG的条件 但是还是会对SARG部分进行索引利用的。

 

2.表之间的联接

参考：http://blog.csdn.net/happyflystone/archive/2009/01/15/3786158.aspx

 

 

3.大量数据插入
 
 a.在进行大容量数据插入的时候，可以通过BCP BULK INSERT选项尽量屏蔽表上的触发器、约束,甚至删除表上索引，这样可以让插入操作快很多。
   如果你担心数据的不规则，可以先将数据导入到一个临时表，然后再数据库里面过滤掉不规则数据后，再次由临时表插入目标表。
 b.同样的大容量插入，推荐使用BULK INSERT。因为通常情况下，它比BCP要快。
 c.使用BCP 或者 BULK INSERT插入时候，采用表锁定而不是默认的记录锁。
 d.如服务器多核，可以尽量让多客户端并行对表插入。
 e.如果插入的数据需要转换，不要使用DTS等工具直接对记录的转化,可以使用先插入临时表，在临时表内转化后进行第二次导入。

 

4.一些其他注意事项

1.进行少进行排序操作。DISTINCT、Order By、Union 等关键词 只能在需要的时候才用；

2.大批处理数据时，没有LOG操作一定比逐条且经由2此写入的动作快。SELECT INTO 比 INSERT SELECT 快，TRUNCATE TABLE 比DELETE TABLE（不含WHERE ）快；

3.在05及以上的版本引用数据库对象时，最好使用两节名称格式。因为SQL SERVER对单节名称对象首先在当前用户的默认架构下寻找对象名字，如果找不到，再去DBO下寻找。

4.注意联接操作和子查询操作的选择。有时候2者的结果一样，虽然语句结构不一样，但是产生的执行计划是一样的。当然，更多时候会出现不一致的执行计划，尽管结果是相同的，所以要注意不同语句的调试；

5.查询提示：注意，一般情况下，不需要使用HINT进行查询，因为会导致优化器不对查询进行优化；一般的HINT只会用于测试了解一些查询方式，索引，锁用法对查询的影响；
 

（三）索引的合理设计
     这又是很大的一块内容，也不是三言两语能说完的，介于内容的量和本人水平以及实践太少，就稍微写点：

       设计索引的准备工作：
  1.了解数据库的性质。看它是OLTP还是OLAP。如果是前者，由于频繁修改数据，索引建立索引要谨慎；对于后者，可以建立很多索引，以加快搜索速度；
  2.了解哪些查询是常用的查询；
  3.了解索引的选项，从这个点提高索引性能；
  4.确定索引存储位置：如将非聚集索引和数据存放于不同磁盘有利于提高性能
      建立索引的一些准则：
  1.对于经常变动的表，即经常进行插入、更新和删除操作的表，不能建立太多的索引；反之，则可以建立比较多的索引，这样有利于查询优化器的选择；
  2.索引列务必尽量保持较小的宽度；
  3.对于小型表，即含数据量比较少的表，可以不建立索引，因为索引的使用有时候还不如进行表扫描来的快；
  4.当视图包含聚合运算，大型表连接的时候可以使用索引视图来显著提高性能，注意这里的表不能经常变更；
  4.选择重复性少的列做索引列，重复性大的列 比如性别列这样的列不如不建索引；
  5.使用合理的填充因子；
  6.为经常用于查询中的谓词和连接条件的和分组的排序的列加上索引；
  7.组合索引(覆盖索引)，即由多个列组成的索引，当查询所需要的列都包含其中的时候可以显著提高性能，因为它只需要索引页的内容就够了。我们要注意在这些索引列中
    要将经常使用的列放在尽量靠前，列的排序为从最不重复道最重复的列；这里还有一个索引叫包含性列索引，它是一个特殊的非聚集索引，它可以包含一些非键
    列，将这些列加到索引页的叶级别，这同样可以在查询所需要的列都包含其中的时候可以显著提高性能，这2种列 合成为 多列索引；

     我提供几篇关于索引的文章： 
     http://www.cnblogs.com/bhtfg538/(里面有几篇)
     http://hi.baidu.com/dusongw/blog/item/4090493d6ec0cdee3d6d97a6.html

 

（四）前端应用程序设计
      应用程序作为数据的控制使用端，它将决定何时使用数据，如何使用数据，得来的数据结果又如何处理。它将直接影响服务器端的活动，对整个性能非常关键。
      下面说几点应用程序设计的注意点：
1.减少网络流量
   你可以通过使用存储过程，甚至可以使用 SET NOCOUNT 设置来禁用一些影响行数。还有就是要尽可能少的返回数据量，什么意思呢？就是说,如果你的程序只需要表
   中的某些数据，你就不要返回整个表的数据，然后再筛选，直接通过条件在服务器筛选好后再传过来。

2.限定锁定超时 
   不要让你的查询无限运行，应该用适当的API设置查询超时。

3.直到必要的时候才使用游标

4.事务要尽可能的短

 

5.确保将应用程序设计为可避免死锁。

 

（五）其他的一些措施：
 1.优化服务器性能
      服务器配置选项一般都会有默认的自动调整，你可以通过实际情况，在确定有利于你的系统优化的前提下修改某些选项。
      比如可以修改以下几个方面：
      SQL Server 内存;I/0子系统；Windows Server选项。

2.强化硬件设备
      加内存、加硬盘容量、升级RAID等等

3.设计联合数据库服务器
      对于大型的系统，往往需要多个服务器平衡各层的处理负荷。

    

 

 

                                           推荐：海爷写的SQL SERVER性能优化综述http://blog.csdn.net/Haiwer/archive/2008/08/25/2826881.aspx
                                           本文参考：性能调优（胡百敬）