SQL 创建分区表
程序员文章站
2023-12-21 09:51:34
（以项目中实际使用的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])