sqlserver表分区

表分区是提升 SQL Server 数据库性能、提高维护效率的重要手段，尤其在处理大数据量时更显著。在设计和实施分区策略时，建议结合具体的业务场景和数据需求，以实现最佳效果。

概述

什么是表分区：

• 表分区是将单个逻辑表分散到多个物理存储位置的过程。SQL Server 支持多种分区方法，包括基于id范围、日期、列表、哈希等。每个分区可以被独立管理，从而优化查询性能和维护后台操作。
• 对表进行分区的过程，实际上是将逻辑上完整的一个表，按照特定的字段拆分成多个分区，分散到相同或不同的FileGroup中，每一个部分叫做表的一个分区，一个分区实际上是一个独立的物理表。也就是说，分区表在逻辑上是一个表，而在物理上是多个完全独立的表。

表分区的优点：

• 1.提高查询性能。当查询涉及到大数据量时，全表扫描会消耗大量资源。分区可以限制这些查询只在相关的分区上进行，从而大幅提高查询效率。
• 2.方便数据维护。表分区使数据清理和归档变得更简单。可以独立处理每个分区，如直接删除或修剪过期数据，不影响其他分区的数据。

--.将特定分区切换到备份表
ALTER TABLE Log_UserVisit SWITCH PARTITION 2 TO Log_UserVisit_20130416

• 3.提高并发处理能力。分区数据可以在多个文件组中存储，从而允许多个用户或进程并行访问不同的分区。这增强了系统的处理能力，并提高了响应速度。
• 4.允许精细的索引策略。对于每个分区，可以实施不同的索引策略，以满足特定的查询需求。这不仅优化了存储，还有助于提升查询性能。

配置表分区实战

• 示例：磁盘0划分C盘、D盘，磁盘1划分E盘，磁盘2划分F盘，使用2个磁盘划分4个文件组（有条件的话，建议每个文件组使用单独的磁盘）

配置项	配置名称	备注
机器名	win2019	Windows Server 2019 Datacenter x64
SQL Server 版本	15.0.2000.5	SQL Server 2019 (RTM) Enterprise x64
数据目录	D:\sqldata\data	磁盘0
日志目录	D:\sqldata\log	磁盘0
文件组1	E:\sqldata\filegroup1	磁盘1
文件组2	F:\sqldata\filegroup2	磁盘2
文件组3	E:\sqldata\filegroup3	磁盘1
文件组4	F:\sqldata\filegroup4	磁盘2

1. 创建数据库

• 示例：数据目录及日志目录均在D盘（磁盘0）

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name='MyTestDB')
DROP DATABASE MyTestDB
GO

--.创建数据库目录
EXEC xp_create_subdir 'D:\sqldata\data'
EXEC xp_create_subdir 'D:\sqldata\log'

--.创建数据库
CREATE DATABASE MyTestDB ON PRIMARY ( 
  NAME = N'MyTestDB', 
  FILENAME = N'D:\sqldata\data\MyTestDB.mdf' , 
  SIZE = 100MB , 
  MAXSIZE = UNLIMITED, 
  FILEGROWTH = 10MB )
LOG ON ( 
  NAME = N'MyTestDB_log', 
  FILENAME = N'D:\sqldata\log\MyTestDB_log.ldf' , 
  SIZE = 1024MB , 
  MAXSIZE = 128GB , 
  FILEGROWTH = 10%)
GO

2. 添加文件组

• 示例：4个文件组分布在磁盘1、磁盘2，依次为E盘、F盘、E盘、F盘（有条件的话，建议每个文件组使用单独的磁盘）

USE [master]
GO

--.创建数据库目录
EXEC xp_create_subdir 'E:\sqldata\filegroup1'
EXEC xp_create_subdir 'F:\sqldata\filegroup2'
EXEC xp_create_subdir 'E:\sqldata\filegroup3'
EXEC xp_create_subdir 'F:\sqldata\filegroup4'

--.添加文件组
ALTER DATABASE MyTestDB ADD FILEGROUP fg_1
ALTER DATABASE MyTestDB ADD FILEGROUP fg_2
ALTER DATABASE MyTestDB ADD FILEGROUP fg_3
ALTER DATABASE MyTestDB ADD FILEGROUP fg_4

--.添加数据库文件到指定的文件组
ALTER DATABASE MyTestDB ADD FILE ( 
  NAME = N'MyTestDB_f1', 
  FILENAME = N'E:\sqldata\filegroup1\MyTestDB_f1.ndf' , 
  SIZE = 100MB, 
  MAXSIZE = 102400MB, 
  FILEGROWTH = 10MB ) TO FILEGROUP fg_1
GO

ALTER DATABASE MyTestDB ADD FILE ( 
  NAME = N'MyTestDB_f2', 
  FILENAME = N'F:\sqldata\filegroup2\MyTestDB_f2.ndf' , 
  SIZE = 100MB, 
  MAXSIZE = 102400MB, 
  FILEGROWTH = 10MB ) TO FILEGROUP fg_2
GO

ALTER DATABASE MyTestDB ADD FILE (
  NAME = N'MyTestDB_f3', 
  FILENAME = N'E:\sqldata\filegroup3\MyTestDB_f3.ndf' , 
  SIZE = 100MB, 
  MAXSIZE = 102400MB, 
  FILEGROWTH = 10MB ) TO FILEGROUP fg_3
GO

ALTER DATABASE MyTestDB ADD FILE (
  NAME = N'MyTestDB_f4', 
  FILENAME = N'F:\sqldata\filegroup4\MyTestDB_f4.ndf' , 
  SIZE = 100MB, 
  MAXSIZE = 102400MB, 
  FILEGROWTH = 10MB ) TO FILEGROUP fg_4
GO

• 示例db如下：

3. 配置表分区

USE MyTestDB
GO

--.1.清理测试痕迹
IF OBJECT_ID('Log_UserVisit') IS NOT NULL
DROP TABLE Log_UserVisit
GO

IF EXISTS (SELECT [name] FROM sys.partition_schemes WHERE [name] = 'ps_LogUserVisit_date')
DROP PARTITION SCHEME ps_LogUserVisit_date
GO

IF EXISTS (SELECT [name] FROM sys.partition_functions WHERE [name] = 'pfRight_LogUserVisit_date')
DROP PARTITION FUNCTION pfRight_LogUserVisit_date
GO

--.2.创建分区函数
CREATE PARTITION FUNCTION pfRight_LogUserVisit_date(DATETIME)
AS RANGE RIGHT
FOR VALUES
(                   --(更早, 2012/12/01]
  '2012-12-01',     --[2012/12/01,2012/12/24)
  '2012-12-24',     --[2012/12/24,2012/12/31)
  '2012-12-31'      --[2012/12/31,至今)
)
GO

--.3.创建分区方案,关联到分区函数
CREATE PARTITION SCHEME ps_LogUserVisit_date
AS PARTITION pfRight_LogUserVisit_date
TO (fg_1,fg_2,fg_3,fg_4)   --警告：N个临界值就要有N+1个文件组
-- ALL TO ([PRIMARY])
GO

--.4.创建测试表
CREATE TABLE Log_UserVisit(
  [UserID] [varchar](50) NOT NULL,
  [UserType] [int] NOT NULL,
  [InsertTime] [datetime] NOT NULL,
  [IPAddress] [varchar](50) NOT NULL)
GO

--.5.添加分区索引,并将其绑定到分区方案上
ALTER TABLE Log_UserVisit
  ADD CONSTRAINT PK_Log_UserVisit_Date PRIMARY KEY CLUSTERED (InsertTime)
  ON ps_LogUserVisit_date(InsertTime)
GO

--.针对老表添加分区索引
-- CREATE NONCLUSTERED INDEX IX_Log_UserVisit_Date ON Log_UserVisit(InsertTime) ON ps_LogUserVisit_date(InsertTime)

4. 测试验证

--.1.新增测试数据
INSERT INTO Log_UserVisit
        SELECT 'zhang1',0,'2011-10-01 18:59:09','192.168.0.1'
  UNION SELECT 'zhang2',0,'2012-12-04 19:59:09','192.168.0.2'
  UNION SELECT 'zhang3',0,'2012-12-28 19:59:09','192.168.0.3'
  UNION SELECT 'zhang4',0,'2012-12-31 19:59:09','192.168.0.4'
  UNION SELECT 'zhang5',0,'2013-05-01 19:59:09','192.168.0.5'

--.2.查询该表是不是分区成功
SELECT * FROM sys.partition_range_values
SELECT $partition.pfRight_LogUserVisit_date(GETDATE())
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('Log_UserVisit')

--.3.查询指定分区的数据
SELECT $partition.pfRight_LogUserVisit_date(InsertTime) AS 'Partition',*
FROM Log_UserVisit
WHERE $partition.pfRight_LogUserVisit_date(InsertTime)=1

--.4.统计每个分区有多少数据
SELECT $partition.pfRight_LogUserVisit_date(InsertTime) AS 'Partition',COUNT(*) AS 'cunt'
FROM Log_UserVisit
GROUP BY $partition.pfRight_LogUserVisit_date(InsertTime)
ORDER BY $partition.pfRight_LogUserVisit_date(InsertTime)

表分区维护

1. 对特定分区进行维护

--.对特定分区进行索引重建
ALTER INDEX PK_Log_UserVisit_Date ON Log_UserVisit REBUILD PARTITION = 2

2. split 添加分区

• 示例：添加分区，使其包含大于 '2013-04-01'

--.1.创建目录
EXEC xp_create_subdir 'D:\sqldata\filegroup5'

--.2.添加新的文件组
ALTER DATABASE MyTestDB ADD FILEGROUP fg_5

--.3.添加db文件到新的文件组
ALTER DATABASE MyTestDB ADD FILE (
  NAME = N'MyTestDB_f5', 
  FILENAME = N'D:\sqldata\filegroup5\MyTestDB_f5.ndf' , 
  SIZE = 100MB, 
  MAXSIZE = 102400MB, 
  FILEGROWTH = 10MB ) TO FILEGROUP fg_5

--.4.调整分区方案
ALTER PARTITION SCHEME ps_LogUserVisit_date NEXT USED fg_5

--.5.调整分区函数
ALTER PARTITION FUNCTION pfRight_LogUserVisit_date() SPLIT RANGE('2013-04-01')

--.确认：统计每个分区有多少数据
SELECT $partition.pfRight_LogUserVisit_date(InsertTime) AS 'Partition',COUNT(*) AS 'cunt'
FROM Log_UserVisit
GROUP BY $partition.pfRight_LogUserVisit_date(InsertTime)
ORDER BY $partition.pfRight_LogUserVisit_date(InsertTime)

3. merge 合并分区

• 去除一个边界值，实现分区合并

--.调整分区函数
ALTER PARTITION FUNCTION pfRight_LogUserVisit_date() MERGE RANGE('2013-04-01')

4. switch 切换分区

• 场景1：切换源表的指定分区 to 目标分区表的指定分区（表结构应与分区表的一致），然后再切回源分区表

--.1.创建目标分区表
IF OBJECT_ID('Log_UserVisit_Bak') IS NOT NULL
DROP TABLE Log_UserVisit_Bak
GO

CREATE TABLE Log_UserVisit_Bak(
  [UserID] [varchar](50) NOT NULL,
  [UserType] [int] NOT NULL,
  [InsertTime] [datetime] NOT NULL,
  [IPAddress] [varchar](50) NOT NULL)
GO

ALTER TABLE Log_UserVisit_Bak
  ADD CONSTRAINT PK_Log_UserVisit_Bak_Date PRIMARY KEY CLUSTERED (InsertTime)
  ON ps_LogUserVisit_date(InsertTime)
GO

--.2.先切换到备份表
ALTER TABLE Log_UserVisit SWITCH PARTITION 1 TO Log_UserVisit_Bak PARTITION 1 
ALTER TABLE Log_UserVisit SWITCH PARTITION 2 TO Log_UserVisit_Bak PARTITION 2 

--.3.统计每个分区有多少数据,针对 Log_UserVisit
SELECT $partition.pfRight_LogUserVisit_date(InsertTime) AS 'Partition',COUNT(*) AS 'cunt'
FROM Log_UserVisit
GROUP BY $partition.pfRight_LogUserVisit_date(InsertTime)
ORDER BY $partition.pfRight_LogUserVisit_date(InsertTime)

--.3.统计每个分区有多少数据,针对 Log_UserVisit_Bak
SELECT $partition.pfRight_LogUserVisit_date(InsertTime) AS 'Partition',COUNT(*) AS 'cunt'
FROM Log_UserVisit_Bak
GROUP BY $partition.pfRight_LogUserVisit_date(InsertTime)
ORDER BY $partition.pfRight_LogUserVisit_date(InsertTime)


--.回滚：再切换到原表
ALTER TABLE Log_UserVisit_Bak SWITCH PARTITION 1 TO Log_UserVisit PARTITION 1 
ALTER TABLE Log_UserVisit_Bak SWITCH PARTITION 2 TO Log_UserVisit PARTITION 2 
GO

• 场景2：切换源表的指定分区 to 普通表（表结构应与分区表的一致），然后再切回源分区表

--.1.创建目标表
IF OBJECT_ID('Log_UserVisit_Part2') IS NOT NULL
DROP TABLE Log_UserVisit_Part2
GO

--.2.警告：普通表必须建立在分区表切换分区所在的文件组上,否则 SWITCH PARTITION 会报错
CREATE TABLE Log_UserVisit_Part2(
  [UserID] [varchar](50) NOT NULL,
  [UserType] [int] NOT NULL,
  [InsertTime] [datetime] NOT NULL,
  [IPAddress] [varchar](50) NOT NULL)
ON fg_2   --.第4步示例的 PARTITION 2 在文件组 fg_2 上

--.3.警告：普通表上的索引要跟分区表一致,否则 SWITCH PARTITION 会报错
ALTER TABLE Log_UserVisit_Part2 ADD CONSTRAINT PK_Log_UserVisit_Part2_Date PRIMARY KEY CLUSTERED (InsertTime)

--.4.将特定分区切换到普通表（目标表必须是空表,不能有任何数据）
ALTER TABLE Log_UserVisit SWITCH PARTITION 2 TO Log_UserVisit_Part2

--.确认：统计每个分区有多少数据
SELECT $partition.pfRight_LogUserVisit_date(InsertTime) AS 'Partition',COUNT(*) AS 'cunt'
FROM Log_UserVisit
GROUP BY $partition.pfRight_LogUserVisit_date(InsertTime)
ORDER BY $partition.pfRight_LogUserVisit_date(InsertTime)


--.回滚：切换普通表数据到分区表的特定分区中
--.警告：普通表必须要有和分区数据范围一致的 check 约束条件,否则 SWITCH TO 会报错
ALTER TABLE Log_UserVisit_Part2 ADD CONSTRAINT CK_Log_UserVisit_Part2_Date check (InsertTime>='2012-12-01' and InsertTime<'2012-12-24')
ALTER TABLE Log_UserVisit_Part2 SWITCH TO Log_UserVisit PARTITION 2

附录

1. 单块磁盘配置表分区也能提高性能吗?

多块磁盘配置表分区可以提升io并发性能，而在单块磁盘配置下使用表分区，受限于物理I/O瓶颈，性能提升的效果确实有限，但仍可能有以下方面的好处：

• 1、查询时如果使用了分区键条件，引擎可以只扫描相关分区，减少I/O量。对大型表特别有效，即使在同一磁盘上
• 2、可以单独对某个分区进行维护（如重建索引、统计信息更新）。对存在大量历史数据的表，可以只对最新数据所在的分区（而非全表）进行索引重建
• 3、数据归档时可以直接切换分区而非删除大量数据。大表删数据的效率太慢且影响业务
• 4、分区表更易于实现并行查询计划

2. 单个文件组如何配置表分区?

• 如果不便使用多个文件组来配置表分区，请在创建分区方案的时候指定 ALL TO ([PRIMARY])，如下示例：

USE MyTestDB
GO

--.1.创建分区函数
CREATE PARTITION FUNCTION pfRight_LogUserVisit_date(DATETIME) AS RANGE RIGHT FOR VALUES ('2012-12-01','2012-12-24','2012-12-31')

--.2.创建分区方案,关联到分区函数
CREATE PARTITION SCHEME ps_LogUserVisit_date AS PARTITION pfRight_LogUserVisit_date ALL TO ([PRIMARY])

--.3.创建测试表
CREATE TABLE Log_UserVisit(UserID varchar(50) NOT NULL, UserType int NOT NULL, InsertTime datetime NOT NULL)

--.4.添加分区索引,并将其绑定到分区方案上
ALTER TABLE Log_UserVisit ADD CONSTRAINT PK_Log_UserVisit_Date PRIMARY KEY CLUSTERED (InsertTime) ON ps_LogUserVisit_date(InsertTime)