欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL 创建分区表

程序员文章站 2023-12-18 20:50:40
(以项目中实际使用的GNSS库为例) 背景:数据量巨大,定时创建月表存放数据,月表中数据存放在不同的文件组中来提高查询效率 一、创建数据库,添加文件组 除了逻辑文件和物理文件的分离之外,SQL Server使用文件组还有一个优势,那就是分散IO负载,其实现的原理是: 对于单分区表,数据只能存到一个文 ......
(以项目中实际使用的gnss库为例)
背景:数据量巨大,定时创建月表存放数据,月表中数据存放在不同的文件组中来提高查询效率
 
一、创建数据库,添加文件组
除了逻辑文件和物理文件的分离之外,sql server使用文件组还有一个优势,那就是分散io负载,其实现的原理是:
  • 对于单分区表,数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上,那么sql server能同时从不同的物理硬盘上读写数据,把io负载分散到不同的硬盘上。
  • 对于多分区表,每个分区使用一个文件组,把不同的数据子集存储在不同的磁盘上,sql server在读写某一个分组的数据时,能够调用不同的硬盘io。
这两种方式,其本质上,都是使每个硬盘均摊系统负载,提高io性能。
SQL 创建分区表
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%)
view code
 

二、创建分区函数和分区方案

分区函数定义了用于分区的数据边界,而分区方案指定了符合分区边界的数据存放在哪个文件组。因此,分区方案中指定的文件组个数应该是比分区函数中指定的边界数大1的。

SQL 创建分区表
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
view code

 

三、创建分区表

分区表跟普通表创建有点不一样,分区表的创建还需要指定这个分区需要使用哪个分区方案下的分区字段,那么这里就是[ps201808]中的[signaldatetime]字段。

SQL 创建分区表
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])
view code

 

 

上一篇:

下一篇: