Để Dùng Được Index Trong Điều Kiện Tìm Kiếm Của Câu Lệnh

Vũ Huy Tâm

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.
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.




Tags: , , , , , , , ,

26 Comments
Posted on 24/6/2010 | Categories: Index, Performance tuning

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

Comments
  • kl (19/04/2011 6:37 pm)

    phần comment cho ai xem mà trông rõ nhỉ . Trong khi phần nội dung của topic thì đen thui. Nếu đã giấu thì k0 xem nữa

    • CuongIT (25/08/2011 1:01 pm)

      Ông này phàn nàn ghê quá. Người ta đã có tinh thần chia sẻ rồi thi dù tốt hay xấu kũng nên cảm ơn chứ

  • Jack (20/04/2011 1:22 am)

    Anh ơi trong bài anh có post đoạn :" Ở đâ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’”

    Em tìm thử nhưng không thấy chõ nào trong Execution Plan có phần này cả

  • Vũ Huy Tâm (21/04/2011 9:53 am)

    @kl: chắc template này không tương thích với máy tín của bạn. Bạn có thể gửi các thông số (hệ điều hành, độ phân giải, browser version) để tôi xem có đúng như vậy và có thể khắc phục được không. Sorry vì những phiền toái của bạn.

    @Jack: Trong bài tôi nói hơi vắn tắt. Bạn chỉ có thể nhìn thấy khi trường cần tìm (trường Ten ở ví dụ trong bài) được index và index đủ tốt để được dùng cho câu lệnh (độ selectivity đủ lớn – xem bài http://www.sqlviet.com/blog/index-tren-truong-bit.... Khi hai điều kiện trên được thỏa mãn, bạn có thể từ kế hoạch thực thi click chuột vào hình "Index seek" rồi ấn F4 để bật cửa sổ property. Sau đó xem ở dòng "Seek Predicate" sẽ thấy nó chuyển thành dạng so sánh chuỗi.

  • Tiến (25/06/2011 11:52 pm)

    Thanks bạn Huy Tâm rất nhiều về những kiến thức bạn đã chia sẻ.

    Mình rất thích các bài viết của bạn,

    Tiến

  • firefox (18/08/2011 11:52 pm)

    Làm thế nào để biết câu quẻy nào sử dụng index thành công và không thành công không ?Mong bạn chỉ giáo

    • Vũ Huy Tâm (20/08/2011 2:00 pm)

      Việc này đi vào chi tiết thì cũng khá dài dòng, nhưng với những câu lệnh chỉ trả về 1 hoặc vài bản ghi, bạn quan sát phương án thực thi mà thấy “Index seek” thì nghĩa là câu lệnh đã sử dụng index thành công; còn nếu thấy “Index scan” hoặc “Table scan” thì có nghĩa là index đã không được sử dụng một cách thích hợp.

  • firefox (27/08/2011 6:03 am)

    Cám ơn bạn đã chia sẻ !

  • tunglam (29/08/2011 11:06 pm)

    Bài viết hay lắm, cảm ơn bạn chia sẻ.! :)

  • Nguyễn Viết Văn (08/11/2011 5:26 am)

    bạn ơi cho mình hỏi nếu mình muốn like (1 cái biến thì làm thế nào bạn) ví dụ như
    ALTER PROC TK_SV_TEN(@TEN NVARCHAR(7))
    AS
    BEGIN
    SELECT MASV ‘MÃ SINH VIÊN’,HODEM ‘HỌ ĐỆM’,TEN ‘TÊN’,NGAYSINH’NGÀY SINH’,QUEQUAN ‘QUÊ QUÁN’,LIENHE ‘SỐ ĐIỆN THOẠI’
    FROM TBL_SINHVIEN WHERE TEN=@TEN
    END
    như ví dụ mình muốn like cái @TEN thi lam the nao
    ai chỉ dùm mình với
    gửi về yahoo cho mình nhé y2k.pc19 thank bạn nhiều

    • Vũ Huy Tâm (09/11/2011 9:52 pm)

      Bạn viết như sau:

      ...WHERE TEN LIKE '%'+@Ten+'%'
  • Nguyễn Duy (16/12/2011 4:43 am)

    cảm ơn a! bài viết rất hay!
    sẫn cho e hỏi nếu như trong trường hợp lấy tất cả row của 1 bảng e sử dụng 2 trường hợp
    1.

     SELECT * FROM News

    2.

     SELECT * FROM News WHERE ID > 0

    như dậy thời gian thực thi nó là như nhau nhưng kế hoạch thì lại khác
    theo a thì 2 câu lệnh đó khác gì nhau không.
    và a có cách nào hay hơn để lấy tất cả dữ liệu 1 lần không

    • Vũ Huy Tâm (22/12/2011 10:13 am)

      nếu lấy tất cả dữ liệu từ bảng thì index không có vai trò gì nữa bạn ạ :)

  • nguyenminh (25/04/2012 12:27 pm)

    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.

    Anh ơi, Cho em hỏi: Làm răng a lấy ra được các số liệu nay?

    • Vũ Huy Tâm (29/04/2012 1:12 pm)

      Bạn chạy 2 lệnh sau rồi hãy chạy lệnh trên, nó sẽ in ra chi tiết các thông tin vào ra và thời gian CPU:

      SET STATISTICS IO ON
      SET STATISTICS TIME ON
      • nguyenminh (01/05/2012 11:58 pm)

        ok! Cảm ơn anh hêy.

  • Nguyễn Nam (21/06/2012 4:38 am)

    Tuyệt vời những bài viết của anh đã bổ trợ kiến thức cho tôi rất nhiều

  • hoang (27/09/2012 5:10 am)

    thanks verymuch

  • Tam (16/06/2013 6:45 am)

    nếu dùng như thế index có hoạt động ko?

    SELECT *
    FROM dbo.DonHang
    WHERE OrderDate = ’20090821′

    • uyen (07/05/2014 3:29 am)

      em cũng thắc mắc vấn đề này…. chia sẽ với em với ^^

      • tung (07/05/2014 10:20 pm)

        bạn vào xem plan của nó la biết ngay mà

  • Kh (18/12/2013 5:29 am)

    Cảm ơn anh rất nhiều. Bài viết quá hay!

  • Nguyễn Minh Tuấn (08/01/2014 2:50 am)

    mình đang học phần sql, mong các pro giúp đỡ dùm mấy câu hỏi của thầy dạy,. Rất mong được hỗ trợ gấp
    Câu 1/ Khi có yêu cầu dữ liệu thì Buffer Manager sẽ làm gì?

    Câu 2/ Khi nào thì dùng chỉ mục (index)? Khi nào thì không dùng chỉ mục?

    Câu 3/ Người ta sinh ra nhiều loại file để làm gì?

    Câu 4/ Bản chất của Trigger là gì?

    Câu 5/ Tại sao Free Space nằm chỗ đó?

    Câu 6/ Tương đương xung đột là gì?

    Câu 7/ Thế nào là khả tuần tự xung đột?

    Câu 8/ Schedule là gì?

  • Lê Bửu (06/05/2015 4:47 am)

    Cảm ơn bài viết của anh rất nhiều!

  • Vương (27/06/2016 12:32 am)

    về fulltext index thì sao, có bài hướng dẫn tỉ mỉ như vầy k ạ!

  • Thảo (14/03/2017 11:07 am)

    Xin chào em có 1 thắc mắc muốn hỏi
    Khi em sử dụng Full text để tìm kiếm dữ liệu trong table ví dụ từ khóa ” thực phẩm và sữa” thì tiêu đề bài viết nào chứa 1 trong những từ đó đều ra cả, nhưng khi tìm kiếm bằng rỗng hay chỉ ‘TH’ thì không ra được gì. Có cách nào trả về tất cả dữ liệu trong table khi gõ từ khóa bằng rỗng không ạ.

    SELECT *
    FROM TABLE1
    WHERE FREETEXT(*, ‘ ‘).

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>