Chào anh em Viblo!
Nếu anh em làm việc với database đủ lâu, chắc chắn sẽ có lúc gặp một tình "huống dở khóc dở cười" thế này: Mở query ra thấy chạy chậm, anh em hùng hổ thêm ngay một cái index. Xong xuôi chạy thử.. Ủa nó vẫn chậm? dùng EXPLAIN kiểm tra thì tá hỏa phát hiện cái DB nó "khinh" cái index của mình, nó tự quyết định quét toàn bộ bảng (Full Table Scan) luôn!
Lý do đằng sau "cú lừa" này thường nằm ở một quy tắc sống còn mà anh em hay quên khi tạo index nhiều cột (composite index): Left most Prefix Rule (quy tắc tiền tố trái nhất).
Hôm nay, từ những lần tối ưu hệ thống mướt mồ hôi, mình sẽ giải ngố cho anh em về quy tắc này một cách trực quan nhất.
1. Composite Index (Index nhiều cột) là gì?
Thay vì tạo Index cho từng cột lẻ tẻ, đôi khi chúng ta tạo một Index bao trùm nhiều cột cùng một lúc để phục vụ các câu query phức tạp.
Giả sử bạn có một bảng Users vào một Composite Index như sau:
INDEX idx_name_age_city (last_name, age, city)
Database sẽ gom 3 cột này lại và sắp xếp chúng thành một cấu trúc cây (B-Tree). Quan trong nhất là thứ tự sắp xếp. Nó sẽ ưu tiên sắp xếp theo last_name trước, hễ ai trùng last_name thì mới xét tiếp xem ai nhỏ age hơn để xếp lên trước, trùng cả age thì mới xét đến city
2. Left-most Prefix Rule là gì? (Quy tắc tiền tố trái nhất)
Quy tắc này phát biểu đơn giản như sau: Để database sử dụng được Composite Index, câu lệnh WHERE của bạn BẮT BUỘC phải chứa các cột tù TRÁI sang PHẢI của Index, và không được nhảy cóc.
Hãy tưởng tượng Composite Index của bạn là một Cuốn danh bạ điện thoại được sắp xếp theo: Họ -> Tên đệm -> Tên.
- Trường hợp 1 (Quy tắc đúng): Bạn muốn tìm một người có họ là "Nguyễn". Dễ ợt, lật ngay phần vần N, tìm chữ Nguyễn là ra. (Sử dụng Index thành công).
- Trường hợp 2 (Đúng quy tắc): Bạn tìm người Họ "Nguyễn", Tên đệm "Văn". Bạn lật phần vần N (Nguyễn), rồi tìm tiếp chữ V (Văn). Rất nhanh! (Sử dụng Index thành công).
- Trường hợp 3 (Lỗi nhảy cóc): Bạn muốn tìm người có Tên đệm là "Văn" (mà không biết Họ là gì). Lúc này, cuốn danh bạ hoàn toàn vô dụng vì nó được xếp theo Họ trước. Bạn bắt buộc phải lật TỪNG TRANG MỘT từ đầu đến cuối cuốn sổ để dò. (Full Table Scan - Index vô dụng!)
3. Phân tích các trường hợp Hit/Miss Index
Quay lại ví dụ INDEX (last_name, age, city) của chúng ta, đây là cách DB sẽ xử lý các câu WHERE:
- Hit (Ăn 100% Index): *
WHERE last_name = 'Nguyen'(Dùng 1 cột đầu) WHERE last_name = 'Nguyen' AND age = 25(Dùng 2 cột đầu)WHERE last_name = 'Nguyen' AND age = 25 AND city = 'Hanoi'(Dùng cả 3 cột)
(Lưu ý: Thứ tự các cột trong câu WHERE không quan trọng, query optimizer của DB đủ thông minh để tự sắp xếp lại, miễn là có đủ cột).
- Miss (Tạch 100% - Full Table Scan):
WHERE age = 25(Mất cộtlast_namengoài cùng bên trái)WHERE city = 'Hanoi'(Mất cộtlast_namevàage)WHERE age = 25 AND city = 'Hanoi'(Vẫn mất cộtlast_namengoài cùng bên trái)
Trường hợp đặc biệt (Partial Hit - Ăn một nửa):
WHERE last_name = 'Nguyen' AND city = 'Hanoi': Vì bạn "nhảy cóc" qua cột age, DB chỉ có thể sử dụng Index cho cột last_name. Tìm được một đống người họ Nguyễn xong, nó sẽ phải quét tuần tự đống đó để lọc ra ai ở Hanoi. Cột city trong Index không giúp ích được gì ở đây.
4. Đúc kết kinh nghiệm thực chiến
Từ quy tắc khắc nghiệt này, mình rút ra được vài bài học xương máu khi thiết kế DB:
- Thứ tự cột là Vua: Khi tạo Composite Index, cột nào hay được query độc lập nhất, và có tính phân loại (Cardinality) cao nhất thì hãy đặt ở tận cùng bên trái. Ví dụ, user_id hay email nên đặt trước status hay gender.
- Đừng tạo Index thừa thãi: Nếu bạn đã có INDEX(A, B), bạn không cần tạo thêm INDEX(A) nữa, vì quy tắc Left-most Prefix đã cover luôn trường hợp query mỗi cột A rồi. Nhưng nếu bạn hay query độc lập cột B, bạn PHẢI tạo thêm INDEX(B).
- Cẩn thận với dấu > hoặc <: Nếu trong câu WHERE có toán tử khoảng (Range query như >, <, BETWEEN), thì Index sẽ dừng lại ở cột đó. Các cột nằm bên phải cột Range sẽ không được dùng Index nữa. (Ví dụ: WHERE last_name = 'Nguyen' AND age > 20 AND city = 'Hanoi' -> Chỉ dùng Index cho last_name và age, cột city không được xài tới).
