SQLserver 表拆分的使用示例

2026-04-01 13:25:04发布 2次浏览

详情描述

1. 水平拆分（按行拆分）

示例1：按日期范围拆分

-- 创建主表（用于查询接口）
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20)
);

-- 创建年度分表
CREATE TABLE Orders_2023 (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20),
    CHECK (YEAR(OrderDate) = 2023)
);

CREATE TABLE Orders_2024 (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20),
    CHECK (YEAR(OrderDate) = 2024)
);

-- 创建视图统一访问
CREATE VIEW vw_AllOrders AS
SELECT * FROM Orders_2023
UNION ALL
SELECT * FROM Orders_2024;

示例2：按地区拆分

-- 创建地区分表
CREATE TABLE Orders_North (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Region VARCHAR(50) DEFAULT 'North',
    OrderDate DATE,
    TotalAmount DECIMAL(10,2)
);

CREATE TABLE Orders_South (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Region VARCHAR(50) DEFAULT 'South',
    OrderDate DATE,
    TotalAmount DECIMAL(10,2)
);

-- 使用分区视图
CREATE VIEW vw_OrdersByRegion AS
SELECT * FROM Orders_North
UNION ALL
SELECT * FROM Orders_South;

2. 垂直拆分（按列拆分）

示例：将大表拆分为常用列和不常用列

-- 主表（常用信息）
CREATE TABLE Customers_Main (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Email VARCHAR(100),
    Phone VARCHAR(20),
    CreateDate DATETIME DEFAULT GETDATE()
);

-- 扩展表（不常用信息）
CREATE TABLE Customers_Details (
    CustomerID INT PRIMARY KEY,
    Address NVARCHAR(200),
    BirthDate DATE,
    Preferences NVARCHAR(MAX),
    LastLogin DATETIME,
    FOREIGN KEY (CustomerID) REFERENCES Customers_Main(CustomerID)
);

-- 使用视图合并
CREATE VIEW vw_CustomerFullInfo AS
SELECT 
    m.CustomerID,
    m.CustomerName,
    m.Email,
    m.Phone,
    d.Address,
    d.BirthDate,
    d.Preferences,
    d.LastLogin
FROM Customers_Main m
LEFT JOIN Customers_Details d ON m.CustomerID = d.CustomerID;

3. 分区表（Partitioned Tables）

示例1：按日期范围分区

-- 创建分区函数
CREATE PARTITION FUNCTION pf_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES (
    '2023-01-01',
    '2024-01-01',
    '2025-01-01'
);

-- 创建分区方案
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (
    [PRIMARY],
    [FG_2023],
    [FG_2024],
    [FG_2025]
);

-- 创建分区表
CREATE TABLE Orders_Partitioned (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20)
) ON ps_OrderDate(OrderDate);

-- 创建聚集索引（分区依据）
CREATE CLUSTERED INDEX IX_Orders_OrderDate 
ON Orders_Partitioned(OrderDate)
ON ps_OrderDate(OrderDate);

示例2：按状态分区

-- 创建分区函数
CREATE PARTITION FUNCTION pf_OrderStatus (VARCHAR(20))
AS RANGE RIGHT FOR VALUES (
    'Pending',
    'Processing',
    'Shipped',
    'Delivered'
);

-- 创建分区方案
CREATE PARTITION SCHEME ps_OrderStatus
AS PARTITION pf_OrderStatus
TO ([PRIMARY], [FG_Pending], [FG_Processing], [FG_Shipped], [FG_Delivered]);

-- 创建分区表
CREATE TABLE Orders_ByStatus (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    Status VARCHAR(20),
    TotalAmount DECIMAL(10,2)
) ON ps_OrderStatus(Status);

4. 分布式分区视图

示例：跨服务器分区

-- 服务器1上执行
CREATE TABLE Server1.dbo.Orders_Part1 (
    OrderID INT PRIMARY KEY CHECK (OrderID BETWEEN 1 AND 10000),
    CustomerID INT,
    OrderDate DATE
);

-- 服务器2上执行
CREATE TABLE Server2.dbo.Orders_Part2 (
    OrderID INT PRIMARY KEY CHECK (OrderID BETWEEN 10001 AND 20000),
    CustomerID INT,
    OrderDate DATE
);

-- 创建分布式分区视图（在任一服务器上）
CREATE VIEW vw_DistributedOrders AS
SELECT * FROM Server1.dbo.Orders_Part1
UNION ALL
SELECT * FROM Server2.dbo.Orders_Part2;

5. 实用查询和管理

查询分区信息

-- 查看分区情况
SELECT 
    p.partition_number,
    f.name AS function_name,
    r.boundary_id,
    r.value AS boundary_value,
    p.rows
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions f ON ps.function_id = f.function_id
LEFT JOIN sys.partition_range_values r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID('Orders_Partitioned')
ORDER BY p.partition_number;

-- 统计分区行数
SELECT 
    $PARTITION.pf_OrderDate(OrderDate) AS PartitionNumber,
    COUNT(*) AS RowCount
FROM Orders_Partitioned
GROUP BY $PARTITION.pf_OrderDate(OrderDate)
ORDER BY PartitionNumber;

分区维护操作

-- 添加新分区
ALTER PARTITION SCHEME ps_OrderDate
NEXT USED [FG_2026];

ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ('2026-01-01');

-- 合并分区
ALTER PARTITION FUNCTION pf_OrderDate()
MERGE RANGE ('2023-01-01');

-- 切换分区（归档旧数据）
CREATE TABLE Orders_Archive_2022 (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20)
) ON [ARCHIVE_FG];

ALTER TABLE Orders_Partitioned
SWITCH PARTITION 1 TO Orders_Archive_2022;

6. 最佳实践示例

-- 综合示例：订单系统分区方案
CREATE PARTITION FUNCTION pf_MonthlyPartition (DATETIME)
AS RANGE RIGHT FOR VALUES (
    '2024-01-01', '2024-02-01', '2024-03-01',
    '2024-04-01', '2024-05-01', '2024-06-01'
);

-- 为每个月创建文件组
ALTER DATABASE CurrentDB
ADD FILEGROUP FG_202401;

ALTER DATABASE CurrentDB
ADD FILE (
    NAME = N'FG_202401_File',
    FILENAME = N'D:\Data\FG_202401.ndf',
    SIZE = 100MB
) TO FILEGROUP FG_202401;

-- 创建分区方案
CREATE PARTITION SCHEME ps_Monthly
AS PARTITION pf_MonthlyPartition
TO (
    [FG_202401], [FG_202402], [FG_202403],
    [FG_202404], [FG_202405], [FG_202406],
    [PRIMARY]
);

-- 创建分区表
CREATE TABLE dbo.OrderTransactions (
    TransactionID BIGINT IDENTITY(1,1),
    OrderID INT NOT NULL,
    TransactionDate DATETIME NOT NULL DEFAULT GETDATE(),
    Amount DECIMAL(15,2),
    TransactionType VARCHAR(20),
    INDEX IX_TransactionDate CLUSTERED (TransactionDate)
) ON ps_Monthly(TransactionDate);