推广

首页 > 行业资讯 > 推广 > 正文内容

推广

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

iseeyu3年前 (2024-02-22)推广164

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，直至图形执行计划中每个表对应的运算符基本为聚集索引查找或索引查找