Table Partitioning Trong SQL Server

Vũ Huy Tâm

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. Đâ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 thì 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 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 bảng này vào bảng chính một cách tức thì. 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ở đi vào một đoạn (về sau bạn vẫn luôn luôn có thể sửa đổi để già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 ý là 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, và 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(
BanHang_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 đã đăng trên tạp chí Thế Giới Vi Tính – PC World VN)


Phiên bản áp dụng: SQL Server 2005 trở lên

Tags: , , ,

39 Comments

Các bài viết tương tự

Comments
  • HungDL (16/01/2011 7:40 pm)

    Trong ví dụ ở Bước 4 của bạn. Mình không thấy trường nào được đặt là PK.

    Theo mình đoán thì có lẽ là trường BangHang_ID.

    Nếu như mình đặt thêm lệnh để cho trường BangHang_ID thành PK thì đoạn code trên sẽ sinh ra lỗi. "Partition columns for a unique index must be a subset of the index key."

    Bạn sẽ giải quyết lỗi này như thế nào? Mình đang rất thắc mắc về điều này.

  • Vũ Huy Tâm (19/01/2011 7:55 am)

    Trong ví dụ trên thì primary key sẽ là (NgayGiaoDich,BangHang_ID). Bạn có thể đưa partition key vào primary key để tránh lỗi trên

  • Phihn (26/01/2011 12:07 am)

    Trong ví dụ của bạn là tạo mới 1 table. Hiện mình đã có 1 table và đã có dữ liệu trên 8 triệu record. Bạn có thể giúp mình cách đưa các dữ liệu đã có theo Period vào từng Table Partitioning được không.

  • Vũ Huy Tâm (26/01/2011 6:53 am)

    Bạn có thể làm bằng cách tạo lại clustered index trên partition scheme:

    CREATE UNIQUE CLUSTERED INDEX idxname on tblname(col1,col2)

    WITH DROP_EXSISTING

    ON PSchema(pcol) –pcol là partition column

    vì clustered index chứa dữ liệu, khi bạn tạo lại nó trên partition schema, tức là phân đoạn index, thì dữ liệu cũng được kéo sang và phân đoạn cùng với nó.

    • TuyetHuong (24/05/2012 6:02 am)

      Em đã thử dùng lệnh của a đưa tren bảng đã có record thì báo lỗi nay “Invalid usage of the option DROP_EXSISTING in the CREATE INDEX statement.”

      • Vũ Huy Tâm (25/05/2012 9:33 am)

        hóa ra la DROP_EXISTING, tôi type nhầm :)

  • lâm (07/03/2011 6:08 pm)

    Bạn có thể đưa code thực hiện việc partition primary key được không? Mình thử một số cách mà không thành công. Xin cảm ơn

  • lâm (08/03/2011 7:04 am)

    Mình đã tìm ra cách rồi :)

    Phải Set cho cột primary key not null.

  • Xavier Nguyen (26/03/2011 2:37 am)

    Bài viết hay lắm. Cảm ơn bạn.

  • Nguyen Phuong (24/04/2011 10:54 am)

    Bạn ơi cho mình hỏi chút

    Create Table thongke
    (
    namsinh smallint,
    hoten nvarchar(50)
    )
    ….
    Insert thongke(namsinh, hoten) values (2009, ‘Nguyen Van A’)
    Insert thongke(namsinh, hoten) values (2010, ‘Nguyen Van A’)
    Insert thongke(namsinh, hoten) values (2011, ‘Nguyen Van B’)

    Mình phân table làm 3 đoạn:
    đoạn 1 nhỏ hơn 2009
    đoạn 2 từ 2009 – 2010
    đoạn 3 từ 2010->

    Mình muốn hỏi là nếu sang năm 2012 mình muốn thêm một đoạn nữa thì mình phải làm sao.

    Bạn mới chỉ hưỡng dẫn cách tạo phân đoạn mà không chỉ cách thêm, sửa, xóa mình thấy không được ổ lắm

    Rất mong bạn sớm hồi đáp

  • PhaoThu (06/09/2011 6:12 am)

    Bài viết hay lắm. Bây giờ bạn có thể trình bày thêm về indexed trên một table đã partition được không ?

  • Quy do MU (11/10/2011 10:17 pm)

    Bài viết hay lắm. Cảm ơn bạn. Mình còn có một số thắc mắc giống bạn “Phihn” và bạn “Nguyen Phuong” đó là :

    - Trong ví dụ của bạn là tạo mới 1 table. Hiện mình đã có 1 table và đã có dữ liệu trên 8 triệu record. Bạn có thể giúp mình cách đưa các dữ liệu đã có theo Period vào từng Table Partitioning được không. (“Phihn”)

    - Bạn ơi cho mình hỏi chút

    Create Table thongke
    (
    namsinh smallint,
    hoten nvarchar(50)
    )
    ….
    Insert thongke(namsinh, hoten) values (2009, ‘Nguyen Van A’)
    Insert thongke(namsinh, hoten) values (2010, ‘Nguyen Van A’)
    Insert thongke(namsinh, hoten) values (2011, ‘Nguyen Van B’)

    Mình phân table làm 3 đoạn:
    đoạn 1 nhỏ hơn 2009
    đoạn 2 từ 2009 – 2010
    đoạn 3 từ 2010->

    Mình muốn hỏi là nếu sang năm 2012 mình muốn thêm một đoạn nữa thì mình phải làm sao.

    Bạn mới chỉ hưỡng dẫn cách tạo phân đoạn mà không chỉ cách thêm, sửa, xóa mình thấy không được ổ lắm

    Rất mong bạn sớm hồi đáp (“Nguyen Phuong”)

    Rất mong bạn chỉ giúp

    Thanks

  • Vũ Huy Tâm (26/10/2011 9:00 am)

    Mời bạn xem bài này:
    http://www.sqlviet.com/blog/table-partitioning-split-va-merge

  • HgNamVN (02/12/2011 12:22 am)

    Bài viết rất bổ ích. Cám ơn nhiều.

  • LoLem (10/09/2012 10:44 pm)

    Chào anh Tâm, Em đã xem qua loạt bài về Table Partition, em thấy a dùng partition trên cột có kiểu dữ liệu là datetime và int, nhưng giờ em muốn dung partition trên cột có kiểu dữ liệu là nvarchar hoặc char chia theo thứ tự alpabel được không anh ?????

  • LoLem (10/09/2012 10:44 pm)

    Chào anh Tâm,
    em thấy a dùng partition trên cột có kiểu dữ liệu là datetime và int, nhưng giờ em muốn dung partition trên cột có kiểu dữ liệu là nvarchar hoặc char chia theo thứ tự alpabel được không anh ?????

    • Vũ Huy Tâm (11/09/2012 8:35 am)

      partition trên trường varchar cũng như các kiểu dữ liệu khác, nvarchar thì tôi chưa làm nhưng chắc cũng vậy. Bạn vẫn cần tạo một partition function có kiểu nvarchar hoăc varchar và thực hiện partition như bình thường

  • newbie (26/11/2012 4:27 am)

    ” 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.”
    anh có thể giải thích giùm em đoạn này được không
    cảm ơn anh nhiều

  • Vũ Huy Tâm (26/11/2012 11:35 am)

    Khi bạn phân đoạn index, index cũng được chặt ra từng khúc cùng với bảng, và SQL Server cung cấp tính năng rebuild lại index trên từng đoạn. Ví dụ bạn phân đoạn bảng theo năm, và bạn cần rebuild lại index cho bảng. Vì đoạn cho năm 2011 về trước không có thay đổi gì về dữ liệu nên không cần phải rebuild, trong khi đoạn cho 2012 thì có rất nhiều cập nhật. Vì thế bạn chỉ cần rebuild lại index trên đoạn cho năm 2012.

    --Rebuild only partition x (lưu ý đoạn luôn bắt đầu từ 1)
    ALTER INDEX index_name
    ON table_name
    REBUILD PARTITION = x
  • newbie (27/11/2012 11:36 pm)

    “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.”
    theo em được biết thì lock isolation level mặc định của sql server là read committed. điều này có nghĩa là khi có thao tác insert update vào 1 table nào thì table đó sẽ bị lock không thực hiện được các thao tác khác thậm chí là select . nhưng theo anh nói về kĩ thuật partition thì nó có thể thực hiện song song nếu như dữ liệu insert ở partition khác dữ liệu update date ở partition khác trong cùng một table. không biết em hiểu như vậy có đúng không và nếu đúng hoặc sai thì anh có thể đưa ra ví dụ cụ thể cho em được không.
    vì em mới tìm hiểu kĩ thuật này nên có nhiều thắc mắc. nhưng comment qua blog thì đợi lâu mới có phản hồi nếu được ở trên là địa chỉ yahoo của em anh có thể liên lạc với em qua yahoo được không. em cảm ơn anh nhiều

  • Vũ Huy Tâm (28/11/2012 10:26 am)

    Khi có insert/update thì không nhất thiết toàn bộ bảng bị khóa. SQL Server sẽ bắt đầu bằng khóa bản ghi/trang, rồi tăng đến extent, partition, … nếu thấy cần (cái này gọi là lock escalation). Khóa bảng chỉ xảy ra khi số bản ghi cần update quá nhiều và/hoặc bảng thiếu index để hỗ trợ lệnh update. Vì thế các phần khác của bảng vẫn có thể được select. Khi bảng được partition, nó tạo thêm 1 cấp nữa cho lock escalation, nếu các bản ghi được update chỉ nằm trong 1 đoạn thì mức khóa cao nhất là partition lock chứ không cần tới table lock, nghĩa là chỉ đoạn đó bị khóa còn các đoạn khác vẫn truy nhập được bình thường.
    Đoạn bạn trích dẫn ở trên nói tới khả năng tăng lưu lượng vào/ra cho truy vấn trên cùng 1 bảng, điều mà bảng không partition không làm nổi.

  • newbie (03/12/2012 2:41 am)

    hi
    cảm ơn anh đã support

  • Diện (06/12/2012 12:25 am)

    Hi anh.
    A cho em hỏi là có thể dựa vào partition trên trường của một bảng để tạo partition cho các bảng các bảng khác không?
    Thank anh.

  • ecompc (24/12/2012 12:08 am)

    cho mình hỏi là mình cso dùng table partitioning cho 1 database có sẵn và 1 table có sẳn từ trc đó đc hay không? và nếu có thì cách thức thực hiện như thế nào
    thánk

  • tung (20/02/2014 10:19 pm)

    Bác cho em hỏi nếu dùng như trên thì khi backup data thì phải copy cả nơi chứa filegroup theo phải không ?

    • tung (21/02/2014 12:09 am)

      Cụ thể là khi em tạo FG như ví dụ của bác chẳng hạn, thì sẽ có file tương ứng được tạo ra trên ổ đĩa . VD :

      B1:
      ALTER DATABASE PartTest ADD FILE (NAME = N'FY2009AndBefore', FILENAME = N'D:\DATA\PartTest\FY2009AndBefore.ndf') TO FILEGROUP FG2009AndBefore 
      B2:
      sau đó em tạo BACKUP FG trên và mang sang máy khác RESTORE lại thì không thấy nó tạo FILE "FY2009AndBefore.ndf". Cái này có ảnh hưởng gì không bác , hay là em làm không đúng ạ?
    • ahkhinmat (21/02/2014 10:50 pm)

      Hi mọi người, cảm ơn vì các bài đăng rất hữu ích, cho mình hỏi thêm chức năng Partition Table này có trong phiên bản nào của SQL:
      Standdard ?, Enterprise?… Vì công ty mình đang mua SQL và mình đang phân vân sợ chọn bản SQL Standdard rẻ hơn lại không có tính năng này. Cảm ơn mọi người nhé.

    • Vũ Huy Tâm (24/02/2014 1:12 pm)

      Tùng gửi code backup/restore lên xem thế nào.
      @ahkhinmat: table partitioning chỉ có trong bản enterprise (và development), bản standard ko có.

      • tung (24/02/2014 10:03 pm)

        Em tạo như ví dụ của bác, và backup full . Trong ví dụ của bác khi dùng đoạn code trên máy A:

        ALTER DATABASE PartTest ADD FILE (NAME = N'FY2009AndBefore', FILENAME = N'D:\DATA\PartTest\FY2009AndBefore.ndf') TO FILEGROUP FG2009AndBefore 
        thì nó sẽ tạo 1 FILE vật lý FY2009AndBefore.ndf theo đường dẫn tương ứng ở ổ D .
         
        Khi em RESTORE bản FULL BACKUP đấy sang máy B thì không thấy FILE vật lý tương ứng ấy đâu mặc dù bảng vẫn được phân đoạn . Ý em hỏi là như thế có ảnh hưởng gì không ạ ?
      • tung (26/02/2014 4:32 am)

        Bác có rỗi trả lời dùm em nhé !

      • tung (03/03/2014 4:08 am)

        Em làm từng bước như trên VD của bác và em tạo backup full :

        BACKUP DATABASE TEST TO DISK = 'D:\Backup\Test_FULL.bak' WITH INIT

        thì khi em mang file backup này sang 1 máy khác có cần phải copy mấy cái file “D:\DATA\PartTest\FY2009AndBefore.ndf” theo không a? Và khi restore thì làm như bình thường hay là sao ạ ? Bác trả lời giúp em nhé !

        • Vũ Huy Tâm (04/03/2014 10:19 am)

          Khi bạn backup full thì bản backup có trọn vẹn các thông tin về db, bao gồm cả file mới bạn vừa tạo (tất nhiên nếu file được tạo trước khi backup). Khi restore sang kia thì file mới cũng được restore lại.

          • tung (04/03/2014 9:43 pm)

            Cảm ơn bác nhé ! Tại em không thấy mấy cái file vật lý kia nên nghĩ không được.

  • hant (08/04/2014 3:37 am)

    a Tâm ơi cho e hỏi chut ạ,
    e muốn pảtition theo tháng thì phân đoạn như nào hả a?

  • Hoàng Văn Thái (21/05/2015 4:54 am)

    Em có dữ liệu theo ngày thống kê theo từng tài khoản.
    Dữ liệu ngày càng lớn.
    Em muốn cải thiện tốc độ truy vấn với nhiều điều kiện khac nhau trên bảng thi có thể làm partiton được không và nếu làm theo ngày thi như nào a.
    Anh có thể hướng dẫn không. Thank a:)

  • cuonghv (23/10/2015 12:16 am)

    Mình chỉ là dân ngoại đạo nên có chút thắc mắc ngớ ngẩn:
    Mình có một table rất lớn, giờ nếu mình chỉ đơn giản chia nhỏ table bằng HASH PARTITION thì nó có tác dụng giảm tải không, khi các câu truy vấn của mình vẫn giữ nguyên như cũ?
    Rất mong nhận được giải đáp của bạn.

  • Jack Le (24/07/2017 6:05 am)

    Mọi người cho mình hỏi là khi mình đã partition rồi, khi mình query select lên thì SQL tự động biết khu vực nào để lấy dữ liệu hay mình phải thay đổi câu truy vấn gì không ?

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>