Cơ bản về Index trong SQL Server

Index trong SQL Server là một trong những yếu tố quan trọng nhất góp phần vào việc nâng cao hiệu suất của cơ sở dữ liệu. Index trong SQL Server tăng tốc độ của quá trình truy vấn dữ liệu bằng cách cung cấp phương pháp truy xuất nhanh chóng tới các dòng trong các bảng, tương tự như cách mà mục lục của một cuốn sách giúp bạn nhanh chóng tìm đến một trang bất kỳ mà bạn muốn trong cuốn sách đó. Trong bài viết này, chúng ta sẽ bàn tổng quan về Index trong SQL Server và giải thích cách chúng được định nghĩa trong một cơ sở dữ liệu cũng như làm thế nào mà index có thể làm cho quá trình truy vấn dữ liệu của chúng ta nhanh hơn. Hầu hết các kiến thức trong bài này đều có thể áp dụng cho SQL Server 2005 và 2008, kiến trúc của Index trong SQL Server có thay đổi một ít từ phiên bản này sang phiên bản khác, nhưng về cơ bản, chúng đều giữ được những thiết kế nguyên thủy, cho nên hầu như chúng ta sẽ không thấy có sự thay đổi nào. Thậm chí chúng ta cũng có thể ứng dụng rất nhiều các kiến thức ở bài này cho cả SQL Server 2000. Tuy nhiên hiện nay, 3 phiên bản phổ biến là SQL Server 2005, 2008 và mới nhất là SQL Server 2012. Chúng ta sẽ tập trung chủ yếu vào SQL Server 2005 và 2008.
Index trong SQL Server được tạo ra trên các cột trong bảng hoặc View. Chúng cung cấp một phương pháp giúp bạn nhanh chóng tìm kiếm dữ liệu dựa trên các giá trị trong các cột. Ví dụ, nếu bạn tạo ra một Index trên cột khóa chính và sau đó tìm kiếm một dòng dữ liệu dựa trên một trong các giá trị của cột này, đầu tiên SQL Server sẽ tìm giá trị này trong Index, sau đó nó sử dụng Index để nhanh chóng xác định vị trí của dòng dữ liệu bạn cần tìm. Nếu không có Index, SQL Server sẽ thực hiện động tác quét qua toàn bộ bảng (table scan) để xác định vị trí dòng cần tìm, mà table scan là một trong những động tác có hại nhất cho hiệu suất của SQL Server.
Index trong SQL Server có thể tạo trên hầu hết các cột trong bảng  hoặc View. Ngoại trừ các cột dùng để lưu trữ các đối tượng dữ liệu lớn như kiểu Image, Text, varchar(max)… Bạn cũng có thể tạo Index trên các cột kiểu XML, nhưng Index  trên XML hơi khác so với Index cơ bản nói chung và cũng ít được dùng nên chúng ta sẽ chưa bàn đến ở đây. 


Index trong SQL Server được tạo thành từ một tập hợp các page (các Index Node) và chúng được tổ chức trong một cấu trúc có tên gọi là B-tree. Cấu trúc này là cấu trúc kiểu thứ bậc trong tự nhiên, với các nút gốc ở trên cùng của hệ thống phân cấp và các nút lá ở phía dưới, như thể hiện trong hình dưới đây:

 

 

Index trong SQL Server



Khi một truy vấn được xây dựng dựa trên các cột được tạo Index, cỗ máy thực thi truy vấn sẽ bắt đầu tại nút gốc và điều hướng qua các nút trung gian cho đến khi cỗ máy truy vấn tìm được đến nút lá.

Ví dụ, nếu bạn đang tìm kiếm giá trị 123 trong một cột được tạo index, ví dụ như cột ID chẳng hạn, đầu tiên cỗ máy truy vấn sẽ tìm ở nút gốc (Root Level)  để xác định page nào sẽ được tham chiếu tới ở level trung gian (Intemediate Level). Trong ví dụ này, trang đầu tiên chỉ các giá trị từ 1-100, và trang thứ hai là các giá trị 101-200, vì vậy cỗ máy truy vấn sẽ đi đến trang thứ hai ở level trung gian. Cỗ máy truy vấn sau đó sẽ xác định trang tiếp theo  mà nó phải tham chiếu tới ở level trung gian kế tiếp. Cuối cùng, cỗ máy truy vấn sẽ tìm đến nút lá cho giá trị 123. Nút lá sẽ chứa toàn bộ dòng dữ liệu hoặc nó chỉ chứa một con trỏ làm tham chiếu dến dòng dữ liệu, tùy thuộc vào việc Index trên cột ID này là kiểu Clustered Index hay là  Non Clustered Index.
Để tìm hiểu về Clustered IndexNon Clustered Index là gì, mời bạn đọc xem chi tiết ở link sau:
http://bigdata.com.vn/clustered-index-va-non-clustered-index-la-gi/
Kiểu Index
Để bổ sung vào 2 kiểu Index cơ sở là  Clustered IndexNon Clustered Index, chúng ta có thể mở rộng kiểu Index theo các cách sau đây:
  1. Composite index: Là kiểu Index có nhiều hơn 1 cột. Trong SQL Server 2005 và 2008, bạn có thể có đồng thời tối đa 16 cột trong một Index, miễn là kích thước của Index không vượt quá giới hạn 900 byte. Cả hai kiểu index cơ sở là Clustered Index  và Non Clustered Index cũng có thể đồng thời là là kiểu Composite index.
  2. Unique Index: Là kiểu Index dùng để đảm bảo tính duy nhất trong các cột được tạo Index. Nếu Index loại này được tạo dựa trên nhiều cột, thì tính duy nhất của giá trị được tính trên tất cả các cột đó, không chỉ riêng rẽ từng cột. Ví dụ, nếu bạn đã tạo ra một Index trên các cột FirstName và LastName trong một bảng, thì giá trị của 2 cột này kết hợp với nhau phải là duy nhất, nhưng riêng rẽ từng cột thì giá trị vẫn có thể trùng nhau.
Một Unique Index được tự động tạo ra khi bạn định nghĩa một khóa chính (Primary Key) hoặc một ràng buộc duy nhất (Unique Constraint):
  • Primary Key: Khi bạn định nghĩa một ràng buộc khoá chính trên một hoặc nhiều cột của bảng, SQL Server tự động tạo ra một Unique – Clustered Index  nếu chưa có một Clustered Index nào tồn tại trên bảng hoặc view. Tuy nhiên, bạn hoàn toàn có thể khai báo lại hành vi mặc định này của SQL Server bằng cách định nghĩa lại index cho cột hoặc nhóm cột này là Unique – Non Clustered Index.
  • Unique: Khi bạn định nghĩa một ràng buộc duy nhất, SQL Server tự động tạo ra một index có các đặc tính là Unique và là Non Clustered Index. Bạn cũng hoàn toàn có thể tạo ra một Unique và là Clustered Index nếu như chưa có một Clustered Index nào được tạo ra trước đó trên bảng.
  1. Covering index: là một loại chỉ số bao gồm tất cả các cột cần thiết để xử lý một truy vấn cụ thể. Ví dụ, truy vấn của bạn có thể lấy các cột FirstName và LastName từ một bảng, dựa trên một giá trị trong cột ContactID. Từ đó, để tăng tốc độ xử lý câu truy vấn, bạn có thể tạo ra một chỉ số bao gồm tất cả ba cột này.


Index Design
Chính vì những lợi ích mà Index đem lại, nên chúng phải được thiết kế cẩn thận. Vì Index có thể chiếm nhiều không gian của ổ cứng, do đó không nên triển khai quá nhiều Index nếu như chúng không thực sự cần thiết. Ngoài ra, Index sẽ được tự động cập nhật khi bản thân các dòng dữ liệu được cập nhật, do đó có thể dẫn đến phát sinh thêm chi phí và ảnh hưởng đến hiệu suất của quá trình xử lý dữ liệu. Vì vậy, việc thiết kế Index trong SQL Server cần phải có một số cân nhắc trước khi thực hiện chúng..
  • Đối với các bảng được cập dữ liệu nhiều và thường xuyên, sử dụng càng ít cột càng tốt trong một Index và không sử dụng Index tràn lan trên các bảng của dữ liệu.
  • Nếu một bảng có khối lượng dữ liệu lớn nhưng tần suất cập nhật dữ liệu thấp, bạn nên sử dụng nhiều Index cần thiết để cải thiện hiệu suất truy vấn, . Tuy nhiên, nên sử dụng Index một cách khôn ngoan trên các bảng nhỏ vì cỗ máy truy vấn có thể mất nhiều thời gian và chi phí để tìm kiếm dữ liệu dựa trên các Index hơn là tìm kiếm dữ liệu dựa trên việc thực hiện một thao tác scan table.
  • Đối với Clustered Index, hãy cố gắng giữ cho độ dài của các cột được lập Index càng ngắn càng tốt. Lý tưởng nhất là tạo Clustered Index trên cột có thuộc tính Unique và không cho phép giá trị Null.. Đây là lý do tại sao các khóa chính thường được sử dụng cho Clustered Index của bảng, bên cạnh đó,  việc xem xét các truy vấn thường thực hiện trên bảng cũng cần được tính đến khi xác định các cột nên tham gia vào một Clustered Index..
  • Tính duy nhất của các giá trị trong một cột có tác động đến hiệu suất của Index. Nhìn chung, càng nhiều giá trị trùng lặp thì hiệu suất thực thi của Index càng kém. Nói cách khác, tính duy nhất của giá trị trong một cột càng cao thì hiệu suất của Index càng cao. Do đó, nếu xác định các giá trị của một cột nào đó trong một table là duy nhất thì khi đó bạn nên tạo một Unique Index trên cột đó.
  • Đối với Composite Index, cần phải xem xét thứ tự của các cột trong định nghĩa của Index. Cột nào thường được sử dụng trong các biểu thức so sánh ở mệnh đề WHERE (như WHERE FirstName = ‘Charlie’) sẽ được liệt kê đầu tiên. Thứ tự của các cột tiếp theo sẽ được liệt kê dựa trên tính duy nhất của các giá trị trong cột, trong đó tính duy nhất của giá trị trong cột càng cao thì càng được liệt kê trước.
  • Bạn cũng có thể tạo Index trên các Computed Column nếu chúng đáp ứng được các yêu cầu nhất định. Ví dụ, biểu thức được sử dụng để tạo ra các giá trị trong cột phải được xác định (có nghĩa là nó luôn luôn trả về kết quả tương tự cho một tập của các giá trị đầu vào). Để biết thêm chi tiết về lập Index cho Computed Column, bạn có thể tham khảo thêm trong MSDN.
Queries
Một lưu ý khác khi tạo Index đó là xem xét dữ liệu trong database sẽ được truy vấn như thế nào. Như đã đề cập ở trên, bạn phải tính đến tần suất thay đổi dữ liệu. Ngoài ra, bạn nên xem xét các nguyên tắc sau đây:
  • Cố gắng Insert hoặc update càng nhiều dòng càng tốt trong một câu lệnh duy nhất, thay vì sử dụng nhiều câu truy vấn tách rời.
  • Tạo Non Clustered Index trên các cột được sử dụng thường xuyên trong các biểu thức so sánh và các cột tham gia vào điều kiện Join.
  • Xem xét tạo Index trên các cột được sử dụng trong các truy vấn tìm kiếm chính xác.
Kết luận
Trong bài viết này, chúng ta đã thảo luận các vấn đề cơ bản về Index trong SQL Server và các vấn đề cần lưu ý khi triển khai Index. Điều đó không có nghĩa là chúng ta đã nắm được đầy đủ một bức tranh toàn cảnh về Index trong Server. Có một số loại Index mà chúng ta đã không thảo luận ở đây, như index cho cột có kiểu là XML, Filtered Index, Spatial Index được hỗ trợ trong SQL Server 2008. Bài viết này chỉ nên được xem như là điểm bắt đầu để tiếp tục nghiên cứu sâu hơn về Index trong SQL Server. Trươc mắt, bạn hãy thực hành những gì mà bài viết này đề cập và tìm hiểu sâu hơn về Index dựa trên các hướng dẫn có trong MSDM.

 

 

No Comments

Leave a Reply

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

SQL Server
1
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 …

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à …