解说 CREATE PARTITION 用法

<think> 我们正在讨论SQL Server中的分区创建，具体是CREATE PARTITION FUNCTION和CREATE PARTITION SCHEME的用法。 根据用户提供的引用[1]和[2]，我们可以总结如下： 1. 分区函数（CREATE PARTITION FUNCTION）定义如何根据指定列的值将数据划分到不同分区。 例如，引用[2]中： CREATE PARTITION FUNCTION TestPartition (int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000); 这表示创建一个按整数字段分区的函数，分区边界值为1000000, 2000000, 3000000，并且使用RANGE LEFT（即每个边界值属于左侧分区）。 2. 分区方案（CREATE PARTITION SCHEME）将分区函数指定的分区映射到文件组。 例如，引用[2]中： CREATE PARTITION SCHEME [TestPartitionScheme] AS PARTITION TestPartition TO ([P1], [P2], [P3], [PRIMARY]); 这里将分区函数TestPartition的4个分区（因为边界值有3个，所以分成4个分区）分别映射到文件组P1、P2、P3和PRIMARY。 3. 在创建表时，使用分区方案来存储表数据。例如： CREATE TABLE dbo.TestTable (ID int, ...) ON TestPartitionScheme(ID) 4. 文件组需要预先创建，并且可以为每个文件组添加数据文件（如引用[2]中ALTER DATABASE添加文件组和文件的步骤）。 因此，我们将详细解释这两个步骤以及如何应用到表上。 注意：用户要求解说CREATE PARTITION的用法，但实际上SQL Server中分区是通过分区函数和分区方案两个步骤实现的，没有单独的CREATE PARTITION语句。 步骤分解： 步骤1：创建分区函数（Partition Function） 语法： CREATE PARTITION FUNCTION partition_function_name (input_parameter_type) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) 作用：定义分区边界和分区方式（RANGE LEFT或RANGE RIGHT）。 - RANGE LEFT：指定边界值属于左侧分区。例如，边界值1000000，则小于等于1000000的数据在第一个分区，大于1000000且小于等于2000000的在第二个分区，依此类推。 - RANGE RIGHT：指定边界值属于右侧分区。例如，边界值1000000，则小于1000000的数据在第一个分区，大于等于1000000且小于2000000的在第二个分区，依此类推。 步骤2：创建分区方案（Partition Scheme） 语法： CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) 作用：将分区函数创建的分区映射到具体的文件组。注意，分区的个数由分区函数中边界值的数量决定（n个边界值产生n+1个分区），因此需要提供n+1个文件组（除非使用ALL关键字，将所有分区映射到同一个文件组）。 步骤3：创建表或索引时应用分区方案 在创建表时，指定分区方案和分区列： CREATE TABLE table_name (...) ON partition_scheme_name (column_name) 示例（结合引用[2]）： 1. 创建分区函数TestPartition，按整型字段分区，边界值为1000000,2000000,3000000，采用RANGE LEFT方式： CREATE PARTITION FUNCTION TestPartition (int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000); 分区结果： 分区1：ID <= 1000000 分区2：ID > 1000000且 <= 2000000 分区3：ID > 2000000且 <= 3000000 分区4：ID > 3000000 2. 创建文件组（P1, P2, P3）和对应的数据文件（通过ALTER DATABASE语句），然后将分区映射到这些文件组和一个主文件组（PRIMARY）： CREATE PARTITION SCHEME [TestPartitionScheme] AS PARTITION TestPartition TO ( [P1], [P2], [P3], [PRIMARY] ); 这里分区1对应P1，分区2对应P2，分区3对应P3，分区4对应PRIMARY。 3. 创建表并应用分区方案： CREATE TABLE dbo.TestTable ( ID int PRIMARY KEY, ...其他列 ) ON TestPartitionScheme(ID); 注意事项： - 分区列必须是表中的一个列，且数据类型与分区函数参数类型一致。 - 文件组必须事先存在。 - 分区方案中指定的文件组数量必须等于分区函数定义的分区数（即边界值数+1）。 此外，还可以使用以下系统函数查看分区情况： - $PARTITION.partition_function_name(column_value)：返回指定值所在的分区号。 - sys.partitions：查看每个对象（表或索引）的分区信息。 因此，我们将按照上述步骤进行解说，并给出一个完整的示例。 </think> ### SQL Server 分区创建详解 在 SQL Server 中，分区通过 **分区函数** (`CREATE PARTITION FUNCTION`) 和 **分区方案** (`CREATE PARTITION SCHEME`) 两步实现，需结合文件组管理物理存储。 --- ### 一、核心语法及步骤 #### 1. 创建分区函数 定义数据分区的逻辑边界规则： ```sql CREATE PARTITION FUNCTION PartitionFunctionName (data_type) AS RANGE [LEFT|RIGHT] FOR VALUES (boundary1, boundary2, ...); ``` - **`RANGE LEFT`**：边界值归属左侧分区（如 `value <= boundary`） - **`RANGE RIGHT`**：边界值归属右侧分区（如 `value < boundary`） *示例*： ```sql CREATE PARTITION FUNCTION SalesPF (int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000); ``` 此函数将数据分为 4 个分区： $$\begin{align*} & \text{分区1: } \text{id} \leq 1000000 \\ & \text{分区2: } 1000000 < \text{id} \leq 2000000 \\ & \text{分区3: } 2000000 < \text{id} \leq 3000000 \\ & \text{分区4: } \text{id} > 3000000 \end{align*}$$ #### 2. 准备文件组 为每个分区创建独立的物理存储位置： ```sql ALTER DATABASE MyDB ADD FILEGROUP FG1; ALTER DATABASE MyDB ADD FILEGROUP FG2; -- 为文件组添加数据文件 ALTER DATABASE MyDB ADD FILE ( NAME = 'FG1_Data', FILENAME = 'D:\Data\FG1.ndf', SIZE = 100MB ) TO FILEGROUP FG1; ``` 重复此步骤为每个分区创建文件组（FG1, FG2, FG3, ...）[^2]。 #### 3. 创建分区方案 将分区函数映射到物理文件组： ```sql CREATE PARTITION SCHEME SalesPS AS PARTITION SalesPF TO (FG1, FG2, FG3, [PRIMARY]); ``` - **文件组数量** = **分区数**（边界值数量+1） - `[PRIMARY]` 表示使用默认主文件组[^1]。 #### 4. 应用分区到表 创建表时指定分区方案和分区列： ```sql CREATE TABLE Sales ( OrderID INT PRIMARY KEY, Amount MONEY, OrderDate DATE ) ON SalesPS (OrderID); -- OrderID 是分区列 ``` --- ### 二、关键参数说明 | **组件** | **参数** | **作用** | |---------------------|--------------------|-------------------------------------------------------------------------| | 分区函数 | `RANGE LEFT/RIGHT` | 控制边界值归属分区 | | 分区方案 | `TO (filegroups)` | 指定每个分区对应的物理文件组 | | 表定义 | `ON Scheme(Column)`| 将表数据按分区列存储到指定方案中 | --- ### 三、分区验证与管理 1. **查看分区数据分布**： ```sql SELECT $PARTITION.SalesPF(OrderID) AS PartitionID, COUNT(*) FROM Sales GROUP BY $PARTITION.SalesPF(OrderID); ``` 2. **添加新分区**（需修改分区函数和方案）： ```sql ALTER PARTITION FUNCTION SalesPF() SPLIT RANGE (4000000); -- 新增边界值 ``` 3. **合并分区**： ```sql ALTER PARTITION FUNCTION SalesPF() MERGE RANGE (2000000); -- 删除边界值2000000 ``` --- ### 四、最佳实践 1. **分区列选择**： - 使用高频查询的列（如日期、ID） - 避免选择频繁更新的列，减少分区移动开销 2. **文件组规划**： - 将活跃分区放在SSD文件组 - 归档分区放在HDD文件组以节省成本 3. **索引对齐**： 创建与分区方案一致的索引，提升查询性能： ```sql CREATE INDEX IX_OrderDate ON Sales(OrderDate) ON SalesPS(OrderID); -- 索引与表使用相同分区方案 ``` > ⚠️ **注意事项** > - 分区键修改需重建表 > - 跨分区查询可能引发性能问题，需优化过滤条件 > - 定期检查分区分布均衡性 ---