🤸🏾 🙋🏽 🍛 SQL Server中的计算列性能 🗳️ 🚑 👩🏽‍🔬

本文的翻译是专门为“ MS SQL Server Developer”课程的学生准备的。

计算的列可能会导致难以诊断的性能问题。本文讨论了许多问题和一些解决方案。

计算列是将计算嵌入表定义中的便捷方法。但是它们会导致性能问题，尤其是在表达式变得更加复杂，应用程序变得越来越苛刻并且数据量不断增长的情况下。

计算列是一个虚拟列，其值是根据表中其他列中的值计算得出的。默认情况下，计算值不是物理存储的，而是SQL Server在每个列请求上进行计算。这增加了处理器的负载，但减少了表更改时需要保留的数据量。

通常，非持久性计算列会占用大量CPU资源，从而减慢查询速度并冻结应用程序。幸运的是，SQL Server提供了几种方法来提高计算列的性能。您可以创建持久化的计算列，对其进行索引或同时执行这两个操作。

在演示中，我创建了四个相似的表，并使用WideWorldImporters演示数据库中的相同数据填充它们。每个表具有相同的计算列，但是两个表具有持久性，两个表具有索引。结果是以下选项：

表Orders1是非持久计算列。
表Orders2是一个持久的计算列。
该表Orders3是具有索引的非持久计算列。
表Orders4是带有索引的持久性计算列。

计算的表达式非常简单，数据集非常小。但是，足以证明持久化和索引计算列的原理以及这如何帮助解决性能问题。

未保存的计算列

也许在您的情况下，您可能希望非持久性计算列避免存储数据，创建索引或与非确定性列一起使用。例如，如果函数定义中缺少WITH SCHEMABINDING，则SQL Server将标量UDF视为不确定的。如果尝试使用此函数创建持久性计算列，则会收到一个错误，指出无法创建持久性列。

但是，应注意的是，自定义函数可能会产生自己的性能问题。如果表包含具有函数的计算列，则查询引擎将不使用并发性（除非您正在使用SQL Server 2019）。即使在查询中未指定计算列的情况下。对于大型数据集，这可能会对性能产生巨大影响。函数还可能减慢UPDATE的执行速度，并影响优化器如何在计算列上计算查询成本。这并不意味着您永远不要在计算列上使用函数，但是绝对应谨慎对待。

无论是否使用函数，创建一个非持久的计算列都非常简单。下一条指令CREATE TABLE定义Orders1包含计算列的表Cost。

USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit));

INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

要定义计算列，请指定其名称，后跟AS关键字和表达式。在我们的例子中，我们乘Quantity用Price和中减去Profit。创建表后，我们使用Sales.InvoiceLinesWideWorldImporters数据库表中的数据用INSERT填充它。接下来，我们执行SELECT。

SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;

该查询应返回22,973行或WideWorldImporters数据库中的所有行。该查询

的执行计划如图1所示。图1. Orders1表上的查询的执行计划

首先要注意的是聚簇索引扫描，这不是一种有效的数据获取方法。但这不是唯一的问题。让我们看一下聚集索引扫描属性中的逻辑读取（Actual Logical Reads）数（见图2）。

图2.逻辑读取以查询Orders1表

逻辑读取数（在这种情况下为1108）是已从数据缓存读取的页面数。目标是尝试尽可能减少此数目。因此，记住并将其与其他选项进行比较非常有用。

逻辑读取的数量也可以通过SET STATISTICS IO ON在执行SELECT之前运行语句来获得。查看CPU和总时间-SET STATISTICS TIME ON或查看查询执行计划中SELECT语句的属性。

另一点值得注意的是，执行计划中有两个Compute Scalar语句。第一个（右边的一个）是对每个返回的行的计算列值的计算。由于列值是动态计算的，因此除非在该列上创建索引，否则无法避免使用非持久计算列进行此步骤。

在某些情况下，非持久的计算列无需存储或使用索引即可提供所需的性能。这不仅节省了存储空间，而且还避免了与更新表或索引中的计算值相关的开销。但是，非持久的计算列通常会导致性能问题，然后您应该开始寻找替代方法。

持久计算列

通常用于解决性能问题的一种技术是将计算列定义为持久化。使用这种方法，可以预先计算表达式，并将结果与表数据的其余部分一起存储。

为了使列持久化，它必须是确定性的，也就是说，表达式必须始终为相同的输入返回相同的结果。例如，您不能在列表达式中使用GETDATE函数，因为返回值总是在变化。

要创建一个持久的计算列，您必须在列定义中添加一个关键字PERSISTED，如以下示例所示。

DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED);

INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

该表Orders2与该表几乎相同Orders1，除了该列Cost包含关键字PERSISTED。添加或修改行时，SQL Server会自动填充此列。当然，这意味着表Orders2将比table占用更多的空间Orders1。可以使用存储过程进行验证sp_spaceused。

sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO

图3显示了此存储过程的输出。表中的数据大小Orders1为8,824 KB，表中的数据大小为Orders212,936 KB。还有4 112 KB存储计算值。

图3. Orders1和Orders2表的大小比较

尽管这些示例基于相当小的数据集，但是您可以看到存储的数据量如何快速增长。但是，如果性能提高，这可能是一个折衷。

若要查看性能差异，请执行以下SELECT。

SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;

这与我用于Orders1表的SELECT相同（名称更改除外）。图4显示了执行计划。

图4.查询Orders2表的执行计划，

这也从聚簇索引扫描开始。但是这一次，只有一个Compute Scalar语句，因为不再需要在运行时计算所计算的列。通常，步骤越少越好。尽管并非总是如此。

第二个查询生成1593个逻辑读，比第一个表的1108个读多485个。尽管如此，它的运行速度比第一个要快。虽然只有大约100ms，有时少得多。处理器时间也减少了，但也没有减少太多。在更大的数量和更复杂的计算中，这种差异最可能会更大。

非持久计算列上的索引

通常用于提高计算列性能的另一种技术是索引。为了能够创建索引，列必须是确定性和精确的，这意味着表达式不能使用浮点和实型（如果列不是持久性的）。其他数据类型和SET参数也有限制。有关限制的完整列表，请参见SQL Server文档“计算列的索引”。

您可以检查非持久计算列是否适合通过其属性建立索引。让我们使用该函数来查看属性COLUMNPROPERTY。 IsDeterministic，IsIndexable和IsPrecise属性对我们很重要。

DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
  COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
  COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
  COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';

SELECT语句必须为每个属性返回1，以便可以对计算出的列进行索引（请参见图5）。

图5.验证

是否可以创建索引验证之后，您可以创建非聚集索引。Orders1我没有修改表，而是创建了第三个表（Orders3）并将索引包括在表定义中。

DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit),
  INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

我创建了一个非聚集覆盖索引，其中包含来自SELECT查询的列ItemID和Cost来自SELECT查询的列。创建并填充表和索引之后，您可以执行以下与前面的示例类似的SELECT语句。

SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;

图6显示了此查询的执行计划，该查询现在使用ix_cost3（索引查找）非聚集索引而不是执行聚集索引扫描。

图6. Orders3表上查询的执行计划

如果查看Index Seek语句的属性，您会发现该查询现在仅执行92个逻辑读取，而在SELECT语句的属性中，您将看到CPU和总时间减少了。差异不明显，但是同样，这是一个很小的数据集。

还应注意，执行计划中只有一个Compute Scalar语句，而不是第一个查询中的两个。由于已计算的列已建立索引，因此已经计算出了值。即使没有将列定义为持久性的，这也消除了在运行时计算值的需要。

存储列上的索引

您还可以在要保存的计算列上创建索引。虽然这将导致存储其他数据和索引数据，但在某些情况下可能很有用。例如，即使它使用浮点型或实型数据类型，也可以在持久性计算列上创建索引。当使用CLR函数以及无法检查函数是否具有确定性时，此方法也很有用。

以下语句CREATE TABLE创建一个表Orders4。该表定义同时包含一个持久列Cost和一个非聚集覆盖索引ix_cost4。

DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED,
  INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

创建并填充表和索引后，执行SELECT。

SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;

图7显示了执行计划。与前面的示例一样，查询从非聚集索引搜索（索引搜索）开始。

图7. Orders4表上的查询的执行计划

该查询还只执行92个逻辑读操作，与前一个逻辑读操作相比，其性能大致相同。两个计算列之间以及索引列和非索引列之间的主要区别是使用的空间量。让我们通过运行存储过程进行检查sp_spaceused。

sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GO

结果如图8所示。正如预期的那样，存储的计算列包含更多数据，而索引列包含更多索引。

图8.所有四个表的空间利用率比较

最有可能的是，您没有充分的理由就不需要索引存储的计算列。与其他与数据库相关的问题一样，您的选择应基于您的特定情况：您的查询和数据的性质。

在SQL Server中使用计算列

计算列不是常规表列，应小心处理，以免降低性能。通过存储或索引列可以解决大多数性能问题，但是两种方法都需要考虑额外的磁盘空间以及数据的变化方式。数据更改时，如果为持久化的计算列编制了索引，则必须在表或索引或两者中更新计算列的值。您只能决定哪种选择最适合您的特定情况。并且，很可能您将不得不使用所有选项。

阅读更多

SQL Server 2017图形数据库简介

SQL Server中的计算列性能

未保存的计算列

持久计算列

非持久计算列上的索引

存储列上的索引

在SQL Server中使用计算列

阅读更多

More articles: