Tăng tốc độ truy vấn trong mysql
Bài đăng này đã không được cập nhật trong 3 năm
1. Các nguyên nhân làm chậm truy vấn
Ngoài nguyên nhân khiến cho việc truy vấn dữ liệu ứng dụng của bạn bị chậm. Loại bỏ các nguyên nhân vật lý như thiếu bộ nhớ, kết nối mạng chậm, dưới đây là một số nguyên nhân khách quan và cách khắc phục.
-
Đầu tiên phải kể đến đó là việc thiết kế các bảng cơ sở dữ liệu chưa được hợp lý
- Một bảng chứa quá nhiều thuộc tính logic phức tạp, ví dụ: 1 hệ thống luôn có 2 ngôn ngữ tiếng Anh và tiếng Nhật thì nên lưu 2 thuộc tính: language_ja và language_en thay vì chỉ là 1 thuộc tính: language
- Thiết kế chứa nhiều dữ liệu lặp dẫn đến việc dư thừa dữ liệu và khó kiểm soát.
-
Thiếu hoặc không sử dụng Indexes trong các bảng dữ liệu. Việc đánh chỉ mục cho bảng dữ liệu sẽ giúp truy vấn nhanh hơn để tìm ra bản ghi mong muốn.
-
Các câu truy vấn Transact-SQL chuyển số lượng dữ liệu lớn từ server đến client.
-
Truy vấn nhiều, trả lại nhiều dữ liệu dư thừa. Nhiều người chỉ quan tâm đến mục đích của câu truy vấn là lấy được đúng dữ liệu mình cần mà quên đi rằng việc trả lại qúa nhiều dữ liệu sẽ khiến ứng dụng chậm đi.
-
Các câu truy vấn quá nghèo nàn.
2. Cách khắc phục
1.Giảm thiểu sự kết nối tới hệ quản trị cơ sở dữ liệu
Mỗi lần lấy dữ liệu, hệ thống sẽ phải kết nối tới CSDL, điều này làm tăng thời gian của truy vấn. Nếu có rất nhiều truy vấn mà mỗi truy vấn chỉ xử lý trong thời gian ngắn, sẽ tiêu tốn rất nhiều thời gian. Thay vào đó, hãy chỉ sử dụng một số ít kết nối tới CSDL, mỗi kết nối sẽ xử lý nhiều nghiệp vụ hơn.
2.Chỉ SELECT những trường mà bạn cần
Đa số các nhà phát triển website thường sẽ SELECT * chọn tất cả các trường(fields) trong dòng dữ liệu. Tuy nhiên, bạn sẽ ngạc nhiên là một dòng SELECT * có thể gây chậm khi chạy truy vấn, đặc biệt là trên các bảng dữ liệu lớn. Giả sử, bạn chỉ sử dụng mội trường name trong bảng Users thì thay vì chọn tất cả 20 trường của tất cả bản ghi, bạn chỉ nên SELECT đúng trường name mà bạn cần thôi. Nó sẽ chiếm ít bộ nhớ trên máy chủ của bạn và do đó cho phép máy chủ cơ sở dữ liệu của bạn sử dụng bộ nhớ trống để xử lý các truy vấn và các tiến trình khác.
SELECT name FROM USER;
Thay vì
SELECT * FROM USER;
3.Sử dụng Indexs và cố gắng truy vấn dữ liệu thông qua các điều kiện xác lập trên chỉ số.
Việc đánh chỉ mục cho 1 số thuộc tính trong bảng giúp cho việc tìm kiếm bản ghi nhanh hơn. Lợi ích của Indexs trong Mysql bạn có thể tham khảo trong bài: Sử dụng indexs trong mysql
4.Sử dụng EXPLAIN để phân tích truy vấn
Việc sử dụng EXPLAIN hết sức đơn giản, chỉ cần thêm nó vào trước SELECT trong câu truy vấn. Trước tiên hãy cùng tìm hiểu kết quả trả về của một câu truy vấn đơn giản để bạn có thể làm quen với các cột trong bảng kết quả.
EXPLAIN SELECT * FROM USER;
Bạn có thể bổ sung thêm từ khóa EXTENDED sau EXPLAIN và MySQL sẽ đưa ra các thông tin bổ sung về quá trình thực hiện truy vấn. Để xem chi tiết, thực hiện lệnh SHOW WARNINGS ngay sau lệnh EXPLAIN. Nó thường được dùng để xem các câu truy vấn được thực hiện sau bất cứ thay đổi nào được tạo ra bởi Query Optimizer.
EXPLAIN EXTENDED SELECT USER.name FROM USERS
JOIN Country ON (User.Country_id = Country.id)
WHERE Country.code = 'IND' AND Country.continent = 'Asia'
5.Đếm có bao nhiêu dữ liệu trả về
Việc đếm xem có bao nhiêu dữ liệu trả về rất quan trọng, nó giúp bạn so sánh được số truy vấn tính toán và số truy vấn thực tế. Nhằm tìm ra nguyên nhân và tối ưu truy vấn của bạn.
Bạn có thể đọc số truy vấn trong file Log hoặc ví dụ như trong Rails có 1 số Gem có thể đếm số truy vấn: sql_queries_count
hoặc db-query-matchers, ..
6.Chấp nhận dư thừa dữ liệu
Một thiết kế dữ liệu theo dạng chuẩn có thể rất đẹp mắt, nhưng khi truy vấn dữ liệu, chúng ta sẽ phải xới tung nhiều bảng quan hệ có khi chỉ để lấy ra một record. Ngày xưa, khi giá thành ổ cứng quá cao, dung lượng ổ cứng bé tẹo nên các cụ phải thiết kế dữ liệu ở dạng tiêu chuẩn cao nhằm giảm dung lượng lưu trữ, nhưng ngày nay, dung lượng lưu trữ không còn là vấn đề đáng lo lắng, vì vậy trong một số trường hợp, hãy chịu khó hi sinh tính đẹp đẽ của chuẩn để tăng tốc độ truy vấn. Nên nhớ rằng truy vấn trên một bảng sẽ nhanh hơn rất nhiều lần khi truy vấn trên nhiều bảng quan hệ.
7.Dùng nhiều inserts nếu có thể
Bạn có rất nhiều truy vấn INSERT để chạy cùng một lúc? Tại sao không gửi tất cả đến MySQL cùng lúc. Nó sẽ được thực hiện nhanh hơn rất nhiều. Điều đó đặc biệt hữu ích khi thực hiện nhập nhiều dữ liệu cùng lúc.
8.Chỉ dùng DISTINCT khi cần
Đặt từ khóa DISTINCT trong câu lệnh SELECT sẽ loại bỏ các kết quả trùng lặp trong số những kết quả trả về của câu truy vấn. Nó khiến máy chủ SQL phải thực hiện thêm thao tác SORT để sắp xếp dữ liệu nhằm nhận biết và loại bỏ các bản trùng lặp. Vì thế, nếu bạn biết trước các kết quả trả về sẽ không trùng lặp thì không nên dùng từ khóa DISTINCT trong câu lệnh SQL. Với việc sử dụng từ khóa DISTINCT trong câu truy vấn, bạn đã yêu cầu máy chủ SQL thực hiện thao tác sắp xếp vào loại bỏ các kết quả trùng lặp. Đây là phần công việc phụ thêm của máy chủ SQL và không có ý nghĩa gì nếu tập hợp kết quả của bạn chỉ bao gồm những bản ghi độc nhất.
9.Chỉ dùng UNION khi cần
Cũng giống như trường hợp từ khóa DISTINCT, toán tử UNION đòi hỏi thêm thao tác SORT để máy chủ SQL có thể loại bỏ những kết quả trùng lặp. Nếu bạn biết trước danh sách kết quả trả về không có kết quả nào giống nhau thì thao tác sắp xếp mà máy chủ SQL phải thực hiện trở nên không cần thiết. Bởi vậy khi bạn cần dùng toán tử UNION để nối hai tập hợp bản ghi với nhau, trong đó các bản ghi là độc nhất không trùng lặp, tốt hơn bạn nên dùng toán tử UNION ALL. Toán tử UNION ALL không loại bỏ các bản ghi trùng lặp bởi vậy sẽ giảm nhẹ phần công việc cho máy chủ SQL trong quá trình xử lý do không phải thực hiện thao tác sắp xếp. Giảm bớt công việc cho máy chủ SQL đồng nghĩa với việc thao tác xử lý sẽ được thực hiện nhanh hơn.
10.Sử dụng loại(type) trường(field) chính xác cho dữ liệu
Nên sử dụng đúng loại dữ liệu cho thuộc tính của bản dữ liệu, nhằm tránh tốn bộ nhớ. Ví dụ: Để lưu điểm môn học, sử dụng TINYINT tốt hơn INTERGER. Nó chiếm ít không gian lưu trữ trên máy chủ của bạn.
11.Giải phóng bộ nhớ ngay sau khi sử dụng xong
Ngày nay đa số các ngôn ngữ lập trình sẽ tự động giải phóng bộ nhớ sau khi chạy toàn bộ chương trình.
12.Áp dụng các thói quen tốt để code nhanh hơn
Có rất nhiều lý do để tập luyện cho mình quen với lối viết code tối ưu. Điều đó sẽ giúp bạn tránh được rủi ro có thể xảy ra khi cơ sở dữ liệu thay đổi, đồng thời cải thiện hiệu suất làm việc của máy chủ nhờ giảm thiểu lưu lượng truyền tải qua mạng.
Nguồn tham khảo:
All rights reserved
