优化
SQL Server
数据库
查询速度慢的原因很多,常见如下几种:
1 、没有索引或者没有用到索引 ( 这是查询慢最常见的问题,是程序设计的缺陷 )
2 、 I/O 吞吐量小,形成了瓶颈效应。
3 、没有创建计算列导致查询不优化。
4 、内存不足。
5 、网络速度慢。
6 、查询出的数据量过大;(可以采用多次查询,其他的方法降低数据量)
7 、锁或者死锁 ( 这也是查询慢最常见的问题,是程序设计的缺陷 )
8 、 sp_lock,sp_who, 活动的用户查看 , 原因是读写竞争资源。
9 、返回了不必要的行和列。
10 、查询语句不好,没有优化。
查询速度慢的原因很多,常见如下几种:
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 、升级硬件
1 、把数据、日志、索引放到不同的 I/O 设备上,增加读取速度,以前可以将 Tempdb 应放在 RAID0 上, SQL2000 不在支持。数据量(尺寸)越大,提高 I/O 越重要 .
2 、纵向、横向分割表,减少表的尺寸 (sp_spaceuse)
3 、升级硬件
5
、提高网速
;
6 、扩大服务器的内存;
6 、扩大服务器的内存;
7
、增加服务器
CPU
个数
;
二、
SQL
语句方面:
4
、根据查询条件
,
建立索引
,
优化索引、
优化访问方式,限制结果集的数据量。
13
、在查询
Select
语句中用
Where
字句限制返回的行数
,
避免表扫描
,
如果返回不必要的数据,浪费了服务器的
I/O
资源,加重了网络的负担降低性能。如果表很大,在表扫描的期间将表锁住,禁止其他的联接访问表
,
后果严重。
17
、注意
UNion
和
UNion all
的区别。
UNION all
好
18 、注意使用 DISTINCT ,在没有必要时不要用 ,它同 UNION 一样会使查询变慢。重复的记录在查询里是没有问题的
18 、注意使用 DISTINCT ,在没有必要时不要用 ,它同 UNION 一样会使查询变慢。重复的记录在查询里是没有问题的
19
、查询时不要返回不需要的行、列
34
、没有必要时不要用
DISTINCT
和
ORDER BY
,
这些动作可以改在客户端执行。它们增加了额外的开销。这同
UNION
和
UNION ALL
一样的道理。
35 、在 IN 后面值的列表中,将出现最频繁的值放在最前面 ,出现得最少的放在最后面,减少判断的次数。
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 更快
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 的返回值是不同的!!!