Cú lừa về Index và cuộc chiến giữa SQL Server vs PostgreSQL
. Case Study 1: "Cùng một câu lệnh, tại sao một ông treo máy, một ông chạy vù vù?"
Mình có một bảng Users khoảng 24 triệu dòng (dữ liệu StackOverflow). Mình tạo một Index trên cột DownVote. Bài toán là tìm user theo số lượng DownVote:
• Case A: Tìm ông có DownVote = 980920 (Chỉ có 1 ông - Hàng hiếm).
• Case B: Tìm ông có DownVote = 0 (Có tới 2.2 triệu ông - Hàng phổ thông, chiếm 90% dữ liệu).
Cú "ngáo" của SQL Server (Vấn đề Parameter Sniffing)
Khi mình chạy một thủ tục (Stored Procedure) để tìm Case A trước:
• SQL Server thấy tìm 1 dòng -> Nó dùng Index Seek. Quá chuẩn, chạy siêu nhanh.
• Nó lưu luôn cái bản đồ (Execution Plan) này vào Cache (Plan Cache) để lần sau dùng lại cho đỡ mất công tính,.
Thảm họa xảy ra khi mình tìm Case B (2.2 triệu dòng):
• SQL Server lôi cái bản đồ cũ (dùng Index) ra áp dụng.
• Thay vì quét một mạch hết bảng (Full Scan) cho nhanh, nó lại hì hục nhảy vào Index, tìm địa chỉ, rồi nhảy sang bảng lấy dữ liệu... lặp lại 2.2 triệu lần,.
• Kết quả: Treo máy, CPU tăng vọt, chạy chậm rì dù có Index,. Đây gọi là lỗi Parameter Sniffing kinh điển – dùng sai chiến lược do cache cứng nhắc.
Note: Ngay cả bản SQL Server 2022 có tính năng "Parameter Sensitive Plan" (PSP) để chia nhỏ plan, nhưng thực tế nó vẫn chưa xử lý triệt để, đặc biệt với các phép so sánh lớn hơn/nhỏ hơn.
Sự "tinh tế" của PostgreSQL
Thằng PostgreSQL nó khôn hơn ở chỗ nó không "lười" như ông kia. Nó có 2 kiểu plan:
1. Custom Plan: Plan riêng cho từng tham số cụ thể.
2. Generic Plan: Plan chung chung dựa trên thống kê trung bình.
Thuật toán của nó như sau:
• 5 lần chạy đầu tiên: Nó luôn tính toán lại (Custom Plan) để xem tham số đó cần đi đường nào là ngon nhất.
• Lần thứ 6: Nó tính một cái Generic Plan, sau đó so sánh chi phí (Cost) của Generic này với trung bình cộng của 5 lần Custom trước đó,.
• Nếu thấy Generic ngon hơn hoặc xêm xêm thì dùng Generic. Nếu Custom ngon hơn hẳn thì nó tiếp tục tính lại Custom cho các lần sau.
👉 Kết quả: PostgreSQL tự động biết khi nào nên dùng Index (với dữ liệu hiếm) và khi nào nên quét Full bảng (với dữ liệu nhiều),. Quá nể!
2. Case Study 2: Bảng có 1 dòng, Join với chính nó mà mất... 1 phút?!
Đây là cái mình sốc nhất.
• Đề bài: Có một bảng bé tẹo, chỉ có 1 dòng, 1 cột ID. Không index, không phân mảnh gì cả.
• Thao tác: Join bảng này với chính nó khoảng 10 lần (kiểu Select * From Tbl Join Tbl ...).
• Kết quả: Chạy mất gần 1 phút!.
Tại sao? Mình cứ nghĩ chạy chậm là do máy tính toán lâu. Nhưng không, bảng có 1 dòng thì tính toán (Execution) chỉ mất vài mili-giây. Vấn đề nằm ở Compile Time (Thời gian biên dịch/tính toán đường đi).
• Khi câu lệnh quá phức tạp (Join lồng nhau nhiều tầng), Database Engine bị "lú". Nó mất toàn bộ thời gian chỉ để vẽ ra cái bản đồ (Execution Plan) xem đi đường nào, trong khi thời gian đi thật thì tí xíu.
• Mình thử xem chỉ cái Plan thôi mà nó đã load mất mấy chục giây chưa xong.
Giải pháp: Đừng bắt nó nghĩ một cục to đùng. Hãy chia nhỏ câu lệnh ra.
• Dùng bảng tạm (Temp Table). Join 2 bảng đầu, lưu vào Temp 1. Lấy Temp 1 join tiếp, lưu vào Temp 2....
• Kết quả: Chạy vèo phát xong, nhanh như điện.
3. Bài học rút ra cho Junior Dev (như mình)
1. Index không phải thuốc tiên: Với dữ liệu chiếm phần lớn bảng (ví dụ 90%), dùng Index còn chậm hơn là không dùng (quét Full bảng nhanh hơn),.
2. Statistics là trái tim: Database biết được nên dùng Index hay không là nhờ bảng thống kê (Statistics). Nếu cái này sai hoặc cũ, Database sẽ chọn sai đường,.
3. Chậm do đâu? Đừng chỉ nhìn thời gian chạy. Có thể nó chậm do đang "ngồi nghĩ" (Compile time) chứ không phải do "chân chạy chậm" (Execution time),.
4. Tư duy độc lập: Đừng thấy Microsoft hay hãng lớn mà sợ. Họ cũng có những vấn đề chưa giải quyết được (như vụ cache plan của SQL Server). Hiểu bản chất thì mình mới làm chủ được cuộc chơi,.
Hy vọng bài note này giúp anh em có cái nhìn khác về Database giống mình. Code không chỉ là gõ phím, mà là hiểu thằng Engine nó đang nghĩ gì!