SQL语句优化.docx
- 文档编号:10894204
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:18
- 大小:22.51KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 语句 优化