目录

一、数据查询部分

1、 看到执行计划有两种方式,对sql语句按Ctrl+L,或按Ctrl+M打开显示执行计划窗口每次执行sql都会显示出相应的执行计划

2、 执行计划的图表是从右向左看的

3、 SQL Server有几种方式查找数据记录

4、  实例

⑴没有主键的表查询[表扫描]

⑵有主键的表查询[聚集索引扫描]

⑶建立非聚集索引的表查询[索引扫描+书签查找]

⑷建立非聚集索引并把其他显示列加入索引中[索引查找]

⑸建立非聚集索引并把其他显示列加入索引中并把聚集索引列当作条件[聚集索引查找]

二、函数计算部分

1. 标量聚合

2. 散列聚合(哈希匹配)

3.  排序

4. 查看查询IO、时间、内存占用

三、连接查询部分

1. 嵌套循环

2. 合并连接

3. 哈希连接 

4. 多表并行


一、数据查询部分

1、 看到执行计划有两种方式,对sql语句按Ctrl+L,或按Ctrl+M打开显示执行计划窗口每次执行sql都会显示出相应的执行计划

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_执行计划

2、 执行计划的图表是从右向左看的

3、 SQL Server有几种方式查找数据记录

[Table Scan] 表扫描(最慢),对表记录逐行进行检查

[Clustered Index Scan] 聚集索引扫描(较慢),按聚集索引对记录逐行进行检查

[Index Scan] 索引扫描(普通),根据索引滤出部分数据在进行逐行检查

[Index Seek] 索引查找(较快),根据索引定位记录所在位置再取出记录

[Clustered Index Seek] 聚集索引查找(最快),直接根据聚集索引获取记录

(如果有些sql执行很慢可以看一下执行计划是否包含太多“扫描”操作,如果有可以考虑为这些字段建立索引,建立索引切记不要再经常有更新操作的字段上建立,每次更新都会导致重建索引的操作,也会影响性能,0或1这种标识状态的字段因为大部分数据都是一样的建立索引也没有什么作用)

(索引就两种,1是聚集索引,2个非聚集索引,聚集索引每张表只能有一个,非聚集索引每张表可以有多个,主键Id就是典型的聚集索引,聚集索引是顺序排列的类似于字典查找拼音a、b、c……和字典文字内容顺序是相同的,非聚集索引与内容是非顺序排列的,类似字典偏旁查找时,同一个偏旁‘马’的汉字可能一个在第10页一个在第100页)

4、  实例

⑴没有主键的表查询[表扫描]

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_microsoft_02

⑵有主键的表查询[聚集索引扫描]

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_执行计划_03

⑶建立非聚集索引的表查询[索引扫描+书签查找]

书签查找:通过非聚集索引找到所求的行,但这个索引并不包含显示的列,因此还要额外去基本表中找到这些列,所以要进行键查找,如果基本表在堆中则Key Lookup会变成RID查找,这两个查找统称为书签查找。

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_sqlserver_04

⑷建立非聚集索引并把其他显示列加入索引中[索引查找]

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_microsoft_05

⑸建立非聚集索引并把其他显示列加入索引中并把聚集索引列当作条件[聚集索引查找]

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_microsoft_06

二、函数计算部分

1. 标量聚合

--------------------标量聚合--------------------/*
标量聚合-主要在聚合函数操作中产生
计算标量:根据行中的现有值计算出一个新值
流聚合:在相应排序的流中,计算多组行的汇总值
所有的聚合函数都会有流聚合出现,但是其不会消耗IO,只消耗CPU
除MAX()和MIN()外其他聚合函数都会同时出现标量和聚合两个操作
当列列表只包含聚合函数时,则结果集只具有一个行给出的聚合值,
该值由与WHERE子句相匹配的源行计算得到。
*/

SELECT MAX(Age) FROM dbo.UserInfo
SELECT COUNT(*) FROM dbo.UserInfo

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_执行计划_07

/*
执行以下语句,你会发现对[Id]进行去重由于是主键不会有重复,所以直接
通过流聚合就可以计算出结果,而[Name]字段进行去重的时候会有一个Sort排序的操作,
排序是比较消耗资源的尤其在数据量较大的表中,所以我们可以针对这个进行一下优化
*/

SELECT COUNT(DISTINCT Id) FROM dbo.UserInfo
SELECT COUNT(DISTINCT Name) FROM dbo.UserInfo

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_执行计划_08

/*
为[Name]字段建立一个非聚集索引再执行一下,会发现出现两个流聚合却没有了排序,
这样就节省了排序的开销,标量聚合算法比较简单,适合非重复值的聚合操作,调优时
尽量避免排序的产生,将分组(GROUP BY)字段锁定在索引覆盖范围内
*/

DROP INDEX dbo.UserInfo.Index_UserInfo_Name
CREATE INDEX Index_UserInfo_Name ON dbo.UserInfo(Name)

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_microsoft_09

2. 散列聚合(哈希匹配)

--------------------散列聚合(哈希匹配)--------------------/*
散列聚合(哈希匹配)-为了解决流聚合的不足,应对大数据的操作而产生的
对于数据量比较大时,SQL Server选择的是哈希匹配。
在内存中建立好散列表后,会按照GROUP BY后面的值作为键,
然后依次处理集合中的每条数据,当键在散列表中不存在时,
向散列表添加条目,当键已经在散列表中存在时,按照规则
聚合函数计算散列表中的值
*/

SELECT Name,COUNT(*) FROM dbo.UserInfo
GROUP BY Name
SELECT [Type],COUNT(*) FROM dbo.UserInfo
GROUP BY [Type]

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_执行计划_10

3.  排序

--------------------排序--------------------/*
排序-资源消耗较高的操作
对于数据量比较小时,执行GROUP BY操作会使用SORT
注意:Sort操作是占用内存的操作,当内存不足时会占用Tempdb
(SQL Server总是会在Sort操作和散列匹配中选择成本最低的)
调优时为排序字段建立索引可以更好的提高查询效率,如果想
按照添加时间倒序,那么Order By Id(Identity)可以达到同样的
结果,而且效率还更高
*/

SELECT * FROM dbo.UserInfo
ORDER BY AddTime
SELECT * FROM dbo.UserInfo
ORDER BY Id

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_sqlserver_11

4. 查看查询IO、时间、内存占用

--------------------查看查询IO、时间、内存占用--------------------/*
 使用SET STATISTICS IO, TIME还有其他的监控元素可以查看到当前
 执行的SQL性能,有针对性的进行调优操作选中表按Alt+F1可以显示选中表的结构,包括列、主键、索引等
 执行SQL语句使用Ctrl+M可以显示当前SQL的执行计划,便于调试
 dbo是SQL Server的架构名,默认就是dbo,除非强制将函数等写在其他架构名
 下才无法调用
 */
SET STATISTICS IO, TIME ON
SELECT * FROM dbo.UserInfo
SET STATISTICS IO, TIME OFF

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_执行计划_12

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_数据库_13

三、连接查询部分

1. 嵌套循环

--------------------嵌套循环--------------------
 /*
 UserInfo表数据少、Coupon表数据多嵌套循环可以理解为就是两层For循环,外层For会循环其中的每一项,内层For进行匹配,相应的外层For对应外部输入表,执行计划的图示排在上面,内层For对应内部出入表,执行计划的图示排在下面,外部表每一行都要使用来匹配,而内部表却不一定每一行都在匹配中被使用,所以,1、外部表输入越小越好,也可以利用索引来减少输入行数2、内部表匹配则可以利用索引来减少匹配条件的范围,尽快获取匹配行3、多大多数情况下,查询优化器会自动更替结果集小的表为外部,大的为内部当两个Join的表外部输入结果集比较小,而内部输入所查找的表非常大时,查询优化器更倾向于选择循环嵌套方式。
 */
SELECT * FROM dbo.UserInfo AS u
INNER JOIN dbo.Coupon AS c
ON u.Id = c.UserId

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_sqlserver性能优化_14

2. 合并连接

--------------------合并连接--------------------
/*UserInfo表数据少、Coupon表数据多
不同于循环嵌套的是,合并连接是从每个表仅仅执行一次访问,对于两个输入列都有序的情况下,合并连接的效率更高,其中排序的的重要性毋庸置疑了,B树中的叶层就是按照一定的逻辑顺序维护的。也就是说,聚集索引和非聚集覆盖索引,都可以通过对叶层的有序扫描以较小的代价就可以获取有序的数据。在这种情况下,就算输入表的规模比较大,合并联接也相当给力。如果计划分析器确定连接的一侧记录集中的元素是唯一确定的,那么就会采用一对多的匹配方式(多指另一侧的元素会有重复),在这种情况下,合并排序效率应该是几种连接方式中最高的。但如果所需的数据列并不存在上述的条件的时候,对于较大的输入来说排序往往是一个开销非常大的操作(因为基于比较的排序最快也就是n log n的),因此优化器通常不会在这种情况下选用合并联接。但是对于较小的输入排序的消耗还是可以接受的。合并连接需要双方有序,并且要求join的条件为等号,如果输入数据的双方无序,则查询分析器不会选择合并连接,我们也可以通过索引提示强制使用合并连接,这就是SQL语句为什么要加OPTION(MERGE JOIN)的原因
*/

CREATE NONCLUSTERED INDEX Index_Coupon_UserId ON dbo.Coupon(UserId)
--DROP INDEX Index_Coupon_UserId ON dbo.CouponSELECT * FROM dbo.UserInfo AS u
INNER JOIN dbo.Coupon AS c
ON u.Id = c.UserId
--OPTION(MERGE JOIN)

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_sqlserver_15

3. 哈希连接 

--------------------哈希连接--------------------
/*
散列连接同样仅仅只需要只访问1次双方的数据。散列连接通过在内存中建立散列表实现。
这比较消耗内存,如果内存不足还会占用tempdb。但并不像合并连接那样需要双方有序。
删除掉UserInfo的主键及其中的聚集索引,在执行以下SQL要删除掉聚集索引,否则两个有序输入SQL Server会选择代价更低的合并连接。
SQL Server利用两个上面的输入生成哈希表,下面的输入来探测,可以在属性窗口看到这些信息,
通常来说,所求数据在其中一方或双方没有排序的条件达成时,会选用哈希匹配。
*/

ALTER TABLE dbo.UserInfo DROP CONSTRAINT PK_UserInfo_Id --删除主键
--DROP INDEX Index_UserInfo_Name --删除聚集索引
--ALTER TABLE dbo.UserInfo ADD CONSTRAINT PK_UserInfo_Id PRIMARY KEY CLUSTERED(Id) --创建主键

SELECT * FROM dbo.UserInfo AS u
INNER JOIN dbo.Coupon AS c
ON u.Id = c.UserId

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_sqlserver_16

4. 多表并行

--------------------多表并行--------------------
/*
当多个表连接时,SQL Server还允许在多CPU或多核的情况下允许查询并行,这样无疑提高了效率。
*/

SELECT * FROM dbo.UserInfo AS u
INNER JOIN dbo.Coupon AS c
ON u.Id = c.UserId
INNER JOIN dbo.OneWayAirPolicy_20w AS o
ON u.Id = o.PId

SQL server 执行计划 Key Lookup 如何看懂sql执行计划_sqlserver性能优化_17