SqlServer 分区视图实现分表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SqlServer 分区视图实现分表,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3591字,纯文字阅读大概需要6分钟。
内容图文
![SqlServer 分区视图实现分表](/upload/InfoBanner/zyjiaocheng/450/74f5e7b9483240e199171f4c90eb14f6.jpg)
我们都知道在数据库数据量较多的时候,可数据进行水平扩展,如分库,分区,分表(也叫分区)等。对于分表的一个方案,就是使用分区视图实现。
分区视图允许将大型表中的数据拆分成较小的成员表。根据其中一列中的数据值范围,在各个成员表之间对数据进行分区。每个成员表的数据范围都在为分区依据列指定的 CHECK 约束中定义。然后定义一个视图,以使用 UNION ALL 将选定的所有成员表组合成单个结果集。引用该视图的 SELECT 语句为分区依据列指定搜索条件后,查询优化器将使用 CHECK 约束定义确定哪个成员表包含相应行。
CHECK 约束 在查询方面提供更好的优化特性,看一位大侠的实验 SQL Server中使用Check约束提升性能 ,当前在其他操作方面就不太好了,以下测试。
当前测试为本地分区视图:
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]
![SqlServer 分区视图实现分表 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424100746358.jpg)
-- 现在对视图查询 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
![SqlServer 分区视图实现分表 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424100746720.jpg)
![SqlServer 分区视图实现分表 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424100746897.jpg)
id = 3000 在表 [DemoTab01] 中,对视图的查询转化为对表的查询,但是其他表则都加上了意向排它锁(IX),这样在大量并发中势必影响到其他用户的访问。
执行计划中,不符合条件的都进行了常量扫描,实际并不读取数据,但也增加了开销。
参考:
实施联合数据库服务器
解析分布式分区视图
使用分区视图
博客:SQL Server中使用Check约束提升性能
博客:SQL Server横向扩展:设计,实现与维护(3)- 分布式分区视图的实现
SqlServer 分区视图实现分表
标签:
本文系统来源:http://blog.csdn.net/kk185800961/article/details/46482769
内容总结
以上是互联网集市为您收集整理的SqlServer 分区视图实现分表全部内容,希望文章能够帮你解决SqlServer 分区视图实现分表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。