Optimize MySQL UNION, PAGING, and Subquery for high speed
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 ...
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
| code | color | start_year |
|---|---|---|
| a1 | White | 2015 |
| a2 | Blue | 2014 |
| a3 | Ping | 2013 |
| a4 | Red | 2013 |
| a5 | Yellow | 2014 |
| a6 | Green | 2015 |
Table table_b
| code | color | start_year |
|---|---|---|
| b1 | Purpose | 2013 |
| b2 | Green | 2014 |
| b3 | Black | 2014 |
| b4 | Yellow | 2015 |
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.
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
| id(pk) | code(unique) | name | address | phone |
|---|---|---|---|---|
| 1 | c001 | Tuan Anh | 12 Le Hong Phong | 012345 |
| 2 | c002 | Minh Thanh | 62 Hang Bai | 054321 |
| ... |
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 ;
