Sql-Server
帶父子設計的 SQL 索引
我希望了解數據庫父子設計中索引的典型方法。這是一個基本的財務計算器，用於計算每月使用並由儲存過程驅動的複雜佣金。
目前，推動這一趨勢的基本事務數約為 400,000 行。每個月都會增加大約 3-5%。每筆交易可能儲存 1-5 條（平均 3 條）佣金記錄，這意味著每個月將有一百萬條或更多條記錄插入下表中。
通常，根據分析師的調整，佣金會多次執行和刪除。
環境： SQL Server 2016 標準，32Gb 記憶體，8 個 cpu 核心
設計：
transaction_mstr - 儲存每個委託執行日期、週期和使用者詳細資訊（誰執行它、什麼日期和時間等）的表
transaction_map - 儲存周期內每個受委託賬戶的表
transaction_hdr - 儲存與該週期關聯的每個事務的表account
transaction_dtl - 儲存每筆交易佣金詳細資訊的表
鑰匙
transaction_mstr: id
transaction_map: id, mstr_id
transaction_hdr: id, mstr_id, map_id
transaction_dtl: id, mstr_id, map_id, hdr_id
簡單查詢來說明關係：
SELECT * FROM transaction_mstr a JOIN transaction_map b ON a.id = b.mstr_id JOIN transaction_hdr c ON b.id = c.map_id JOIN transaction_dtl d ON c.id = d.hdr_id;目前鍵
每個表中的 ID 列是一個主鍵（唯一的自動增量值）。
在與引用父主鍵相關的每個表上創建一個外鍵。
Ask 在目前狀態下，從 hdr 和 dtl 表中插入和刪除佣金非常慢。我已要求基礎架構團隊確保為環境提供適當的資源，但我想了解更好的密鑰設計是否會提高性能。
此外，我已將等待時間縮小到實際的插入和刪除。計算執行得相當快（3-5 分鐘），但插入到各個表中需要 30 分鐘以上。每個佣金週期的刪除大約需要 20 分鐘。
創建表/索引腳本
CREATE TABLE [dbo].[transaction_dtl]( [id] [bigint] IDENTITY(1,1) NOT NULL, [mstr_id] [smallint] NULL, [map_id] [bigint] NULL, [hdr_id] [bigint] NULL, [trx_id] [bigint] NULL, [trx_parent_id] [int] NULL, [agent_id] [smallint] NULL, [rate_id] [smallint] NULL, [frequency_id] [tinyint] NULL, [period_days] [tinyint] NULL, [commission] [numeric](38, 6) NULL, [row_create_ts] [datetime] NULL, CONSTRAINT [PK_transaction_dtl] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[transaction_hdr]( [id] [bigint] IDENTITY(1,1) NOT NULL, [mstr_id] [smallint] NULL, [map_id] [bigint] NULL, [trx_id] [bigint] NULL, [trx_parent_id] [int] NULL, [trx_other_id] [int] NULL, [level] [tinyint] NULL, [days_held] [smallint] NULL, [units_bal] [numeric](38, 6) NULL, [reversal] [int] NULL, [sortkey] [varchar](300) NULL, [row_create_ts] [datetime] NULL, CONSTRAINT [PK_transaction_hdr] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[transaction_map]( [id] [bigint] IDENTITY(1,1) NOT NULL, [mstr_id] [smallint] NULL, [fund_id] [tinyint] NULL, [account_id] [int] NULL, [wholesaler_id] [int] NULL, [unbalanced] [bit] NULL, [nobalance] [bit] NULL, [row_create_ts] [datetime] NULL, CONSTRAINT [PK_transaction_map] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[transaction_mstr]( [id] [smallint] IDENTITY(1,1) NOT NULL, [caller_sid] [varbinary](85) NOT NULL, [caller_name] [sysname] NOT NULL, [status] [tinyint] NOT NULL, [start_time] [datetimeoffset](7) NULL, [end_time] [datetimeoffset](7) NULL, [commission_type] [tinyint] NULL, [commission_date] [date] NULL, [run_date] [date] NULL, [frequency_id] [tinyint] NULL, [closed] [tinyint] NOT NULL, [row_create_ts] [datetime] NOT NULL, [row_modified_ts] [datetime] NULL, CONSTRAINT [PK_transaction_mstr] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[transaction_dtl] ADD CONSTRAINT [DF_commission_dtl_row_create_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_hdr] ADD CONSTRAINT [DF_transaction_hdr_reversal] DEFAULT ((0)) FOR [reversal] GO ALTER TABLE [dbo].[transaction_hdr] ADD CONSTRAINT [DF_commission_hdr_row_create_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_map] ADD CONSTRAINT [DF_transaction_map_balanced] DEFAULT ((0)) FOR [unbalanced] GO ALTER TABLE [dbo].[transaction_map] ADD CONSTRAINT [DF_transaction_map_nobalance] DEFAULT ((0)) FOR [nobalance] GO ALTER TABLE [dbo].[transaction_map] ADD CONSTRAINT [DF_commission_map_row_create_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_mstr] ADD CONSTRAINT [DF_transaction_master_closed] DEFAULT ((0)) FOR [closed] GO ALTER TABLE [dbo].[transaction_mstr] ADD CONSTRAINT [DF_transaction_master_create_row_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_dtl] WITH CHECK ADD CONSTRAINT [FK_transaction_dtl_hdr] FOREIGN KEY([hdr_id]) REFERENCES [dbo].[transaction_hdr] ([id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[transaction_dtl] CHECK CONSTRAINT [FK_transaction_dtl_hdr] GO ALTER TABLE [dbo].[transaction_hdr] WITH CHECK ADD CONSTRAINT [FK_transaction_hdr_map] FOREIGN KEY([map_id]) REFERENCES [dbo].[transaction_map] ([id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[transaction_hdr] CHECK CONSTRAINT [FK_transaction_hdr_map] GO ALTER TABLE [dbo].[transaction_map] WITH CHECK ADD CONSTRAINT [FK_transaction_map_mstr] FOREIGN KEY([mstr_id]) REFERENCES [dbo].[transaction_mstr] ([id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[transaction_map] CHECK CONSTRAINT [FK_transaction_map_mstr] GO級聯刪除
我一直對使用級聯刪除持謹慎態度。我可以很容易地編寫這個腳本，但認為它可能對參照完整性有用。
更新了索引程式碼（顯著降低了性能）
CREATE TABLE [dbo].[transaction_dtl]( [id] [bigint] IDENTITY(1,1) NOT NULL, [mstr_id] [int] NOT NULL, [map_id] [bigint] NOT NULL, [hdr_id] [bigint] NOT NULL, [trx_id] [bigint] NULL, [trx_parent_id] [int] NULL, [agent_id] [smallint] NULL, [rate_id] [smallint] NULL, [frequency_id] [tinyint] NULL, [period_days] [tinyint] NULL, [commission] [numeric](38, 6) NULL, [row_create_ts] [datetime] NULL, CONSTRAINT [PK_transaction_dtl] PRIMARY KEY CLUSTERED ( [mstr_id] ASC, [map_id] ASC, [hdr_id] ASC, [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[transaction_hdr]( [id] [bigint] IDENTITY(1,1) NOT NULL, [mstr_id] [int] NOT NULL, [map_id] [bigint] NOT NULL, [trx_id] [bigint] NULL, [trx_parent_id] [int] NULL, [trx_other_id] [int] NULL, [level] [tinyint] NULL, [days_held] [smallint] NULL, [units_bal] [numeric](38, 6) NULL, [reversal] [int] NULL, [sortkey] [varchar](300) NULL, [row_create_ts] [datetime] NULL, CONSTRAINT [PK_transaction_hdr] PRIMARY KEY CLUSTERED ( [mstr_id] ASC, [map_id] ASC, [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[transaction_map]( [id] [bigint] IDENTITY(1,1) NOT NULL, [mstr_id] [int] NOT NULL, [fund_id] [tinyint] NULL, [account_id] [int] NULL, [wholesaler_id] [int] NULL, [unbalanced] [bit] NULL, [nobalance] [bit] NULL, [row_create_ts] [datetime] NULL, CONSTRAINT [PK_transaction_map] PRIMARY KEY CLUSTERED ( [mstr_id] ASC, [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[transaction_mstr]( [id] [int] IDENTITY(1,1) NOT NULL, [caller_sid] [varbinary](85) NOT NULL, [caller_name] [sysname] NOT NULL, [status] [tinyint] NOT NULL, [start_time] [datetimeoffset](7) NULL, [end_time] [datetimeoffset](7) NULL, [commission_type] [tinyint] NULL, [commission_date] [date] NULL, [run_date] [date] NULL, [frequency_id] [tinyint] NULL, [closed] [tinyint] NOT NULL, [row_create_ts] [datetime] NOT NULL, [row_modified_ts] [datetime] NULL, CONSTRAINT [PK_transaction_mstr] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[transaction_dtl] ADD CONSTRAINT [DF_commission_dtl_row_create_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_hdr] ADD CONSTRAINT [DF_transaction_hdr_reversal] DEFAULT ((0)) FOR [reversal] GO ALTER TABLE [dbo].[transaction_hdr] ADD CONSTRAINT [DF_commission_hdr_row_create_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_map] ADD CONSTRAINT [DF_transaction_map_balanced] DEFAULT ((0)) FOR [unbalanced] GO ALTER TABLE [dbo].[transaction_map] ADD CONSTRAINT [DF_transaction_map_nobalance] DEFAULT ((0)) FOR [nobalance] GO ALTER TABLE [dbo].[transaction_map] ADD CONSTRAINT [DF_commission_map_row_create_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_mstr] ADD CONSTRAINT [DF_transaction_master_closed] DEFAULT ((0)) FOR [closed] GO ALTER TABLE [dbo].[transaction_mstr] ADD CONSTRAINT [DF_transaction_master_create_row_ts] DEFAULT (getdate()) FOR [row_create_ts] GO ALTER TABLE [dbo].[transaction_dtl] WITH CHECK ADD CONSTRAINT [FK_transaction_dtl_transaction_hdr] FOREIGN KEY([mstr_id], [map_id], [hdr_id]) REFERENCES [dbo].[transaction_hdr] ([mstr_id], [map_id], [id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[transaction_dtl] CHECK CONSTRAINT [FK_transaction_dtl_transaction_hdr] GO ALTER TABLE [dbo].[transaction_hdr] WITH CHECK ADD CONSTRAINT [FK_transaction_hdr_transaction_map] FOREIGN KEY([mstr_id], [map_id]) REFERENCES [dbo].[transaction_map] ([mstr_id], [id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[transaction_hdr] CHECK CONSTRAINT [FK_transaction_hdr_transaction_map] GO ALTER TABLE [dbo].[transaction_map] WITH CHECK ADD CONSTRAINT [FK_transaction_map_transaction_mstr] FOREIGN KEY([mstr_id]) REFERENCES [dbo].[transaction_mstr] ([id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[transaction_map] CHECK CONSTRAINT [FK_transaction_map_transaction_mstr] GO
有正確的方法來儲存父/子層次結構。
將每個子表的 ID 放在最後，這樣每個表都有一個主鍵聚集索引，其中父鍵是前導列。例如主鍵應該是：
transaction_mstr: id transaction_map: mstr_id, id transaction_hdr: mstr_id, map_id, id transaction_dtl: mstr_id, map_id, hdr_id, id這樣，每個父級的所有子行都儲存在一起，並且有一個索引支持從父級高效查找子行，並在刪除父行時防止全表掃描。
但是對於復合鍵，您需要使用完整的外鍵加入。例如
SELECT * FROM transaction_mstr a JOIN transaction_map b ON b.mstr_id = a.id JOIN transaction_hdr c ON c.mstr_id = b.mstr_id AND c.map_id = c.id JOIN transaction_dtl d ON d.mstr_id = c.mstr_id AND d.map_id = c.map_id AND d.hdr_id = c.id