VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > SQL教程 >
  • SQL Server高级进阶之分区表创建

一、分区表概念

1.1、什么是分区表?

分区表是在SQL Server 2005之后的版本引入的特性,这个特性允许把逻辑上的一个表在物理上分为很多部分。换句话说,分区表从物理上看是将一个大表分成几个小表,但是从逻辑上看,还是一个大表。

1.2、分区与分表的区别

分区:就是把一张表的数据分成N个区块,从逻辑上看只是一张表,但底层是由N个物理区块组成的。

分表:就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。

1.3、水平分表与垂直分表的区别

水平分表:将一张表中的数据分成多个表且表结构不变。

垂直分表:将一张表按照字段分成不同表且表结构发生改变。

二、分区表优点

2.1、使用多个文件分布数据到多个硬盘中,可以极大地提高IO性能。

2.2、多个文件对于数据略多的数据库来说,备份和恢复都会轻松很多。

三、分区表场景

3.1、数据库中某个表的数据量很大,在查询数据时会明显感觉到速度很慢,这种情况可以考虑分区表。

3.2、数据是分段的,如以年份为分隔的数据,对于当前的数据经常进行增删改查操作,而对于往年的数据几乎不做操作或只做查询操作,这种情况可以考虑分区表。

3.3、对数据的操作如果只涉及一部分数据而非全部数据,这种情况可以考虑分区表。

3.4、如果一张表的数据经常进行增删改查操作,而不管年份之类的因素,这种情况最好不要考虑分区表。

四、分区表创建

4.1、创建步骤

创建分表区的步骤分为5步:

1)创建数据库文件组

2)创建数据库文件

注:应将文件组和文件存放于不同的硬盘甚至不同的服务器中,因为数据的读取瓶颈很大程度在于硬盘的读写速度,多个硬盘存储一个表可以实现负载均衡。

3)创建分区函数

注:声明分区的标准。

4)创建分区方案

注:即哪些区域使用哪个分区函数,形成完整的分区方案。

5)创建分区表

4.2、创建实操

背景:现以表Sales.SalesOrderHeader作为示例,此表有2011-2014年的数据。

分区:按年进行分区,此表有2011201220132014等4个边界值,需要5个分区,分别是2011前、2011、2012、2013、2013后。

描述:分区表的数据存放于分区文件(数据库ndf文件)中;分区文件存放于分区文件组中;分区文件组存放于多个硬盘中。

1)对着数据库点击"右键"->"属性"。

2)点击"文件组"->"添加文件组"->分别建立FG2011BF、FG2011、FG2012、FG2013、FG2013AF等5个文件组->"确定"。

3)点击"文件"->"添加"->分别建立FL2011BF、FL2011、FL2012、FL2013、FL2013AF等5个文件->选择对应的文件组及存放路径->"确定"。

4)对着表Sales.SalesOrderHeader点击"右键"->"存储"->"创建分区"->"下一步"。

5)分区列选择"OrderDate"->勾选"将存储区中的所有非唯一索引和唯一索引与索引分区列对齐"->点击"下一步"。

6)起个分区函数名如"SalesOrderHeader_OrderDate"->点击"下一步"。

7)起个分区方案名如"SalesOrderHeader_OrderDate"->点击"下一步"。

8)映射分区范围选择"左边界"->点击"设置边界"->开始日期:"2011/01/01"、结束日期:"2014/01/01"、日期范围:"年"->点击"确定"。

9)依边界值选择相对应的文件组->点击"预计存储空间"可查看行计数及空间信息->点击"下一步"。

10)选择"立即运行"->点击"下一步"->点击"完成"。

五、分区表检查

5.1、检查分区函数与分区方案

5.2、检查分区文件

六、分区表查询

6.1、查看分区及行计数

复制代码
SELECT CONVERT(VARCHAR(50),A.NAME) Partition_Scheme,D.Partition_Number,CONVERT(VARCHAR(10),E.NAME) FileGroup,
       CONVERT(VARCHAR(19),ISNULL(G.VALUE,''),120) Range_Boundary,STR(D.ROWS,9) Rows
FROM SYS.PARTITION_SCHEMES A INNER JOIN SYS.DESTINATION_DATA_SPACES B ON A.DATA_SPACE_ID=B.PARTITION_SCHEME_ID
    INNER JOIN SYS.INDEXES C ON A.DATA_SPACE_ID=C.DATA_SPACE_ID
    INNER JOIN SYS.PARTITIONS D ON B.DESTINATION_ID=D.PARTITION_NUMBER AND C.OBJECT_ID=D.OBJECT_ID AND C.INDEX_ID=D.INDEX_ID
    INNER JOIN SYS.DATA_SPACES E ON B.DATA_SPACE_ID=E.DATA_SPACE_ID
    INNER JOIN SYS.PARTITION_FUNCTIONS F ON A.FUNCTION_ID=F.FUNCTION_ID
    LEFT JOIN SYS.PARTITION_RANGE_VALUES G ON F.FUNCTION_ID=G.FUNCTION_ID AND D.PARTITION_NUMBER-F.BOUNDARY_VALUE_ON_RIGHT=G.BOUNDARY_ID
WHERE C.OBJECT_ID=OBJECT_ID('SALES.SALESORDERHEADER')    --分区表名
    AND C.INDEX_ID IN (0,1)
ORDER BY Partition_Scheme,D.Partition_Number
复制代码

6.2、查看文件及文件组

SELECT A.[NAME],A.PHYSICAL_NAME,A.[SIZE],A.GROWTH,B.[NAME] [FILEGROUP],B.IS_DEFAULT
FROM SYS.DATABASE_FILES A INNER JOIN SYS.FILEGROUPS B ON A.DATA_SPACE_ID=B.DATA_SPACE_ID


出处:https://www.cnblogs.com/atomy/p/15337939.html


相关教程