SqlServer 垂直分表(減少程序改動)
由於sqlserver的設計特殊性,一般大量數據一般都是采用水平分表,而垂直分表隻是把text、圖片都較大數據放到單獨的表中,這樣數據設計會更合理,相對於mysql可能要好一點,mssql本來就是一個文件,基本上提升不大,目前來看幾十萬的數據沒有分不分表沒有任何影響,對於千萬以上數據還是采用水平分表比較好。
而 垂直分表 則相對很少見到和用到,因為這可能是數據庫設計上的問題瞭。如果數據庫中一張表有部分字段幾乎從不不更改但經常查詢,而部分字段的數據頻繁更改,這種設計放到同一個表中就不合理瞭,相互影響太大瞭。在已存在改情況的表的時候,可以考慮按列拆分表,即垂直拆分。
由於垂直分表的案例比較少,最近因為存在這樣的表,所以個人搗鼓瞭一下。
源表設計結構:
-- 源表 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]) ) GO ALTER TABLE [dbo].[DemoTab] ADD CONSTRAINT [DF_DemoTab_Guid] DEFAULT (newsequentialid()) FOR [Guid] GO -- 原來是訪問視圖的(好處就是視圖層不變) CREATE VIEW [dbo].[VDemoTab] AS SELECT [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 CASCADE GO
如果之前是對單個表或者視圖操作,拆分之後邏輯層改動可能很多,為保持改動最小,可以用聯合視圖操作。怎麼連接表依個人情況而定。
-- 拆分後使用聯合視圖(INNER JOIN 也可以) ALTER VIEW [dbo].[VDemoTab] AS SELECT 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 INSERT AS 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; END GO
update 觸發器:
同理,更新時涉及虛擬表 deleted 和 inserted,而更新是對視圖[VDemoTab]更新的,所以虛擬表inserted包括瞭所有的字段,所以需要觸發器分別更新多個分表。
-- update 觸發器 CREATE TRIGGER [dbo].[tgr_VDemoTab_update] ON [dbo].[VDemoTab] INSTEAD OF UPDATE AS BEGIN 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] END GO
delete 觸發器:
刪除視圖[VDemoTab]記錄,涉及多個表則不允許刪除,因此隻要刪除”主表”的記錄即可,其他分表都會級聯刪除。
-- delete 觸發器 CREATE TRIGGER [dbo].[tgr_VDemoTab_delete] ON [dbo].[VDemoTab] INSTEAD OF DELETE AS BEGIN DELETE FROM [dbo].[DemoTab001] WHERE [Guid] IN (SELECT [Guid] FROM deleted) END GO
設計基本就完成瞭,現在進行測試。
INSERT INTO [dbo].[VDemoTab]([Guid],[UserName],[Password],[UserAccount],[Amount]) SELECT NEWID(),'user01','pw01','account01',100 UNION ALL SELECT NEWID(),'user02','pw02','account02',99 UNION ALL SELECT NEWID(),'user03','pw03','account03',0 GO UPDATE [VDemoTab] SET [Password]='pw',[Amount]='10' WHERE [Amount] >=0 AND [Amount]<100 AND [UserName] LIKE '%3' GO DELETE FROM [VDemoTab] WHERE [UserName] = 'user03' GO SELECT * FROM [dbo].[DemoTab001] SELECT * FROM [dbo].[DemoTab002] SELECT * FROM [dbo].[VDemoTab]
基本操作都是正常的!垂直分表完成!
性能怎麼樣呢?
由於 Guid 作為主鍵,使用的是 NEWID() 而不是 NEWSEQUENTIALID(),新增記錄時聚集索引都可能重新排序較多數據。
分表之後,單個數據頁能存儲的數據更多瞭,但是分成多個表中,數據頁也增多瞭,同時 Guid 在每個表都存在,所以查詢數據時IO會更多。
對於更新數據,在觸發器中是兩個表同時更新的,即使更新其中一個分表,其他分表都會影響。如果分表之後不同時更新,可以在觸發器中使用 if(update(col)) 來判斷更新的是那一列,就更新相應的基表就行,其他分表不更新。
最好的情況就是,拆分後的表都是“獨立”的,不用聯合視圖,查詢和更改都獨立,這需要更改邏輯層。
到此這篇關於SqlServer 垂直分表(減少程序改動)的文章就介紹到這瞭,更多相關SqlServer 垂直分表內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- None Found