Trong bài viết này, tôi sẽ chia sẻ với anh em cách mà tôi tối ưu MySQL trong những dự án của mình. Phạm vi của bài viết này liên quan đến tối ưu câu lệnh trong cơ sở dữ liệu, không liên quan tới các việc cấu hình, cài đặt, tinh chỉnh tham số gì anh em nhé.

Nguyên lý và cách tối ưu này thì anh em có thể áp dụng ở các loại cơ sở dữ liệu khác nhau luôn (đôi khi khác thuật ngữ tí tẹo thôi).

Nếu như anh em nào đang gặp một trong các vấn đề sau, thì bài viết này sẽ giúp ích được anh em.

• Làm việc với cơ sở dữ liệu, viết câu lệnh SQL lúc chậm, lúc nhanh, nói chung là hên xui, chậm cũng không hiểu chậm ở đâu, nhanh cũng chẳng hiểu nhanh ở đâu.
• Dự án đang có câu lệnh bị chậm, nhưng không biết tối ưu thế nào.
• Lên mạng đọc thấy người ta nói về Index, đánh bừa mấy Index, chẳng biết đánh Index xong thì kiểm thử lại kiểu gì, không biết nó có giúp ích gì không.

Okie, bây giờ vào luôn nội dung chính.

Bài viết này sử dụng cơ sở dữ liệu MySQL 8 nhé anh em

1. Tối ưu MySQL thì bắt đầu từ đâu nhỉ?

Câu hỏi này giống hệt với câu hỏi

Hỏi: Tôi muốn đi từ nhà tới công ty thì đi đường nào là tốt nhất?

Đáp: Xem Google Map, nó chỉ đường nào thì đi đường đó.

Khi chúng ta đến một thành phố xa lạ, ông nào cũng có thói quen xem google map phải không nào anh em.

Chúng ta nhập vào điểm bắt đầu và nơi muốn đến, ứng dụng sẽ cho chúng ta con đường chi tiết để đi đến nơi.

Có thể sẽ có rất nhièu cách thức khác nhau, con đường khác nhau cùng đưa chúng ta tới đích, nhưng google map sẽ chọn ra 1 con đường NHANH NHẤT.

Việc tối ưu MySQL cũng thế.

Khi chúng ta gửi 1 câu lệnh tới MySQL, cơ sở dữ liệu cũng sẽ có vô số cách thức để thực hiện yêu cầu của chúng ta.

Mỗi cách thức thực hiện giống như 1 con đường vậy, MySQL sẽ đánh giá các lựa chọn và sau đó quyết định chọn 1 con đường NHANH NHẤT (tức là tiêu tốn ít tài nguyên của hệ thống nhất).

Cái con đường ấy về thuật ngữ gọi là chiến lược thực thi anh em ạ (SQL Execution Plans).

Khi một câu lệnh SQL bị chậm, điều đầu tiên chúng ta cần phải biết là hệ thống đã chọn cho chúng ta con đường thế nào?

Xem SQL Execution Plans là điều bắt buộc nếu như anh em muốn tối ưu được những dự án trong thực tế.

Tôi cũng có các bài viết về tối ưu SQL trong các loại cơ sở dữ liệu khác, anh em có thể đọc những bài viết này tại đây

• Xem chiến lược thực thi của SQL trong SQL Server: Click vào bài viết tại đây.
• Xem chiến lược thực thi của SQL trong PostgreSQL: Click vào bài viết tại đây
• Xem chiến lược thực thi của SQL trong Oracle: Click vào bài viết tại đây

2. Làm thế nào để xem được chiến lược thực thi trong MySQL?

Để biết được câu lệnh SQL sẽ thực hiện như thế nào, chúng ta sử dụng lệnh EXPLAIN và EXPLAIN ANALYZE

Sự khác biệt giữa 2 lệnh này nằm ở các điểm sau

• Explain bản chất không hề thực hiện câu lệnh SQL, trong khi Explain Analyze thì thực thi câu lệnh đó, bản chất là sẽ sử dụng tài nguyên của hệ thống.
• Các thông số của Explain là thông tin ước lượng, trong khi Explain Analyze do đã thực thi câu lệnh nên sẽ trả ra các thông số tài nguyên sử dụng thực tế

1 phút cho quảng cáo nhé anh em: Tôi có 1 chương trình gồm có chiến lược, tư duy, kinh nghiệm để xây dựng sự nghiệp giúp anh em trở thành Top 1% những lập trình viên ngon nhất. Anh em cực kỳ TỰ TIN dù ở bất kỳ doanh nghiệp nào, đồng thười chúng ta sẽ tự biến bản thân mình thành một THỎI NAM CHÂM để TỰ ĐỘNG thu hút các CƠ HỘI LỚN trong sự nghiệp đến với bản thân mình. Anh em có thể tìm hiểu chương trình này tại đây nhé.

Tôi có một chương trình vô cùng đặc biệt, khi tham dự anh em sẽ có

Tôi sẽ lấy 4 ví dụ bao gồm các câu lệnh từ cơ bản đến phức tạp hơn tí tẹo để anh em thấy được hình dáng chiến lược thực thi ra sao nhé.

a. Cấu trúc của Table sử dụng trong các demo tại bài viết này

mysql> desc employees;

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employee_id    | int unsigned | NO   | PRI | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(25)  | NO   |     | NULL    |       |
| email          | varchar(25)  | NO   |     | NULL    |       |
| phone_number   | varchar(20)  | YES  |     | NULL    |       |
| hire_date      | date         | NO   |     | NULL    |       |
| job_id         | varchar(10)  | NO   | MUL | NULL    |       |
| salary         | decimal(8,2) | NO   | MUL | NULL    |       |
| commission_pct | decimal(2,2) | YES  |     | NULL    |       |
| manager_id     | int unsigned | YES  | MUL | NULL    |       |
| department_id  | int unsigned | YES  | MUL | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.04 sec)

mysql> desc departments;

+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| department_id   | int unsigned | NO   | PRI | NULL    |       |
| department_name | varchar(30)  | NO   |     | NULL    |       |
| manager_id      | int unsigned | YES  | MUL | NULL    |       |
| location_id     | int unsigned | YES  | MUL | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

b. Câu lệnh 1: Xem chiến lược thực thi khi câu lệnh chỉ làm việc trên 1 Table

Câu lệnh SQL

select * from employees;

Sử dụng Explain để xem thông tin như sau:

mysql> explain select * from employees;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  107 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Sử dụng Explain Analyze như sau

mysql> explain analyze select * from employees;
+---------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                     |
+---------------------------------------------------------------------------------------------+
| -> Table scan on employees  (cost=11 rows=107) (actual time=0.183..0.247 rows=107 loops=1)
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)