1. Chúng ta có thể thực hiện Explain SQL đối với các câu lệnh nào.
Trong cơ sở dữ liệu MySQL, đa phần các câu lệnh đều có thể sử dụng Explain để xem chiến lược thực thi.
Dưới đây là các loại lệnh phổ biến nhất:
- Các câu lệnh SELECT
- Các câu lệnh DML (Update/ Delete/ Insert).
Sử dụng Explain, bạn có thể biết chính xác câu lệnh SQL của mình đang hoạt động như thế nào trong cơ sở dữ liệu MySQL
- Thứ tự (mức vật lý) của câu lệnh thực hiện (phải quét bảng nào trước, index nào trước, thứ tự thực hiện trong giải thuật Join ra sao…)
- Các bước thực hiện trên mất bao nhiêu chi phí (COST của câu lệnh SQL).
- Từ đó ta xác định được điểm chiếm nhiều tài nguyên nhất và đang ảnh hưởng phần lớn đến hiệu năng của câu lệnh.
2. Sử dụng Explain SQL như thế nào?
Cách sử dụng rất đơn giản: Bạn chỉ cần thêm Explain vào đằng trước câu lệnh SQL.
Bên dưới đây là một số ví dụ để bạn dễ hình dung và có thể áp dụng được trong công việc của bản thân
2.1. Demo sử dụng Explain SQL với câu lệnh SELECT
Sử dụng Explain SQL để xem chiến lược thực thi
mysql> explain select * from city where name='Hanoi';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)2..2 Demo sử dụng Explain SQL với câu lệnh Update
Sử dụng Explain SQL để xem chiến lược thực thi
mysql> explain update city set name='HANOI_TEST' where name='Hanoi';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | UPDATE | city | NULL | index | NULL | PRIMARY | 4 | NULL | 4046 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)2.3. Demo sử dụng Explain SQL với câu lệnh Delete
Sử dụng Explain SQL để xem chiến lược thực thi
mysql> explain delete from city where name='HANOI_TEST';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | DELETE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)2.4. Demo sử dụng Explain SQL với câu lệnh Insert
mysql> create table demo as select * from city where 1=0;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0Sử dụng Explain SQL để xem chiến lược thực thi
mysql> explain insert into demo select * from city;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | INSERT | demo | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)3. Xem chi tiết hơn với Explain Analyze
Từ phiên bản MySQL 8.0.18, bạn có thể sử dụng Explain Analyze để xem chi tiết hơn câu lệnh Explain thông thường.Một số thông tin kết quả của câu lệnh Explain Analyze như sau:
- Ước lược chi phí thực thi của câu lệnh (Cost của câu lệnh SQL).
- Ước lược số bản ghi sẽ được trả ra (Lưu ý: đây là dựa trên tính toán các thông số thống kê mà hệ thống có được, nó có thể không chính xác so với thực tế).
- Thời gian để trả ra bản ghi đầu tiên
- Thời gian để trả ra toàn bộ các bản ghi
- …
Gỉa sử tôi cần tối ưu câu lệnh sau
select * from city where name='Hanoi';Thực hiện Explain Analyze để xem chiến lược thực thi của câu lệnh
mysql> explain analyze select * from city where name='Hanoi';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (city.`Name` = 'Hanoi') (cost=410.85 rows=405) (actual time=2.057..2.194 rows=1 loops=1)
-> Table scan on city (cost=410.85 rows=4046) (actual time=0.071..1.716 rows=4079 loops=1)
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)Để ý vào câu lệnh trên, từ chiến lược thực thi chúng ta có thể thấy các bước thực hiện của MySQL như sau:
- Bước 1: Thực hiện quét toàn bộ bảng CITY (thể hiện ở nội dun TABLE SCAN ON CITY). Chi phí của công việc này là COST = 410.85. Dự kiến số lượng bản ghi ở bước này cần xử lý là 4046 bản ghi. Thời gian thực hiện tìm tới bản ghi đầu tiên là 0.071 miliseconds, thời gian để lấy toàn bộ các bản ghi là 1.716 miliseconds.
- Bước 2: Từ tập dữ liệu lấy ở bước 1, hệ thống thực hiện lọc các kết quả theo điều kiện WHERE (FILTER điều kiện city.’Name’ = ‘HaNoi’. Câu lệnh này dự kiến trả ra 405 bản ghi và có chi phí thực hiện là 410.85.
Từ chiến lược thực thi trên, ta có thể thấy: Nếu loại bỏ được bước TABLE SCAN thì sẽ tối ưu được đáng kể cho câu lệnh này.
Ta có thể thực hiện một giải pháp nhanh chóng ở đây là tạo Index trên cột name.
mysql> create index idx_name on city(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0Sau khi tạo Index, chúng ta thực hiện kiểm tra lại chiến lược thực thi của câu lệnh
mysql> explain analyze select * from city where name='Hanoi';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on city using idx_name (Name='Hanoi'), with index condition: (city.`Name` = 'Hanoi') (cost=0.35 rows=1) (actual time=0.086..0.088 rows=1 loops=1)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Phân tích chiến lược thực thi mới:
- Thay vì thực hiện quét toàn bộ bảng (TABLE SCAN), MySQL đã sử dụng quét qua Index (điều này thể hiện ở nội dụng Index lookup).
- Chi phí thực hiện của câu lệnh đã cải thiện rất đáng kể: Từ lúc đầu là 410.85 xuống còn 0.35.
