SqlServer 分區視圖實現水平分表

SqlServer 分區視圖實現水平分表

我們都知道在數據庫數據量較多的時候,可數據進行水平擴展,如分庫,分區,分表（也叫分區）等。對于分表的一個方案,就是使用分區視圖實現。

分區視圖允許將大型表中的數據拆分成較小的成員表。根據其中一列中的數據值范圍，在各個成員表之間對數據進行分區。每個成員表的數據范圍都在為分區依據列指定的 CHECK 約束中定義。然后定義一個視圖，以使用 UNION ALL 將選定的所有成員表組合成單個結果集。引用該視圖的 SELECT 語句為分區依據列指定搜索條件后，查詢優化器將使用 CHECK 約束定義確定哪個成員表包含相應行。

CHECK 約束 在查詢方面提供更好的優化特性，看一位大俠的實驗 SQL Server中使用Check約束提升性能 ，當前在其他操作方面就不太好了，以下測試。

當前測試為本地分區視圖：

[sql] view plain copy

1. USE [DemoDB]  
2. GO  
3.   
4. --  創建結構相同的表，[id] 不要設置自增（IDENTITY(1,1) ），因為插入表前就需要知道id值  
5. --  DROP TABLE [DemoTab01],[DemoTab02],[DemoTab03]  
6. CREATE TABLE [dbo].[DemoTab01](  
7.     [id] [int] NOT NULL,  
8.     [insdate] [datetime] NULL  
9. ) ON [PRIMARY]  
10. GO  
11. CREATE TABLE [dbo].[DemoTab02](  
12.     [id] [int] NOT NULL,  
13.     [insdate] [datetime] NULL  
14. ) ON [PRIMARY]  
15. GO  
16. CREATE TABLE [dbo].[DemoTab03](  
17.     [id] [int] NOT NULL,  
18.     [insdate] [datetime] NULL  
19. ) ON [PRIMARY]  
20. GO  
21.   
22. --  約束每個表的范圍  
23. ALTER TABLE [dbo].[DemoTab01] WITH CHECK ADD CONSTRAINT [CK_DemoTab01_id] CHECK ([id] BETWEEN 0 AND 99999 )  
24. GO  
25. ALTER TABLE [dbo].[DemoTab02] WITH CHECK ADD CONSTRAINT [CK_DemoTab02_id] CHECK ([id] BETWEEN 100000 AND 199999 )  
26. GO  
27. ALTER TABLE [dbo].[DemoTab03] WITH CHECK ADD CONSTRAINT [CK_DemoTab03_id] CHECK ([id] BETWEEN 200000 AND 299999 )  
28. GO  
29.   
30. --  既然是按id劃分，把id作為聚集索引更容易定位查找  
31. ALTER TABLE [dbo].[DemoTab01] ADD CONSTRAINT [PK_DemoTab01_id] PRIMARY KEY CLUSTERED ([id] ASC)  
32. GO  
33. ALTER TABLE [dbo].[DemoTab02] ADD CONSTRAINT [PK_DemoTab02_id] PRIMARY KEY CLUSTERED ([id] ASC)  
34. GO  
35. ALTER TABLE [dbo].[DemoTab03] ADD CONSTRAINT [PK_DemoTab03_id] PRIMARY KEY CLUSTERED ([id] ASC)  
36. GO  
37.   
38.   
39. --  每個字段名稱列出，避免用星號，否則升級增刪字段不同時會出錯  
40. --  DROP VIEW [dbo].[V_DemoTab]  
41. CREATE VIEW [dbo].[V_DemoTab]  
42. AS  
43. SELECT [id],[insdate] FROM [dbo].[DemoTab01]  
44. UNION ALL  
45. SELECT [id],[insdate] FROM [dbo].[DemoTab02]  
46. UNION ALL  
47. SELECT [id],[insdate] FROM [dbo].[DemoTab03]  
48. GO  
49.   
50. --  121317行數據  
51. INSERT INTO [V_DemoTab]([id],[insdate])  
52. SELECT SalesOrderDetailID,ModifiedDate FROM AdventureWorks2012.Sales.SalesOrderDetail  
53. GO  
54.   
55.   
56. SELECT COUNT(*) FROM [dbo].[V_DemoTab]  
57. SELECT COUNT(*) FROM [dbo].[DemoTab01]  
58. SELECT COUNT(*) FROM [dbo].[DemoTab02]  
59. SELECT COUNT(*) FROM [dbo].[DemoTab03]  

[sql] view plain copy

1. --  現在對視圖查詢  
2. SELECT * FROM [dbo].[V_DemoTab] WHERE id = 0 --不存在  
3. SELECT * FROM [dbo].[V_DemoTab] WHERE id = 3000 --只有該id有記錄  
4. SELECT * FROM [dbo].[V_DemoTab] WHERE id = 300000 --超出check范圍  

上面可以看到，只要查詢在 check 約束范圍內，就會進行查找。第三個查詢不在范圍內，并沒有掃描表行數，只進行了常量掃描，這樣提高了查詢性能。

現在執行視圖更新：

[sql] view plain copy

1. BEGIN TRAN  
2.     UPDATE [dbo].[V_DemoTab] SET insdate = '2005-11-01' WHERE id = 3000  
3.   
4.     select CASE resource_type WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id) ELSE '' END AS [object]  
5.     ,resource_type,resource_description,request_mode,request_status,request_type  
6.     from sys.dm_tran_locks where resource_database_id=DB_ID() and request_session_id=@@SPID    
7. COMMIT TRAN  

id = 3000 在表 [DemoTab01] 中，對視圖的查詢轉化為對表的查詢，但是其他表則都加上了意向排它鎖（IX），這樣在大量并發中勢必影響到其他用戶的訪問。

執行計劃中，不符合條件的都進行了常量掃描，實際并不讀取數據，但也增加了開銷。

關于垂直拆分，參考  SqlServer 垂直分表