SQL Server 性能调优4 之书写高效的查询
来源:互联网 发布:mac os 刷新dns 编辑:程序博客网 时间:2024/06/14 13:54
限制查询的行和列来提高性能
这条规则非常简单,这里就不细说了。
使用搜索可参数化判断(sargable conditions)来提高性能
Sargable 由 Search ARGument Able 简写而来,字面意思是搜索可参数化?还是比较晦涩哎…
总之使用Sargable判断可以帮助查询优化器更有效地利用索引,并提高采用 index seek 的可能性,我们先把所有的操作符分一下组。
Sargable操作符 = > >= < <= BETWEEN LIKE (通配符必须出现在最后,比如‘R%’)非Sargable操作符 != !> !< <> NOT EXISTS IN NOT IN BETWEEN LIKE (通配符出现在前面,比如'%R') NOT LIKE 针对某列的函数
知道了这些你就可以在允许的场合下尽量使用Sargable操作(譬如用多个 OR 来替代 IN)
在判定中巧妙地利用计算操作来提高性能
WHERE 子句中针对列的计算操作会导致非Sargable,间接导致列上的索引无法被有效利用,所以应当尽量避免。不幸的是没有什么自动方法来帮助你找到这些地方,你必须手工检查查询脚本并利用性能工具来定位问题所在。
我们用 AdventureWorks2012 数据库中的两张表来做个例子:
[AdventureWorks2012].[HumanResources].[Employee][AdventureWorks2012].[HumanResources].[EmployeePayHistory]
我们第一个查询抽出 Employee 表的基本信息,并取得对应的 EmployeePayHistory 表中最新的数据,并且满足 HourlyRate*8<=152
SELECT E.LoginID ,E.JobTitle ,E.BirthDate ,E.MaritalStatus ,E.Gender ,E.HireDate ,EP.HourlyRate ,EP.RateChangeDate FROM [AdventureWorks2012].[HumanResources].[Employee] AS E JOIN ( Select Max(BusinessEntityID) AS BusinessEntityID ,Max(RateChangeDate) AS RateChangeDate ,Rate AS HourlyRate FROM [AdventureWorks2012].[HumanResources].[EmployeePayHistory] GROUP BY Rate ) as EP ON E.BusinessEntityID=EP.BusinessEntityID WHERE EP.HourlyRate*8<=152
上面的查询在 WHERE 中进行<=判断前对列进行了一次数字计算(*8),该列上的索引就无法起效了。
对 WHERE 子句稍作修改,就可避免这个问题:
.. .. WHERE EP.HourlyRate<=152/8
修改后在 <= 判断前没有对列进行操作,所以列上的索引会起效。看一下前后的性能指标:
前一个查询占用了53%的开销,后一个占用了47%,两者相差数量级虽然不是非常大,但是随着表数据的增加,差异将逐渐扩大。
不在判定中对列进行函数操作来提高性能
与前一段的原理一样,WHERE 子句中针对列的函数操作会导致“非Sargable”,导致性能下降。这里就不重复举例了。
利用定义参照的完整性来改善性能
定义参照的完整性 (Declarative Referential Integrity),简称 DRI,指利用主键和外键来保证数据库的完整性/一致性。
经常遇到这样的情况:开发者在主表中建立了主键,并用这个主键到子表中去取关联数据,但却没有在子表中建立外键。实际上建立外键不但能帮助你保证数据库的完整性/一致性,还能提高查询关联数据时的性能,我们用一个示例来验证这个说法:
1.我们新建两张表
IF OBJECT_ID('ProductDemo') IS NOT NULL DROP TABLE ProductDemo GO IF OBJECT_ID('ProductModelDemo') IS NOT NULL DROP TABLE ProductModelDemo GO select * into ProductModelDemo from Production.ProductModel select * into ProductDemo from Production.Product WHERE ProductModelID is not null GO
2.在子表 ProductDemo 上声明 ProductModelID 为非空字段,并建立为主键
ALTER TABLE ProductDemo ALTER COLUMN ProductModelID INT NOT NULL GO ALTER TABLE ProductDemo ADD CONSTRAINT [PK_ProductDemo_ProductID] PRIMARY KEY CLUSTERED ( [ProductID] ASC ) GO
3.在主表 ProductModelDemo: 上建立主键
ALTER TABLE ProductModelDemo ADD CONSTRAINT [PK_ProductModelDemo_ ProductModelID] PRIMARY KEY CLUSTERED ( ProductModelID ASC ) GO
4.执行测试查询
SELECT P.ProductID ,P.ProductModelID FROM ProductDemo AS P JOIN ProductModelDemo AS PM ON P.ProductModelID=PM.ProductModelID WHERE P.ProductID=680 GO
执行计划如下:
5.建立子父之间的外间关联
ALTER TABLE ProductDemo WITH CHECK ADD CONSTRAINT FK_ProductDemo_ProductModelDemo_ProductModelID FOREIGN KEY (ProductModelID) REFERENCES ProductModelDemo(ProductModelID) GO
再次执行第4步的查询,执行计划如下:
修改后的查询只需要在 ProductDemo 表上执行一次索引检索。
在外键字段上定义 NOT NULL,保证了子表中的数据必在父表中存在关联信息,优化器从而“信任”该子表(在不检索父表数据的情况下,不需要再去访问/检查父表)。
“信任”外键来获取性能改进
使用 sys.foreign_keys 系统视图能获取外键的信息,is_not_trusted 字段表示该外键是否被“信任”。
要建立被“信任”的外键可以在创建脚本中使用 WITH CHECK 参数,具体可参照前文的脚本。
备注
最后再添加几点:
尽可能多的指定查询筛选条件可以不用 ORDER BY 尽量不用GROUP BY 子句中的项目尽可能少,并尽可能取自同一个表GROUP BY 子句中尽量用数字类型的字段,避免用文本GROUP BY 和 ORDER BY 中的字段尽量取自同一个表
- SQL Server 性能调优4 之书写高效的查询
- SQL Server 性能调优4 之书写高效的查询
- Sql Server查询性能优化之走出索引的误区
- Sql Server查询性能优化之走出索引的误区
- Sql Server查询性能优化之走出索引的误区
- Sql Server查询性能优化之创建合理的索引
- 【SQL Server学习笔记】SQL Server查询性能调优、捕捉和评估查询性能
- SQL Server查询性能
- Sql Server 模糊查询的性能问题
- SQL Server提高查询性能的方法
- SQL server 查询性能的问题
- SQL Server查询性能问题调优案例
- SQL Server查询性能调优、捕捉和评估查询性能
- 多表多查询条件对SQL Server查询性能的优化
- SQL SERVER 2000 高效分页查询语句
- SQL Server,Oracle,MySQL高效分页查询
- 优化 SQL Server 查询性能
- 优化 SQL Server 查询性能
- SQL Server 性能调优3 之索引(Index)的维护
- 代码中使用styles里面定义的属性
- [境内法规]中国2008-2012年反洗钱战略—反洗钱工作部际联席会议20091230
- Spring+Quartz实现定时任务的配置方法
- 一步一个坑
- SQL Server 性能调优4 之书写高效的查询
- SVN 用户权限管理
- php数组操作相关函数reset()list()
- logger使用
- c++中如何只保留float型的小数点后两位?
- SAP请求传输事务代码
- ConfigReader(十九)—— ReadGuideObstructTaskConfig
- 谈谈用户体验中的表单设计-理论篇
- Linux下jdk安装及配置