Index Partitioning

Bài viết tham khảo tại: http://www.sqlviet.com/blog/index-partitioning

Với một bảng đã được phân đoạn, mỗi index được tạo ra theo mặc định được phân đoạn theo giống như bảng. Ví dụ bảng được phân đoạn theo năm của ngày giao dịch thì index cũng được phân đoạn theo cách đó. Index khi đó được gọi là aligned với bảng. Tuy nhiên bạn cũng có thể không phân đoạn cho index và để nó nguyên một khối, hoặc phân đoạn theo cách khác. Khi đó index được gọi là non-aligned.
Bạn hãy hình dung bảng như một cái bánh tét, và index là sợi lạt buộc xung quanh. Khi phân đoạn, chiếc bánh được cắt thành từng lát. Nếu sợi lạt cũng được cắt theo và mỗi sợi con giờ buộc xung quanh từng lát bánh, sợi lạt được gọi là aligned với chiếc bánh. Nếu sợi lạt không được cắt và vẫn bao xung quanh cả chiếc bánh to, sợi lạt được gọi là non-aligned với chiếc bánh.
SQL Server luôn ưu tiên phân đoạn index để nó aligned với bảng và luôn cố gắng phân đoạn khi có thể, vì nó đem lại nhiều thuận lợi cho các tác vụ trên bảng. Tuy nhiên khi đi vào chi tiết từng loại index, cách cư xử này được thể hiện qua những sắc thái khác nhau, ta sẽ xem xét kỹ hơn ở dưới đây.
Non-clustered Index Khi bạn tạo nonclustered index mà không chỉ định filegroup, SQL Server sẽ mặc định tạo index trên partition scheme của bảng, nghĩa là phân đoạn index cùng với bảng:

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 INDEX CI_BanHang_MaSP ON dbo.BanHang(MaSP)
GO

Bạn sẽ thấy index cũng được phân đoạn bằng partition scheme PScheme_NGD:

--code kiểm tra xem index có được phân đoạn
SELECT i.name AS IndexName, i.type_desc, ps.name AS PartitionName
FROM SYS.indexes i
JOIN SYS.partition_schemes ps
 ON ps.data_space_id = i.data_space_id 
WHERE i.name = 'IX_BanHang_MaSP'

Đồng thời index cũng tự động được thêm trường partition key là NgayGiaoDich, mặc dù bạn chỉ tạo index trên trường MaSP:

--code để liệt kê các cột chứa trong index
SELECT ind.name AS Index_Name, col.name AS Column_Name
FROM SYS.indexes ind 
INNER JOIN SYS.index_columns ic 
    ON  ind.OBJECT_ID = ic.OBJECT_ID and ind.index_id = ic.index_id 
INNER JOIN SYS.COLUMNS col 
    ON ic.OBJECT_ID = col.OBJECT_ID and ic.column_id = col.column_id 
WHERE ind.name = 'IX_BanHang_MaSP'

Ở ví dụ ở trên, index là non-unique. Khi bạn tạo unique index, tình hình sẽ khác đi một chút. SQL Server không thể tự động thêm trường partition key vào index vì như thế sẽ làm thay đổi tính chất của index. Ví dụ khi bạn tạo unique index trên trường BangHang_ID, trường này chỉ được chứa các giá trị duy nhất trong toàn bảng. Nếu SQL Server thêm trường NgayGD vào index, index đó sẽ chỉ đảm bảo tính duy nhất cho cặp BanHang_ID+NgayGD và khi đó BanHang_ID vẫn có thể chứa các giá trị lặp lại miễn là khác NgayGD. Vì thế, thay vì tự động sửa index, SQL Server sẽ kiểm tra xem index có chứa trường partition key hay không, nếu không thì nó báo lỗi và không cho tạo index. Ví dụ, lệnh tạo index sau sẽ báo lỗi:

CREATE UNIQUE INDEX UI_BanHang_BanHang_ID ON dbo.BanHang(BanHang_ID)

Msg 1908, Level 16, State 1, Line 1
Column ‘NgayGiaoDich’ is partitioning column of the index ‘UI_BanHang_BanHang_ID’. Partition columns for a unique index must be a subset of the index key.

Để khắc phục, bạn cần chỉ định rõ filegroup cho index:

CREATE UNIQUE INDEX UI_BanHang_BanHang_ID ON dbo.BanHang(BanHang_ID) ON [PRIMARY]

Khi đó index được tạo ra trên PRIMARY filegroup thay vì trên partition scheme PScheme_NGD, nói cách khác là nó không được phân đoạn theo bảng.

Clustered Index Vì clustered index chính là bảng nên nó đương nhiên phải được phân đoạn cùng với bảng. Cũng giống như trên, nếu index là non-unique SQL Server sẽ tự động bổ sung partition key vào index và phân đoạn nó. Còn nếu index là unique, trường partition key bắt buộc phải có mặt trong index nếu không sẽ bị lỗi. Tuy nhiên lưu ý là khi bạn chỉ định filegroup cho index, toàn bộ bảng sẽ được kéo sang filegroup mới và bảng sẽ không còn được phân đoạn nữa. Vì thế bạn chỉ nên làm điều này khi chủ định thay đổi cấu trúc lưu trữ vật lý của bảng.
Bảng dưới đây tóm tắt các loại index và các tình huống tương ứng:

Loại Index	Không chỉ định filegroup	Chỉ định filegroup
Non-Clustered non-unique
- Không chứa partition key	aligned, partition key được thêm vào index	not partitioned, non-aligned
- Chứa partition key	partitioned, aligned	not partitioned, non-aligned
Non-clustered unique
- Không chứa partition key	Lỗi, cần thêm partition key vào index	not partitioned, non-aligned
- Chứa partition key	partitioned, aligned	not partitioned, non-aligned
Clustered non-unique
- Không chứa partition key	partitioned, aligned, partition key được thêm vào index
- Chứa partition key	partitioned, aligned
Clustered unique
- Không chứa partition key	Lỗi, cần thêm partition key vào index
- Chứa partition key	partitioned, aligned

Như vậy bạn thấy index luôn được phân đoạn cùng với bảng chỉ trừ trường hợp với non-clustered index và bạn chỉ định filegroup cho nó. Vì sao SQL Server luôn ưu tiên phân đoạn index? Lý do là vì những lợi ích sau:
- Bạn có thể backup/restore từng đoạn của bảng và index cũng được backup/restore theo.
- Bạn có thể rebuild hoặc reorganize lại index trên từng đoạn mà bạn thấy cần phải rebuild hoặc reorganize
- Bạn có thể thực hiện các thao tác trên từng đoạn dễ dàng như switch/switch out hoặc split/merge
- Bộ optimizer khi sử dụng aligned index có thể áp dụng loại bỏ các đoạn không chứa dữ liệu cần cho câu lệnh
Hãy liên hệ bảng với chiếc bánh tét như đã nêu ở đầu bài, tất cả các thao tác kể trên có thể thực hiện dễ dàng nếu như từng lát bánh có một sợi lạt riêng. Còn nếu vẫn còn một sợi lạt buộc vòng tất cả các lát bánh với nhau (non-aligned index) thì các thao tác này sẽ bị sợi lạt đó làm vướng và không thực hiện được.

Vậy trong trường hợp nào vẫn cần non-aligned index? Khi bạn cần tạo unique index (non-clustered) trên một cột để đảm bảo tính duy nhất cho nó, bạn không có cách nào khác là tạo index trên một filegroup khác và để nó non-aligned với bảng.