首页 > 数据库 >MSSQLSERVER执行计划详解

MSSQLSERVER执行计划详解

时间:2023-10-06 19:58:12浏览次数:39  
标签:MSSQLSERVER 查询 查找 索引 详解 执行 数据 聚集

转自:https://www.cnblogs.com/knowledgesea/p/5005163.html

1. 序言

本篇主要目的有二:

  1. 看懂t-sql的执行计划,明白执行计划中的一些常识。
  2. 能够分析执行计划,找到优化sql性能的思路或方案。

如果你对sql查询优化的理解或常识不是很深入,那么推荐几篇博文给你:SqlServer性能检测和优化工具使用详细sql语句的优化分析T-sql语句查询执行顺序

2. 执行计划简介

2.1 什么是执行计划?

提交的sql语句,经过"数据库查询优化器"分析, 生成多个数据库可以识别的高效执行查询方式。然后优化器会在众多执行计划中找出一个资源使用最少,而不是最快的执行方案,给你展示出来,可以是xml格式,文本格式,也可以是图形化的执行方案。

2.2 预估执行计划,实际执行计划

image

选择语句,点击上面其中一个执行计划,预估执行计划可以立即显示,而实际执行计划则需要执行sql语句后出现。预估执行计划不等于实际执行计划,但是绝大多数情况下实际的执行计划跟预估执行计划都是一致的。统计信息变更或者执行计划重编译等情况下,会造成不同。

SET STATISTICS PROFILESET STATISTICS XML 彼此互为对等物。 前者生成文本输出;后者生成 XML 输出。
在 SQL Server 的未来版本中,新的查询执行计划信息将只通过 SET STATISTICS XML 语句显示,而不通过 SET STATISTICS PROFILE 语句显示。
https://docs.microsoft.com/zh-cn/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-2017

2.3 为什么要读懂执行计划

首先执行计划让你知道你复杂的sql到底是怎么执行的,有没有按照你想的方案执行,有没有按照最高效的方式执行,使用啦众多索引的哪一个,怎么排序,怎么合并数据的,有没有造成不必要资源浪费等等。官方数据显示,执行t-sql存在问题,80%都可以在执行计划中找到答案。

2.4 针对图形化执行计划分析

执行计划,可以以文本,xml,图形化展示出来。本骗主要以图形化执行计划主导进行分析,然而执行计划中包含78个可用的操作符,本篇也只能对常用的进行分析,常用的几乎就包含你日常所有的了。Msdn上有图片介绍:https://msdn.microsoft.com/zh-cn/library/ms175913(v=sql.90).aspx

2.5 怎么看执行计划

图形化执行计划是 从上到下 从右到左 看的。

2.6 清除缓存的执行计划

dbcc freeprocache
dbcc flushprocindb(db_id)

3. 看懂图形化执行计划

3.1 连线

image

  1. 越粗表示扫描影响的行数愈多。
  2. Actual Number of Rows 扫描中实际影响的的行数。
  3. Estimated Number of Rows 预估扫描影响的行数。
  4. Estimated row size 操作符生成的行的估计大小(字节)。
  5. Estimated Data Size 预估影响的数据的大小。

3.2 Tooltips,当前步骤执行信息

image

Note:这个tips的信息告诉我们执行的对象是什么,采用的操作操作是什么,查找的数据是什么,使用的索引是什么,排序与否,预估cpu、I/O、影响行数,实际行数等信息。具体参数清单参见msdn:https://msdn.microsoft.com/zh-cn/library/ms178071(v=sql.90).aspx

3.3 Table Scan(表扫描)

image
当表中没有聚集索引,又没有合适索引的情况下,会出现这个操作。这个操作是很耗性能的,他的出现也意味着优化器要遍历整张表去查找你所需要的数据。

3.4 Clustered Index Scan(聚集索引扫描)、Index Scan(非聚集索引扫描)

image
这个图标两个操作都可以使用,一个聚集索引扫描,一个是非聚集索引扫描

聚集索引扫描: 聚集索引的数据体积实际是就是表本身,也就是说表有多少行多少列,聚集所有就有多少行多少列,那么聚集索引扫描就跟表扫描差不多,也要进行全表扫描,遍历所有表数据,查找出你想要的数据。
非聚集索引扫描: 非聚集索引的体积是根据你的索引创建情况而定的,可以只包含你要查询的列。那么进行非聚集索引扫描,便是你非聚集中包含的列的所有行进行遍历,查找出你想要的数据。

3.5 Key Lookup(键值查找)

image
首先需要说的是查找,查找与扫描在性能上完全不是一个级别的,扫描需要遍历整张表,而查找只需要通过键值直接提取数据,返回结果,性能要好。

当你查找的列没有完全被非聚集索引包含,就需要使用键值查找在聚集索引上查找非聚集索引不包含的列。

另:
在数据库查询执行计划中,"Key Lookup"(也称为 "Bookmark Lookup")是一种查询操作,它通常出现在执行计划中,用于检索没有包含在索引中的其他列的值。Key Lookup 操作发生在已经使用一个索引执行过滤操作后,需要获取原始表格中的其他列数据时。

Key Lookup 操作通常涉及以下步骤:

  1. 数据库引擎首先使用索引来过滤查询中的行,这可以极大地加速数据检索,因为索引通常更小且更快。
  2. 但索引通常只包含部分列的数据,而不是表中的所有列。如果查询需要获取未包含在索引中的其他列数据,数据库引擎将执行 Key Lookup 操作。
  3. 在 Key Lookup 操作中,引擎将使用索引中的键值(通常是主键或唯一索引值)来查找原始表中对应的行,以检索需要的其他列数据。
  4. 这个操作可能涉及访问磁盘上的数据页,以获取缺失的列数据。这通常比直接从索引中获取数据要昂贵,因为需要更多的 I/O 操作。

Key Lookup 操作可以降低查询性能,特别是当需要大量的 Key Lookup 操作时。为了优化查询性能,可以考虑以下几种方法:

  • 创建覆盖索引:一个覆盖索引包含了查询所需的所有列数据,从而避免了 Key Lookup 操作。
  • 使用包含列:在索引中使用 INCLUDE 子句,以包括查询所需的其他列数据,减少 Key Lookup 的需要。
  • 重新设计查询:考虑是否可以重写查询以减少 Key Lookup 操作的数量。
  • 考虑内存和磁盘性能:Key Lookup 涉及访问磁盘,因此磁盘性能和内存大小可能会影响性能。

总之,Key Lookup 是一种在查询执行计划中用于检索未包含在索引中的其他列数据的操作,它可能会对查询性能产生负面影响,因此需要进行优化。

3.6 RID Lookoup(RID查找)

image
跟键值查找类似,只不过RID查找,是需要查找的列没有完全被非聚集索引包含,而剩余的列所在的表又不存在聚集索引,不能键值查找,只能根据行表示Rid来查询数据。

3.7 Clustered Index Seek(聚集索引查找)、Index Seek(非聚集索引查找)

image
聚集索引查找非聚集索引查找都是使用该图标。
聚集索引查找: 聚集索引包含整个表的数据,也就是在聚集索引的数据上根据键值取数据。
非聚集索引查找: 非聚集索引包含创建索引时所包含列的数据,在这些非聚集索引的数据上根据键值取数据。

3.8 Hash Match

image
这个图标有两种地方用到,一种是表关联,一种是数据聚合运算时。
再分别说这两中运算的前面,我先说说Hashing(编码技术)Hash Table(数据结构)
Hashing: 在数据库中根据每一行的数据内容,转换成唯一符号格式,存放到临时哈希表中,当需要原始数据时,可以给还原回来。类似加密解密技术,但是他能更有效的支持数据查询。
Hash Table: 通过hashing处理,把数据以key/value的形式存储在表格中,在数据库中他被放在tempdb中。

接下来,来说说Hash Math表关联行数据聚合是怎么操作运算的。

表关联:
image
如上图,关联两个数据集时,Hash Match会把其中较小的数据集,通过Hashing运算放入HashTable中,然后一行一行的遍历较大的数据集与HashTable进行相应的匹配拉取数据。

数据聚合:当查询中需要进行Count/Sum/Avg/Max/Min时,数据可能会采用把数据先放在内存中的HashTable中然后进行运算。

3.9 Nested Loops

image
这个操作符号,把两个不同列的数据集汇总到一张表中。提示信息中的Output List中有两个数据集,下面的数据集(inner set)会一一扫描与上面的数据集(out set),知道扫描完为止,这个操作才算是完成。

3.10 Merge Join

image

这种关联算法是对两个已经排过序的集合进行合并。如果两个聚合是无序的则将先给集合排序再进行一一合并,由于是排过序的集合,左右两个集合自上而下合并效率是相当快的。

3.11 Sort(排序)

image
对数据集合进行排序,需要注意的是,有些数据集合在索引扫描后是自带排序的。

3.12 Filter(筛选)

image
根据出现在having之后的操作运算符,进行筛选

3.13 Computer Scalar

image

在需要查询的列中需要自定义列,比如count(*) as cnt,select name + '' + age 等会出现此符号。

3.14 Table Spool

"Table Spool" 是 SQL 查询执行计划中的一种操作符,它通常用于创建和维护一个临时表格(也称为 "spool"),以便在查询的后续步骤中使用。Table Spool 操作主要用于优化查询性能,特别是在某些情况下可以减少查询的复杂性或避免多次访问相同的数据。

Table Spool 操作通常出现在以下情况下:

  1. 排序操作: 当查询需要按照某列或多列排序结果时,Table Spool 可能会在排序之前创建一个临时表格,将数据存储在其中,以便在排序过程中使用。这可以避免对原始表格进行多次排序。
  2. 重复数据删除: 当查询需要从结果集中删除重复的行时,Table Spool 可以帮助创建一个包含唯一值的临时表格,以过滤重复数据。
  3. 延迟计算: 有时,Table Spool 可以用于延迟计算,只在需要时计算部分数据,而不是提前计算所有数据。

Table Spool 操作的使用是为了优化查询性能,因为它可以减少查询的复杂性,避免多次访问相同的数据,或者在需要时执行计算。然而,过多或不正确使用 Table Spool 可能会导致性能下降,因此需要谨慎使用并进行性能测试。在查询执行计划中,你可以查看 Table Spool 操作的详细信息,包括输入、输出行数和成本等,以帮助进行性能分析和调优。

4. 根据执行计划细节要做的优化操作

这里会有很多建议给出,我不一一举例了,给出几个示例,想做到优化行家,多的还需要大家去悟去理解。

  1. 如果select * 通常情况下聚集索引会比非聚集索引更优。
  2. 如果出现Nested Loops,需要查下是否需要聚集索引,非聚集索引是否可以包含所有需要的列。
  3. Hash Match连接操作更适合于需要做Hashing算法集合很小的连接。
  4. Merge Join时需要检查下原有的集合是否已经有排序,如果没有排序,使用索引能否解决。
  5. 出现表扫描,聚集索引扫描,非聚集索引扫描时,考虑语句是否可以加where限制,select * 是否可以去除不必要的列。
  6. 出现Rid查找时,是否可以加索引优化解决。
  7. 在计划中看到不是你想要的索引时,看能否在语句中强制使用你想用的索引解决问题,强制使用索引的办法 Select CluName1, CluName2 from Table with(index = IndexName)
  8. 看到不是你想要的连接算法时,尝试强制使用你想要的算法解决问题。强制使用连接算法的语句:select * from t1 left join t2 on t1.id = t2.id option(Hash/Loop/Merge Join)
  9. 看到不是你想要的聚合算法是,尝试强制使用你想要的聚合算法。强制使用聚合算法的语句示例:select age, count(age) as cnt from t1 group by age option(order/hash group)
  10. 看到不是你想要的解析执行顺序是,或这解析顺序耗时过大时,尝试强制使用你定的执行顺序。option(force order)
  11. 看到有多个线程来合并执行你的sql语句而影响到性能时,尝试强制是不并行操作。option(maxdop 1)
  12. 在存储过程中,由于参数不同导致执行计划不同,也影响啦性能时尝试指定参数来优化。option(optiomize for(@name='zlh'))
  13. 不操作多余的列,多余的行,不做务必要的聚合,排序。

标签:MSSQLSERVER,查询,查找,索引,详解,执行,数据,聚集
From: https://www.cnblogs.com/Ceri/p/17744898.html

相关文章

  • Java多线程详解
          线程对象是可以产生线程的对象。比如在Java平台中Thread对象,Runnable对象。线程,是指正在执行的一个指点令序列。在java平台上是指从一个线程对象的start()开始,运行run方法体中的那一段相对独立的过程。相比于多进程,多线程的优势有:   (1)进程之间不能共享数据,线......
  • 常见的C语言执行效率优化方法
    C语言之心效率优化写代码时考虑代码的执行效率是一个好习惯,嵌入式开发多年,让自己养成了这样的习惯。优化C语言代码的执行效率是一项重要的任务,可以通过多种方法和注意事项来实现。下面列出了一些常用的方法和注意事项,并结合具体实例进行详细讲解:选择合适的数据结构使用合适的数......
  • 三层架构详解
    一、什么是三层架构?   UI(表现层):主要是指与用户交互的界面。用于接收用户输入的数据和显示处理后用户需要的数据。   BLL:(业务逻辑层):UI层和DAL层之间的桥梁。实现业务逻辑。   DAL:(数据访问层):与数据库打交道。主要实现对数据的增、删、改、查。将存储在数......
  • ORACLE 存储过程详解
    一、定义所谓存储过程(StoredProcedure),就是一组用于完成特定数据库功能的SQL语句集,该SQL语句集经过编译后存储在数据库系统中。在使用时候,用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它,从而完成一个或一系列的数据库操作。二、存储过程创建......
  • SQL SERVER 存储过程执行日志记录方法(有案例)
    查询历史执行总体情况SELECTTOP100db_name(d.database_id)asDBName,s.nameas存储名称,s.type_descas存储类型,d.cached_timeasSP添加到缓存的时间,d.last_execution_timeas上次执行SP的时间,d.last_elapsed_timeas[上次执行SP所用的时间(μs)],......
  • mysql命令行执行sql文件
    方法一:mysql命令,直接在服务器上执行mysql–u用户名–p密码–D数据库<【sql脚本文件路径全名】例如:mysql-uroot-proot-Dtest</tmp/test.sql方法二:source命令,登录mysqlclient执行source【sql脚本文件路径全名】例如:source/tmp/test.sql......
  • Java基础知识29--主线程、子线程执行顺序
    1、主线程与子线程互不影响最常见的情况,主线程中开启了一个子线程,开启之后,主线程与子线程互不影响各自的生命周期,即主线程结束,子线程还可以继续执行;子线程结束,主线程也能继续执行。publicclassTestThread{publicstaticvoidmain(String[]args)throwsInterruptedExcepti......
  • PHP配置文件详解php.ini
    [PHP];PHP还是一个不断发展的工具,其功能还在不断地删减;而php.ini的设置更改可以反映出相当的变化,;在使用新的PHP版本前,研究一下php.ini会有好处的;;;;;;;;;;;;;;;;;;;;关于这个文件;;;;;;;;;;;;;;;;;;;;;这个文件控制了PHP许多方面的观点。为了让PHP读取这个文件,它必须被......
  • 【Citrix篇】2-Citrix ADC/Gateway远程代码执行XSS漏洞修复方案
    、一、前言    最近我们根据修复了CVE-2023-3519漏洞,仍有部分安全厂商扫描出XSS漏洞,我们从400获悉该XSS漏洞不存在风险的,但是可拒绝请求,拦截掉。【Citrix篇】1-CitrixADC/Gateway远程代码执行漏洞CVE-2023-3519和升级方法二、漏洞详情    我们根据构建XSS语句,发现Citrix......
  • linux 执行脚本报错 No such file or directory (转)
    windows下编辑的脚本,拿到linux上面执行windows下的换行符为\r\n ,叫做CRLF      linux下的换行符为\n,叫做LF需要进行转换 解决办法:用vim打开该sh文件,输入::setff 回车,显示fileformat=dos,重新设置下文件格式::setff=unix 保存退出 转自: https://www......