做电影解析网站,深圳罗湖建网站,自己做的简单网站下载,设计师常用的图片网站1. 概要说明
SQL SERVER的表分区功能是为了将一个大表#xff08;表中含有非常多条数据#xff09;的数据根据某条件#xff08;仅限该表的主键#xff09;拆分成多个文件存放#xff0c;以提高查询数据时的效率。创建表分区的主要步骤是
1、确定需要以哪一个字段作为分…1. 概要说明
SQL SERVER的表分区功能是为了将一个大表表中含有非常多条数据的数据根据某条件仅限该表的主键拆分成多个文件存放以提高查询数据时的效率。创建表分区的主要步骤是
1、确定需要以哪一个字段作为分区条件
2、拆分成多少个文件保存该表
3、分区函数拆分条件
4、分区方案按拆分函数拆分后需要对应到哪些文件组中去。 不是企业版的sql server不支持分区
参考SQL SERVER 表分区实施步骤_sqlserver表分区步骤_Henry_Wu001的博客-CSDN博客
sql server 分区表 性能 sqlserver分区表实战_mob6454cc77db30的技术博客_51CTO博客
0.1SQL Server分区介绍 在SQL Server中数据库的所有表和索引都视为已分区表和索引默认这些表和索引值包含一个分区也就是说表或索引至少包含一个分区。SQL Server中数据是按水平方式分区是多行数据映射到单个分区。已经分区的表或者索引在执行查询或者更新时将被看作为单个逻辑实体简单说来利用分区将一个表数据分多个表来存储对于大数据量的表将表分成多块查询若只查询某个分区数据将降低消耗提高效率。需要注意的是单个索引或者表的分区必须位于一个数据库中。在使用大量数据管理时SQL Server使用分区可以快速访问数据子集减少io提高效率。
同时不同分区可以存放在不同文件组里文件组若能存放在不同逻辑磁盘上则可以实现io的并发使用以提高效率 0.2SQL Server分区创建概述 创建分区函数确定分区方式和界点 创建分区架构将分区函数指定的分区映射到文件组 新建分区表 索引分区知识详解 0.3SQL Server分区管理概述 拆分分区split 合并分区merge 切换分区switch $PARTION 【1】创建表分区 未分区的表相当于只有一个分区只能存储在一个FileGroup中对表进行分区后每一个分区都存储在一个FileGroup或分布式存储在不同的FileGroup中。对表进行分区的过程实际上是将逻辑上完整的一个表按照特定的字段拆分成多个分区分散到相同或不同的FileGroup中每一个部分叫做表的一个分区Partition一个分区实际上是一个独立的内部的物理表。也就是说分区表在逻辑上是一个表而在物理上是多个完全独立的表。
分区Partition的特性是
每一个Partition在FileGroup中都独立存储分区之间是相互独立的 每一个parititon都属于唯一的表对象 每一个Partition 都有唯一的ID 每一个Partition都有一个编号Partition Number同一个表的分区编号是唯一的从1开始递增 Step0准备工作构建文件组和文件 登录后复制 --添加文件组 alter database testSplit add filegroup db_fg1
--添加文件到文件组 alter database testSplit add file (nameNById1,filenameNJ:\Work\数据库\data\ById1.ndf,size5Mb,filegrowth5mb) to filegroup db_fg1
一新建分区表分为三步
Step1 创建分区函数 要先创建函数
分区函数的作用是提供分区字段的类型和分区的边界值进而决定分区的数量
CREATE PARTITION FUNCTION [pf_int](int) AS RANGE LEFT FOR VALUES (10, 20)
分区函数pf_int 的含义是按照int类型分区分区的边界值是1020left表示边界值属于左边界。两个边界值能够分成三个分区别是(-infinite10],1020],(20,infinite)。
Step2创建分区架构Scheme 再创建架构、应用函数
分区架构的作用是为Parition分配FileGroup在逻辑上Partition Scheme和FileGroup是等价的都是数据存储的逻辑空间只不过Partition Scheme指定的是多个FileGroup。
CREATE PARTITION SCHEME [ps_int] AS PARTITION [pf_int] TO ([PRIMARY], [db_fg1], [db_fg1])
不管是在不同的FileGroup中还是在相同的FileGroup中分区都是独立存储的。
分区scheme的所有分区都存储到相同的文件组中
CREATE PARTITION SCHEME [ps_int] AS PARTITION [pf_int] ALL TO ([PRIMARY])
Step3新建分区表 新建分区表实际上是在创建Table时使用on子句指定数据存储的逻辑位置是分区架构Partition Scheme
create table dbo.dt_test ( ID int, code int ) on [ps_int] (id)
查看分区编号Partition Number 分区编号Partition Number 从1开始从最左边的分区向右依次递增1边界值最小的分区编号是1
例如对于以下分区函数
CREATE PARTITION FUNCTION pf_int_Left (int) AS RANGE LEFT FOR VALUES (10,20);
分区的边界值Boundary Value是1020 边界值属于左边界Range Left该分区函数 pf_int_Left 划分了三个分区Partition范围区间是(-infinite,10], (10,20], (20,infinite)小括号表示不包括边界值中括号表示包括边界值系统分配的分区编号分别是123。用户可以通过使用$Partition函数 查看分区编号调用语法格式是 $Partition.Partition_Function(Partition_Column_Value)
例如通过$Partition函数 查看分区列值为21时该行数据所在的分区编号
select $Partition.pf_int_left(21)
由于分区列值是21 属于范围(20,infinite)因此分区编号是3。
【2】对现有表分区 在SQL Server中普通表可以转化为分区表而分区表不能转化为普通表普通表转化成分区表的过程是不可逆的将普通表转化为分区表的方法是
在分区架构Partition Scheme上创建聚集索引就是说将聚集索引分区。
数据库中已有分区函数partition function 和分区架构Partition scheme
-- create parition function CREATE PARTITION FUNCTION pf_int_Left (int) AS RANGE LEFT FOR VALUES (10,20);
--determine partition number select $Partition.pf_int_left(21)
CREATE PARTITION SCHEME PS_int_Left AS PARTITION pf_int_Left TO ([primary], [primary], [primary]); 如果在普通表上存在聚集索引并且聚集索引列是分区列那么重建聚集索引就能使表转化成分区表。聚集索引的创建有两种方式使用clustered 约束primary key 或 unique约束创建使用 create clustered index 创建。 【2.1】在分区架构Partition Scheme上创建聚集索引 如果聚集索引是使用 create clustered index 创建的并且聚集索引列就是分区列使普通表转换成分区表的方法是删除所有的 nonclustered index在partition scheme上重建clustered index
1表dbo.dt_partition的聚集索引是使用 create clustered index 创建的
create table dbo.dt_partition ( ID int, Code int )
create clustered index cix_dt_partition_ID on dbo.dt_partition(ID)
2从系统表Partition中查看该表的分区只有一个
select * from sys.partitions p where p.object_idobject_id(Ndbo.dt_partition,NU)
3使用partition scheme重建表的聚集索引
create clustered index cix_dt_partition_ID on dbo.dt_partition(ID) with(drop_existingon) on PS_int_Left(ID)
4重建聚集索引之后表的分区有三个
select * from sys.partitions p where p.object_idobject_id(Ndbo.dt_partition,NU) 【2.4】普通表》分区表不可逆 普通表转化成分区表的过程是不可逆的普通表能够转化成分区表而分区表不能转化成普通表。
普通表存储的Location是FileGroup分区表存储的Location是Partition Scheme在SQL Server中存储表数据的Location叫做Data Space。
通过在Partition Scheme上创建Clustered Index 能够将已经存在的普通表转化成partition table但是将Clustered index删除表仍然是分区表转化过程将普通表转换成分区表是不可逆的
一个Partition Table 是不能转化成普通表的即使通过合并分区使Partiton Table 只存在一个Partition这个表的仍然是Partition Table这个Table的Data Space 是Partition Scheme而不会转化成File Group。
从 sys.data_spaces 中查看Data Space 共有两种类型分别是FG 和 PS。
FG是File Group意味着数据表的数据存储在File Group分配的存储空间一个Table 只能存在于一个FileGroup中。PS 是Partition Scheme意味着将数据分布式存储在不同的File Groups中存储数据的File Group是根据Partition column值的范围来分配的。对于分区表SQL Server从指定的File Group分配存储空间虽然一个Table只能指定一个Partition Scheme但是其数据却分布在多个File Groups中这些File Groups由Partition Scheme指定可以相同也可以不同。 【3】分区切换 在SQL Server中对超级大表做数据归档使用select和delete命令是十分耗费CPU时间和Disk空间的
SQL Server必须记录相应数量的事务日志而使用switch操作归档分区表的老数据十分高效switch操作不会移动数据只是做元数据的置换
因此执行分区切换操作的时间是非常短暂的几乎是瞬间完成但是在做分区切换时源表和靶表必须满足一定的条件
表的结构相同列的数据类型可空性nullability相同 索引结构必须相同索引键的结构聚集性唯一性列的可空性必须相同 主键约束如果源表存在主键约束那么靶表必须创建等价的主键约束 唯一约束唯一约束可以使用唯一索引来实现 索引键的结构索引键的顺序包含列唯一性聚集性都必须相同 存储的数据空间data space相同源表和靶表必须创建在相同的FileGroup或Partition Scheme上 分区切换是将源表中的一个分区切换到靶表target_table中靶表可以是分区表也可以不是分区表switch操作的语法是
ALTER TABLE schema_name . table_name SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_partition_number_expression ]
【3.2】源表和目标表的结构必须相同 1数据列的可空性必须相同nullability
2数据列的数据类型必须相同
1数据列的可空性必须相同nullability 【3.5】交换分区总结 在执行分区操作时要求源表和靶表必须满足
表的结构相同列的数据类型可空性nullability相同 索引结构必须相同索引键的结构聚集性唯一性列的可空性必须相同 主键约束如果源表存在主键约束那么靶表必须创建等价的主键约束 唯一约束唯一约束可以使用唯一索引来实现 索引键的结构索引键的顺序包含列唯一性聚集性都必须相同 存储的数据空间data space相同源表和靶表必须创建在相同的FileGroup或Partition Scheme上
1时间分区 代码现有表转成分区表
-- 创建测试数据测试表 part_test use test1; if object_id(part_test ) is not null drop table part_test; ;with t1 as ( select 1 as id,1 as num ,cast(2021-01-01 00:01:01 as datetime) as day_info union all select id1 ,num1 ,dateadd(day,1,day_info) from t1 where id1000000 ) select * into part_test from t1 option(maxrecursion 0)
-- 分区函数 CREATE PARTITION FUNCTION [pf_datetime](datetime) AS RANGE LEFT for values( 2021-01-01 , 2022-01-01 , 2023-01-01 , 2024-01-01 , 2025-01-01 , 2026-01-01 , 2027-01-01 , 2028-01-01 , 2029-01-01 , 2030-01-01 , 2031-01-01 , 2032-01-01 , 2033-01-01 , 2034-01-01 , 2035-01-01 , 2036-01-01 , 2037-01-01 , 2038-01-01 , 2039-01-01 , 2040-01-01 , 2041-01-01 , 2042-01-01 , 2043-01-01 , 2044-01-01 , 2045-01-01 , 2046-01-01 , 2047-01-01 , 2048-01-01 );
-- 分区架构 CREATE PARTITION SCHEME [ps_datetime] AS PARTITION [pf_datetime] ALL TO ([PRIMARY]) -- 创建聚集索引和耳机索引 create clustered index PIX_id on part_test(id) create index ix_dayinfo on part_test(day_info)
-- 查看是否还有二级索引 -- sp_help part_test
-- 删掉二级索引重建聚集索引并应用分区架构 drop index ix_dayinfo on part_test
-- 重建聚集索引》现有表改成分区表分区列必须是在主键内比如这里的 day_info 就必须在主键内 create clustered index PIX_id on dbo.part_test(ID,day_info) with(drop_existingon) on [ps_datetime](day_info)
--创建索引对齐分区索引 create index id_p_num on part_test(num) on [ps_datetime](day_info) create index id_p_dayinfo on part_test(day_info) on [ps_datetime](day_info)
select * from part_test where day_info2021-01-11 00:01:01.000
-- 拆分分区最末尾 -- 在分区函数中新增一个边界值即可将一个分区变为2个。一般边界值默认是 left 放到最前或者最后来拆分就是新增分区 alter partition function pf_datetime() split range(2049-01-01) --将第二个分区拆为2个分区
-- 归档到历史表 alter table bigorder switch partition 1 to 同表结构、默认值、null约束一致的表
