SQL Tips 07 – Viết query để tận dụng index

Để hệ quản trị cơ sở dữ liệu (DBMS) tận dụng được sức mạnh của các index thì ta cần phải biết viết SQL query cho đúng. Nếu viết không đúng thì DBMS engine sẽ không dùng index. Query muốn kích hoạt sử dụng index thì phần predicate của nó (chính là phần điều kiện mà ta thường thấy ở mệnh đề WHERE, GROUP BY, ORDER BY, HAVING, phần điều kiện join của mệnh đề FROM) phải “sargable” (Search ARGument ABLE).

Những operator sau đây thường là sargable:

• =
• >
• <
• >=
• <=
• BETWEEN
• LIKE (không có dấu % ở đầu, ví dụ: LIKE ‘abc%’)
• IS [NOT] NULL

Các operator sau đây có thể sargable, nhưng hiếm khi giúp cải thiện performance:

• <>
• IN
• OR
• NOT IN
• NOT EXISTS
• NOT LIKE

Các trường hợp sau đây đều khiến query trở thành non-sargable:

• Áp dụng hàm (function) lên 1 hoặc nhiều cột trong mệnh đề WHERE
• Tính toán (cộng trừ nhân chia …) trên 1 hoặc nhiều cột trong mệnh đề WHERE
• Tìm kiếm sử dụng LIKE ‘%valuetofind%’

Ví dụ 1:

Ta có 1 non-sargable query như sau để tìm ra các hợp đồng (contract) có giá trị hợp đồng lớn hơn 100 triệu VND với tỷ giá USD/VND = 23000

SELECT 
	ContractID, ContractAmountUSD
FROM Contract
WHERE ContractAmount*23000 > 100000000;

Query này có áp dụng phép tính nhân lên cột ContractAmount ở mệnh đề WHERE nên nó là non-sargable. Để sửa query này thành sargable, ta làm như sau:

SELECT 
	ContractID, ContractAmountUSD
FROM Contract
WHERE ContractAmount > 100000000/23000;

Ví dụ 2:

Ta có non-sargable query sau để tìm ra các hợp đồng (contract) được mở vào năm 2021:

SELECT ContractID, ContractOpenDate, ContractAmountUSD
FROM Contract
WHERE YEAR(ContractOpenDate) = 2021;

Query này có áp dụng function YEAR() lên cột ContractOpenDate ở mệnh đề WHERE nên nó là non-sargable. Để sửa query này thành sargable, ta làm như sau:

SELECT ContractID, ContractOpenDate, ContractAmountUSD
FROM Contract
WHERE ContractOpenDate >= CAST('2021-01-01' AS Date)
	AND ContractOpenDate < CAST('2022-01-01' AS Date);