如何写出高效的SQL脚本《一》
注:测试记录:每个表记录数: 3040000 。
如何写出高效的SQL脚本:
1. 设计如何满足SARG形式的SQL脚本
SARG 的定义:用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值得范围内的匹配或者两个以上条件的 AND 连接。[可以理解为索引扫描]形式如下:
列名 操作符 < 常数 或 变量 >
或
< 常数 或 变量 > 操作符列名
列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如:
Name=’ATA’
数量 >5000
5000< 数量
Name=’ATA’ and 数量 >5000
如果一个表达式不能满足 SARG 的形式,那它就无法限制搜索的范围了,也就是 SQL SERVER 必须对每一行都判断它是否满足 WHERE 子句中的所有条件。所以一个索引对于不满足 SARG 形式的表达式来说是无用的
2. Like
Like 语句是否属于 SARG 取决于所使用的通配符的类型
如: name like ‘ATA%’ ,这就属于 SARG
而: name like ‘%ATA’ , 就不属于 SARG 。
原因是通配符 % 在字符串的开头使得索引无法使用。
3. OR 和 IN
or 会引起全表扫描
Name=’ATA’ and 数量 >5000 符号 SARG ,而: Name=’ATA’ or 数量 >5000 则不符合 SARG 。使用 or 和In会引起全表扫描
4. 非操作符、函数引起的不满足 SARG 形式的语句
不满足 SARG 形式的语句最典型的情况就是包括非操作符的语句,如: NOT 、 != 、 <> 、 !< 、 !> 、 NOT EXISTS 、 NOT IN 、 NOT LIKE , is null, not null 等,另外还有函数。下面就是几个不满足 SARG 形式的例子:
ABS( 数量 )<5000
Name like ‘%ATA’
有些表达式,如:
WHERE 数量 *2>5000
SQL SERVER 也会认为是 SARG , SQL SERVER 会将此式转化为:
WHERE 数量 >2500/2
不推荐这样使用,因为有时 SQL SERVER 不能保证这种转化与原始表达式是完全等价的。
5. 函数 charindex() 、前面加通配符 % 的 LIKE ,后面加 % 的效率比较
如果在 LIKE 前面加上通配符 % ,那么将会引起全表扫描,所以其执行效率是低下的。用函数 charindex() 来代替 LIKE 速度会有大的提升的说法不对的,测试如下:
1. select fcandidateid,fcandidatename from tcandidate where fcandidatename like 'Tim%'
用时: 36 秒,记录结果数:200万
2. select fcandidateid,fcandidatename from tcandidate
where charindex('Tim',fcandidatename)>0
用时: 47 秒,记录结果数:200万
3. select fcandidateid,fcandidatename from tcandidate
where fcandidatename like '%Tim%'
用时: 45 秒,记录结果数:200万
通过以上 3 个例子可以看出,再使用 Like 的时候,后面加 “Tim%” 符合 SARG 规则,用时明显少于后两种,后两种的性能基本上差不多
如果非的模糊,比如: substring(fcandidatename,1,1)=’A’, 那么可以考虑这样: fcandidatename like ‘A%’ 来代替(因为这样用的是索引扫描,不是表扫描)
如何写出高效的SQL脚本《二》
6. 字段提取要按照 “ 需多少、提多少 ” 的原则,避免 “select *“
下面我来做一个测试:
a) select fcandidateid,fcandidatename from tcandidate where fcandidatename like 'Tim%'
用时: 35 秒 记录结果数:200万
b) select fcandidateid,fcandidatename,fCredentialsID,fbirthday from tcandidate where fcandidatename like 'Tim%'
用时: 51 秒记录结果数:200万
c) select fcandidateid from tcandidate where fcandidatename like 'Tim%'
用时: 23 秒 记录结果数:200万
由此看来,我们每少提取一个字段,数据的提取速度就会有相应的提升,当然提升的速度还要看您舍弃的字段的大小来判断。
7. order by 的使用,用那些字段作为排序效率比较高
1. 使用具有族索引或者 primary key 的字段排序。
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like 'Tim%' order by fcandidateid desc
用时: 45 秒 记录结果数:200万
2. 使用数字的字段(既不是 primary key, 也不是族索引、 foreign key )
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like 'Tim%' order by fRegionID desc
用时: 1 分 15 秒 记录结果数:200万
3. 使用字符串的字段 [ 数字字符串 ] (既不是 primary key, 也不是族索引、 foreign key )
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like 'Tim%' order by fCredentialsID desc
用时: 1 分 22 秒 记录结果数:200万
4. 使用字符串 [ 字母组成的字符串 ]
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like 'Tim%' order by fcandidatename desc
用时: 1 分 34 秒 记录结果数:200万
5. 使用日期字段
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like 'Tim%' order by fbirthday desc
用时: 1 分 9 秒 记录结果数:200万
从以上可以看出,使用族索引、 Primary key 的字段进行排序,速度是比较快的,另外,如果表有 foreign key 的字段,在排序的时候,可以优先考虑这些字段。
以上进行了数字、数字字符串、字母字符串、日期的排序,关于性能大家可以参看以上的测试结果,测试的结果日期字段的排序比数字的要高,这个可以在多测试一下类似的数据量,比较一下到底数字和日期字段到底哪个速度快些。
另外的几种情况,大家就一目了然了。
同时,按照某个字段进行排序的时候,无论是正序还是倒序,速度是基本相当的。
8. 插入大的二进制值到 Image 列需要注意的
如果要插入大的二进制值到 Image 列,使用存储过程,千万不要用内嵌 INsert 来插入。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍),服务器收到字符后又将他转换成二进制值 .
存储过程就没有这些动作 : 在前台调用这个存储过程传入二进制参数,这样处理速度明显改善。
9. 高效的 TO P
事实上,在查询和提取超大容量的数据集时,根据需要提取一次需要的数据,如果允许尽量使用top语句。如:
select top 50000 * from (
select top 2000000 fcandidateid,fcandidatename,fbirthday from tcandidate
where fcandidatename like 'Tim%'
order by fcandidateid desc) as a
用时: 1179 毫秒
大家可以仔细看上面的查询语句,可以和以上的语句项比较,就可以知道 top 的效率有多高了
这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。因为,子句执行后返回的是 2000000 条记录,而整条语句仅返回 50000 条语句,所以影响数据库响应时间最大的因素是物理 I/O 操作。而限制物理 I/O 操作此处的最有效方法之一就是使用 TOP 关键词了。 TOP 关键词是 SQL SERVER 中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。所以经过优化算法的TOP效率就很高了
10. 如何使用SQL的函数注意的问题
1. SQL的函数在SQL脚本中不同的位置消耗的成本就不一样
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID
from tcandidate where dateadd(year,5,fbirthday)='1981/09/08'
CPU:2079 用时: 25317 毫秒 记录: 1000000
2. select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fbirthday=dateadd(year,-5,'1981/09/08')
CPU:1219 用时: 21666 毫秒 记录结果: 1000000
所以从以上可以看出,不同的 SQL 函数方的位置不一样,性能和消耗的成本也不一样,总体原则把SQL函数放到条件的右边性能消耗的成本等比较低。
3. 注意使用 DISTINCT ,在没有必要时不要用,它同 UNION 一样会使查询变慢,如果确认结果集中没有重复的记录,请不要轻易用distict
11. 表和临时表的用法
1. 一般情况下尽量使用表变量而不用临时表,为何推荐表变量,请看下面的解释:
l 表变量(如局部变量)具有明确定义的范围,在该范围结束时会自动清除这些表变量。
l 与临时表相比,表变量导致存储过程的重新编译更少。
l 涉及表变量的事务仅维持表变量上更新的持续时间。因此,使用表变量时,需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分,所以事务回滚并不影响它们。
2. 什么时候使用表变量而不使用临时表
• 插入到表中的行数。
• 从中保存查询的重新编译的次数。
• 查询类型及其对性能的指数和统计信息的依赖性。
3. 关于表变量的缺陷,大家可以到 msdn 上搜索一下( table variable )