Bảng mặc định của mysql là gì năm 2024

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:

select_type Ý nghĩa id SELECT identifier Trong trường hợp bạn sử dụng câu truy vấn lồng nhau thì các câu SELECT sẽ được đánh thứ tự để phân biệt.

select_type

Cột này chỉ ra dòng này là một SELECT phức tạp hay đơn giản. Nếu nhãn là SIMPLE có nghĩa đây là dạng truy vấn đơn giản, không sử dụng UNION hay SUBQUERIES. Nếu truy vấn có bất kỳ phần con phức tạp nào, phần ngoài cùng được gắn nhãn là PRIMARY và các phần khác có thể được gắn nhãn như sau:

• SUBQUERY: SELECT trong SUBQUERY đặt trong mệnh đề SELECT
• DERIVED: SELECT trong SUBQUERY đặt trong mệnh đề FROM. MySQL gọi đây là "bảng dẫn xuất", vì bảng tạm thời có nguồn gốc từ truy vấn con.
• UNION: Các SELECT thứ hai hoặc tiếp theo trong một UNION. SELECT đầu tiên đã được gắn nhãn PRIMARY.
• UNION RESULT: SELECT được sử dụng để truy xuất kết quả từ bảng tạm thời ẩn danh của UNION Ngoài các giá trị trên, bạn có thể xem thêm các loại SELECT khác tại select_types. table tên table, mà dòng output đang tham khảo tới partitions Giá trị là null đối với nonpartitioned tables. Ngược lại, hiển thị các partitions đã sử dụng.

type

Đây là một thông tin rất quan trọng, 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. Các loại type thường gặp gồm:

• const hoặc system: Bảng chỉ có duy nhất 1 row phù hợp với điều kiện tìm kiếm. Vì chỉ có 1 row nên giá trị của các cột trong row này được xem là hằng số. Loại join này rất nhanh vì chỉ đọc 1 lần duy nhất. SELECT FROM tblname WHERE primarykey=1;
• eq_ref: Một tra cứu chỉ mục với index type là PRIMARY KEY, UNIQUE NOT NULL, với toán tử =. Sau system và const thì đây là loại join nhanh nhất.

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

  0
• ref: Một tra cứu chỉ mục với index type không phải PRIMARY KEY hoặc UNIQUE, với toán tử =, <=>.
• range: Quét phạm vi là quét chỉ mục giới hạn, trả về các hàng phù hợp với range. Điều này tốt hơn so với quét toàn bộ chỉ mục.

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

  1
• index: index scan. Ưu điểm chính của loại này là tránh được việc sorting. Nhược điểm là chi phí đọc toàn bộ bảng theo thứ tự chỉ mục. Điều này thường có nghĩa là truy cập các hàng theo thứ tự ngẫu nhiên rất tốn kém.
• ALL: table scan. MySQL phải quét qua bảng từ đầu đến cuối để tìm hàng. Đây là loại tệ nhất. Ngoài các giá trị trên, bạn có thể xem thêm các loại JOIN khác tại . possible_keys Liệt kê tất cả các indexes liên quan có thể có để tìm các dòng trong table. Các column này có hoặc không sử dụng trong thực tế. Nếu giá trị là NULL, tức là không có indexes nào liên quan. Trong trường hợp này bạn có thể xem xét lại mệnh đề WHERE để cải thiện hiệu suất câu query, để có thể tham khảo đến các cột indexes phù hợp. Hoặc tạo thêm indexes nếu cần thiết. key Là các cột indexes thực thế mà MySQL quyết định sử dụng. Cột này có thể chứa khoá không liệt kê trong possible_keys. key_len key: là index column, và nó sẽ có cây index key_len: chỉ độ dài cây index mà MySQL sử dụng Nếu key có giá trị NULL thì key_len cũng có giá trị NULL ref Hiển thị các cột hoặc các hằng số được so sánh với index trong cột key rows Thể hiện số rows mà MySQL dự kiến sẽ duyệt qua để thực thi câu query. Con số này là estimate, không chính xác. filtered Thể hiện tỷ lệ phần trăm dự kiến các hàng của table được filtered bởi điều kiện. Giá trị lớn nhất là 100: tức là không có quá trình lọc hàng nào xảy ra. Con số giảm dần: cho thấy số lượng hàng lọc ngày càng tăng. Và nếu hàng được lọc càng nhiều thì có nghĩa số lượng hàng được joined với bảng tiếp theo cũng sẽ càng nhiều. Ví dụ: số hàng là 1000, tỷ lệ lọc filtered: 50.00 (50%), thì số lượng hàng joined với bảng tiếp theo là: 1000 x 50% = 500

Extra

Các thông tin thêm về cách mà MySQl giải quyết câu query. Những giá trị Extra thường gặp gồm:

• Using index: MySQL sử dụng
• Using where: MySQL sẽ lọc các hàng theo điều kiện
• Using temporary: MySQL sẽ sử dụng một bảng tạm thời trong khi sắp xếp kết quả của truy vấn.
• Using filesort: query nếu có ORDER BY chẳng hạn thì thông tin lấy về sẽ cần phải sort. Using filesort thường được hiển thị cho trường hợp này
• Range checked for each record (index map:N): MySQL phát hiện không có good index, nhưng nhận thấy rằng một số chỉ mục có thể được sử dụng sau khi các giá trị cột từ các bảng trước đó được biết đến. Đối với mỗi tổ hợp hàng trong các bảng trước, MySQL sẽ kiểm tra xem có thể sử dụng range or index_merge để truy xuất các hàng hay không. Để xem thêm về Extra, tham khảo

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ị:

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

  2,

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

  3 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ì 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

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

4 để tìm kiếm trong

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

5, ước lượng số dòng phải đọc là

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

6 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

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

7 để đọc

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

8 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:

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

9 - đâ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 (

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)

0)

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)

1 - 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)

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)

2 - đây là phần số liệu thực tế khi thực thi câu lệnh SELECT:

• 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)

  3 - thời gian để trả về dòng đầu tiên (milliseconds)
• 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)

  4 - thời gian để trả về tất cả các dòng (milliseconds)
• 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)

  5 - 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
• 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)

  6 - 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:

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)

7, thời gian thực tế cần thiết là

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)

8. Report cuối cùng vì vậy mà hiển thị:

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)

9. Như vậy, theo report thì câu query này cần

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

0 để 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à

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

7. 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

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

2,

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

3,

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

4,

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

5 của tất cả employees có

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

6 thời gian bắt đầu làm việc nằm trong khoảng thời gian

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

7 đến

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

8, và sắp xếp thứ tự kết quả theo

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

4:

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ả:

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)

0

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:

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)

1(loại JOIN tốn kém nhất),

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)

2(không thể xác định được bất cứ khoá nào dùng cho JOIN),

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)

3(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

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)

1, 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

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)

5

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:

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)

1(loại JOIN tốn kém nhất) đã thay đổi thành

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)

7 vì sử dụng

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)

8. Thay vì scan qua

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)

9 thì giờ đây là:

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

0. 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ả:

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

1. So với khi chưa đánh indexes:

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)

0. 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é!