仓酷云

标题: SQL优化技能详解 [打印本页]

作者: 分手快乐 时间: 2015-1-16 14:09
标题: SQL优化技能详解
但我们知道，若使用statement，并没有上述需要的数据。试想binlog中记录了一句updatetsetf1=3whereid=3。怎么恢复呢？SQL优化永无尽头，本章只是收搜了SQLSERVER优化的部分计划与通用做法，更多更深的了解，应当靠人人本人多在实践中实验，找出最切合本人数据库的优化计划。

1、使用程序中，包管在完成功效的基本上，只管削减对数据库的会见次数；经由过程搜刮参数，只管削减对表的会见行数,最小化了局集，从而加重收集包袱；可以分隔的操纵只管分隔处置，进步每次的呼应速率；在数据窗口利用SQL时，只管把利用的索引放在选择的首列；算法的布局只管复杂；在查询时，不要过量地利用通配符如SELECT*FROMT1语句，要用到几列就选择几列如：SELECTCOL1,COL2FROMT1；在大概的情形下只管限定只管了局集行数如：SELECTTOP300COL1,COL2,COL3FROMT1,由于某些情形下用户是不必要那末多的数据的。不要在使用中利用数据库游标，游标长短常有效的工具，但比利用惯例的、面向集的SQL语句必要更年夜的开支；依照特定按次提取数据的查找。

2、制止利用不兼容的数据范例。比方float和int、char和varchar、binary和varbinary是不兼容的。数据范例的不兼容大概使优化器没法实行一些原本能够举行的优化操纵。比方:SELECTnameFROMemployeeWHEREsalary>60000在这条语句中,如salary字段是money型的,则优化器很难对其举行优化,由于60000是个整型数。我们应该在编程时将整型转化成为货币型,而不要比及运转时转化。

3、只管制止在WHERE子句中对字段举行函数或表达式操纵，这将招致引擎保持利用索引而举行全表扫描。如：
SELECT*FROMT1WHEREF1/2=100
应改成:
SELECT*FROMT1WHEREF1=100*2

SELECT*FROMRECORDWHERESUBSTRING(CARD_NO,1,4)=5378
应改成:
SELECT*FROMRECORDWHERECARD_NOLIKE5378%

SELECTmember_number,first_name,last_nameFROMmembersWHEREDATEDIFF(yy,datofbirth,GETDATE())>21
应改成:
SELECTmember_number,first_name,last_nameFROMmembersWHEREdateofbirth<DATEADD(yy,-21,GETDATE())

即：任何对列的操纵都将招致表扫描，它包含数据库函数、盘算表达式等等，查询时要尽量将操纵移至等号右侧。

4、制止利用!=或、ISNULL或ISNOTNULL、IN，NOTIN等如许的操纵符,由于这会使体系没法利用索引,而只能间接搜刮表中的数据。比方:
SELECTidFROMemployeeWHEREid!=B%
优化器将没法经由过程索引来断定将要射中的行数,因而必要搜刮该表的一切行。

5、只管利用数字型字段，一部分隔发职员和数据库办理职员喜好把包括数值信息的字段计划为字符型，这会下降查询和毗连的功能，并会增添存储开支。这是由于引擎在处置查询和毗连回逐一对照字符串中每个字符，而关于数字型而言只必要对照一次就够了。

6、公道利用EXISTS,NOTEXISTS子句。以下所示：
1.SELECTSUM(T1.C1)FROMT1WHERE((SELECTCOUNT(*)FROMT2WHERET2.C2=T1.C2>0)
2.SELECTSUM(T1.C1)FROMT1WHEREEXISTS(SELECT*FROMT2WHERET2.C2=T1.C2)
二者发生不异的了局，可是后者的效力明显要高于前者。由于后者不会发生大批锁定的表扫描或是索引扫描。

假如你想校验内外是不是存在某条记录，不要用count(*)那样效力很低，并且华侈服务器资本。能够用EXISTS取代。如：
IF(SELECTCOUNT(*)FROMtable_nameWHEREcolumn_name=xxx)
能够写成：
IFEXISTS(SELECT*FROMtable_nameWHEREcolumn_name=xxx)

常常必要写一个T_SQL语句对照一个父了局集和子了局集，从而找到是不是存在在父了局会合有而在子了局会合没有的纪录，如：
1.SELECTa.hdr_keyFROMhdr_tbla----tbla暗示tbl用别号a取代
WHERENOTEXISTS(SELECT*FROMdtl_tblbWHEREa.hdr_key=b.hdr_key)

2.SELECTa.hdr_keyFROMhdr_tblaLEFTJOINdtl_tblbONa.hdr_key=b.hdr_keyWHEREb.hdr_keyISNULL

3.SELECThdr_keyFROMhdr_tblWHEREhdr_keyNOTIN(SELECThdr_keyFROMdtl_tbl)
三种写法都能够失掉一样准确的了局，可是效力顺次下降。

7、只管制止在索引过的字符数据中，利用非打头字母搜刮。这也使得引擎没法使用索引。
见以下例子：
SELECT*FROMT1WHERENAMELIKE%L%
SELECT*FROMT1WHERESUBSTING(NAME,2,1)=L
SELECT*FROMT1WHERENAMELIKEL%
即便NAME字段建有索引，前两个查询仍然没法使用索引完成加速操纵，引擎不能不对全表一切数据逐条操纵来完成义务。而第三个查询可以利用索引来加速操纵。

8、分使用毗连前提，在某种情形下，两个表之间大概不但一个的毗连前提，这时候在WHERE子句中将毗连前提完全的写上，有大概年夜年夜进步查询速率。
例：
SELECTSUM(A.AMOUNT)FROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NO
SELECTSUM(A.AMOUNT)FROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NOANDA.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句实行快很多。

9、打消对年夜型表行数据的按次存取
只管在一切的反省列上都有索引，但某些情势的WHERE子句强制优化器利用按次存取。如：
SELECT*FROMordersWHERE(customer_num=104ANDorder_num>1001)OR
order_num=1008
办理举措可使用并集来制止按次存取：
SELECT*FROMordersWHEREcustomer_num=104ANDorder_num>1001
UNION
SELECT*FROMordersWHEREorder_num=1008
如许就可以使用索带路径处置查询。

10、制止坚苦的正轨表达式
LIKE关头字撑持通配符婚配，手艺上叫正轨表达式。但这类婚配出格泯灭工夫。比方：SELECT*FROMcustomerWHEREzipcodeLIKE"98___"即便在zipcode字段上创建了索引，在这类情形下也仍是接纳按次扫描的体例。假如把语句改成SELECT*FROMcustomerWHEREzipcode>"98000"，在实行查询时就会使用索引来查询，明显会年夜年夜进步速率。
11、利用视图减速查询
把表的一个子集举行排序并创立视图，偶然能减速查询。它有助于制止多重排序操纵，并且在其他方面还能简化优化器的事情。比方：
SELECTcust.name，rcvbles.balance，……othercolumnsFROMcust，rcvbles
WHEREcust.customer_id=rcvlbes.customer_idANDrcvblls.balance>0ANDcust.postcode>98000
ORDERBYcust.name

假如这个查询要被实行屡次而不止一次，能够把一切未付款的客户找出来放在一个视图中，并按客户的名字举行排序：
CREATEVIEWDBO.V_CUST_RCVLBES
AS
SELECTcust.name，rcvbles.balance，……othercolumns
FROMcust，rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ORDERBYcust.name

然后以上面的体例在视图中查询：
SELECT*FROMV_CUST_RCVLBESWHEREpostcode>98000
视图中的行要比主表中的行少，并且物理按次就是所请求的按次，削减了磁盘I/O，以是查询事情量能够失掉年夜幅削减。

12、可以用BETWEEN的就不要用IN
SELECT*FROMT1WHEREIDIN(10,11,12,13,14)
改成：
SELECT*FROMT1WHEREIDBETWEEN10AND14
由于IN会使体系没法利用索引,而只能间接搜刮表中的数据。

13、DISTINCT的就不必GROUPBY
SELECTOrderIDFROMDetailsWHEREUnitPrice>10GROUPBYOrderID
可改成：
SELECTDISTINCTOrderIDFROMDetailsWHEREUnitPrice>10

14、部分使用索引
1.SELECTemployeeID,firstname,lastnameFROMnames
WHEREdept=prodorcity=Orlandoordivision=food

2.SELECTemployeeID,firstname,lastnameFROMnamesWHEREdept=prod
UNIONALL
SELECTemployeeID,firstname,lastnameFROMnamesWHEREcity=Orlando
UNIONALL
SELECTemployeeID,firstname,lastnameFROMnamesWHEREdivision=food
假如dept列建有索引则查询2能够部分使用索引，查询1则不克不及。

15、能用UNIONALL就不要用UNION
UNIONALL不实行SELECTDISTINCT函数，如许就会削减良多不用要的资本

16、不要写一些不做任何事的查询
如：SELECTCOL1FROMT1WHERE1=0
SELECTCOL1FROMT1WHERECOL1=1ANDCOL1=2
这类逝世码不会前往任何了局集，可是会损耗体系资本。

17、只管不要用SELECTINTO语句。
SELECTINTO语句会招致表锁定，制止其他用户会见该表。

18、需要时强迫查询优化器利用某个索引
SELECT*FROMT1WHEREnextprocess=1ANDprocessidIN(8,32,45)
改成：
SELECT*FROMT1(INDEX=IX_ProcessID)WHEREnextprocess=1ANDprocessidIN(8,32,45)
则查询优化器将会强行使用索引IX_ProcessID实行查询。

19、固然UPDATE、DELETE语句的写法基础流动，可是仍是对UPDATE语句给点倡议：
a)只管不要修正主键字段。
b)当修正VARCHAR型字段时，只管利用不异长度内容的值取代。
c)只管最小化关于含有UPDATE触发器的表的UPDATE操纵。
d)制止UPDATE将要复制到其他数据库的列。
e)制止UPDATE建有良多索引的列。
f)制止UPDATE在WHERE子句前提中的列。

下面我们提到的是一些基础的进步查询速率的注重事项,可是在更多的情形下,常常必要重复实验对照分歧的语句以失掉最好计划。最好的办法固然是测试，看完成不异功效的SQL语句哪一个实行工夫起码，可是数据库中假如数据量很少，是对照不出来的，这时候能够用检察实行企图，即：把完成不异功效的多条SQL语句考到查询剖析器，按CTRL+L看查所使用的索引，表扫描次数（这两个对功能影响最年夜），整体上看询本钱百分比便可。

实行企图操纵，请检察本站以下文章：
使用showplan_all检察sql具体实行企图
SQLServer功能调优之实行企图深度分析

复杂的存储历程能够用导游主动天生：在企业办理器工具栏点击运转导游图标，点击"数据库"、"创立存储历程导游"。庞大存储历程的调试：在查询剖析器右边的工具扫瞄器（没有？按F8）选择要调试的存储历程,点右键，点调试，输出参数实行，呈现一个浮开工具条，下面有单步实行，断点设置等。提供TCP/IP、ODBC和JDBC等多种数据库连接路径。

作者: 小女巫 时间: 2015-1-18 12:11
发几份SQL课件，以飨阅者

作者: 小妖女 时间: 2015-1-26 16:06
需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

作者: 再现理想 时间: 2015-2-4 20:40
至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。

作者: 兰色精灵 时间: 2015-2-10 09:20
所以你总能得到相应的升级版本，来满足你的需求。

作者: 爱飞 时间: 2015-3-1 09:21
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

作者: 再见西城 时间: 2015-3-10 16:14
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

作者: 柔情似水 时间: 2015-3-24 05:05
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

欢迎光临仓酷云 (http://ckuyun.com/)