SQL 创建分区表

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