Sử dụng index trong sql server một cách hiệu quả

Sử dụng index trong sql server một cách hiệu quả

Trong một câu lệnh SQL, một điều kiện tìm kiếm ở mệnh đề WHERE được gọi là sargable (viết tắt từ Search Argument-Able) nếu index có thể được sử dụng khi thực hiện câu lệnh (giả sử cột tương ứng có index). Ví dụ, với câu lệnh sau:

SELECT *

FROM dbo.Customer

WHERE CustomerID = 1234

thì điều kiện “CustomerID = 1234″ là sargable, vì nó cho phép index trên cột CustomerID được sử dụng.
Vì index giúp tăng hiệu năng của câu lệnh lên rất nhiều, việc viết code để sao cho các điều kiện tìm kiếm trở thành sargable là một mục tiêu rất quan trọng. Một nguyên tắc rất cơ bản trong SQL Server mà bạn có thể áp dụng trong rất nhiều trường hợp, đó là cột cần tìm phải đứng một mình ở một phía của biểu thức tìm kiếm, nói cách khác là không có hàm số hay phép tính toán nào áp dụng trên cột đó. Hãy xem xét hai câu lệnh dưới đây:

USE AdventureWorks

GO

-- câu lệnh 1 (non-sargable)

SELECT * FROM Sales.Individual

WHERE CustomerID+2 = 11002

 

-- câu lệnh 2 (sargable)

SELECT * FROM Sales.Individual

WHERE CustomerID = 11000

Hai câu lệnh trên cho cùng một kết quả, nhưng ở câu lệnh 1 điều kiện tìm kiếm của nó là non-sargable và index trên cột CustomerID trở nên vô dụng. Kế hoạch thực thi của nó cho thấy điều này:

Câu lệnh 1 dẫn đến thao tác Clustered Index Scan, tức là quét cả cây clustered index, đồng nghĩa với quét bảng (vì clustered index chính là bảng). Do vậy mà chi phí của nó tăng vọt. Sở dĩ index đã không được sử dụng vì khi bạn áp dụng một phép tính toán trên cột, hệ thống phải thực hiện tính toán đó trên từng node trên cây index trước khi có thể lấy kết quả để so sánh với giá trị cần tìm. Vì thế nó phải duyệt tuần tự qua từng node thay vì tìm theo kiểu nhị phân (index seek, như với câu lệnh 2). Và đây là các con số thống kê về IO và thời gian thực hiện:
Câu lệnh 1 (non-sargable):

Table ‘Individual’. Scan count 1, logical reads 3088,
physical reads 35
CPU time = 0 ms, elapsed time = 259 ms.

Câu lệnh 2 (sargable):

Table ‘Individual’. Scan count 0, logical reads 3, physical
reads 3
CPU time = 0 ms, elapsed time = 19 ms.

Trên đây là một trường hợp đơn giản mà có lẽ không mấy ai mắc phải, tuy nhiên có những tình huống khác không hiển nhiên như thế. Ví dụ, khi cần tìm tất cả các đơn hàng được thực hiện trong ngày 21/08/2009, một cách trực giác có thể bạn nghĩ ngay đến một trong các cách làm sau:

SELECT *

FROM dbo.DonHang

WHERE CONVERT(VARCHAR,OrderDate,103) = '21/08/2009' --cắt bỏ phần thời gian, chỉ giữ lại phần ngày

 

-- hoặc

SELECT *

FROM dbo.DonHang

WHERE DATEPART(d,OrderDate) =21

AND DATEPART(m,OrderDate)=8

AND DATEPART(YEAR,OrderDate)=2009

Cả hai cách viết trên đều làm mất tác dụng index trên trường OrderDate. Cách viết đúng phải là:

SELECT *

FROM dbo.DonHang

WHERE OrderDate >= '20090821' AND OrderDate < '20090822'

-- lưu ý chuỗi ngày tháng mặc định của ANSI có dạng yyyymmdd

Một ví dụ khác, bạn cần tìm tất cả các khách hàng có tên bắt đầu bằng chữ C, như Can, Công, Cường… Các cách viết sau là không sargable:

SELECT *

FROM dbo.Customer

WHERE SUBSTRING(Ten,1,1) = 'C'

 

--hoặc

SELECT *

FROM dbo.Customer

WHERE LEFT(Ten,1) = 'C'

Cách viết để thành sargable là:

SELECT *

FROM dbo.Customer

WHERE Ten LIKE 'C%'

Ở đây có một chi tiết thú vị, khi quan sát kế hoạch thực thi của câu lệnh ở ngay trên, bạn sẽ thấy điều kiện “Ten LIKE ‘C%’” được chuyển thành “Ten >=’C’ AND Ten< ‘D’” (vâng, chuỗi cũng so sánh lớn bé được như số), và điều kiện này hoàn toàn thích hợp với việc tìm kiếm bằng index.

Tuy nhiên, nếu bạn thay đổi yêu cầu một chút, tìm các khách hàng có tên chứa chữ ‘C’ ở bất kỳ vị trí nào, không chỉ ở đầu. Câu lệnh của bạn sẽ trở thành:

SELECT *

FROM dbo.Customer

WHERE Ten LIKE '%C%'

Khi đó index trên trường Ten lại bị mất tác dụng, vì hệ thống không có cách nào khác là dừng lại trên từng node, kiểm tra xem giá trị của trường Ten có chứa ký tự ‘C’ hay không. Trên thực tế đây là một thao tác rất tốn kém về tài nguyên, với các bài toàn như thế này cách làm hiệu quả hơn là dùng fulltext index và fulltext search.