Query performance optimization - MySQL

Khi làm việc với dữ liệu lớn thì tốc độ truy vấn dữ liệu là cả vấn đề. Việc tối ưu câu lệnh query, đánh index, tối ưu schema luôn phải thực hiện xong hành với nhau. Topic này xin được trình bày một số "mẹo" tối ưu query thực hiện với MySQL.

Count() là hàm đặc biệt, nó làm việc theo hai cách rất khác nhau là đếm giá trị của trường, và đếm số dòng từ kết quả trả về.

• Nếu viết Count([tên cột]) thì hàm sẽ đếm số lần cột có giá trị khác NULL.
• Nếu viết Count(*) thì hàm sẽ không quan tâm đến cột, và chỉ đếm số dòng kết quả trả về.

Do vậy khi bạn muốn biết số dòng kết quả trả về thì hãy dùng Count(*). Ta xét ví dụ sau.

Bảng 'goods_item' có 20.000 bản ghi với cột id là khóa chính tự tăng.

Giờ ta cần biết có bao nhiêu bản ghi có id > 5? Thông thường ta sẽ viết query như sau:

Select COUNT(*) From goods_item
Where id > 5;

Ta sẽ kiểm tra hoạt động của query trên bằng lệnh Explain.

và thời gian query khoảng 0.012s.

Phân tích query trên ta thấy rằng engine sử dụng index để lấy giá trị id và so sánh (where) với hằng số 5. Việt so sánh này sẽ thực hiện với 9932 bản ghi. Thời gian truy vấn chủ yếu dùng để so sánh.

Để tối ưu query này ta tìm cách giảm thiểu số bản ghi cần phải so sánh. Nhận thấy rằng số bản ghi có id > 5 = Tổng số bản ghi - số bản ghi có id <= 5. Mà tổng số bản ghi là Count(*), tức engine chỉ sử dụng đến index. Vậy chỉ cần sử dụng điều kiện so sánh Where cho những bản ghi có id <= 5 (ta biết trước là tối đa 6 bản ghi).

Query được tối ưu như sau:

Select (Select Count(*) From goods_item) - Count(*)
From goods_item
Where id <= 5;

Ta lại kiểm tra bằng lệnh Explain.

Và thời gian query khoảng 0.007s, tối ưu 40%.

Trong query không phải lúc nào viết ngắn là tối ưu về hiệu năng. Ta xét ví dụ sau sử dụng database sakila (nếu chưa biết thì bạn hãy tìm hiểu thêm về database sakila).

Ta cần lấy danh sách diễn viên gồm các thông tin first_name, last_name, và số film đã tham gia.

Query 1

Select actor.first_name, actor.last_name, COUNT(*)
From sakila.film_actor
Inner JOIN sakila.actor USING(actor_id)
GROUP BY actor.first_name, actor.last_name;

Query 2

Select actor.first_name, actor.last_name, COUNT(*)
From sakila.film_actor
Inner JOIN sakila.actor USING(actor_id)
GROUP BY actor.actor_id;

Nếu như họ tên diễn viên là uniq thì cả hai query trên trả về kết quả như nhau, nhưng query 2 hiệu quả hơn vì actor_id đánh index. Cụ thể là query 1 thực hiện hết khoảng 0.01s, query 2 chỉ hết 0.005s, tối ưu 50%.

MySql sẽ tự động order kết quả trả về theo cột mà dùng trong điều kiện GROUP BY.

Ta xét tiếp ví dụ: lấy danh sách gồm thông tin actor.actor_id, actor.first_name, actor.last_name, film.release_year với điều kiện là film.release_year lớn nhất, danh sách được order theo first_name và last_name.

Query 1

Select * From
    (Select * From
        (Select A.actor_id, A.first_name, A.last_name, F.release_year
        From actor A
        Inner join film_actor FA Using(actor_id)
        Inner join film F On FA.film_id = F.film_id
        Order by A.first_name, A.last_name, F.release_year desc
        ) actor
    GROUP BY actor.actor_id
    -- Sau khi GROUP BY sẽ tự động sort theo actor.actor_id.
) actor2
-- Phải sort lại một lần nữa.
ORDER BY actor2.first_name, actor2.last_name, actor2.release_year desc;

Query 2

Select * From
	(Select A.actor_id, A.first_name, A.last_name, F.release_year
	From actor A
	Inner join film_actor FA Using(actor_id)
	Inner join film F On FA.film_id = F.film_id
	Order by A.first_name, A.last_name, F.release_year desc
	) actor
GROUP BY actor.actor_id
ORDER BY NULL;

Rõ ràng ta thấy query 1 rối rắm và không tối ưu bằng query 2, vì query 1 phải thực hiện thêm 1 lần order trên bảng tạm. Điều này làm tốc độ query rất chậm khi dữ liệu lớn. Mấu chốt ở đây là ORDER BY NULL ở query 2 sẽ bỏ qua tự động sort của GROUP BY. Tất nhiên là kết quả query 2 trả về vẫn giống query 1 vì nó dữ nguyên thứ tự order đầu tiên.

a. Tối ưu LIMIT, OFFSET

Một số trường hợp cần truy vấn một khối dữ liệu lớn hàng trăm nghìn, hàng triệu bản ghi, ta không thể dùng query để lấy và trả về toàn bộ dữ liệu một lúc. Sử dụng LIMIT, OFFSET là một giải pháp hữu hiệu.

b. Tối ưu JOIN query

• Đảm bảo chắc chắn rằng cột dùng trong điều kiện join ON, USSING phải được đánh index.
• Đảm bảo chắc chắn điều kiên GROUP BY, ORDER BY được thực hiện cho những cột chỉ của một bảng duy nhất. Cố gắng đánh index những cột này có thể.
• Và hãy lưu ý mỗi khi nâng cấp phiên bản MySql, vì rất có thể cú pháp JOIN có thể đã bị thay đổi.

c. Tối ưu Subquery

Vì subquery sẽ tạo ra bảng tạm, vậy nên tốt nhất là sử dụng JOIN thay subquery nếu có thể.