使用MSSQL查看执行计划的实践经验

1. 什么是执行计划

执行计划是指运行一个 SQL 查询时，SQL Server 数据库引擎在读取 SQL 查询的 T-SQL 代码后，会自动生成一种描述，描述了 SQL Server 数据库引擎如何连接表来检索请求的数据、如何编写聚合，以及搜索策略等。这种描述就是执行计划，根据这个计划可以优化查询性能。

2. MSSQL 查看执行计划的方法

在 MSSQL 中，查看查询计划有两种方法：

2.1 使用 SQL Server Management Studio (SSMS)

在 SQL Server Management Studio 界面中，可以通过以下步骤查看执行计划：

打开 SSMS

新建一个查询

在查询窗口输入要查看计划的 T-SQL 代码

在工具栏上或查询菜单中选中“执行查询计划”或按快捷键“Ctrl+L”

执行之后，SSMS 会打开一个新的查询分析器窗口，展示查询计划。

下面是一个使用 SSMS 查看计划的示例：

SELECT
  SalesPersonID,
  YEAR(OrderDate) AS OrderYear,
  SUM(TotalDue) AS SalesAmount
FROM
  Sales.SalesOrderHeader
WHERE
  YEAR(OrderDate) BETWEEN 2005 AND 2008
GROUP BY
  SalesPersonID,
  YEAR(OrderDate)
ORDER BY
  SalesPersonID ASC,
  YEAR(OrderDate) ASC;

执行结果如下所示：

2.2 使用 SET SHOWPLAN_XML 语句

在 MSSQL 中，可以使用 SET SHOWPLAN_XML 语句来查看查询计划。执行此语句后，SQL Server 数据库引擎会将查询计划显示为 XML 文档。

下面是一个使用 SET SHOWPLAN_XML 语句查看计划的示例：

SET SHOWPLAN_XML ON;
GO
 
SELECT
  SalesPersonID,
  YEAR(OrderDate) AS OrderYear,
  SUM(TotalDue) AS SalesAmount
FROM
  Sales.SalesOrderHeader
WHERE
  YEAR(OrderDate) BETWEEN 2005 AND 2008
GROUP BY
  SalesPersonID,
  YEAR(OrderDate)
ORDER BY
  SalesPersonID ASC,
  YEAR(OrderDate) ASC;
 
SET SHOWPLAN_XML OFF;
GO

执行结果如下所示：

3. 如何优化执行计划

在优化执行计划时，我们需要了解 SQL 查询执行计划的特点。执行计划是由 SQL Server 数据库引擎自动生成的，因此，我们需要对SQL Server 数据库引擎的工作原理有一定的了解。此外，在执行计划中，我们需要关注以下关键字：

SCAN：全表扫描，表示 SQL Server 数据库引擎将检查表中的所有行，以获得所需数据。

SEEK：索引查找，表示 SQL Server 数据库引擎将根据指定的关键字进行索引查找，以获得所需数据。

NESTED LOOP、MERGE JOIN、HASH JOIN：连接算子，表示 SQL Server 数据库引擎在连接表时使用的算子。

FILTER：过滤器，表示 SQL Server 数据库引擎在处理数据后会使用过滤器进行预处理。

在了解以上基本信息后，我们可以通过以下措施优化执行计划，提高 SQL 查询的性能：

3.1 创建索引

我们可以通过创建索引来实现快速查找数据，减少全表扫描的情况。

CREATE INDEX IX_OrderDate_SalesPersonID
ON Sales.SalesOrderHeader (OrderDate, SalesPersonID);
GO

这样可以优化上面的例子中查询数据的速度。

3.2 修改查询语句

我们可以修改查询语句，将 SQL Server 数据库引擎执行查询的方式更改为更有效的方式，例如，我们可以使用聚合函数或者更改 WHERE 子句，来优化查询。

SELECT
  SalesPersonID,
  SUM(CASE
        WHEN YEAR(OrderDate) = 2005 THEN TotalDue
        ELSE 0
      END) AS [SalesAmount_2005],
  SUM(CASE
        WHEN YEAR(OrderDate) = 2006 THEN TotalDue
        ELSE 0
      END) AS [SalesAmount_2006],
  SUM(CASE
        WHEN YEAR(OrderDate) = 2007 THEN TotalDue
        ELSE 0
      END) AS [SalesAmount_2007],
  SUM(CASE
        WHEN YEAR(OrderDate) = 2008 THEN TotalDue
        ELSE 0
      END) AS [SalesAmount_2008]
FROM
  Sales.SalesOrderHeader
WHERE
  YEAR(OrderDate) BETWEEN 2005 AND 2008
GROUP BY
  SalesPersonID
ORDER BY
  SalesPersonID ASC;

这样可以将执行计划中的 SCAN 操作转换成 SEEK 操作，从而优化查询性能。

4. 执行计划的常见问题

4.1 死锁问题

死锁是一个常见问题，一般是由于多个事务同时访问同一数据行或表，并试图以不同的顺序对这些数据行或表进行锁定。如果多个事务试图以相反的顺序锁定相同的资源，就会发生死锁。在执行计划中，出现死锁一般是因为执行计划中的操作顺序与锁定顺序不一致。

解决方法：

设置事务级别

使用加锁提示

重构查询语句

4.2 性能问题

性能问题是执行计划中的另一个常见问题。在执行计划中，很容易出现全表扫描等情况，导致性能下降。

解决方法：

设计优化的数据库结构

创建索引

重构查询语句

缓存中间结果

4.3 数据库安全问题

数据库安全问题通常是指在执行计划中可以看到敏感信息或者存在 SQL 注入等漏洞。

解决方法：

使用加密

设置数据库访问控制

避免动态 SQL

5. 总结

执行计划是优化 SQL 查询性能的重要工具，通过查看执行计划可以了解查询的执行过程和效率。在进行优化时，我们需要关注计划中的关键字，例如 SCAN、SEEK、NESTED LOOP、MERGE JOIN、HASH JOIN 等。同时，为了避免死锁、性能问题和安全问题，我们需要创建索引、设计优化的数据库结构、重构查询语句、缓存中间结果等。