高效率SQL的基础知识

高效率SQL的基础知识一个高效率的数据库系统是从两个方面来评价的：响应时间和吞吐量。在应用系统开发阶段，由于开发库上的数据比较少，在SQL语句的编写上感觉不出各种写法的性能差异，在将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就会成为最需要解决的主要问题之一。缩短系统的的响应时时间，增增加操作作的并发发度，可可以提高高系统的的吞吐量量。要缩缩短系统统的响应应时间，就就需要可可以高效效率执行行的SQQL语句句。高效效SQLL语句的的基本原原则，是是充分合合理的利利用索引引，避免免表扫描描。理解索引大多数情况况下，数数据库使使用索引引来检索索表，优优化器根根据用户户定义的的索引来来提高执执行性能能。但是是，如果果在SQQL语句句的whheree子句中中写的SSQL代代码不合合理，就就会造成成优化器器忽略索索引而采采用全表表扫描，而而这种SSQL语语句就是是所谓的的劣质SSQL语语句。在在编写SSQL语语句时需需要了解解优化器器根据何何种原则则来使用用索引，这这将有助助于写出出高性能能的SQQL语句句。ISNUULL与IISNNOTNULLL不要用nuull值值作索引引，任何何包含nnulll值的列列都将不不会被包包含在索索引中以以NULLL值做做条件时时，将无无法使用用包含NNULLL值的列列上的索索引。即即使索引引有多列列这样的的情况下下，只要要这些列列中有一一列含有有nulll，该该列就会会从索引引中排除除。也就就是说如如果某列列存在空空值，在在使用NNULLL值做条条件时，即使对对该列建建索引也也不会提提高性能能。列的联接本节应该阐阐述列被被包含到到表达式式中导致致不能使使用索引引。对于有联接接的列，即即使最后后的联接接值为一一个静态态值，优优化器是是不会使使用索引引的。例：假定有有一个职职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（BillCliton）的职工。下面是一个个采用联联接查询询的SQQL语句句，selecct**frromempployysswhereefiirstt_naame|||'''||llastt_naame='BBilllCllitoon';;改进方法：：selecct**frromempployyeewhereefiirstt_naame=‘‘Billl’anddlaast__namme==‘CClitton’’带通配符（%）的like语句selecct**frromempployyeewheerelasst_nnameeliike'%cclitton%%';由于通配符符（%）在在搜寻词词首出现现，所以以数据库库将不使使用laast__namme的索索引。在在很多情情况下可可能无法法避免这这种情况况，但是是一定要要心中有有数，通通配符如如此使用用会降低低查询速速度。当通配符出出现在字字符串其其他位置置时，优优化器就就能利用用索引。在在下面的的查询中中索引得得到了使使用：selecct**frromempployyeewheerelasst_nnameeliike'c%%';Orderrbyy语句ORDERRBYY语句决决定了数数据库如如何将返返回的查查询结果果排序。OOrdeerbby语句句对要排排序的列列没有什什么特别别的限制制，也可可以将函函数加入入列中（象象联接或或者附加加等）。任任何在OOrdeerbby语句句的非索索引项或或者有计计算表达达式都将将降低查查询速度度。需要仔细检检查orrderrbyy语句以以找出非非索引项项或者表表达式，它它们会降降低性能能。解决这个问问题的办办法就是是重写oordeerbby语句句以使用用索引，也也可以为为所使用用的列建建立另外外一个索索引，同同时应绝绝对避免免在orrderrbyy子句中中使用表表达式。NOTnot((staatuss=''VALLID'')和(sstattus<<>'VVALIID'))哪个效效率更高高？salarry<>>30000和和saalarry<330000orrsaalarry>330000和saalarry<＝＝29999oorssalaary>>=30001(假假设是整整数)哪哪一个效效率更高高？在wherre子句句使用一一些逻辑辑表达式式，如大大于、小小于、等等于以及及不等于于等等，也也可以使使用annd（与与）、oor（或或）以及及nott（非）。NNOT可可用来对对任何逻逻辑运算算符号取取反。例：...wwherrennot(sttatuus=='VAALIDD')要使用NOOT，则则应在取取反的短短语前面面加上括括号，并并在短语语前面加加上NOOT运算算符。NNOT运运算符包包含在另另外一个个逻辑运运算符中中，这就就是不等等于（<<>）运运算符。换换句话说说，即使使不在查查询whheree子句中中显式地地加入NNOT词词，NOOT仍在在运算符符中，见见下例：：...wwherresstattus<>''INVVALIID';;再例：selecct**frromempployyeewheeresaalarry<>>30000;解决方法，不不使用NNOT：：selecct**frromempployyeewheeresaalarry<330000orrsaalarry>330000;这两种查询询的结果果一样，但但是第二二种查询询方案会会比第一一种查询询方案更更快些。第第二种查查询会对对sallaryy列使用用索引，而而第一种种查询则则不会使使用索引引。IN和EXXISTTS将一列和一一系列值值相比较较，常用用的方法法是在wwherre子句句中使用用子查询询。在wherre子句句中可以以使用两两种格式式的子查查询。第一种格式式是使用用IN操操作符：：...wwherreccoluumnin((sellectt*froom....wheere....);第二种格式式是使用用EXIIST操操作符：：...wwherreeexissts(seelecct''X'froom....wwherre....));采用第二种种格式要要比第一一种格式式的效率率高。第二种格式式中，子子查询以以‘sellectt'XX'开始始。运用用EXIISTSS子句不不管子查查询从表表中抽取取什么数数据它只只查看wwherre子句句。这样样优化器器就不必必遍历整整个表而而仅根据据索引就就可完成成工作（这这里假定定在whheree语句中中使用的的列存在在索引）。使用EXIIST，数数据库系系统会首首先检查查主查询询，然后后运行子子查询直直到它找找到第一一个匹配配项。使用IN子子查询时时，首先先执行子子查询，并并将获得得的结果果列表存存放在在在一个加加了索引引的临时时表中。在在执行子子查询之之前，系系统先将将主查询询挂起，待待子查询询执行完完毕，存存放在临临时表中中以后再再执行主主查询。所以使用EEXISSTS通通常比使使用INN查询速速度快。应尽可能使使用NOOTEEXISSTS来来代替NNOTIN，尽尽管二者者都使用用了NOOT（不不能使用用索引而而降低速速度），但但NOTTEXXISTTS要比比NOTTINN查询效效率高。不充份的连连接条件件例：表caard有有78996行，在在carrd_nno上有有一个非非聚集索索引，表表acccounnt有11911122行行，在acccounnt_nno上有有一个非非聚集索索引，在在不同的的表连接接条件下下，两个个SQLL的执行行情况：：selecctssum((a.aamouunt))frromacccounntaa,caardbwhereea..carrd_nno==b..carrd_nno（220秒）将SQL改改为：selecctssum((a.aamouunt))frromacccounntaa,caardbwhereea..carrd_nno==b..carrd_nnoandaa.acccouunt__no==b.aaccoountt_noo（<1秒）在第一个连连接条件件下，最最佳查询询方案是是将acccouunt作作外层表表，caard作作内层表表，利用用carrd上的的索引，其其I/OO次数可可由以下下公式估估算为：：外层表acccouunt上上的2225411页+（外外层表aaccoountt的19911222行**内层表表carrd上对对应外层层表第一一行所要要查找的的3页）==59559077次I//O在第二个连连接条件件下，最最佳查询询方案是是将caard作作外层表表，acccouunt作作内层表表，利用用acccounnt上的的索引，其其I/OO次数可可由以下下公式估估算为：：外层表caard上上的19944页页+（外外层表ccardd的78896行行*内层层表acccouunt上上对应外外层表每每一行所所要查找找的4页页）=335528次次I/OO只有充份的的连接条条件，真真正的最最佳方案案才会被被执行。多表操作在在被实际际执行前前，查询询优化器器会根据据连接条条件，列列出几组组可能的的连接方方案并从从中找出出系统开开销最小小的最佳佳方案。连接条件要要充份考考虑带有有索引的的表、行行数多的的表；内外表的选选择可由由公式：：外层表表中的匹匹配行数数*内层层表中每每一次查查找的次次数确定定，乘积积最小为为最佳方方案。不可优化的的wheere子子句下列SQLL条件语语句中的的列都建建有恰当当的索引引，但执执行速度度却非常常慢selecct**frromreccorddwhhereesubsttrinng(ccardd_noo,1,,4)=='53378''(133秒)selecct**frromreccorddwhhereeamounnt/330<10000（111秒）selecct**frromreccorddwhhereeconveert((chaar(110),,datte,1112))='11999912001'（110秒）分析：wheree子句中中对列的的任何操操作结果果都是在在SQLL运行时时逐列计计算得到到的，因因此它不不得不进进行表搜搜索，而而没有使使用该列列上面的的索引；；如果这这些结果果在查询询编译时时就能得得到，那那么就可可以被SSQL优优化器优优化，使使用索引引，避免免表搜索索，因此此将SQQL重写写成下面面这样：：selecct**frromreccorddwhhereecaard__nolikke'53788%'（<<1秒秒）selecct**frromreccorddwhhereeammounnt<10000*330（<<1秒秒）selecct**frromreccorddwhhereedaate=='119999/122/011'（<1秒秒）表stufff有22000000行行，idd_noo上有非非群集索索引，如如下：selecctccounnt(**)ffrommsttufffwhhereeidd_nooinn('00',''1'))（23秒）分析：wheree条件中中的'iin'在在逻辑上上相当于于'orr'，所所以语法法分析器器会将iin(('0'','11')转转化为iid_nno=='0''orridd_noo='11'来执执行。我我们期望望它会根根据每个个or子子句分别别查找，再再将结果果相加，这这样可以以利用iid_nno上的的索引；；但实际际上（根根据shhowpplann）,它它却采用用了"OOR策略略"，即即先取出出满足每每个orr子句的的行，存存入临时时数据库库的工作作表中，再再建立唯唯一索引引以去掉掉重复行行，最后后从这个个临时表表中计算算结果。因因此，实实际过程程没有利利用idd_noo上索引引，并且且完成时时间还要要受teempddb数据据库性能能的影响响。表的行数越越多，工工作表的的性能就就越差，将将or子子句分开开：selecctccounnt(**)ffrommsttufffwhhereeidd_noo='00'selecctccounnt(**)ffrommsttufffwhhereeidd_noo='11'得到两个结结果，再再作一次次加法合合算。因因为每句句都使用用了索引引，执行行时间只只有3秒秒，在66200000行行下，时时间只有有4秒。更好的方法法，写一一个简单单的存储储过程：：creattepprocccoountt_sttufffassdeclaare@ainttdeclaare@binttdeclaare@cinttdeclaare@dchaar(110)beginnselecct@@a=ccounnt(**)ffrommsttufffwhhereeidd_noo='00'selecct@@b=ccounnt(**)ffrommsttufffwhhereeidd_noo='11'endselecct@@c=@@a+@@bselecct@@d=cconvvertt(chhar((10)),@cc)printt@dd直接算出结结果，执执行时间间同上面面一样快快。小结所谓优化即即wheere子子句利用用了索引引，不可可优化即即发生了了表扫描描或额外外开销。任何对列的的操作都都将导致致表扫描描，它包包括数据据库函数数、计算算表达式式等等，查查询时要要尽可能能将操作作移至等等号右边边。左右右不重要要，重要要的是表表达式中中是否包包含列。in、orr子句常常会使用用工作表表，使索索引失效效；如果果不产生生大量重重复值，可可以考虑虑把子句句拆开；；拆开的的子句中中应该包包含索引引。要善于使用用存储过过程，它它使SQQL变得得更加灵灵活和高高效。SQL优化化的实质质就是在在结果正正确的前前提下，用用优化器器可以识识别的语语句，充充份利用用索引，减减少表扫扫描的II/O次次数，尽尽量避免免表搜索索的发生生。不合理的索索引设计计例：表reecorrd有66200000行行，试看看在不同同的索引引下，下下面几个个SQQL的运运行情况况：在datee上建有有一非个个群集索索引selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999912001'andddaate<''1999912214''anddammounnt>>20000(25秒))selecctddatee,suum(aamouunt))frromreccorddgrrouppbyydaate(55秒))selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999909001'anddpllaceeinn(''BJ'','SSH'))(27秒))分析：date上上有大量量的重复复值，在在非群集集索引下下，数据据在物理理上随机机存放在在数据页页上，在在范围查查找时，必必须执行行一次表表扫描才才能找到到这一范范围内的的全部行行。在datee上的一一个群集集索引selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999912001'andddaate<''1999912214''anndaamouunt>2000（144秒）selecctddatee,suum(aamouunt))frromreccorddgrrouppbyydaate（28秒）selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999909001'anddpllaceeinn(''BJ'','SSH'))（144秒）分析：在群集索引引下，数数据在物物理上按按顺序在在数据页页上，重重复值也也排列在在一起，因因而在范范围查找找时，可可以先找找到这个个范围的的起末点点，且只只在这个个范围内内扫描数数据页，避避免了大大范围扫扫描，提提高了查查询速度度。在placce，ddatee，ammounnt上的的组合索索引selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999912001'andddaate<''1999912214''anndaamouunt>2000（26秒）selecctddatee,suum(aamouunt))frromreccorddgrrouppbyydaate（27秒）selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999909001'anddpllaceeinn(''BJ,,'SSH'))（<1秒秒）分析：这是一个不不很合理理的组合合索引，因因为它的的前导列列是pllacee，第一一和第二二条SQQL没有有引用pplacce，因因此也没没有利用用