Slow Query Log — cách bắt lỗi query như thám tử

Slow Query Log là gì

Slow Query Log là tính năng của MySQL/MariaDB/PostgreSQL giúp ghi lại những câu query mất quá nhiều thời gian để chạy. Thay vì đoán mò, bạn xem log để biết query nào chậm, chạy bao lâu và được chạy bao nhiêu lần.

Lợi ích của Slow Query Log

• Biết chính xác câu query nào gây chậm.
• Biết được tần suất query chạy — query chậm + chạy nhiều = thảm họa.
• Kết hợp với EXPLAIN để tối ưu bằng index hoặc rewrite.
• Không phải bật profiling hay trace nặng nề.

Bật Slow Query Log trên MySQL / MariaDB

Mở file config:

sudo nano /etc/mysql/my.cnf

Thêm vào phần:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Giải thích:

• slow_query_log: bật ghi log.
• long_query_time = 1: log query chạy > 1 giây.
• log_queries_not_using_indexes: log mọi query không dùng index.

Restart MySQL:

sudo systemctl restart mysql

Xem log

tail -f /var/log/mysql/mysql-slow.log

Log sẽ hiển thị dạng:

# Query_time: 3.105  Rows_examined: 1200000
SELECT * FROM orders WHERE user_id = 12345;

Cách đọc Slow Query Log để tìm thủ phạm

• Query_time: thời gian chạy query
• Rows_examined: số dòng DB phải quét để trả kết quả

Nếu Rows_examined quá lớn, đó là dấu hiệu thiếu index hoặc filter sai điều kiện.

Sử dụng EXPLAIN để phân tích query chậm

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

Kết quả EXPLAIN sẽ cho biết:

• Có dùng index không
• Loại scan (index / range / ALL)
• Dự đoán số dòng đọc

Nếu type là ALL → full table scan → chuẩn bị thêm index.

Thêm INDEX để tăng tốc ngay

Ví dụ query chậm:

SELECT * FROM orders WHERE user_id = 12345 AND created_at >= '2025-01-01';

Cách khắc phục:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

Giảm từ đọc cả triệu dòng → chỉ đọc đúng block dữ liệu.

Phân tích Slow Query Log bằng công cụ mysqldumpslow

MySQL tích hợp sẵn tool để gom thống kê:

mysqldumpslow /var/log/mysql/mysql-slow.log

Hoặc lọc theo query chạy lâu nhất:

mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Lọc theo query chạy nhiều nhất:

mysqldumpslow -s c /var/log/mysql/mysql-slow.log

Bonus: Dùng pt-query-digest (Percona Toolkit) — phân tích như chuyên gia

pt-query-digest /var/log/mysql/mysql-slow.log

Tool này nhóm các query giống nhau, hiển thị tổng thời gian tiêu tốn và tần suất chạy.

Quy trình tối ưu query chậm

1. Bật slow query log
2. Xác định query chậm và tần suất
3. Dùng EXPLAIN để xem access plan
4. Thêm hoặc chỉnh index, rewrite query
5. Chạy lại và so sánh

Kết luận

Slow Query Log là công cụ “thám tử database” mạnh mẽ. Chỉ cần bật log, xem query nào chậm và tối ưu bằng index đúng chỗ, database sẽ nhanh hơn ngay lập tức mà không cần nâng cấp phần cứng. Tối ưu trước, scale sau.