Clustered Index và Non-Clustered Index là gì?

Index trong SQL Server là một cấu trúc dữ liệu được lưu trữ trên ổ cứng tương ứng với một table hoặc view nhằm mục đích tăng tốc độ việc truy xuất dữ liệu từ table hoặc view đó.

Một index chứa các keys được xây dựng từ một hoặc nhiều cột trong table hoặc view.

Các key này được lưu trong một cấu trúc dạng B-Tree và cho phép SQL Server tìm bản ghi hoặc các tập hợp bản ghi tương ứng với những key này một cách nhanh chóng.

Một table hoặc View có thể chứa hai loại index sau:

  1. Clustered Index:
    • Clustered Index lưu trữ và sắp xếp dữ liệu vật lý trong table hoặc view dựa trên các giá trị khóa của chúng. Các cột khóa này được chỉ định trong định nghĩa index. Mỗi table hoặc view chỉ có duy nhất một Clusterd Indexvì bản thân các dòng dữ liệu được lưu trữ và sắp xếp theo thứ tự vật lý dựa trên các cột trong loại Index này.
    • Khi dữ liệu trong table hoặc view cần được lưu trữ và sắp xếp theo một thứ tự nhất định chính là lúc cần dùng đến Clustered Index. Khi một table có một Clusted Indexthì khi đó table được gọi là Clustered Table. Nếu không, các dòng dữ liệu của table được lưu trong một cấu trúc không được sắp xếp gọi là HEAP.
  2. Non-Clustered Index:
    • Non-Clustered có một cấu trúc tách biệt với data row trong table hoặc view. Mỗi một index loại này chứa các giá trị của các cột khóa trong khai báo của index, và mỗi một bản ghi giá trị của key trong index này chứa một con trỏ tới dòng dữ liệu tương ứng của nó trong table.
    • Mỗi con trỏ từ một dòng của Non-Clustered index tới một dòng dữ liệu trong table được gọi là  “row locator”. Cấu trúc của row locator phụ thuộc vào việc các trang dữ liệu được lưu trong HEAP hay trong một Clustered Table như đã diễn giải ở mục Clustered Index ở trên. Đối với HEAP, row locator là một con trỏ tới dòng dữ liệu, với clustered table, row locator chính là khóa index của clustered index.
    • Bạn có thể thêm vào các cột không phải là khóa vào một index có sẵn với giới hạn là 900 bytes và 16 cột. Vui lòng xem thêm về Included Index.

Cả Clustered IndexNon-Clustered Index đều có thể là unique. Điều này có nghĩa là không có hai dòng nào có sự trùng lặp giá trị trong các cột tạo ra index.

Index cũng được tự động bảo trì mỗi khi dữ liệu của table có sự thay đổi.

Indexes và Constraints
Index được tự động tạo ra mỗi khi chúng ta tạo Primary Key hoặc Unique Contraints. Ví dụ bạn tạo một cột ID trên table và set Primary key cho nó thì SQL Server tự động tạo ra Primary Key contraints và index cho nó.

 

Index trong SQL Server được sử dụng như thế nào bởi trình Query Optimizer để tối ưu hóa câu lệnh SQL?

Một database được thiết kế tốt sẽ làm giảm các hoạt động I/O trên ổ đĩa và tiêu tốn ít tài nguyên hệ thống hơn, do đó làm tăng hiệu suất của các câu lệnh SQL. Index rất hữu ích cho các loại câu lệnh khác nhau trong SQL như Inser, Update, Delete hoặc Merge… Hãy xem xét câu lệnh sau chạy trên database AdventureWorks2012:

SELECT Title, HireDate FROM
HumanResources.Employee WHERE EmployeeID = 250

Khi câu lệnh này thực thi, trình query optimizer sẽ đánh giá các phương thức để lấy dữ liệu có sẵn và lựa chọn một phương thức tối ưu nhất. Các phương thức đó có thể là table scan, hoặc scan trên một hoặc nhiều Index nếu như chúng tồn tại.

Nếu phương thức scan table được lựa chọn, query optimizer sẽ đọc tất cả các dòng trên table và trích xuất ra các dòng dữ liệu khớp với điều kiện trong mệnh đề where.

Phương thức scan table là một phương thức tiêu tốn nhiều chi phí của hệ thống, nó thực hiện nhiều hoạt động I/O trên ổ cứng và dễ làm tài nguyên hệ thống trở nên quá tải nếu hành động scan table xảy ra thường xuyên với cường độ lớn. Tuy nhiên, phương thức scan table lại là phương thức tối ưu nhất nếu như số dòng trả về chiếm tỷ lệ cao trên tổng số dòng dữ liệu có trong table.

Khi query optimizer sử dụng Index, nó sẽ tìm kiếm dữ liệu trên các cột đã được index trước, sau đó sẽ tìm nơi lưu trữ dòng dữ liệu tương ứng trên ổ cứng và trích xuất các dòng dữ liệu khớp với điều kiện. Thông thường, tìm kiếm trên index nhanh hơn nhiều so với tìm kiếm trong table, bởi vì không giống như table, index chỉ chứa thông tin của những cột được index và chúng được sắp xếp theo thứ tự.

Trình query optimizer sẽ chọn phương thức tối ưu nhất để chạy câu query của bạn, tuy nhiên, nếu như không có index nào trên bảng thì nó sẽ dùng phương thức là scan table. Do đó, nhiệm vụ của bạn là phải lựa chọn các cột phù hợp trên table để tạo index cho chúng nhằm giúp trình query optimizer lựa chọn được phương pháp tối ưu nhất. SQL Server cung cấp công cụ  Database Engine Tuning Advisor để giúp chúng ta phân tích và gợi ý những index cần tạo trong môi trường database của chúng ta.

(Big Data tổng hợp từ MSDN )

 

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

SQL Server
Đánh số thứ tự bản ghi trong SQL Server

Trong rất nhiều trường hợp, bạn muốn đánh số thứ tự các bản ghi của kết quả câu truy vấn trong SQL theo  một hoặc một số tiêu chí nào đó nhằm mục đích trình bày cũng như xử lý dữ liệu được trực quan hoặc dùng vào nhiều mục …

SQL Server
Hàm thông dụng trong SQL Server – Phần 1: Hàm ngày tháng (1)

Loạt bài này giới thiệu các hàm và cách sử dụng các hàm thông dụng trong SQL Server, cũng như ví dụ cụ thể đi kèm để giúp người đọc, đặc biệt là những người mới làm quen với SQL Server tiếp cận dễ dàng. Những hàm này cũng là …

SQL Server
Hàm thông dụng trong SQL Server – Phần 1: Hàm ngày tháng (2)

Tiếp theo phần 1 của hàm ngày tháng trong SQL Server, phần này giới thiệu riêng thêm một hàm trong nhóm hàm ngày tháng đó là hàm DATEADD(). Hàm DATEADD trong SQL Server  Hàm này có cấu trúc như sau: DATEADD (datepart , number , date ) –          Trong đó …