Table/Index Partitioning trong SQL Server
Khái niệm
Partitioning là cách sắp xếp dữ liệu table hoặc index thành các đơn vị nhỏ hơn dựa trên giá trị của các column nhất định.
Ưu điểm
- Tăng hiệu năng truy vấn: query giới hạn theo phân vùng sẽ nhanh hơn thay vì query cả table. Dữ liệu được lưu trữ ở các vùng vật lý khác nhau, giảm tranh chấp I/O giữa các câu query.
- Quản lý dễ hơn: chia table lớn thành các phần nhỏ, dễ quản lý hơn trong việc backup/restore, hoặc create/rebuild index. Nhanh chóng loại bỏ hoặc thêm mới dữ liệu 1 phần thay vì dùng lệnh DELETE (SWITCH-OUT)/ INSERT (SWITCH-IN)
- Nâng cao tính khả dụng: Vì các phân vùng có thể được quản lý độc lập nên lỗi của một phân vùng sẽ không ảnh hưởng đến các phân vùng khác.
Hạn chế
- Tăng độ phức tạp trong việc thiết kế database và quản trị
- Phát sinh chi phí: trong sql server thì partition chỉ có trên bản SQL Server Enterprise Edition, chi phí Reindex hoặc Gather Statistics phức tạp hơn, cần người có kinh nghiệm DBA quản trị, disk...
- Nếu dùng không đúng cách có thể giảm hiệu suất thay vì tăng.
Partitioned Table: Table được chia theo chiều ngang thành nhiều phân vùng, mỗi phân vùng chứa một tập con dữ liệu dựa trên phạm vi giá trị (range) của một Column.
Partitioned Index: Index được chia tương ứng với Table đã được phân vùng, giúp cải thiện hiệu năng truy vấn khi Index cũng được phân vùng giống Table.
Chọn partition key
- Ứng viên để lựa chọn làm partition key chính là các column thường xuyên xuất hiện trong mệnh đề WHERE.
- Column nào có nhiều giá trị khác nhau có thể cân nhắc làm partition key.
VD: Column gioi_tinh với chỉ có 2 giá trị Nam và Nữ, thì có lẽ không cải thiện được hiệu năng nhiều.
- Các giá trị trong column nên được phân phối đều nhau.
VD: Column cap_bac với 3 giá trị: Nhân viên, trưởng phòng và giám đốc. Mà số nhân viên chiếm tới 90% tổng nhân sự thì lựa chọn column này làm partition key không ổn đâu!
- Các column có kiểu varchar, mà giá trị có thể là bất cứ thứ gì, không nên lựa chọn là partition key ví dụ như column ho_ten vì:
- Phân phối dữ liệu không đồng đều: Dữ liệu trong column varchar như ho_ten thường có giá trị khác nhau và phân tán rộng. Khi sử dụng column này làm partition key, dữ liệu có thể phân bố không đều giữa các partition. Điều này dẫn đến một số partition có nhiều dữ liệu hơn, gây mất cân bằng và ảnh hưởng đến hiệu suất truy vấn.
- Hiệu suất truy vấn thấp: Partition key nên được chọn sao cho các truy vấn thường xuyên sử dụng column đó để lọc dữ liệu. Tuy nhiên, các truy vấn trên column ho_ten thường không sử dụng giá trị cụ thể mà có thể sử dụng các phép so sánh như LIKE. Điều này làm giảm hiệu quả của partitioning vì hệ thống phải quét qua nhiều partition để tìm dữ liệu, thay vì chỉ tập trung vào một partition cụ thể.
- Khó quản lý phân vùng: Quản lý partitioning đòi hỏi việc phân chia và hợp nhất các partition một cách hợp lý. Với column varchar chứa các giá trị không đoán trước như tên người, việc xác định ranh giới giữa các partition rất khó khăn, dẫn đến việc quản lý phức tạp.
- Nếu table của bạn có chính sách định kỳ cut off / archive) dữ liệu ( tức là định kỳ sẽ xóa hoặc move các dữ liệu đã cũ sang chỗ khác), thì column điều kiện để archive dữ liệu (VD column created_date) có thể được lựa chọn làm partition key. Làm như vậy, việc cutoff hay archive dữ liệu sau này sẽ rất nhàn.
Thông thường partition key là các column có kiểu dữ liệu số học (int, bigint,...) hoặc ngày tháng (date, datetime,...) vì chúng thường có tính đơn điệu và dễ dự đoán, giúp phân vùng dữ liệu hiệu quả hơn.
Các bước để tạo partition table
- Tạo filegroup và data file: Tạo một hoặc nhiều filegroup cùng với các tệp dữ liệu để chứa các phân vùng. Việc này cho phép bạn thực hiện sao lưu và khôi phục độc lập cho từng filegroup. Nếu không cần tính năng này, bạn có thể gán tất cả các phân vùng vào một filegroup duy nhất, như PRIMARY.
- Tạo partition function: dùng để tạo rule giá trị biên cho các partition, phân chia các row vào các phân vùng dựa trên giá trị của một column nhất định.
- Tạo partition scheme: Ánh xạ rule partition function vào các filegroup
- Tạo hoặc sửa Table/Indexes: chỉ định partition scheme làm vị trí lưu trữ.
VD: tạo partition table và index cho table chứa thông tin đơn hàng, chọn column partition key là OrderDate
1. Tạo filegroup and data file
ALTER DATABASE Ecommerce ADD FILEGROUP FG_12_2019, FG_12_2020, FG_12_2021;
ALTER DATABASE Ecommerce ADD FILE
(
NAME = N'FG_12_2019',
FILENAME = N'C:\Data\FG_12_2019.ndf',
SIZE = 5MB
) TO FILEGROUP FG_12_2019;
ALTER DATABASE Ecommerce ADD FILE
(
NAME = N'FG_12_2020',
FILENAME = N'C:\Data\FG_12_2020.ndf',
SIZE = 5MB
) TO FILEGROUP FG_12_2020;
ALTER DATABASE Ecommerce ADD FILE
(
NAME = N'FG_12_2021',
FILENAME = N'C:\Data\FG_12_2021.ndf',
SIZE = 5MB
) TO FILEGROUP FG_12_2021;
2. Tạo partition function
CREATE PARTITION FUNCTION OrderDateRangePF (DATE)
AS RANGE LEFT FOR VALUES
('2019-12-31', '2020-12-31');
3. Tạo partititon scheme
CREATE PARTITION SCHEME OrderDateRangePS
AS PARTITION OrderDateRangePF
TO (FG_12_2019, FG_12_2020, FG_12_2021);
4. Chỉ định data table lưu trữ
CREATE TABLE Orders
(
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
Amount MONEY
)
ON OrderDateRangePS(OrderDate);
5. Đánh index cho partition: trường hợp này được hiểu là đánh index cho column Id theo phân vùng OrderDate
ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY KEY Orders (Id) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Orders_ID ON Orders (Id)
ON OrderDateRangePS (OrderDate);
Loại partition trong SQL Server
Có 3 kiểu partition trong SQL Server: List partition, Range partition, Hash partition.
List partition
Đặc điểm: Table sẽ được phân chia thành các partition dựa trên các giá trị của coulmn partition key, các giá trị này là hữu hạn và rời rạc (discrete value) không có thứ tự cụ thể. VD: column quoc_gia có các giá trị: VN, US, CN, JP,.., mỗi giá trị sẽ ứng với 1 partition.
Các column partition key thông thường dùng cho List partition, ví dụ như: chi_nhanh, phong_ban, san_pham, …
CREATE PARTITION FUNCTION CountryPartitionFunction (char(3))
AS RANGE LEFT FOR VALUES ('USA', 'CAN', 'MEX');
CREATE PARTITION SCHEME CountryPartitionScheme
AS PARTITION CountryPartitionFunction
TO ([Primary], [Secondary], [Tertiary]);
Range partition
Đặc điểm: Range partition sẽ chia table ra thành các partition dựa trên việc chia khoảng các giá trị của column partition key. Các giá trị của column partition key có thể là bất cứ giá trị nào, nhưng có thể sắp xếp chúng theo thứ tự cụ thể, range partition sẽ được sử dụng.
Thường sử dụng cho các partition có data type là date, datetime hoặc number. VD như: column created_date, ngay_mua, ngay_thanh_toan, …
Đa số các table parttioned table mình gặp là Range partition.
CREATE PARTITION FUNCTION YearPartitionFunction (INT)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021);
CREATE PARTITION SCHEME YearPartitionScheme
AS PARTITION YearPartitionFunction
TO ([Primary], [Secondary], [Tertiary]);
Hash partition
Đặc điểm: Các dòng dữ liệu sẽ được phân phối ngẫu nhiên vào các partition, bằng cách sử dụng 1 hàm hash value column partition key các parttion sẽ có dữ liệu đồng đều như nhau. Mỗi khi có dữ liệu mới, hash value sẽ được tính toán và quyết định dữ liệu đó sẽ thuộc về parttion nào.
Thường dùng khi giá trị của partition key là ngẫu nhiên, không có thứ tự. VD như: column mã khách hàng, mã hóa đơn, mã giao dịch,…
Áp dụng khi bạn muốn tăng tốc truy vấn bằng xử lý song song (parallel query)
CREATE PARTITION FUNCTION HashPartitionFunction (INT)
AS HASH (3);
CREATE PARTITION SCHEME HashPartitionScheme
AS PARTITION HashPartitionFunction
TO ([Primary], [Secondary], [Tertiary]);
Script Check trạng thái partition, thay db_name 'Sales'
SELECT OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName ,
OBJECT_NAME(pstats.object_id) AS TableName ,
ps.name AS PartitionSchemeName ,
ds.name AS PartitionFilegroupName ,
pf.name AS PartitionFunctionName ,
CASE pf.boundary_value_on_right
WHEN 0 THEN 'Range Left'
ELSE 'Range Right'
END AS PartitionFunctionRange ,
CASE pf.boundary_value_on_right
WHEN 0 THEN 'Upper Boundary'
ELSE 'Lower Boundary'
END AS PartitionBoundary ,
prv.value AS PartitionBoundaryValue ,
c.name AS PartitionKey ,
CASE
WHEN pf.boundary_value_on_right = 0 THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id
ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))
ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id
ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
END AS PartitionRange ,
pstats.partition_number AS PartitionNumber ,
pstats.row_count AS PartitionRowCount ,
p.data_compression_desc AS DataCompression
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id
AND pstats.index_id = i.index_id
AND dds.partition_scheme_id = i.data_space_id
AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND pstats.partition_number = (CASE pf.boundary_value_on_right
WHEN 0 THEN prv.boundary_id
ELSE (prv.boundary_id+1)
END)
WHERE pstats.object_id = OBJECT_ID('Sales')
ORDER BY TableName,
PartitionNumber;
Automation partition
Tạo script Procedure T-SQL, tạo Job set plan chạy theo đúng plan tạo partition:
VD: tạo automation partition cho database Sales bên trên, table order
USE Ecommerce;
ALTER PROCEDURE [dbo].[SP_Partition_Sales]
AS
BEGIN
DECLARE @Sql NVARCHAR(MAX);
DECLARE @filegroups NVARCHAR(MAX) = CONCAT('FG_', FORMAT(GETDATE(), 'MM_yyyy'));
DECLARE @pathgroups NVARCHAR(MAX) = CONCAT('D:\Data\', @filegroups, '.ndf');
SET @Sql = '
IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = ' + QUOTENAME(@filegroups, '''') + ')
BEGIN
ALTER DATABASE [Sales]
ADD FILEGROUP [' + @filegroups + '];
ALTER DATABASE [Sales]
ADD FILE (
NAME = ' + QUOTENAME(@filegroups, '''') + ',
FILENAME = ' + QUOTENAME(@pathgroups, '''') + '
) TO FILEGROUP [' + @filegroups + '];
ALTER PARTITION SCHEME OrderDateRangePS
NEXT USED [' + @filegroups + '];
ALTER PARTITION FUNCTION [OrderDateRangePF] ()
SPLIT RANGE (FORMAT(EOMONTH(GETDATE()), ''yyyy-MM-dd''));
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_Orders_Id'' AND object_id = OBJECT_ID(''Orders''))
BEGIN
ALTER TABLE [Orders] DROP CONSTRAINT IF EXISTS [PK_Orders];
ALTER TABLE [Orders] DROP CONSTRAINT IF EXISTS [IX_Orders_Id];
ALTER TABLE [Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY (Id);
CREATE NONCLUSTERED INDEX IX_Orders_Id ON [Orders]([Id]) ON OrderDateRangePS ([OrderDate]);
END
END';
EXEC sp_executesql @Sql;
END;
All rights reserved
