如何优化SQL Server数据库查询(三)

 

 

优化 SQL Server 数据库
 
查询速度慢的原因很多，常见如下几种：　
　　 1 、没有索引或者没有用到索引 ( 这是查询慢最常见的问题，是程序设计的缺陷 ) 　　
　　 2 、 I/O 吞吐量小，形成了瓶颈效应。　　
　　 3 、没有创建计算列导致查询不优化。　　
　　 4 、内存不足。
　　 5 、网络速度慢。
　　 6 、查询出的数据量过大；（可以采用多次查询，其他的方法降低数据量）　　
　　 7 、锁或者死锁 ( 这也是查询慢最常见的问题，是程序设计的缺陷 ) 　　
　　 8 、 sp_lock,sp_who, 活动的用户查看 , 原因是读写竞争资源。　　
　　 9 、返回了不必要的行和列。
　　 10 、查询语句不好，没有优化。

 

可以通过如下方法来优化查询 : 　 　

一、硬件方面：
　　 1 、把数据、日志、索引放到不同的 I/O 设备上，增加读取速度，以前可以将 Tempdb 应放在 RAID0 上， SQL2000 不在支持。数据量（尺寸）越大，提高 I/O 越重要 . 　　
　　 2 、纵向、横向分割表，减少表的尺寸 (sp_spaceuse) 　　
　　 3 、升级硬件　　

        5 、提高网速 ; 　　
　　 6 、扩大服务器的内存；

        7 、增加服务器 CPU 个数 ;

二、 SQL 语句方面：

4 、根据查询条件 , 建立索引 , 优化索引、 优化访问方式，限制结果集的数据量。

13 、在查询 Select 语句中用 Where 字句限制返回的行数 , 避免表扫描 , 如果返回不必要的数据，浪费了服务器的 I/O 资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表 , 后果严重。　

17 、注意 UNion 和 UNion all 的区别。 UNION all 好 　　
　　 18 、注意使用 DISTINCT ，在没有必要时不要用 ，它同 UNION 一样会使查询变慢。重复的记录在查询里是没有问题的　　

19 、查询时不要返回不需要的行、列

34 、没有必要时不要用 DISTINCT 和 ORDER BY ， 这些动作可以改在客户端执行。它们增加了额外的开销。这同 UNION 和 UNION ALL 一样的道理。　　　
　　 35 、在 IN 后面值的列表中，将出现最频繁的值放在最前面 ，出现得最少的放在最后面，减少判断的次数。　　

29 、 Between 在某些时候比 IN 速度更快 ,Between 能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where title in (' 男 ',' 女 ') Select * from chineseresume where between ' 男 ' and ' 女 ' 是一样的。由于 in 会在比较多次，所以有时会慢些。　　

21 、用 select top 100 / 10 Percent 来限制用户返回的行数或者 SET ROWCOUNT 来限制操作的行 ; 　　　　　

33 、尽量少用视图，它的效率低。对视图操作比直接对表操作慢 , 可以用 stored procedure 来代替她。特别的是不要用视图嵌套 , 嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的 SQL 。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销 , 查询受到干扰 . 为了加快视图的查询， MsSQL 增加了视图索引的功能。　

28 、如果要插入大的二进制值到 Image 列，使用存储过程，千万不要用内嵌 Insert 来插入 ( 不知 JAVA 是否 ) 。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值 . 存储过程就没有这些动作 : 方法： Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。　　

24 、如果使用了 IN 或者 OR 等时发现查询没有走索引，使用显示申明指定索引： Select * FROM PersonMember (INDEX = IX_Title) Where processid IN (' 男 ' ， ' 女 ') 　
　　 30 、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。　　
　　 31 、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。　　
　　 32 、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关 , 如果查询需要用到联合索引，用 UNION all 执行的效率更高 . 多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。　　　
　　 36 、当用 Select INTO 时，它会锁住系统表 (sysobjects ， sysindexes 等等 ) ，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个连接中 Select * from sysobjects 可以看到 Select INTO 会锁住系统表， Create table 也会锁系统表 ( 不管是临时表还是系统表 ) 。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。　　
　　 37 、一般在 GROUP BY 个 HAVING 字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优： select 的 Where 字句选择所有合适的行， Group By 用来分组个统计行， Having 字句用来剔除多余的分组。这样 Group By 个 Having 的开销小，查询快 . 对于大的数据行进行分组和 Having 十分消耗资源。如果 Group BY 的目的不包括计算，只是分组，那么用 Distinct 更快

38 、一次更新多条记录比分多次更新每次一条快 , 就是说批处理好

39 、少用临时表，尽量用结果集和 Table 类性的变量来代替它 ,Table 类型的变量比临时表好　　
　　 40 、在 SQL2000 下，计算字段是可以索引的，需要满足的条件如下：　　
　　 a 、计算字段的表达是确定的　　
　　 b 、不能用在 TEXT,Ntext ， Image 数据类型　　
　　 c 、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, ……. 　　
　　 41 、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL, 可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。以前由于 SQL SERVER 对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。 SQL2000 支持 UDFs, 现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程　　
　　 42 、不要在一句话里再三的使用相同的函数，浪费资源 , 将结果放在变量里再调用更快　　
　　 43 、 Select COUNT(*) 的效率教低，尽量变通他的写法，而 EXISTS 快 . 同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！！！