Ba bài trước ca ngợi chỉ mục: nhanh 400 lần, cây rất nông. Nên dễ rút ra kết luận sai: "cứ đánh index lên cột hay lọc là truy vấn nhanh". Bài này đo mặt kia của tấm huy chương — khi nào một cột có index mà truy vấn vẫn không dùng nó, và vì sao đó là quyết định đúng của cơ sở dữ liệu. Tôi vấp đúng cái giả định "có index thì phải dùng index".
Index chỉ có lợi khi truy vấn chọn lọc
Chỉ mục nhanh nhờ nó cho phép bỏ qua phần lớn bảng: đi qua cây B-tree tới đúng vài hàng cần, rồi nhảy vào bảng lấy chúng. Cái lợi đó chỉ có nghĩa khi "vài hàng" thật sự là vài — khi truy vấn chọn lọc (selective), trả về một phần nhỏ số hàng.
Khi truy vấn khớp nhiều hàng — chẳng hạn 50% bảng — bức tranh lật ngược. Dùng index để lấy nửa bảng nghĩa là nhảy vào gần như mọi trang của bảng, mỗi lần một cú nhảy ngẫu nhiên (đọc ngẫu nhiên đắt gấp 4 lần đọc tuần tự, như bài cost model đã đo). Trong khi đó, quét tuần tự đọc toàn bộ bảng đúng một lượt, theo thứ tự đĩa, không nhảy nhót. Với đủ nhiều hàng, quét thẳng cả bảng một lần rẻ hơn nhảy vào từng trang qua index. Planner tính cost cả hai và chọn cái rẻ hơn — và ở độ chọn lọc thấp, nó bỏ index, chọn seq scan.
Đo: index thắng khi 1%, bị bỏ khi 50%
Tôi tạo bảng một triệu hàng với cột v (1000 giá trị, đánh index) và cột kich_hoat kiểu boolean (chỉ true/false, cũng đánh index). Trước hết một truy vấn chọn lọc — WHERE v < 10 (khoảng 1% số hàng):
index (Bitmap Heap Scan) : ~2,0 ms
ép quét tuần tự : ~9,3 ms
Index nhanh hơn khoảng 4,7 lần — đúng như kỳ vọng, vì chỉ cần lấy 1% hàng. Giờ một truy vấn không chọn lọc — WHERE kich_hoat = true, khớp khoảng 50% bảng. Dù cột kich_hoat có index, EXPLAIN cho:
Seq Scan on t (cost=... rows=502133)
Planner chọn quét tuần tự, bỏ hoàn toàn index idx_kh. Nó tính ra rằng để lấy nửa bảng, quét thẳng một lượt rẻ hơn nhảy vào gần hết số trang qua index. Đây không phải lỗi — đó là lựa chọn đúng: với 50% hàng, index chẳng giúp được gì, chỉ tổ tốn thêm bước. Để kiểm chứng, tôi thử ép quét tuần tự trên chính truy vấn v < 10 chọn lọc: nó chậm hẳn (9,3 ms so với 2,0 ms) — đúng là ở đó index mới thật sự đáng giá. Cùng một cơ chế index, mà một truy vấn thì nó là vũ khí, truy vấn kia thì nó là gánh nặng; khác biệt nằm ở độ chọn lọc, không nằm ở bản thân index.
Một lần tôi đo hớ: "có index thì phải dùng index"
Cái bẫy tôi bước vào nằm ở cột boolean. Tôi đánh index idx_kh lên kich_hoat, và đinh ninh rằng từ nay WHERE kich_hoat = true sẽ chạy nhanh nhờ index — tôi đã tạo nó ra cơ mà. Trong đầu tôi có một quy tắc ngầm: "cột có index thì truy vấn trên cột đó dùng index".
Nhưng EXPLAIN phũ phàng cho Seq Scan — planner ngó lơ index của tôi hoàn toàn. Lúc đầu tôi ngờ mình cấu hình sai, hay planner "hỏng". Theo kỷ luật, tôi kiểm lại: đây không phải công cụ sai, mà là giả định của tôi sai. Planner chỉ dùng index khi truy vấn chọn lọc. kich_hoat là boolean — chỉ hai giá trị — nên truy vấn tốt nhất cũng chỉ lọc còn ~50% bảng, không bao giờ đủ chọn lọc để index có lợi. Index trên một cột hai giá trị gần như vô dụng cho việc đọc.
Tệ hơn, nó không chỉ vô dụng mà còn có hại: mỗi index phải được cập nhật ở mỗi lần INSERT/UPDATE/DELETE trên bảng, nên một index không bao giờ được dùng để đọc vẫn âm thầm làm chậm mọi thao tác ghi và tốn dung lượng đĩa. Cái tôi đo hớ — "tạo index là truy vấn sẽ dùng" — dẫn thẳng tới thói quen đánh index bừa lên mọi cột, tạo ra một đống index chết chỉ làm chậm ghi mà chẳng tăng tốc đọc nào.
Bài học đo lường: một chỉ mục không phải cái công tắc bật-là-nhanh; nó là một lựa chọn mà planner cân nhắc theo độ chọn lọc của từng truy vấn. Đừng đo "cột có index chưa" mà hãy đo "truy vấn này có thật sự dùng index không" — chạy EXPLAIN và nhìn planner chọn gì. Index không "sai"; nó chỉ không hợp với truy vấn không chọn lọc, và cơ sở dữ liệu đủ khôn để bỏ qua nó.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: đừng đánh index lên cột có ít giá trị phân biệt (boolean, cột trạng thái với vài giá trị, cột giới tính). Không truy vấn nào trên chúng đủ chọn lọc để index có lợi, nên index chỉ tổ tốn chỗ và làm chậm ghi. Ngoại lệ đáng nói là khi phân bố lệch nhiều — ví dụ cột da_xoa mà 99,9% là false và bạn hầu như chỉ truy vấn 0,1% true; khi đó một partial index (WHERE da_xoa = true) mới đáng, nhưng đó là chuyện khác. Quy tắc chung: index đáng giá khi cột có nhiều giá trị phân biệt và truy vấn lọc còn ít hàng.
Hệ quả thứ hai: một index tồn tại không đảm bảo nó được dùng — luôn kiểm bằng EXPLAIN. Rất nhiều người thêm index rồi yên tâm rằng truy vấn đã nhanh, mà không kiểm tra planner có thật sự chọn nó không. Có thể index không được dùng vì truy vấn không chọn lọc (như bài này), vì kiểu dữ liệu không khớp (so sánh text với số), vì có hàm bọc quanh cột (WHERE lower(email) = ... không dùng index trên email thường), hoặc vì thống kê cũ. EXPLAIN là cách duy nhất để biết chắc, thay vì tin rằng "đã có index thì xong".
Hệ quả thứ ba là bài học đo lường. Con số mang theo: index nhanh khi truy vấn chọn lọc (v < 10, 1% hàng: nhanh 4,7 lần), nhưng bị planner bỏ khi truy vấn khớp nhiều hàng (boolean 50%: seq scan) — vì lấy nửa bảng qua index đắt hơn quét thẳng một lượt. Một chỉ mục là công cụ có điều kiện, không phải phép màu; giá trị của nó phụ thuộc độ chọn lọc của truy vấn, và cơ sở dữ liệu tự quyết định dùng hay bỏ. Đo "truy vấn có dùng index không" bằng EXPLAIN, đừng đoán từ "cột có index chưa".
Thử ba mươi giây
Trong cơ sở dữ liệu của bạn, tìm một truy vấn lọc trên cột có ít giá trị (một cột trạng thái, một boolean) và chạy EXPLAIN nó — nhiều khả năng bạn thấy Seq Scan dù cột đó có index, vì nó không đủ chọn lọc. Rồi kiểm kho index của bạn: SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan; — cột idx_scan là số lần mỗi index thật sự được dùng. Những index có idx_scan = 0 (hoặc rất thấp) sau một thời gian chạy là index chết: chúng làm chậm mọi lần ghi mà không tăng tốc đọc nào, và thường an toàn để xóa. Đó là cách đo index nào đang giúp thật, thay vì đếm xem bạn đã tạo bao nhiêu.
