SqlServer 垂直分表

標籤：style   影響   table   lte   foreign   提高   guid   mount   get   

當單表資料太多時。我們能夠水平劃分,參考 SqlServer 分區視圖實現水平分表 ，水平劃分能夠提高表的一些效能。

而 垂直分表 則相對非常少見到和用到，由於這可能是資料庫設計上的問題了。假設資料庫中一張表有部分欄位差點兒從不不更改但常常查詢，而部分欄位的資料頻繁更改。這樣的設計放到同一個表中就不合理了，相互影響太大了。在已存在改情況的表的時候，能夠考慮按列拆分表，即垂直分割。

由於垂直分表的案例比較少，近期由於存在這種表，所以個人搗鼓了一下。

源表設計結構：

--  源表CREATE TABLE [dbo].[DemoTab]([Guid] [uniqueidentifier] NOT NULL,[UserName] [nvarchar](30) NOT NULL,[Password] [nvarchar](30) NOT NULL,[UserAccount] [varchar](30) NOT NULL,[Amount] [numeric](18, 4) NULL,CONSTRAINT [PK_DemoTab] PRIMARY KEY CLUSTERED ([Guid]))GOALTER TABLE [dbo].[DemoTab] ADD CONSTRAINT [DF_DemoTab_Guid] DEFAULT (newsequentialid()) FOR [Guid]GO--  原來是訪問視圖的（優點就是視圖層不變）CREATE VIEW [dbo].[VDemoTab]ASSELECT [Guid],[UserName],[Password],[UserAccount],[Amount]FROM [dbo].[DemoTab]GO

註：拆分後各表的主鍵都是同樣了。並且拆分後的表是正常化的。

如今拆成兩張表：

注意選擇一張表作為基表，其它表都有與該表的外鍵。

--  分表【1】,以該表為"主表"，其它拆分出的表為"子表"CREATE TABLE [dbo].[DemoTab001]([Guid] [uniqueidentifier] NOT NULL,[UserName] [nvarchar](30) NOT NULL,[Password] [nvarchar](30) NOT NULL,CONSTRAINT [PK_DemoTab001] PRIMARY KEY CLUSTERED ([Guid]))GO--  主鍵預設值能夠不須要,由於插入資料前須要確定主索引值--ALTER TABLE [dbo].[DemoTab001] --ADD CONSTRAINT [DF_DemoTab001_Guid] DEFAULT (newsequentialid()) FOR [Guid]--GO--  分表【2】,"子表"CREATE TABLE [dbo].[DemoTab002]([Guid] [uniqueidentifier] NOT NULL,[UserAccount] [varchar](30) NOT NULL,[Amount] [numeric](18, 4) NULL,CONSTRAINT [PK_DemoTab002] PRIMARY KEY CLUSTERED ([Guid]))GO--  主鍵預設值能夠不須要,由於插入資料前須要確定主索引值--ALTER TABLE [dbo].[DemoTab002] --ADD CONSTRAINT [DF_DemoTab002_Guid] DEFAULT (newsequentialid()) FOR [Guid]--GO--  若主表變更主鍵則串聯更新或刪除（主鍵一般是不更新的，也可省去 ON UPDATE CASCADE）ALTER TABLE [dbo].[DemoTab002] ADD CONSTRAINT [FK_DemoTab002_DemoTab001_Guid] FOREIGN KEY ([Guid]) REFERENCES [DemoTab001]([Guid]) ON UPDATE CASCADE ON DELETE CASCADEGO

假設之前是對單個表或者視圖操作。拆分之後邏輯層修改可能非常多。為保持修改最小，能夠用聯合視圖操作。怎麼串連表依個人情況而定。

--  拆分後使用聯合視圖(INNER JOIN 也能夠)ALTER VIEW [dbo].[VDemoTab]ASSELECT T1.[Guid],T1.[UserName],T1.[Password],T2.[UserAccount],T2.[Amount]FROM [dbo].[DemoTab001] T1 LEFT JOIN [dbo].[DemoTab002] T2 ON T1.[Guid]=T2.[Guid]GO

這時問題來了，要對錶進行DML操作。insert , update , delete 怎麼解決？由於要求主鍵是分散在多個表而且是同樣的。

這時僅僅能用考慮觸發器來保證一致性了，觸發器則定義在視圖上，使用的是 INSTEAD OF 類型的觸發器。

insert 觸發器：

視圖 [VDemoTab] 中的 [Guid] 為表 插入時值。在插入觸發器中，虛擬表[inserted]的[Guid]是唯一的。所以在觸發器中能夠同一時候使用該 [Guid] 插入到多個分表中，保證了多個分表的[Guid]是同樣的。

--  insert 觸發器CREATE TRIGGER [dbo].[tgr_VDemoTab_insert]ON [dbo].[VDemoTab] INSTEAD OF INSERTAS BEGIN INSERT INTO [dbo].[DemoTab001]([Guid],[UserName],[Password]) SELECT [Guid],[UserName],[Password] FROM inserted;  INSERT INTO [dbo].[DemoTab002]([Guid],[UserAccount],[Amount]) SELECT [Guid],[UserAccount],[Amount] FROM inserted;ENDGO

update 觸發器：

同理，更新時涉及虛擬表 deleted 和 inserted，而更新是對視圖[VDemoTab]更新的，所以虛擬表inserted包含了全部的欄位，所以須要觸發器分別更新多個分表。

--  update 觸發器CREATE TRIGGER [dbo].[tgr_VDemoTab_update]  ON [dbo].[VDemoTab]   INSTEAD OF UPDATE  ASBEGIN UPDATE T1 SET  T1.[UserName] = T2.[UserName],  T1.[Password] = T2.[Password] FROM [dbo].[DemoTab001] AS T1, inserted AS T2 WHERE T1.[Guid] = T2.[Guid]  UPDATE T1 SET  T1.[UserAccount] = T2.[UserAccount],  T1.[Amount] = T2.[Amount] FROM [dbo].[DemoTab002] AS T1, inserted AS T2 WHERE T1.[Guid] = T2.[Guid] ENDGO

delete 觸發器：

刪除視圖[VDemoTab]記錄，涉及多個表則不同意刪除，因此僅僅要刪除"主表"的記錄就可以。其它分表都會串聯刪除。

--  delete 觸發器CREATE TRIGGER [dbo].[tgr_VDemoTab_delete]  ON [dbo].[VDemoTab]   INSTEAD OF DELETE  ASBEGIN    DELETE FROM [dbo].[DemoTab001]    WHERE [Guid] IN (SELECT [Guid] FROM deleted)ENDGO

設計基本就完畢了，如今進行測試。

INSERT INTO [dbo].[VDemoTab]([Guid],[UserName],[Password],[UserAccount],[Amount])SELECT NEWID(),'user01','pw01','account01',100UNION ALLSELECT NEWID(),'user02','pw02','account02',99UNION ALLSELECT NEWID(),'user03','pw03','account03',0GOUPDATE [VDemoTab] SET [Password]='pw',[Amount]='10'WHERE [Amount] >=0 AND [Amount]<100 AND [UserName] LIKE '%3'GODELETE FROM [VDemoTab] WHERE [UserName] = 'user03'GOSELECT * FROM [dbo].[DemoTab001] SELECT * FROM [dbo].[DemoTab002] SELECT * FROM [dbo].[VDemoTab]

基本操作都是正常的！垂直分表完畢！

效能怎麼樣呢？

因為 Guid 作為主鍵，使用的是 NEWID() 而不是  NEWSEQUENTIALID()，新增記錄時叢集索引都可能又一次排序較多資料。

分表之後，單個資料頁能儲存的資料很多其它了，可是分成多個表中，資料頁也增多了，同一時候 Guid 在每一個表都存在，所以查詢資料時IO會很多其它。

對於更新資料。在觸發器中是兩個表同一時候更新的。即使更新當中一個分表，其它分表都會影響。

假設分表之後不同一時候更新。能夠在觸發器中使用 if(update(col)) 來推斷更新的是那一列，就更新對應的基表即可，其它分表不更新。

最好的情況就是，拆分後的表都是“獨立”的。不用聯合視圖，查詢和更改都獨立，這須要更改邏輯層。

本文出自“Hello.KK （SQL Server）”的部落格，轉載請務必保留此出處http://blog.csdn.net/kk185800961/article/details/46740315

SqlServer 垂直分表