Tìm hiểu về MySQL EXPLAIN

Trước khi thực thi câu lệnh, MySQL sẽ thực hiện quá trình tối ưu hoá. MySQL sẽ thực hiện việc tính toán để đưa ra chi phí thực thi ước lượng (thời gian thực hiện) cho các phương án khác nhau có thể có. Bạn có thể xem thêm bài viết Sơ lược về kiến trúc của MySQL, để hiểu rõ hơn cách MySQL thực thi câu query.

EXPLAIN là câu lệnh sẽ giúp chúng ta có được thông tin về các tính toán tối ưu này. Qua đó, chúng ta có thể đánh giá được câu lệnh có vấn đề gì không, có điểm nào cần cải thiện hay không?

Bài viết này giúp bạn tìm hiểu về cách sử dụng MySQL EXPLAIN. Trong bài viết này tôi sử dụng MySQL Employees Sample Database để thực hiện các ví dụ minh hoạ.

Sơ lược về EXPLAIN

• EXPLAIN nằm trong nhóm các Utility Statements, làm việc với các câu lệnh: SELECT, DELETE, INSERT, REPLACE, và UPDATE.
• Khi EXPLAIN được sử dụng, MySQL sẽ hiển thị các thông tin về execution plan từ optimizer. Điều đó có nghĩa là MySQL giải thích cách mà nó xử lý câu lệnh, cũng như các thông tin về các bảng được joined, thứ tự thực hiện.
• MySQL 8.0.18 giới thiệu EXPLAIN ANALYZE, cho ra output của EXPLAIN với thời gian, và các thông tin bổ sung về iterator.
• Về cơ bản thì output format của EXPLAIN tập trung vào thông tin về execution plan, còn EXPLAIN ANALYZE lại cung cấp thông tin chi tiết thời gian thực thi. Chúng ta sẽ tìm hiểu về từng format ở các phần tiếp theo của bài viết nhé.

Cú pháp sử dụng

Dưới đây là mô tả cú pháp sử dụng EXPLAIN và EXPLAIN ANALYZE:

EXPLAIN
    tbl_name [col_name | wild]
EXPLAIN
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}
EXPLAIN ANALYZE [FORMAT = TREE] select_statement

explain_type: { FORMAT = format_name }

format_name: { TRADITIONAL | JSON | TREE }

explainable_stmt: { SELECT | TABLE | DELETE | INSERT | REPLACE | UPDATE}

• Sử dụng EXPLAIN để lấy các thông tin về cấu trúc bảng, thông tin về execution plan.
• Sử dụng EXPLAIN ANALYZE để lấy các thông tin về thời gian thực thi hay các thông tin về iterator.
• EXPLAIN cho phép bạn chọn format output. Dạng output hiển thị theo bảng TRADITIONAL, JSON hoặc TREE.
• EXPLAIN ANALYZE chỉ chấp nhận format TREE.

Giải thích EXPLAIN output

EXPLAIN SELECT * FROM dept_emp, employees
WHERE dept_emp.emp_no=employees.emp_no;

Với định dạng mặc định TRADITIONAL, output của EXPLAIN là một table. Mỗi dòng output cung cấp thông tin về 1 table. Với câu query bên trên, kết quả sẽ cho hai dòng như sau:

Dưới đây là ý nghĩa output EXPLAIN ở định dạng TRADITIONAL:

Nhìn chung, mỗi dòng thông tin của EXPLAIN chứa khá nhiều thông tin. Để đánh giá nhanh câu query, nhận biết câu query có đang gặp vấn đề performance hay không, bạn có thể nhìn vào các field: select_type, type và Extra.

• select_type cho chúng ta biết loại SELECT. Loại SELECT càng đơn giản, ít phụ thuộc, không chứa subquery sẽ càng truy vấn nhanh.
• type mô tả cách access vào table trong câu query. Tuỳ vào cách access mà tốc độ truy vấn nhanh chậm khác nhau. Dựa vào type, chúng ta có thể điều chỉnh index cho database.
• Extra đưa ra thêm một số thông tin về cách mà MySQl giải quyết câu query. Khi các giá trị: Using temporary, Using filesort xuất hiện tại đây, chúng ta có thể nhận diện ngay được câu query đang chưa thực sự tốt.

Giải thích EXPLAIN ANALYZE output

Từ MySQL 8.0.18, bên cạnh EXPLAIN, MySQL cung cấp thêm câu lệnh EXPLAIN ANALYZE. Về cơ bản thì EXPLAIN ANALYZE cũng thực hiện câu lệnh EXPLAIN. Tuy nhiên, định dạng output khác so với EXPLAIN, nó hiển thị các thông tin output về thời gian thực thi và iterator.

Với EXPLAIN ANALYZE, định dạng không được hỗ trợ phong phú như EXPLAIN. Nó chỉ chấp nhận định dạng TREE cho output.

Để hiểu rõ hơn sự khác biệt của EXPLAIN ANALYZE và EXPLAIN, chúng ta sẽ cùng đi qua ví dụ bên dưới:

Với EXPLAIN:

mysql> EXPLAIN SELECT COUNT(*) FROM employees WHERE employees.emp_no > 10400;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 149778 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Output của EXPLAIN cho biết: Câu SELECT sử dụng index PRIMARY trên bảng employees để tìm kiếm trong range, ước lượng số dòng phải đọc là 149778 dòng. Với InnoDB, đây là là con số ước lượng, không phải số thực tế khi thực thi.

Khi chạy câu query, kết quả thực tế như sau:

mysql> SELECT COUNT(*) FROM employees WHERE employees.emp_no > 10400;
+----------+
| COUNT(*) |
+----------+
|   299624 |
+----------+
1 row in set (0.08 sec)

Câu query mất 0.08s để đọc 299624 dòng. Như bạn thấy đó, số dòng thực tế cần đọc khi thực thi khác nhiều so với con số mà EXPLAIN đưa ra.

Với EXPLAIN ANALYZE:

mysql> EXPLAIN ANALYZE SELECT COUNT(*) FROM employees WHERE employees.emp_no > 10400;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=44970.80 rows=1) (actual time=129.047..129.047 rows=1 loops=1)
    -> Filter: (employees.emp_no > 10400)  (cost=29993.00 rows=149778) (actual time=0.760..110.114 rows=299624 loops=1)
        -> Covering index range scan on employees using PRIMARY over (10400 < emp_no)  (cost=29993.00 rows=149778) (actual time=0.016..75.085 rows=299624 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.13 sec)

Phân tích output của EXPLAIN ANALYZE:

Covering index range scan on employees using PRIMARY - đây là bước đầu tiên thực hiện, chỉ ra rằng câu SELECT sử dụng index PRIMARY trên bảng employees để tìm kiếm trong range (10400 < emp_no)

cost=29993.00 rows=149778 - chỉ ra chi phí ước lượng và số dòng cần đọc (con số này giống với số dòng EXPLAIN đưa ra bên trên)

actual time=0.016..75.085 rows=299624 loops=1 - đây là phần số liệu thực tế khi thực thi câu lệnh SELECT:

• 0.016 - thời gian để trả về dòng đầu tiên (milliseconds)
• 75.085 - thời gian để trả về tất cả các dòng (milliseconds)
• rows=299624 - số dòng trả về (con số chính xác), con số giống với con số khi chúng ta chạy câu query thực
• loops=1 - số lượng vòng loops

Như bạn thấy EXPLAIN ANALYZE hiển thị tất cả các thông tin chi tiết như trên cho từng bước: index scan, filter, aggregate. Điều đó thực sự hữu ích cho việc tối ưu hoá câu truy vấn.

Tại bước cuối cùng: Aggregate: count(0) (cost=44970.80 rows=1) (actual time=129.047..129.047 rows=1 loops=1), thời gian thực tế cần thiết là 129.047ms. Report cuối cùng vì vậy mà hiển thị: 1 row in set (0.13 sec). Như vậy, theo report thì câu query này cần 0.13s để thực thi.

Tuy nhiên, với câu query này, chúng ta chạy ở bên trên, kết quả chạy thực tế cho thấy thời gian thực thi là 0.08s. EXPLAIN ANALYZE thực hiện câu query, đo thời gian thực thi của từng bước, và xuất ra output. Chính vì thế so với câu query đơn thuần thì câu query chứa EXPLAIN ANALYZE sẽ có thời gian thực thi nhiều hơn.

Định dạng output EXPLAIN ANALYZE so với EXPLAIN thì: ngắn gọn, cô đọng, dễ hình dung các bước thực hiện câu query hơn. Tuy nhiên cần lưu ý thời gian thực thi cuối cùng của report EXPLAIN ANALYZE bao gồm cả thời gian xử lý cho việc EXPLAIN.

Sử dụng EXPLAIN để tối ưu một câu query chậm

Qua phần giải thích về output, các bạn đã hiểu được những thông tin từ EXPLAIN đưa ra. Trong phần này, chúng ta sẽ sử dụng những thông tin đó để tối ưu cho một câu query.

Như đã đề cập ở phần mở đầu, tôi sử dụng MySQL Employees Sample Database để làm các ví dụ minh hoạ. Câu query chọn làm ví dụ là: Lấy ra thông tin first_name, last_name, from_date, to_date của tất cả employees có first_name = 'Zvonko' thời gian bắt đầu làm việc nằm trong khoảng thời gian '1989-01-01' đến '1994-01-01', và sắp xếp thứ tự kết quả theo from_date:

SELECT employees.first_name, employees.last_name, dept_emp.from_date, dept_emp.to_date
FROM employees JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
WHERE employees.first_name = 'Zvonko'
  AND dept_emp.from_date BETWEEN '1989-01-01' AND '1994-01-01'
ORDER BY dept_emp.from_date

Dưới đây là hình ảnh trích lược kết quả câu query:

Report kết quả: 98 rows in set (0.10 sec)

Bây giờ, sử dụng EXPLAIN để kiểm tra các thông tin về loại JOIN có vấn đề gì không nhé:

mysql> EXPLAIN
    -> SELECT employees.first_name, employees.last_name, dept_emp.from_date, dept_emp.to_date
    -> FROM employees JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
    -> WHERE employees.first_name = 'Zvonko'
    ->   AND dept_emp.from_date BETWEEN '1989-01-01' AND '1994-01-01'
    -> ORDER BY dept_emp.from_date;
+----+-------------+-----------+------------+------+---------------+---------+---------+----------------------------+--------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref                        | rows   | filtered | Extra                                        |
+----+-------------+-----------+------------+------+---------------+---------+---------+----------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL                       | 299556 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | dept_emp  | NULL       | ref  | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no |      1 |    11.11 | Using where                                  |
+----+-------------+-----------+------------+------+---------------+---------+---------+----------------------------+--------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Dòng đầu tiên của output cho thấy: type-ALL(loại JOIN tốn kém nhất), key-NULL(không thể xác định được bất cứ khoá nào dùng cho JOIN), Extra-Using temporary; Using filesort(sử dụng sắp xếp, bảng tạm đều tốn chi phí). Tất cả các thông tin này đều cho thấy câu query không tốt.

Với trường hợp type-ALL, chúng ta có thể tối ưu bằng cách thêm indexes để tránh việc quét toàn bộ bảng. Chúng ta nên bắt đầu đánh indexes những cột xuất hiện trong mệnh đề WHERE. Với câu query trên, tôi chọn thêm indexes trên cột employees.first_name:

ALTER TABLE employees ADD INDEX first_name_index (first_name)

Kiểm tra lại bằng EXPLAIN để xem sự thay đổi:

mysql> EXPLAIN
    -> SELECT employees.first_name, employees.last_name, dept_emp.from_date, dept_emp.to_date
    -> FROM employees JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
    -> WHERE employees.first_name = 'Zvonko'
    ->   AND dept_emp.from_date BETWEEN '1989-01-01' AND '1994-01-01'
    -> ORDER BY dept_emp.from_date;
+----+-------------+-----------+------------+------+--------------------------+------------------+---------+----------------------------+------+----------+---------------------------------+
| id | select_type | table     | partitions | type | possible_keys            | key              | key_len | ref                        | rows | filtered | Extra                           |
+----+-------------+-----------+------------+------+--------------------------+------------------+---------+----------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | PRIMARY,first_name_index | first_name_index | 58      | const                      |  258 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | dept_emp  | NULL       | ref  | PRIMARY                  | PRIMARY          | 4       | employees.employees.emp_no |    1 |    11.11 | Using where                     |
+----+-------------+-----------+------------+------+--------------------------+------------------+---------+----------------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

Dòng đầu tiên của output cho thấy: type-ALL(loại JOIN tốn kém nhất) đã thay đổi thành type-ref vì sử dụng key-first_name_index. Thay vì scan qua rows-299556 thì giờ đây là: rows-258. Nhờ vậy mà số rows phải duyệt giảm đi đáng kể.

Chúng ta chạy lại câu query trên một lần nữa để kiểm tra thời gian thực thi thực tế:

Report kết quả: 98 rows in set (0.01 sec). So với khi chưa đánh indexes: 98 rows in set (0.10 sec). Thời gian thực thi nhanh hơn 10 lần.

Kết

Bài viết này giải thích ý nghĩa output của EXPLAIN và EXPLAIN ANALYZE, cũng như cách chúng ta sử dụng chúng để tối ưu câu query.

Một vài điểm tóm tắt từ nội dung bài viết:

• EXPLAIN có thể sử dụng như một công cụ giúp phát hiện nhanh chóng những câu query chưa tối ưu.
• Sử dụng EXPLAIN ANALYZE nếu bạn cần output tóm tắt các bước thực hiện, đo thời gian thực tế chạy câu query.
• Sử dụng EXPLAIN nếu bạn cần output chi tiết về loại SELECT, cách các bảng JOIN để phát hiện indexes thiếu hay một vấn đề bất thường của câu query.

Khi viết câu truy vấn dữ liệu, có rất nhiều cách viết câu query, đặc biệt là với dữ liệu lớn, nhiều bảng, nhiều liên kết. Hẳn là bạn sẽ có lúc phân vân không biết cách viết nào là tối ưu. Khi đó, câu lệnh EXPLAIN thực sự hữu ích. Chúng ta không cần cài đặt gì thêm, chỉ cần gọi trước câu truy vấn, bạn sẽ có ngay được kết quả so sánh.

Tôi cũng khá thích format output của EXPLAIN ANALYZE, nó liệt kê ngắn gọn các bước thực hiện và thời gian thực thi. Hãy tận dụng linh hoạt EXPLAIN và EXPLAIN ANALYZE để hỗ trợ bạn tốt nhất trong lúc kiểm tra và tối ưu câu query nhé!

Tài liệu tham khảo

• https://dev.mysql.com/doc/refman/8.0/en/using-explain.html
• https://linuxhint.com/mysql_explain_statement/
• https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/