Partition SQL là gì

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')

Bài viết được sự cho phép của tác giả Edward Thiên Hoàng

Phân chia dữ liệu (Sharding) là một giải pháp chia nhỏ một Database lớn thành nhiều Database nhỏ, ta có thể phân tách từng bảng hoặc cả một DB ra nhiều phần nhỏ đặt ở nhiều máy chủ (server) khác nhau. Điều này sẽ giúp cho hệ thống DB của chúng ta đạt được các tính chất khả năng bảo trì (manageability), hiệu xuất (performance), tính sẵn sàng (availability), và cân bằng tải (load balancing) của ứng dụng. Và giải pháp này cũng giảm chi phí cũng như tính mở rộng (scalability) để scale up DB bằng cách dùng nhiều server nhỏ gộp lại hơn là nâng cấp một server lớn.

  26 công cụ và kỹ thuật trong Big Data có thể bạn chưa biết

  Big Data là gì? Tất tần tật về Big Data

Ta có 3 cách thức Sharding dữ liệu như sau:

1. Horizontal sharding
   Là cách chia cùng dữ liệu của cùng một bảng (table) ra nhiều DB khác nhau. Ví dụ ta có bảng dữ liệu thông tin về người dùng, ta sẽ dựa trên location của người dùng để quyết định nó nằm ở DB nào, ví dụ người dùng ở Sài Gòn thì sẽ chứ ở DB_SG, thông tin người dùng ở Biên Hòa sẽ nằm ở DB_BH hay thông tin người dùng ở Vĩnh Long sẽ nằm ở DB_VL.
   Giải pháp này có một vấn đề là ta phải chọn nơi dữ liệu Sharding rất cẩn thận để không gây mất cân bằng (unbalanced) giữa các DB dẫn tới rất có thể có một vài Server sẽ thành điểm nóng (hot spot), ví dụ người dùng ở Sài Gòn chắc chắn là đông hơn rất nhiều lần người dùng ở Biên Hòa hay Vĩnh Long.
2. Vertical sharing
   Là cách sharding dữ liệu dựa trên tính năng (feature) của hệ thống. Ví dụ ta thiết kế một hệ thống chia sẻ ảnh giống Instagram, ta sẽ lưu thông tin của User vào DB_Users, lưu thông tin ảnh họ up lên trên một DB khác là DB_Photos và thông tin danh sách những người họ follow ở một DB thứ 3 là DB_Follow.
   Cách làm này rất là rõ ràng dễ để implement và không làm ảnh hưởng lớn đến ứng dụng, nhưng khi hệ thống lớn dần lên thì dữ liệu cũng lớn dần theo, do đó ta lại phải thực hiện sharding tiếp những DB trên từng feature (bởi vì 1 DB không thể sử lý 10 tỷ bức ảnh của 140 triệu user được).
3. Directory Based sharding
   Cách này sẽ yêu cầu ta phải thiết kế một “lookup service” có tác dụng quyết định ánh xạ (mapping) dữ liệu sẽ nằm ở đâu, DB nào. Mỗi khi có request ghi hoặc đọc sẽ thông qua lookup service để mapping vị trí sẽ đọc và ghi. Khi business mở rộng số lượng server có thể tăng lên mà không ảnh hưởng hay đòi hỏi ứng dụng phải thay đổi theo.

Bên trên ta đã tìm hiểu và các method để sharding dữ liệu, giờ ta hãy tìm kiểu sâu hơn về các tiêu chí để phân vùng dữ liệu.

1. Phân vùng theo key hoặc hash
   Hệ thống sẽ áp dụng các hàm băm (hash function) cho một hoặc nhiều các key chính trong dữ liệu (thường là ID) để xác định ra một con số của phân vùng nó đang nằm. Ví dụ: nếu hệ thống có 100 máy chủ DB và ID của bản ghi sẽ tự tăng lên mỗi lần một bản ghi mới được chèn. Trong ví dụ này, hàm băm có thể là ‘ID% 100, từ đó ta có thể xác định vị trí của dữ liệu. Cách tiếp cận này cần đảm bảo phân bổ dữ liệu thống nhất giữa các máy chủ. Nhưng cách làm này có một điểm yếu là mỗi khi ta thay đổi số lượng Server tăng hoặc giảm thì hàm băm cũng sẽ phải thay đổi theo và sẽ xuất hiện hiện tượng xáo trộn tập dữ liệu trên mỗi server, và đòi hỏi ta phải phân phối lại dữ liệu và xuất hiện độ trễ dữ liệu. Có một cách giải quyết vấn đề này là sử dụng Consistent Hashing (hàm băm nhất quán).
2. Phân vùng theo danh sách (list)
   Cách phân vùng sẽ được quyết định gán một danh sách các giá trị ngay từ đầu, từ đó mỗi lần ghi một bản ghi mới ta sẽ tìm ra giá trị của bản ghi nằm ở phân vùng nào và ghi vào đó. Ví dụ ta sẽ quyết định nhóm tất cả các người dùng ở Ai-len, Na Uy, Thụy Điển, Phần Lan và Đan Mạch và một phân vùng có tên là Nordic (Bắc Âu).
3. Phân vùng vòng tròn (round-robin) 
   Cách phân vùng này rất đơn giản là mỗi lần có thao tác ghi ta sẽ ghi vòng tròn quanh các phân vùng có sẵn. Cách làm này đơn giản nhưng rất khó để xác định dữ liệu nào ở đâu lấy ra khi cần.
4. Phân vùng tổng hợp
   Là cách tổng hợp các giải pháp trên thành một giải pháp mới. Ví dụ ta có thể áp dụng phân vùng theo Key/Hash và sau đó xác định được key rồi ta sẽ áp dụng tiếp phân vùng theo danh sách để chứa key sau khi hash vào một danh sách cụ thể nào đó. Consistent Hashing có thể được coi là cách phân vùng tổng hợp.

Vì việc dữ liệu sẽ bị phân tán đi nhiều Server khác nhau do vậy sẽ phát sinh một vài vấn đề khi sharding dữ liệu như sau:

1. Joins and De-normalization
   Bởi vì việc dữ liệu ở các bảng được phân bố và trải rộng đi nhiều DB/Server khác nhau nên việc join bảng dữ liệu là điều rất khó khăn và cũng không đem lại hiệu xuất bởi vì việc dữ liệu phải được queries từ nhiều máy chủ khác nhau. Để giải quyết vấn đề này ta có thể thiết kế dữ liệu dạng non-relationship DB hay còn gọi là NoSQL, giống như MongoDB hay Cassandra hai hệ NoSQL rất nổi tiếng và hỗ trợ Sharding vô cùng tốt. Tuy nhiên việc này ta phải chấp nhận rủi ro việc không nhất quán dữ liệu (inconsistency)
2. Referential integrity
   Cũng vì lý do trên về việc truy vấn chéo dữ liệu giữa các DB nằm trên các máy chủ khác nhau là bất khả thi, do vậy việc ràng buộc khóa ngoại để bảo đảm sự toàn vẹn dữ liệu cũng là một điều vô cùng khó khăn. Hầu hết RDBMS không hỗ trợ các ràng buộc khóa ngoại trên các cơ sở dữ liệu phân tán trền nhiều server. Do vậy để đạt được điều này ta phải thực hiện điều này trên mã ứng dụng (code), điều này sẽ tăng tính phức tạp của ứng dụng.
3. Rebalancing Trong suốt quá trình hệ thống vận hành có rất nhiều lý do ta thay đổi các chiến thuật hay cách thức Sharding dữ liệu, như business tăng trưởng yêu cầu thêm Server… Và mỗi khi như vậy ta phải tái cân bằng (rebalancing) dữ liệu trên tất cả các Server, có nghĩa là dữ liệu phải được phân phối lại trên toàn bộ server. Để làm được điều này mà không có độ trễ (downtime) là cực kỳ khó khăn. Mô hình sharding “Directory Based” có khả năng rebalancing tốt nhất nhưng lại tăng độ phức tạp của ứng dụng và tạo thêm một single point of failure (ví dụ “lookup service”).

   Tóm lại với Sharding thì mô hình sharding với key/hash với Consistent Hashing hiện tại là giải pháp tối ưu nhất cho việc Rebalancing.

Theo medium

Bài viết gốc được đăng tải tại edwardthienhoang.wordpress.com

Có thể bạn quan tâm:

Xem thêm Việc làm Developer hấp dẫn trên TopDev