sql 优化

   1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘 积最小为最佳方案。  
  2.查看执行方案的方法--   用set   showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,30  
 3 where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，

 

所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。  
  1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。  
2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。  
3.要善于使用存储过程，它使SQL变得更加灵活和高效。  
 从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会 涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。  

 

速度，影响它的因数太多了，且数据量越大越明显。  
  1、存储  
        将硬盘分成NTFS格式，NTFS比FAT32快，并看你的数据文件大小，1G以上你可以采用多数据库文件，这样可以将存取负载分散到多个物理硬盘或磁盘阵列上。  
   
  2、tempdb  
        tempdb也应该被单独的物理硬盘或磁盘阵列上,建议放在RAID   0上，这样它的性能最高,不要对它设置最大值让它自动增长  
   
  3、日志文件  
        日志文件也应该和数据文件分开在不同的理硬盘或磁盘阵列上，这样也可以提高硬盘I/O性能。  
   
  4、分区视图  
        就是将你的数据水平分割在集群服务器上，它适合大规模OLTP,SQL群集上，如果你数据库不是访问特别大不建议使用。  
   
  5、簇索引  
        你的表一定有个簇索引，在使用簇索引查询的时候，区块查询是最快的，如用between，应为他是物理连续的，你应该尽量减少对它的updaet,应为这可以使它物理不连续。  
   
  6、非簇索引  
        非簇索引与物理顺序无关，设计它时必须有高度的可选择性，可以提高查询速度，但对表update的时候这些非簇索引会影响速度，且占用空间大，如果你愿意用空间和修改时间换取速度可以考虑。  
   
  7、索引视图  
        如果在视图上建立索引,那视图的结果集就会被存储起来，对与特定的查询性能可以提高很多，但同样对update语句时它也会严重减低性能，一般用在数据相对稳定的数据仓库中。  
   
  8、维护索引  
        你在将索引建好后，定期维护是很重要的，用dbcc   showcontig来观察页密度、扫描密度等等，及时用dbcc   indexdefrag来整理表或视图的索引,在必要的时候用dbcc   dbreindex来重建索引可以受到良好的效果。  
   
  不论你是用几个表1、2、3点都可以提高一定的性能，5、6、8点你是必须做的，至于4、7点看你的需求，我个人是不建议的

1、要合理使用索引  
  索引是数据库一个重要的构成部分，很多人都会忽略它，其实索引的根本目的就是  
  为了提高查询效率。  
  使用原则如下：    
  在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则  
  由优化器自动生成索引。    
   
  在频繁进行排序或分组（即进行group   by或order   by操作）的列上建立索引。    
   
  在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要  
  建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就  
  无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度  
  。    
  如果待排序的列有多个，可以在这些列上建立复合索引（compound   index）。    
   
  在写sql语句时就必须注意有些写法是会使得数据库无法使用索引的，比如IS   NULL  
  IS   NOT   NULL，IN   ，NOT   IN   等。。。  
   
  2．避免或简化排序    
  应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生  
  输出时，优化器就避免了排序的步骤。以下是一些影响因素：    
  ●索引中不包括一个或几个待排序的列；    
  ●group   by或order   by子句中列的次序与索引的次序不一样；    
  ●排序的列来自不同的表。    
  为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可  
  能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应  
  当试图简化它，如缩小排序的列的范围等。    
   
  3．消除对大型表行数据的顺序存取    
  在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存  
  取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10  
  亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学  
  生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要  
  做连接，就要在“学号”这个连接字段上建立索引。    
  还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的  
  where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作  
  ：    
  SELECT   ＊   FROM   orders   WHERE   (customer_num=104   AND   order_num>1001)   OR    
  order_num=1008    
  虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使  
  用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该  
  改为如下语句：    
  SELECT   ＊   FROM   orders   WHERE   customer_num=104   AND   order_num>1001    
  UNION    
  SELECT   ＊   FROM   orders   WHERE   order_num=1008    
  这样就能利用索引路径处理查询。    
   
  4．避免相关子查询    
  一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中  
  的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此  
  应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的  
  行。    
   
  5．避免困难的正规表达式    
  MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗  
  费时间。例如：SELECT   ＊   FROM   customer   WHERE   zipcode   LIKE   “98_   _   _”    
  即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如  
  果把语句改为SELECT   ＊   FROM   customer   WHERE   zipcode   >“98000”，在执行查询  
  时就会利用索引来查询，显然会大大提高速度。    
  另外，还要避免非开始的子串。例如语句：SELECT   ＊   FROM   customer   WHERE    
  zipcode[2，3]   >“80”，在where子句中采用了非开始子串，因而这个语句也不会  
  使用索引。    
   
  6．使用临时表加速查询,SQL2000中还可以使用表变量来代替临时表    
  把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序  
  操作，而且在其他方面还能简化优化器的工作。例如：    
  SELECT   cust.name，rcvbles.balance，……other   columns    
  FROM   cust，rcvbles    
  WHERE   cust.customer_id   =   rcvlbes.customer_id    
  AND   rcvblls.balance>0    
  AND   cust.postcode>“98000”    
  ORDER   BY   cust.name    
  如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个  
  临时文件中，并按客户的名字进行排序：    
  SELECT   cust.name，rcvbles.balance，……other   columns    
  FROM   cust，rcvbles    
  WHERE   cust.customer_id   =   rcvlbes.customer_id    
  AND   rcvblls.balance>0    
  ORDER   BY   cust.name    
  INTO   TEMP   cust_with_balance    
  然后以下面的方式在临时表中查询：    
  SELECT   ＊   FROM   cust_with_balance    
  WHERE   postcode>“98000”    
  临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘  
  I/O，所以查询工作量可以得到大幅减少。    
  注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意  
  不要丢失数据。  
     
  7．用排序来取代非顺序存取    
  非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这  
  一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。    
  有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。

优化的重点:  
   
  1.合理的表结构设计  
  2.适当的索引  
  3.合理的查询语句

 

 

我们经常是写好查询SQL,然后调用程序执行SQL。但是它内部的工作流程是怎样的呢？先做哪一步，然后做哪一步等,我想还有大部分朋友和我一样都不一定清楚。　
    第一步：应用程序把查询SQL语句发给服务器端执行。
 我们在数据层执行SQL语句时,应用程序会连接到相应的数据库服务器,把SQL语句发送给服务器处理。
    第二步：服务器解析请求的SQL语句。

    1:SQL计划缓存,经常用查询分析器的朋友大概都知道这样一个事实,往往一个查询语句在第一次运行的时候需要执行特别长的时间,但是如果你马上或者在一定时间内运行同样的语句,会在很短的时间内返回查询结果。   

原因:

  1):服务器在接收到查询请求后,并不会马上去数据库查询,而是在数据库中的计划缓存中找是否有相对应的执行计划,如果存在,就直接调用已经编译好的执行计划,节省了执行计划的编译时间。
2):如果所查询的行已经存在于数据缓冲存储区中,就不用查询物理文件了,而是从缓存中取数据,这样从内存中取数据就会比从硬盘上读取数据快很多,提高了查询效率.数据缓冲存储区会在后面提到。
 2:如果在SQL计划缓存中没有对应的执行计划,服务器首先会对用户请求的SQL语句进行语法效验,如果有语法错误,服务器会结束查询操作,并用返回相应的错误信息给调用它的应用程序。
  注意:此时返回的错误信息中,只会包含基本的语法错误信息,例如select 写成selec等,错误信息中如果包含一列表中本没有的列,此时服务器是不会检查出来的,因为只是语法验证,语义是否正确放在下一步进行。

3:语法符合后,就开始验证它的语义是否正确,例如,表名,列名,存储过程等等数据库对象是否真正存在,如果发现有不存在的,就会报错给应用程序,同时结束查询。
4:接下来就是获得对象的解析锁,我们在查询一个表时,首先服务器会对这个对象加锁,这是为了保证数据的统一性,如果不加锁,此时有数据插入,但因为没有加锁的原因,查询已经将这条记录读入,而有的插入会因为事务的失败会回滚,就会形成脏读的现象。
5:接下来就是对数据库用户权限的验证,SQL语句语法,语义都正确,此时并不一定能够得到查询结果,如果数据库用户没有相应的访问权限,服务器会报出权限不足的错误给应用程序,在稍大的项目中,往往一个项目里面会包含好几个数据库连接串,这些数据库用户具有不同的权限,有的是只读权限,有的是只写权限,有的是可读可写,根据不同的操作选取不同的用户来执行,稍微不注意,无论你的SQL语句写的多么完善,完美无缺都没用。
  6:解析的最后一步,就是确定最终的执行计划。当语法,语义,权限都验证后,服务器并不会马上给你返回结果,而是会针对你的SQL进行优化,选择不同的查询算法以最高效的形式返回给应用程序。例如在做表联合查询时,服务器会根据开销成本来最终决定采用hash join,merge join ,还是loop join,采用哪一个索引会更高效等等,不过它的自动化优化是有限的,要想写出高效的查询SQL还是要优化自己的SQL查询语句。
            当确定好执行计划后,就会把这个执行计划保存到SQL计划缓存中,下次在有相同的执行请求时,就直接从计划缓存中取,避免重新编译执行计划。
  第三步：语句执行。
              服务器对SQL语句解析完成后,服务器才会知道这条语句到底表态了什么意思,接下来才会真正的执行SQL语句。
  些时分两种情况:
            1):如果查询语句所包含的数据行已经读取到数据缓冲存储区的话,服务器会直接从数据缓冲存储区中读取数据返回给应用程序,避免了从物理文件中读取,提高查询速度。
            2):如果数据行没有在数据缓冲存储区中,则会从物理文件中读取记录返回给应用程序,同时把数据行写入数据缓冲存储区中,供下次使用。
            说明:SQL缓存分好几种,这里有兴趣的朋友可以去搜索一下,有时因为缓存的存在,使得我们很难马上看出优化的结果,因为第二次执行因为有缓存的存在,会特别快速,所以一般都是先消除缓存,然后比较优化前后的性能表现,这里有几个常用的方法:

DBCC DROPCLEANBUFFERS
从缓冲池中删除所有清除缓冲区。
DBCC FREEPROCCACHE
从过程缓存中删除所有元素。
DBCC FREESYSTEMCACHE
　　从所有缓存中释放所有未使用的缓存条目。SQL Server 2005 数据库引擎会事先在后台清理未使用的缓存条目，以使内存可用于当前条目。但是，可以使用此命令从所有缓存中手动删除未使用的条目。

    这只能基本消除SQL缓存的影响,目前好像没有完全消除缓存的方案,如果大家有,请指教。
    结论:只有知道了服务执行应用程序提交的SQL的操作流程才能很好的调试我们的应用程序。
            1:确保SQL语法正确;
            2:确保SQL语义上的正确性,即对象是否存在;
            3:数据库用户是否具有相应的访问权限。

分页一定要充分利用数据库的性能。最重要的就是索引的排序，一定要设置索引排序方式与你要显示的记录排列方式相同，否则需要在每个SELECT后面加入order   by   id   desc,asc。加入order   by会降低效率20%左右。尽量通过索引排序不要使用order   by   。  
   
  第一页：select   top   10   *   form   tab   where   ……  
   
  降序的。  
  第N页：select   top   10   *   form   tab   where   ……   and   id   <   (select   min(id)   from   (select   top   (n-1)*10   id   from   tab   where   ……   )   )  
   
  升序的  
  第N页：select   top   10   *   form   tab   where   ……   and   id   >   (select   max(id)   from   (select   top   (n-1)*10   id   from   tab   where   ……   )   )