sql语句优化方式
sqlserver查询效率优化
很多⼈不知道SQL语句在SQL SERVER中是如何执⾏的，他们担⼼⾃⼰所写的SQL语句会被SQL SERVER误解。⽐如：
select * from table1 where name='zhangsan' and tID > 10000
和执⾏:
select * from table1 where tID > 10000 and name='zhangsan'
⼀些⼈不知道以上两条语句的执⾏效率是否⼀样，因为如果简单的从语句先后上看，这两个语句的确是不⼀样，如果tID是⼀个聚合索引，那么后⼀句仅仅从表的10000条以后的记录中查就⾏了；⽽前⼀句则要先从全表中查看有⼏个name='zhangsan'的，⽽后再根据限制条件条件tID>10000来提出查询结果。
事实上，这样的担⼼是不必要的。SQL SERVER中有⼀个“查询分析优化器”，它可以计算出where⼦句中的搜索条件并确定哪个索引能缩⼩表扫描的搜索空间，也就是说，它能实现⾃动优化。
虽然查询优化器可以根据where⼦句⾃动的进⾏查询优化，但⼤家仍然有必要了解⼀下“查询优化器”的⼯作原理，如⾮这样，有时查询优化器就会不按照您的本意进⾏快速查询。
在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有⽤。如果⼀个阶段可以被⽤作⼀个扫描参数（SARG），那么就称之为可优化的，并且可以利⽤索引快速获得所需数据。
SARG的定义：⽤于限制搜索的⼀个操作，因为它通常是指⼀个特定的匹配，⼀个值得范围内的匹配或者两个以上条件的AND连接。形式如下：
列名操作符 <；常数或变量>
或
<；常数或变量> 操作符列名
列名可以出现在操作符的⼀边，⽽常数或变量出现在操作符的另⼀边。如：
Name=’张三’
价格>5000
5000<；价格
Name=’张三’ and 价格>5000
如果⼀个表达式不能满⾜SARG的形式，那它就⽆法限制搜索的范围了，也就是SQL SERVER必须对每⼀⾏都判断它是否满⾜WHERE⼦句中的所有条件。所以⼀个索引对于不满⾜SARG形式的表达式来说是⽆⽤的。
介绍完SARG后，我们来总结⼀下使⽤SARG以及在实践中遇到的和某些资料上结论不同的经验：
1、Like语句是否属于SARG取决于所使⽤的通配符的类型
如：name like ‘张%’ ，这就属于SARG
⽽：name like ‘%张’ ，就不属于SARG。
原因是通配符%在字符串的开通使得索引⽆法使⽤。
2、or 会引起全表扫描
Name=’张三’ and 价格>5000 符号SARG，⽽：Name=’张三’ or 价格>5000 则不符合SARG。使⽤or会引起全表扫描。
3、⾮操作符、函数引起的不满⾜SARG形式的语句
不满⾜SARG形式的语句最典型的情况就是包括⾮操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。下⾯就是⼏个不满⾜SARG形式的例⼦：
ABS(价格)<5000
Name like ‘%三’
有些表达式，如：
WHERE 价格*2>5000
SQL SERVER也会认为是SARG，SQL SERVER会将此式转化为：
WHERE 价格>2500/2
但我们不推荐这样使⽤，因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。
4、IN 的作⽤相当与OR
语句：
Select * from table1 where tid in (2,3)
和
Select * from table1 where tid=2 or tid=3
是⼀样的，都会引起全表扫描，如果tid上有索引，其索引也会失效。
5、尽量少⽤NOT
6、exists 和 in 的执⾏效率是⼀样的
很多资料上都显⽰说，exists要⽐in的执⾏效率要⾼，同时应尽可能的⽤not exists来代替not in。但事实上，我试验了⼀下，发现⼆者⽆论是前⾯带不带not，⼆者之间的执⾏效率都是⼀样的。因为涉及⼦查询，我们试验这次⽤SQL SERVER⾃带的pubs数据库。运⾏前我们可以把SQL SERVER的statistics I/O状态打开。
（1）select title,price from titles where title_id in (select title_id from sales where qty>30)
该句的执⾏结果为：
表 'sales'。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。
表 'titles'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
（2）select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)
第⼆句的执⾏结果为：
表 'sales'。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。
表 'titles'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
我们从此可以看到⽤exists和⽤in的执⾏效率是⼀样的。
7、⽤函数charindex()和前⾯加通配符%的LIKE执⾏效率⼀样
前⾯，我们谈到，如果在LIKE前⾯加上通配符%，那么将会引起全表扫描，所以其执⾏效率是低下的。但有的资料介绍说，⽤函数charindex()来代替LIKE速度会有⼤的提升，经我试验，发现这种说明也是错误的：
select gid,title,fariqi,reader from tgongwen where charindex('刑侦⽀队',reader)>0 and fariqi>'2004-5-5'
⽤时：7秒，另外：扫描计数 4，逻辑读 7155 次，物理读 0 次，预读 0 次。
select gid,title,fariqi,reader from tgongwen where reader like '%' + '刑侦⽀队' + '%' and fariqi>'2004-5-5'
⽤时：7秒，另外：扫描计数 4，逻辑读 7155 次，物理读 0 次，预读 0 次。
8、union并不绝对⽐or的执⾏效率⾼
我们前⾯已经谈到了在where⼦句中使⽤or会引起全表扫描，⼀般的，我所见过的资料都是推荐这⾥⽤union来代替or。事实证明，这种说法对于⼤部分都是适⽤的。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000
⽤时：68秒。扫描计数 1，逻辑读 404008 次，物理读 283 次，预读 392163 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
⽤时：9秒。扫描计数 8，逻辑读 67489 次，物理读 216 次，预读 7499 次。
看来，⽤union在通常情况下⽐⽤or的效率要⾼的多。
但经过试验，笔者发现如果or两边的查询列是⼀样的话，那么⽤union则反倒和⽤or的执⾏速度差很多，虽然这⾥union扫描的是索引，⽽or扫描的是全表。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'
⽤时：6423毫秒。扫描计数 2，逻辑读 14726 次，物理读 1 次，预读 7176 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'
⽤时：11640毫秒。扫描计数 8，逻辑读 14806 次，物理读 108 次，预读 1144 次。
9、字段提取要按照“需多少、提多少”的原则，避免“select *”
我们来做⼀个试验：
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
⽤时：4673毫秒
select top 10000 gid,fariqi,title from tgongwen order by gid desc
⽤时：1376毫秒
select top 10000 gid,fariqi from tgongwen order by gid desc
⽤时：80毫秒
由此看来，我们每少提取⼀个字段，数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的⼤⼩来判断。
10、count(*)不⽐count(字段)慢
某些资料上说：⽤*会统计所有列，显然要⽐⼀个世界的列名效率低。这种说法其实是没有根据的。我们来看：
select count(*) from Tgongwen
⽤时：1500毫秒
select count(gid) from Tgongwen
⽤时：1483毫秒
select count(fariqi) from Tgongwen
⽤时：3140毫秒
select count(title) from Tgongwen
⽤时：52050毫秒
从以上可以看出，如果⽤count(*)和⽤count(主键)的速度是相当的，⽽count(*)却⽐其他任何除主键以外的字段汇总速度要快，⽽且字段越长，汇总的速度就越慢。我想，如果⽤count(*)， SQL SERVER
可能会⾃动查最⼩字段来汇总的。当然，如果您直接写count(主键)将会来的更直接些。
11、order by按聚集索引列排序效率最⾼
我们来看：（gid是主键，fariqi是聚合索引列）
select top 10000 gid,fariqi,reader,title from tgongwen
⽤时：196 毫秒。扫描计数 1，逻辑读 289 次，物理读 1 次，预读 1527 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
⽤时：4720毫秒。扫描计数 1，逻辑读 41956 次，物理读 0 次，预读 1287 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
⽤时：4736毫秒。扫描计数 1，逻辑读 55350 次，物理读 10 次，预读 775 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
⽤时：173毫秒。扫描计数 1，逻辑读 290 次，物理读 0 次，预读 0 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
⽤时：156毫秒。扫描计数 1，逻辑读 289 次，物理读 0 次，预读 0 次。
从以上我们可以看出，不排序的速度以及逻辑读次数都是和“order by 聚集索引列” 的速度是相当的，但这些都⽐“order by ⾮聚集索引列”的查询速度是快得多的。
同时，按照某个字段进⾏排序的时候，⽆论是正序还是倒序，速度是基本相当的。
12、⾼效的TOP
事实上，在查询和提取超⼤容量的数据集时，影响数据库响应时间的最⼤因素不是数据查，⽽是物理的I/0操作。如：
select top 10 * from (
select top 10000 gid,fariqi,title from tgongwen
where neibuyonghu='办公室'
order by gid desc) as a
order by gid asc
这条语句，从理论上讲，整条语句的执⾏时间应该⽐⼦句的执⾏时间长，但事实相反。因为，⼦句执⾏后返回的是10000条记录，⽽整条语句仅返回10条语句，所以影响数据库响应时间最⼤的因素是物理I/O操作。⽽限制物理I/O操作此处的最有效⽅法之⼀就是使⽤TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的⼀个⽤来提取前⼏条或前⼏个百分⽐数据的词。经笔者在实践中的应⽤，发现TOP确实很好⽤，效率也很⾼。但这个词在另外⼀个⼤型数据库ORACLE中却没有，这不能说不是⼀个遗憾，虽然在ORACLE中可以⽤其他⽅法（如：rownumber）来解决。在以后的关于“实现千万级数据的分页显⽰存储过程”的讨论中，我们就将⽤到TOP这个关键词。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。