由于sqlserver的設(shè)計(jì)特殊性，一般大量數(shù)據(jù)一般都是采用水平分表，而垂直分表只是把text、圖片都較大數(shù)據(jù)放到單獨(dú)的表中，這樣數(shù)據(jù)設(shè)計(jì)會(huì)更合理，相對(duì)于mysql可能要好一點(diǎn)，mssql本來就是一個(gè)文件，基本上提升不大，目前來看幾十萬的數(shù)據(jù)沒有分不分表沒有任何影響，對(duì)于千萬以上數(shù)據(jù)還是采用水平分表比較好。
而 垂直分表 則相對(duì)很少見到和用到，因?yàn)檫@可能是數(shù)據(jù)庫設(shè)計(jì)上的問題了。如果數(shù)據(jù)庫中一張表有部分字段幾乎從不不更改但經(jīng)常查詢，而部分字段的數(shù)據(jù)頻繁更改，這種設(shè)計(jì)放到同一個(gè)表中就不合理了，相互影響太大了。在已存在改情況的表的時(shí)候，可以考慮按列拆分表，即垂直拆分。
由于垂直分表的案例比較少，最近因?yàn)榇嬖谶@樣的表，所以個(gè)人搗鼓了一下。
源表設(shè)計(jì)結(jié)構(gòu)：
-- 源表
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
注：拆分后各表的主鍵都是相同了，而且拆分后的表是規(guī)范化的。
現(xiàn)在拆成兩張表：
注意選擇一張表作為基表，其他表都有與該表的外鍵。
-- 分表【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
-- 主鍵默認(rèn)值可以不需要,因?yàn)椴迦霐?shù)據(jù)前需要確定主鍵值
--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
-- 主鍵默認(rèn)值可以不需要,因?yàn)椴迦霐?shù)據(jù)前需要確定主鍵值
--ALTER TABLE [dbo].[DemoTab002]
--ADD CONSTRAINT [DF_DemoTab002_Guid] DEFAULT (newsequentialid()) FOR [Guid]
--GO
-- 若主表變更主鍵則級(jí)聯(lián)更新或刪除（主鍵通常是不更新的，也可省去 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
如果之前是對(duì)單個(gè)表或者視圖操作，拆分之后邏輯層改動(dòng)可能很多，為保持改動(dòng)最小，可以用聯(lián)合視圖操作。怎么連接表依個(gè)人情況而定。
-- 拆分后使用聯(lián)合視圖(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
這時(shí)問題來了，要對(duì)表進(jìn)行DML操作，insert , update , delete 怎么解決？因?yàn)橐笾麈I是分散在多個(gè)表并且是相同的！
這時(shí)只能用考慮觸發(fā)器來保證一致性了，觸發(fā)器則定義在視圖上，使用的是 INSTEAD OF 類型的觸發(fā)器。
insert 觸發(fā)器：
視圖 [VDemoTab] 中的 [Guid] 為表 插入時(shí)值，在插入觸發(fā)器中，虛擬表[inserted]的[Guid]是唯一的，所以在觸發(fā)器中可以同時(shí)使用該 [Guid] 插入到多個(gè)分表中，保證了多個(gè)分表的[Guid]是相同的！
-- insert 觸發(fā)器
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 觸發(fā)器：
同理，更新時(shí)涉及虛擬表 deleted 和 inserted，而更新是對(duì)視圖[VDemoTab]更新的，所以虛擬表inserted包括了所有的字段，所以需要觸發(fā)器分別更新多個(gè)分表。
-- update 觸發(fā)器
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 觸發(fā)器：
刪除視圖[VDemoTab]記錄，涉及多個(gè)表則不允許刪除，因此只要?jiǎng)h除"主表"的記錄即可，其他分表都會(huì)級(jí)聯(lián)刪除。
-- delete 觸發(fā)器
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
設(shè)計(jì)基本就完成了，現(xiàn)在進(jìn)行測試。
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()，新增記錄時(shí)聚集索引都可能重新排序較多數(shù)據(jù)。
分表之后，單個(gè)數(shù)據(jù)頁能存儲(chǔ)的數(shù)據(jù)更多了，但是分成多個(gè)表中，數(shù)據(jù)頁也增多了，同時(shí) Guid 在每個(gè)表都存在，所以查詢數(shù)據(jù)時(shí)IO會(huì)更多。
對(duì)于更新數(shù)據(jù)，在觸發(fā)器中是兩個(gè)表同時(shí)更新的，即使更新其中一個(gè)分表，其他分表都會(huì)影響。如果分表之后不同時(shí)更新，可以在觸發(fā)器中使用 if(update(col)) 來判斷更新的是那一列，就更新相應(yīng)的基表就行，其他分表不更新。
最好的情況就是，拆分后的表都是“獨(dú)立”的，不用聯(lián)合視圖，查詢和更改都獨(dú)立，這需要更改邏輯層。
到此這篇關(guān)于SqlServer 垂直分表(減少程序改動(dòng))的文章就介紹到這了,更多相關(guān)SqlServer 垂直分表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- sqlserver2017共享功能目錄路徑不可改的解決方法
- SQLServer2008提示評(píng)估期已過解決方案
- SQLServer2019 數(shù)據(jù)庫的基本使用之圖形化界面操作的實(shí)現(xiàn)
- SQLServer2019 數(shù)據(jù)庫環(huán)境搭建與使用的實(shí)現(xiàn)
- SQLServer 日期函數(shù)大全(小結(jié))
- SQLServer2019配置端口號(hào)的實(shí)現(xiàn)
- sqlserver主鍵自增的實(shí)現(xiàn)示例
- C語言中操作sqlserver數(shù)據(jù)庫案例教程
