还剩6页未读,继续阅读
文本内容:
mysql海量数据库的查询优化及分页算法方案文章分类:数据库转自链接 http://www.111cn.cn/database/110/256c64abb774f5680a270b456166f
795.htm select*fromtable1wherename=‘zhangsan‘andtID10000和执行:select*fromtable1wheretID10000andname=‘zhangsan‘一些人不知道以上两条语句的执行效率是否一样因为如果简单的从语句先后上看这两个语句的确是不一样如果tID是一个聚合索引那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name=‘zhangsan‘的而后再根据限制条件条件tID10000来提出查询结果事实上这样的担心是不必要的SQLSERVER中有一个“查询分析优化器”它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间也就是说它能实现自动优化虽然查询优化器可以根据where子句自动的进行查询优化但大家仍然有必要了解一下“查询优化器”的工作原理如非这样有时查询优化器就会不按照您的本意进行快速查询在查询分析阶段查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用如果一个阶段可以被用作一个扫描参数SARG)那么就称之为可优化的并且可以利用索引快速获得所需数据SARG的定义用于限制搜索的一个操作因为它通常是指一个特定的匹配一个值得范围内的匹配或者两个以上条件的AND连接形式如下列名操作符常数或变量或常数或变量操作符列名列名可以出现在操作符的一边而常数或变量出现在操作符的另一边如Name=’张三’价格50005000价格Name=’张三’and价格5000如果一个表达式不能满足SARG的形式那它就无法限制搜索的范围了也就是SQLSERVER必须对每一行都判断它是否满足Where子句中的所有条件所以一个索引对于不满足SARG形式的表达式来说是无用的介绍完SARG后我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验
1、Like语句是否属于SARG取决于所使用的通配符的类型如namelike‘张%’这就属于SARG而namelike‘%张’就不属于SARG原因是通配符%在字符串的开通使得索引无法使用
2、or会引起全表扫描Name=’张三’and价格5000符号SARG而Name=’张三’or价格5000则不符合SARG使用or会引起全表扫描
3、非操作符、函数引起的不满足SARG形式的语句不满足SARG形式的语句最典型的情况就是包括非操作符的语句如NOT、!=、、!、!、NOTEXISTS、NOTIN、NOTLIKE等另外还有函数下面就是几个不满足SARG形式的例子ABS价格5000Namelike‘%三’有些表达式如Where价格*25000SQLSERVER也会认为是SARGSQLSERVER会将此式转化为Where价格2500/2但我们不推荐这样使用因为有时SQLSERVER不能保证这种转化与原始表达式是完全等价的
4、IN的作用相当与OR语句Select*fromtable1wheretidin23和Select*fromtable1wheretid=2ortid=3是一样的都会引起全表扫描如果tid上有索引其索引也会失效
5、尽量少用NOT
6、exists和in的执行效率是一样的很多资料上都显示说exists要比in的执行效率要高同时应尽可能的用notexists来代替notin但事实上我试验了一下发现二者无论是前面带不带not二者之间的执行效率都是一样的因为涉及子查询我们试验这次用SQLSERVER自带的pubs数据库运行前我们可以把SQLSERVER的statisticsI/O状态打开1)selecttitlepricefromtitleswheretitle_idinselecttitle_idfromsaleswhereqty30该句的执行结果为表‘sales‘扫描计数18逻辑读56次物理读0次预读0次表‘titles‘扫描计数1逻辑读2次物理读0次预读0次2)selecttitlepricefromtitleswhereexistsselect*fromsaleswheresales.title_id=titles.title_idandqty30第二句的执行结果为表‘sales‘扫描计数18逻辑读56次物理读0次预读0次表‘titles‘扫描计数1逻辑读2次物理读0次预读0次我们从此可以看到用exists和用in的执行效率是一样的
7、用函数charindex和前面加通配符%的LIKE执行效率一样前面我们谈到如果在LIKE前面加上通配符%那么将会引起全表扫描所以其执行效率是低下的但有的资料介绍说用函数charindex来代替LIKE速度会有大的提升经我试验发现这种说明也是错误的selectgidtitlefariqireaderfromtgongwenwherecharindex‘刑侦支队‘reader0andfariqi‘2004-5-5‘用时7秒另外扫描计数4逻辑读7155次物理读0次预读0次selectgidtitlefariqireaderfromtgongwenwherereaderlike‘%‘+‘刑侦支队‘+‘%‘andfariqi‘2004-5-5‘用时7秒另外扫描计数4逻辑读7155次物理读0次预读0次
8、union并不绝对比or的执行效率高我们前面已经谈到了在where子句中使用or会引起全表扫描一般的我所见过的资料都是推荐这里用union来代替or事实证明这种说法对于大部分都是适用的selectgidfariqineibuyonghureadertitlefromTgongwenwherefariqi=‘2004-9-16‘orgid9990000用时68秒扫描计数1逻辑读404008次物理读283次预读392163次selectgidfariqineibuyonghureadertitlefromTgongwenwherefariqi=‘2004-9-16‘ unionselectgidfariqineibuyonghureadertitlefromTgongwenwheregid9990000用时9秒扫描计数8逻辑读67489次物理读216次预读7499次看来用union在通常情况下比用or的效率要高的多但经过试验笔者发现如果or两边的查询列是一样的话那么用union则反倒和用or的执行速度差很多虽然这里union扫描的是索引而or扫描的是全表selectgidfariqineibuyonghureadertitlefromTgongwenwherefariqi=‘2004-9-16‘orfariqi=‘2004-2-5‘用时6423毫秒扫描计数2逻辑读14726次物理读1次预读7176次selectgidfariqineibuyonghureadertitlefromTgongwenwherefariqi=‘2004-9-16‘ unionselectgidfariqineibuyonghureadertitlefromTgongwenwhere fariqi=‘2004-2-5‘用时11640毫秒扫描计数8逻辑读14806次物理读108次预读1144次
9、字段提取要按照“需多少、提多少”的原则避免“select*”我们来做一个试验selecttop10000gidfariqireadertitlefromtgongwenorderbygiddesc用时4673毫秒selecttop10000gidfariqititlefromtgongwenorderbygiddesc用时1376毫秒selecttop10000gidfariqifromtgongwenorderbygiddesc用时80毫秒由此看来我们每少提取一个字段数据的提取速度就会有相应的提升提升的速度还要看您舍弃的字段的大小来判断
10、count*不比count字段慢某些资料上说用*会统计所有列显然要比一个世界的列名效率低这种说法其实是没有根据的我们来看selectcount*fromTgongwen用时1500毫秒selectcountgidfromTgongwen 用时1483毫秒selectcountfariqifromTgongwen用时3140毫秒selectcounttitlefromTgongwen用时52050毫秒从以上可以看出如果用count*和用count主键的速度是相当的而count*却比其他任何除主键以外的字段汇总速度要快而且字段越长汇总的速度就越慢我想如果用count*SQLSERVER可能会自动查找最小字段来汇总的当然如果您直接写count主键将会来的更直接些
11、orderby按聚集索引列排序效率最高我们来看gid是主键fariqi是聚合索引列)selecttop10000gidfariqireadertitlefromtgongwen用时196毫秒扫描计数1逻辑读289次物理读1次预读1527次selecttop10000gidfariqireadertitlefromtgongwenorderbygidasc用时4720毫秒扫描计数1逻辑读41956次物理读0次预读1287次selecttop10000gidfariqireadertitlefromtgongwenorderbygiddesc用时4736毫秒扫描计数1逻辑读55350次物理读10次预读775次selecttop10000gidfariqireadertitlefromtgongwenorderbyfariqiasc用时173毫秒扫描计数1逻辑读290次物理读0次预读0次selecttop10000gidfariqireadertitlefromtgongwenorderbyfariqidesc用时156毫秒扫描计数1逻辑读289次物理读0次预读0次从以上我们可以看出不排序的速度以及逻辑读次数都是和“orderby聚集索引列”的速度是相当的但这些都比“orderby非聚集索引列”的查询速度是快得多的同时按照某个字段进行排序的时候无论是正序还是倒序速度是基本相当的
12、高效的TOP事实上在查询和提取超大容量的数据集时影响数据库响应时间的最大因素不是数据查找而是物理的I/0操作如selecttop10*fromselecttop10000gidfariqititlefromtgongwenwhereneibuyonghu=‘办公室‘orderbygiddescasaorderbygidasc这条语句从理论上讲整条语句的执行时间应该比子句的执行时间长但事实相反因为子句执行后返回的是10000条记录而整条语句仅返回10条语句所以影响数据库响应时间最大的因素是物理I/O操作而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了TOP关键词是SQLSERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词经笔者在实践中的应用发现TOP确实很好用效率也很高但这个词在另外一个大型数据库ORACLE中却没有这不能说不是一个遗憾虽然在ORACLE中可以用其他方法如rownumber)来解决在以后的关于“实现千万级数据的分页显示存储过程”的讨论中我们就将用到TOP这个关键词到此为止我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法当然我们介绍的这些方法都是“软”方法在实践中我们还要考虑各种“硬”因素如网络性能、服务器的性能、操作系统的性能甚至网卡、交换机等
三、实现小数据量和海量数据的通用分页显示存储过程建立一个web应用分页浏览功能必不可少这个问题是数据库处理中十分常见的问题经典的数据分页方法是:ADO纪录集分页法也就是利用ADO自带的分页功能利用游标)来实现分页但这种分页方法仅适用于较小数据量的情形因为游标本身有缺点游标是存放在内存中很费内存游标一建立就将相关的记录锁住直到取消游标游标提供了对特定集合中逐行扫描的手段一般使用游标来逐行遍历数据根据取出数据条件的不同进行不同的操作而对于多表和大表中定义的游标大的数据集合)循环很容易使程序进入一个漫长的等待甚至死机更重要的是对于非常大的数据模型而言分页检索时如果按照传统的每次都加载整个数据源的方法是非常浪费资源的现在流行的分页方法一般是检索页面大小的块区的数据而非检索所有的数据然后单步执行当前行最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”这个存储过程用了游标由于游标的局限性所以这个方法并没有得到大家的普遍认可后来网上有人改造了此存储过程下面的存储过程就是结合我们的办公自动化实例写的分页存储过程Createprocedurepagination1@pagesizeint --页面大小如每页存储20条记录@pageindexint --当前页码assetnocountonbegindeclare@indextabletableidintidentity11nidint --定义表变量declare@PageLowerBoundint --定义此页的底码declare@PageUpperBoundint --定义此页的顶码set@PageLowerBound=@pageindex-1*@pagesizeset@PageUpperBound=@PageLowerBound+@pagesizesetrowcount@PageUpperBoundinsertinto@indextablenidselectgidfromTGongwenwherefariqidateaddday-365getdateorderbyfariqidescselectO.gidO.midO.titleO.fadanweiO.fariqifromTGongwenO@indextabletwhereO.gid=t.nidandt.id@PageLowerBoundandt.id=@PageUpperBoundorderbyt.idendsetnocountoff以上存储过程运用了SQLSERVER的最新技术。