EXPLAIN ANALYZE — Vũ khí săn query chậm trong database

Hồi đó mình nhận được cái alert query chạy 3.4 giây, mà table chỉ có hơn 200k dòng. Ngồi nhìn câu SQL qua lại hoài không hiểu tại sao nó chậm dữ vậy — index đủ, join đúng, mà query cứ ì ạch. Mãi tới lúc mình chịu ngồi đọc EXPLAIN ANALYZE mới vỡ lẽ: database hoàn toàn không xài cái index mình vừa khoe, mà quét cả table mỗi lần gọi. Từ đó mình nghiệm ra một điều — đoán mò performance database là con đường nhanh nhất dẫn tới đêm mất ngủ, còn EXPLAIN ANALYZE mới là chỗ bắt đầu đúng.

Ảnh: Markus Spiske — Pexels

EXPLAIN ANALYZE là gì?

Nói nôm na, EXPLAIN cho mình biết database dự định chạy câu query ra sao — còn ANALYZE là bắt nó chạy thiệt rồi báo lại số liệu cụ thể. Kết quả là một cái cây các bước (execution plan), mỗi bước ghi rõ: cách truy cập dữ liệu (Seq Scan hay Index Scan), số dòng ước tính, chi phí, và thời gian thực thi thật.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Đầu ra sẽ giống giống vầy:

Limit  (cost=0.56..12.30 rows=20 width=320) (actual time=0.05..0.09 rows=20)
  ->  Index Scan using idx_orders_customer_status on orders
      (cost=0.56..1012.4 rows=1785) (actual time=0.05..0.07 rows=20)
        Index Cond: ((customer_id = 42) AND (status = 'paid'::text))

Thấy Index Scan là mừng rồi. Còn nếu gặp cái này:

Seq Scan on orders  (cost=0.00..5234.80 rows=1785) (actual time=0.5..120.3 rows=1785)
  Filter: ((customer_id = 42) AND (status = 'paid'::text))

Nghĩa là database quét hết 200k dòng để lọc — và đây chính là lúc query lên tới mấy giây.

Đừng chỉ nhìn "chạy nhanh hay chậm"

Điều mình thích nhất ở EXPLAIN ANALYZE là nó phơi bày cái mà benchmark vội vàng giấu đi: ước tính vs thực tế. Cột rows đầu là dự đoán của planner, cột rows sau actual time là số liệu thật. Khi hai con số này lệch quá xa (ví dụ ước tính 100 dòng mà thực tế 100k dòng), database sẽ chọn sai plan — và đó là lúc mình cần chạy ANALYZE để cập nhật statistic, hoặc xem lại điều kiện WHERE có bị viết sai kiểu (type mismatch) khiến index bị bỏ qua.

Một lần khác mình gặp query đã có index mà vẫn Seq Scan. Xem kỹ mới biết: cột customer_id trong table là VARCHAR, còn query truyền số nguyên — Postgres không xài index được vì phải cast từng dòng. Sửa query truyền đúng kiểu là query chạy nhanh lại liền, chẳng cần thêm index nào.

Ảnh: RDNE Stock project — Pexels

Kinh nghiệm thực chiến

Mấy thứ mình đúc kết được sau nhiều lần "ngồi đồng" với query chậm:

• Chạy EXPLAIN ANALYZE trước khi thêm index. Nghe ngược đời, nhưng nhiều khi vấn đề không phải thiếu index mà là query viết sai kiểu, hoặc statistic cũ. Thêm index vô tội vạ tốn disk, chậm write, mà query vẫn chậm như thường.
• Index cho đúng cột lọc + cột sort. Với query ở trên, index (customer_id, status, created_at DESC) là đủ. Thứ tự cột trong index quan trọng: cột dùng cho WHERE bằng đứng trước, cột ORDER BY đứng sau.
• Chú ý actual time chứ đừng tin cost. Cost là con số ước tính của planner, actual time mới là sự thật ngoài đời.
• Lên kế hoạch chạy ANALYZE định kỳ sau những lần insert/xoá ồ ạt — không thì planner cứ vẽ plan trên dữ liệu cũ.

Lời kết

Sau vụ query 3.4 giây kia, mình giữ thói quen: bất kỳ query nào sắp lên production đều phải chạy qua EXPLAIN ANALYZE một lần, coi như "hít thở" trước khi ra mắt. Nghe tốn công, nhưng so với cảnh 2 giờ sáng dậy xử lý alert thì rẻ quá chừng. Query chậm không đáng sợ — đáng sợ là mình không biết database đang làm gì với query của mình.

📋 Phụ lục thuật ngữ

• Execution plan — cây các bước database thực hiện để chạy một query, từ đọc bảng, join tới sắp xếp
• Seq Scan (sequential scan) — quét tuần tự toàn bộ bảng, thường chậm khi bảng lớn và không có index phù hợp
• Index Scan — truy cập dữ liệu qua index, nhanh hơn Seq Scan rất nhiều khi lọc đúng cột
• Planner — bộ phận của database quyết định cách chạy query dựa trên statistic của bảng
• Statistic — số liệu thống kê (số dòng, phân bố giá trị) database thu thập qua lệnh ANALYZE để ước tính chi phí