Menu
Học tập
Khóa học Lộ trình nghề nghiệp Roadmap BA → CPO BA → Product Manager Tài liệu & Sách
Cộng đồng
Cộng đồng Interview Questions
Công cụ
Phân tích CV Theo dõi ứng tuyển Hiring Pipeline Luyện phỏng vấn Tối ưu LinkedIn Đàm phán lương
Tài khoản
ESC

Nhập từ khóa để tìm kiếm

↑↓ Di chuyển
Enter Mở
ESC Đóng

EXPLAIN plan: đọc kế hoạch query

SQL and Data Analysis cho BA Bài 25/60

Mở đầu — vì sao bài này quan trọng

Hãy tưởng tượng bạn là BA tại một sàn thương mại điện tử như Tiki. Sáng thứ Hai, bạn viết một query lấy báo cáo doanh thu theo danh mục cho ban giám đốc. Trên môi trường staging với vài nghìn dòng, query chạy trong nháy mắt. Nhưng khi đẩy lên production với 80 triệu dòng đơn hàng, cũng query đó treo cứng 4 phút, làm chậm cả hệ thống, và DBA nhắn tin hỏi "ai vừa chạy query gì mà CPU lên 90%?".

Câu hỏi đặt ra: tại sao cùng một câu SQL lại nhanh ở chỗ này và chậm chết ở chỗ kia? Câu trả lời nằm ở cách database quyết định thực thi query, chứ không phải ở cách bạn viết câu SQL. Và công cụ duy nhất cho bạn nhìn thấy quyết định đó chính là EXPLAIN.

Ở bài trước, bạn đã hiểu index là gì và vì sao nó quan trọng. Nhưng hiểu index trên lý thuyết là một chuyện; biết được liệu database có thực sự dùng index của bạn hay không lại là chuyện khác. EXPLAIN chính là chiếc kính hiển vi giúp bạn nhìn vào "bộ não" của database. Với một BA, kỹ năng đọc EXPLAIN không phải để bạn tự tay tối ưu engine — đó là việc của DBA — mà để bạn hỏi đúng câu hỏi, hiểu vì sao report của mình chậm, và trao đổi với đội kỹ thuật bằng ngôn ngữ chung. Đây là ranh giới phân biệt một BA "viết được SQL" với một BA "hiểu SQL".

Khái niệm cốt lõi

EXPLAIN làm gì?

EXPLAIN đặt trước một câu query sẽ yêu cầu database trả về kế hoạch thực thi (execution plan) — bản mô tả từng bước database dự định làm để lấy ra kết quả, mà không thực sự chạy câu query đó (với EXPLAIN thường). Nó cho bạn biết:

  • Database sẽ quét toàn bộ bảng (full table scan) hay dùng index để tìm dữ liệu.
  • Khi JOIN nhiều bảng, database bắt đầu từ bảng nào và ghép theo thứ tự nào.
  • Database ước lượng phải đọc bao nhiêu dòng để hoàn thành.
  • Có phải tạo bảng tạm (temporary table) hay sắp xếp tốn kém (filesort) hay không.
Cú pháp cơ bản đơn giản đến bất ngờ:

EXPLAIN
SELECT * FROM orders WHERE customer_id = 12345;

Phân biệt EXPLAIN và EXPLAIN ANALYZE

Đây là điểm rất nhiều người nhầm:

  • EXPLAIN: chỉ đưa ra kế hoạch dự kiến dựa trên ước lượng của database. Không chạy query thật, an toàn tuyệt đối, trả kết quả tức thì.
  • EXPLAIN ANALYZE: thực sự chạy query rồi báo cáo lại thời gian thật và số dòng thật. Chính xác hơn nhiều, nhưng vì nó chạy thật nên cẩn thận với UPDATE/DELETE — nó sẽ thực sự thay đổi dữ liệu (trong PostgreSQL cần bọc trong transaction rồi ROLLBACK).
Lời khuyên cho BA: dùng EXPLAIN để xem nhanh, dùng EXPLAIN ANALYZE (trên môi trường read-only hoặc với SELECT) khi muốn con số thật.

Đọc các trường quan trọng trong MySQL

Khi chạy EXPLAIN trên MySQL, bạn nhận một bảng với các cột. Bốn cột bạn cần thuộc lòng:

1. type — kiểu truy cập, quan trọng bậc nhất. Đây là tín hiệu rõ nhất về việc query nhanh hay chậm. Xếp từ tốt đến tệ:

  • const / eq_ref: tìm theo khóa chính, nhanh nhất. Tuyệt vời.
  • ref: tìm theo index không unique. Tốt.
  • range: quét một khoảng theo index (ví dụ WHERE date BETWEEN ...). Chấp nhận được.
  • index: quét toàn bộ index. Tạm ổn nhưng cần để ý.
  • ALL: full table scan — quét sạch cả bảng. Đây là dấu hiệu đỏ với bảng lớn.
Nguyên tắc nằm lòng: thấy type = ALL trên một bảng vài chục triệu dòng là phải dừng lại suy nghĩ ngay.

2. key: index nào thực sự được dùng. Nếu là NULL, nghĩa là database không dùng index nào cả.

3. rows: ước lượng số dòng database phải kiểm tra. Con số càng nhỏ càng tốt. Nếu bạn lọc ra 100 dòng nhưng rows báo 50 triệu, có gì đó sai.

4. Extra: ghi chú thêm. Cảnh giác với hai cụm:

  • Using filesort: database phải sắp xếp thủ công, tốn kém.
  • Using temporary: phải tạo bảng tạm, thường gặp khi GROUP BY không có index hỗ trợ.
  • Ngược lại, Using index (index-only scan) là tín hiệu tốt: lấy được dữ liệu ngay từ index mà không cần đụng vào bảng.

Đọc EXPLAIN trong PostgreSQL

PostgreSQL trình bày kế hoạch dưới dạng cây các node, đọc từ trong ra ngoài, từ dưới lên. Các thuật ngữ tương đương:

  • Seq Scan (Sequential Scan) = full table scan, tương đương ALL của MySQL.
  • Index Scan / Index Only Scan = dùng index, tốt.
  • Bitmap Heap Scan = trung gian, dùng khi cần lấy nhiều dòng qua index.
  • Nested Loop, Hash Join, Merge Join = ba thuật toán JOIN khác nhau.
Mỗi node có chú thích cost=0.00..431.00 rows=5000 width=24. Con số cost là chi phí ước lượng (đơn vị tương đối, không phải giây): số đầu là chi phí khởi động, số sau là chi phí hoàn thành. rows là số dòng ước lượng. Bạn không cần hiểu cách tính cost; chỉ cần biết cost lớn = bước đắt đỏ, và đó là nơi cần tối ưu.

Tình huống thực tế

Tình huống 1: Báo cáo doanh thu chậm tại sàn TMĐT

Một BA tại sàn TMĐT giả định "ShopViet" viết query lọc đơn hàng theo email khách:

EXPLAIN
SELECT order_id, total_amount, created_at
FROM orders
WHERE customer_email = 'nguyenvana@gmail.com';

Kết quả EXPLAIN trên MySQL:

type: ALL    key: NULL    rows: 82,400,000    Extra: Using where

Nhìn vào đây, BA hiểu ngay vấn đề: type = ALL, key = NULL, và rows lên tới 82 triệu. Database đang quét sạch cả bảng 82 triệu dòng chỉ để tìm vài đơn của một khách. Không hề có index trên cột customer_email.

BA mang phát hiện này sang đội DBA: "Cột customer_email đang bị full scan trên bảng 82 triệu dòng, mình cần một index ở đó." DBA thêm index, chạy lại EXPLAIN:

type: ref    key: idx_customer_email    rows: 6    Extra: Using where

Giờ type = ref, dùng index idx_customer_email, và chỉ phải đọc 6 dòng. Query từ 4 phút rút xuống dưới 50 mili-giây.

Bài học: BA không cần tự tạo index, nhưng nhờ đọc được EXPLAIN, bạn phát hiện đúng nguyên nhân và yêu cầu đúng giải pháp. Nếu không có EXPLAIN, bạn chỉ biết kêu "report chậm quá" — một lời than vô dụng với đội kỹ thuật.

Tình huống 2: JOIN sai thứ tự ở công ty gọi xe

Một analyst tại hãng gọi xe giả định "GoMove" (tương tự Be/Grab) ghép bảng trips (45 triệu chuyến) với drivers (200 nghìn tài xế) để tính doanh thu theo thành phố:

EXPLAIN
SELECT d.city, SUM(t.fare)
FROM trips t
JOIN drivers d ON t.driver_id = d.driver_id
WHERE d.city = 'Hồ Chí Minh'
GROUP BY d.city;

EXPLAIN cho thấy database bắt đầu từ bảng trips 45 triệu dòng trước, rồi mới lọc drivers, kèm Extra: Using temporary; Using filesort. Database đọc 45 triệu chuyến rồi mới loại bớt — cực kỳ lãng phí, vì thực ra chỉ một phần tài xế ở TP.HCM.

Sau khi thêm index trên drivers.city và trips.driver_id, EXPLAIN cho thấy database đảo thứ tự: bắt đầu từ drivers (lọc ra ~30 nghìn tài xế TP.HCM trước), rồi mới ghép sang trips qua index. Số dòng quét giảm hàng chục lần.

Bài học: Thứ tự JOIN do database tự quyết, nhưng nó phụ thuộc vào việc có index hay không và độ chọn lọc (selectivity) của điều kiện lọc. EXPLAIN cho bạn thấy database có chọn thứ tự khôn ngoan không. Khi thấy nó "khởi đầu từ bảng to nhất", đó thường là dấu hiệu thiếu index.

Tình huống 3: ORDER BY bí mật làm chậm dashboard fintech

Một BA tại ví điện tử giả định "PayViet" (kiểu MoMo/ZaloPay) xây dashboard "20 giao dịch mới nhất":

EXPLAIN
SELECT txn_id, amount, created_at
FROM transactions
WHERE status = 'SUCCESS'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN báo Extra: Using filesort, rows: 12,000,000. Dù chỉ lấy 20 dòng, database phải sắp xếp toàn bộ 12 triệu giao dịch thành công theo thời gian rồi mới cắt lấy 20. Filesort trên 12 triệu dòng là thủ phạm khiến dashboard tải 8 giây.

Giải pháp đội DBA đưa ra: tạo composite index (status, created_at). Index này đã sắp sẵn theo created_at trong từng nhóm status, nên database chỉ cần đọc 20 dòng đầu tiên từ index — Using filesort biến mất, dashboard tải dưới 0.1 giây.

Bài học: Using filesort và Using temporary là hai "cờ đỏ" trong cột Extra mà BA cần học cách nhận ra. Chúng thường ẩn sau những query trông rất vô hại như "lấy N dòng mới nhất".

Hướng dẫn từng bước

Đây là quy trình thực dụng để một BA dùng EXPLAIN khi một report bị chậm:

Bước 1 — Đặt EXPLAIN trước query nghi vấn. Đừng đoán mò vì sao chậm. Chạy:

EXPLAIN
SELECT ... -- nguyên văn query đang chậm

Bước 2 — Tìm full scan trước tiên. Trên MySQL, quét cột type tìm giá trị ALL. Trên PostgreSQL, tìm Seq Scan trên bảng lớn. Đây là thủ phạm phổ biến nhất.

Bước 3 — Đối chiếu rows với thực tế. Hỏi: "Kết quả cuối cùng của mình có bao nhiêu dòng?" Nếu bạn chỉ cần 50 dòng nhưng rows ước lượng hàng triệu, database đang đọc thừa rất nhiều — dấu hiệu thiếu index trên cột lọc.

Bước 4 — Kiểm tra cột key / loại scan. Nếu key = NULL ở một bảng lớn có điều kiện WHERE, gần như chắc chắn thiếu index trên cột đó.

Bước 5 — Soi cột Extra. Thấy Using filesort hoặc Using temporary thì để ý phần ORDER BY và GROUP BY — đó thường là nơi cần composite index.

Bước 6 — Với JOIN, đọc thứ tự thực thi. Database có bắt đầu từ bảng nhỏ/được lọc mạnh không, hay khởi động từ bảng khổng lồ? Thứ tự sai thường do thiếu index trên khóa JOIN.

Bước 7 — Ghi lại phát hiện và trao đổi với DBA. Đừng tự ý thêm index trên production. Hãy viết rõ: "Query X bị type=ALL trên bảng orders (82 triệu dòng) do thiếu index trên customer_email; đề xuất thêm index." Đó là cách BA đóng góp giá trị mà không vượt quyền.

Lỗi thường gặp & mẹo

Lỗi 1 — Tin tuyệt đối vào rows. Con số rows là ước lượng dựa trên thống kê của database, không phải số thật. Nếu thống kê cũ (lâu chưa ANALYZE bảng), ước lượng có thể lệch xa. Khi nghi ngờ, dùng EXPLAIN ANALYZE để có số thật.

Lỗi 2 — Chạy EXPLAIN ANALYZE với UPDATE/DELETE mà không bọc transaction. Nhớ rằng ANALYZE chạy thật. Trên PostgreSQL hãy làm: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. Với SELECT thì an toàn.

Lỗi 3 — EXPLAIN trên staging rồi suy ra production. Kế hoạch phụ thuộc kích thước dữ liệu. Bảng 5 nghìn dòng ở staging thì full scan vẫn nhanh, nên database có thể chọn full scan. Trên production 50 triệu dòng, kế hoạch sẽ khác hẳn. Luôn EXPLAIN trên môi trường có dữ liệu giống production (thường là read-replica).

Lỗi 4 — Quên rằng SELECT * cũng ảnh hưởng kế hoạch. Lấy hết cột có thể khiến database không dùng được index-only scan. Chỉ chọn cột cần thiết đôi khi giúp tận dụng Using index.

Mẹo dùng định dạng dễ đọc. PostgreSQL hỗ trợ EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) cho thông tin chi tiết. MySQL 8 có EXPLAIN ANALYZE và EXPLAIN FORMAT=TREE trình bày dạng cây dễ đọc hơn bảng truyền thống.

Mẹo dùng công cụ trực quan. Với kế hoạch phức tạp, dán output PostgreSQL vào các công cụ như explain.dave.cm hay explain.depesz.com để xem trực quan, đỡ phải đọc cây node bằng mắt thường.

Bài tập thực hành

Hãy thực hành trên một bảng orders mô phỏng (giả định vài triệu dòng):

  • Phát hiện full scan. Chạy EXPLAIN SELECT * FROM orders WHERE customer_email = 'test@example.com'; khi chưa có index. Ghi lại giá trị type (MySQL) hoặc loại scan (PostgreSQL). Bạn thấy ALL / Seq Scan chứ?
  • Quan sát index phát huy tác dụng. Tạo index CREATE INDEX idx_email ON orders(customer_email); rồi chạy lại EXPLAIN. So sánh type, key, và rows trước và sau. Con số rows thay đổi thế nào?
  • Bắt thủ phạm filesort. Chạy EXPLAIN SELECT * FROM orders WHERE status = 'SUCCESS' ORDER BY created_at DESC LIMIT 10;. Cột Extra có Using filesort không? Thử tạo composite index (status, created_at) rồi kiểm tra lại.
  • So sánh ước lượng với thực tế. Chạy EXPLAIN ANALYZE cho một trong các query trên, đối chiếu rows ước lượng với số dòng thật mà database báo cáo. Chúng có khớp không?
  • Đọc thứ tự JOIN. Viết một query JOIN hai bảng và dùng EXPLAIN xác định database bắt đầu từ bảng nào. Thử thêm WHERE lọc mạnh trên bảng nhỏ và xem thứ tự có thay đổi không.
Hãy viết lại bằng lời của bạn: với mỗi bài, một câu giải thích "vì sao kế hoạch thay đổi". Khả năng diễn giải này mới là thứ nhà tuyển dụng tìm kiếm ở một BA.

Tóm tắt

EXPLAIN là chiếc kính hiển vi cho phép BA nhìn thấy cách database thực thi query thay vì chỉ thấy kết quả cuối. Với một BA, mục tiêu không phải tự tay tối ưu engine, mà là chẩn đoán đúng nguyên nhân query chậm và trao đổi chính xác với đội kỹ thuật.

Những điểm cần nhớ: cột type trong MySQL (cảnh giác ALL) và loại scan trong PostgreSQL (cảnh giác Seq Scan) cho biết có dùng index hay không; cột rows cho biết database đọc thừa bao nhiêu; cột Extra với Using filesort / Using temporary là cờ đỏ thường gặp; và với JOIN, thứ tự thực thi tiết lộ database có chọn đường đi khôn ngoan không. Phân biệt EXPLAIN (chỉ dự đoán, an toàn) với EXPLAIN ANALYZE (chạy thật, chính xác hơn nhưng cần cẩn thận với DML). Và luôn nhớ: kế hoạch phụ thuộc dữ liệu, nên hãy EXPLAIN trên môi trường giống production. Nắm vững kỹ năng này, bạn không chỉ viết được SQL — bạn hiểu SQL, và đó là thứ phân biệt một BA bình thường với một BA đáng tin cậy.

Trước Tiếp