Mục lục nội dung

Nhiều Backend Developer tin rằng chỉ cần đánh Index lên một cột là mọi truy vấn liên quan đến cột đó sẽ tự động bay nhanh hơn. Đáng tiếc, đây là một ngộ nhận khá phổ biến.

Sự thật là database không bắt buộc phải dùng Index chỉ vì nó tồn tại. Mỗi lần chạy một câu query, Query Optimizer sẽ cân nhắc hàng loạt yếu tố - kích thước bảng, thống kê phân bố dữ liệu, số dòng cần quét, loại Index đang có, chi phí truy cập ước tính - rồi mới đưa ra quyết định: dùng Index Scan hay quét toàn bộ bảng (Sequential Scan). Nói cách khác, có Index không đồng nghĩa với việc Index sẽ được dùng.

Dưới đây là những tình huống thường gặp khiến mệnh đề WHERE "vô hiệu hóa" Index một cách âm thầm.

1. Bọc hàm quanh cột đang lọc

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

Nhiều người mặc định rằng chỉ cần cột email có Index là câu trên sẽ chạy nhanh. Vấn đề là Index được xây dựng dựa trên giá trị gốc của cột, còn ở đây database phải tính LOWER(email) cho từng dòng trước khi so sánh - nên với nhiều hệ quản trị, Index thông thường sẽ không được tận dụng.

Nếu cần so khớp không phân biệt hoa thường, có vài hướng xử lý:

  • Dùng kiểu dữ liệu hỗ trợ sẵn (ví dụ citext trong PostgreSQL).
  • Tạo Functional Index (Index trên biểu thức, không phải trên cột gốc).
  • Chuẩn hóa dữ liệu ngay lúc ghi xuống database.

2. Tính toán trên cột thay vì trên hằng số

SELECT * FROM employees WHERE salary * 12 > 500000;

Ở đây salary * 12 phải được tính lại cho từng dòng, khiến Index trên salary khó phát huy tác dụng. Cách khắc phục đơn giản là chuyển phép tính sang phía hằng số:

SELECT * FROM employees WHERE salary > 500000 / 12;

Nguyên tắc chung: tính toán trên hằng số, không tính toán trên cột.

3. Ép kiểu dữ liệu ngầm (Implicit Conversion)

Giả sử id là kiểu INT nhưng bạn lại viết:

WHERE id = '100'

Một số database sẽ tự ép kiểu cho từng giá trị so sánh, số khác lại ép kiểu cho toàn bộ cột - và nếu rơi vào trường hợp thứ hai, Index gần như chắc chắn bị bỏ qua. Giải pháp là luôn truyền đúng kiểu:

WHERE id = 100

Đây cũng là một lý do khiến Prepared Statement luôn được khuyến nghị: driver sẽ tự truyền tham số đúng kiểu dữ liệu, giảm rủi ro ép kiểu ngoài ý muốn.

4. LIKE với dấu % ở đầu chuỗi

WHERE name LIKE '%son'
WHERE name LIKE '%admin%'

Đây là lỗi cực kỳ phổ biến trong các API tìm kiếm. Với B-Tree Index, database chỉ có thể tra cứu hiệu quả khi biết chuỗi bắt đầu bằng gì. Nếu tìm %son, database buộc phải rà từng dòng vì không biết "Jackson", "Johnson", "Anderson", "Wilson"... khác nhau ở đâu cho tới khi so hết chuỗi.

Ngược lại, WHERE name LIKE 'John%' cho database biết chính xác tiền tố cần tìm, nên B-Tree Index vẫn phát huy tác dụng bình thường.

Nếu nghiệp vụ bắt buộc phải tìm kiểu %keyword%, hãy cân nhắc Full-Text Search hoặc các Index chuyên dụng như GIN kết hợp pg_trgm trong PostgreSQL.

5. Dùng NOT hoặc !=

WHERE status != 'ACTIVE'
WHERE NOT active

Nếu phần lớn bản ghi đều khác ACTIVE, thì việc "tra Index rồi đi tìm dòng tương ứng" tốn kém hơn hẳn so với quét thẳng toàn bảng. Lúc này Query Optimizer thường chủ động chọn Sequential Scan - không phải vì Index không tồn tại, mà vì dùng Index lại đắt hơn.

6. OR trên nhiều cột khác nhau

WHERE email = '[email protected]' OR phone = '0901234567'

Nếu chỉ email có Index còn phone thì không, Optimizer có thể quyết định bỏ Index luôn và quét toàn bảng cho gọn. Ngay cả khi cả hai cột đều có Index riêng, kế hoạch thực thi cho OR cũng phức tạp hơn và không phải lúc nào cũng tối ưu. Trong nhiều trường hợp, tách thành hai câu query rồi gộp bằng UNION ALL lại cho hiệu năng tốt hơn.

7. Biểu thức kết hợp nhiều cột

WHERE price + tax > 100

Database chỉ có Index trên từng cột riêng lẻ (price, tax), chứ không có Index nào cho biểu thức price + tax. Vì vậy nó buộc phải tính lại biểu thức này cho mọi dòng trước khi lọc.

8. Dùng COALESCE trong điều kiện lọc

WHERE COALESCE(status, 'ACTIVE') = 'ACTIVE'

Về mặt logic câu này không sai, nhưng việc bọc hàm COALESCE trực tiếp lên cột thường khiến Index thông thường không được dùng, tương tự lỗi ở mục 1. Có thể viết lại thân thiện hơn với Optimizer:

WHERE status = 'ACTIVE' OR status IS NULL

9. Điều kiện không khớp thứ tự Composite Index

Giả sử có Composite Index trên (first_name, last_name), nhưng câu query lại là:

WHERE last_name = 'Smith'

B-Tree Composite Index hoạt động theo nguyên tắc Left-most Prefix - chỉ tận dụng được hiệu quả khi điều kiện lọc bắt đầu từ cột đầu tiên trong Index. Vì vậy các câu sau sẽ tận dụng Index tốt hơn nhiều:

WHERE first_name = 'John'
-- hoặc
WHERE first_name = 'John' AND last_name = 'Smith'

Đừng đoán - hãy dùng EXPLAIN

Cách duy nhất để biết chắc database có đang dùng Index hay không là chạy:

EXPLAIN
-- hoặc
EXPLAIN ANALYZE

Kết quả sẽ cho biết Optimizer đang chọn kế hoạch nào, ví dụ:

  • Index Scan
  • Index Only Scan
  • Bitmap Index Scan / Bitmap Heap Scan
  • Sequential Scan
  • Parallel Sequential Scan

Đừng suy đoán dựa trên cảm tính - EXPLAIN mới là câu trả lời chính xác.

Tổng kết

Tạo Index chỉ là bước khởi đầu, chứ chưa phải là đích đến của việc tối ưu truy vấn. Database luôn cân đo chi phí thực thi trước khi quyết định có dùng Index hay không, và rất nhiều thói quen viết query tưởng chừng vô hại - bọc hàm lên cột, tính toán trên cột, ép kiểu ngầm, LIKE '%keyword%', dùng OR/NOT, sai thứ tự Composite Index, hay lọc trên cột có độ chọn lọc thấp - đều có thể khiến Optimizer quay lưng với Index mà bạn đã dày công tạo ra.

Thay vì mặc định "có Index là nhanh", hãy tập thói quen kiểm tra bằng EXPLAIN hoặc EXPLAIN ANALYZE mỗi khi tối ưu một câu truy vấn quan trọng. Chỉ khi hiểu rõ cách database thực sự đọc dữ liệu, bạn mới viết được những câu query vừa đúng, vừa khai thác tối đa sức mạnh của Index.