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....
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 (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ăngcườ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ớicá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ọilà partition key). Ví dụ bạn có dữ liệu về các giao dịch bán hàng chứa trongbả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ăngkhả 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áccơ 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ệuchứ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 truyvấn hoặc sửa đổi dữ liệu cũng rất vất vả. Table partitioning n hằm giải quyếtcá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 ònlạ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ămtrướ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ênbackup 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ảiREBUILD 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ảngthay vì phải dùng lệnh DELETE (thao tác này gọi là SWITCH-OUT). Tươngtự 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ảngriê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àobả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ầntruy 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 tranhchấ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àntoà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àmnà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ácfilegroup (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ủaNgayGiaoDich: Để cho đơn giản, giả sử bạn muốn l ưu các giao dịch của năm2009 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ấuhì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à filegroupCREATE DATABASE PartTestGOUSE PartTestGO-- tạo filegroupALTER DATABASE PartTest ADD FILEGROUP FG2009AndBeforeALTER DATABASE PartTest ADD FILEGROUP FG2010ALTER DATABASE PartTest ADD FILEGROUP FG2011AndAfter-- thêm data file vào mỗi filegroupALTER DATABASE PartTest ADD FILE (NAME = NFY2009AndBefore,FILENAME = ND:DATAPartTestFY2009AndBefore.ndf) TOFILEGROUP FG2009AndBeforeALTER DATABASE PartTest ADD FILE (NAME = NFY2010, FILENAME= ND:DATAPartTestFY2010.ndf) TO FILEGROUP FG2010ALTER DATABASE PartTest ADD FILE (NAME = NFY2011AndAfter,FILENAME = ND:DATAPartTestFY201AndAfter.ndf) TO FILEGROUPFG2011AndAfterBước 2: Tạo partition function và partition schemeUSE PartTestGOCREATE PARTITION FUNCTION PFunc_NG D(DATETIME) AS RANGERIGHT FOR VALUES (2010-01-01, 2011-01-01)GOCREATE PARTITION SCHEME PScheme_NGD AS PARTITIONPFunc_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ư khibạn cắt một sợi dây, chỉ ...