没有数据更改的UPDATE性能

CREATE TABLE MyTable (ID int PRIMARY KEY, Value int);
INSERT INTO MyTable (ID, Value)
VALUES
    (1, 1),
    (2, 2),
    (3, 3);

-- UPDATE 1
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2
    AND Value <> 2;
SELECT @@ROWCOUNT;

-- UPDATE 2
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2;
SELECT @@ROWCOUNT;

DROP TABLE MyTable;

#1 楼

如果我有一个UPDATE语句实际上并未更改任何数据（因为数据已经处于更新状态），那么将检查放在where子句中以防止更新是否对性能有好处？ >

当然可能存在，因为UPDATE 1会导致性能略有不同：


实际上并没有更新任何行（因此没有任何内容可写入磁盘，甚至没有最少的日志活动），并且
执行的限制锁少于进行实际更新所需的限制（因此并发性更好）（请参阅末尾的更新部分）

但是，那么您需要在系统上通过架构，数据和系统负载来衡量两者之间的差异。影响非更新UPDATE的影响的因素有很多：


要更新的表上的争用量
要更新的行数
/>如果要更新的表上有UPDATE触发器（如Mark在问题注释中所述）。如果执行UPDATE TableName SET Field1 = Field1，则将触发更新触发器，并指示该字段已更新（如果您使用UPDATE（）或COLUMNS_UPDATED函数进行检查），并且INSERTED和DELETED表中的字段具有相同的值。 />
此外，在保罗·怀特（Paul White）的文章“非更新更新的影响”（@spaghettidba在对他的回答的评论中指出）中可以找到以下摘要部分：


SQL Server包含许多优化措施，以避免在处理UPDATE操作时不必要的日志记录或页面刷新，这些操作不会导致对持久数据库的任何更改。


不更新集群表的更新通常可以避免额外的日志记录和页面刷新，除非构成集群键（一部分）的列受到更新操作的影响。 >如果集群密钥的任何部分被“更新”为相同的值，则会记录操作，就好像数据已更改一样，并且在缓冲池中将受影响的页标记为脏页。这是由于UPDATE转换为Delete-then-insert操作的结果。
堆表的行为与聚簇表相同，不同之处在于它们没有聚簇键来引起任何额外的日志记录或页面刷新。即使堆上存在非集群主键，情况依然如此。因此，对堆的非更新更新通常避免了额外的日志记录和刷新（但请参见下文）。
对于包含8000字节以上的LOB列的任何行，堆和群集表都将遭受额外的日志记录和刷新。使用“ SET column_name = column_name”以外的任何语法将数据更新为相同的值。无论更新事务有效的隔离级别如何，都会发生这种情况。




请记住（特别是如果您不按链接查看Paul的全文） ，则以下两项：非更新的更新仍具有某些日志活动，表明事务正在开始和结束。只是没有发生数据修改（这仍然是一个不错的节省）。
如上所述，您需要在系统上进行测试。使用与Paul相同的研究查询，看看是否获得相同的结果。我在系统上看到的结果与文章中显示的结果略有不同。仍然没有脏页要写，但是日志活动更多。如果ID不存在，则插入。 ...是否有可能获得我需要的行计数？


简单地说，如果您只处理一行，则可以执行以下操作：

 UPDATE MyTable
SET    Value = 2
WHERE  ID = 2
AND Value <> 2;

IF (@@ROWCOUNT = 0)
BEGIN
  IF (NOT EXISTS(
                 SELECT *
                 FROM   MyTable
                 WHERE  ID = 2 -- or Value = 2 depending on the scenario
                )
     )
  BEGIN
     INSERT INTO MyTable (ID, Value) -- or leave out ID if it is an IDENTITY
     VALUES (2, 2);
  END;
END;
 

对于多行，您可以使用OUTPUT子句获得做出该决定所需的信息。通过准确捕获要更新的行，然后可以缩小项目范围，以了解不更新不存在的行与不更新存在但不需要更新的行之间的区别。

我在以下答案中展示了基本的实现：

使用xml参数更新多个数据时如何避免使用合并查询？

方法所示答案不会过滤掉已存在但仍不需要更新的行。可以添加该部分，但首先需要确切显示要合并到MyTable的数据集的位置。他们来自临时餐桌吗？表值参数（TVP）？


更新1：

我终于能够进行一些测试，这是我发现的有关事务日志和锁定。首先，表的架构：

 CREATE TABLE [dbo].[Test]
(
  [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED,
  [StringField] [varchar](500) NULL
);
 

接下来，测试将字段更新为它已经具有的值：

 UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
 

结果：

 -- Transaction Log (2 entries):
Operation
----------------------------
LOP_BEGIN_XACT
LOP_COMMIT_XACT


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
8 - IX          6 - PAGE
5 - X           7 - KEY
 

最后，由于值未更改而筛选出更新的测试：

 UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
AND    rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';
 

结果：

 -- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
7 - IU          6 - PAGE
4 - U           7 - KEY
 

如您所见，过滤出该行时，没有任何内容写入事务日志，这与标记事务开始和结束的两个条目相对。虽然这两个条目几乎是空的，但它们仍然是东西。

同样，在筛选出未更改的行时，PAGE和KEY资源的锁定限制较少。如果没有其他进程与该表进行交互，那么它可能不是问题（但是，可能性有多大呢？）。请记住，任何链接的博客中显示的测试（甚至是我的测试）都隐含地假定表上没有争用，因为它从来都不是测试的一部分。说非更新的更新很轻，以至于不需要进行过滤就需要花费一小笔盐，因为测试或多或少是在真空中完成的。但是在生产中，此表很可能不是孤立的。当然，很可能一点点的日志记录和更多限制性的锁并不会转化为效率降低。那么，最可靠的信息来源可以回答这个问题吗？ SQL Server。具体来说：您的SQL Server。它会告诉您哪种方法更适合您的系统：-)。


更新2：

如果新值与以下值相同的操作当前值（即没有更新）输出编号，其中新值不同且需要更新的操作，那么以下模式可能会被证明是更好的，尤其是在表上存在很多争用时。这个想法是先做一个简单的SELECT来获取当前值。如果您没有得到值，那么您会得到有关INSERT的答案。如果您有值，则可以执行简单的IF并仅在需要时才发出UPDATE。

 DECLARE @CurrentValue VARCHAR(500) = NULL,
        @NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A',
        @ID INT = 4082117;

SELECT @CurrentValue = rt.StringField
FROM   dbo.Test rt
WHERE  rt.ID = @ID;

IF (@CurrentValue IS NULL) -- if NULL is valid, use @@ROWCOUNT = 0
BEGIN
  -- row does not exist
  INSERT INTO dbo.Test (ID, StringField)
  VALUES (@ID, @NewValue);
END;
ELSE
BEGIN
  -- row exists, so check value to see if it is different
  IF (@CurrentValue <> @NewValue)
  BEGIN
    -- value is different, so do the update
    UPDATE rt
    SET    rt.StringField = @NewValue
    FROM   dbo.Test rt
    WHERE  rt.ID = @ID;
  END;
END;
 

结果：

 -- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (2 Lock:Acquired events):
Mode            Type
--------------------------------------
6 - IS          5 - OBJECT
6 - IS          6 - PAGE
 

因此，仅获得了2个锁，而不是3个，并且这两个锁都是共享的，不是意图专用或意图更新（锁兼容性）。请记住，获得的每个锁也将被释放，每个锁实际上是2个操作，因此此新方法总共有4个操作，而不是最初提出的方法中的6个操作。考虑到此操作每15毫秒运行一次（大约由O.P.表示），即每秒约66次。因此，最初的建议是每秒396次锁定/解锁操作，而即使是较轻型的锁定，这种新方法也仅相当于每秒264次锁定/解锁操作。这不能保证性能出色，但绝对值得测试：-)。

#2 楼

缩小一点并考虑更大的图景。在现实世界中，您的更新语句真的是这样的吗：

UPDATE MyTable
  SET Value = 2
WHERE
     ID = 2
     AND Value <> 2;

还是要看起来像这样：

UPDATE Customers
  SET AddressLine1 = '123 Main St',
      AddressLine2 = 'Apt 24',
      City = 'Chicago',
      State = 'IL',
      (and a couple dozen more fields)
WHERE
     ID = 2
     AND (AddressLine1 <> '123 Main St'
     OR AddressLine2 <> 'Apt 24'
     OR City <> 'Chicago'
     OR State <> 'IL'
      (and a couple dozen more fields))

由于在现实世界中，表具有许多列。这意味着您将不得不生成许多复杂的动态应用逻辑来构建动态字符串，或者每次都必须指定每个字段的前后内容。

如果为每个表动态构建这些更新语句，仅传递要更新的字段，那么几年前，您可能会很快遇到类似于NHibernate参数大小问题的计划缓存污染问题。更糟糕的是，如果您在SQL Server中构建更新语句（如在存储过程中），则将消耗宝贵的CPU周期，因为SQL Server在大规模地将字符串串联在一起方面效率不高。

由于这些复杂性，因此在进行更新时，通常不进行这种逐行，逐字段的比较。请考虑基于集合的操作。

#3 楼

您可以看到跳过仅在行数较大时才需要更新的行（减少了日志记录，减少了要写入磁盘的脏页）的性能提高。

像您这样处理单行更新时，性能差异可以忽略不计。如果在任何情况下都可以更新行，请执行此操作。

有关该主题的更多信息，请参见Paul White的“不更新更新”

#4 楼

您可以合并更新并插入到一条语句中。在SQL Server上，可以使用MERGE语句进行更新和插入（如果找不到）。对于MySQL，可以使用INSERT ON DUPLICATE KEY UPDATE。

#5 楼

不能检查所有字段的值，而不能使用感兴趣的列获取哈希值，然后将其与针对表中的行存储的哈希值进行比较？

IF EXISTS (Select 1 from Table where ID =@ID AND HashValue=Sha256(column1+column2))
GOTO EXIT
ELSE