Phân đoạn bảng trong SQL Server

Phân đoạn bảng trong SQL ServerKỹ 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. Đâ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....
Nội dung trích xuất từ tài liệu:
Phân đoạn bảng trong SQL Server Phân đoạn bảng trong SQL ServerKỹ thuật phân chia bảng thành từng đoạn (Tablepartitioning) nhằm quản lý hiệu quả cơ sở dữ liệu với dunglượng lớn.Đây là tính năng mới được đưa vào SQL Server 2005 và tiếptục được tăng cường ở phiên bản 2008. Đối với các ứng dụngtruy 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ôngphâ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ệuvề 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 giaodịch): các giao dịch xảy ra trong năm 2009 được nằm trongmộ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ấtnhiề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ămtriệ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ờigian. Việc truy vấn hoặc sửa đổi dữ liệu cũng rất vất vả. Tablepartitioning 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ạnchứa dữ liệu của 2009 và các năm trước không còn tiếp nhậndữ 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ácnà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ể importvào một bảng riêng và sau đó switch-in tức thì bảng này vàobảng chính. Trước khi có partitioning, bạn phải dùng lệnhINSERT để 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âulệnh SELECT và UPDATE hoạt động trên cùng một bảngnhưng ở hai đoạn khác nhau có thể thực hiện hoàn toàn songsong 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ộcvào.- Partition scheme: Ánh xạ các đoạn khai báo trong partitionfunction vào các filegroup (mỗi đoạn được lưu trữ tại mộtfilegroup).Dưới đây tôi sẽ đi qua từng bước thiết lập việc phân đoạnthô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ướcvà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 cho2012, 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ạncũng cần 3 filegroup.Bước 1: Tạo database và filegroupCREATE DATABASE PartTestGOUSE PartTestGO-- tạo filegroupALTER DATABASE PartTest ADD FILEGROUPFG2009AndBeforeALTER DATABASE PartTest ADD FILEGROUPFG2010ALTER DATABASE PartTest ADD FILEGROUPFG2011AndAfter-- thêm data file vào mỗi filegroupALTER DATABASE PartTest ADD FILE (NAME =NFY2009AndBefore, FILENAME =ND:DATAPartTestFY2009AndBefore.ndf)TO FILEGROUP FG2009AndBeforeALTER DATABASE PartTest ADD FILE (NAME =NFY2010, FILENAME =ND:DATAPartTestFY2010.ndf) TOFILEGROUP FG2010ALTER DATABASE PartTest ADD FILE (NAME =NFY2011AndAfter, FILENAME =ND:DATAPartTestFY201AndAfter.ndf) TOFILEGROUP FG2011AndAfterBước 2: Tạo partition function và partition schemeUSE PartTestGOCREATE PARTITION FUNCTIONPFunc_NGD(DATETIME) AS RANGE RIGHT FORVALUES (2010-01-01, 2011-01-01)GOCREATE PARTITION SCHEME PScheme_NGD ASPARTITION 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 đầutiên của 2011. Giống như khi bạn cắt một sợi dây, chỉ cần 2nhá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 ...