MSSQL 执行计划

作者：lixiaofeng

2021 年 12 月 13 日
本文字数：3838 字
阅读完需：约 13 分钟

执行计划，是 sql 语句在物理层面的具体执行过程。我们平时的 select * from table 等等，都是逻辑层面的执行。

实例数据库：Performance 和 Performance_2

这两个数据库中的数据都是一样的，表结构和数据量，都一样。

区别是，Performance 库中的表上都有相关的索引；Performance_2 上没有索引。

显示 SQL 语句的执行计划：

如下图，我们点击红框选中的图标，可以显示 sql 语句的执行计划。

如何看执行计划：

入下图箭头所示，从右到左看各个操作符

常用的执行计划。

1，表扫描

表扫描，主要就是从叶子节点的第一页开始扫描，通过链表，一直扫描的最后一页的过程。

我们看下面的语句：

dbcc dropcleanbuffers，表示从缓冲池删除所有的缓冲；

dbcc freeproccache，删除缓存的计划；

set statistics io on，显示执行过程中的 IO 信息（数据库服务器的瓶颈一般在 IO 上）；

set statistics time on，显示执行过程中的时间信息；

我们要重点关注执行计划操作符，操作对象和 IO 信息。

我们去 SSMS 中查看索引情况和具体的执行计划。

dbcc dropcleanbuffersdbcc freeproccachegoset statistics io onset statistics time ongouse Performance_2SELECT orderid, custid, empid, shipperid, orderdateFROM dbo.OrdersWHERE orderid BETWEEN 101 AND 120;

复制代码

2，索引查找

接下来我们看索引查找。

这次我们使用 Performance 数据库，Orders 表上有相关的索引。

我们去 SSMS 中查看索引和执行计划。

重点关注执行计划操作符，操作对象和 IO 信息。

dbcc dropcleanbuffersdbcc freeproccachegoset statistics io onset statistics time ongouse PerformanceSELECT orderid, custid, empid, shipperid, orderdateFROM dbo.OrdersWHERE orderid BETWEEN 101 AND 120;

复制代码

3，关于逻辑读，物理读和预读

上面的两个例子中，我们重点关注了 IO 信息，那么什么是逻辑读，物理读和预读呢？

为了解释这三种读，我们先看 sql 语句的执行过程。

sqlserver 引擎可以分为计算引擎（关系引擎）和存储引擎。

sql 语句传输到 sqlserer 之后，首先关系引擎负责进行，词法分析语法分析，然后生成并选择执行计划（也有可能使用缓存的计划），

有了执行计划之后，就去存储引擎访问数据，存储引擎访问磁盘上的数据文件，返回锁需要的数据（也有可能，所需要的数据已经才缓存中，无需再去访问磁盘）

逻辑读：就是指数据在缓存中，直接从缓存中读取数据；

物理读：就是发现所需要的数据不再缓存中，需要从磁盘读取数据；

预读：如下图，就是在生成执行计划的过程中，引擎已经知道需要某一部分数据，而这部分数据不再缓存中，于是马上从磁盘提前（因为 sql 还没有到执行阶段）读取到缓存。

数据在内存和磁盘间的交换

数据存取的基本单位是页面（page）
SQL Server 会把尽可能多的数据放入内存中（通常内存使用比例会达到 95%及以上，其他数据库也一样，但是 linux 平台对内存的限制比较严格）
缓存的清理机制，LUR 算法，最近最少使用算法

Scan Count：表示迭代器运行的次数，在涉及到连接操作符的时候非常有用

Logical Reads：从内存读取的次数

Physical Reads：从磁盘读取的次数

Read-ahead Reads：表示迭代器预读的数据页。

预读机制，若 SQL Server 执行引擎在执行计划的某一步时，发现某些可能需要的数据不在内存页中，就会预告将这些数据从磁盘读取到内存。

4，覆盖索引和非覆盖索引

创建索引

如下面的语句，索引键是两个列：orderid 和 orderdate，该索引又包括了 custid 列

use Performance_2--drop index idx_unc_od_oid_i_cid_eid on OrdersCREATE NONCLUSTERED INDEX idx_unc_od_oid_i_cid_eid  ON dbo.Orders(orderid,orderdate)  include (custid)go

复制代码

覆盖索引查找

我们看下面的查询，下面的查询，查询三个列，而这三个列恰好都包括在上面的索引 idx_unc_od_oid_i_cid_eid 中。

我们仔细看下执行计划，操作符是索引查询，操作对象是非聚集索引 idx_unc_od_oid_i_cid_eid 。

也就是说，在索引对象 idx_unc_od_oid_i_cid_eid 中，已经可能完成所需要的所有查询需求，无需回到表对象 Orders 获取数据。

我们到 SSMS 中，看具体执行。

dbcc dropcleanbuffersdbcc freeproccachegoset statistics io onset statistics time ongouse Performance_2SELECT orderdate, orderid,custid--, empidFROM dbo.OrdersWHERE orderid BETWEEN 101 AND 420;

复制代码

非覆盖索引查找

接下来，我们来看下面的查询，这个查询相比上面的查询，多查询了一个字段 empid。

因为 where 条件 orderid 字段有索引，索引应该使用索引查找，

有因为需要返回的 empid 字段不再索引上，索引需要回表操作。

我们到 SSMS 中去看具体的执行计划。

这里还要关注执行计划中有循环操作。

dbcc dropcleanbuffersdbcc freeproccachegoset statistics io onset statistics time ongouse Performance_2SELECT orderdate, orderid,custid, empidFROM dbo.OrdersWHERE orderid BETWEEN 101 AND 420;;

复制代码

5，多列索引的顺序

上面的索引中，我们创建的索引，索引键包括两个列：orderid,orderdate，那么我们在 where 条件中，分别查询这两个列，执行计划是否相同呢？

1，查询索引键的第一个列，

我们依然重点关注，操作符操作对象和 IO 信息；

操作符是索引查找，操作对象是 idx_unc_od_oid_i_cid_eid 非聚集索引

我们到 SSMS 中，看具体执行。

dbcc dropcleanbuffersdbcc freeproccachegoset statistics io onset statistics time ongouse Performance_2
--drop index idx_unc_od_oid_i_cid_eid on Orders--CREATE NONCLUSTERED INDEX idx_unc_od_oid_i_cid_eid--  ON dbo.Orders(orderid,orderdate)--  INCLUDE(custid, empid);
SELECT orderid,orderdate,custid --, empidFROM dbo.OrdersWHERE orderid BETWEEN 101 AND 4200 ;

复制代码

2，我们看第二个查询，查询的条件是索引键中的第二列

我们依然重点关注，操作符操作对象和 IO 信息；

操作符是索引扫描（这是和上面查询不一样的地方），操作对象是 idx_unc_od_oid_i_cid_eid 非聚集索引

我们到 SSMS 中，看具体执行。

dbcc dropcleanbuffersdbcc freeproccachegoset statistics io onset statistics time ongouse Performance_2
--drop index idx_unc_od_oid_i_cid_eid on Orders--CREATE NONCLUSTERED INDEX idx_unc_od_oid_i_cid_eid--  ON dbo.Orders(orderdate,orderid)--  INCLUDE(custid, empid);
SELECT orderid,orderdate,custid, empidFROM dbo.OrdersWHERE orderid BETWEEN 101 AND 4200 ;