SQL - Index trong điều kiện tìm kiếm (How Indexes are Used by the WHERE clause?).

Bài viết này tôi sẽ hướng dẫn cách sử dụng Index trong mệnh đề tìm kiếm của câu lệnh.

Trong một câu lệnh tìm kiếm trong SQL một định nghĩa tìm kiếm ở câu lệnh Where được gọi là đối số có thể tìm kiếm(sargable: Search Argument-Able) khi Index có thể được sử dụng trong lúc thực thị câu lệnh.



ví dụ:

SELECT * FROM data_index WHERE P_Id = 100

Bạn tạo database theo hướng dẫn của bài viết SQL - Tăng tốc độ thực thi câu truy vấn(query) bằng hàm Index. để thực hiện ví dụ trên.

Mệnh đề "WHERE P_Id = 100" được coi là đối số có thể tìm kiếm(sargable) vì nó cho phép Index trên cột P_Id được sử dụng trong khi thực thi câu lệnh.

Index giúp tăng hiệu suất của câu lệnh lên rất nhiều nên việc viết code sao cho các điều kiện tìm kiếm trở thành sargable là điều rất quan trọng.

Một nguyên tắc cơ bản trong mệnh đề tìm kiếm có Index trong SQL Server bạn cần chú ý khi sử dụng là cột cần tìm kiếm phải đứng một mình ở một phía của câu lệnh tìm kiếm, hiểu đơn giản là câu lệnh không có hàm số hay biểu thức tính toàn nào được áp dụng lên cột Index.

Hãy xem xét 2 câu lệnh bên dưới:

SELECT * FROM data_index WHERE P_Id = 100
SELECT * FROM data_index WHERE P_Id+2 = 100

Theo kết quả thực thực hiển thị như hình trên bạn thấy rõ ràng câu lệnh 2 có mệnh đề tìm kiếm "WHERE P_Id+2 = 100" không sửa dụng được Index trong cột P_Id vì vậy hiệu suất của nó không bằng câu lệnh thứ nhất.

Hai câu lệnh trên cho cùng một kết quả, ở câu lệnh 1 điều kiện tìm kiếm của nó sargable,  nhưngcâu lệnh 2 là non-sargable và Index trên cột P_Id thì không có hiệu lực.

Nhưng bạn thấy ở trên hình câu lệnh 2 dẫn đến thao tác là Index Scan tương đương với Table Scan, vì vậy mà chi phí của nó cao hơn rất nhiều so với câu lệnh 1. Nguyên nhân Index không được sử dụng là vì câu lệnh áp dụng một phép tính toán trên cột nên hệ thống phải thực hiện tính toán trên từng node của cây Index trước khi lấy kết quả để so sánh với giá trị cần tìm. Vì vậy nguyên tắc của nó là phải duyệt tuần tự qua từng node thay vì tìm kiếm theo kiểu nhị phân(Index Seek như câu lệnh 1).

Tiếp theo chúng ta sẽ phân tích thêm một  vài ví dụ để hiểu hơn về Index trong mệnh đề tìm kiếm. Đầu tiên ta cần tạo 1 table để thực hiện các ví dụ.

CREATE TABLE customer
(
    cus_ID int IDENTITY,
    name varchar(255),
    birth date
)

Tiếp theo bạn chạy câu lệnh sau để cập nhật dữ liệu để test, nó chạy hơi lâu xíu ^^!

DECLARE @Begin Date, @End Date, @Current Date, @intFlag INT
SET @Begin = Cast('1000-01-01' as Date)
SET @End = Cast('4000-01-01' as Date)
SET @Current = @Begin
set @intFlag = 1
WHILE (@Current <= @End)
BEGIN
    INSERT INTO customer(name, birth) VALUES (dbo.fnColumnNameFromIndex(@intFlag),@Current)
    SET @Current = DateAdd(day, 1, @Current)
    SET @intFlag = @intFlag + 1
END

Đầu tiên ta sẽ phân tích cách viết ham đối với kiểu dữ liệu là varchar, trong bảng sẽ là cột "name". Bạn hãy tạo Index cho cột "name" nhu bên dưới:

CREATE INDEX Index_customerName ON customer(name)

Đề bài đặt ra là bạn hãy tìm tên của tất cả các khách hàng bắt đầu từ D đầu tiên. Theo như yêu cầu của đề bài thì bạn có thể làm như sau:

SELECT name FROM customer WHERE LEFT(name,1) = 'D' 

Nhưng nếu bạn viết câu truy vấn theo cách trên thì không thể tận dụng Index vì nó vi phạm vào quy tắc sử dụng Index như tôi đã nêu ở trên.

Vậy làm sao có thể viết được câu truy vấn để thực hiện yêu cầu trên và sử dụng Index. Bạn tham khảo cách viết sau đây:

 SELECT name FROM customer WHERE name LIKE 'D%'

Để có thể phân tích rõ hàng hiêu quả của 2 câu lệnh bạn hãy chạy cùng lúc 2 câu lệnh truy vấn trên và sẽ tháy được kết quả như hình bên dưới

Câu truy vấn thứ nhất mất tới 98% chi phí, mà câu lệnh thứ hai mất chỉ có 2% chi phí, như vậy ta thấy được câu lệnh thứ 2 mới sử dụng được tính năng của Index.

Tiếp theo chúng ta sẽ phân tích thêm cách sử dụng index trong câu truy vấn với kiểm dữ liệu là DATE. Trước tiên bạn hãy tạo Index cho cột "birth".

CREATE INDEX Index_customerBirth ON customer(birth)

Và đề bài đặt ra bạn hãy tìm các khách hàng có ngày sinh là '16/12/1986', theo cách thông thường và để xử lý nhanh đề bài thì ta có thể sẽ viết như sau:

SELECT * FROM customer WHERE CONVERT(VARCHAR,birth,103) = '16/12/1986'

Nhưng nếu chú ý tới việc sử dụng Index, thì phải xử lý câu truy vấn như sau:

SELECT * FROM customer WHERE birth >= '19861216' AND birth <= '19861216'

Giống như phần trên ta sẽ chạy 2 câu truy vấn cùng lúc để phân tích, sau khi chạy xong kết quả sẽ như bên dưới.

Câu truy vấn thứ nhất mất tới 100% chi phí, mà câu lệnh thứ hai mất chỉ có 0% chi phí, kết quả cũng khá là bất ngờ nhưng mình cũng không biết gải thích tại sao là không tốn chi phí nào cũng chạy được ra kết quả đâu nha ^^! cái này do chương trình nó báo vậy nên chỉ biết vậy thôi.

Theo bài phân tích này khẳng định một lần nữa việc tạo và sử dụng Index không phải là một công việc dễ dàng hay đơn giản, mà nó đòi phải có sự phân tích giữa cấu trúc database và câu truy vấn thì mới có thể sử dụng và tối ưu được.

Hix đuối quá... bài này phải ngồi phân tích và chia thành 2 lần viết mới xong. Nếu bài viết có chỗ nào chưa hợp lý, mọi người góp ý bổ sung cho ý kiến.