分享 SQL Server 2005 分区实现教程

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

技术会议- SQL Server Partitioning  

V2※高捷

本月技术会议专题为数据库分区( SQL Server Partitioning ),主要讲述为什么要分区,在什么情况下需要对数据进行分区,如何进行分区,分区表管理等内容。

一、 摘要

◆    分区基础知识

u  SQL Server2005 分区

u  技术讨论

u  会议决议

二、 分区技术知识

1、  为什么要分区?

(1)       scale up VS scale out

首先需要理解 scale up 和 scale out 的含义, scale up (向上扩展),即后端大型服务器以增加处理器等运算资源进行升级以获得对应用性能的要求,提高硬件来提高数据处理及提高性能的一种方式。而 scale out (向外扩展)主要是指根据需求增加不同的服务器应用,依靠多部服务器协同运算,借负载平衡及容错等功能来提高运算能力及可靠度的方式来提高数据处理和提高性能的一种方式。数据库分区技术的应用则是 scale out 的体现。在此需要注意的是 Scale Out 方案始终面临着数据集中的问题,即拆分过的数据在服务器逻辑体系中仍然是各自相对集中的而非无限随意拆分。如果大量的逻辑放在数据库服务器一端,数据库服务器将会使得系统失去 Scale out 的能力和可能。因此,要保证 Scale out 的能力就必须保证数据库只处理实质性的数据提交和不可避免的数据查询,对于能够避免的数据查询和非实质性数据提交都应该想办法予以避免。而具体的策略和方案相对没有最优的方法。

(2)       避免昂贵的硬件开销

使用分区技术处理大容量数据表,可以让我们不必为了性能而购买昂贵的新服务器或者提高服务器硬件性能来提高性能。

(3)       使数据在一个合适的 Level 上管理

使用分区技术后,我们在对数据库进行管理时,避免了面对 G 级别的数据量维护,只需要面对几百兆或者几十兆的数据,这样使得我们面对数据库管理时,处于一个合适的水平和级别,就能保证数据库的高维护性,节约维护成本、资源

(4)       消除性能瓶颈,最小化维护成本

同样使用分区技术后,客户端对数据库的操作也更容易更迅速,提高了数据库的性能,对数据库维护也相对简单,比如说数据的备份、恢复等等。

2、  什么情况下使用数据库分区

(1)       大数据量表(管理 / 索引创建 / 索引重建 / 备份与恢复 / 扩充 )

面对大表,你首先遇到的一个管理的问题,因为数据量太大,管理非常复杂和麻烦。其次是索引,在大数据量表进行索引的建立、重建都会有可能因为超时而导致失败。再次是表的维护,例如备份、恢复等有可能因为长时间的操作影响其他用户的操作,最后还有一个表的扩充的问题,比如说扩充字段、锁的升级。

在此,需要特别讲述一下有关索引的问题,在 OLTP ( On-Line Transaction Processing )和 DSS ( Decision Support Systems )系统中,索引在上述两种系统中的应用是不同,在 OLTP 系统中,可能我们需要很少的索引,而在 DSS 系统中,我们肯定需要大量的索引,同时我们在 OLTP 系统中对索引的重建和碎片整理需要经常性的去处理,而对于 DSS 系统,对于索引我们原则上只需要一次即可。

在备份与恢复的层面上分析, OLTP 是一个需要经常备份、存放很多关键数据的、需要保持高可用性的比较小型的 VLDB( VERY LARGE DATE BASE ) 的系统,而 DSS 是一个不需要经常备份、数据也非关键数据但需要保高可用的大型 VLDB 系统。

(2)       不同的访问模式

一种访问模式主要指联机事务处理的方式,比如一些插入、更新、删除等内容。另一种访问模式主要是对数据库的查询、分析等操作,这些主要是一些 SELECT 的操作。

3、  分区策略( Partitioning Strategies )

(1)       垂直分区

垂直分区将一个表分为多个表,每个表包含较少的列。垂直分区包括两种类型,即规范化和行拆分:

规范化是标准的数据库进程,它删除表中的多余列,并将这些列放置在通过主键和外键关系链接到主表的辅助表中。

行拆分将原始表垂直分成多个只包含较少列的表。拆分表内的每个逻辑行都与其他表内由 UNIQUE KEY 列(在所有已分区表中都相同)标识的相同逻辑行相匹配。例如,联接每个拆分表内具有 ID 712 的行将重新创建原始行。

应该慎用垂直分区,因为分析多个分区中的数据时需要联接表的查询。如果分区过大,垂直分区还可能会影响性能。

(2)       水平分区

水平分区将表分为多个表。这样,每个表包含的列数相同,但是行更少。例如,可以将一个包含十亿行的表水平分区成 12 个表,每个小表表示特定年份内一个月的数据。任何需要特定月的数据的查询都只引用相应的表。

具体如何将表进行水平分区取决于如何分析数据。您应将表进行分区,以便查询引用的表尽可能少。否则,查询时需要使用过多的 UNION 查询来逻辑合并表,这会影响查询性能。

4、  垂直分区案例

某个表存在记录行为 1,600,000 rows 。此表有 47 个列, 4600Bytes/Row ,由于 SQL Server 本身系统限制一条记录不能超过 8060Bytes ,所以我们一行记录需要一页,每条记录浪费 3460Bytes ,这样计算后整个表空间占用约 12G 。表内容如下所示:

表列数

( columns )

记录行

( Rows )

每行大小

( Bytes/Row )

需要页数

( Pages )

表大小

 

表 A

47

1,600,000

4600

1,600,000

12G

先对其进行垂直分区,假设此表为一个主键,我们将其分为三个表,分区标准如下表所示:

表列数

( columns )

记录行

( Rows )

每行大小

( Bytes/Row )

需要页数

( Pages )

表大小

 

表 1

14

1,600,000

1000

200,000

1.6G

表 2

18

1,600,000

1600

320,000

2.5G

表 3

17

1,600,000

2000

400,000

3.2G

合计:

7G

结果:节省了 5G 的空间,同时提高了性能。

另外在进行垂直分区的时候还需要注意一下几点:

1 优化行的尺寸

   因为 SQL Server 在对数据进行检索的时候,是通过页来取得的,这就要求我们尽量让更多的记录在一个页上,才能保证更多的行在缓存中,这样就保证了数据库在进行 I/O 操作时,提高了性能。

    另外,从锁的方面分析,我们将列分区后,在进行操作时,列锁定的是没有分区表的几个字段,而不是所有的字段,这就保证了另外两个表不被锁限制,也就降低了行锁对数据库并发用户的影响。

2 使用方法

   首先考虑将经常关联的逻辑列进行分组,也就说将同一类属性,经常放在一起进行查询划分为一个分组放到一个表中,从而减少表与表之间的交流和关联;其次要考虑到那些列是只读的,那些是 OLTP 环境下的列,这样可以避免数据检索时的重复性,提高性能。

5、  水平分区

水平分区创造出了更多的可管理的块,同时减少的 DBA 对表的维护的影响,例如表索引管理、备份恢复等。另外,他减少的锁对数据操作的影响,使得我们在对部分数据进行操作时,锁仅仅在某一个部分进行,减少了锁操作对数据库资源的占用,从而提高了数据处理的效率。

三、 SQL Server2005 分区实现

1、  SQL Server2005 分区表和索引概念

物理分区,具有标准表和索引相关的所有的属性和功能

大型表或索引经过分区后更容易管理,因为分区后可以更快速有效地管理和访问数据子集,同时维护数据集合的完整性。通过分区,从 OLTP 向 OLAP 系统加载数据操作只需要几秒钟,同时由于对数据子集执行的维护操作只是针对所需数据而不是整个表,因此效率也得到了很大的提高。

已分区表和已分区索引的数据划分为分布于一个数据库中多个文件组的单元,数据俺水平方式分区,因此多组行映射到单个的分区中。单个索引或表的所有分区都必须位于同一个数据库中。

在 SQL Server2005 中,数据库中所有表和索引都视为已分区表和索引,即使这些表和索引只包含一个分区。

2、  分区设计

(1)       创建分区函数;

分区函数:分区函数定义如何根据某些列的值将表或索引的行映射到一组分区。(一张表最多有 1000 个分区)

(2)       创建分区方案;

分区方案:分区方案将分区函数指定的每个分区映射到文件组。(一个分区方案只能使用一个分区函数,但是,一个分区函数可以参与到多个分区方案)

(3)       在创建表和索引的时候,指定表或索引的分区方案。

3、  分区实现

(1)       创建分区函数

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

例句:

CREATE PARTITION FUNCTION myRangePF1( DATETIME ) AS RANGE RIGHT FOR VALUES ( '2007-12-21' , '2007-12-22' , '2007-12-23' , '2007-12-24' , '2007-12-25' , '2007-12-26' )

说明:指定当间隔值由 数据库引擎 按升序从左到右排序时, boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧(左侧还是右侧)。如果未指定,则默认值为 LEFT 。

(2)       创建分区方案

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

            例句:

            CREATE PARTITION SCHEME mySchemePS1 AS PARTITION [myRangePF1] TO ( [PRIMARY], [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7])

说明:如果创建的文件组少于要分区的指定的文件组,创建语句将执行失败,如果创建的文件组大于要分区指定的文件组,那么剩余的文件组将会做为下一次分区指定时使用,所以多出来的文件组也不会存在数据。

(3)       增加分区

ALTER PARTITION SCHEME partition_scheme_name

NEXT USED [ filegroup_name ] [ ; ]

例句:

ALTER PARTITION SCHEME MyRangePS1

NEXT USED test5fg;

说明:修改分区方案后,需要修改分区函数。

ALTER PARTITION FUNCTION partition_function_name()

{  SPLIT RANGE ( boundary_value )

  | MERGE RANGE ( boundary_value ) } [ ; ]

例句:

ALTER PARTITION FUNCTION myRangePF1 ()

SPLIP RANGE ( 100);

(4)       合并分区

ALTER PARTITION FUNCTION myRangePF1 ()

MERGE RANGE (100);

(5)       创建分区表

CREATE TABLE PatitionTable( col1 int , col2 char ( 10))

ON MyRangePS1( col1);

(6)       创建分区索引

CREATE INDEX ix_Col2 ON PartitionTable( col2)

ON myRangePS1( col1);

CREATE INDEX ix_Col2 ON PartitionTable( col2)

说明:如果是根据分区依据列来创建索引,则不需要增加 On 后的内容。

(7)       分区信息查看

※ 使用 $PARTITION 函数

※ 访问已分区表的分区子集中的所有行。

SELECT $PARTITION . myRangePF1( col1), count (*)

FROM PartitionTable

GROUP BY $Partition . myRangePF1( col1)

※确定包含特定分区键值的行位于哪个分区中?

SELECT $PARTITION . myRangePF1( col1)

4、  分区 DEMO

示例数据为 SQL Server2005 自带的 AdventureWorks 数据库,在这个数据库中有两个表 TransactionHistory (交易历史信息表)和 TransactionHistoryArchive (交易历史归档表), TransactionHistory 主要维护年度最新事务信息,而 TransactionHistoryArchive 保存历史的事务信息。

TransactionHistory 设定为 12 个分区,存放了 03 年 9 月份到 04 年八月份的数据, TransactionHistoryArchive 分为 2 个区,存放了 03 年 9 月份之前和之后的数据,分区字段为 TransactionDate 。

每个月开始, TransactionHistory 当前最早的一个月的数据将被切换到 TransactionHistoryArchive 表中。需要注意的是,在这个操作中,如果不使用分区,而是使用导入导出或 INSERT 等语句进行数据的切换,是非常耗费资源和时间的,而采用分区,则避免了这个问题,因为在真正的操作中, SQL Server 并不是真正的将数据进行了迁移,而只是将源数据进行了切换,就是说数据的指针或者说数据资源表位置进行了修改,所以表分区之间的数据切换是瞬间的事情。

实现脚本:

ALTER TABLE [Production]. [TransactionHistory]

SWITCH PARTITION 1

TO [Production]. [TransactionHistoryArchive] PARTITION 2;

实现:见 SQL 脚本。

5、  条码物流系统分区应用介绍

目前部门所规划的 5 大产品之一条码物流系统在开发中使用到了数据库分区技术来改善数据库性能,目前应用只是轮胎状态表。

因为轮胎状态表贯穿于整个条码物流系统的各个工序和环节,也存放着大量的生产、质检等数据,数据量非常巨大,在没有进行分区时整个表的检索经常出现检索超时和客户端死机的情况。

轮胎状态表的分区主要依据于轮胎胎号,将 08 年之前的信息,按年分区,而 08 年之后的数据按照每 10 周进行分区,目前应用良好,对整个现场系统的应用和 WEB 系统的查询性能都有了很大的改善。

作者: 黄聪
出处: http://www.cnblogs.com/huangcong/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

posted on  2010-05-07 01:06  黄聪 阅读( 604) 评论( 0编辑  收藏
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
18天前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
在Web开发中,安全至关重要,尤其要警惕SQL注入和XSS攻击。SQL注入通过在数据库查询中插入恶意代码来窃取或篡改数据,而XSS攻击则通过注入恶意脚本来窃取用户敏感信息。本文将带你深入了解这两种威胁,并提供Python实战技巧,包括使用参数化查询和ORM框架防御SQL注入,以及利用模板引擎自动转义和内容安全策略(CSP)防范XSS攻击。通过掌握这些方法,你将能够更加自信地应对Web安全挑战,确保应用程序的安全性。
52 3
|
13天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
69 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
51 6
|
3月前
|
SQL 关系型数据库 数据库
关系型数据库SQLserver教程
【7月更文挑战第26天】
57 6
|
3月前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
【7月更文挑战第26天】在 Web 开发中, SQL 注入与 XSS 攻击常令人担忧, 但掌握正确防御策略可化解风险. 对抗 SQL 注入的核心是避免直接拼接用户输入至 SQL 语句. 使用 Python 的参数化查询 (如 sqlite3 库) 和 ORM 框架 (如 Django, SQLAlchemy) 可有效防范. 防范 XSS 攻击需严格过滤及转义用户输入. 利用 Django 模板引擎自动转义功能, 或手动转义及设置内容安全策略 (CSP) 来增强防护. 掌握这些技巧, 让你在 Python Web 开发中更加安心. 安全是个持续学习的过程, 不断提升才能有效保护应用.
47 1
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
253 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
167 1
|
3月前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之使用sql查询一个表的分区数据时遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。