SQL Server 冗余维护
介绍
冗余是维护的魔鬼, 是性能优化的天使
常见的冗余有
1. computed column
2. principal 的识别字段
3. cross computed
4. cascade soft delete
维护冗余的方案有很多. 比如 computed column, trigger, view, 甚至在应用层写 event bus.
但不同情况利弊也不同. 还得看场景决定.
我目前使用 computed column 和 trigger 来维护冗余.
对比在应用层维护, 好处是可以直接修改 SQL, 冗余一样可以正常 working (在业务还不稳定的情况下, 直接使用数据库来做信息管理可以提高效率和节约试错成本)
另一个好处是不需要在应用层额外的开发一套维护方案, 要知道 EF core 并没有现成的方案,甚至连 trigger 机制都没有 build-in 的.
Computed Column Same Row
比如 Subtotal, TotalAmount 这类的字段.
比较简单的 computed column 是依赖同一个 row 里面的字段, 比如 FullName, Subtotal
ALTER TABLE InvoiceItem DROP COLUMN Subtotal; GO ALTER TABLE InvoiceItem ADD Subtotal as (CAST(Qty as DECIMAL(19)) * UnitPrice) PERSISTED NOT NULL; GO
用普通的 computed column 就可以解决了, 只能依赖同行, 而且依赖的字段不可以是 computed column
Cross Table Computed Column
如果需要跨表, 比如 TotalAmount 要 SUM 子表.
就要使用 Trigger 监听所有依赖字段, 然后重新跑 Computed 方法.
例子:
GO CREATE OR ALTER TRIGGER TR_Contract_AfterInsert_ForCrossComputed_Project_ProjectBiddingCost ON [Contract] AFTER INSERT AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [ParentTable] SET [ProjectBiddingCost] = ISNULL( (SELECT SUM([ContractBiddingCost]) FROM [Contract] WHERE [ProjectId] = [ParentTable].[ProjectId] AND (([Deleted] = 0))), 0 ) FROM [Project] AS [ParentTable] INNER JOIN inserted ON [ParentTable].[ProjectId] = inserted.[ProjectId]; GO GO CREATE OR ALTER TRIGGER TR_Contract_AfterDelete_ForCrossComputed_Project_ProjectBiddingCost ON [Contract] AFTER DELETE AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [ParentTable] SET [ProjectBiddingCost] = ISNULL( (SELECT SUM([ContractBiddingCost]) FROM [Contract] WHERE [ProjectId] = [ParentTable].[ProjectId] AND (([Deleted] = 0))), 0 ) FROM [Project] AS [ParentTable] INNER JOIN deleted ON [ParentTable].[ProjectId] = deleted.[ProjectId]; GO GO CREATE OR ALTER TRIGGER TR_Contract_AfterUpdate_ForCrossComputed_Project_ProjectBiddingCost ON [Contract] AFTER UPDATE AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [ParentTable] SET [ProjectBiddingCost] = ISNULL( (SELECT SUM([ContractBiddingCost]) FROM [Contract] WHERE [ProjectId] = [ParentTable].[ProjectId] AND (([Deleted] = 0))), 0 ) FROM deleted INNER JOIN inserted ON deleted.[ContractId] = inserted.[ContractId] INNER JOIN [Project] AS [ParentTable] ON deleted.[ProjectId] = [ParentTable].[ProjectId] OR inserted.[ProjectId] = [ParentTable].[ProjectId] WHERE (((deleted.[Deleted] <> inserted.[Deleted]) OR (deleted.[Deleted] IS NULL OR inserted.[Deleted] IS NULL)) AND (deleted.[Deleted] IS NOT NULL OR inserted.[Deleted] IS NOT NULL)) OR (((deleted.[ProjectId] <> inserted.[ProjectId]) OR (deleted.[ProjectId] IS NULL OR inserted.[ProjectId] IS NULL)) AND (deleted.[ProjectId] IS NOT NULL OR inserted.[ProjectId] IS NOT NULL)) OR (((deleted.[ContractBiddingCost] <> inserted.[ContractBiddingCost]) OR (deleted.[ContractBiddingCost] IS NULL OR inserted.[ContractBiddingCost] IS NULL)) AND (deleted.[ContractBiddingCost] IS NOT NULL OR inserted.[ContractBiddingCost] IS NOT NULL)); GO
Principal Table 识别字段
比如 Name, Code, Number 之类的. 由于 foreign table 是依靠 Id 作为 foreign key, 而 Id 对业务来说不具备识别能力, 所以一般上会需要一些识别字段
每次 join table 获取识别字段对性能很伤, 语句也不好了, 所以就有了 Principal 识别字段的冗余.
同样可以使用 Trigger 来维护
例子:
GO CREATE OR ALTER TRIGGER [TR_PaymentInvoice_AfterInsert_ForPrincipalProperty_PaymentInvoice_ProjectNumber] ON [PaymentInvoice] AFTER INSERT AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [PaymentInvoice] SET [ProjectNumber] = [PurchaseOrder].[ProjectNumber] FROM [PaymentInvoice] INNER JOIN inserted ON [PaymentInvoice].[PurchaseOrderId] = [inserted].[PurchaseOrderId] INNER JOIN [PurchaseOrder] ON [PaymentInvoice].[PurchaseOrderId] = [PurchaseOrder].[PurchaseOrderId]; GO GO CREATE OR ALTER TRIGGER [TR_PaymentInvoice_AfterUpdate_ForPrincipalProperty_PaymentInvoice_ProjectNumber] ON [PaymentInvoice] AFTER Update AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [PaymentInvoice] SET [ProjectNumber] = [PurchaseOrder].[ProjectNumber] FROM deleted INNER JOIN inserted ON deleted.[PaymentInvoiceId] = inserted.[PaymentInvoiceId] INNER JOIN [PurchaseOrder] ON [inserted].[PurchaseOrderId] = [PurchaseOrder].[PurchaseOrderId] WHERE (((deleted.[PurchaseOrderId] <> inserted.[PurchaseOrderId]) OR (deleted.[PurchaseOrderId] IS NULL OR inserted.[PurchaseOrderId] IS NULL)) AND (deleted.[PurchaseOrderId] IS NOT NULL OR inserted.[PurchaseOrderId] IS NOT NULL)); GO GO CREATE OR ALTER TRIGGER [TR_PurchaseOrder_AfterUpdate_ForPrincipalProperty_PaymentInvoice_ProjectNumber] ON [PurchaseOrder] AFTER UPDATE AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [PaymentInvoice] SET [ProjectNumber] = inserted.[ProjectNumber] FROM deleted INNER JOIN inserted ON deleted.[PurchaseOrderId] = inserted.[PurchaseOrderId] INNER JOIN [PaymentInvoice] ON [inserted].[PurchaseOrderId] = [PaymentInvoice].[PurchaseOrderId] WHERE (((deleted.[ProjectNumber] <> inserted.[ProjectNumber]) OR (deleted.[ProjectNumber] IS NULL OR inserted.[ProjectNumber] IS NULL)) AND (deleted.[ProjectNumber] IS NOT NULL OR inserted.[ProjectNumber] IS NOT NULL)); GO
Cascade soft delete
SQL Server 支持 Cascade delete, 但如果希望 soft delete 就没有 build-in 支持了.
用 trigger 也是可以解决
例子:
GO CREATE OR ALTER TRIGGER [TR_TradeItem_AfterUpdate_ForCascadeSoftDelete_PurchaseRequisition] ON [TradeItem] AFTER UPDATE AS IF (ROWCOUNT_BIG() = 0) RETURN; SET NOCOUNT ON; UPDATE [PurchaseRequisition] SET [DeletedBy] = CASE WHEN deleted.[DateDeleted] IS NULL AND inserted.[DateDeleted] IS NOT NULL THEN CASE WHEN [PurchaseRequisition].[Deleted] = 1 THEN [PurchaseRequisition].[DeletedBy] ELSE inserted.DeletedBy END ELSE CASE WHEN [PurchaseRequisition].[DateDeleted] = deleted.[DateDeleted] THEN NULL ELSE [PurchaseRequisition].[DeletedBy] END END, DateDeleted = CASE WHEN deleted.[DateDeleted] IS NULL AND inserted.[DateDeleted] IS NOT NULL THEN CASE WHEN [PurchaseRequisition].[Deleted] = 1 THEN [PurchaseRequisition].[DateDeleted] ELSE inserted.[DateDeleted] END ELSE CASE WHEN [PurchaseRequisition].[DateDeleted] = deleted.[DateDeleted] THEN NULL ELSE [PurchaseRequisition].[DateDeleted] END END FROM deleted INNER JOIN inserted ON deleted.[TradeItemId] = inserted.[TradeItemId] INNER JOIN [PurchaseRequisition] ON inserted.[TradeItemId] = [PurchaseRequisition].[TradeItemId] WHERE (((deleted.[DateDeleted] <> inserted.[DateDeleted]) OR (deleted.[DateDeleted] IS NULL OR inserted.[DateDeleted] IS NULL)) AND (deleted.[DateDeleted] IS NOT NULL OR inserted.[DateDeleted] IS NOT NULL)); GO