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.
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:
- Bước 1: MySQL gửi câu truy vấn đến server
- Bước 2: MySQL kiểm tra query cache. Nếu kết quả đã tồn tại, trả về. Ngược lại, thực hiện bước tiếp theo.
- Bước 3: MySQL thực hiện parse câu truy vấn, đưa ra plan thực thi tối ưu.
- Bước 4: Plan được thực thi, gọi đến Storage engines
- Bước 5: Storage engines trả về kết quả là các row dữ liệu
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.
- Khi gom các câu query nhỏ thành một câu query lớn, lợi ích là làm giảm số lần kết nối và ngắt kết nối đến MySQL. Tuy nhiên, một câu query quá phức tạp, gộp nhiều query nhỏ không chắc giúp tăng hiệu suất truy vấn. Vì câu truy vấn phức tạp thì tại step 3 khi MySQL thực hiện parse câu truy vấn để tính toán và đưa ra plan thực thi tối ưu sẽ kém chính xác hơn. Điều đó dễ dẫn đến tình trạng plan thực thi chưa thực sự tối ưu, làm tốc độ thực thi chậm. Do đó, chúng ta cần cân nhắc khi viết câu truy vấn gộp.
- Trong vòng đời của câu truy vấn, quá trình thực thi (step 4) là một giai đoạn quan trọng. Quá trình này liên quan đến việc truy cập Storage engines một hay nhiều lần để lấy dữ liệu, thực hiện gom nhóm, sắp xếp hay duyệt qua các hàng dữ liệu, v.v. Đây là những tác vụ chiếm tài nguyên mạng, CPU. Vì vậy, nếu chúng ta viết câu query mà việc truy cập Storage engines thực hiện quá nhiều lần, hay quá lâu, hay nhiều lần truy cập dư thừa thì nó sẽ thực sự làm cho câu query chậm.
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
- Luôn sử dụng
SELECT (*)để fetch dữ liệu:SELECT (*)sẽ fetch tất cả các column của 1 table. Chúng ta nên chỉ định column cần lấy để tiết kiệm thời gian thực thi - Fetch nhiều row dữ liệu hơn cần thiết: Chỉ nên lấy đúng các row dữ liệu cần sử dụng thay vì lấy tất cả các row, mà chỉ sử dụng một số ít
- Fetch dữ liệu trùng nhau nhiều lần: Các dữ liệu được sử dụng nhiều nơi trong ứng dụng, nên được lưu lại ở cache, hay biến global, thay vì mỗi lần sử dụng chúng ta lại thực hiện fetch dữ liệu
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ặp
WHERE
Vớ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 |
|---|---|
((a AND b) AND c OR (((a AND b) AND (c AND d)))) |
a AND b AND c |
(a<b AND b=c) AND a=5 |
b>5 AND b=c AND a=5 |
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) |
b=5 OR b=6 |
Hạn chế sử dụng function trên column
Ta có câu truy vấn sau:
SELECT * FROM Orders WHERE YEAR(finished_at) = ‘2021’Sử dụng function YEAR(finished_at) dẫn đến MySQL không thể sử dụng index ở cột finished_at. Vì index được đánh trên cột finished_at, không phải trên YEAR(finished_at).
Để 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:
SELECT * FROM Orders WHERE finished_at >= '2021-01-01' AND finished_at < '2022-01-01';COUNT
Syntax: COUNT(expression) với expression: column name/string value
Thông thường chúng ta truyền expression 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 expression 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 employees 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(*) |
|---|---|
EXPLAIN ANALYZE SELECT COUNT(emp_no) FROM employees; |
EXPLAIN ANALYZE SELECT COUNT(*) FROM employees; |
COUNT(emp_no) có thời gian thực thi nhỉnh hơn một chút so với COUNT(*), vì storage engine luôn biết thông tin về số hàng trong một bảng. Trong khi COUNT(emp_no) 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).
FUNCTION
MySQL 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: RAND(), UUID()
Đặ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 |
|---|---|
EXPLAIN ANALYZE SELECT * FROM t WHERE id = POW(1,2); > -> Aggregate: count(0) (cost=0.10..0.10 rows=1) (actual time=0.032..0.032 rows=1 loops=1) -> Rows fetched before execution (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1) POW(): constant arguments, cho ra một constant Do đó, giá trị này có thể sử dụng làm index lookup |
EXPLAIN ANALYZE SELECT * FROM t WHERE id = RAND(); -> Aggregate: count(0) (cost=50.35 rows=238) (actual time=0.150..0.150 rows=1 loops=1) -> Filter: (tab_cb_user._id = rand()) (cost=26.55 rows=238) (actual time=0.132..0.132 rows=0 loops=1) -> Covering index scan on tab_cb_user using idx_deleted (cost=26.55 rows=238) (actual time=0.038..0.083 rows=238 loops=1) 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:
-
Tránh việc sử dụng gọi hàm không xác định trong mệnh đề WHERE
SET @keyval = RAND(); UPDATE t SET col_a = some_expr WHERE id = @keyval; -
Kết hợp thêm điều kiện khác để giúp optimizer tránh table scan
SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();
LIKE
Khi sử dụng LIKE, không nên sử dụng ký tự % hay * đặ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 |
|---|---|
select Username from Users where LIKE "%Hello" |
select Username from Users where LIKE "Hello%" |
Lý do:
MySQL lưu trữ nội bộ các varchar 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 where LIKE "Hello%": MySQL sẽ tìm các ký tự theo cây chỉ mục nhanh chóng. Ngược lại, với LIKE "%Hello", sẽ dẫn đến việc scan toàn bộ table.
Hạn chế toán tử so sánh 2 lần
Phép toán so sánh 2 lần như "<=", ">=", bản chất là phép toán OR.
Ví dụ: Viết câu truy vấn để lấy ra emp_no từ bảng employees với điều kiện là emp_no lớn hơn hoặc bằng 1000.
SELECT emp_no FROM employees WHERE emp_no >= 1000;Câu lệnh trên tương ứng với câu lệnh sau :
SELECT emp_no FROM employees WHERE emp_no > 1000 AND emp_no = 1000;Câu lệnh này sẽ khiến MySQL phải so sánh 2 lần: emp_no > 1000 và emp_no = 1000. 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:
SELECT emp_no FROM employees WHERE emp_no > 999Sử dụng INNER JOIN thay vì OUTER JOIN
INNER 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 |
|---|---|
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5; |
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1; |
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2 |
SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2 |
Giới hạn kết quả trả về
Sử dụng SELECT * 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 ALL
UNION đượ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 UNIONvà UNION ALL
Với UNION:
EXPLAIN
SELECT emp_no FROM employees
UNION
SELECT emp_no FROM dept_emp
Với UNION ALL:
EXPLAIN
SELECT emp_no FROM employees
UNION ALL
SELECT emp_no FROM dept_emp
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ết
Hi 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