Tốc độ thực thi câu truy vấn là một trong những yếu tố quan trọng, ảnh hưởng lớn đến performance của một ứng dụng phần mềm. Do đó, khi thực hiện performance tuning thì tối ưu các câu truy vấn là thực sự cần thiết. Trong bài viết này, chúng ta sẽ cùng tìm hiểu về cách tối ưu một số câu truy vấn thường gặp trong MySQL. Show Tại sao câu truy vấn thực thi chậm?Trong bài viết Sơ lược về kiến trúc MySQL, chúng ta đã tìm hiểu về quá trình MySQL thực thi một câu truy vấn, được tóm tắt ngắn gọn bởi các bước bên dưới:
Cách viết câu truy vấn có thể dẫn đến sự thay đổi thời gian thực thi của câu truy vấn: làm câu truy vấn được thực thi nhanh hơn hoặc chậm đi.
Dưới đây là một số trường hợp gây ra tình trạng câu truy vấn thực thi chậm: Lấy nhiều dữ liệu hơn nhu cầu sử dụng
Kiểm tra quá nhiều dữ liệu Khi dữ liệu cần lấy join từ nhiều bảng, mysql server cần truy cập từng hàng của từng bảng để tạo ra một hàng kết quả. Điều đó dẫn đến số row kiểm tra, duyệt qua nhiều hơn so với row kết quả. Nếu một câu query cần kiểm tra quá nhiều dữ liệu thì thời gian trả về kết quả sẽ tăng lên. Sau đây, phần tiếp theo chúng ta sẽ tìm hiểu về các cách tối ưu cụ thể cho các câu query thường sử dụng. Tối ưu một số câu truy vấn thường gặpWHEREVới WHERE, bạn chú ý những điểm dưới đây để giúp tăng thời gian thực thi truy vấn như sau: Rút gọn condition Điều kiện trong WHERE nên được viết đơn giản để quá trình parse và đưa plan thực thi tối ưu diễn ra nhanh chóng, chính xác. BAD GOOD
1
2
3
4
5
6 Hạn chế sử dụng function trên column Ta có câu truy vấn sau:
Sử dụng function
7 dẫn đến MySQL không thể sử dụng index ở cột
8. Vì index được đánh trên cột
8, không phải trên
7. Để có thể tránh được điều này, ta có thể viết lại câu truy vấn tương đương mà không phải sử dụng đến function:
COUNTSyntax:
1 với
2: column name/string value Thông thường chúng ta truyền
2 của COUNT là một column name. Và COUNT sẽ làm nhiệm vụ đếm số record mà column đó có giá trị. Một trường hợp phổ biến khác là COUNT(*). Lúc này COUNT sẽ làm nhiệm vụ đếm tất cả các record của table đó. Hãy cân nhắc việc sử dụng
2 phù hợp, vì nó sẽ ảnh hưởng đến hiệu năng của câu query. Cùng xem ví dụ sau đây: Phân tích hai câu query đều thực hiện đếm số record trên bảng
5 và đều cho ra kết quả giống nhau, nhưng thời gian thực hiện lại khác nhau: COUNT(column) COUNT(*)
6
7
8 có thời gian thực thi nhỉnh hơn một chút so với
9, vì storage engine luôn biết thông tin về số hàng trong một bảng. Trong khi
8 có sẽ mất thời gian để check xem record có NULL hay không. Do đó, nếu chỉ có nhu cầu đếm số hàng trong bảng, hãy sử dụng COUNT(*) thay vì COUNT(column). FUNCTIONMySQL functions (hàm) gồm 2 dạng: hàm xác định và hàm không xác định. Một hàm được gọi là không xác định nếu với giá trị cố định của các arguments, mỗi lần gọi hàm có thể cho ra kết quả khác nhau. Ví dụ về hàm không xác định:
1,
2 Đặc điểm của hàm không xác định là cho ra kết quả khác nhau mỗi lần chạy nên khi chúng ta sử dụng một hàm không xác định trong mệnh đề WHERE, thì nó sẽ được đánh giá lại giá trị trên mỗi dòng của bảng. Vì vậy, hàm không xác định có thể ảnh hưởng đến performance. Hàm xác định Hàm không xác định
3 POW(): constant arguments, cho ra một constant Do đó, giá trị này có thể sử dụng làm index lookup
4 RAND(): hàm không xác định Đối với mỗi dòng, nó có thể sẽ có một giá trị khác vì RAND() sẽ cho giá trị random. Do đó, truy vấn đọc mọi hàng của bảng, đánh giá cho mỗi hàng và trả về tất cả các hàng mà khóa chính khớp với giá trị ngẫu nhiên Một hàm không xác định không tạo ra giá trị cố định, optimizer không thể sử dụng các chiến lược như tra cứu chỉ mục được. Điều đó dẫn đến phải thực hiện table scan, khiến giảm performance. Cách khắc phục:
LIKEKhi sử dụng LIKE, không nên sử dụng ký tự
5 hay
6 đặt ở phía trước giá trị tìm kiếm. Các ký tự đại diện nên được đặt ở phía sau giá trị tìm kiếm: BAD GOOD
7
8 Lý do: MySQL lưu trữ nội bộ các
9 như cây chỉ mục với ký hiệu đầu tiên là gốc và phân nhánh cho mỗi chữ cái tiếp theo. Vì vậy khi tìm kiếm LIKE
0: MySQL sẽ tìm các ký tự theo cây chỉ mục nhanh chóng. Ngược lại, với
1, sẽ dẫn đến việc scan toàn bộ table. Hạn chế toán tử so sánh 2 lầnPhép toán so sánh 2 lần như
2, bản chất là phép toán OR. Ví dụ: Viết câu truy vấn để lấy ra
3 từ bảng
5 với điều kiện là
3 lớn hơn hoặc bằng 1000.
Câu lệnh trên tương ứng với câu lệnh sau :
Câu lệnh này sẽ khiến MySQL phải so sánh 2 lần:
6 và
7. Do đó làm chậm truy vấn. Thay vào đó, chúng ta có thể sử dụng điều kiện đơn giản hơn bên dưới:
Sử dụng INNER JOIN thay vì OUTER JOININNER JOIN và OUTER JOIN (gồm LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) khác nhau về tập kết quả trả về. Để hiểu rõ hơn về lý do vì sao nên sử dụng INNER JOIN thay vì OUTER JOIN, chúng ta cùng đi qua ví dụ sau: Bảng dữ liệu: Members, Movies Yêu cầu: lấy danh sách các thành viên đã thuê phim cùng với các tựa phim được họ thuê Lần lượt sử dụng INNER JOIN và OUTER JOIN (cụ thể là LEFT JOIN) để so sánh kết quả OUTER JOIN phải thực hiện tất cả công việc của một phép INNER JOIN, cộng thêm việc mở rộng kết quả bằng null cho các trường hợp không tìm thấy kết quả phù hợp với điều kiện. Nó cũng sẽ trả về nhiều hàng hơn, tăng thêm tổng thời gian thực hiện do kích thước lớn hơn của tập kết quả. Vì vậy, thông thường INNER JOIN sẽ được khuyến khích sử dụng hơn. Một số ví dụ về việc chuyển câu truy vấn từ OUTER JOIN sang INNER JOIN: BAD GOOG
8
9
0
1 Giới hạn kết quả trả vềSử dụng
2 sẽ khiến MySQL quét toàn bộ table: Nếu bạn không có nhu cầu sử dụng tất cả các dữ liệu của bảng, hãy chỉ định các cột cần thiết. Chỉ cần thay đổi đơn giản này sẽ giúp tiết kiệm bộ nhớ, I/O striping, dung lượng khi truyền từ server về client. Tối ưu hóa câu lệnh bằng UNION ALLUNION được thực hiện tương đương với câu lệnh SELECT DISTINCT, tức là MySQL sẽ thực hiện: sắp xếp, lọc và loại bỏ các bản ghi trùng. Nếu không quan tâm đến việc có bản ghi trùng thì không cần thiết sử dụng UNION. Trong trường hợp này, bạn nên sử dụng UNION ALL. Hãy xem qua ví dụ bên dưới để thấy sự khác biệt giữa
3và
4 Với UNION: Với UNION ALL: Như chúng ta đã thấy: 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, chỉ sử dụng cho trường hợp UNION bên trên. Đây là một hành vi tốn kém của MySQL. KếtHi vọng qua các phân tích và ví dụ bên trên, các bạn hiểu hơn về cách tối ưu một số câu truy vấn thường gặp trong MySQL. 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. Bạn có thể tham khảo thêm về câu lệnh này trong bài viết Tìm hiểu về MySQL EXPLAIN |