提高 SQL Server 查询性能： 第 2 部分

引言

在前一篇文章：SQL Server 查询性能优化：第一部分 中，我们讨论了使用 SQL Profiler 来识别需要关注的查询，以查看性能是否可以得到改进。我们学习了如何设置标准，以便我们能够专注于最有可能通过修复来带来性能提升的查询。

在本文中，我们将探讨如何利用从 SQL Profiler 中获得的信息来改进我们的查询，使其性能更佳。

开始

在深入研究索引和执行计划之前，我们需要考虑查询是否写得好。如果查询本身写得很糟糕，我们最好重写它，那么研究执行计划就没有任何意义。

这个查询就是一个写得很糟糕的查询的典型例子，在我们考虑执行计划之前，它需要进行一次彻底的修改。

CREATE PROCEDURE dbo.BadSelectUsingCursor
   @StartQTY INT,
   @EndQTY INT
AS
DECLARE @userID INT
DECLARE @forename NVARCHAR(30)
DECLARE @surname NVARCHAR(30)
DECLARE @qty INT
DECLARE @Price MONEY
DECLARE @ProductID INT

SET NOCOUNT ON

CREATE TABLE #Output
(
   Forename NVARCHAR(30),
   Surname NVARCHAR(30),
   Qty INT,
   TotalPrice MONEY
)
DECLARE getOrders_Cursor CURSOR FOR
SELECT Qty, UserId, productID FROM userOrders WHERE 
Qty BETWEEN @StartQty AND @EndQty

OPEN getOrders_Cursor
FETCH NEXT FROM getOrders_Cursor INTO @Qty, @userID, @productID

WHILE @@FETCH_STATUS = 0
BEGIN
   DECLARE userOrders_Cursor CURSOR FOR
   SELECT forename, surname FROM [user]
   WHERE [ID] = @userID
   OPEN userOrders_Cursor

   FETCH NEXT FROM userOrders_Cursor 
   INTO @forename, @surname

   SELECT @Price = Cost FROM
   Product WHERE Id = @productID

   SET @Price = @Price * @Qty

   WHILE @@FETCH_STATUS = 0
   BEGIN
       INSERT INTO #Output
       VALUES (@Forename, @Surname, @Qty, @Price)
       FETCH NEXT FROM userOrders_Cursor 
       INTO @forename, @surname
   END
   CLOSE userOrders_Cursor
   DEALLOCATE userOrders_Cursor

   FETCH NEXT FROM getOrders_Cursor INTO @Qty, @userID, @productID
END

CLOSE getOrders_Cursor
DEALLOCATE getOrders_Cursor

SELECT * FROM #Output

正如我们所见，这个查询最大的问题在于它写得很糟糕。它完全不必要地使用了游标和临时表。顺便说一句，这是我几年前重写过的一个查询的版本。重写它后，性能提升了近 1500%——是的，你没看错，1500%。

好了，那么我们首先需要做的是重写这个查询，使其消除不必要的工作。通过阅读这个查询，我们发现它需要返回客户的名字和姓氏、他们订购商品的数量，以及基于数量范围的总价。带着这些信息，我们重写了这个查询，如下所示：

CREATE PROCEDURE dbo.BadSelectUsingCursor
  @StartQTY INT,
  @EndQTY INT
AS

SELECT
  forename,
  surname,
  qty,
  qty * Cost as TotalPrice
FROM
  [user] a
INNER JOIN
  userorders
ON
  userid = a.[id]
INNER JOIN
  product p
ON
  productId = p.[id]
WHERE 
  qty BETWEEN @StartQTY AND @EndQty
GO

现在，当这个查询运行时，它返回数据的速度快得多。在我的测试环境中，第一个版本返回所有记录需要 16 秒，而重写后的版本返回相同的记录只需不到一秒。

此时，我们需要考虑是否值得继续在我们的查询中寻求改进。通过 SQL Profiler 重新运行此查询是查看我们是否需要担心此查询的好方法。

我们假设现在这个查询已经处理完毕，然后转向一个看起来写得不错的查询。

执行计划

在继续深入之前，了解什么是执行计划以及为什么我们应该关心它是有价值的。

简单来说，执行计划（有时也称为查询计划或查询执行计划）是数据库用来访问信息的一系列步骤。由于我们用于访问数据，SQL 语言是声明性的，通常有多种方法来执行查询。每种方法都有不同的性能，这就是所谓的成本。

数据库引擎负责考虑不同的方法，以确定哪种方法是最佳的。作为开发人员，我们有责任通过使我们的查询尽可能高效来帮助数据库选择最佳方法。

好了，现在我们对执行计划的用途有了共同的理解，我们如何才能真正了解数据库实际在做什么呢？如果您还记得，在第一篇文章中，我们跟踪了数据库上运行了哪些 SQL。好消息是我们还可以将此跟踪信息直接运行到数据库上。（在我的示例中，我使用的是 SQL Server 2000 上的 Northwind 数据库和 Query Analyzer，但在 SSIS 和 SQL Server 2005 中运行结果应该相似。）

我们要看的第一个查询是

SELECT
   Title,
   FirstName,
   LastName
FROM
   Employees emp
LEFT JOIN
   Orders ord
ON
   emp.EmployeeId = ord.EmployeeId
INNER JOIN
   Shippers ship
ON
   ShipVia = ShipperID
WHERE
   Title LIKE 'Sales%' AND
   ShipName LIKE 'Chop%'

这是一个相当简单的查询，但它产生了一个有趣的计划。要运行它，我们将其复制到 Query Analyzer 中。在运行查询之前，值得查看数据库引擎认为查询需要做什么。要获取此信息，我们将显示估计的执行计划。可以在 **Query > Display Estimated Execution Plan** 中找到。这是该查询的计划：

让我们来看看这实际意味着什么。

Orders.PK_Order

这是一个聚集索引扫描。聚集索引扫描（顾名思义）是对聚集索引进行扫描。在这种情况下，聚集索引是 orders 表上的主键。有趣的是，这里有一个 `where` 子句需要引擎进一步考虑，即查询中的 `ShipName LIKE 'Chop%'` 部分。

如果此扫描的参数部分包含一个存在于 `ORDER BY` 子句中的项，那么查询引擎必须按照聚集索引存储行的顺序返回这些行。如果它不存在于 `ORDER BY` 子句中，那么引擎可以自由地生成另一种（可能更好的）方法来扫描索引，这可能不是排序的。

Shippers.PK_Shi 和 Employees.PK_Em

这些都是聚集索引查找。它们与扫描不同，因为它们可以使用 RDBMS 的能力来查找索引以从聚集索引中检索行。与扫描类似，`where` 子句会引导数据库引擎确定哪些行满足查找条件，然后从中评估匹配 `where` 条件的行。与扫描一样，`ORDER BY` 语句中某项的存在会影响其评估方式。

嵌套循环

这些是实际满足连接条件的方法。它们通过循环遍历顶部的查询项并在底部行中扫描匹配的行来工作。数据库可能会选择先执行排序以提高扫描性能，因为它会尝试使用索引进行匹配（如果可能）。

执行计划的最后一部分是 `select`。这是从数据库检索行的实际操作。

此时，我们可以运行查询并查看其性能。如果我们想查看数据库引擎最终确定的执行计划，只需选择 **Query > Show Execution Plan**。当我们运行查询时，将显示实际的执行计划。一切顺利的话，它应该与估计的执行计划差别不大。

索引调整向导

在 **Query Analyzer** 中仍然显示我们的查询，选择 **Query > Index Tuning Wizard**。该向导将分析查询以确定需要哪些索引来提高我们查询的性能，并且它应该告诉我们如果我们接受其建议，性能将提升多少百分比。

选择 **Next >** 进入向导的第一个实际页面。在这里，我们可以选择我们要分析的服务器和数据库，以及我们希望如何管理现有索引。

如果我们取消选择 **Keep all existing indexes**，查询引擎将有机会真正改变当前查询的优化方式。可能会删除现有索引并添加新索引以提高性能，因此乍一看，这似乎是一个有吸引力的选项，因为它通常可以推荐比保留现有索引更能带来改进的更改。但是我们必须警惕它提供的诱惑。如果我们删除索引以提高一个查询的性能，我们可能会破坏其他运行正常的查询。就像生活中的许多事情一样，仅仅因为我们能做到，并不意味着我们应该这样做。

好了，我们将选择 **Thorough** 调优，然后选择 **Next >**。工作负载屏幕允许我们选择要优化的查询。在这种情况下，将其保留为 SQL Query Analyzer selection，然后单击 **Next >**。这将带我们进入表选择屏幕。

选择 **Select All Tables**，然后再次单击 **Next >**。调优向导现在将分析查询并推荐改进性能所需的索引。

在我们的示例中，我们应该看到 21% 的性能提升，这还不错。再次单击 Next >，我们将有机会保存更改或立即应用它们。勾选 **Apply changes** 复选框，然后再次单击 **Next >**。最后，选择 **Finish** 以实际执行更新。

如果我们再次显示执行计划，我们可以看到它与我们的原始计划不同，引入了对 **Orders.Orders5** 的 **Indexed Seek**。这意味着它现在是在查找索引，而不是执行聚集索引扫描。

现在，我们将转向另一个更复杂的查询。

SELECT DISTINCT
   Title,
   FirstName,
   LastName,
   ProductName,
   (dets.UnitPrice * dets.Quantity)
FROM
   Employees emp
LEFT JOIN
   Orders ord
ON
   emp.EmployeeId = ord.EmployeeId
INNER JOIN
   [Order Details] dets
ON
   ord.OrderID = dets.OrderID
INNER JOIN
   Products prod
ON
   prod.ProductID = dets.ProductID
INNER JOIN
   Shippers ship
ON
   ShipVia = ShipperID
WHERE
   (dets.UnitPrice * dets.Quantity) > 200

正如你所见，我们正在引入更多的表，并且条件也更复杂一些。那么，数据库引擎对此有什么看法？嗯，这就是它生成的执行计划：

我们可以看到这与前面的查询非常相似。唯一需要查看的新内容是 Compute Scalar 和 Sort/Distinct。

Compute Scalar

随着查询中引入了计算，优化器知道它需要执行操作来返回计算结果。在这里，我们在两个位置使用了相同的计算，因此优化器知道它可以在两个位置使用相同的标量值。

Sort/Distinct

通过在查询中添加 `distinct` 关键字，我们告诉优化器它需要对输入进行排序，以便删除重复的行。

如果我们在此查询上运行索引调整向导，遵循上述相同步骤，我们将再次获得估计的 21% 性能提升。

再次运行查询会得到以下计划：

Hash Match/Inner Join

请注意，我们现在有不同的筛选条件。我们现在有一个 Hash Match/Inner Join。这基本上是使用顶部表构建哈希表，然后将底部表与之进行比较以查找匹配项。

注意事项

此时，您可能会认为您可以做的最好的事情是为每个字段添加索引，这样数据库引擎将始终针对索引进行查询。

这是诱人的，但您应该尽量避免走这条路，因为必须维护索引。这意味着每次执行插入、更新或删除时，都必须修改索引。您拥有的索引越多，需要维护的内容就越多。因此，虽然 SELECT 可能会开始更快地返回，但您可能会发现所有其他操作都会降级。另外，还有一个额外的陷阱是，索引的最大大小为 900 字节。所以，您不能为像这样的 Unicode 字段创建索引：`NVARCHAR(500)`。

结论

使用 SQL Profiler、Query Analyzer (或 SSIS) 和 SQL Profiler 等工具，我们可以识别我们系统上减速的可能原因。我们可以通过重写或调整索引来改进长时间运行的查询的性能。

我们还没有涵盖所有类型的执行计划运算符，例如 Sequential Scans，但我希望我已经提供了足够的信息，让您有兴趣进一步探索这个主题。