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

2022-07-23 10:15:28

限制查询的行和列来提高性能

这条规则非常简单，这里就不细说了。

使用搜索可参数化判断(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 之书写高效的查询,布布扣,bubuko.com