Partition Table SQL Server

Table partitioning là kỹ thuật phân chia bảng thành từng đoạn nhằm quản lý hiệu quả cơ sở dữ liệu với dung lượng lớn, cung cấp 1 phương pháp khác để chia dữ liệu những bảng lớn và trỏ tới những vùng nhỏ hơn. Bằng phương pháp đó, nó tạo ra một phiên bản quản trị cơ sở dữ liệu dễ dàng hơn khi back up ( sao lưu), loading (nạp dữ liệu), phục hồi (recovery) và truy vấn dữ liệu (query data) Bảng được phân đoạn dựa vào giá trị một trường của nó (trường được chọn gọi là partition key). Kỹ thuật này làm tăng khả năng mở rộng của SQL Server lên rất nhiều, và giúp cho việc quản trị các cơ sở dữ liệu lớn trở nên dễ dàng hơn. Với những bảng dữ liệu chứa vài trăm triệu bản ghi thường xuyên được cập nhật, các tác vụ như backup/restore, hoặc create/rebuild index đều rất tốn kém thời gian. Table partitioning nhằm giải quyết các vấn đề đó và có các ưu điểm sau :

1. Tiện lợi về quản trị

• Backup/restore một đoạn mà không ảnh hưởng đến các đoạn còn lại
• REBUILD lại index trên từng phân đoạn thay vì trên toàn bộ bảng.
• Cho phép nhanh chóng loại bỏ dữ liệu nguyên một đoạn ra khỏi bảng thay vì phải dùng lệnh DELETE (SWITCH-OUT). Tương tự nó cũng cho phép nạp dữ liệu từ một bảng khác vào thành một đoạn mới (SWITCH-IN)

2. Cải tiến về hiệu năng

• Lấy dữ liệu ở một đoạn nào đó chỉ cần truy nhập vào đoạn đó và bỏ qua các đoạn còn lại
• Dữ liệu được lưu trữ ở các vùng vật lý khác nhau, giảm tranh chấp in/out giữa các câu lệnh. Ví dụ hai câu lệnh SELECT và UPDATE hoạt động trên cùng một bảng nhưng ở hai đoạn khác nhau có thể thực hiện hoàn toàn song song với nhau. Việc phân đoạn bảng dựa trên hai khái niệm mới sau đây :
• Partition function: Qui định giá trị biên cho các đoạn. Hệ thống dựa vào hàm này để xác định đoạn mà mỗi bản ghi thuộc vào.
• Partition scheme: Ánh xạ các đoạn khai báo trong partition function vào các filegroup (mỗi đoạn được lưu trữ tại một filegroup).

Partition Function

Đầu tiên bạn cần tạo partition function để định nghĩa giá trị biên của các đoạn. Partition function không giống với các hàm UDF thông thường (bạn không thể gọi trực tiếp), mà chỉ dùng vào mục đích phân đoạn. Nó định nghĩa logic mỗi đoạn chứa dải giá trị từ đâu đến đâu chứ không qui định các đoạn được lưu trữ như thế nào, cũng như tên trường dùng để phân đoạn là gì.

Ví dụ 1: Phân đoạn dựa vào dãy số

CREATE DATABASE PartDB
GO
USE PartDB
GO
CREATE PARTITION FUNCTION PartFunc_1(INT) AS RANGE LEFT FOR VALUES (1000, 2000, 3000)

Ví dụ 2: phân đoạn theo năm

CREATE PARTITION FUNCTION PartFunc_2(DATETIME) 
AS RANGE RIGHT FOR VALUES ('2010-01-01', '2011-01-01')

Partition Scheme

Sau khi định nghĩa dải giá trị cho các đoạn, việc tiếp theo là tạo partition scheme để định nghĩa không gian lưu trữ. Partition scheme ánh xạ từng đoạn đã được định nghĩa trong partition function vào các filegroup

CREATE PARTITION SCHEME MyPartScheme_1 
AS PARTITION MyPartFunc_1 TO (FG1, FG2, FG3, FG4)

Filegroup

Filegroup tương tự như folder trong windows, chỉ là một tên logic để nhóm các data file chứ không bản thân nó chứa dữ liệu. Filegroup chứa một hoặc nhiều data file và các data file này mới thực chứa dữ liệu. Khi mới tạo database, một filegroup có tên PRIMARY tự động được tạo và chứa data file chính (.mdf). Tuy nhiên bạn không nên dùng PRIMARY filegroup vào việc phân đoạn mà nên tạo filegroup mới.

-- tạo filegroup
ALTER DATABASE PartDB ADD FILEGROUP FG1
ALTER DATABASE PartDB ADD FILEGROUP FG2
ALTER DATABASE PartDB ADD FILEGROUP FG3
ALTER DATABASE PartDB ADD FILEGROUP FG4
  
-- thêm data file vào mỗi filegroup
  
ALTER DATABASE PartDB ADD FILE (NAME = N'F1', FILENAME = N'D:DATAPartDB_F1.ndf') TO FILEGROUP FG1
ALTER DATABASE PartDB ADD FILE (NAME = N'F2', FILENAME = N'D:DATAPartDB_F2.ndf') TO FILEGROUP FG2
ALTER DATABASE PartDB ADD FILE (NAME = N'F3', FILENAME = N'D:DATAPartDB_F3.ndf') TO FILEGROUP FG3
ALTER DATABASE PartDB ADD FILE (NAME = N'F4', FILENAME = N'D:DATAPartDB_F4.ndf') TO FILEGROUP FG4

Phân đoạn bảng Sau khi các bước trên hoàn tất, giờ bạn có thể tạo bảng và đồng thời phân đoạn nó:

CREATE TABLE dbo.TablePart(
PartId INT IDENTITY PRIMARY KEY,
PartData VARCHAR(100)
) ON PartScheme_1(PartId)

Việc phân đoạn được gói gọn ở mệnh đề "ON PartScheme_1(PartId)", và bảng được phân đoạn thông qua partition scheme PartScheme_1 dựa vào trường PartId. Khi đó trường PartId được gọi là partition key.

Lưu ý:

• Trong một Database bạn có thể tạo nhiều partition function. Mỗi partition function có thể được dùng cho nhiều partition scheme. Đến lượt mỗi partition scheme lại được dùng cho nhiều bảng.
• Việc phân đoạn chỉ có thể được thực hiện trên một dải giá trị duy nhất. Có những trường hợp bạn muốn phân đoạn dựa vào 2 hoặc nhiều trường, ví dụ phân đoạn hóa đơn bán hàng theo năm và trong mỗi năm tiếp tục phân chia các đơn hàng có trị giá hơn 1 triệu vào một đoạn và nhỏ hơn 1 triệu vào một đoạn. Khi đó bạn cần tạo một computed field đại diện cho hai trường kia và phân đoạn dựa vào computed field này.