Hai bài đầu sê-ri đều dùng EXPLAIN ANALYZE để nhìn cơ sở dữ liệu làm gì. Giờ dừng lại mổ chính công cụ đó — vì nó là dụng cụ đo quan trọng nhất khi làm việc với truy vấn, và cũng là nơi dễ đọc nhầm nhất. Có hai biến thể: EXPLAIN và EXPLAIN ANALYZE, khác nhau một trời một vực. Bài này đo sự khác biệt đó, và vấp đúng cái bẫy đọc nhầm một con số trong kế hoạch.
Một cái đoán, một cái chạy
EXPLAIN <truy vấn> cho bạn xem kế hoạch mà trình lập kế hoạch (planner) định chạy: nó chọn quét tuần tự hay quét chỉ mục, join kiểu gì, sắp xếp ở đâu — kèm các con số ước lượng như cost và rows. Điểm mấu chốt: EXPLAIN không thật sự chạy truy vấn. Nó lập kế hoạch rồi in ra, tức thì, an toàn, kể cả với truy vấn nặng.
EXPLAIN ANALYZE <truy vấn> thì chạy thật truy vấn, rồi thêm vào kế hoạch những con số đo được trên lần chạy đó: actual time (thời gian thật mỗi node), actual rows (số hàng thật đi qua mỗi node), và loops (số lần node được lặp). Nói cách khác, EXPLAIN là dự báo, EXPLAIN ANALYZE là dự báo kèm kết quả thực tế đặt cạnh nhau — và chính việc đặt cạnh nhau đó là thứ quý giá nhất.
Đo: planner đoán 99, thực tế 10000
Tôi dựng một bảng một triệu hàng với một cái bẫy cố ý: hai cột nhom và nhom_ban_sao luôn bằng nhau (cùng một nguồn). Rồi truy vấn với điều kiện trên cả hai: WHERE nhom = 7 AND nhom_ban_sao = 7.
Chạy EXPLAIN thuần, planner ước lượng:
Seq Scan on don_hang (cost=... rows=99)
Filter: ((nhom = 7) AND (nhom_ban_sao = 7))
Nó đoán truy vấn trả về 99 hàng. Giờ chạy EXPLAIN ANALYZE để xem thực tế:
Seq Scan ... (cost=... rows=99) (actual ... rows=10000)
Rows Removed by Filter: ...
Execution Time: 11.3 ms
Thực tế là 10000 hàng — gấp 100 lần con số planner đoán. Vì sao planner sai xa vậy? Vì nó giả định hai điều kiện độc lập với nhau: mỗi điều kiện lọc còn 1/100 số hàng (có 100 nhóm), nên nó nhân hai xác suất: 1/100 × 1/100 = 1/10000, ra ~99 hàng trên một triệu. Nhưng hai cột của tôi trùng khít nhau — hễ nhom = 7 thì nhom_ban_sao cũng bằng 7 — nên điều kiện thứ hai chẳng lọc thêm gì; thực tế vẫn là 1/100, tức 10000 hàng. Để đối chứng, tôi thử với một điều kiện WHERE nhom = 7: planner đoán 9967, thực tế 10000 — khớp gần như hoàn hảo. Sai lệch chỉ xuất hiện khi có tương quan mà planner không biết.
Một lần tôi đo hớ: đọc "rows" như số thật
Khi mới nhìn output EXPLAIN, tôi thấy rows=99 và ghi ngay vào ghi chú: "truy vấn này trả về khoảng 99 hàng". Con số nằm ngay trên dòng kế hoạch, rõ ràng, nên tôi coi nó là sự thật về truy vấn.
Rồi EXPLAIN ANALYZE cho actual rows=10000. Hai con số chọi nhau gấp trăm lần. Theo kỷ luật đo lường của sê-ri, hai số mâu thuẫn nghĩa là tôi đang đọc nhầm đại lượng — và đúng vậy. rows trong EXPLAIN không phải số hàng thật; nó là ước lượng của planner, tính từ thống kê bảng (histogram phân bố giá trị) cộng với một giả định lớn: các điều kiện độc lập nhau. Khi giả định đó sai — như hai cột tương quan của tôi — ước lượng lệch rất xa. actual rows của EXPLAIN ANALYZE mới là số đếm thật trên lần chạy.
Bài học kép ở đây. Thứ nhất, giống như cost không phải thời gian (bài phần 1), rows ước lượng không phải số hàng thật — luôn tin con số actual của ANALYZE, không tin con số đoán của EXPLAIN thuần. Thứ hai, và tinh tế hơn: khoảng cách giữa rows ước lượng và actual rows tự nó là một chẩn đoán. Lệch xa (như 99 so với 10000) là dấu hiệu planner đang "mù" về dữ liệu — hoặc vì thống kê cũ (chưa ANALYZE sau khi nạp dữ liệu), hoặc vì các cột tương quan mà nó tưởng độc lập. Và một planner đoán sai số hàng dễ chọn sai cả kế hoạch (ví dụ chọn nested loop vì tưởng chỉ có 99 hàng, trong khi 10000 hàng thì hash join mới hợp). Nên khi một truy vấn chậm bất thường, việc đầu tiên là chạy EXPLAIN ANALYZE và so cột estimated với actual ở mỗi node — chỗ lệch nhiều nhất thường là gốc rễ.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên là một cảnh báo an toàn phải khắc cốt: EXPLAIN ANALYZE CHẠY THẬT truy vấn — kể cả INSERT, UPDATE, DELETE. Tôi kiểm chứng: chạy EXPLAIN ANALYZE UPDATE don_hang SET tien = -1 WHERE nhom = 7 và đếm lại — 10000 hàng đã bị sửa thật. EXPLAIN (không ANALYZE) thì an toàn vì chỉ lập kế hoạch, nhưng EXPLAIN ANALYZE thực thi. Muốn xem kế hoạch thật của một lệnh ghi mà không đổi dữ liệu, phải bọc trong giao dịch rồi hủy: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; — tôi thử và sau ROLLBACK thì 0 hàng thay đổi. Quên điều này trên cơ sở dữ liệu thật là một tai nạn chờ sẵn.
Hệ quả thứ hai: đọc kế hoạch là đọc một cái cây, từ trong ra ngoài. Kế hoạch có cấu trúc lồng nhau — node con (thụt vào, có mũi tên ->) chạy trước và đẩy kết quả lên node cha. actual time ở mỗi node là cộng dồn (gồm cả thời gian các node con), và nhớ nhân với loops để ra tổng thật. Đọc từ node trong cùng ra ngoài, tìm node nào có actual time nhảy vọt hoặc actual rows lệch xa ước lượng — đó là chỗ tối ưu. Trong phép đo trên, node quét tuần tự có actual time chiếm gần hết 11ms, còn Rows Removed by Filter cho thấy nó phải xét và loại bỏ đúng chỗ nào — những chi tiết mà một dòng cost đơn lẻ không bao giờ nói cho bạn. Đây là kỹ năng đọc, không phải đọc một con số duy nhất.
Hệ quả thứ ba là bài học đo lường chung. Con số mang theo: EXPLAIN chỉ đoán (không chạy, cho cost/rows ước lượng); EXPLAIN ANALYZE chạy thật (cho actual time/actual rows) — và độ lệch giữa ước lượng với thực tế, như 99 so với 10000 khi hai cột tương quan, vừa cho biết con số nào đáng tin vừa tố cáo planner đang đoán mù. Cơ sở dữ liệu cho bạn cả dự báo lẫn kết quả; biết cái nào là đoán và cái nào là đo, rồi so hai cái với nhau, là toàn bộ nghệ thuật đọc kế hoạch.
Thử ba mươi giây
Chọn một truy vấn có nhiều điều kiện WHERE ... AND ... trong cơ sở dữ liệu của bạn và chạy EXPLAIN ANALYZE nó. Ở mỗi dòng kế hoạch, để ý cặp số: rows=... (ước lượng, trước ngoặc) và actual ... rows=... (thật, trong ngoặc actual). Nếu chúng gần nhau, planner đang hiểu đúng dữ liệu; nếu lệch tới hàng chục hay hàng trăm lần, bạn vừa tìm ra lý do một kế hoạch có thể tồi — thường là thống kê cũ (thử ANALYZE ten_bang; rồi chạy lại) hoặc các cột tương quan. Và nếu truy vấn là UPDATE/DELETE, đừng chạy EXPLAIN ANALYZE trần — bọc BEGIN; ... ROLLBACK; để xem kế hoạch mà không đụng vào dữ liệu.
