SQL 创建分区表
程序员文章站
2023-12-18 20:50:40
(以项目中实际使用的GNSS库为例) 背景:数据量巨大,定时创建月表存放数据,月表中数据存放在不同的文件组中来提高查询效率 一、创建数据库,添加文件组 除了逻辑文件和物理文件的分离之外,SQL Server使用文件组还有一个优势,那就是分散IO负载,其实现的原理是: 对于单分区表,数据只能存到一个文 ......
(以项目中实际使用的gnss库为例)
背景:数据量巨大,定时创建月表存放数据,月表中数据存放在不同的文件组中来提高查询效率
一、创建数据库,添加文件组
除了逻辑文件和物理文件的分离之外,sql server使用文件组还有一个优势,那就是分散io负载,其实现的原理是:
- 对于单分区表,数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上,那么sql server能同时从不同的物理硬盘上读写数据,把io负载分散到不同的硬盘上。
- 对于多分区表,每个分区使用一个文件组,把不同的数据子集存储在不同的磁盘上,sql server在读写某一个分组的数据时,能够调用不同的硬盘io。
这两种方式,其本质上,都是使每个硬盘均摊系统负载,提高io性能。
create database [gnss] containment = none on primary ( name = n'gnss', filename = n'd:\databases\gnss\gnss.mdf' , size = 6144kb , maxsize = unlimited, filegrowth = 1024kb ), filegroup [gnssfg0] default ( name = n'gnssfile0', filename = n'd:\databases\gnss\gnssfile0.ndf' , size = 287744kb , maxsize = unlimited, filegrowth = 1024kb ), filegroup [gnssfg1] ( name = n'gnssfile1', filename = n'd:\databases\gnss\gnssfile1.ndf' , size = 778240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg10] ( name = n'gnssfile10', filename = n'd:\databases\gnss\gnssfile10.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg11] ( name = n'gnssfile11', filename = n'd:\databases\gnss\gnssfile11.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg12] ( name = n'gnssfile12', filename = n'd:\databases\gnss\gnssfile12.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg13] ( name = n'gnssfile13', filename = n'd:\databases\gnss\gnssfile13.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg14] ( name = n'gnssfile14', filename = n'd:\databases\gnss\gnssfile14.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg15] ( name = n'gnssfile15', filename = n'd:\databases\gnss\gnssfile15.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg16] ( name = n'gnssfile16', filename = n'd:\databases\gnss\gnssfile16.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg17] ( name = n'gnssfile17', filename = n'd:\databases\gnss\gnssfile17.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg18] ( name = n'gnssfile18', filename = n'd:\databases\gnss\gnssfile18.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg19] ( name = n'gnssfile19', filename = n'd:\databases\gnss\gnssfile19.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg2] ( name = n'gnssfile2', filename = n'd:\databases\gnss\gnssfile2.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg20] ( name = n'gnssfile20', filename = n'd:\databases\gnss\gnssfile20.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg21] ( name = n'gnssfile21', filename = n'd:\databases\gnss\gnssfile21.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg22] ( name = n'gnssfile22', filename = n'd:\databases\gnss\gnssfile22.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg23] ( name = n'gnssfile23', filename = n'd:\databases\gnss\gnssfile23.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg24] ( name = n'gnssfile24', filename = n'd:\databases\gnss\gnssfile24.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg25] ( name = n'gnssfile25', filename = n'd:\databases\gnss\gnssfile25.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg26] ( name = n'gnssfile26', filename = n'd:\databases\gnss\gnssfile26.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg27] ( name = n'gnssfile27', filename = n'd:\databases\gnss\gnssfile27.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg28] ( name = n'gnssfile28', filename = n'd:\databases\gnss\gnssfile28.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg29] ( name = n'gnssfile29', filename = n'd:\databases\gnss\gnssfile29.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg3] ( name = n'gnssfile3', filename = n'd:\databases\gnss\gnssfile3.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg30] ( name = n'gnssfile30', filename = n'd:\databases\gnss\gnssfile30.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg31] ( name = n'gnssfile31', filename = n'd:\databases\gnss\gnssfile31.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg4] ( name = n'gnssfile4', filename = n'd:\databases\gnss\gnssfile4.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg5] ( name = n'gnssfile5', filename = n'd:\databases\gnss\gnssfile5.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg6] ( name = n'gnssfile6', filename = n'd:\databases\gnss\gnssfile6.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg7] ( name = n'gnssfile7', filename = n'd:\databases\gnss\gnssfile7.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg8] ( name = n'gnssfile8', filename = n'd:\databases\gnss\gnssfile8.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ), filegroup [gnssfg9] ( name = n'gnssfile9', filename = n'd:\databases\gnss\gnssfile9.ndf' , size = 10240kb , maxsize = unlimited, filegrowth = 51200kb ) log on ( name = n'gnss_log', filename = n'd:\databases\gnss\gnss_log.ldf' , size = 10240kb , maxsize = 2048gb , filegrowth = 10%)
二、创建分区函数和分区方案
分区函数定义了用于分区的数据边界,而分区方案指定了符合分区边界的数据存放在哪个文件组。因此,分区方案中指定的文件组个数应该是比分区函数中指定的边界数大1的。
use [gnss] go /****** object: partitionfunction [pf201808] ******/ create partition function [pf201808](datetime) as range right for values (n'2018-08-02t00:00:00.000', n'2018-08-03t00:00:00.000', n'2018-08-04t00:00:00.000', n'2018-08-05t00:00:00.000', n'2018-08-06t00:00:00.000', n'2018-08-07t00:00:00.000', n'2018-08-08t00:00:00.000', n'2018-08-09t00:00:00.000', n'2018-08-10t00:00:00.000', n'2018-08-11t00:00:00.000', n'2018-08-12t00:00:00.000', n'2018-08-13t00:00:00.000', n'2018-08-14t00:00:00.000', n'2018-08-15t00:00:00.000', n'2018-08-16t00:00:00.000', n'2018-08-17t00:00:00.000', n'2018-08-18t00:00:00.000', n'2018-08-19t00:00:00.000', n'2018-08-20t00:00:00.000', n'2018-08-21t00:00:00.000', n'2018-08-22t00:00:00.000', n'2018-08-23t00:00:00.000', n'2018-08-24t00:00:00.000', n'2018-08-25t00:00:00.000', n'2018-08-26t00:00:00.000', n'2018-08-27t00:00:00.000', n'2018-08-28t00:00:00.000', n'2018-08-29t00:00:00.000', n'2018-08-30t00:00:00.000', n'2018-08-31t00:00:00.000') go use [gnss] go /****** object: partitionscheme [ps201808] ******/ create partition scheme [ps201808] as partition [pf201808] to ([gnssfg1], [gnssfg2], [gnssfg3], [gnssfg4], [gnssfg5], [gnssfg6], [gnssfg7], [gnssfg8], [gnssfg9], [gnssfg10], [gnssfg11], [gnssfg12], [gnssfg13], [gnssfg14], [gnssfg15], [gnssfg16], [gnssfg17], [gnssfg18], [gnssfg19], [gnssfg20], [gnssfg21], [gnssfg22], [gnssfg23], [gnssfg24], [gnssfg25], [gnssfg26], [gnssfg27], [gnssfg28], [gnssfg29], [gnssfg30], [gnssfg31]) go
三、创建分区表
分区表跟普通表创建有点不一样,分区表的创建还需要指定这个分区需要使用哪个分区方案下的分区字段,那么这里就是[ps201808]中的[signaldatetime]字段。
create table [dbo].[201808]( [vin] [char](17) not null, [terminalcode] [varchar](20) not null, [latitude] [float] not null, [longitude] [float] not null, [direction] [smallint] not null, [speed] [float] not null, [elevation] [int] not null, [stateflag] [bigint] not null, [alarmflag] [bigint] not null, [serverdatetime] [datetime] not null, [signaldatetime] [datetime] not null, [isblind] [bit] not null, [accstate] [bit] not null, [positioningstate] [bit] not null, [servercode] [varchar](20) not null, [terminalsim] [varchar](13) null, [platenumber] [nvarchar](8) null, [extrastateflag] [bigint] null, [extrastateflag2] [bigint] null, [extraalarmflag] [bigint] null, [extraalarmflag2] [bigint] null, [rollerstate] [tinyint] null, [electricity] [float] null, [temperature] [varchar](20) null, [oilheight] [float] null, [mileage] [float] null, [oilvolume] [float] null, [drspeed] [float] null, [signalstrength] [tinyint] null, [satellitecount] [tinyint] null, [extendedstate] [bigint] null, [iostate] [int] null, [overspeedlocationtype] [tinyint] null, [overspeedareaorlineid] [bigint] null, [inandoutareaorlinelocationtype] [tinyint] null, [inandoutareaorlineid] [bigint] null, [inandoutareaorlinedirection] [bit] null, [linedrivingtimetoolongornotenoughid] [bigint] null, [linedrivingtime] [int] null, [linedrivingresult] [bit] null ) on [ps201808]([signaldatetime])