如何写出高效的SQL脚本《一》-ASP.Net
发布日期:2022-02-05 18:27:45
浏览次数:13
分类:技术文章
本文共 4697 字,大约阅读时间需要 15 分钟。
如何写出高效的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. 高效的TOP 事实上,在查询和提取超大容量的数据集时,根据需要提取一次需要的数据,如果允许尽量使用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)转载地址:https://blog.csdn.net/lisong58420/article/details/2453775 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
路过,博主的博客真漂亮。。
[***.116.15.85]2024年04月22日 02时58分26秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
Scala学习第十六天 包的定义、包对象、包的引用、包的隐式引用代码实战
2021-07-06
Scala学习第十七天 包、类、对象、成员、伴生类、伴生对象访问权限实战彻底详解
2021-07-06
Scala学习第十八天 文件的读取、写入、控制台输入操作代码实战
2021-07-06
Scala学习第十九天 正则表达式、与模式匹配结合的的Reg代码实战
2021-07-06
剑指offer:栈的压入、弹出序列(java)
2021-07-06
剑指offer:往上到下打印二叉树(java)
2021-07-06
剑指offer:二叉搜索树的后序遍历序列(java)
2021-07-06
剑指offer:二叉树中和为某一值的所有路径(java)
2021-07-06
剑指offer:复杂链表的复制(java)
2021-07-06
剑指offer:二叉搜索树与双向链表(java)
2021-07-06
剑指offer:字符串的排列(java)
2021-07-06
剑指offer:字符串的组合(java)
2021-07-06
剑指offer:数组中出现次数超过一半的数字(java)
2021-07-06
剑指offer:最小的k个数(java)
2021-07-06
剑指offer:连续子数组的最大和(java)
2021-07-06
剑指offer:从1到n整数中1出现的次数(java)
2021-07-06
剑指offer:把数组排成最小的数(java)
2021-07-06
剑指offer:丑数(java)
2019-05-03
剑指offer:第一个只出现一次的字
2019-05-03
剑指offer:数组中的逆序对(java)
2019-05-03