MySQL là hệ quản trị cơ sở dữ liệu phổ biến nhất và có lẽ là tốt nhất vài năm trở lại đây. Lập trình viên backend thì hầu hết ai cũng biết đến, làm việc với MySQL. Dự án mình đang tham gia cũng sử dụng MySQL, và việc viết các câu lệnh chuy vấn SQL với MySQL là công việc hàng ngày.

Bài viết tháng này mình tìm hiểu và trình bày về một số cách tối ưu để cải thiện tốc độ cho câu chuy vấn SQL với MySQL.

1. Dùng Union All thì nhanh hơn Union

Union làm việc thế nào?

Ta dùng lệnh Union để kết hợp kết quả của nhiều câu truy vấn (có cùng số cột) với nhau. Khi dó MySQL engin sẽ thực hiện các bước: kết hợp kết quả của mỗi câu chuy vấn, sắp xếp chúng, loại bỏ dữ liệu trùng lặp, trả về dữ liệu dạng set.

Ta xét ví dụ sau: Table table_a

Table table_b

Ta sử dụng Union lấy số màu ở hai bảng table_a và table_b như sau:

Select color from table_a
Union
Select color from table_b;

Kết quả:
+ Kết hợp và sort
color
------
Black
Blue
Green
Green
Ping
Purpose
Red
White
Yellow
Yellow

+ Loại bỏ duplicate và trả về kết quả

color
------
Black
Blue
Green
Ping
Purpose
Red
White
Yellow

Khi ta sử dụng Union All MySQL engin chỉ kết hợp kết quả của 2 query và trả về kết quả.

Select color from table_a
Union All
Select color from table_b;

Kết quả:
color
------
White
Blue
Ping
Red
Yellow
Green
Purpose
Green
Black
Yellow

Vì Union All không thực hiện sắp xếp và loại bỏ dupplicate dữ liệu nên truy vấn sẽ nhanh hơn Union rất nhiều khi thực hiện truy vấn với dữ liệu lớn. Khi ta không cần sắp xếp và loại bỏ dữ liệu trùng lặp thì hãy sử dụng Union All cho câu chuy vấn với MySQL.

2. Đưa điều kiện chung vào từng subquery khi sử dụng Union

Ta xét câu chuy vấn sau.

Select color, start_year from table_a
Union
Select color, start_year from table_b;
Where start_year > 2013;

Khi đó điều kiện start_year > 2013 được áp dụng (kiểm tra) cho toàn bộ 10 bản ghi dữ liệu trả về trên bảng tạm để được kết quả thực sự trả về.

Giờ ta sẽ chuyển điều kiện where start_year > 2013 vào trong từng sub query.

Select color, start_year from table_a
Where start_year > 2013
Union
Select color, start_year from table_b
Where start_year > 2013
;
-------------------------
+ Kết hợp và sort
color
------
Black
Blue
Green
Green
Ping
Purpose
Red
White
Yellow
Yellow

+ Lọc theo điều kiện truy vấn
color
------
Black
Blue
Green
Green
White

+ Loại bỏ duplicate
color
------
Black
Blue
Green
White

Giờ điều kiện truy vấn cũng được thực hiện cho 10 bản ghi dữ liệu (ở hai bảng thực riêng biệt), nhưng kết quả gộp của 2 sub query chỉ là 5 bản ghi.

+ Gộp và sắp xếp
color
-------
Black
Blue
Green
Green
White

+ Loại bỏ duplicate
color
------
Black
Blue
Green
White

Kết quả trả về là như nhau giữa hai phương pháp, nhưng tốc độ thì khác nhau. Việc sắp xếp và loại bỏ duplicate trên bảng tạm có số lượng bản ghi nhỏ hơn sẽ nhanh hơn. Đặc biệt khi thực hiện truy vấn với nhiều bảng và có số lượng bản ghi lớn hàng triệu, chục triệu bản ghi thì tốc độ sẽ khác nhau rất nhiều giữa hai phương pháp.

3. Tạo page không cần loại bỏ record

Dùng SQL trong tạo phân trang thông thường chúng ta sử dụng LIMIT và OFFSET. Ví dụ.

Select * from custormer
Limit 50 Offset 50;

Câu query trên sẽ trả về danh sách khách hàng cho trang thứ 1 (mỗi trang 50 bản ghi). Mặc định MySQL sẽ quét toàn bộ từ bản ghi 1 của danh sách, sau đó loại bỏ đi 50 bản ghi đầu chỉ lấy từ bản ghi 51 trở đi.

Vì thiết kế bảng thường có khóa chính (hoặc unique). Giả sử bảng customer có thiết kế như sau.

customer

Giờ ta tạo câu truy vấn trả về dữ liệu cho page 1.

Select * from custormer
Where id > 0
Limit 50;

Giả xử bản ghi thứ 50 có id = 101, khi đó query cho page 2 sẽ là.

Select * from custormer
Where id > 101
Limit 50;

Vì id là khóa chính được đánh index nên tốc độ của truy vấn rất nhanh. Bằng việc sử dụng điều kiện id > ta chánh được việc phải duyệt và loại bỏ những bản ghi đầu để được những bản ghi mong muốn theo page.

4. Thay thế Subquery IN trong câu lệnh Update bằng INNER JOIN

Với câu lệnh Update có sử dụng điều kiện IN với một Subquery thì thường sẽ rất chậm, đặc biệt Subquery là một query phức tạp với nhiều điều kiện lọc. Sở dĩ chậm là vì truy tìm mỗi bản ghi ở mệnh đề chính cho việc update dữ liệu thì Subquery được thực hiện một lần. Ví dụ:

Update backet
set ....
Where backet_id IN
(Select backet_id From backet_item Where item_id...)
;

Ta tối ưu câu lệnh trên sử dụng INNER JOIN như sau.

Update backet
INNER JOIN backet_item using(backet_id)
set ....
Where item_id...
;

5. Thay INNER JOIN bằng Subquery khi điều kiện Join không đánh Index

Thông thường câu truy vấn sử dụng INNER JOIN sẽ nhanh hơn dùng Subquery khi các field dùng dể join giữa các bảng được đánh index. Nhưng ngược lại, khi điều kiện join không được đánh index thì câu truy vấn sẽ rất chậm, càng chậm hơn với dữ liệu lớn.

Select A.cl1, A.cl2, B.cl1, B.cl2 From table1 A
INNER JOIN table2 B on A.cl1 = B.cl2
;

Ta sẽ khắc phục nhựng điểm không được đánh index cho các field join bằng cách sử dụng Subquery và đánh order cho điều kiện join.

Select * From
(Select cl1, cl2 From table1 Order by cl1) A
INNER JOIN
(Select cl1, cl2 From table2 Order by cl1) B
on A.cl1 = B.cl2
;

Tài liệu tham khảo

• iheavy
• tocker
• dzone

Thank you.