I. TĂNG TỐC DỘ XỬ LÝ CSDL MySQL
Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể
Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể. Loạt bài dưới đây sẽ trình bày các kỹ thuật tối ưu hoá hệ thống với CSDL MySQL. Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể.
Quy tắc 1 : Giảm thiểu sự kết nối tới MySQL Server.
Khi kết nối tới CSDL MySQL, chúng ta có 2 hàm kết nối là MySQL_connect() và MySQL_pconnect(). Về cơ bản thì hai hàm này có các tham số y hệt nhau, nhưng nội hàm của chúng có những khác biệt đáng kể.
Theo lý thuyết, mỗi lần gọi hàm MySQL_connect(), hệ thống sẽ khởi tạo một kết nối mới tới CSDL, còn khi sử dụng hàm MySQL_pconnect(), hệ thống sẽ tận dụng kết nối đã được thiết lập trước đó.
Nếu trang Web của chúng ta được triệu gọi nhiều lần trong một khoảng thời gian ngắn, hàm MySQL_connect() sẽ tiêu tốn một lượng đáng kể tài nguyên của hệ thống để thiết lập kết nối. Vì vậy, hãy cố gắng sử dụng hàm kết nối MySQL_pconnect().
Quy tắc 2 : Thiết lập các trường index 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ố.
Nếu các bạn học qua cấu trúc dữ liệu và giải thuật, hẳn chúng ta cũng phải nhớ đến các giải thuật tìm kiếm nhanh. Chúng ta đã đúc kết được rằng giải thuật tìm kiếm là nhanh nhất với cách tìm dựa trên bảng băm hoặc trên mảng đã sắp xếp (với thuật toán tìm kiếm nhị phân nổi tiếng). Các trường được thiết lập ở dạng index sẽ được sắp xếp trên một file riêng, khi chúng ta truy vấn dữ liệu thông qua các trường index, các giải thuật tìm kiếm sẽ phát huy tính hiệu quả tối đa của nó, đặc biệt là các trường index dạng số.
Vì vậy, hãy cố gắng thiết kế các truy vấn cũng như CSDL sao cho tối ưu nhất dựa trên nguyên tắc chỉ số này.
Quy tắc 3 Chấp nhận dư thừa dữ liệu
Một thiết kế dữ liệu theo dạng chuẩn 4 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 cao ngất ngểu, 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 4 để 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ệ.
Quy tắc 4 Chỉ lấy đúng và đủ dữ liệu cần thiết
Nhiều người thường thích truy vấn dạng Select .... Dấu ở đây sẽ bắt hệ thống làm việc mệt nhọc hơn vì phải xử lý nhiều dữ liệu hơn. Dữ liệu trả về cũng tiêu tốn nhiều bộ nhớ hơn. Vì vậy, thay vì select , hãy chỉ select những trường cần thiết.
Một vấn đề nữa là khi sử dụng hàm MySQL_fetch_array, nhiều người thường bỏ qua các tham số tuỳ chọn. Nếu có thể, hãy sử dụng tham số MySQL_ASSOC, khi đó hệ thống sẽ trả về một mảng với chỉ số là tên trường, như vậy các bạn sẽ dễ hình dung và đỡ tốn bộ nhớ vì phải phát sinh thêm một mảng với chỉ số dạng số.
Quy tắc 5 Giải phóng bộ nhớ ngay sau khi sử dụng xong
Theo mặc định thì PHP sẽ giải phóng bộ nhớ sau khi chạy xong toàn bộ chương trình, nhưng với một cỗ máy chủ già nua cũ kỹ với hàng trăm lượt truy cập một lúc thì 1 KB bộ nhớ cũng là một tài nguyên cực kỳ quý giá. Vậy tại sao chúng ta không giải phóng bộ nhớ cho những thứ không dùng đến
Sau khi thực hiện các truy vấn và thực hiện xong các phép tính toán với các bản ghi lấy được, hãy chịu khó nhét cái function MySQL_free_result() vào ngay nhé
II. TỐI ƯU HÓA CÂU TRUY VẤN CSDL MYSQL
1. Chỉ rõ columns cần lấy thay vì dấu [*] ngay cả khi lấy tất cả các column
2. Chỉ dùng HAVING sau khi selected các rows.
ex:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;3. Trong môt câu truy vấn có nhiều subqueries thì cố gắng giảm thiểu số lượng các câu subqueries
4. Sử dụng EXISTS, IN và TABLE JOIN một cách thích hợp trong truy vấn của bạn.
-> Thông thường IN có hiệu suất chậm nhất.
-> IN là hiệu quả khi hầu hết các tiêu chí lọc trong Subqueries.
-> EXISTS hiệu quả khi hầu hết các tiêu chí lọc trong truy vấn chính.
5. Dùng EXISTS thay cho DISTRICT khi join các bảng có mối quan hệ 1-n
6. Dùng UNION ALL thay UNION
7. Sử dụng điều kiện trong mệnh đề WHERE một cách hợp lý.
Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể
Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể. Loạt bài dưới đây sẽ trình bày các kỹ thuật tối ưu hoá hệ thống với CSDL MySQL. Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể.
Quy tắc 1 : Giảm thiểu sự kết nối tới MySQL Server.
Khi kết nối tới CSDL MySQL, chúng ta có 2 hàm kết nối là MySQL_connect() và MySQL_pconnect(). Về cơ bản thì hai hàm này có các tham số y hệt nhau, nhưng nội hàm của chúng có những khác biệt đáng kể.
Theo lý thuyết, mỗi lần gọi hàm MySQL_connect(), hệ thống sẽ khởi tạo một kết nối mới tới CSDL, còn khi sử dụng hàm MySQL_pconnect(), hệ thống sẽ tận dụng kết nối đã được thiết lập trước đó.
Nếu trang Web của chúng ta được triệu gọi nhiều lần trong một khoảng thời gian ngắn, hàm MySQL_connect() sẽ tiêu tốn một lượng đáng kể tài nguyên của hệ thống để thiết lập kết nối. Vì vậy, hãy cố gắng sử dụng hàm kết nối MySQL_pconnect().
Quy tắc 2 : Thiết lập các trường index 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ố.
Nếu các bạn học qua cấu trúc dữ liệu và giải thuật, hẳn chúng ta cũng phải nhớ đến các giải thuật tìm kiếm nhanh. Chúng ta đã đúc kết được rằng giải thuật tìm kiếm là nhanh nhất với cách tìm dựa trên bảng băm hoặc trên mảng đã sắp xếp (với thuật toán tìm kiếm nhị phân nổi tiếng). Các trường được thiết lập ở dạng index sẽ được sắp xếp trên một file riêng, khi chúng ta truy vấn dữ liệu thông qua các trường index, các giải thuật tìm kiếm sẽ phát huy tính hiệu quả tối đa của nó, đặc biệt là các trường index dạng số.
Vì vậy, hãy cố gắng thiết kế các truy vấn cũng như CSDL sao cho tối ưu nhất dựa trên nguyên tắc chỉ số này.
Quy tắc 3 Chấp nhận dư thừa dữ liệu
Một thiết kế dữ liệu theo dạng chuẩn 4 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 cao ngất ngểu, 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 4 để 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ệ.
Quy tắc 4 Chỉ lấy đúng và đủ dữ liệu cần thiết
Nhiều người thường thích truy vấn dạng Select .... Dấu ở đây sẽ bắt hệ thống làm việc mệt nhọc hơn vì phải xử lý nhiều dữ liệu hơn. Dữ liệu trả về cũng tiêu tốn nhiều bộ nhớ hơn. Vì vậy, thay vì select , hãy chỉ select những trường cần thiết.
Một vấn đề nữa là khi sử dụng hàm MySQL_fetch_array, nhiều người thường bỏ qua các tham số tuỳ chọn. Nếu có thể, hãy sử dụng tham số MySQL_ASSOC, khi đó hệ thống sẽ trả về một mảng với chỉ số là tên trường, như vậy các bạn sẽ dễ hình dung và đỡ tốn bộ nhớ vì phải phát sinh thêm một mảng với chỉ số dạng số.
Quy tắc 5 Giải phóng bộ nhớ ngay sau khi sử dụng xong
Theo mặc định thì PHP sẽ giải phóng bộ nhớ sau khi chạy xong toàn bộ chương trình, nhưng với một cỗ máy chủ già nua cũ kỹ với hàng trăm lượt truy cập một lúc thì 1 KB bộ nhớ cũng là một tài nguyên cực kỳ quý giá. Vậy tại sao chúng ta không giải phóng bộ nhớ cho những thứ không dùng đến
Sau khi thực hiện các truy vấn và thực hiện xong các phép tính toán với các bản ghi lấy được, hãy chịu khó nhét cái function MySQL_free_result() vào ngay nhé
II. TỐI ƯU HÓA CÂU TRUY VẤN CSDL MYSQL
1. Chỉ rõ columns cần lấy thay vì dấu [*] ngay cả khi lấy tất cả các column
2. Chỉ dùng HAVING sau khi selected các rows.
ex:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;3. Trong môt câu truy vấn có nhiều subqueries thì cố gắng giảm thiểu số lượng các câu subqueries
4. Sử dụng EXISTS, IN và TABLE JOIN một cách thích hợp trong truy vấn của bạn.
-> Thông thường IN có hiệu suất chậm nhất.
-> IN là hiệu quả khi hầu hết các tiêu chí lọc trong Subqueries.
-> EXISTS hiệu quả khi hầu hết các tiêu chí lọc trong truy vấn chính.
5. Dùng EXISTS thay cho DISTRICT khi join các bảng có mối quan hệ 1-n
6. Dùng UNION ALL thay UNION
7. Sử dụng điều kiện trong mệnh đề WHERE một cách hợp lý.
Nhận xét