Tối ưu SQL bằng phân tích Query Plan — Nhìn EXPLAIN như hacker

1. Tại sao phải đọc EXPLAIN? — Không tối ưu bằng cảm tính

EXPLAIN cho bạn “bản đồ” trình tối ưu truy vấn dự kiến (planner/optimizer). Nếu chỉ nhìn query mà đoán index cần thêm, dễ sai. EXPLAIN cho biết: bảng nào bị quét (seq scan), bảng nào dùng index, số dòng ước tính, cách planner nối bảng (nested loop / hash join), và các hành động tốn kém (filesort, temp table). Quy tắc đơn giản: tìm cái tốn nhất trong plan và sửa nó.

2. Khái niệm nhanh các thành phần thường thấy

• Seq scan / Full table scan: đọc toàn bộ bảng — thường tệ với bảng lớn.
• Index scan / Index seek: dùng index, nhanh nếu selectivity tốt.
• Index-only scan: chỉ đọc index, không cần truy xuất heap/table — rất nhanh nếu phủ index.
• Nested loop / Hash join / Merge join: chiến lược join; nested loop tốt với dataset nhỏ hoặc có index, hash join tốt với join lớn mà memory đủ.
• Rows / estimated rows: planner ước tính; chênh lớn giữa ước tính và thực tế là dấu chặn cần ANALYZE statistics.
• Extra / Buffers: MySQL Extra (Using where, Using temporary, Using filesort); Postgres buffers gösterir I/O usage.

3. Quy trình tối ưu một truy vấn — checklist

1. Chụp truy vấn thật chậm (slow log / APM / pg_stat_statements).
2. Chạy EXPLAIN (MySQL) / EXPLAIN ANALYZE (Postgres) trên bản copy test.
3. Tìm step có cost lớn / seq scan / filesort / temp table / huge nested loop.
4. Kiểm tra column used in WHERE/JOIN/ORDER/GROUP BY — có index phù hợp không?
5. Thử sửa: thêm index, rewrite JOIN, giới hạn cột (avoid SELECT *), or materialized view.
6. Chạy EXPLAIN ANALYZE lại, so sánh thời gian và kế hoạch.

4. EXPLAIN MySQL — đọc các cột quan trọng

MySQL EXPLAIN (truyền thống) trả về: id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra. Các điểm chú ý:

• type: đánh giá access method — ALL (tệ), index, range, ref, eq_ref (tốt nhất).
• possible_keys vs key: có index phù hợp không? nếu possible_keys có nhưng key = NULL nghĩa optimizer không dùng được vì selectivity hoặc chức năng áp lên cột.
• rows: ước tính số hàng cần quét; nếu lớn nghĩa truy vấn tốn I/O.
• Extra: chú ý “Using where”, “Using index”, “Using temporary”, “Using filesort”. Filesort/temporary = cần sửa ORDER BY / GROUP BY hoặc làm index hỗ trợ.

5. EXPLAIN ANALYZE PostgreSQL — đọc kỹ hơn

Postgres cho cả actual time / rows cùng với estimates. Ví dụ output rút gọn:

Seq Scan on orders  (cost=0.00..5000.00 rows=100000 width=64) (actual time=0.12..120.45 rows=100000 loops=1)
  Filter: (created_at > now() - '7 days'::interval)
  Buffers: shared hit=1000 read=200

Chú ý: nếu actual rows >> estimated rows — statistics (ANALYZE) cần cập nhật hoặc planner bị lừa do distribution skewed.

6. Ví dụ thực chiến — trường hợp phổ biến và cách sửa

Tình huống: bảng orders có millions rows, truy vấn trả về recent orders của một user với ORDER BY created_at DESC LIMIT 20 — nhưng chạy chậm.

-- Query ban đầu
SELECT * FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN có thể cho thấy full table scan (MySQL type = ALL hoặc Postgres Seq Scan) vì không có index phù hợp. Cách sửa:

-- Thêm index phù hợp (MySQL / Postgres)
CREATE INDEX idx_orders_user_created_at ON orders (user_id, created_at DESC);

-- Hoặc (Postgres) nếu muốn index-only(scan) và chỉ cần vài cột:
CREATE INDEX idx_orders_user_created_at_partial ON orders (user_id, created_at DESC) WHERE status = 'active';

Ghi chú: thứ tự cột trong composite index quan trọng — cột trong WHERE nên đứng trước, cột dùng cho ORDER BY có thể đứng sau để tận dụng index ordering và tránh filesort.

7. Covering index và index-only scan

Nếu truy vấn chỉ select những cột có trong index (covering), DB có thể đọc index mà không cần truy cập table heap — gọi là index-only scan (Postgres) hoặc Using index (MySQL). Ví dụ:

SELECT id, total, created_at FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

-- index bao phủ:
CREATE INDEX idx_orders_covering ON orders (user_id, created_at DESC, total, id);

Lợi ích: giảm I/O, tăng tốc đáng kể trên bảng lớn.

8. JOIN tốn kém — hiểu nested loop vs hash join

Nếu plan cho thấy nested loop với outer scan quét lớn và inner table cũng quét nhiều, nested loop có thể rất chậm. Ví dụ:

-- Chạy JOIN đơn giản
SELECT o.id, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > now() - interval '30 days';

Optimizer chọn nested loop khi ước tính các hàng nhỏ hoặc inner có index. Nếu không, cần:

• Thêm index trên join key (users.id thường đã là PK).
• Đảm bảo statistics được cập nhật (ANALYZE).
• Nếu dataset lớn, cân nhắc hash join (Postgres tự chuyển nếu tốt) hoặc thay đổi reordering / enable_hashjoin=false chỉ để test.
• Hoặc tách truy vấn: pre-filter orders vào temp table hoặc materialized view nếu reuse.

9. ORDER BY + GROUP BY gây filesort/temporary — cách xử lý

Các dấu hiệu: MySQL Extra chứa “Using temporary; Using filesort”. Postgres sử dụng Sort node lớn. Cách tối ưu:

• Thêm index hỗ trợ ORDER BY, ví dụ (col1, col2) để tránh sort.
• Giảm dữ liệu trước khi GROUP BY (subquery filter hoặc WHERE).
• Sử dụng aggregates có index-friendly patterns hoặc pre-aggregate (materialized view).

10. SUBQUERY vs JOIN — khi nào nên đổi

Subquery/IN có thể bị planner xử lý kém hơn JOIN (tùy engine và phiên bản). Ví dụ:

-- Chậm khi dùng IN
SELECT * FROM posts WHERE author_id IN (SELECT id FROM authors WHERE active = 1);

-- Thay bằng JOIN có thể tốt hơn
SELECT p.* FROM posts p
JOIN authors a ON p.author_id = a.id
WHERE a.active = 1;

Test bằng EXPLAIN; nếu subquery bị materialize thành temp table, join/better rewrite có thể nhanh hơn.

11. Các anti-pattern thường gặp và cách sửa

• WHERE function(col) = … — ví dụ WHERE LOWER(name) = 'bob' sẽ không dùng index; giải pháp: tạo column duy trì giá trị đã chuẩn hóa hoặc sử dụng functional index (Postgres) / indexed generated column (MySQL 8.0+).
• SELECT * — lấy nhiều cột không cần thiết làm tăng I/O; chỉ SELECT cột cần thiết.
• ORDER BY RAND() — tốn kém; dùng sampled id hoặc reservoir sampling cho random.
• Over-indexing — quá nhiều index làm write chậm; chỉ tạo index cần thiết cho đọc quan trọng.

12. Statistics & ANALYZE — planner cần dữ liệu tốt

Nếu estimates lệch xa thực tế, planner chọn kém. Hành động:

• Chạy ANALYZE table_name (Postgres) hoặc ANALYZE TABLE (MySQL) để cập nhật stats.
• Với Postgres, tăng sampling hoặc set session_statistics_target nếu data skewed.
• Xem histogram/ndistinct nếu phân bố dữ liệu lạ (null-heavy, highly-skewed).

13. EXPLAIN FORMAT=JSON / verbose — để debug sâu

Dùng JSON output (MySQL EXPLAIN FORMAT=JSON, Postgres EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)) để inspect plan tree, I/O, buffer usage, timing. Rất hữu ích khi plan phức tạp nhiều join.

14. Thực nghiệm: trước & sau — mẫu workflow

1. Copy query lên môi trường dev với dataset representative.
2. Ghi lại baseline: EXPLAIN ANALYZE + time.
3. Thay đổi: thêm index / rewrite / phân vùng / materialized view.
4. Chạy lại EXPLAIN ANALYZE, so sánh actual time, rows, buffers/IO.
5. Chỉ deploy thay đổi đã xác minh — backup index/rollback plan nếu write-heavy.

15. Khi index không cứu được — các lựa chọn khác

• Partitioning (range/hash) để cắt nhỏ bảng quét.
• Materialized views / pre-aggregation cho báo cáo nặng.
• Change schema: denormalize khi read-heavy (với trade-off write complexity).
• Cache kết quả truy vấn bằng Redis/varnish cho workloads đọc nhiều.

16. Công cụ hỗ trợ & tips

• Enable slow query log / pg_stat_statements để tìm top offenders.
• Dùng EXPLAIN ANALYZE (Postgres) hoặc EXPLAIN ANALYZE (MySQL 8.0+ with ANALYZE) để đo thực tế.
• Dùng pt-query-digest (Percona) hoặc pgBadger để phân tích logs.
• Thử EXPLAIN (ANALYZE, BUFFERS, VERBOSE) cho thông tin I/O.

17. Ví dụ “before-after” (giả lập output để hiểu)

-- BEFORE: thiếu index, full scan
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;
-- MySQL (rút gọn): type: ALL, rows: 5,000,000, Extra: Using filesort

-- AFTER: composite index tận dụng ORDER BY
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;
-- MySQL (rút gọn): type: ref, key: idx_user_created, rows: 20, Extra: Using where

18. Kết luận — tư duy của người đọc EXPLAIN

Đọc EXPLAIN là kỹ năng bắt buộc nếu bạn muốn tối ưu SQL thực tế. Luôn bắt đầu bằng dữ liệu: đo, đọc plan, thay đổi có căn cứ, và xác nhận bằng EXPLAIN ANALYZE. Thêm index không phải luôn là câu trả lời — đôi khi cần thay đổi logic truy vấn, partition, hoặc denormalize. Mục tiêu cuối cùng: giảm I/O và số hàng phải xử lý. Hãy nhìn EXPLAIN như hacker: tìm nơi DB phải “đọc nhiều” và ngăn nó càng sớm càng tốt.

19. Bonus — checklist nhanh trước khi deploy index vào production

• Đã thử trên môi trường staging với dataset tương đương?
• Đánh giá tác động write (insert/update) do index mới?
• Đặt alert nếu index không được sử dụng trong 30 ngày để consider drop?
• Document lý do tạo index (query pattern, EXPLAIN trước/sau).