SQL Server 性能调优4 之书写高效的查询

3121321321

限制查询的行和列来提高性能这条规则非常简单，这里就不细说了。
使用搜索可参数化判断(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 中的字段尽量取自同一个表