09-30 08:12 阅读 81

SQL基础-执行计划-源动力

什么是执行计划

执行计划:是查询优化器分析语句后，生成的一种确定性访问所需数据的最高效方式。

执行计划的输入：

查询语句文本
相关表, 约束, 索引定义
数据库的统计信息
提示
数据库的优化器参数设置

执行计划定义了：

访问源表的顺序
数据库服务器一般可以按许多不同的序列访问基表以生成结果集。
用于从每个表提取数据的方法
访问每个表中的数据一般也有不同的方法。如果只需要有特定键值的几行，数据库服务器可以使用索引。如果需要表中的所有行，数据库服务器则可以忽略索引并执行表扫描。如果需要表中的所有行，而有一个索引的键列在 ORDER BY中，则执行索引扫描而非表扫描可能会省去对结果集的单独排序。如果表很小，则对该表的几乎所有访问来说，表扫描可能都是最有效的方法。
用于计算的方法，以及如何对每个表中的数据进行筛选、聚合和排序的方法
从表访问数据时，可以使用不同的方法对数据进行计算，例如，计算标量值，以及对查询文本中定义的数据进行聚合和排序（例如，使用 GROUP BY 或 ORDER BY 子句时），以及如何筛选数据（例如在使用 WHERE 或 HAVING 子句时）。

执行计划生成

从潜在的多个可能的计划中选择一个执行计划的过程称为“优化”。

SQL Server 查询优化器是基于成本的优化器。就所使用的计算资源量而言，每个可能的执行计划都具有相关成本。查询优化器必须分析可能的计划并选择一个预计成本最低的计划。

查询优化器不会分析所有的可能组合，而是使用复杂的算法查找一个执行计划：其成本合理地接近最低可能成本。

SQL Server 查询优化器不只选择资源成本最低的执行计划，还选择能将结果最快地返回给用户且资源成本合理的计划。

SQL Server 查询优化器在估计用于从表或索引中提取信息的不同方法所需的资源成本时，依赖于分发内容统计信息。如果索引统计信息不是当前的，则查询优化器可能无法对表的当前状态做出最佳选择。

查看执行计划

估计的执行计划

不实际运行SQL语句，不包含任何运行时信息

image.png

实际执行计划

实际运行SQL语句，包含处理的行数、经过的时间、CPU、内存和 I/O 使用情况等

image.png

查看执行计划

显示分析、编译和执行各语句所需的毫秒数
SET STATISTICS TIME ON

当 SET STATISTICS TIME 为 ON 时，会显示语句的时间统计信息。为 OFF 时，不显示时间统计信息。
SET STATISTICS TIME 的设置是在执行或运行时设置，而不是在分析时设置。

若要使用 SET STATISTICS TIME，用户必须具有执行 Transact-SQL 语句的相应权限。但不需要 SHOWPLAN 权限。

image.png

显示语句生成的磁盘活动量的信息
SET STATISTICS IO ON

image.png

扫描计数：索引或表扫描次数

逻辑读取：数据缓存中读取的页数

物理读取：从磁盘中读取的页数

预读：为进行查询而放入缓存的页数

lob逻辑读取：从数据缓存读取的页数。包括 text、ntext、image、varchar(max)、 nvarchar(max)、varbinary(max) 或列存储索引页

lob物理读取：从磁盘读取的页数。包括 text、ntext、image、varchar(max)、 nvarchar(max)、varbinary(max) 或列存储索引页

lob预读：为进行查询而放入缓存的页数。包括 text、ntext、image、varchar(max)、 nvarchar(max)、varbinary(max) 或列存储索引页

image.png

按照从右到左、从上到下的方式阅读执行计划。
箭头宽度与箭头数据量成正比。
最左侧的运算符中可以查看该执行计划的缓存大小。
表运算符中估计行数与实际行数是否一致（统计信息是否过期）

image.png

执行 Transact-SQL 语句的适当权限。
对包含 Transact-SQL 语句所引用对象的所有数据库的 SHOWPLAN 权限。

运算符解释：
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms191158(v=sql.105)

看懂执行计划

image.png

嵌套循环联接（Nested Loops Join）

嵌套循环联接使用一个联接输入作为外部输入表，另一个作为内部输入表。外部输入表是执行计划中上面的输入，而内部输入表是下面的输入表。外部循环逐行消费外部输入表。内部循环为每个外部行执行一次，搜索内部输入表的匹配行。

image.png

哈希联接（Hash Join）

哈希联接运算符通过计算其生成输入中每行的哈希值生成哈希表。对于联接，使用第一个（顶端）输入生成哈希表，使用第二个（底端）输入探测哈希表。按联接类型规定的模式输出匹配项（或不匹配项）。

image.png

合并联接（Merge Join）

合并联接要求两个联接输入在合并列上排序，这将在联接条件中定义。如果两个联接上有索引，那么联接输入由该索引排序。因为每个联接输入都被排序，合并排序从每个输入得到一行比较是否相等，如果它们相等，匹配的行被生成。这个过程重复直到所有行都被处理。

image.png

分析执行计划及优化

隐式转换

SELECT TOP 10000 u.UserCode,u.UserName,o.HostIP,o.FunctionName
FROM myUser u 
INNER JOIN myOperLog o ON o.BUGUID = u.BUGUID
WHERE o.HostIP='192.168.0.34'
AND u.UserName='售楼部'

image.png

书签查找（键查找）

image.png

临界点是查询计划“提示”从寻找不覆盖的非聚集索引到扫描聚集索引或堆的阈值。
基本公式并不是硬性的规则，因为还有其他各种影响因素，它是这样的：

当估计行数超过表中页数的33%时，通常会出现聚集索引(或表)扫描。
当估计行数低于表中页的25%时，通常会出现非群集查找加键查找。
在25%到33%之间，它可以走任何一条路。

image.png

参数嗅探

image.png

重编译

根据数据库新状态的不同，数据库中的某些更改可能导致执行计划效率降低或无效。
SQL Server 将检测到使执行计划无效的更改，并将计划标记为无效。
此后，必须为执行查询的下一个连接重新编译新的计划。
导致计划无效的情况包括：

对查询所引用的表或视图进行更改（ALTER TABLE 和 ALTER VIEW）。
对单个过程进行更改，这将从缓存中删除该过程的所有计划 (ALTER PROCEDURE)。
对执行计划所使用的任何索引进行更改。
对执行计划所使用的统计信息进行更新，这些更新可能是从语句（如 UPDATE STATISTICS）显式生成，也可能是自动生成的。
删除执行计划所使用的索引。
显式调用 sp_recompile。
对键进行大量更改（这些更改是对查询所引用的表进行修改的其他用户执行 INSERT 或 DELETE 语句所产生的）。
对于带触发器的表，如果插入的或删除的表内的行数显著增长。
使用 WITH RECOMPILE 选项执行存储过程。

执行计划分析优化步骤

1、找到逻辑读取次数最多的表、执行计划中开销最大的查询

2、在图形执行计划中找到对应的表的运算符，不是聚集索引查找或索引查找的，如堆表、聚集索引扫描、键查找、一个表使用了2个索引关联取数等，结合表的数据量和现有索引情况尝试优化，如新建缺失索引（执行计划中建议的缺失索引不应照搬，应分析后再创建或修改现有索引，同时应删除缺失索引中的聚集键），修改索引为包含索引等（表数据量小的情况下，索引扫描可能更优于索引查找）

3、索引无法提高性能的情况下，检查估计行数与实际行数的差别，更新统计信息。还应分析执行时间长的SQL语句，修改SQL语句的写法，将SELECT子句中的子查询、自定义标量函数（串行）、APPLY运算符等改写为JOIN联接，尽量减少中间结果的数据量，如使用CTE、临时表等，尽量少用表变量，表变量不创建统计信息，会导致执行计划不准确

4、重复步骤1-3，直至图形执行计划中每个表对应的运算符基本为聚集索引查找或索引查找

作者:苦难_69e0

原文链接:https://www.jianshu.com/p/172a345fee95