MSSQL编程：怎样让你的SQL运转得更快!
限制,如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。1、分歧理的索引计划----例：表record有620000行，试看在分歧的索引下，上面几个SQL的运转情形：
----1.在date上建有一非个聚集索引
selectcount(*)fromrecordwheredate>
19991201anddate<19991214andamount>
2000(25秒)
selectdate,sum(amount)fromrecordgroupbydate
(55秒)
selectcount(*)fromrecordwheredate>
19990901andplacein(BJ,SH)(27秒)
----剖析：
----date上有大批的反复值，在非聚集索引下，数据在物理上随机寄存在数据页上，在
局限查找时，必需实行一次表扫描才干找到这一局限内的全体行。
----2.在date上的一个聚集索引
selectcount(*)fromrecordwheredate>
19991201anddate<19991214andamount>
2000（14秒）
selectdate,sum(amount)fromrecordgroupbydate
（28秒）
selectcount(*)fromrecordwheredate>
19990901andplacein(BJ,SH)（14秒）
----剖析：
----在聚集索引下，数据在物理上按按次在数据页上，反复值也分列在一同，因此在范
围查找时，能够先找到这个局限的起末点，且只在这个局限内扫描数据页，制止了年夜范
围扫描，进步了查询速率。
----3.在place，date，amount上的组合索引
selectcount(*)fromrecordwheredate>
19991201anddate<19991214andamount>
2000（26秒）
selectdate,sum(amount)fromrecordgroupbydate
（27秒）
selectcount(*)fromrecordwheredate>
19990901andplacein(BJ,SH)（<1秒）
----剖析：
----这是一个不很公道的组合索引，由于它的前导列是place，第一和第二条SQL没有引
用place，因而也没有益用上索引；第三个SQL利用了place，且援用的一切列都包括在组
合索引中，构成了索引掩盖，以是它的速率长短常快的。
----4.在date，place，amount上的组合索引
selectcount(*)fromrecordwheredate>
19991201anddate<19991214andamount>
2000(<1秒)
selectdate,sum(amount)fromrecordgroupbydate
（11秒）
selectcount(*)fromrecordwheredate>
19990901andplacein(BJ,SH)（<1秒）
----剖析：
----这是一个公道的组合索引。它将date作为前导列，使每一个SQL都能够使用索引，并
且在第一和第三个SQL中构成了索引掩盖，因此功能到达了最优。
----5.总结：
----缺省情形下创建的索引长短聚集索引，但偶然它并非最好的；公道的索引计划要
创建在对各类查询的剖析和展望上。一样平常来讲：
----①.有大批反复值、且常常有局限查询
（between,>,<，>=,<=）和orderby
、groupby产生的列，可思索创建聚集索引；
----②.常常同时存取多列，且每列都含有反复值可思索创建组合索引；
----③.组合索引要只管使关头查询构成索引掩盖，其前导列必定是利用最频仍的列。
2、不充份的毗连前提：
----例：表card有7896行，在card_no上有一个非会萃索引，表account有191122行，在
account_no上有一个非会萃索引，试看在分歧的表毗连前提下，两个SQL的实行情形：
selectsum(a.amount)fromaccounta,
cardbwherea.card_no=b.card_no（20秒）
----将SQL改成：
selectsum(a.amount)fromaccounta,
cardbwherea.card_no=b.card_noanda.
account_no=b.account_no（<1秒）
----剖析：
----在第一个毗连前提下，最好查询计划是将account作外层表，card作内层表，使用
card上的索引，其I/O次数可由以下公式预算为：
----外层表account上的22541页+（外层表account的191122行*内层表card上对应外层
表第一行所要查找的3页）=595907次I/O
----在第二个毗连前提下，最好查询计划是将card作外层表，account作内层表，使用
account上的索引，其I/O次数可由以下公式预算为：
----外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每
行所要查找的4页）=33528次I/O
----可见，只要充份的毗连前提，真实的最好计划才会被实行。
----总结：
----1.多表操纵在被实践实行前，查询优化器会依据毗连前提，列出几组大概的毗连方
案并从中找出体系开支最小的最好计划。毗连前提要充份思索带有索引的表、行数多的
表；表里表的选择可由公式：外层表中的婚配行数*内层表中每次查找的次数断定，乘
积最小为最好计划。
----2.检察实行计划的办法--用setshowplanon，翻开showplan选项，就能够看到连
接按次、利用何种索引的信息；想看更具体的信息，需用sa脚色实行dbcc(3604,310,30
2)。
3、不成优化的where子句
----1.例：以下SQL前提语句中的列都建有得当的索引，但实行速率却十分慢：
select*fromrecordwhere
substring(card_no,1,4)=5378(13秒)
select*fromrecordwhere
amount/30<1000（11秒）
select*fromrecordwhere
convert(char(10),date,112)=19991201（10秒）
----剖析：
----where子句中对列的任何操纵了局都是在SQL运转时逐列盘算失掉的，因而它不能不
举行表搜刮，而没有利用该列下面的索引；假如这些了局在查询编译时就可以失掉，那末
就能够被SQL优化器优化，利用索引，制止表搜刮，因而将SQL重写成上面如许：
select*fromrecordwherecard_nolike
5378%（<1秒）
select*fromrecordwhereamount
<1000*30（<1秒）
select*fromrecordwheredate=1999/12/01
（<1秒）
----你会发明SQL分明快起来！
----2.例：表stuff有200000行，id_no上有非聚集索引，请看上面这个SQL：
selectcount(*)fromstuffwhereid_noin(0,1)
（23秒）
----剖析：
----where前提中的in在逻辑上相称于or，以是语法剖析器会将in(0,1)转化
为id_no=0orid_no=1来实行。我们希冀它会依据每一个or子句分离查找，再将了局
相加，如许能够使用id_no上的索引；但实践上（依据showplan）,它却接纳了"OR战略"
，即先掏出满意每一个or子句的行，存进一时数据库的事情表中，再创建独一索引以往失落
反复行，最初从这个一时表上钩算了局。因而，实践历程没有益用id_no上索引，而且完
成工夫还要受tempdb数据库功能的影响。
----理论证实，表的行数越多，事情表的功能就越差，当stuff有620000行时，实行时
间竟到达220秒！还不如将or子句分隔：
selectcount(*)fromstuffwhereid_no=0
selectcount(*)fromstuffwhereid_no=1
----失掉两个了局，再作一次加法合算。由于每句都利用了索引，实行工夫只要3秒，
在620000行下，工夫也只要4秒。大概，用更好的办法，写一个复杂的存储历程：
createproccount_stuffas
declare@aint
declare@bint
declare@cint
declare@dchar(10)
begin
select@a=count(*)fromstuffwhereid_no=0
select@b=count(*)fromstuffwhereid_no=1
end
select@c=@a+@b
select@d=convert(char(10),@c)
print@d
----间接算出了局，实行工夫同下面一样快！
----总结：
----可见，所谓优化即where子句使用了索引，不成优化即产生了表扫描或分外开支。
----1.任何对列的操纵都将招致表扫描，它包含数据库函数、盘算表达式等等，查询时
要尽量将操纵移至等号右侧。
----2.in、or子句常会利用事情表，使索引生效；假如不发生大批反复值，能够思索把
子句拆开；拆开的子句中应当包括索引。
----3.要擅长利用存储历程，它使SQL变得加倍天真和高效。
----从以上这些例子能够看出，SQL优化的本色就是在了局准确的条件下，用优化器可
以辨认的语句，充份使用索引，削减表扫描的I/O次数，只管制止表搜刮的产生。实在S
QL的功能优化是一个庞大的历程，上述这些只是在使用条理的一种表现，深切研讨还会
触及数据库层的资本设置、收集层的流量把持和操纵体系层的整体计划。
1．公道利用索引
索引是数据库中主要的数据布局，它的基本目标就是为了进步查询效力。如今年夜多半的数据库产物都接纳IBM开始提出的ISAM索引布局。索引的利用要恰如其分，其利用准绳以下：
●在常常举行毗连，可是没有指定为外键的列上创建索引，而不常常毗连的字段则由优化器主动天生索引。
●在频仍举行排序或分组（即举行groupby或orderby操纵）的列上创建索引。
●在前提表达式中常常用到的分歧值较多的列上创建检索，在分歧值少的列上不要创建索引。好比在雇员表的“性别”列上只要“男”与“女”两个分歧值，因而就无需要创建索引。假如创建索引不仅不会进步查询效力，反而会严峻下降更新速率。
●假如待排序的列有多个，能够在这些列上创建复合索引（compoundindex）。
●利用体系工具。如Informix数据库有一个tbcheck工具，能够在可疑的索引长进行反省。在一些数据库服务器上，索引大概生效大概由于频仍操纵而使得读取效力下降，假如一个利用索引的查询不明不白地慢上去，能够试着用tbcheck工具反省索引的完全性，需要时举行修复。别的，当数据库表更新大批数据后，删除偏重建索引能够进步查询速率。
2．制止或简化排序
应该简化或制止对年夜型表举行反复的排序。当可以使用索引主动以得当的序次发生输入时，优化器就制止了排序的步骤。以下是一些影响要素：
●索引中不包含一个或几个待排序的列；
●groupby或orderby子句中列的序次与索引的序次纷歧样；
●排序的列来自分歧的表。
为了不不用要的排序，就要准确地增建索引，公道地兼并数据库表（只管偶然大概影响表的标准化，但相对效力的进步是值得的）。假如排序不成制止，那末应该试图简化它，如减少排序的列的局限等。
3．打消对年夜型表行数据的按次存取
在嵌套查询中，对表的按次存取对查询效力大概发生致命的影响。好比接纳按次存取战略，一个嵌套3层的查询，假如每层都查询1000行，那末这个查询就要查询10亿行数据。制止这类情形的次要办法就是对毗连的列举行索引。比方，两个表：先生表（学号、姓名、岁数……）和选课表（学号、课程号、成就）。假如两个表要做毗连，就要在“学号”这个毗连字段上创建索引。
还可使用并集来制止按次存取。只管在一切的反省列上都有索引，但某些情势的where子句强制优化器利用按次存取。上面的查询将强制对orders表实行按次操纵：
SELECT＊FROMordersWHERE(customer_num=104ANDorder_num>1001)ORorder_num=1008
固然在customer_num和order_num上建有索引，可是在下面的语句中优化器仍是利用按次存取路径扫描全部表。由于这个语句要检索的是分别的行的汇合，以是应当改成以下语句：
SELECT＊FROMordersWHEREcustomer_num=104ANDorder_num>1001
UNION
SELECT＊FROMordersWHEREorder_num=1008
如许就可以使用索带路径处置查询。
4．制止相干子查询
一个列的标签同时在主查询和where子句中的查询中呈现，那末极可能当主查询中的列值改动以后，子查询必需从头查询一次。查询嵌套条理越多，效力越低，因而应该只管制止子查询。假如子查询不成制止，那末要在子查询中过滤失落尽量多的行。
5．制止坚苦的正轨表达式
MATCHES和LIKE关头字撑持通配符婚配，手艺上叫正轨表达式。但这类婚配出格泯灭工夫。比方：SELECT＊FROMcustomerWHEREzipcodeLIKE“98___”
即便在zipcode字段上创建了索引，在这类情形下也仍是接纳按次扫描的体例。假如把语句改成SELECT＊FROMcustomerWHEREzipcode>“98000”，在实行查询时就会使用索引来查询，明显会年夜年夜进步速率。
别的，还要制止非入手下手的子串。比方语句：SELECT＊FROMcustomerWHEREzipcode>“80”，在where子句中接纳了非入手下手子串，因此这个语句也不会利用索引。
6．利用一时表减速查询
把表的一个子集举行排序并创立一时表，偶然能减速查询。它有助于制止多重排序操纵，并且在其他方面还能简化优化器的事情。比方：
SELECTcust.name，rcvbles.balance，……othercolumns
FROMcust，rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ANDcust.postcode>“98000”
ORDERBYcust.name
假如这个查询要被实行屡次而不止一次，能够把一切未付款的客户找出来放在一个一时文件中，并按客户的名字举行排序：
SELECTcust.name，rcvbles.balance，……othercolumns
FROMcust，rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ORDERBYcust.name
INTOTEMPcust_with_balance
然后以上面的体例在一时表中查询：
SELECT＊FROMcust_with_balance
WHEREpostcode>“98000”
一时表中的行要比主表中的行少，并且物理按次就是所请求的按次，削减了磁盘I/O，以是查询事情量能够失掉年夜幅削减。
注重：一时表创立后不会反应主表的修正。在主表中数据频仍修正的情形下，注重不要丧失数据。
7．用排序来代替非按次存取
非按次磁盘存取是最慢的操纵，体现在磁盘存取臂的往返挪动。SQL语句埋没了这一情形，使得我们在写使用程序时很简单写出请求存取大批非按次页的查询。
有些时分，用数据库的排序才能来替换非按次的存取能改善查询。
3.优化tempdb功能
对tempdb数据库的物理地位和数据库选项设置的一样平常倡议包含：
使tempdb数据库得以按需主动扩大。这确保在实行完成前不停止查询，该查询所天生的存储在tempdb数据库内的两头了局集比预期年夜很多。
将tempdb数据库文件的初始巨细设置为公道的巨细，以免当必要更多空间时文件主动扩大。假如tempdb数据库扩大得过于频仍，功能会受不良影响。
将文件增加增量百分比设置为公道的巨细，以免tempdb数据库文件按太小的值增加。假如文件增加幅度与写进tempdb数据库的数据量比拟太小，则tempdb数据库大概必要一直扩大，因此将妨碍功能。
将tempdb数据库放在疾速I/O子体系上以确保好的功能。在多个磁盘上条带化tempdb数据库以取得更好的功能。将tempdb数据库放在除用户数据库所利用的磁盘以外的磁盘上。有关更多信息，请拜见扩大数据库。
4.优化服务器:
利用内存设置选项优化服务器功能
Microsoft 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！ 原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。 一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。） 相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐 对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。 SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）
页:
[1]