Phân đoạn bảng trong SQL Server
Kỹ thuật phân chia bảng thành từng đoạn (Table partitioning) nhằm quản lý
hiệu quả cơ sở dữ liệu với dung lượng lớn.
>>> Thiết lập SQL Server luôn sẵn sàng
Đây là tính năng mới được đưa vào SQL Server 2005 và tiếp tục được tăng
cường ở phiên bản 2008. Đối với các ứng dụng truy cập từ bên ngoài, bảng
(table) vẫn là một bảng duy nhất, chỉ có cấu trúc vật lý của nó là khác so với
các bảng không phân đoạn.
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). Ví dụ bạn có dữ liệu về các giao dịch bán hàng chứa trong
bảng BanHang, bạn có thể phân đoạn theo năm của trường NgayGiaoDich
(ngày giao dịch): các giao dịch xảy ra trong năm 2009 được nằm trong một
đoạn riêng, tương tự với các giao dịch của năm 2010… 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. Thử hình dung với một 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. Việc truy
vấn hoặc sửa đổi dữ liệu cũng rất vất vả. Table partitioning nhằm giải quyết
các trở ngại đó, nó có các ưu điểm chính sau:
1. Tiện lợi về quản trị:
- Bạn có thể backup/restore một đoạn mà không ảnh hưởng đến các đoạn còn
lại. Ví dụ, tại thời điểm năm 2010 các đoạn chứa dữ liệu của 2009 và các năm
trước không còn tiếp nhận dữ liệu mới nữa, bạn không cần phải thường xuyên
backup các đoạn này và chỉ cần backup đoạn 2010.
- Bạn cũng có thể REBUILD lại index trên từng đoạn (những đoạn cần phải
REBUILD do có nhiều thao tác xóa, sửa) thay vì trên toàn bộ bảng.
- Nó cũ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 (thao tác này gọi là 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). Tính năng này rất có giá trị đối với các
ứng dụng ETL (Extract,
Transform & Load) và Datawarehouse.
Ví dụ bạn cần import dữ liệu của năm 2008, bạn có thể import vào một bảng
riêng và sau đó switch-in tức thì bảng này vào bảng chính. Trước khi có
partitioning, bạn phải dùng lệnh INSERT để chuyển dữ liệu từ bảng riêng vào
bảng chính. Quá trình này mất nhiều thời gian hơn và trong suốt quá trình đó
bảng bị khóa và không thể truy cập được.
2. Cải tiến về hiệu năng:
- Khi một câu lệnh chỉ cần lấy dữ liệu ở một đoạn nào đó thì hệ thống chỉ cần
truy nhập vào đoạn đó và bỏ qua các đoạn còn lại (tính năng này gọi là
partition elimination)
- Khi các đoạn dữ liệu được lưu trữ ở các ổ cứng khác nhau sẽ làm giảm tranh
chấp vào/ra 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).
Dưới đây tôi sẽ đi qua từng bước thiết lập việc phân đoạn thông qua một ví dụ
cụ thể.
Bạn có bảng BanHang gồm các cột BangHang_ID, NgayGiaoDich, MaSP,
SoLuong, ThanhTien. Bạn muốn phân đoạn bảng theo từng năm của
NgayGiaoDich: Đ
ể cho đơn giản, giả sử bạn muốn lưu các giao dịch của năm
2009 trở về trước vào một đoạn, trong năm 2010 vào một đoạn, và từ 2011 trở
lên vào một đoạn (về sau bạn vẫn luôn luôn có thể sửa đổi để dành riêng một
đoạn cho 2011 và bổ sung các đoạn mới cho 2012, 2013…). Như vậy với cấu
hình ở trên, bảng sẽ có 3 đoạn: 2009 trở về trước, 2010, và 2011 trở về sau. Do
đó bạn cũng cần 3 filegroup.
Bước 1: Tạo database và filegroup
CREATE DATABASE PartTest
GO
USE PartTest
GO
-- tạo filegroup
ALTER DATABASE PartTest ADD FILEGROUP FG2009AndBefore
ALTER DATABASE PartTest ADD FILEGROUP FG2010
ALTER DATABASE PartTest ADD FILEGROUP FG2011AndAfter
-- thêm data file vào mỗi filegroup
ALTER DATABASE PartTest ADD FILE (NAME = N'FY2009AndBefore',
FILENAME = N'D:\DATA\PartTest\FY2009AndBefore.ndf') TO
FILEGROUP FG2009AndBefore
ALTER DATABASE PartTest ADD FILE (NAME = N'FY2010', FILENAME
= N'D:\DATA\PartTest\FY2010.ndf') TO FILEGROUP FG2010
ALTER DATABASE PartTest ADD FILE (NAME = N'FY2011AndAfter',
FILENAME = N'D:\DATA\PartTest\FY201AndAfter.ndf') TO FILEGROUP
FG2011AndAfter
Bước 2: Tạo partition function và partition scheme
USE PartTest
GO
CREATE PARTITION FUNCTION PFunc_NGD(DATETIME) AS RANGE
RIGHT FOR VALUES ('2010-01-01', '2011-01-01')
GO
CREATE PARTITION SCHEME PScheme_NGD AS PARTITION
PFunc_NGD TO (FG2009AndBefore, FG2010, FG2011AndAfter)
Hàm partition function có tên PFunc_NGD định nghĩa giá trị biên cho các
đoạn, là ngày đầu tiên của năm 2010 và ngày đầu tiên của 2011. Giống nh
ư khi
bạn cắt một sợi dây, chỉ cần 2 nhát cắt để chia sợi dây làm 3 đoạn, ở đây cũng
chỉ có 2 giá trị biên. Do vậy dải giá trị của các đoạn sẽ như sau:
Đoạn 1: Từ trước đến 2009-12-31 23:59:59
Đoạn 2: 2010-01-01 00:00:00 đến 2010-12-31 23:59:59
Đoạn 3: 2011-01-01 00:00:00 về sau
Sau đó partition scheme PScheme_NGD dùng hàm PFunc_NGD để “gắn” các
đoạn vào từng filegroup. Như vậy đoạn 1 sẽ đến FG2009AndBefore, đoạn 2
đến FG2010 và đoạn 3 đến FG2011AndAfter.
Lưu ý, partition function không giống với các user-defined function. Trong
Management Studio, bạn thấy partition function và partition scheme ở mục
Database/Storage.
Một lưu ý nữa là một partition function có thể được dùng cho nhiều partition
scheme, cả hai là các đối tượng chung trong database chứ không gắn liền với
một bảng cụ thể. Khi định nghĩa bảng (xem bước 4) bạn cần chỉ định dùng
partition scheme nào.
Bước 4: Tạo bảng dùng partition scheme
USE PartTest
GO
CREATE TABLE dbo.BanHang(
BangHang_ID INT IDENTITY,
NgayGiaoDich DATETIME,
MaSP INT,
SoLuong INT,
ThanhTien INT
) ON PScheme_NGD(NgayGiaoDich)
GO
CREATE CLUSTERED INDEX CI_BanHang_NGD ON
dbo.BanHang(NgayGiaoDich) ON PScheme_NGD(NgayGiaoDich)
Mệnh đề “ON PScheme_NGD(NgayGiaoDich)” trong hai lệnh tạo bảng và t
ạo
index ở trên chỉ định bảng BanHang và index CI_BanHang_NGD được tạo
trên partition scheme PScheme_NGD, có nghĩa là để cho nó quản lý việc phân
bổ dữ liệu. Vậy là bảng BanHang đã được phân đoạn. Bạn có thể kiểm tra xem
dữ liệu được ghi vào đoạn nào:
SELECT $PARTITION.PFunc_NGD('2008-07-24')
SELECT $PARTITION.PFunc_NGD('2009-12-31')
SELECT $PARTITION.PFunc_NGD('2010-01-01')
SELECT $PARTITION.PFunc_NGD('2010-11-25')
SELECT $PARTITION.PFunc_NGD('2011-03-16')
Theo PCWorld VN
