SQL语句执行效率3799.docx

《SQL语句执行效率3799.docx》由会员分享，可在线阅读，更多相关《SQL语句执行效率3799.docx（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL语句执行效率SQL语句中，IN、EXISTS、NOT IN、NOT EXISTS的效率较低，尤其是后两种语句，当数据量较大时，更常给人一种死机般的感觉。本文提供一种使用连接的方法代替以上的四种语句，可大副提高SQL语句的运行效率。以NOT IN为例，当数据量达到一万时，效率可提高20倍，数据量越大，效率提高的幅度也就越大。1)SELLECTT语句中中的中的的效率提提高方法法 SQL语句句如下：CREATTE TTABLLE TTAB11(COL1 VARRCHAAR(220) NOTT NUULL,COL2 INTTEGEER，PRIMAARY KEYY(COOL1);CREATTE T

2、TABLLE TTAB22(COL1 VARRCHAAR(220) NOTT NUULL,PRIMAARY KEYY(COOL1);CREATTE TTABLLE TTAB33(COL1 VARRCHAAR(220) NOTT NUULL,PRIMAARY KEYY(COOL1);下面，我们们将举22个例子子来具体体说明使使用连接接替换IIN、NNOT IN、EEXISSTS、NNOT EXIISTSS的方法法。读取表11中第22列（CCOL22）数据据的总和和，且其其第1列列数据存存在于表表2的第第1列中中。1. 使用用IN的的SQLL语句：SELECCT SSUM(COLL2) FROOM

3、 TTAB11 WHHEREE COOL1 IN(SELLECTT COOL1 FROOM TTAB22)2. 使用用EXIISTSS的SQQL语句句：SELECCT SSUM(COLL2) FROOM TTAB11 WHHEREE EXXISTTS(SSELEECT * FFROMM TAAB2 WHEERE TABB1.CCOL11=TAAB2.COLL1)3. 使用用连接的的SQLL语句：SELECCT SSUM(A.CCOL22) FFROMM TAAB1 A,TTAB22 B WHEREE A.COLL1=BB.COOL1读取表11中第22列（CCOL22）数据据的总和和，且其其第1

4、列列数据不不存在于于表2的的第1列列中。1. 使用用NOTT INN的SQQL语句句：SELECCT SSUM(COLL2) FROOM TTAB11 WHHEREE COOL1 NOTT INN(SEELECCT CCOL11 FRROM TABB2)2. 使用用NOTT EXXISTTS的SSQL语语句：SELECCT SSUM(COLL2) FROOM TTAB11 WHHEREE NOOT EEXISSTS(SELLECTT * FROOM TTAB22 WHHEREE TAB1.COLL1=TTAB22.COOL1)3. 使用用外连接接的SQQL语句句：SELECCT SSUM(A.

5、CCOL22) FFROMM TAAB1 A,TTAB22 B WHEERE A.CCOL11=B.COLL1(+) AAND B.CCOL11 ISS NUULL2) DEELETTE语句句中的效效率提高高方法 SQL语句句如下： CREATTE TTABLLE TTA(CA INNT)CREATTE TTABLLE TTB(CA INNT)CREATTE TTABLLE TTC(CA INNT)1 用IIN的SSQL语语句：DELETTE FFROMM TAA WHHEREE TAA.CAA INN (SSELEECT CA FROOM TTB)2 用EEXISSTS的的SQLL语句：DE

6、LETTE FFROMM TAA WHHEREE EXXISTTS (SELLECTT * FROOM TTB WWHERRE TTB.CCA=TTA.CCA)3 使用用连接的的SQLL语句：DELETTE TTA FFROMM TAA,TBB WHHEREE TAA.CAA=TBB.CAA删除表AA中表AA存在但但表B中中不存在在的数据据1. 使用用IN的的SQLL语句：DELETTE FFROMM TAA WHHEREE TAA.CAA NOOT IIN (SELLECTT CAA FRROM TB)2. 使用用EXIISTSS的SQQL语句句：DELETTE FFROMM TAA WHH

7、EREE NOOT EEXISSTS (SEELECCT CCA FFROMM TBB WHHEREE TBB.CAA=TAA.CAA)3. 使用用连接的的SQLL语句：DELETTE TTA FFROMM TAA LEEFT OUTTER JOIIN TTB OON TTA.CCA=TTB.CCA WWHERRE TTB.CCA IIS NNULLL 3) UPPDATTE语句句中的效效率提高高方法更新表A中中表A和和表B相相同的数数据1. 使用用IN的的SQLL语句：UPDATTE TTA SSET CA=CA+100000 WHEERE CA IN (SEELECCT CCA FFROM

8、M TBB)2. 使用用EXIISTSS的SQQL语句句：UPDATTE TTA SSET CA=CA+100000 WHEERE EXIISTSS (SSELEECT CA FROOM TTB WWHERRE TTB.CCA=TTA.CCA)3. 使用用连接的的SQLL语句：UPDATTE TTA SSET TA.CA=TA.CA+100000 FROOM TTA,TTB WWHERRE TTA.CCA=TTB.CCA更新表A中中表A存存在但表表B中不不存在的的数据1. 使用用IN的的SQLL语句：UPDATTE TTA SSET CA=CA+100000 WHEERE CA NOTT IN

9、N (SSELEECT CA FROOM TTB)2. 使用用EXIISTSS的SQQL语句句：UPDATTE TTA SSET CA=CA+100000 WHEERE NOTT EXXISTTS (SELLECTT CAA FRROM TB WHEERE TB.CA=TA.CA)3. 使用用连接的的SQLL语句：UPDATTE TTA SSET TA.CA=TA.CA+100000 FROOM TTA LLEFTT OUUTERR JOOIN TB ON TA.CA=TB.CA WHEERE TB.CA IS NULLL=方法一、尽尽量使用用复杂的的SQLL来代替替简单的的一堆 SQLL.

10、同样的事务务，一个个复杂的的SQLL完成的的效率高高于一堆堆简单SSQL完完成的效效率。有有多个查查询时，要要善于使使用JOOIN。 oRs=ooConnn.EExeccutee(SSELEECT * FFROMM Boookss) whilee noot ooRs.Eoff strSQQL = SSELEECT * FFROMM Auuthoors WHEERE AutthorrID=&ooRs(AuuthoorIDD) oRss2=ooConnn.EExeccutee(sttrSQQL) Respoonsee.wrritee oRRs(Tittle)&oRRs2(Naame)&qq uoo

11、t;oRs.MMoveeNexxt() wend 要比下面的的代码慢慢： strSQQL=SELLECTT Boookss.Tiitlee,Auuthoors.Namme FFROMM Boookss JOOIN Autthorrs OON AAuthhorss.AuuthoorIDD=Boookss.AuuthoorIDD oRs=ooConnn.EExeccutee(sttrSQQL) whilee noot ooRs.Eoff Respoonsee.wrritee oRRs(Tittle)&oRRs(Namme)&quu ott; oRs.MMoveeNexxt() wend 方法二、尽

12、尽量避免免使用可可更新 Reccorddsett oRs=ooConnn.EExeccutee(SSELEECT * FFROMM Auuthoors WHEERE AutthorrID=17,3,3)oRs(Namme)=DDarkkMannoRs.UUpdaate()要比下面的的代码慢慢：strSQQL = UUPDAATE Autthorrs SSET Namme=DarrkMaan WHEERE AutthorrID=17oConnn.Exxecuute strrSQLL方法三、更更新数据据库时，尽尽量采用用批处 理更新新将所有的SSQL组组成一个个大的批批处理SSQL，并并一次运运行

13、；这这比一个个一个地地更新数数据要有有效率得得多。这这样也更更加满足足你进行行事务处处理 的的需要：strSQQL=strSQQL=sstrSSQL&SEET XXACTT_ABBORTT ONNn;strSQQL=sstrSSQL&BEEGINN TRRANSSACTTIONNn;strSQQL=sstrSSQL&INNSERRT IINTOO Orrderrs(OOrdIID,CCusttID,OrddDatt) VVALUUES(99999,112344,GGETDDATEE()n;strSQQL=sstrSSQL&INNSERRT IINTOO OrrderrRowws(OOrdIID

14、,OOrdRRow,Iteem,QQty) VAALUEES(99999,011,G43385,5)n;strSQQL=sstrSSQL&INNSERRT IINTOO OrrderrRowws(OOrdIID,OOrdRRow,Iteem,QQty) VAALUEES(99999,022,G47726,1)n;strSQQL=sstrSSQL&COOMM999v TRAANSAACTIIONn;strSQQL=sstrSSQL&SEET XXACTT_ABBORTT OFFFnn;oConnn.Exxecuute(strrSQLL);其中，SEET XXACTT_ABBORTT OFFF 语

15、语句告诉诉SQLL Seerveer，如如果下面面的事务务处理过过程中，如如果遇到到错误，就就取消已已经完成成的事务务。方法四、数数据库索索引那些将在WWherre子句句中出现现的字段段，你应应该首先先考虑建建立索引引；那些些需要排排序的字字段，也也应该在在考虑之之列 。在MS AAcceess中中建立索索引的方方法：在在Acccesss里面选选择需要要索引的的表，点点击“设计”，然后后设置相相应字段段的索引引.在MS SSQL Serrverr中建立立索引的的方法：在SQQL SServver管管理器中中，选择择相应的的表，然然后“设计表表”，点击击右键，选选择“Prooperrtiees”

16、，选择择“inddexees/kkeyss”方法五、避避免使TTextt字段太太大 当字符串的的值大小小不固定定时，用用varrchaar比用用chaar的效效果要好好 些。我我曾经看看到一个个例子程程序，字字段被定定义为TTEXTT(2555)，但但是他的的取值经经常只有有20个个字符。这这个数据据表有550k个个记录，从从而使这这慢 =- 人们在在使用SSQL时时往往会会陷入一一个误区区，即太太关注于于所得的的结果是是否正确确，而忽忽略了不不同的实实现方法法之间可可能存在在的性能能差异，这这种性能能差异在在大型的的或是复复杂的数数据库环环境中（如如联机事事务处理理OLTTP或决决策支持持系

17、统DDSS）中中表现得得尤为明明显。笔笔者在工工作实践践中发现现，不良良的SQQL往往往来自于于不恰当当的索引引设计、不不充份的的连接条条件和不不可优化化的whheree子句。在在对它们们进行适适当的优优化后，其其运行速速度有了了明显地地提高！下面我我将从这这三个方方面分别别进行总总结： - 为了更更直观地地说明问问题，所所有实例例中的SSQL运运行时间间均经过过测试，不不超过秒的均均表示为为（1999912001 andd daate 2000 (255秒)selecct ddatee,suum(aamouunt) frrom reccordd grroupp byy daate(55秒)s

18、elecct ccounnt(*) ffromm reecorrd wwherre ddatee 1999909001 andd pllacee inn (BJ,SSH) (227秒)- 分析：-ddatee上有大大量的重重复值，在在非群集集索引下下，数据据在物理理上随机机存放在在数据页页上，在在范围查查找时，必必须执行行一次表表扫描才才能找到到这一范范围内的的全部行行。- 2.在在datte上的的一个群群集索引引selecct ccounnt(*) ffromm reecorrd wwherre ddatee 1999912001 andd daate 2000 （144秒）selecct

19、ddatee,suum(aamouunt) frrom reccordd grroupp byy daate（28秒）selecct ccounnt(*) ffromm reecorrd wwherre ddatee 1999909001 andd pllacee inn (BJ,SSH)（144秒）- 分析：- 在群集集索引下下，数据据在物理理上按顺顺序在数数据页上上，重复复值也排排列在一一起，因因而在范范围查找找时，可可以先找找到这个个范围的的起末点点，且只只在这个个范围内内扫描数数据页，避避免了大大范围扫扫描，提提高了查查询速度度。- 3.在在plaace，ddatee，ammounnt

20、上的的组合索索引selecct ccounnt(*) ffromm reecorrd wwherre ddatee 1999912001 andd daate 2000 （266秒）selecct ddatee,suum(aamouunt) frrom reccordd grroupp byy daate（27秒）selecct ccounnt(*) ffromm reecorrd wwherre ddatee 1999909001 andd pllacee inn (BJ, SSH)（1999912001 andd daate 2000(1999909001 andd pllacee inn

21、 (BJ,SSH)（,=, =）和和ordder by、grouup bby发生生的列，可可考虑建建立群集集索引；- .经常常同时存存取多列列，且每每列都含含有重复复值可考考虑建立立组合索索引；- .组合合索引要要尽量使使关键查查询形成成索引覆覆盖，其其前导列列一定是是使用最最频繁的的列。二、不充份份的连接接条件：- 例：表表carrd有778966行，在在carrd_nno上有有一个非非聚集索索引，表表acccounnt有11911122行行，在 acccounnt_nno上有有一个非非聚集索索引，试试看在不不同的表表连接条条件下，两两个SQQL的执执行情况况：selecct ssum(a.

22、aamouunt) frrom acccounnt aa,card b wwherre aa.caard_no = bb.caard_no（220秒）- 将SQQL改为为：selecct ssum(a.aamouunt) frrom acccounnt aa,card b wwherre aa.caard_no = bb.caard_no andd a.accouunt_no=b.aaccoountt_noo（ 1秒）- 分析：- 在第一一个连接接条件下下，最佳佳查询方方案是将将acccounnt作外外层表，ccardd作内层层表，利利用caard上上的索引引，其II/O次次数可由由以下公公式

23、估算算为：- 外层表表acccounnt上的的225541页页+（外外层表aaccoountt的19911222行*内层表表carrd上对对应外层层表第一一行所要要查找的的3页）=59559077次I/O - 在第二二个连接接条件下下，最佳佳查询方方案是将将carrd作外外层表，aaccoountt作内层层表，利利用acccouunt上上的索引引，其II/O次次数可由由以下公公式估算算为：- 外层表表carrd上的的19444页+（外层层表caard的的78996行*内层表表acccounnt上对对应外层层表每一一行所要要查找的的4页）= 3335288次I/O- 可见，只只有充份份的连接接条

24、件，真真正的最最佳方案案才会被被执行。- 总结：- 1.多多表操作作在被实实际执行行前，查查询优化化器会根根据连接接条件，列列出几组组可能的的连接方方案并从从中找出出系统开开销最小小的最佳佳方案。连连接条件件要充份份考虑带带有索引引的表、行行数多的的表；内内外表的的选择可可由公式式：外层层表中的的匹配行行数*内内层表中中每一次次查找的的次数确确定，乘乘积最小小为最佳佳方案。- 2.查查看执行行方案的的方法- 用用sett shhowpplannon，打打开shhowpplann选项，就就可以看看到连接接顺序、使使用何种种索引的的信息；想看更更详细的的信息，需需用saa角色执执行dbbcc(36

25、004,3310,3022)。三、不可优优化的wwherre子句句- 1.例例：下列列SQLL条件语语句中的的列都建建有恰当当的索引引，但执执行速度度却非常常慢：selecct * frrom reccordd whhereesubsttrinng(ccardd_noo,1,4)=53378(133秒)selecct * frrom reccordd whhereeamounnt/330 10000（111秒）selecct * frrom reccordd whhereeconveert(chaar(110),datte,1112)=11999912001（110秒）- 分析：- wheer

26、e子子句中对对列的任任何操作作结果都都是在SSQL运运行时逐逐列计算算得到的的，因此此它不得得不进行行表搜索索，而没没有使用用该列上上面的索索引；如如果这些些结果在在查询编编译时就就能得到到，那么么就可以以被SQQL优化化器优化化，使用用索引，避避免表搜搜索，因因此将SSQL重重写成下下面这样样：selecct * frrom reccordd whheree caard_no likke53788%（ 1秒秒）selecct * frrom reccordd whheree ammounnt 10000*330（ 1秒秒）selecct * frrom reccordd whheree da

27、ate= 119999/122/011（ 1秒秒）- 你会发发现SQQL明显显快起来来！- 2.例例：表sstufff有22000000行行，idd_noo上有非非群集索索引，请请看下面面这个SSQL：selecct ccounnt(*) ffromm sttufff whheree idd_noo inn(00,1)（23秒）- 分析：- wheere条条件中的的inn在逻逻辑上相相当于or，所以以语法分分析器会会将inn (0,1)转化化为idd_noo =0 or id_no=1来执行行。我们们期望它它会根据据每个oor子句句分别查查找，再再将结果果相加，这这样可以以利用iid_nno上

28、的的索引；但实际际上（根根据shhowpplann）,它它却采用用了OOR策略略，即即先取出出满足每每个orr子句的的行，存存入临时时数据库库的工作作表中，再再建立唯唯一索引引以去掉掉重复行行，最后后从这个个临时表表中计算算结果。因因此，实实际过程程没有利利用idd_noo上索引引，并且且完成时时间还要要受teempddb数据据库性能能的影响响。 - 实践证证明，表表的行数数越多，工工作表的的性能就就越差，当当stuuff有有62000000行时，执执行时间间竟达到到2200秒！还还不如将将or子子句分开开：selecct ccounnt(*) ffromm sttufff whheree i

29、dd_noo=00selecct ccounnt(*) ffromm sttufff whheree idd_noo=11- 得到两两个结果果，再作作一次加加法合算算。因为为每句都都使用了了索引，执执行时间间只有33秒，在在62000000行下，时时间也只只有4秒秒。或者者，用更更好的方方法，写写一个简简单的存存储过程程：creatte pprocc coountt_sttufff assdeclaare a inttdeclaare b inttdeclaare c inttdeclaare d chaar(110)beginnselecct a=ccounnt(*) ffromm sttu

30、fff whheree idd_noo=00selecct b=ccounnt(*) ffromm sttufff whheree idd_noo=11endselecct c=a+bselecct d=cconvvertt(chhar(10),cc)printt dd- 直接算算出结果果，执行行时间同同上面一一样快！- 总结：- 可见，所所谓优化化即whheree子句利利用了索索引，不不可优化化即发生生了表扫扫描或额额外开销销。- 1.任任何对列列的操作作都将导导致表扫扫描，它它包括数数据库函函数、计计算表达达式等等等，查询询时要尽尽可能将将操作移移至等号号右边。- 2.iin、oor子句句

31、常会使使用工作作表，使使索引失失效；如如果不产产生大量量重复值值，可以以考虑把把子句拆拆开；拆拆开的子子句中应应该包含含索引。- 3.要要善于使使用存储储过程，它它使SQQL变得得更加灵灵活和高高效。- 从以上上这些例例子可以以看出，SSQL优优化的实实质就是是在结果果正确的的前提下下，用优优化器可可以识别别的语句句，充份份利用索索引，减减少表扫扫描的II/O次次数，尽尽量避免免表搜索索的发生生。其实实SQLL的性能能优化是是一个复复杂的过过程，上上述这些些只是在在应用层层次的一一种体现现，深入入研究还还会涉及及数据库库层的资资源配置置、网络络层的流流量控制制以及操操作系统统层的总总体设计计。