Query Performance Optimization
Bài đăng này đã không được cập nhật trong 7 năm
Giới thiệu
Ở các bài viết trước mình có giới thiệu qua các một số cách để tăng hiệu năng trong mysql(ví dụ: sử dụng index, thiết kế schema). Tuy nhiên như thế vẫn chưa đủ, để cho hiểu năng tốt hơn nữa thì việc cần làm là thiết kế các câu truy vấn một cách hợp lí và tối ưu nhất. Cho dù bạn có thiết kế schema hay đánh index cho các trường một cách tối ưu, nhưng với các câu truy vấn tồi cũng sẽ ảnh hưởng tới hiệu năng của cả hệ thống. Chính vì thế hôm nay mình viết bài viết này để chia sẻ một số lưu ý khi làm việc với database.
Hầu hết các framework hiện nay đều hỗ trợ chúng ta cơ chế ORM và sinh tự động các câu truy vấn, như vây có thể bạn nghĩ là bài viết là không đem lại giá trị. Nhưng thực tế thì lại khác trong một số trường hợp việc tự mình phải viết các câu truy vấn trong các trường hợp phức tạp là ko thế tránh được trong các dự án, hay trong một ngày đẹp trời khi mà dữ liệu sinh ra với số lượng lớn và tăng một cách nhanh chóng, ứng dụng của bạn trở lên nặng nề hơn thì hãy xem lại câu truy vấn của bạn viết đã ổn chưa đã được tơi ưu chưa.
Sau đây mình xin được chia sẻ một số tip mình đọc được:
Đòi hỏi hệ thống quá nhiều dữ liệu không cần thiết
Một số câu lệnh viết ra thường yêu cầu quá nhiều dữ liệu không cần thiết, điều này dẫn đến tiêu tốn khá nhiều tài nguyên của máy(network overhead, memory...). Sau đây là một số ví dụ điển hình
- Lấy ra toàn bộ các cột khi truy vấn Giả sử trong ví dụ sau yêu cầu đặt ra là lấy toàn bộ tên diễn viên trong bộ phim Academy Dinosaur và câu truy vấn để thực hiện yêu cầu như sau:
mysql> SELECT * FROM sakila.actor
-> INNER JOIN sakila.film_actor USING(actor_id)
-> INNER JOIN sakila.film USING(film_id)
-> WHERE sakila.film.title = 'Academy Dinosaur';
Câu truy vấn sẽ trả về toàn bộ dữ liệu của 3 bảng, thay vì viết như trên ta có thể viết lại như sau:
mysql> SELECT sakila.actor.* FROM sakila.actor...;
Luôn luôn đặt câu hỏi khi sử dụng câu truy vấn như sau SELECT *. Bạn có thực sự cần thiết tất cả các cột(chắc chắn câu trả lời là không). Việc lấy ra tất cả các cột làm hạn chế việc tối ưu hóa hệ thống. Một số các hệ quản trị dữ liệu còn cấm sử dụng câu lệnh SELECT * bời vì nó sẽ giảm nguy cơ gây ra lỗi khi ai đó chỉnh sửa các cột trong bảng dữ liệu. Trong một số trường hợp việc lấy ra nhiều hơn dữ liệu mà bạn cần là điều không thể tránh khỏi(các lập trình viên thường sẽ sử dụng lại code để cũ hạn chế dư thừa code DRY) đây cũng là một điều đáng cân nhắc cho dù nó sẽ tốn thêm nhiều tài nguyên. Tuy nhiên nếu bằng cách nào đó bạn sử dụng cơ chế caching dữ liệu trong ứng dụng của bạn thì việc lấy ra nhiều dữ liệu hơn trong khi truy vấn là điều hoàn toàn chấp nhận được.
- Lấy ra dữ liệu lặp lại giống nhau Nếu bạn không cẩn thận, rất dễ để bạn viết code trong ứng dụng mà lấy dữ liệu trong bảng một cách lặp lại nhiều lần, thực hiện một câu lệnh nhưng nhiều lần . Ví dụ trong đoạn code sau thể hiện việc lấy dữ liệu thống kê doanh số của 1 cửa hàng trong 7 ngày
['mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun'].each do |day|
Shop.statistic_revenue_by(day_of_week: day)
end
Đoạn code trên được viết bằng ngôn ngữ ruby thỏa mãn yêu cầu lấy ra doanh số của cửa hàng 7 ngày trong tuần tuy nhiên việc để câu truy vấn trong vòng for như trên khiến cho ứng dụng liên tục gọi tới DB và đây là một điều tuyệt đối phải tránh trong quá trình phát triển phần mềm. Đối với đoạn code trên thay vì phải truy vấn từng ngày thì bạn có thể lấy hết doanh số cả 7 ngày ra và xử lý kết quả thu được sẽ là một biện pháp tối ưu hơn.
Trong framework ruby on rails có hỗ trợ các phương thức cache dữ liệu đó là cơ chế eager_load. Với eager load việc các câu lệnh giống nhau (n+1 query) sinh ra và truy vấn đến DB sẽ hạn chế
Database của bạn đang phải đọc quá nhiều dữ liệu
Sau khi bạn đã chắc chắn rằng câu truy vấn của bạn đã lấy ra những dữ liệu cần thiết thì việc tiếp theo cần phải cân nhắc câu truy vấn có phải kiểm tra quá nhiều dữ liệu để lấy được kết quả không. Trong mysql thì biện pháp đo lường đơn giản nhất là:
- Response time
Response time bao gồm tổng của hai thời gian: service time và queue time. Service time là thời gian cần để server thực hiện câu truy vấn. Queue time là thời gian chờ đợi khi mà server không thực hiện câu truy vấn bởi vì nó phải chờ đợi các thao tác khác như I/O, trong hàng chờ khi có một connection khác đang sử dụng tài nguyên và một số các thao tác khác...Response time thì không nhất quán trong mọi trường hợp do đó và ta không thể đo lường một cách chính xác bao nhiêu sẽ là tối ưu, một số yếu tố khác ảnh hưởng tới response time: cơ chế khóa tài nguyên của storage, khả năng chịu tải(high concurrency), phần cứng cũng được cân nhắc là có tác động tới response time. Khi bạn nhìn vào response time của câu truy vấn, hay tự đặt cho bản thân một câu hỏi liệu thời gian đó đã hợp lí hay chưa từ đó đưa ra các biện pháp để khắc phục. Để có thể tính toán chính xác hơn bạn có thể tham khảo sách của tác giả Relational Database Index Design and the Optimizers (Wiley) . 2. Số cột đã kiểm tra và số cột trả về
Một cách nữa để đánh giá câu lệnh truy vấn của bạn là số các dòng đã được được kiểm tra tuy nhiên đấy cũng ko phải là một cách tối ưu để tìm ra câu truy vấn tồi. Việc truy cập vào các hàng dữ liệu là không giống nhau, những hàng ngắn hơn sẽ truy cập nhanh hơn, những cột được lấy từ bộ nhớ sẽ nhanh hơn là đọc từ ổ đĩa. Con số lý tưởng nhất khi số lượng các dòng kiểm tra bằng với các dòng trả về tuy nhiên điều này khó xảy ra trong thực tế. Do đó thông thường tỉ lệ số dòng kiểm tra / số dòng trả về trong khoảng 1 : 1 và 10 : 1 (có đôi khi ) 3. Số cột đã kiểm tra và kiểu truy cập(access types)
Mysql sử dụng một vài phương thức truy cập để tìm được dòng dữ liệu mong muốn. Một số cách thì cần kiểm tra rất nhiều dòng dữ liệu, tuy nhiên cũng có những cách có thể trả kết về kết quả mà chỉ cần kiểm tra rất ít. Để có thể biết phương thức mà mysql sử dụng để truy vấn kết quả ta sẽ sử dụng câu lệnh EXPLAIN khi đó phương thức truy cập sẽ được thể hiện ở cột type. Ngoài ra bọn còn nên chú ý đến cột access type bao gồm các kết quả sau có thể có: "full table scan; index scans; range scans; unique index lookups; constants". Nhìn vào các kiểu access ta có thể thấy rằng kiểu full scan table sẽ là lâu nhất và tốn tài nguyên nhất và sẽ nhanh dần cho đến kiểu constants có nghĩa là chỉ có 1 giá trị được sử dụng làm điều kiện(single-value accesses).Ví dụ: ta sẽ xem xét 1 câu query đơn giản trong bảng Sakila của mysql
mysql> SELECT * FROM sakila.film_actor WHERE film_id = 1;
+----------+---------+---------------------+
| actor_id | film_id | last_update |
+----------+---------+---------------------+
| 1 | 1 | 2006-02-15 05:05:03 |
| 10 | 1 | 2006-02-15 05:05:03 |
| 20 | 1 | 2006-02-15 05:05:03 |
| 30 | 1 | 2006-02-15 05:05:03 |
| 40 | 1 | 2006-02-15 05:05:03 |
| 53 | 1 | 2006-02-15 05:05:03 |
| 108 | 1 | 2006-02-15 05:05:03 |
| 162 | 1 | 2006-02-15 05:05:03 |
| 188 | 1 | 2006-02-15 05:05:03 |
| 198 | 1 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
10 rows in set (0,07 sec)
Kết quả trả về 10 dòng dữ liệu, ta sẽ sử dụng explain để xem chi tiết thông tin câu truy vấn vừa thực hiện:
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
filtered: 100.00
Extra: NULL
Qua thông tin trên ta có thể thấy rằng cột film_id đã được đánh index; kiểu truy cập là constant; số cột kiểm tra là 10. Có nghĩa là câu lệnh trên đã được tối ưu hóa. Chuyện gì sẽ xảy ra nếu ta loại bỏ index của cột film_id
mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where
Đúng như dự đoán kiểu truy cập lúc này full table scan có nghĩa là mysql đã tìm kiếm tất cả các dòng dữ liệu để đưa ra được kết quả. Số dòng đã kiểm tra để tìm ra kết quả thỏa mãn là 5073 cũng tương ứng với số dòng dữ liệu trong bảng film_actor. Ở dòng extra còn cho ta thêm thông tin rằng Mysql dùng WHERE để loại bỏ các dòng dữ liệu sau khi đọc qua các dòng mà ko thỏa mãn điều kiện.
Như vậy, mysql có thể sử dụng câu lệnh WHERE theo 3 cách, từ tốt đến kém:
- Áp dụng điều kiện trực tiếp vào phần mà chỉ mục(index) tìm thấy thỏa mãn điều kiện.
- Index không thể tìm thấy trực tiếp các phần thỏa mãn điều kiện nhưng vẫn có thể loại bỏ một số phần không thỏa mãn.
- Kiểm tra toàn bộ các dòng trong bảng để tìm những dòng thỏa mãn điều kiện. Ví dụ trên cho ta thấy tầm quan trọng trong việc sử dụng index để tối ưu hóa câu truy vấn của bạn. Tuy nhiên có lưu ý rằng thêm index không có nghĩa là mysql sẽ kiểm tra với số dòng tối thiểu nhất ví dụ với câu truy vấn sau
mysql> SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;
+----------+----------+
| actor_id | COUNT(*) |
+----------+----------+
| 1 | 19 |
| 2 | 25 |
| 3 | 22 |
| 4 | 22 |
| 5 | 29 |
| 6 | 20 |
| 7 | 30 |
| 8 | 20 |
| 9 | 25 |
| 10 | 22 |
...
200 rows in set (0,13 sec
Câu lệnh này trả về 200 dòng kết quả, nhưng nó cần đọc rất nhiều dòng dữ liệu để có tập dữ liệu như trên, như vậy index không phải lúc nào cũng có tối ưu số dòng phải kiểm tra khi thực hiện truy vấn
mysql> Explain SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: index
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY
key_len: 4
ref: NULL
rows: 5462
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0,00 sec)
Tổng kết khi thực hiện một câu truy vấn mà câu lệnh phải kiểm tra quá nhiều dòng dữ liệu để để xử lý điều kiện trả về số dòng dữ liệu quá ít thì bạn có thể thử một số cách để cải thiện:
- Đánh index và trường điều kiện để giảm thiểu số dòng phải kiểm tra
- Tối ưu hóa schema
- Viết lại câu lệnh sql, đơn giản hóa câu lệnh truy vấn
Tái cấu trúc cây truy vấn
Trước khi đến với phần tái cấu trúc câu truy vấn có một điều bạn nên biết Mysql được thiết kế để xử lý việc kết nối và ngắt kết nối một cách hiệu quả và thực hiên các câu lệnh đơn giản một cách nhanh chóng. Với sự hiện đại của internet ngày này việc xuất hiện độ trễ đã được giảm thiều một cách tối đa. Do đó mysql có thể xử lý hơn 100,000 câu truy vấn đơn giản trong 1 giây và hơn 2000 câu lệnh mội giây tương ứng với 1GB network như vậy việc chạy nhiều câu lệnh một lúc không phải là một điều qúa tệ.
- Tách câu truy vấn Một trong những cách để tách câu truy vấn đó là chia để trị, việc xóa một lượng lớn dữ liệu trong bảng bẳng một câu truy vấn phức tạp dẫn đến việc mất thời gian để khóa nguồn tài nguyên trong khi thực hiện câu lệnh. Do đó tách câu truy vấn to thành các câu truy vấn đơn giản hơn có thể gia tăng hiệu năng một cách đáng kể. Ví dụ bạn xem một câu truy vấn để xóa các tin nhắn đã cũ(các tin nhắn cách thời điểm hiện tại ba tháng) dưới đây:
mysql> DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);
Thay vào đó bạn có thể tái cấu trúc câu truy vấn như sau:
rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
LIMIT 10000")
} while rows_affected > 0
Bạn có thấy việc giới hạn LIMIT 10000 tin nhắn xóa nó sẽ là một tác vụ đủ lớn để xử lý một cách hiệu quả . Nếu tốt hơn nữa có thể đặt thêm sleep time giữa các câu lệnh DELETE để giảm thiểu thời gian tài nguyên bị khóa.
2. Join Decomposition (tách bỏ JOIN)
Rất nhiều ứng dụng hiện năng cao hiện nay sử dụng cơ chế tách bỏ join. Bằng cách tách bỏ các phép join trong câu truy vấn thay vào đó ta sẽ chạy hàng loạt các câu lệnh truy vấn lên từng bảng trong database, sau đó mới thực hiện join các kết quả đo lại trong ứng dụng, ví dụ:
mysql> SELECT * FROM tag
-> JOIN tag_post ON tag_post.tag_id=tag.id
-> JOIN post ON tag_post.post_id=post.id
-> WHERE tag.tag='mysql';
Thay vì viết câu lệnh như trên, ta có thể viết lại câu lệnh dưới đây:
mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);
Nhìn qua có vẻ như là câu lệnh trên gây lãng phí do ta đã tăng số lượng câu truy vấn lên, tuy nhiên việc viết các câu lệnh như thế làm tăng hiệu suất một cách đáng kể:
- Việc caching dữ liệu sẽ hiệu quả hơn
- Thực hiện lần lượt các câu lệnh tránh việc lock tài nguyên
- Thực hiện join sau khi đã lấy ra kết quả sẽ dễ dàng hơn trong trường hợp đặt database tại nhiều server khác nhau
- Giảm thiểu số lượng các dòng dữ liệu phải kiểm tra Với những lợi ích như trên việc join dữ liệu ở tầng ứng dụng sẽ hiệu quả hơn bởi việc cache lại kết quả và sử dụng lại các kết quả vừa thực hiện ở câu truy vấn trước đó, ngoài ra bạn có thể phân tán dữ liệu qua rất nhiều server.
Kết luận
Bài viết của mình đến đây là kết thúc nếu cảm thấy có phần nào chưa đúng hoặc chưa rõ ràng thị bạn có thể hỏi ở phía dưới để mình có thể cải thiện bài viết. Cảm ơn các bạn đã đọc bài viết của mình hy vong bài viết sẽ giúp ích cho việc nâng cao hiệu năng cho hệ thống.
Bài viết trên được tham khảo từ cuốn sách High.Performance.MySQL
All rights reserved
