数据库优化器后门 Hints
共 4224字，需浏览 9分钟
·
2021-01-23 09:54
点击蓝色“有关SQL”关注我哟
加个“星标”，天天与10000人一起快乐成长
知识星球特供稿 https://articles.zsxq.com/id_ddwf21qgviw8.html
机器与人的较量，谁能找到更优化的执行计划？
有时候我们总是说优化器太笨，居然看不出来这段 Join 必须要走 Hash Join. 但往往未必人总是对的，因为优化器在执行的时候一定是考虑到了各种资源开销，牺牲性能保障系统稳定和可用，也是优化器的分内事。
但最终人还是有决策权，因为优化器也是人编程而造的。这个后门就是 Hints.
在使用 hints 之前，必须考虑到 hints 是最后一步杀手锏，可能你的查询性能问题，使用常规方法就可以解决。数据量是随时都在变化着的，整个系统的平衡靠 hints 这种短平快的打法，必定无法长久支撑下去。所以在使用 Hints 这类重量级优化手段之前，必须要考虑其他的优化方法是否已经无法满足你的要求了：
1）系统的瓶颈问题：是否查询慢的时候，系统正在经历着各类资源短缺？是否系统资源其实已经到了要更新换代的必要？
2）statistics, histogram 时效性：统计信息是否有正确的更新计划，一旦失效，即使用了 hints也不会有很好的效果。
3）索引：对缺失索引的查询，是否及时正确添加了索引，并且索引碎片等索引维护是否安排到位
4）SQL改写：是否考虑将复杂 SQL 改写成简单 SQL， 并引入临时表，做更细致的排查。或者换成临时表就能提升性能，那就干脆用临时表即可。
Hints 并不是银弹，不能解决一切性能问题。有些查询就是要消耗很多资源去取数，那么再怎么用 hints 也于事无补。hints 如果用在了查询中的部分操作上，要防止的是它带给其他操作的影响，牵一发而动全身，执行计划是个系统工程，部分拆解有可能使得整体提高性能，也有可能带来灾难。
Hints 的衍生用法是 plan guide. 当我们无法直接作用于查询本身（查询为第三方公司所写），那么使用 plan guide 来实现 hints 是唯一方法，简单易维护。
Hints 的种类
Hints 可以出现在一段查询的三个地方，所以有三种：
query 级别
Join 级别
表级别
Join 级别 hints :
Join 级的 hints 不仅影响的是 Join 的方法，比如 hash join, merge join, nested loops join, 还间接影响了 Join 的顺序。
比如 Hash Join, 有 Build Table, Probe Table 的区别。通常优化器会选择小数据量的表作为 Build Table。但硬指定 Hash Join, 可能整个查询 Join 的次序就变了，原本优化器自由选择的多表 Join 顺序，现在这些表的 Join 必须按顺序处理。
Join 的 hints 可以在 Join 关键字上实现，比如 INNER HASH JOIN, 也可以在查询最后，使用option(MERGE JOIN, HASH JOIN), 来实现，这一例中，就将 merge join, hash join 考虑在计划之内，而 loop join 就被排除了。
不用任何 joint hints 的正常查询：
SELECT TOP 10 FirstName,LastName
FROM Person.Contact C
INNER JOIN Sales.Individual I
ON C.ContactID = I.ContactID
使用了 Hash Join hint 之后的查询：
SELECT TOP 10 FirstName,LastName
FROM Person.Contact C
INNER HASH JOIN Sales.Individual I
ON C.ContactID = I.ContactID
Join hints 不仅仅影响了显式的 Join 类型，包括外键的检查也可以被影响。但就如之前所说，hints 不能产生无效的计划，且不能违背优化器的基本准则。
非相等的 hash join
SELECT TOP 10 FirstName,LastName
FROM Person.Contact C
INNER HASH JOIN Sales.Individual I
ON C.ContactID >= I.ContactID
消息 8622，级别 16，状态 1，第 7 行
由于此查询中定义了提示，查询处理器未能生成查询计划。请重新提交查询，并且不要在查询中指定任何提示，也不要使用 SET FORCEPLAN。
违背优化器基本准则
SELECT *
FROM Person.Address
WHERE City = 'JiangSu'
OPTION(Merge Join)
常用的 bookmark lookup, 只能使用 nested loops join, 所以 Merge join 不能改变优化器基本准则。
Query 级别 hints
查询级别的 hints 会影响该查询中所有适用 Hints 的地方，比如 Option（Hash Join), 会将查询中所有的 Join 都通过 Hash Join 来实现。因此在指定该类 Hints 的时候，要注意的地方就是它的全局性影响。
Aggregation 在查询级别的 hint ：
Option(Hash Group) 指定所有的聚合都按照 Hash Match 方法来实现。很显然，如果在聚集索引表，按照聚集字段做 Hash Group 显然是开销巨大的，而用 Stream Aggregate 则高效的多。
不加任何 hints 之前：
SELECT SalesOrderID, COUNT(*)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
加了 Hash Group hint 之后：
SELECT SalesOrderID, COUNT(*)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
OPTION(HASH GROUP)
开启 statistics time:
SET STATISTICS TIME ON
Stream Aggreation 用时：
SQL Server 执行时间:
CPU 时间 = 32 毫秒，占用时间 = 61 毫秒。
Hash Group Aggregation 用时：
SQL Server 执行时间:
CPU 时间 = 62 毫秒，占用时间 = 81 毫秒。
因此有序集合上使用 Stream Aggregation 要高效的多。
Plan Guide
当有第三方的应用，查询很慢，我们又不能改动他们的源代码时，该怎么办？我们仅仅需要对这段时间内的执行计划做特定修正，而未来不确定是否还要继续做这样的修正，所以不想在源代码中加入 hints 时，怎么办？针对某个特定用户，场景，某段查询就需要特别的执行计划，又不能影响其他人的使用，怎么办？Plan Guide 应运而生。
还需要考虑的一个问题，在独立的 hints 和 Plan Guide 同时使用时，以哪个为先？
1）增加一个 Plan Guide
CREATE PROCEDURE dbo.getContact
AS
BEGIN
SELECT TOP 10 FirstName,LastName
FROM Person.Contact C
INNER JOIN Sales.Individual I
ON C.ContactID = I.ContactID
END
GO
EXEC dbo.getContact
上面的例子是没有对存储过程做 plan guide, 因为表上都有 contactid 索引，所以走 nested loops join 很正确。
接下来，我们建立一个 plan guide, 指定这段查询走 hash join :
EXEC sp_create_plan_guide
@name = N'plan_guide_test'
, @stmt = N'SELECT TOP 10 FirstName,LastName
FROM Person.Contact C
INNER JOIN Sales.Individual I
ON C.ContactID = I.ContactID'
, @type = N'OBJECT'
, @module_or_batch = N'getContact'
, @params = NULL
, @hints = N'OPTION (HASH JOIN)';
当再次执行这段存储过程时，就得到了和显式指定 hints 一样的效果了：新建 Plan Guide 时，一定要清楚的概念是 plan guide 只能为 object, sql, template 而建。
object 是指数据库对象，包括存储过程，函数，视图等；sql 是指单独的 SQL 语句，必须一字不差的指定，哪怕一个空格都能造成 plan guide 的失效。
而指定的 hints 可以是详细的 Hints 命令，也可以是具体的 xml plan 文本。
查看一个 Plan Guide
SELECT *
FROM sys.plan_guides
更新（包括删除）一个 Plan Guide
sp_control_plan_guide
@operation=N'DROP'
,@name='plan_guide_test
再次执行
EXEC dbo.getContact
则执行计划又回到了原样。
往期精彩：