SqlServer 分区视图实现水平分表

原文：https://blog.csdn.net/kk185800961/article/details/46482769

 

当前测试为本地分区视图：

USE [DemoDB]
GO
 
--    创建结构相同的表，[id] 不要设置自增（IDENTITY(1,1) ），因为插入表前就需要知道id值
--    DROP TABLE [DemoTab01],[DemoTab02],[DemoTab03]
CREATE TABLE [dbo].[DemoTab01](
    [id] [int] NOT NULL,
    [insdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DemoTab02](
    [id] [int] NOT NULL,
    [insdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DemoTab03](
    [id] [int] NOT NULL,
    [insdate] [datetime] NULL
) ON [PRIMARY]
GO
 
--    约束每个表的范围
ALTER TABLE [dbo].[DemoTab01] WITH CHECK ADD CONSTRAINT [CK_DemoTab01_id] CHECK ([id] BETWEEN 0 AND 99999 )
GO
ALTER TABLE [dbo].[DemoTab02] WITH CHECK ADD CONSTRAINT [CK_DemoTab02_id] CHECK ([id] BETWEEN 100000 AND 199999 )
GO
ALTER TABLE [dbo].[DemoTab03] WITH CHECK ADD CONSTRAINT [CK_DemoTab03_id] CHECK ([id] BETWEEN 200000 AND 299999 )
GO
 
--    既然是按id划分，把id作为聚集索引更容易定位查找
ALTER TABLE [dbo].[DemoTab01] ADD CONSTRAINT [PK_DemoTab01_id] PRIMARY KEY CLUSTERED ([id] ASC)
GO
ALTER TABLE [dbo].[DemoTab02] ADD CONSTRAINT [PK_DemoTab02_id] PRIMARY KEY CLUSTERED ([id] ASC)
GO
ALTER TABLE [dbo].[DemoTab03] ADD CONSTRAINT [PK_DemoTab03_id] PRIMARY KEY CLUSTERED ([id] ASC)
GO
 
 
--    每个字段名称列出，避免用星号，否则升级增删字段不同时会出错
--    DROP VIEW [dbo].[V_DemoTab]
CREATE VIEW [dbo].[V_DemoTab]
AS
SELECT [id],[insdate] FROM [dbo].[DemoTab01]
UNION ALL
SELECT [id],[insdate] FROM [dbo].[DemoTab02]
UNION ALL
SELECT [id],[insdate] FROM [dbo].[DemoTab03]
GO
 
--    121317行数据
INSERT INTO [V_DemoTab]([id],[insdate])
SELECT SalesOrderDetailID,ModifiedDate FROM AdventureWorks2012.Sales.SalesOrderDetail
GO
 
 
SELECT COUNT(*) FROM [dbo].[V_DemoTab]
SELECT COUNT(*) FROM [dbo].[DemoTab01]
SELECT COUNT(*) FROM [dbo].[DemoTab02]
SELECT COUNT(*) FROM [dbo].[DemoTab03]

 

 

--    现在对视图查询
SELECT * FROM [dbo].[V_DemoTab] WHERE id = 0 --不存在
SELECT * FROM [dbo].[V_DemoTab] WHERE id = 3000 --只有该id有记录
SELECT * FROM [dbo].[V_DemoTab] WHERE id = 300000 --超出check范围

 

 上面可以看到，只要查询在 check 约束范围内，就会进行查找。第三个查询不在范围内，并没有扫描表行数，只进行了常量扫描，这样提高了查询性能。

现在执行视图更新：

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

 

 

 

 

d = 3000 在表 [DemoTab01] 中，对视图的查询转化为对表的查询，但是其他表则都加上了意向排它锁（IX），这样在大量并发中势必影响到其他用户的访问。

执行计划中，不符合条件的都进行了常量扫描，实际并不读取数据，但也增加了开销。

 

————————————————

版权声明：本文为CSDN博主「薛定谔的DBA」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/kk185800961/article/details/46482769