第一章 第一节 SQL Server 数据库规划和分区技术.docx
- 文档编号:8382473
- 上传时间:2023-01-30
- 格式:DOCX
- 页数:29
- 大小:30.11KB
第一章 第一节 SQL Server 数据库规划和分区技术.docx
《第一章 第一节 SQL Server 数据库规划和分区技术.docx》由会员分享,可在线阅读,更多相关《第一章 第一节 SQL Server 数据库规划和分区技术.docx(29页珍藏版)》请在冰豆网上搜索。
第一章第一节SQLServer数据库规划和分区技术
1.1数据库
1.1.1创建
CREATEDATABASEname
[[WITH][OWNER[=]user_name]
[TEMPLATE[=]template]
[ENCODING[=]encoding]
[LC_COLLATE[=]lc_collate]
[LC_CTYPE[=]lc_ctype]
[TABLESPACE[=]tablespace_name]
[CONNECTIONLIMIT[=]connlimit]]
1.1.2删除
DROPDATABASEdb_name01
1.1.3修改
1.1.3.1查看当前的存放位置
selectdatabase_id,name,physical_nameASCurrentLocation,state_desc,sizefromsys.master_files
wheredatabase_id=db_id(N'数据库名');
1.1.3.2修改默认的数据库文件存放位置(即时生效)
EXECxp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultData',
@type=REG_SZ,
@value='E:
\MSSQL_MDF\data'
GO
1.1.3.3修改默认的日志文件存放位置(即时生效)
EXECmaster..xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultLog',
@type=REG_SZ,
@value='E:
\MSSQL_MDF\log'
GO
1.1.3.4修改数据库文件自动增长
ALTERDATABASEdb_003MODIFYFILE(NAME=N'db_003a',FILEGROWTH=10%)
ALTERDATABASEdb_016MODIFYFILE(NAME=N'db_016',maxsize=UNLIMITED)
1.1.3.5重命名数据库
EXECsp_dboption'OldDbName','SingleUser','TRUE'
EXECsp_renamedb'OldDbName','NewDbName'
EXECsp_dboption'NewDbName','SingleUser','FALSE'
1.1.3.6向数据库添加数据文件或日志文件
USEmaster
GO
ALTERDATABASEdb_test
ADDFILEGROUPTest1FG1;
GO
ALTERDATABASEdb_test
ADDFILE
(
NAME=test1dat3,
FILENAME='d:
\MSSQLSERVER\DATA\t1dat3.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
),
(
NAME=test1dat4,
FILENAME='d:
\MSSQLSERVER\DATA\t1dat4.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)
TOFILEGROUPTest1FG1;
GO
1.1.3.7数据库规划和分区技术(新建)
第一步:
首先建立我们要使用的数据库,最重要的是建立多个文件组
我们先新建立四个目录,来组成文件组,一个用来存放主文件的目录:
Primary
三个数据文件目录:
FG1、FG2、FG3
建立库:
createdatabaseSalesonprimary
(
name=N'Sales',
filename=N'C:
\data\Primary\Sales.mdf',
size=3MB,
maxsize=100MB,
filegrowth=10%
),
filegroupFG1
(
NAME=N'File1',
FILENAME=N'C:
\data\FG1\File1.ndf',
SIZE=1MB,
MAXSIZE=100MB,
FILEGROWTH=10%
),
FILEGROUPFG2
(
NAME=N'File2',
FILENAME=N'C:
\data\FG2\File2.ndf',
SIZE=1MB,
MAXSIZE=100MB,
FILEGROWTH=10%
),
FILEGROUPFG3
(
NAME=N'File3',
FILENAME=N'C:
\data\FG3\File3.ndf',
SIZE=1MB,
MAXSIZE=100MB,
FILEGROWTH=10%
)
LOGON
(
NAME=N'Sales_Log',
FILENAME=N'C:
\data\Primary\Sales_Log.ldf',
SIZE=1MB,
MAXSIZE=100MB,
FILEGROWTH=10%
)
GO
第二步:
建立分区函数
目的是用来规范不同数据存放到不同目录的标准,简单讲就是如何分区。
USESales
GO
CREATEPARTITIONFUNCTIONpf_OrderDate(datetime)
ASRANGERIGHT
FORVALUES('2003/01/01','2004/01/01')
GO
第三步:
创建分区方案,关联到分区函数
目的就是我们将已经建立好的分区函数组织成一套方案,简单点将就是我们在哪里对数据进行分区。
创建分区方案的Transact-SQL语法:
CREATEPARTITIONSCHEMEpartition_scheme_name
ASPARTITIONpartition_function_name
[ALL]TO({file_group_name|[PRIMARY]}[,...n])
[;]
参数:
partition_scheme_name:
分区方案的名称。
分区方案名称在数据库中必须是唯一的,并且符合标识符规则。
partition_function_name:
使用分区方案的分区函数的名称。
分区函数所创建的分区将映射到在分区方案中指定的文件组。
partition_function_name必须已经存在于数据库中。
单个分区不能同时包含FILESTREAM和非FILESTREAM文件组。
ALL:
指定所有分区都映射到在file_group_name中提供的文件组,或映射到主文件组(如果指定了[PRIMARY]。
如果指定了ALL,则只能指定一个file_group_name。
file_group_name|[PRIMARY][,...n]:
指定用来持有由partition_function_name指定的分区的文件组的名称。
file_group_name必须已经存在于数据库中。
如果指定了[PRIMARY],则分区将存储于主文件组中。
如果指定了ALL,则只能指定一个file_group_name。
分区分配到文件组的顺序是从分区1开始,按文件组在[,...n]中列出的顺序进行分配。
在[,...n]中,可以多次指定同一个file_group_name。
如果n不足以拥有在partition_function_name中指定的分区数,则CREATEPARTITIONSCHEME将失败,并返回错误。
如果partition_function_name生成的分区数少于文件组数,则第一个未分配的文件组将标记为NEXTUSED,并且出现显示命名NEXTUSED文件组的信息。
如果指定了ALL,则单独的file_group_name将为该partition_function_name保持它的NEXTUSED属性。
如果在ALTERPARTITIONFUNCTION语句中创建了一个分区,则NEXTUSED文件组将再接收一个分区。
若要再创建一个未分配的文件组来拥有新的分区,请使用ALTERPARTITIONSCHEME。
在file_group_name[1,...n]中指定主文件组时,必须像在[PRIMARY]中那样分隔PRIMARY,因为它是关键字。
UseSales
go
createpartitionschemeps_OrderDate
aspartitionpf_OrderDate
to(FG2,FG2,FG3)
go
第四步:
创建分区表
创建表并将其绑定到分区方案上。
我们首先建立两个表,一张原始表另一张用来归档数据,保存归档数据。
UseSales
go
createtableOrders
(
OrderIDintidentity(10000,1),
OrderDatedatetimenotnull,
CustomerIDintnotnull,
constraintPK_Ordersprimarykey(OrderID,OrderDate)
)
onps_OrderDate(OrderDate)
go
createtableOrdersHistory
(
OrderIDintidentity(10000,1),
OrderDatedatetimenotnull,
CustomerIDintnotnull,
constraintPK_OrdersHistoryprimarykey(OrderID,OrderDate)
)
onps_OrderDate(OrderDate)
go
首先,因为是用2003年1月1号作为区分点的,我们先向数据表中写入2002年的规范数据
USESales
GO
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2002/6/25',1000)
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2002/8/13',1000)
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2002/8/25',1000)
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2002/9/23',1000)
GO
同样我们写入2003年四条数据
USESales
GO
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2003/6/25',1000)
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2003/8/13',1000)
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2003/8/25',1000)
INSERTINTOdbo.Orders(OrderDate,CustomerID)VALUES('2003/9/23',1000)
GO
我们来查看这些数据是否完整录入:
因为OrdersHistory表我们还没有归档数据,所以为空。
我们来分条件查询下:
我们来查询分区表Orders的第一个分区,代码如下:
select*fromdbo.orderswhere$PARTITION.pf_Orderdate(orderdate)=1
可以看到我们查询出来的数据全部为2002年的,也就是说在第一分区中我们存入的数据都是小于2003年,按照此推断2003年的数据,就应该存在第二分区中:
select*fromdbo.orderswhere$PARTITION.pf_Orderdate(orderdate)=2
结果如我们所料,我们可以按照这个分区进行分组来查看各个分区的数据行多少,代码如下:
select$partition.pf_OrderDate(OrderDate)asPatition,COUNT(*)countRowfromdbo.Orders
groupby$partition.pf_OrderDate(OrderDate)
2、归档数据
假如现在是2003年年初,那么我们就可以把2002您所有的交易记录归档到我们刚才建立的历史订单表HistroryOrder中。
UseSales
go
altertableordersswitchpartition1toordersHistorypartition1
go
现在我们再重新查看这两张表的数据:
select*fromorders
select*fromordershistory
这时候Orders表只剩下2003年的数据,而OdersHistory表中包含了2002年的数据。
当然如果到了2004年年初,我们就可以归档2003年的所有交易数据。
UseSales
go
altertableordersswitchpartition2toordersHistorypartition2
go
这里需要注意的是我们按照区进行数据修改的时候,必须是同一种分区函数下的分区表进行操作,并且分区结构相对应,如果不这样会报错,例如:
3、添加分区
当我们需要新添加分区的时候,我们需要修改分区方案,比如现在我们到了2005年年初,我们需要为2005年的交易记录准备分区,就需要添加分区:
USE[master]
GO
ALTERDATABASE[Sales]ADDFILEGROUP[FG4]
GO
ALTERDATABASE[Sales]ADDFILE(NAME=N'File4',FILENAME=N'C:
\data\FG4\File4.ndf',SIZE=3072KB,FILEGROWTH=1024KB)TOFILEGROUP[FG4]
GO
我们新建立了一个文件组,然我们同样按照上面的方法,进行修改分区函数和方案:
useSales
go
alterpartitionschemeps_OrderDatenextused[FG4]
alterpartitionfunctionpf_OrderDate()splitrange('2005/01/01')
go
我们这里用alterpartitionSchemeps_OrderDateNextUsedFG4用来指定新分区的数据在那个文件,而这里splitrange是创建新分区的关键语法。
4、删除分区
删除分区又称合并分区,简单讲就是两个分区的数据进行合并,比如我们想合并2002年的分区和2003年的分区到一个分区,我们可以用如下的代码:
useSales
go
alterpartitionfunctionpf_OrderDate()mergerange('2003/01/01')
go
也就是将2003年这个分区点去掉,里面分区里面的数据会自动合并到一起。
SELECT*
FROMdbo.OrdersHistory
WHERE$PARTITION.pf_OrderDate(OrderDate)=2
结果一行数据都没返回,事实就这样,因为OrderHistroy表中只存储了2002和2003年的历史数据,在没有合并分区之前,执行上面的代码肯定会查询出2003年的数据,但是合并了分区之后,上面代码实际查询的是第二个分区中2004年的数据。
不过我们改成如下代码:
SELECT*
FROMdbo.OrdersHistory
WHERE$PARTITION.pf_OrderDate(OrderDate)=1
便会查询出8行数据,包括2002年和2003年的数据,因为合并分区后2002年和2003年的数据都成了第1分区的数据了。
1.1.3.8普通表转为分区表
如果数据库已经投入使用一段时间了,但是当时没有创建创建分区表,现在我们需要做的是将普通表转换成分区表,但是并不能影响我们数据库里面的数据,那么我们应该如何做呢?
只需在该表上创建一个聚集索引,并在该聚集索引中使用分区方案即可。
说的很简单,但是在实现实现可就没有那么容易了,因为你的数据库中存在主键,外键等约束关系,那么我们在将普通表转换成分区表时,首先就需要解决这些问题。
我们知道分区表时某个字段为分区条件的,除了这个字段之外的其他字段是不能创建聚集索引的,所以我们将普通表转换成分区表时,必须要删除聚集索引,然后再重新创建一个新的聚集索引,在该聚集索引中使用分区方案。
但是我们需要修改的tabl_name表中的orderId既是主键又是聚集索引,而且还是其它表的外键。
因此,我们只能先删除外键关联,再删除主键,然后重新创建orderId为主键,但是设置为非聚集索引,然后将我们的sellTime字段设置为聚集索引,最后添加上我们的外键约束,至此普通表转换成分区表的工作结束。
一、创建文件组(同上面新建方案)
二、创建文件(同上面新建方案)
三、删除约束,主键,聚集索引并重建
--查看外键约束
usedb_test
EXECsp_helpconstrainttabl_name
--删除外键约束
ALTERtabletabl_nameDROPconstraintFK_tabl_name_t_User
--删掉主键
ALTERTABLEtabl_nameDROPconstraintPK_tabl_name
--创建主键,但不设为聚集索引
ALTERTABLEtabl_nameADDCONSTRAINTPK_tabl_namePRIMARYKEYNONCLUSTERED(
orderIdASC
)
ON[PRIMARY]
--添加删除掉的外键约束(具体自己根据实际情况自己实现)
四、创建一个分区函数
--创建一个分区函数
CREATEPARTITIONFUNCTIONpart_month_func_range_test(datetime)
ASRANGERIGHTFORVALUES(
'2017-7-100:
00:
00',
'2017-8-100:
00:
00',
'2017-8-100:
00:
00',
'2017-9-100:
00:
00',
'2017-10-100:
00:
00',
'2017-11-100:
00:
00',
)
五、创建一个分区方案
CREATEPARTITIONSCHEMEpartsch_CX
ASPARTITIONpart_month_func_range_test
TO(
FC201706,
FC201707,
FC201708,
FC201709,
FC201710,
FC201711,
FC201712
)
注意:
方案中文件组比函数中要多一个
六、按分区方案创建聚集索引
--创建一个新的聚集索引,在该聚集索引中使用分区方案
CREATECLUSTEREDINDEXCtabl_nameONtabl_name(sellTime)
ONpartsch_CX([sellTime])--partsch_CX为分区方案
为表创建了一个使用分区方案的聚集索引之后,该表就变成了一个分区表了。
七、查看分区相关元数据信息
1)查看分区函数,分区方案,边界值点
我们可以通过三个系统视图来查看我们的分区函数,分区方案,边界值点等。
select*fromsys.partition_functions
select*fromsys.partition_range_values
select*fromsys.partition_schemes
转换成功之后,我们可以通过下面代码查看每个分区表中的记录数:
2)统计所有分区表中的记录总数
select$PARTITION.partfun_CX([sellTime])as分区编号,count(orderId)as记录数fromtabl_nameGROUPby$PARTITION.partfun_CX([sellTime])
我们还可以通过下面的代码,查看数据库库中的数据在哪个分区中:
3)查看数据库表中的数据在哪个分区中
select$PARTITION.partfun_CX('2010-10-1')--查询年月日的数据在哪个分区中
select$PARTITION.partfun_CX('2011-01-1')--查询年月日的数据在哪个分区中如果你想比较一下我们使用分区方案之后和之前程序有多少效率提高,我们可以通过下面的语句来看看一下脚本的执行时间就OK了,我经过测试的数据是快了.017秒,一方面由于我们的测试数据量比较小,另一方面我的机器配置还是蛮不错的。
4)查看SQL脚本的执行时间
selectgetDate()
select*fromtabl_name
selectgetDate()
5)查看数据库分区信息
SELECTOBJECT_NAME(p.object_id)ASObjectName,
i.nameASIndexName,
p.index_idASIndexID,
ds.nameASPartitionScheme,
pf.nameASPartitionfunctions,
p.partition_numberASPartitionNumber,
fg.nameASFileGroupName,
prv_left.valueASLowerBoundaryValue,
prv_right.valueASUpperBoundaryValue,
CASEpf.boundary_value_on_right
WHEN1THEN'RIGHT'
ELSE'LEFT'END
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第一章 第一节 SQL Server 数据库规划和分区技术 数据库 规划 分区 技术