海口网站排名,无锡做网站哪家好,asp网站版权,网站维护协议书KeyLife富翁笔记 作者: HongYuan标题: SQL Server 索引结构及其使用#xff08;二#xff09; 关键字: 分类: 个人专区 密级: 公开 (评分:, 回复: 0, 阅读: 552) SQL Server 索引结构及其使用#xff08;二#xff09; 作者#xff1a;freedk 一、深入浅出理解索引…KeyLife富翁笔记 作者: HongYuan标题: SQL Server 索引结构及其使用二 关键字: 分类: 个人专区 密级: 公开 (评分: , 回复: 0, 阅读: 552) »» SQL Server 索引结构及其使用二 作者freedk 一、深入浅出理解索引结构 改善SQL语句 很多人不知道SQL语句在SQL SERVER中是如何执行的他们担心自己所写的SQL语句会被SQL SERVER误解。比如 select * from table1 where namezhangsan and tID 10000和执行:select * from table1 where tID 10000 and namezhangsan 一些人不知道以上两条语句的执行效率是否一样因为如果简单的从语句先后上看这两个语句的确是不一样如果tID是一个聚合索引那么后一句仅仅从表的10000条以后的记录中查找就行了而前一句则要先从全表中查找看有几个namezhangsan的而后再根据限制条件条件tID10000来提出查询结果。 事实上这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间也就是说它能实现自动优化。 虽然查询优化器可以根据where子句自动的进行查询优化但大家仍然有必要了解一下“查询优化器”的工作原理如非这样有时查询优化器就会不按照您的本意进行快速查询。 在查询分析阶段查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数SARG那么就称之为可优化的并且可以利用索引快速获得所需数据。 SARG的定义用于限制搜索的一个操作因为它通常是指一个特定的匹配一个值得范围内的匹配或者两个以上条件的AND连接。形式如下 列名 操作符 常数 或 变量或常数 或 变量 操作符列名列名可以出现在操作符的一边而常数或变量出现在操作符的另一边。如Name’张三’价格50005000价格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(价格)5000Name like ‘%三’有些表达式如WHERE 价格*25000SQL SERVER也会认为是SARGSQL SERVER会将此式转化为WHERE 价格2500/2但我们不推荐这样使用因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。 4、IN 的作用相当与OR 语句 Select * from table1 where tid in (2,3)和Select * from table1 where tid2 or tid3是一样的都会引起全表扫描如果tid上有索引其索引也会失效。 5、尽量少用NOT 6、exists 和 in 的执行效率是一样的 很多资料上都显示说exists要比in的执行效率要高同时应尽可能的用not exists来代替not in。但事实上我试验了一下发现二者无论是前面带不带not二者之间的执行效率都是一样的。因为涉及子查询我们试验这次用SQL SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开 1select title,price from titles where title_id in (select title_id from sales where qty30)该句的执行结果为 表 sales。扫描计数 18逻辑读 56 次物理读 0 次预读 0 次。 表 titles。扫描计数 1逻辑读 2 次物理读 0 次预读 0 次。 2select title,price from titles where exists (select * from sales where sales.title_idtitles.title_id and qty30)第二句的执行结果为 表 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 fariqi2004-5-5用时7秒另外扫描计数 4逻辑读 7155 次物理读 0 次预读 0 次。select gid,title,fariqi,reader from tgongwen where reader like % 刑侦支队 % and fariqi2004-5-5用时7秒另外扫描计数 4逻辑读 7155 次物理读 0 次预读 0 次。 8、union并不绝对比or的执行效率高 我们前面已经谈到了在where子句中使用or会引起全表扫描一般的我所见过的资料都是推荐这里用union来代替or。事实证明这种说法对于大部分都是适用的。 select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi2004-9-16 or gid9990000用时68秒。扫描计数 1逻辑读 404008 次物理读 283 次预读 392163 次。select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi2004-9-16 unionselect gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid9990000用时9秒。扫描计数 8逻辑读 67489 次物理读 216 次预读 7499 次。 看来用union在通常情况下比用or的效率要高的多。 但经过试验笔者发现如果or两边的查询列是一样的话那么用union则反倒和用or的执行速度差很多虽然这里union扫描的是索引而or扫描的是全表。 select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi2004-9-16 or fariqi2004-2-5用时6423毫秒。扫描计数 2逻辑读 14726 次物理读 1 次预读 7176 次。select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi2004-9-16 unionselect gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi2004-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 tgongwenwhere neibuyonghu办公室order by gid desc) as aorder by gid asc 这条语句从理论上讲整条语句的执行时间应该比子句的执行时间长但事实相反。因为子句执行后返回的是10000条记录而整条语句仅返回10条语句所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用发现TOP确实很好用效率也很高。但这个词在另外一个大型数据库ORACLE中却没有这不能说不是一个遗憾虽然在ORACLE中可以用其他方法如rownumber来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中我们就将用到TOP这个关键词。 到此为止我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然我们介绍的这些方法都是“软”方法在实践中我们还要考虑各种“硬”因素如网络性能、服务器的性能、操作系统的性能甚至网卡、交换机等。 文章引自http://www.vckbase.com/document/viewdoc/?id1308 2005-9-20 10:56:18 转载于:https://www.cnblogs.com/ZhouXiHong/archive/2006/11/22/568248.html
