Index Hit, Miss và Partial Hit: Đọc Hiểu "Bệnh Án" Của Database Khi Câu Query Chạy Chậm

Chào anh em Viblo! 👋

Sau khi nắm được nguyên tắc Left-most Prefix khi tạo Index, chắc hẳn anh em đã tự tin hơn khi "gõ" SQL rồi. Nhưng đời không như là mơ! Đôi khi mình tạo Index đúng chuẩn mực rồi, nhưng tốc độ hệ thống vẫn "rùa bò".

Để biết chính xác Database đang xử lý câu Query của mình như thế nào, chúng ta phải dùng lệnh EXPLAIN (trong MySQL/PostgreSQL) để xem Execution Plan (kế hoạch thực thi). Khi đọc cái plan này, anh em sẽ thường xuyên bắt gặp 3 trạng thái kinh điển: Index Hit, Index Miss, và cay cú nhất là Partial Hit.

Hôm nay, hãy cùng mình "bắt bệnh" xem ba trạng thái này thực chất là gì và tại sao chúng lại xảy ra nhé!

1. Index Hit (Full Hit) - Cú "Ăn Điểm" Tuyệt Đối 🎯

Đây là viễn cảnh tươi đẹp nhất mà mọi Dev đều khao khát.

Index Hit xảy ra khi Database Engine sử dụng trọn vẹn Index mà bạn đã tạo để tìm kiếm dữ liệu một cách trực tiếp và nhanh chóng nhất (thường được gọi là Index Seek). Nó giống như việc bạn mở mục lục sách, tìm đúng số trang và lật thẳng đến đó.

Dấu hiệu nhận biết:

• Lệnh EXPLAIN sẽ hiển thị loại truy cập (type) là ref, eq_ref, hoặc const.
• Số dòng (rows) phải quét rất nhỏ, thường chỉ bằng đúng số kết quả trả về.

Khi nào thì được "Hit"?

• Câu WHERE dùng đúng các cột đã được đánh Index.
• Không dùng hàm (function) bọc ngoài cột Index. (Ví dụ: WHERE status = 1).
• Truy vấn là Covering Index: Tức là tất cả các cột bạn gọi trong hàm SELECT đều đã nằm sẵn trong Index, DB không cần phải mò ngược vào bảng gốc để lấy thêm data nữa.

2. Index Miss - Pha "Trượt Chân" Chết Người 📉

Index Miss là thảm họa. Nó báo hiệu rằng Database đã nhìn thấy Index của bạn, nhưng nó quyết định: "Cái Index này vô dụng trong trường hợp này, thôi tao quét từ đầu đến cuối bảng cho nhanh!". Trạng thái này thường dẫn đến Full Table Scan (hoặc Index Scan toàn bộ).

Tại sao lại bị Miss? Đây là những sai lầm kinh điển:

• Tìm kiếm với ký tự đại diện ở đầu (LIKE '%abc'): Cuốn từ điển xếp theo chữ cái đầu tiên, mà bạn bắt nó tìm những từ kết thúc bằng chữ "abc". Từ điển chịu thua, bắt buộc phải lật từng trang.
  • Dùng hàm (Function) hoặc phép toán lên cột Index: * ❌ Sai: WHERE YEAR(created_at) = 2023 -> MISS (Vì DB phải tính toán hàm YEAR cho từng dòng rồi mới so sánh).
  • ✅ Đúng: WHERE created_at >= '2023-01-01' AND created_at <= '2023-12-31'-> HIT
  • Sai kiểu dữ liệu (Implicit Casting): Cột phone_number trong DB là kiểu VARCHAR (chuỗi), nhưng bạn lại query WHERE phone_number = 0987654321 (số). DB sẽ ngầm ép kiểu toàn bộ cột đó về số rồi mới so sánh -> Index phế võ công.
  • Vi phạm Left-most Prefix Rule: Như mình đã phân tích ở bài trước, nhảy cóc cột trong Composite Index sẽ dẫn đến Miss.

3. Partial Hit - "Nửa Nạc Nửa Mỡ" (Vừa Vui Vừa Tức) 🌗

Đây là trường hợp anh em hay nhầm lẫn nhất. Partial Hit (hoặc Partial Index Match) xảy ra khi Database có dùng Index, nhưng chỉ xài được một nửa tiềm năng, phần còn lại nó vẫn phải dùng "chân tay" để làm.

Trường hợp này thường rơi vào 2 kịch bản phổ biến:

Kịch bản A: Lủng lẳng câu lệnh SELECT * (Gây ra Key Lookup) Giả sử bạn có Index cho cột email. Bạn viết query: SELECT id, email, address, phone FROM Users WHERE email = 'test@viblo.asia'

1. Phần Hit: DB dùng Index để tìm rất nhanh ra vị trí của email test@viblo.asia.
2. Phần "Hành xác": Nhưng Index của bạn chỉ lưu id và email. Để lấy được address và phone, DB phải cầm cái ID vừa tìm được, quay ngược lại ổ cứng (bảng gốc) để "bốc" dữ liệu lên. Hành động quay lại này gọi là Key Lookup (hay Bookmark Lookup). Nếu kết quả trả về có hàng ngàn dòng, việc quay lại bảng gốc hàng ngàn lần sẽ làm DB chạy chậm rì, dù đã Hit Index.

Kịch bản B: Rớt đài vì Composite Index

Bạn có INDEX(A, B, C). Bạn query WHERE A = 1 AND C = 3.

• Nó Hit được cột A (tìm rất nhanh nhóm người có A = 1).
• Nhưng cột B bị khuyết, nên với điều kiện C = 3, nó không dùng Index được nữa. Nó phải quét tuần tự toàn bộ nhóm người có A = 1 ở trên để lọc ra ai có C = 3.

Đúc kết kinh nghiệm thực chiến 🛠️

Để hệ thống mượt mà, anh em hãy luôn nhớ câu thần chú: "Hit là mục tiêu, Miss là kẻ thù, và Partial Hit là thứ phải tối ưu".

1. Luôn dùng EXPLAIN: Đừng tin vào cảm giác. Viết query xong, chậm hay nhanh cũng hãy nhét chữ EXPLAIN đằng trước để xem DB có thực sự "Hit" Index không. Cột type, key và Extra trong bảng kết quả là những thứ cần soi kỹ.
2. Từ bỏ thói quen SELECT *: Chỉ SELECT những cột thực sự cần thiết. Nếu có thể, hãy gom các cột hay lấy cùng nhau vào một Index để tận dụng sức mạnh của Covering Index (loại bỏ hoàn toàn Key Lookup).
3. Tuyệt đối không dùng hàm bọc quanh cột Index: Luôn tính toán các biến số ở tầng Code (Application layer) trước, sau đó truyền giá trị tĩnh vào câu Query.

Hy vọng bài viết này giúp anh em hiểu rõ "bệnh án" của hệ thống và biết cách bốc thuốc cho chuẩn. Lần tới query chậm, nhớ chạy EXPLAIN soi xem mình đang Hit hay Miss nhé!

Anh em có ca "Partial Hit" nào hiểm hóc từng gặp phải chưa? Chia sẻ bên dưới nhé! Happy Coding!