Giải thích MySQL EXPLAIN ANALYZE: Đọc Kế hoạch Thực thi & Tối ưu hoá Truy vấn (Hướng dẫn 8.0)

1. Giới thiệu

Kế hoạch thực thi: Cần thiết cho Tối ưu hiệu suất Cơ sở dữ liệu

Trong các ứng dụng web và hệ thống doanh nghiệp, hiệu suất cơ sở dữ liệu là yếu tố quan trọng ảnh hưởng trực tiếp đến thời gian phản hồi tổng thể. Khi sử dụng MySQL, việc hiểu “kế hoạch thực thi” là điều thiết yếu để đánh giá hiệu quả của truy vấn. Lệnh EXPLAIN truyền thống hiển thị kế hoạch thực thi trước khi chạy câu lệnh SQL và đã lâu cung cấp cho các nhà phát triển những hiểu biết giá trị.

“EXPLAIN ANALYZE” Được giới thiệu trong MySQL 8.0

Được giới thiệu trong MySQL 8.0.18, EXPLAIN ANALYZE là một cải tiến mạnh mẽ của EXPLAIN truyền thống. Trong khi EXPLAIN chỉ cung cấp “kế hoạch lý thuyết”, EXPLAIN ANALYZE thực sự thực thi truy vấn và trả về dữ liệu đo lường như thời gian thực thi và số lượng hàng đã xử lý. Điều này cho phép xác định các nút thắt một cách chính xác hơn và xác thực kết quả tối ưu hoá truy vấn.

Tại sao EXPLAIN ANALYZE lại quan trọng

Ví dụ, thứ tự JOIN, việc sử dụng chỉ mục và các điều kiện lọc ảnh hưởng đáng kể đến thời gian thực thi. Bằng cách sử dụng EXPLAIN ANALYZE, bạn có thể xác nhận trực quan cách một câu lệnh SQL hoạt động và xác định nơi có bất hiệu quả cũng như những gì cần được tối ưu hoá. Điều này đặc biệt không thể thiếu khi làm việc với các bộ dữ liệu lớn hoặc các truy vấn phức tạp.

Mục đích của Bài viết và Đối tượng Độc giả

Bài viết này giải thích mọi thứ từ những kiến thức cơ bản về EXPLAIN ANALYZE của MySQL đến cách diễn giải kết quả và áp dụng các kỹ thuật tối ưu hoá thực tiễn. Nó hướng tới các nhà phát triển và kỹ sư hạ tầng thường xuyên sử dụng MySQL, cũng như các kỹ sư quan tâm đến việc tinh chỉnh hiệu suất. Để đảm bảo sự rõ ràng ngay cả với người mới bắt đầu, chúng tôi bao gồm các giải thích thuật ngữ và các ví dụ cụ thể xuyên suốt nội dung.

2. Sự khác nhau giữa EXPLAIN và EXPLAIN ANALYZE

Vai trò và Cách dùng Cơ bản của EXPLAIN

EXPLAIN của MySQL là một công cụ phân tích dùng để hiểu trước cách một câu lệnh SQL (đặc biệt là SELECT) sẽ được thực thi. Nó cho phép bạn xác nhận các kế hoạch thực thi như việc sử dụng chỉ mục, thứ tự join và phạm vi tìm kiếm.

Ví dụ:

EXPLAIN SELECT * FROM users WHERE age > 30;

Khi lệnh này được thực thi, MySQL không thực sự chạy truy vấn, mà chỉ hiển thị cách nó dự định xử lý truy vấn dưới dạng bảng. Kết quả bao gồm các thông tin như chỉ mục được dùng (key), phương thức truy cập (type) và ước lượng số hàng (rows).

Vai trò và Tính năng của EXPLAIN ANALYZE

Ngược lại, EXPLAIN ANALYZE, được giới thiệu trong MySQL 8.0.18, thực thi truy vấn và hiển thị kế hoạch thực thi dựa trên các giá trị đo lường thực tế. Điều này cho phép xác nhận các chi tiết mà EXPLAIN truyền thống không hiển thị, chẳng hạn như thời gian xử lý thực tế và số hàng thực sự được xử lý.

Ví dụ:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Lệnh này thực thi truy vấn và trả về kết quả bao gồm:

• Thời gian thực thi cho mỗi bước của kế hoạch (ví dụ, 0.0022 sec )
• Số hàng thực tế đã đọc (rows)
• Cấu trúc xử lý (dễ dàng hình dung bằng định dạng TREE)

Tóm tắt các Điểm Khác biệt Chính

Nên Dùng Cái Nào?

• Dùng EXPLAIN khi bạn muốn nhanh chóng kiểm tra cấu trúc truy vấn.
• Dùng EXPLAIN ANALYZE khi bạn cần các chi tiết cụ thể về thời gian thực thi và chi phí truy vấn.

Đặc biệt trong các kịch bản tinh chỉnh hiệu suất, EXPLAIN ANALYZE cho phép tối ưu hoá dựa trên dữ liệu thực tế thay vì ước tính, làm cho nó trở thành một công cụ vô cùng mạnh mẽ.

3. Định dạng Kết quả của EXPLAIN ANALYZE

Ba Định dạng Kết quả: TRADITIONAL, JSON, và TREE

EXPLAIN ANALYZE của MySQL có thể xuất kết quả ở các định dạng khác nhau tùy theo mục đích của bạn. Trong MySQL 8.0 trở lên, ba định dạng sau đây được hỗ trợ.

Hãy xem xét kỹ hơn các khác biệt.

Định dạng TRADITIONAL (Mặc định)

TRADITIONAL output is similar to the classic EXPLAIN style and lets you review execution plans in a familiar form. If you run EXPLAIN ANALYZE without specifying a format, the result is generally shown in this format.

Example output (excerpt):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : chi phí ước tính
• actual time : thời gian thực đo được
• rows : số lượng hàng được ước tính đã xử lý (trước khi thực thi)
• loops : số vòng lặp (đặc biệt quan trọng đối với JOIN)

TRADITIONAL format is easy for humans to scan and understand, making it suitable for beginners and quick checks.

JSON Format

JSON format is more detailed and easier to handle programmatically. The output is structured, with each node represented as a nested object.

Command:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Part of the output (pretty-printed):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

This format is less visually readable, but it is extremely convenient when you want to parse the data and feed it into analysis tools or dashboards.

TREE Format (Readable and Great for Visualizing Structure)

TREE format displays the query execution structure as a tree, making it easier to understand JOIN and subquery processing order.

Command:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Example output (simplified):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

For complex queries, nesting can appear like this:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

TREE format is especially useful for queries with many JOINs or complex nesting, where you need to grasp the processing flow.

Which Format Should You Use?

Choose the format that best fits your goal, and review the execution plan in the most readable and analyzable style.

4. How to Interpret Execution Plans

Why You Need to Read Execution Plans

MySQL query performance can vary greatly depending on data volume and index availability. By correctly interpreting the execution plan output from EXPLAIN ANALYZE, you can objectively identify where work is being wasted and what should be improved. This skill is a cornerstone of performance tuning, especially for queries that handle large datasets or complex joins.

Basic Structure of an Execution Plan

The output of EXPLAIN ANALYZE includes information such as the following (explained here based on TRADITIONAL-style output):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

This single line contains multiple important fields.

How to Read Key Fields

1. cost vs. actual time

• cost là ước tính nội bộ được MySQL tính toán và được dùng để đánh giá tương đối.
• actual time phản ánh thời gian thực tế đã trôi qua và quan trọng hơn cho phân tích hiệu suất.

For example:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

If estimates and measurements closely match, the execution plan is likely accurate. If the gap is large, table statistics may be inaccurate.

2. rows vs. actual rows

• rows là số hàng MySQL dự đoán sẽ đọc.
• actual rows là số hàng thực tế đã đọc (được bao trong dấu ngoặc trong đầu ra kiểu TRADITIONAL).

If there is a large discrepancy, you may need to refresh statistics or reconsider index design.

3. loops

If loops=1, the step runs once. With JOINs or subqueries, you may see loops=10 or loops=1000. The larger the value, the more likely nested loops are causing heavy processing.

Hiểu cấu trúc lồng nhau của kế hoạch thực thi

When multiple tables are joined, the execution plan is shown as a tree (especially clear in TREE format).

Example:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Vấn đề

• Both tables are fully scanned, resulting in a high join cost.

Biện pháp khắc phục

• Add an index on users.age and filter earlier to reduce the join workload.

Cách xác định các nút thắt hiệu năng

Focusing on the following points makes bottlenecks easier to find:

• Nodes with long actual time and many rows : These consume most of the execution time
• Places where a full table scan occurs : Likely missing or unused indexes
• Steps with many loops : Indicates inefficient JOIN order or nesting
• Large gaps between rows and actual rows : Suggests inaccurate statistics or excessive data access

Use these insights as the foundation for the “Query Optimization” techniques introduced in the next section.

5. Các ví dụ thực tế về tối ưu hoá truy vấn

Tối ưu hoá truy vấn là gì?

Query optimization refers to reviewing and improving SQL statements so they can be executed more efficiently. Based on how MySQL processes queries internally (execution plans), you apply improvements such as adding indexes, adjusting join order, and eliminating unnecessary processing.

Here, we demonstrate how to improve queries using EXPLAIN ANALYZE with concrete examples.

Ví dụ 1: Cải thiện tốc độ bằng chỉ mục

Trước khi tối ưu

SELECT * FROM users WHERE email = 'example@example.com';

Kế hoạch thực thi (Trích đoạn)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Vấn đề

• The output shows Table scan , meaning a full table scan is performed. With large datasets, this leads to significant delays.

Giải pháp: Thêm chỉ mục

CREATE INDEX idx_email ON users(email);

Kế hoạch thực thi sau khi tối ưu

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Kết quả

• Execution time significantly reduced.
• Full table scan avoided by using the index.

Ví dụ 2: Tối ưu hoá thứ tự JOIN

Trước khi tối ưu

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Kế hoạch thực thi (Trích đoạn)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Vấn đề

• Both tables are fully scanned, resulting in high join costs.

Giải pháp

• Add an index on users.age and filter first to reduce the join target size.

  CREATE INDEX idx_age ON users(age);
  

Kế hoạch thực thi sau khi tối ưu

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Kết quả

• JOIN targets are filtered first, reducing overall processing load.

Ví dụ 3: Sửa lại truy vấn con

Trước khi tối ưu

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Vấn đề

• The subquery may be evaluated repeatedly, degrading performance.

Giải pháp: Viết lại dưới dạng JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Kết quả

• The execution plan is optimized for JOIN processing, and indexes are more likely to be used.

Tầm quan trọng của so sánh Trước/Sau Comparison

Using EXPLAIN ANALYZE, you can verify optimization results with actual measured values. By comparing execution time and row counts before and after improvements, you ensure that tuning efforts are based on real performance gains rather than assumptions.

Các lưu ý quan trọng trong tối ưu hoá

• Thêm quá nhiều chỉ mục có thể gây phản tác dụng (hiệu suất INSERT/UPDATE chậm hơn).
• Kế hoạch thực thi phụ thuộc vào khối lượng dữ liệu và thống kê, vì vậy cần xác thực cho mỗi môi trường.
• Một tối ưu hoá hiếm khi giải quyết mọi thứ. Phân tích nút thắt là bước đầu tiên.

6. Thận trọng và Thực hành tốt nhất

Lưu ý quan trọng khi sử dụng EXPLAIN ANALYZE

Mặc dù EXPLAIN ANALYZE rất mạnh mẽ, việc sử dụng không đúng có thể dẫn đến hiểu lầm hoặc thậm chí rủi ro vận hành. Giữ các điểm sau trong tâm trí sẽ đảm bảo việc phân tích truy vấn an toàn và hiệu quả.

1. Tránh chạy một cách vô tư trong môi trường Production

Vì EXPLAIN ANALYZE thực sự thực thi truy vấn, việc nhầm lẫn sử dụng nó với các câu lệnh thay đổi (INSERT/UPDATE/DELETE) có thể làm thay đổi dữ liệu.

• Nói chung, chỉ sử dụng nó với các câu lệnh SELECT.
• Ưu tiên chạy nó trong môi trường staging hoặc testing thay vì production.

2. Xem xét tác động của bộ nhớ đệm

MySQL có thể trả về kết quả từ bộ nhớ đệm nếu cùng một truy vấn được thực thi nhiều lần. Do đó, thời gian thực thi được báo cáo bởi EXPLAIN ANALYZE có thể khác với hành vi thực tế.

Biện pháp đối phó:

• Xóa bộ nhớ đệm trước khi thực thi ( RESET QUERY CACHE; ).
• Chạy nhiều lần và đánh giá dựa trên giá trị trung bình.

3. Giữ thống kê luôn cập nhật

MySQL xây dựng kế hoạch thực thi dựa trên thống kê bảng và chỉ mục. Nếu thống kê đã lỗi thời, cả EXPLAIN và EXPLAIN ANALYZE có thể cung cấp thông tin gây hiểu lầm.

Sau các thao tác INSERT hoặc DELETE lớn, cập nhật thống kê bằng cách sử dụng ANALYZE TABLE.

ANALYZE TABLE users;

4. Chỉ mục không phải là giải pháp thần kỳ

Mặc dù chỉ mục thường cải thiện hiệu suất, quá nhiều chỉ mục làm chậm các thao tác ghi.

Lựa chọn giữa chỉ mục tổng hợp và chỉ mục đơn cột cũng quan trọng. Thiết kế chỉ mục cẩn thận dựa trên mẫu truy vấn và tần suất sử dụng.

5. Không nên đánh giá chỉ dựa trên thời gian thực thi

Kết quả từ EXPLAIN ANALYZE chỉ phản ánh hiệu suất của một truy vấn duy nhất. Trong các ứng dụng thực tế, độ trễ mạng hoặc xử lý phía backend có thể là nút thắt thực sự.

Do đó, phân tích các truy vấn trong bối cảnh kiến trúc hệ thống toàn bộ.

Tóm tắt các thực hành tốt nhất

7. Câu hỏi thường gặp (FAQ)

Câu hỏi 1. EXPLAIN ANALYZE có sẵn từ phiên bản nào?

A.
EXPLAIN ANALYZE của MySQL được giới thiệu trong phiên bản 8.0.18 trở lên. Nó không được hỗ trợ trong các phiên bản trước 8.0, vì vậy bạn nên kiểm tra phiên bản MySQL của mình trước khi sử dụng.

Câu hỏi 2. Việc chạy EXPLAIN ANALYZE có thể thay đổi dữ liệu không?

A.
EXPLAIN ANALYZE thực thi truy vấn nội bộ.
Khi được sử dụng với câu lệnh SELECT, nó không thay đổi dữ liệu.

Do đó, khi được sử dụng với câu lệnh SELECT, nó không thay đổi dữ liệu.

Tuy nhiên, nếu bạn nhầm lẫn sử dụng nó với INSERT, UPDATE hoặc DELETE, dữ liệu sẽ bị thay đổi giống như một truy vấn bình thường.

Để an toàn, nên chạy các phân tích trong cơ sở dữ liệu thử nghiệm hoặc staging thay vì trong môi trường production.

Câu hỏi 3. Chỉ EXPLAIN có đủ không?

A.
EXPLAIN đủ để xem xét “kế hoạch thực thi ước tính”. Tuy nhiên, nó không cung cấp các giá trị đo được như thời gian thực thi thực tế hoặc số lượng hàng thực tế.

Nếu bạn cần tối ưu hoá truy vấn nghiêm túc hoặc muốn xác minh hiệu quả tối ưu hoá, EXPLAIN ANALYZE sẽ hữu ích hơn.

Câu hỏi 4. Các giá trị như “loops” và “actual time” chính xác như thế nào?

A.
Các giá trị như actual time và loops là các chỉ số thực thi thực tế được MySQL đo nội bộ. Tuy nhiên, chúng có thể dao động nhẹ tùy thuộc vào điều kiện hệ điều hành, trạng thái bộ nhớ đệm và tải máy chủ.

Vì lý do này, đừng dựa vào một phép đo lường duy nhất. Thay vào đó, chạy truy vấn nhiều lần và đánh giá xu hướng.

Q5. “cost” đại diện chính xác cho cái gì?

A.
cost là một giá trị ước tính được tính toán bởi mô hình chi phí nội bộ của MySQL. Nó phản ánh một đánh giá tương đối về chi phí CPU và I/O. Nó không được biểu thị bằng giây.

Ví dụ, nếu bạn thấy (cost=0.3) và (cost=2.5), cái sau được ước tính đắt hơn về mặt tương đối.

Q6. Lợi ích của việc sử dụng định dạng JSON hoặc TREE là gì?

A.

• Định dạng JSON : Đầu ra có cấu trúc dễ phân tích lập trình. Hữu ích cho các công cụ tự động hóa và bảng điều khiển.
• Định dạng TREE : Làm cho luồng thực thi và lồng ghép rõ ràng về mặt hình ảnh. Lý tưởng để hiểu các truy vấn phức tạp và thứ tự JOIN.

Chọn định dạng phù hợp nhất với mục đích của bạn.

Q7. Tôi nên làm gì nếu không thể cải thiện hiệu suất sau khi xem xét kế hoạch thực thi?

A.
Xem xét các cách tiếp cận bổ sung chẳng hạn như:

• Thiết kế lại chỉ mục (chỉ mục tổng hợp hoặc chỉ mục bao phủ)
• Viết lại truy vấn (truy vấn con → JOINs, loại bỏ các cột SELECT không cần thiết)
• Sử dụng view hoặc bảng tạm
• Xem xét cấu hình MySQL (kích thước bộ đệm, phân bổ bộ nhớ, v.v.)

Việc điều chỉnh hiệu suất hiếm khi thành công với một kỹ thuật duy nhất. Một cách tiếp cận toàn diện và lặp lại là cần thiết.