MySQL slow query log là gì? Cách bật và tìm truy vấn chậm

Khi website hoặc ứng dụng phản hồi chậm, nguyên nhân có thể nằm ở một truy vấn SQL mất nhiều thời gian, chứ không hẳn do máy chủ thiếu tài nguyên. MySQL slow query log giúp quản trị viên ghi lại các truy vấn vượt ngưỡng thời gian đã đặt để kiểm tra và tìm nguyên nhân. Bài viết hướng dẫn cách bật slow query log, xác định vị trí tệp log, đọc kết quả và chọn bước tiếp theo để tối ưu cơ sở dữ liệu.

MySQL slow query log là gì?

MySQL slow query log, hay nhật ký truy vấn chậm, là một loại log ghi lại các câu lệnh SQL mất nhiều thời gian hơn ngưỡng cấu hình. Quản trị viên có thể dùng dữ liệu này để biết truy vấn nào đáng kiểm tra, thay vì phải suy đoán dựa trên cảm giác website đang chậm.

Một bản ghi thường có thời điểm thực thi, thời lượng truy vấn, thông tin máy khách và câu lệnh SQL. Tùy cấu hình, log cũng có thể ghi thời gian chờ khóa, số hàng được kiểm tra hoặc trả về. Những chi tiết này giúp nhận diện truy vấn tốn thời gian và tìm manh mối về nguyên nhân.

Slow query log không giống general query log. General log ghi nhận gần như mọi hoạt động truy vấn, còn slow query log tập trung vào các truy vấn đáp ứng điều kiện được cấu hình. Vì vậy, đây thường là điểm bắt đầu phù hợp khi cần điều tra hiệu năng mà không muốn phải rà soát toàn bộ hoạt động của cơ sở dữ liệu.

Mặc định, truy vấn được xem là chậm khi thời gian chạy vượt giá trị long_query_time. Giá trị cụ thể phụ thuộc vào cấu hình của máy chủ và mục tiêu giám sát; không có một ngưỡng phù hợp cho mọi hệ thống. Ngoài ra, có thể bật ghi nhận truy vấn không sử dụng chỉ mục, nhưng tùy chọn này có thể làm log tăng nhanh và không phải lúc nào cũng chỉ ra vấn đề thực sự.

Chuẩn bị trước khi bật slow query log

Trước khi thay đổi cấu hình, hãy xác định bạn đang quản trị MySQL trực tiếp hay sử dụng dịch vụ có giới hạn quyền. Các lệnh thay đổi biến toàn cục thường cần quyền quản trị phù hợp. Nếu cơ sở dữ liệu do nhà cung cấp hosting quản lý, bạn có thể cần dùng bảng điều khiển hoặc nhờ bộ phận hỗ trợ thay đổi thiết lập.

Nên kiểm tra dung lượng đĩa, quyền ghi của tiến trình MySQL và cách hệ thống quản lý log. Nhật ký tăng dần theo lượng truy vấn; nếu để quá lâu mà không xoay vòng hoặc dọn dẹp đúng cách, tệp có thể chiếm nhiều dung lượng. Trên môi trường đang chạy thật, hãy cân nhắc bật trong thời gian cần chẩn đoán, theo dõi mức tăng và tắt những tùy chọn ghi quá rộng khi không còn cần thiết.

Bạn cũng nên ghi lại cấu hình hiện tại trước khi thay đổi. Các lệnh sau giúp kiểm tra trạng thái và những thiết lập liên quan:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'slow_query_log_file';

Nếu slow_query_log đang tắt, kiểm tra vị trí tệp log hiện tại và quyền truy cập trước khi bật. Điều này giúp tránh mất thời gian tìm tệp hoặc nhầm lẫn giữa log của các môi trường khác nhau.

Cách bật slow query log trong MySQL

Có hai cách phổ biến để bật slow query log: thay đổi cấu hình trong lúc MySQL đang chạy hoặc khai báo trong tệp cấu hình để áp dụng sau khi khởi động lại. Cách đầu phù hợp để kiểm tra nhanh; cách thứ hai giúp lưu thiết lập lâu dài.

Bật tạm thời bằng câu lệnh SQL

Đăng nhập MySQL bằng tài khoản có quyền phù hợp, sau đó đặt ngưỡng và bật log:

SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = 'ON';

Trong ví dụ, ngưỡng được đặt là 1 giây. Hãy điều chỉnh theo đặc điểm ứng dụng và yêu cầu giám sát của bạn. Nếu cần chỉ định nơi xuất log, trước tiên kiểm tra giá trị log_output và slow_query_log_file. MySQL có thể ghi log ra tệp hoặc bảng tùy thiết lập; log dạng tệp thường thuận tiện khi cần phân tích bằng công cụ dòng lệnh.

Có thể kiểm tra lại trạng thái sau khi thay đổi:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_output';

Thay đổi bằng SET GLOBAL thường có hiệu lực trong phiên bản máy chủ đang chạy nhưng không nhất thiết được giữ sau khi MySQL khởi động lại. Vì vậy, đây là cách hữu ích để kiểm tra trước, không nên mặc định rằng cấu hình đã được lưu bền vững.

Bật lâu dài bằng tệp cấu hình

Tìm tệp cấu hình MySQL phù hợp với hệ điều hành và cách cài đặt máy chủ. Vị trí tệp có thể khác nhau giữa các bản phân phối. Trong phần [mysqld], thêm hoặc cập nhật các dòng tương tự:

[mysqld]
slow_query_log = ON
long_query_time = 1
log_output = FILE

Có thể cấu hình thêm slow_query_log_file để chỉ định đường dẫn, nhưng tài khoản MySQL cần có quyền ghi vào thư mục đó. Sau khi lưu thay đổi, khởi động lại dịch vụ MySQL theo quy trình quản trị của hệ thống rồi kiểm tra lại các biến bằng câu lệnh SHOW VARIABLES.

Tên dịch vụ và lệnh khởi động lại khác nhau tùy hệ điều hành, môi trường cài đặt hoặc nền tảng quản lý. Nếu đang vận hành dịch vụ có dữ liệu quan trọng, hãy xem xét ảnh hưởng của việc khởi động lại và thực hiện trong thời điểm phù hợp. Không chỉnh sửa tệp cấu hình khi chưa xác định đúng tệp mà máy chủ đang sử dụng.

Cách tìm và đọc truy vấn MySQL chậm

Sau khi bật log, bước tiếp theo là xác định nơi MySQL ghi dữ liệu. Kiểm tra biến slow_query_log_file để biết đường dẫn tệp. Nếu log_output là TABLE, dữ liệu được lưu trong bảng log thay vì tệp văn bản; cần điều chỉnh cách truy xuất cho phù hợp. Trên máy chủ Linux, khi đã có đường dẫn và quyền đọc, bạn có thể xem phần cuối tệp bằng lệnh tail hoặc lọc thông tin bằng công cụ dòng lệnh.

Với tệp lớn, không nên đọc thủ công từ đầu đến cuối. Công cụ mysqldumpslow có sẵn trong nhiều bộ cài MySQL giúp nhóm các truy vấn tương tự và sắp xếp theo tiêu chí. Ví dụ, lệnh dưới đây hiển thị một số nhóm truy vấn có tổng thời gian thực thi cao:

mysqldumpslow -s t -t 10 /duong-dan/mysql-slow.log

Thay /duong-dan/mysql-slow.log bằng đường dẫn thực tế. Tùy chọn -s t sắp xếp theo tổng thời gian, còn -t 10 giới hạn kết quả hiển thị. Nếu muốn ưu tiên truy vấn có thời gian trung bình cao, có thể dùng tiêu chí at. Hãy đọc hướng dẫn của phiên bản công cụ đang cài nếu cần lựa chọn cách sắp xếp khác.

mysqldumpslow thường chuẩn hóa các giá trị trong truy vấn để gom những câu lệnh có cùng dạng vào một nhóm. Vì thế, kết quả giúp nhìn thấy mẫu truy vấn phổ biến, nhưng không nhất thiết giữ nguyên từng giá trị cụ thể. Với nhu cầu phân tích sâu hơn, quản trị viên có thể cân nhắc công cụ như pt-query-digest, sau khi kiểm tra cách cài đặt và sử dụng phù hợp với môi trường.

Khi xem kết quả, hãy ghi lại các thông tin quan trọng: thời lượng, số lần xuất hiện, tổng thời gian tiêu tốn và câu lệnh SQL. Một truy vấn đơn lẻ mất lâu có thể đáng chú ý, nhưng một truy vấn chỉ chậm vừa phải mà chạy lặp lại rất nhiều lần cũng có thể tạo ra tổng tải lớn. Ưu tiên điều tra dựa trên ảnh hưởng tổng thể đến ứng dụng, không chỉ nhìn vào một con số riêng lẻ.

Tìm nguyên nhân và chọn hướng tối ưu

Log cho biết truy vấn nào cần chú ý, nhưng không tự giải thích đầy đủ vì sao truy vấn chậm. Sau khi chọn được câu lệnh cần xem xét, hãy kiểm tra cấu trúc bảng, lượng dữ liệu, điều kiện lọc, cách sắp xếp và tần suất gọi truy vấn. Đối chiếu thời điểm trong log với tình trạng ứng dụng cũng có thể giúp xác định truy vấn chậm có liên quan đến một tác vụ cụ thể hay không.

Dùng EXPLAIN trước truy vấn để xem kế hoạch thực thi. Kết quả có thể cho biết MySQL dự kiến đọc bảng theo cách nào, có sử dụng chỉ mục hay phải kiểm tra nhiều hàng. Với MySQL 8.0.18 trở lên, EXPLAIN ANALYZE có thể thực thi truy vấn và cung cấp thêm thông tin thực tế về quá trình chạy. Chỉ dùng lệnh này khi hiểu rằng truy vấn sẽ được thực thi, đặc biệt thận trọng với câu lệnh có tác động thay đổi dữ liệu.

Một số hướng tối ưu thường cần kiểm tra gồm:

• Bổ sung hoặc điều chỉnh chỉ mục cho cột thường xuất hiện trong điều kiện WHERE, phép nối hoặc sắp xếp. Chỉ mục ghép cần được thiết kế theo mẫu truy vấn; có chỉ mục không đồng nghĩa mọi truy vấn đều sử dụng được.
• Tránh lấy dữ liệu không cần thiết. Thay vì SELECT *, chỉ yêu cầu các cột ứng dụng thực sự sử dụng.
• Xem lại phép nối, điều kiện lọc và phân trang. Phân trang với độ lệch lớn có thể tốn kém; một số hệ thống có thể phù hợp hơn với cách phân trang dựa trên khóa.
• Kiểm tra việc ứng dụng gọi lặp cùng một truy vấn, chẳng hạn mẫu N+1 khi lấy dữ liệu liên quan. Tối ưu cách truy cập có thể giảm số lần truy vấn mà không cần thay đổi máy chủ.
• Kiểm tra khóa và giao dịch nếu thời gian chờ hoặc tình trạng tranh chấp tài nguyên là dấu hiệu nổi bật. Không phải truy vấn chậm nào cũng khắc phục được bằng cách thêm chỉ mục.

Sau mỗi thay đổi, chạy lại truy vấn với dữ liệu và điều kiện phù hợp, so sánh kế hoạch thực thi và theo dõi slow query log. Đồng thời kiểm tra các truy vấn khác bị ảnh hưởng: chỉ mục mới có thể hỗ trợ thao tác đọc nhưng làm tăng chi phí ghi và dung lượng lưu trữ. Nên thử nghiệm trên môi trường phù hợp trước khi áp dụng thay đổi lớn vào hệ thống đang phục vụ người dùng.

Lưu ý khi vận hành slow query log

Slow query log là công cụ chẩn đoán, không phải cơ chế tự tối ưu. Đặt ngưỡng quá thấp có thể tạo ra lượng log lớn; đặt quá cao lại bỏ sót truy vấn cần quan tâm. Hãy lựa chọn ngưỡng theo nhu cầu, theo dõi kích thước tệp và thiết lập quy trình xoay vòng, lưu trữ hoặc xóa log phù hợp chính sách của hệ thống.

Nếu bật log_queries_not_using_indexes, hãy theo dõi kỹ kết quả. Một truy vấn không dùng chỉ mục chưa chắc đã kém hiệu quả; với bảng nhỏ, quét toàn bảng đôi khi là lựa chọn hợp lý. Tránh bật tùy chọn này kéo dài mà không có kế hoạch xử lý log. Ngoài ra, log có thể chứa thông tin truy vấn nhạy cảm, vì vậy cần giới hạn quyền truy cập và bảo vệ tệp như các dữ liệu vận hành khác.

Kết luận

MySQL slow query log giúp quản trị viên ghi nhận truy vấn tốn thời gian, nhưng giá trị lớn nhất nằm ở quy trình tiếp theo: xác định mẫu truy vấn ảnh hưởng nhiều, kiểm tra bằng EXPLAIN, tối ưu có kiểm soát rồi đo lại kết quả. Hãy bắt đầu với ngưỡng phù hợp, xác nhận vị trí lưu log và theo dõi dung lượng để việc chẩn đoán an toàn, hữu ích.

Nếu đã tối ưu truy vấn nhưng vẫn cần rà soát tài nguyên hạ tầng, bạn có thể tìm hiểu các gói VPS AMD EPYC Gen 3 của VibeVPS từ 81.000đ/tháng. Hãy đối chiếu cấu hình thực tế với nhu cầu ứng dụng trước khi lựa chọn.