Index Partitioning

Vũ Huy Tâm

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.




Tags: , , , , ,

13 Comments
Posted on 7/5/2013 | Categories: Index, Table partitioning, Thiết kế database

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

Comments
  • synv (27/08/2013 12:45 am)

    Anh cho em hỏi:
    em phân đoạn 1 bảng dữ liệu đã có dữ liệu sẵn gồm 4 khoá, trong đó trường Id kiểu dữ liệu indentity làm khoá chính, em phân đoạn theo trường paration key là SubmitTime kiểu dữ liệu là datetimei bằng cách gán cluster index, sau khi phân đoạn xong em chuyển trường này lại thành non- cluster
    khi phân đoạn xong thì chỉ có mỗi Index SubmitTime là phân đoạn còn các Index khác gồm khoá chính và các Non- cluster đều không phân đoạn, vẫn là 1 khối. Xin hỏi làm cách nào để tất cả các index đều phân đoạn

    • Vũ Huy Tâm (27/08/2013 9:44 am)

      Bạn làm như vậy thì chỉ có index trên SubmitTime là được phân đoạn, còn bảng không được phân đoạn nữa. Bạn có 2 lựa chọn:
      - Phân đoạn index trên trường Id, bạn phải thêm trường SubmitTime vào index đó. Khi đó trường Id không còn đảm bảo tính duy nhất được nữa.
      - Giữ trường Id là duy nhất, bạn cần tạo unique index cho trường Id trên PRIMARY filegroup. Khi đó index trên trường Id không được phân đoạn

  • synv (28/08/2013 3:42 am)

    có cách nào, ko muốn thêm trường SubmitTime vào khoá chính mà các index khác vẫn phân đoạn không. Và anh cho em hỏi nếu các index ko được phân đoạn theo dữ liệu thì có bị ảnh hưởng đến hiệu suất như thế nào. Đọc trong blog của anh, thấy rằng nếu các index ko đc phân đoạn theo dữ liệu thì sẽ không dùng được switch in và switch out. Vì bảng này đã có sẵn dữ liệu , ko dùng cách tạo bảng mới nên hơi khó về vấn đề phân chia index

    • Vũ Huy Tâm (28/08/2013 1:42 pm)

      Rất tiếc là không được. Để được phân đoạn thì SubmitTime nhất định phải có mặt trong index.
      Nếu index không phân đoạn thì nó bị hạn chế bớt khả năng. Cũng giống như khi bảng không được phân đoạn. Đúng là bạn sẽ không switch in/out đoạn được. Để làm vậy bạn cần drop index trước và sau đó create index để tạo lại.
      Với bảng đã có sẵn bạn cũng có thể thay đổi cách phân đoạn nếu thấy 1 cách khác hợp lý hơn. Bạn hãy nhớ là clustered index chính là bảng, nên bạn chỉ việc recreate lại clustered index trên một partition scheme mới là toàn bộ bảng được kéo sang theo.

      • ikkistar88 (10/11/2014 11:58 pm)

        T/G a Tâm

        Các bảng trong db thường có ràng buộc quan hệ, field này là khóa ngoại, field kia là khóa chính, vậy việc drop index rồi recreate index có hợp lý trong thực tế không anh? Ví dụ trên, field id là khóa ngoại bảng khác, vậy drop unique index trên id đồng nghĩa với việc drop foreign key trên bảng khác? Lần mò để xóa như vậy có phải là cách hợp lý ko?

        • Vũ Huy Tâm (11/11/2014 4:34 pm)

          Đúng là như vậy nhưng mình không biết có cách nào khác. Nếu một trường đang làm tham chiếu cho khóa ngoại của bảng khác thì bắt buộc nó phải unique, khi đó cách hợp lý nhất là giữ nó unique và không partition. Nói chung database nào cũng có nhiều mục tiêu mâu thuẫn nhau, và người thiết kế phải dung hòa và khi cần phải hy sinh yếu tố này để ưu tiên yếu tố kia. Mình đã gặp nhiều bảng được phân đoạn và vẫn có unique index không phân đoạn, vì cột đó cần phải unique theo yêu cầu ràng buộc dữ liệu và do đó không thể phân đoạn được.

  • synv (29/08/2013 11:17 pm)

    vâng. cảm ơn anh. em đã thực hiện recreate khoá chính của bảng đó là Id, tạo lại khoá chính Id đó với Non- clusterIndex, để cho trường SubmitTime là cluster, thì khi đó tạo đc phân đoạn theo submitTime và khoá chính vẫn là Id thôi, nhưng phát hiện ra là Id, và 3 Non- cluster ko phân đoạn theo dữ liệu mà chỉ có 1 mình SubmitTime là phân đoạn theo dữ liệu. bây h chắc chỉ còn các là gán khoá chính gồm 2 trường là Id và SubmitTime., còn 3 Noncluster kia thì chỉ cần drop đi và creater lại là sẽ phân chia theo dữ liệu. Giải pháp này là đúng chưa ạ ?. e đang rất cần tính năng swith in và switch out.

    • Vũ Huy Tâm (30/08/2013 9:04 am)

      Bạn làm vậy là được – tạo khóa chính gồm Id và SubmitTime, nên để Id trước nếu hay query bằng Id. Nên tạo thêm unique index trên Id để đảm bảo nó duy nhất, index này sẽ không được phân đoạn nhưng khi cần switch in/out thì bạn drop nó đi và create lại

      • tung (20/02/2014 11:49 pm)

        Bác cho em hỏi cái ID để trước khác gì so với để sau ạ?

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

          ID để trước thì index mới dùng được ở những câu query dựa vào ID. Nếu ID để sau thì chỉ dùng được ở những query có filter ở tất cả các cột đứng trước ID. Ví du:
          Nếu index có 3 cột theo đúng thứ tự ID, a, b, hững câu query có dạng WHERE ID = xyz hoặc WHERE ID = xyz AND a = ‘sdfdsgd’ đều được index hỗ trợ
          Nếu 3 cột theo thứ tự a, b, ID thì chỉ những câu WHERE a = ‘sdfsdf’ AND b = ‘edrte45′ and ID = xyz mới dùng được index

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

            Em hiểu rồi ạ ! Cảm ơn bác nhiều.

  • ikkistar88 (10/11/2014 11:46 pm)

    Thân gửi anh Tâm,
    Tôi đã đọc qua bài viết của anh về table partition. Lời đầu tiên cảm ơn anh, vì bài viết này cũng như toàn bộ các bài viết của anh!
    Tôi có một vài điểm còn khúc mắc, về cơ chế backup/restore filegroup, anh có thể nói rõ hơn về vấn đề này được không?

    • Red Devilic (11/11/2014 12:51 am)

      Bạn có thể nêu rõ thắc mắc gì ở phần backup/restore filegroup ?

      Về nguyên tắc thì nó không khác gì backup/restore DB mà chỉ là backup/restore một phần của DB thôi ( Một DB có nhiều filegroup).

      P/S: Bạn có thể post câu hỏi ở trang SQL QA để được trả lời nhanh và cụ thể hơn.

      Thanks.

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>