Amazon Aurora MySQL Bài 28: Phân tích sâu Execution Plan (EXPLAIN / EXPLAIN ANALYZE) trên Aurora MySQL

Trong quá trình phát triển backend, việc viết ra một câu lệnh SQL chạy đúng kết quả là chưa đủ; câu lệnh đó phải chạy nhanh và tiêu tốn ít tài nguyên nhất có thể. Khi một API trả về chậm, công cụ chẩn đoán đầu tiên mà một Senior Backend Engineer phải sử dụng là Execution Plan (Kế hoạch thực thi) thông qua hai lệnh EXPLAIN và EXPLAIN ANALYZE.

Bài viết này sẽ mổ xẻ cách đọc vị các thông số trong execution plan của Aurora MySQL để phát hiện triệt để các câu lệnh SQL kém tối ưu.

1. Phân biệt EXPLAIN truyền thống và EXPLAIN ANALYZE

• EXPLAIN (Kế hoạch tĩnh - Static Plan):

  • Khi bạn chạy EXPLAIN SELECT ..., MySQL Cost-Based Optimizer (CBO) chỉ dự đoán cách nó sẽ thực thi câu lệnh dựa trên số liệu thống kê (statistics) hiện tại của bảng.

  • Hạn chế: Các con số về số dòng (rows) và chi phí (cost) chỉ là ước lượng, đôi khi sai lệch hoàn toàn so với thực tế vận hành.

• EXPLAIN ANALYZE (Kế hoạch động - Thực tế từ MySQL 8.0 / Aurora v3):

  • Khi bạn chạy EXPLAIN ANALYZE SELECT ..., Aurora thực sự chạy câu lệnh đó, đo lường chính xác từng mili-giây thời gian thực (actual time), số vòng lặp thực tế (loops), và số lượng dòng dữ liệu thực tế bị quét qua tại mỗi bước.

  • Lời khuyên: Hãy luôn ưu tiên dùng EXPLAIN ANALYZE khi cần tối ưu hóa các câu lệnh truy vấn phức tạp trên môi trường Staging.

2. Giải phẫu một bảng kết quả EXPLAIN chuẩn xác

Khi bạn chạy lệnh EXPLAIN trên Aurora MySQL, kết quả trả về là một bảng gồm nhiều cột quan trọng. Dưới đây là các cột cốt lõi bạn bắt buộc phải soi:

• type (Kiểu truy cập - Access Type): Đây là cột quan trọng nhất quyết định câu lệnh có nhanh hay không. Xếp hạng từ nhanh nhất đến chậm nhất:

  • system / const: Truy tìm trực tiếp qua Primary Key hoặc Unique Index (chỉ trả về tối đa 1 dòng). Cực kỳ nhanh.

  • eq_ref / ref: Sử dụng Index để tìm kiếm các dòng khớp chính xác hoặc khớp một phần. Rất tốt.

  • range: Quét theo khoảng (ví dụ dùng toán tử BETWEEN, >, <, hoặc IN). Khá tốt nếu index chọn lọc tốt.

  • index: Cảnh báo đỏ! Duyệt qua toàn bộ cây B-Tree của Index từ đầu đến cuối. Tệ hơn Full Table Scan nếu index quá lớn.

  • ALL: Thảm họa! Full Table Scan. Aurora buộc phải đọc toàn bộ bảng dữ liệu từ đĩa hoặc buffer pool dòng này qua dòng khác.

• possible_keys: Các Index mà Optimizer cân nhắc có thể sử dụng cho câu lệnh này.

• key: Index thực tế mà Optimizer đã quyết định chọn để chạy. Nếu cột này trả về giá trị NULL trong khi bảng có hàng triệu dòng, câu lệnh của bạn đang bị Full Table Scan.

• rows: Số lượng dòng dự kiến mà Aurora ước tính phải duyệt qua để tìm ra kết quả. Con số này càng lớn, câu lệnh càng tốn tài nguyên.

• Extra: Các thông tin bổ sung cực kỳ quan trọng:

  • Using index: Tuyệt vời! Câu lệnh sử dụng kỹ thuật Covering Index, chỉ đọc dữ liệu từ index mà không cần chạm vào bảng gốc.

  • Using temporary: Nguy hiểm! Aurora phải tạo một bảng tạm (Temporary Table) trong RAM hoặc trên đĩa cứng để xử lý các mệnh đề như GROUP BY, DISTINCT, hoặc ORDER BY phức tạp, gây ngốn CPU và I/O.

  • Using filesort: Cảnh báo! Aurora phải thực hiện một thuật toán sắp xếp lại dữ liệu vì không thể tận dụng thứ tự của Index có sẵn.

3. Thực chiến đọc kết quả EXPLAIN ANALYZE

Hãy xem xét một câu lệnh truy vấn lịch sử đơn hàng của người dùng:

SQL

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 45678 ORDER BY created_at DESC LIMIT 10;

Kết quả trả về từ Aurora hiển thị dạng cây thực thi thực tế:

Plaintext

-> Limit: 10 row(s)  (actual time=45.2..45.2 rows=10 loops=1)
    -> Index scan on orders using idx_user_date (cost=12.5 rows=5000) (actual time=45.1..45.2 rows=10 loops=1)

• Phân tích:

  • actual time=45.1..45.2: Mất tổng cộng ~45 mili-giây để thực thi.

  • Index scan on orders using idx_user_date: CBO đã chọn đúng Composite Index idx_user_date (user_id, created_at).

  • rows=10 loops=1: Dù bảng có hàng triệu dòng, nhờ có index chuẩn xác, Aurora chỉ cần đọc đúng 10 dòng cần thiết để trả về kết quả, loại bỏ hoàn toàn lãng phí tài nguyên.

4. Góc nhìn vận hành cho Backend Engineer

• Quy tắc vàng khi viết code: Trước khi đẩy code tính năng mới lên môi trường Staging/Production, nếu câu lệnh SQL có liên quan đến các bảng dữ liệu lớn (như bảng orders, users, logs), hãy luôn chạy EXPLAIN ANALYZE để kiểm tra xem cột type có tránh được giá trị ALL (Full Table Scan) hay không.

• Tôn trọng thứ tự cột trong Index: Hãy nhớ rằng cấu trúc của Execution Plan chịu ảnh hưởng trực tiếp bởi cách bạn định nghĩa Index. Một thứ tự sai lệch trong mệnh đề WHERE có thể biến một câu lệnh chạy 1ms thành 5 giây.