SQL语句优化.docx
《SQL语句优化.docx》由会员分享，可在线阅读，更多相关《SQL语句优化.docx（18页珍藏版）》请在冰豆网上搜索。
SQL语句优化
SQL语句优化基础
1
优化基本概念
SARG：
查询参数或扫描参数
用于限制搜索操作的一种规范，通常是指一个确定范围内的匹配或两个以上条件的连接。
一般形式：
列名操作符<常数或变量>
符合SARG：
Name=‘Zh’
Num>5000/2
Name=‘Wang'andAge>20
非SARG：
SUBSTRING（Name,1,2）=‘Zh’
Num*Price>5000
说明：
如果表达式不能满足SARG形式，那它就无法限制搜索的范围，使得查询引擎必须对每行数据进行扫描来判断它是否满足WHERE子句中的所有条件
WHERE中比较运算符的优先级
性能由高到低排序：
=
>,>=,<,<=
LIKE
<>或！
字段运算
说明：
尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致查询无法使用索引而进行全表扫描
IndexScan
IndexSeek
Year（dt）=2003andMonth（dt）=8
Dtbetween‘2003-08-01’and‘2003-08-31’
Datediff（d,dt,getdate（））=30
Dt>=convert（varchar（10）,dateadd（d,-30,getdate（））,120）
Left（number,4）=‘abcd’
Numberlike‘abcd%’
C1/2=100
C1=2*100
NOTc1>100
c1<=100
变量与表达式
DECLARE@dtdatetime
SELECT@dt=DATEADD（d,-10,GETDATE（））
SELECT id,name,mark
FROMtbl_Name
WHEREuptimeLIKE@dt
说明：
在查询中涉及到数据类型转换、计算表达式、字符串运算的条件,如有可能尽量设置到变量中，使用时统一引用变量
减少排序操作
以下操作使用到排序
ORDERBY
GROUPBY
DISTINCT
UNION
CREATEINDEX
注意：
尽可能地使排序列数最少；
尽量减少排序的行数
排序列尽可能用数字类型
条件逻辑转换（OR->AND）
SELECT*FROM[Orders]
WHEREorderDate>'1998-05-05'OR（orderId>11070ANDemployeeId=4）
--1.转换为AND
SELECT*FROM[Orders]
WHERE（orderDate>'1998-05-05'ORorderId>11070）AND
（orderDate>'1998-05-05'ORemployeeId=4）
--2.由于orderId>11070的结果集已包含orderDate>‘1998-05-05’,再次转换
SELECT*FROM[Orders]
WHEREorderId>11070AND
（orderDate>'1998-05-05'ORemployeeId=4）
说明:
从最初的OR子句转换为AND子句后，查询由ClusteredIndexScan变为ClusteredIndexSeek
计算列
selectyear（orderdate）,count（*）
fromSales.SalesOrderHeader
wheremonth（orderdate）=4andyear（orderdate）=2002
groupbyyear（orderdate）
--添加计算列并建立索引
altertableSales.SalesOrderHeaderaddorderyearasyear（orderdate）
altertableSales.SalesOrderHeaderaddordermonthasmonth（orderdate）
Createindexix_orderyearonSales.SalesOrderHeader（orderYear）
Createindexix_ordermonthonSales.SalesOrderHeader（ordermonth）
说明：
上述语句在建立计算列前，使用的是索引扫描，建立计算列后则使用的是索引查找，同时CPU占用,IO读取比例都大量下降
计算列不占用存储空间（设置为persisted则占用空间）
检索到计算列时，由SQLServer内部进行计算（不使用查询处理器），可缓解IO资源占用
计算列上可建立索引，并且索引使用效率较高
各序号使用的方法说明：
1.手动更新字段（提前新建两个字段，将运算后的内容更新到字段中）
2.查询中对字段做运算
3.使用计算列（不存储数据）
4.使用计算列（存储数据）
序号
CPU（ms）
Reads（次数）
Duration（ms）
1
22
822
1621
2
34
703
76
3
31
802
116
4
28
834
617
GROUPBY与DISTINCT
SELECTOrderID
FROM[OrderDetails]
WHEREUnitPrice>10
GROUPBYOrderID
SELECTDISTINCTOrderID
FROM[OrderDetails]
WHEREUnitPrice>10
说明：
如果字段列表中未使用到聚合函数，要去除结果集中的重复记录，使用DISTINCT的性能要好于GROUPBY
WHERE与HAVING
SELECTOrderID,COUNT（*）
FROM[OrderDetails]
WHEREOrderID>101
GROUPBYOrderID
SELECTOrderID,COUNT（*）
FROM[OrderDetails]
GROUPBYOrderID
HAVINGOrderID>101
说明：
WHERE是对SELECT后的结果集进行过滤;
HAVING则是对GROUPBY后的结果集进行过滤，所以应尽可能地多用WHERE
隐式数据类型转换
--表中字段实际类型为varchar（15）
declare@s1nvarchar（15）
set@s1='125295Vi53935'
selectCreditCardApprovalCode
fromSales.SalesOrderHeader
whereCreditCardApprovalCode=@s1
declare@svarchar（15）
set@s='125295Vi53935'
selectCreditCardApprovalCodefromSales.SalesOrderHeader
whereCreditCardApprovalCode=@s
说明：
方法1执行时要进行隐式转换（nvarchar优先级高于varchar），影响整体性能;
编写查询时注意参数与字段数据类型是否匹配
NOTEXISTS与NOTIN
SELECT*FROMProductsp
WHERENOTEXISTS
（SELECT*FROM[OrderDetails]o
WHEREo.ProductId=p.ProductId）
SELECT*FROMProducts
WHEREProductIdNOTIN（SELECTProductIdFROM
[OrderDetails]）
SELECTp.*FROMProductsp
LEFTJOIN[OrderDetails]oONo.ProductId=p.ProductId
WHEREo.ProductIdISNULL
说明：
在通常情况下,前两者的性能优于LEFTJOIN
NOTEXISTS与NOTIN等价（当子查询中包含NULL时，两者返回结果不同，NOTIN结果集为NULL,性能稍差于NOTEXISTS）;
SELECTc1,c2
FROMt1
WHEREc1=some_value
UNION--UNIONALL
SELECTc1,c2
FROMt1
WHEREc2=some_value
--与上述功能等价语句（性能优于UNION）
SELECTDISTINCTc1,c2
FROMt1
WHEREc1=some_valueORc2=some_value
说明：
union先将结果集进行合并，再执行distinct去除重复数据；
unionall只对两个结果集按顺序进行合并，不去除重复数据，性能优于union；
当表中有text等大对象数据类型时，不能执行union，而varchar（max）则无此限制
另外注意的是，使用union（all）时如果将些关键字误替换成其他字符时，查询仍会正常执行，但会返回两个或多个结果集
UNION与UNIONALL
INSERTINTO目的表SELECT*FROM源表
SELECT*INTO目的表FROM源表
--先要建立目的表，执行后源表数据移动到目的表
ALTERTABLE源表SWITCHTO目的表
说明：
性能由高到低：
ALTERTABLESWITCHàSELECTINTOàINSERTSELECT
当数据库恢复模型不是完全恢复时，SELECTINTO比先创建表再执行insertinto要快很多。
它可从源表中复制列名称，数据类型，是否为空及identity属性；但不能复制约束，索引，触发器
SELECTINTO也是个最小限度记录日志的操作，使用它会影响数据的恢复
由于SELECTINTO执行时对系统表加锁，所以应尽量避开繁忙时段对大表执行此操作，或者将SELECTINTO拆分成多个小批量的分支操作进行
ALTERTABLESWITCH操作中，源表和目标表都需要加锁。
对表进行的更改将记录于日志中，并且可以完整恢复，此操作速度相当快
导入数据
多重更新
方法1：
UPDATEProducts
SETUnitPrice=UnitPrice*1.6
WHEREUnitPrice>5
GO
USENorthwind
UPDATEProducts
SETUnitPrice=ROUND（UnitPrice,2）
WHEREUnitPrice>5
方法2:
UPDATEProducts
SETUnitPrice=ROUND（UnitPrice*1.6,2）
WHEREUnitPrice>5
说明：
方法2性能较好
对表进行多重更新时，使用一次UPDATE完成更新的性能要好于多次运行update操作,
判断记录是否存在
1.IF（SELECTCOUNT（*）FROMtable_nameWHEREcolumn_name='xxx'）>0
…………..
2.IFEXISTS（SELECT*FROMtable_nameWHEREcolumn_name='xxx'）
…………….
说明：
方法1判断符合指定条件的总数，效率较低；
方法2判断时找到符合条件的记录时立刻返回，效率相对较高
派生表与相关子查询
--通过原始表Join
SELECTDISTINCTPurchaseOrderNumber
FROMSales.SalesOrderHeaderh
INNERJOINsales.SalesOrderDetaild
ONh.SalesOrderId=d.SalesOrderId
WHEREd.CarrierTrackingNumber=N'E257-40A1-A3‘
--通过派生表Join
SELECTPurchaseOrderNumber
FROMSales.SalesOrderHeaderh
INNERJOIN（SELECTDISTINCTd.SalesOrderId
FROMSales.SalesOrderDetaild
WHEREd.CarrierTrackingNumber=N'E257-40A1-A3'
）d
ONh.SalesOrderId=d.SalesOrderId
--相关子查询
SELECTPurchaseOrderNumber
FROMSales.SalesOrderHeaderh
WHEREEXISTS
（SELECT*
FROMSales.SalesOrderDetaild
WHEREd.CarrierTrackingNumber=N'E257-40A1-A3'
ANDh.SalesOrderId=d.SalesOrderId
）
说明：
由于上述语句的where条件的选择性较高，派生表与相关子查询的性能略好于Join,但删除WHERE条件后，JOIN的性能会好于后两种
总体来说，Join方法在增加伸缩性的情况下提供了一致的速度性能
单元素查询
1.Top方法
SELECTTOP1*--transactionId
FROMproduction.transactionhistoryarchive
WHEREProductId=399
ANDtransactionDate='2001-11-1800:
00:
00.000'
ANDquantity>2
ORDERBYtransactionIdDESC
2.派生表（加粗的字体返回单值，与方法1的Top1transactionId做对比）
SELECT*
FROMproduction.transactionhistoryarchive
WHEREtransactionid=（
SELECTmax（transactionId）
FROMproduction.transactionhistoryarchive
WHEREProductId=399
ANDtransactiondate='2001-11-1800:
00:
00.000'
ANDquantity>2
）
说明：
如果返回单值，上述语句性能差异不大，但改为返回整行后，方法1的性能相对较好
Table变量和临时表
--事务中使用表变量与临时表
CREATEtable#T（svarchar（128））
DECLARE@Ttable（svarchar（128））
INSERTinto#Tselect‘101’
INSERTinto@Tselect‘101’
BEGINTRAN
UPDATE#TSETs=‘102’
UPDATE@TSETs=‘102’
ROLLBACK
SELECT*from#T--101
SELECT*from@T--102
--通过sp_executesql操作表变量
DECLARE@mnvarchar（max）
SET@m=N'DECLARE@tTABLE（IDint）;
INSERTINTO@tVALUES
（1）;
SELECT*FROM@t'
EXECsp_executesql@m
说明：
临时表能起到事务回滚的作用,但是表变量不行；
表变量在存储过程中或EXEC（string）语句中不可见,但可以通过sp_executesql来执行；
表变量不支持索引和统计数据，临时表则可以，
如果临时结果集仅仅需要往里面写数据，比如通过循环多次查找相关数据合并成一个临时结果集，那么就可以使用表变量（如果结果集需要排序，可在表变量中建立主键）；如果中间结果集较大并且数据很少修改，而是更多地充当一个临时的关联数据集去参加各种数据集的连接，则可以考虑使用临时表
表变量不支持并行执行计划，因此对于大型的临时结果集，则不适合选择表变量
表变量和临时表都消耗Tempdb中的存储空间，但是进行数据更新的时候，表变量不会写日志，而临时表则会写日志；
表变量不能被用于INSERT…EXEC语句中
当数据量较少时可以考虑使用表变量，当使用固定的大数据量数据时考虑使用临时表，在SQL2005中临时表的性能已明显优于SQL2000
动态SQL（参数化查询）
--EXEC语句
DECLARE@mvarchar（500）
DECLARE@sVARCHAR（20）
SELECT@s='string‘,
@m='SELECT*FROMtblNameWHEREemaillike''%'+@s+'%'''
EXEC（@m）
--sp_executesql语句
DECLARE@mnvarchar（500）
DECLARE@svarchar（20）
SELECT@s='string‘,
@m='SELECT*FROMtblNameWHEREemaillike''%''+@s+''%'''
EXECsp_executesql@m,N'@sVARCHAR（20）',@s
说明：
动态SQL语句的执行性能稍差于静态SQL（随着查询复杂程度及数据量的增大，二者间的性能差异会很小），但其可维护性较好；
Sp_executesql的性能稍强于exec,并且前者的查询缓存执行计划可重用性较高；
Sp_executesql结合参数化查询，可防止SQL注入式攻击
--如果传入参数@s=''';select*fromt2–'
--exec:
执行两条查询，容易形成恶意攻击
SELECT*FROMdbo.tblNameWHEREemaillike'%';select*fromt2--%‘
--sp_executesql:
按前后%之间传入的参数来查找匹配结果，返回为NULL
SELECT*FROMdbo.tblNameWHEREemaillike'%‘‘;select*fromt2--%‘
自定义函数（UDF）
表值函数
返回table数据类型，功能较强大，可以替代视图;
视图受限于单个SELECT语句，而用户定义函数可包含多条语句;
表值用户定义函数还可以替换返回单个结果集的存储过程;
用户定义函数返回的table可在FROM子句中被引用，
而存储过程返回的结果集不能被引用
CREATEFUNCTION[dbo].[fn_test01]（）
RETURNS@tTABLE（SalesOrderIDint,totalnumeric（10,4））
BEGIN
INSERTINTO@t
SELECTSalesOrderID,sum（LineTotal）ltfromSales.SalesOrderDetail
GROUPBYSalesOrderID
RETURN
End
内联函数
是返回table数据类型的用户定义函数的子集
可获得参数化视图的功能
CREATEFUNCTION[dbo].[fn_test02]（）
RETURNSTABLE
RETURN
SELECTSalesOrderID,sum（LineTotal）ltFROMSales.SalesOrderDetail
GROUPBYSalesOrderID
标量值函数
CREATEFUNCTION[dbo].[fn_test03]（@iint）
RETURNSNUMERIC（10,4）
BEGIN
DECLARE@tnumeric（10,4）
SELECT @t=sum（LineTotal）FROMSales.SalesOrderDetail
WHERE SalesOrderID=@i
RETURN @t
END
--表值函数（返回31465行,下同）
SELECTs.salesOrderid,f.Total
FROMSales.SalesOrderHeadersINNERJOINdbo.fn_test01（）f
ONs.salesOrderid=f.salesOrderid
--内联函数
SELECTs.salesOrderid,f.Total
FROMSales.SalesOrderHeadersINNERJOINdbo.fn_test02（）f
ONs.salesOrderid=f.salesOrderid
--标量值函数
SELECTsalesOrderid,dbo.fn_test03（salesOrderid）Toatl
FROMSales.SalesOrderHeader
说明：
上述语句性能由高到低：
内联函数->表值函数->标量值函数
返回较大结果集时，通常不推荐使用函数（特别注意SELECT选择列表中使用函数）；
在实现相同功能（返回大结果集）时，优先使用内联函数
WHERE中有条件限制返回较少的数据时，可优先考虑使用标量值函数
内联函数与索引视图
1.建立一个索引视图（使用dbccuseroptions，检查有关的7项SET设置是否正确）
CREATEVIEWvw_s1
WITHSCHEMABINDING
AS
SELECT字段列表
FROMdbo.tblName
CREATEUNIQUECLUSTEREDindexix_1ONvw_s1（字段名）
2.建立内联函数调用前面的索引视图
CREATEFUNCTIONfn_n1
（@IDint）
RETURNSTABLE
AS
RETURN（
SELECT*FROMvw_s1WHEREID=@ID
）
说明：
内联函数与索引视图结合使用，可在一定程度上提高查询性能
索引视图自身不能在其WHERE条件中使用参数来返回特定的结果集，但是可以将两者结合起来使用
视图对性能的影响
CREATEVIEWvw_test
AS
SELECTAccountNumber,count（*）AsOrderCnt
FROMSales.SalesOrderHeader
GROUPBYAccountNumber
--1.通过视图查询
SELECT*
FROMvw_test
WHEREAccountNumberLIKE'10-4020-000210'
--2.通过语句查询
SELECTAccountNu
升级会员 