主页 / dba / 问题 / 343049
Accepted
Doug Hills
Asked: 2024-10-17 06:55:03 +0800 CST

让表忽略琐碎更新

  • 772

场景：我拥有一个 ETL 流程。该流程涉及从不同来源提取数据并使我的数据库与最新数据同步。

假设我有一个名为 [catalog] 的表。它包含我公司的产品目录。此表的可信来源每四个小时向我发送一个 CSV。我提取该 CSV 并确保我的 [catalog] 表与其同步。我使用如下更新：

UPDATE mc 
SET
      [field1] = tc.[field1]
    , [field2] = tc.[field2]
FROM [my].[catalog] as mc  
INNER JOIN [their].[catalog] as tc
    ON mc.id = tc.id
WHERE mc.[field1] <> tc.[field1]
OR mc.[field2] <> tc.[field2]

此命令将比较每个匹配项，并仅筛选出那些将看到有意义更改的行，然后仅更新这些行。我不想不必要地触碰任何行——这是 ETL 开发人员的长期任务。

不会执行任何琐碎更新（所谓琐碎更新，我指的是对某一行的更新，但该行中的任何字段均未发生改变）。未发生改变的行将保持原样，不受干扰。

这里用“不受干扰”这个词很贴切，因为更新一行而不影响值会产生不必要的成本。在内存和磁盘中，对行的更新会从物理上删除包含旧值的行，并插入包含新值的新行。所有这些更新都必须记录！因此，在什么都不做的过程中，我们已经在内存和磁盘上对表的索引和数据页进行了碎片化和分割。而且我们正在通过日志进行不必要的流量。

如果我们不做任何更改，那么简单的更新仍会导致内存和磁盘的混乱。为表提供一种自动忽略简单更新的方法，对于许多场景来说都是一大优势。

现在，假设某个笨手笨脚的开发人员通过某个客户端或程序更改进行了更新，该更新对大型表执行了类似操作，但没有包括任何措施来忽略“真正”不需要更新的行。我可以对表执行什么操作，让它表现得好像知道传递琐碎的更新一样？

trigger

3 个回答

  1. 您应该添加您正在使用的数据库系统、版本和版本...

    ...因为根据这些因素，您得出的一些结论可能会有所不同。

    例如：

    UPDATESQL Server在特定场景下会不同程度地不处理冗余语句。

    所以你的陈述...

    所有这些更新都必须记录！因此，在什么都不做的过程中，我们已经将内存和磁盘上表的索引和数据页碎片化和分割了。而且我们正在通过日志发送不必要的流量。

    如果我们不改变任何东西，那么一个简单的更新仍然会导致内存和磁盘的混乱。

    ...在某些情况下是不正确的。

    证明

    Paul White 就此主题写了一篇非常有趣的博客文章，题为《不更新更新的影响》。这篇文章很短，因此强烈建议完整阅读。但他最后总结的一些要点（我不想透露所有有趣的细节）如下：

    • 对聚簇表的非更新更新通常可以避免额外的日志记录和页面刷新，除非构成聚簇键（部分）的列受到更新操作的影响。

    • 堆表的行为与聚簇表相同，只是它们没有聚簇键来导致任何额外的日志记录或页面刷新。

      即使堆上存在非聚集主键，情况也是如此。因此，对堆的非更新更新通常可以避免额外的日志记录和刷新（但请参见下文）

    • 当使用 以外的任何语法将包含超过 8000 字节数据的 LOB 列更新为相同值时，堆和聚集表都将遭受额外的日志记录和刷新SET column_name = column_name。

    ...说真的，不要相信我上面的内容正确复制粘贴，去完整阅读保罗的文章。

    所以

    至少在 SQL Server 中，冗余更新（也称为非更新更新）并不总是不必要地“导致内存和磁盘的混乱”。

    但明确一点是好的

    因为并非每种情况都能得到保证，并且将来的情况可能会发生变化，无论是数据库引擎如何处理非更新UPDATE，还是代码和数据如何随着时间的推移而发展。

    有几种不同的方法可以尝试解决：

    我可以对表做些什么，使它表现得好像知道传递琐碎的更新一样？

    Akina 的答案是一种选择，尽管它仍然会为您预先删除的行生成删除日志记录，并且需要双重传递才能完成您的目标。

    另一种选择是散列行并将散列值具体化为列，以便对其进行索引。然后，您可以通过查询以非常有效的方式比较SourceTable散列TargetTable值不匹配的键，例如：

    UPDATE T
SET T.SomeField1 = S.SomeField1
FROM SourceTable AS S
INNER JOIN TargetTable AS T
    ON S.KeyField = T.KeyField -- Same row by key
    AND S.RowHash <> T.RowHash; -- But has changes

    一次通过，高效比较，无需生成额外的日志记录删除那些不会用于更新的行，因为它们的值是多余的。

    过去，我曾将这种技术用于类似问题的大型数据集。我特别使用该HASHBYTES()函数来计算行哈希，它是确定性的，可以存储在计算列或索引视图中（如果您不想修改表架构）。但同样，您在此处的选项将取决于您的数据库系统、版本和版本。

    附注

    在这里用“未受骚扰”这个词很恰当……

    我认为“未受骚扰”在这个语境中是一个非常糟糕的措辞，因为它具有通常的含义。

    • 1

  2. 您收到 CSV 并将其保存到[their].[catalog]表中。

    第一步是从该表中删除那些会导致“简单更新”的行。

    DELETE FROM [their].[catalog] AS tc
WHERE EXISTS (
    SELECT NULL
    FROM [my].[catalog] AS mc
    WHERE mc.id = tc.id 
      AND mc.[field1] = tc.[field1] 
      AND mc.[field2] = tc.[field2]
    );

    此查询将非常有效地使用相应的复合索引。因此，不要忘记在临时表中创建这些索引，其中包含从“事实来源”收到的实际数据状态。

    删除后，表[their].[catalog]仅包含将导致“实际更新”的行，您无需进行额外过滤即可进行更新：

    UPDATE mc 
SET [field1] = tc.[field1]
  , [field2] = tc.[field2]
FROM [my].[catalog] as mc  
INNER JOIN [their].[catalog] as tc
    ON mc.id = tc.id;

    该查询再次可以有效地使用相应的（主要？）索引。

    • 0
  3. Best Answer

    我非常感谢上述贡献。谢谢大家！Paul White 的文章链接非常有用。非常感谢。

    我的解决方案是向表 [row_hash] 添加一个持久计算列。它是 SQL Server 的 Hashbytes 函数的结果。

    INSTEAD OF 触发器允许我通过使用它们的键将插入和删除的伪表聚合在一起，并且仅对具有已更改 hash_column 的行进行操作。

    有了触发器，表格对于新手开发人员来说就更加宽容了。而且我还可以减少我编写的几乎每个 MERGE 语句所产生的一些开销。

    随着 row_hash 的持久化以及 INSTEAD OF 触发器充当边境牧羊犬，只有实际发生变化的行才会发生变化。

    设置此功能的代码位于此文档中：https：//github.com/islandmonk/update_only_when_changed/blob/main/row_hash%20infrastructure.sql

    以下是针对具有以下架构的表的使用示例：

        CREATE TABLE [dbo].[note](
          [note_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY 
        , [object_id] [int] NULL 
        , [column_id] [int] NULL 
        , [parent_note_id] [int] NULL 
        , [note] [nvarchar](max) NULL 
        , [created] [datetime] NOT NULL default(getdate())
    )

    设置忽略冗余更新所需的代码如下：

    -- Add row_hash column to table [dbo].[note]
--
ALTER TABLE [dbo].[note] ADD row_hash as HASHBYTES('sha2_512', CONCAT(
      [object_id]
    , [column_id]
    , [parent_note_id]
    , [note]
    , [created])
) PERSISTED; 
GO



--------
-- INSTEAD OF UPDATE trigger for [dbo].[note]
CREATE OR ALTER TRIGGER dbo_note__instead_of_IUD ON [dbo].[note]
INSTEAD OF UPDATE, INSERT, DELETE 
AS
    /*
    This is the definition of an INSTEAD OF trigger. Its initial purpose is to reduce churn on tables
    mostly for the sake of performance. There is nothing stopping you from altering this trigger to
    add other functionality. Keep in mind, also, that you can have AFTER UPDATE triggers on the same
    table as one with an INSTEAD OF UPDATE trigger. So that is still available to you even if you
    go with this approach.
    */

    UPDATE d
    SET 
          [object_id] = i.[object_id]
        , [column_id] = i.[column_id]
        , [parent_note_id] = i.[parent_note_id]
        , [note] = i.[note]
    FROM [dbo].[note] as d -- deleted
    INNER JOIN inserted as i 
        ON d.[note_id] = i.[note_id] 

    -- rows where there is no difference between inserted and deleted are redundant and ignored
    WHERE d.[row_hash] <> i.[row_hash] 


    -- Inserts proceed as usual

    INSERT [dbo].[note] (
          [object_id]
        , [column_id]
        , [parent_note_id]
        , [note]
        , [created]
    )
    SELECT 
          [object_id]
        , [column_id]
        , [parent_note_id]
        , [note]
        , [created]
    FROM inserted as i
    WHERE NOT EXISTS (
        SELECT TOP 1 1
        FROM deleted as d
        WHERE d.[note_id] = i.[note_id]     
    )

    -- DELETES proceed as usual

    DELETE t 
    FROM [dbo].[note] as t -- target 
    INNER JOIN deleted as d
        ON d.[note_id] = i.[note_id] 
    WHERE NOT EXISTS (
        SELECT TOP 1 1
        FROM inserted as i
        WHERE d.[note_id] = i.[note_id]     
    )
GO
--------------
    • 0

相关问题