首页 > 代码库 > SQL SERVER 表分区

SQL SERVER 表分区

2024-07-17 04:47:28   216人阅读

一 .SQL SERVER 表分区介绍：

SQL Server  引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能……

二 .SQL SERVER 数据库表分区由三个步骤来完成:

1.创建分区函数

2.创建分区架构

3.对表进行分区

基于缓存更新机制，我使用时间来进行分区，这里大家根据业务的要求使用合适的字段来作为分区

创建数据库分区文件数量，这里存储一年的数据分成十二个分区，需要现在D盘建立好Data 的文件夹 里面包含Primary 文件夹和 FG1 FG2 FG3 FG4............

 

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N‘AirAvCache‘)  DROP DATABASE [AirAvCache]  GO  CREATE DATABASE [AirAvCache]  ON PRIMARY (NAME=‘Data Partition DB Primary FG‘,  FILENAME=  ‘D:\Data\Primary\AirAvCache Primary FG.mdf‘,  SIZE=5,  MAXSIZE=500,  FILEGROWTH=1 ),  FILEGROUP [AirAvCache FG1]  (NAME = ‘AirAvCache FG1‘,  FILENAME =  ‘D:\Data\FG1\AirAvCache FG1.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),  FILEGROUP [AirAvCache FG2]  (NAME = ‘AirAvCache FG2‘,  FILENAME =  ‘D:\Data\FG2\AirAvCache FG2.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),  FILEGROUP [AirAvCache FG3]  (NAME = ‘AirAvCache FG3‘,  FILENAME =  ‘D:\Data\FG3\AirAvCache FG3.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),  FILEGROUP [AirAvCache FG4]  (NAME = ‘AirAvCache FG4‘,  FILENAME =  ‘D:\Data\FG4\AirAvCache FG4.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),  FILEGROUP [AirAvCache FG5]  (NAME = ‘AirAvCache FG5‘,  FILENAME =  ‘D:\Data\FG5\AirAvCache FG5.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),   FILEGROUP [AirAvCache FG6]  (NAME = ‘AirAvCache FG6‘,  FILENAME =  ‘D:\Data\FG6\AirAvCache FG6.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),    FILEGROUP [AirAvCache FG7]  (NAME = ‘AirAvCache FG7‘,  FILENAME =  ‘D:\Data\FG7\AirAvCache FG7.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),   FILEGROUP [AirAvCache FG8]  (NAME = ‘AirAvCache FG8‘,  FILENAME =  ‘D:\Data\FG8\AirAvCache FG8.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),   FILEGROUP [AirAvCache FG9]  (NAME = ‘AirAvCache FG9‘,  FILENAME =  ‘D:\Data\FG9\AirAvCache FG9.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),   FILEGROUP [AirAvCache FG10]  (NAME = ‘AirAvCache FG10‘,  FILENAME =  ‘D:\Data\FG10\AirAvCache FG10.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),   FILEGROUP [AirAvCache FG11]  (NAME = ‘AirAvCache FG11‘,  FILENAME =  ‘D:\Data\FG11\AirAvCache FG11.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 ),   FILEGROUP [AirAvCache FG12]  (NAME = ‘AirAvCache FG12‘,  FILENAME =  ‘D:\Data\FG12\AirAvCache FG12.ndf‘,  SIZE = 5MB,  MAXSIZE=500,  FILEGROWTH=1 )

 

 

 

创建好后如图：

打开FG1 文件夹 看到多了AirAvCacheFG1.ndf 文件

创建分区函数

USE AirAvCache  GO    -- 创建函数   CREATE PARTITION FUNCTION [AirAvCache Partition  Range](DATETIME)  AS RANGE LEFT FOR VALUES (‘2010-09-01‘,‘2010-10-01‘,‘2010-11-01‘,‘2010-12-01‘,‘2011-01-01‘,‘2011-02-01‘,‘2011-03-01‘,‘2011-04-01‘,‘2011-05-01‘,‘2011-06-01‘,‘2010-07-01‘);

 

创建分区架构

CREATE PARTITION SCHEME [AirAvCache Partition Scheme]  AS PARTITION [AirAvCache Partition  Range]  TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3],[AirAvCache FG4],[AirAvCache FG5],[AirAvCache FG6],[AirAvCache FG7],[AirAvCache FG8],  [AirAvCache FG9],[AirAvCache FG10],[AirAvCache FG11],[AirAvCache FG12]);

 

创建一个使用AirAvCache Partitiion Scheme 架构的表

CREATE TABLE [dbo].[AvCache](      [CityPair] [varchar](6)  NOT NULL,      [FlightNo] [varchar](10)  NULL,      [FlightDate] [datetime] NOT NULL,      [CacheTime] [datetime] NOT NULL   DEFAULT (getdate()),      [AVNote] [varchar](300)  NULL )  ON [AirAvCache Partition Scheme] (FlightDate);   --注意这里使用[AirAvCache Partition Scheme]架构，根据FlightDate 分区

 

查询分区情况

  1. -- 查看使用情况   SELECT *, $PARTITION.[AirAvCache Partition  Range](FlightDate)  FROM dbo.AVCache

     

可以看到9 月和 10 月已经分开了。

 


联系
我们