EXPLAIN — Đọc hiểu Execution Plan

EXPLAIN là gì? Khi bạn đặt EXPLAIN trước một câu SQL, MySQL không chạy query mà chỉ trả về execution plan — bản kế hoạch mô tả cách engine sẽ truy cập bảng, dùng index nào, ước tính bao nhiêu row.

EXPLAIN SELECT id, user_id, check_in
FROM bookings
WHERE user_id = 123 AND status = 'confirmed';

#Tất cả các cột trong output EXPLAIN (MySQL)

#Cột type — Thứ tự hiệu suất (tốt → xấu)

#Cột Extra — Các giá trị quan trọng

#1.2 EXPLAIN ANALYZE — Đo lường thực tế (MySQL 8.0.18+)

Khác biệt cốt lõi: EXPLAIN chỉ ước tính (không chạy query). EXPLAIN ANALYZE thực sự chạy query rồi trả về thời gian thực tế + số row thực tế ở mỗi bước.

EXPLAIN ANALYZE
SELECT b.id, b.check_in, u.name
FROM bookings b
JOIN users u ON u.id = b.user_id
WHERE b.status = 'confirmed' AND b.check_in >= '2024-12-01';

Output mẫu (tree format):

-> Nested loop inner join  (cost=4.65 rows=5)
   (actual time=0.089..0.234 rows=5 loops=1)
    -> Index range scan on b using idx_status_checkin
       (cost=2.50 rows=5)
       (actual time=0.045..0.078 rows=5 loops=1)
    -> Single-row index lookup on u using PRIMARY (id=b.user_id)
       (cost=0.43 rows=1)
       (actual time=0.028..0.029 rows=1 loops=5)

Cách đọc output:

#1.3 Các format output khác

FORMAT=JSON — chi tiết nhất, bao gồm cost breakdown:

EXPLAIN FORMAT=JSON SELECT * FROM bookings WHERE user_id = 123;

Hữu ích khi cần xem cost_info, used_key_parts (cột nào trong composite index được dùng), và attached_condition.

FORMAT=TREE — hiển thị dạng cây, dễ đọc hơn tabular:

EXPLAIN FORMAT=TREE SELECT * FROM bookings WHERE status = 'confirmed';

#1.4 Workflow debug query chậm — Từng bước

1. Chạy EXPLAIN — xem execution plan
2. Kiểm tra type — nếu ALL hoặc index → cần thêm/sửa index
3. Kiểm tra key — nếu NULL → xem possible_keys, tại sao optimizer không chọn?
4. Kiểm tra rows — ước tính quá lớn? So sánh với data thực tế
5. Kiểm tra Extra — Using filesort / Using temporary → thêm index cho ORDER BY / GROUP BY
6. Chạy EXPLAIN ANALYZE (staging) — so sánh estimated vs actual. Lệch nhiều → ANALYZE TABLE
7. Thêm index / rewrite query → chạy EXPLAIN lại → so sánh

#1.5 Các kỹ thuật tối ưu query phổ biến

• Tránh SELECT * — chỉ select cột cần thiết, giảm I/O, memory, và tăng cơ hội covering index
• Sử dụng index đúng cách — equality trước, range sau, covering index khi có thể (xem mục 2)
• Tránh function trên cột trong WHERE — WHERE YEAR(created_at) = 2024 → không dùng index. Thay bằng WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
• Subquery vs JOIN — JOIN thường hiệu quả hơn correlated subquery
• EXISTS vs IN — EXISTS dừng ngay khi tìm thấy 1 kết quả, IN phải evaluate toàn bộ danh sách
• LIMIT sớm — áp dụng LIMIT trong subquery trước khi JOIN
• Batch processing — xử lý dữ liệu lớn theo chunk thay vì 1 query khổng lồ

#1.6 Ví dụ thực tế (Booking System)

-- ❌ Chậm: full table scan + function on column
SELECT * FROM bookings 
WHERE DATE(check_in) = '2024-12-25' 
AND status = 'confirmed';

-- ✅ Nhanh: range scan + composite index
SELECT id, user_id, room_id, check_in, total_price 
FROM bookings 
WHERE check_in >= '2024-12-25' AND check_in < '2024-12-26'
AND status = 'confirmed';
-- Cần composite index: (status, check_in)