MySQL slow query log: bật, đọc và tối ưu query Laravel

MySQL slow query log là công cụ chẩn đoán để tìm các câu SQL mất nhiều thời gian hoặc quét quá nhiều dữ liệu. Trong ứng dụng Laravel, slow log giúp nối vấn đề người dùng cảm nhận như trang danh sách chậm, API timeout hoặc queue xử lý lâu với execution plan và câu query cụ thể. Nhưng bật log không đồng nghĩa truy vấn đã được tối ưu; cần một quy trình đo, phân tích, sửa và đo lại.

Bài viết này hướng dẫn bật slow query log có kiểm soát, đặt `long_query_time`, giới hạn truy vấn không dùng index, đọc và nhóm log, liên kết với Laravel Query Builder, dùng `EXPLAIN`, kiểm tra N+1 và tắt hoặc giảm logging sau khi tìm được nguyên nhân. Các ví dụ ưu tiên MySQL 8.x; hãy kiểm tra quyền và cấu hình thực tế trước khi áp dụng production [1].

Nguyên tắc tối ưu: đừng chọn index chỉ vì một câu query trông chậm. Hãy đo baseline, đọc execution plan, kiểm tra độ chọn lọc và workload thực tế, sau đó đo lại cùng điều kiện để biết thay đổi có cải thiện p95 hay chỉ làm một truy vấn riêng lẻ nhanh hơn.

1. Slow query log ghi gì?

Slow query log ghi các câu SQL có thời gian thực thi lớn hơn `long_query_time` và đáp ứng điều kiện số dòng đã examined theo `min_examined_row_limit`. Log có thể ghi thời điểm, user/host, thời gian query, thời gian lock, số rows sent, số rows examined và câu SQL. Những số liệu này giúp phân biệt query chậm do lock, do scan nhiều dòng hay do trả quá nhiều kết quả.

Biến	Vai trò	Lưu ý production
slow_query_log	Bật/tắt slow log	Chỉ bật khi có kế hoạch đọc và giới hạn dung lượng
slow_query_log_file	Đường dẫn file log	Kiểm tra ownership, disk space và rotation
long_query_time	Ngưỡng thời gian	Có thể dùng microsecond; chọn theo p95/workload
min_examined_row_limit	Ngưỡng rows examined	Giảm nhiễu từ query nhỏ nhưng vẫn cần hiểu workload
log_queries_not_using_indexes	Ghi query không dùng index	Có thể tạo log rất lớn
log_output	FILE/TABLE/NONE	Xác định nơi dữ liệu được ghi

2. Kiểm tra cấu hình trước khi bật

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';
SHOW VARIABLES LIKE 'datadir';

Đọc kết quả trước khi thay đổi. Nếu `log_output` là `TABLE`, dữ liệu nằm trong bảng log của schema `mysql`; nếu là `FILE`, MySQL dùng đường dẫn file tương ứng. Trên shared hosting hoặc managed database, bạn có thể không có quyền `SYSTEM_VARIABLES_ADMIN` hoặc quyền đọc file; khi đó cần dùng dashboard/provider hoặc gửi yêu cầu vận hành thay vì tìm cách vượt quyền.

3. Bật slow log tạm thời với ngưỡng phù hợp

Trong một phiên chẩn đoán có kiểm soát, có thể đặt ngưỡng cao hơn mức mặc định 10 giây xuống một giá trị phù hợp với ứng dụng, ví dụ 0.5 hoặc 1 giây. Không nên chọn giá trị quá thấp trên production traffic lớn vì log sẽ tăng nhanh và chính hoạt động ghi log có thể tạo thêm I/O.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.000000;
SET GLOBAL min_examined_row_limit = 1000;
-- Chỉ bật khi đã chấp nhận lượng log tăng và có kế hoạch tắt
SET GLOBAL log_queries_not_using_indexes = 'OFF';
SHOW VARIABLES LIKE 'slow_query%';

Các thay đổi runtime có thể mất sau khi restart nếu chưa được ghi vào cấu hình server; ngược lại, chỉnh file cấu hình rồi restart có thể ảnh hưởng toàn bộ workload. Hãy ghi lại giá trị cũ, thời gian bật, người thực hiện và kế hoạch rollback. Không bật `log_queries_not_using_indexes` trên toàn bộ traffic trong thời gian dài chỉ vì một query không có index; nhiều query hợp lệ vẫn có thể không dùng index hiệu quả hơn.

4. Đọc file và kiểm tra dung lượng

sudo ls -lh /var/log/mysql/
sudo tail -n 100 /var/log/mysql/mysql-slow.log
sudo du -sh /var/log/mysql
# tìm các query lớn theo thời gian xử lý trong file đã copy để phân tích offline
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log
mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log

Tên file phụ thuộc distro và `slow_query_log_file`; không nên đoán đường dẫn rồi kết luận log không hoạt động. Dùng `SHOW VARIABLES` để lấy path và kiểm tra quyền đọc. Khi cần gửi log cho người khác, hãy lọc hoặc ẩn dữ liệu nhạy cảm, token, email và tham số chứa thông tin cá nhân.

5. Liên kết slow log với Laravel

Laravel Query Builder dùng PDO parameter binding cho giá trị, nhưng tên cột hoặc thứ tự sort không nên lấy trực tiếp từ input người dùng. Khi truy vấn xuất hiện trong slow log, hãy tìm vị trí tạo query bằng code path, request ID hoặc log channel. Laravel logging có thể dùng channel daily/stack để ghi route, job hoặc command; không nên log toàn bộ payload nhạy cảm. Với tác vụ nền, hãy xem thêm bài Laravel queue với Supervisor để tách latency database khỏi worker.

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

$started = microtime(true);
$orders = DB::table('orders')
    ->select(['id', 'user_id', 'status', 'created_at'])
    ->where('user_id', $userId)
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->limit(50)
    ->get();

Log::channel('daily')->info('orders query completed', [
    'elapsed_ms' => round((microtime(true) - $started) * 1000, 2),
    'count' => $orders->count(),
]);

Đo ở application giúp biết latency mà người dùng thấy, còn slow log cho biết MySQL đã mất bao lâu và examined bao nhiêu dòng. Hai con số không nhất thiết giống nhau vì còn network, PHP serialization, lock ngoài query hoặc thời gian chờ connection. Hãy dùng chúng cùng nhau thay vì thay thế lẫn nhau.

6. Đọc một slow query và đặt giả thuyết

Bắt đầu bằng câu hỏi: query có scan quá nhiều dòng không, có sort hoặc temporary table không, có chờ lock không, có trả quá nhiều cột/dòng không, và filter có chọn lọc đủ để index hữu ích không? `Rows_examined` cao hơn nhiều lần `Rows_sent` thường là tín hiệu cần xem execution plan, nhưng không phải mọi trường hợp đều sửa được bằng một index mới.

EXPLAIN
SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE
SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Đọc `type`, `possible_keys`, `key`, `rows`, `filtered` và `Extra`; với `EXPLAIN ANALYZE`, đối chiếu estimated rows với actual timing/rows. Chạy trên môi trường an toàn hoặc giờ thấp tải nếu câu query đọc bảng lớn. Không chạy `EXPLAIN ANALYZE` cho một lệnh ghi production nếu chưa hiểu tác động của statement.

7. Tối ưu Query Builder theo thứ tự ưu tiên

Vấn đề	Hướng xử lý	Cách đo lại
SELECT *	Chọn các cột thật sự cần	Rows sent và thời gian serialize
Không có filter phù hợp	Xem index và độ chọn lọc	EXPLAIN/EXPLAIN ANALYZE
N+1 queries	Eager load hoặc gom truy vấn	Query count/request time
OFFSET lớn	Keyset pagination theo khóa ổn định	Latency trang sâu
Sort ngoài index	Đối chiếu WHERE + ORDER BY	Extra và filesort
Query lặp lại	Cache có TTL và invalidation rõ	p95 sau warm/cold cache

Index phải phục vụ workload thật. Composite index cần cân nhắc thứ tự cột theo điều kiện lọc và sort, kích thước index, cardinality và chi phí ghi. Sau khi thêm index, đo cả query đọc lẫn insert/update; một index quá nhiều có thể làm write chậm và tốn storage.

8. Kiểm tra N+1 và query trong queue

Một endpoint có thể không có câu query đơn lẻ vượt ngưỡng nhưng tổng số query khiến request chậm. Với Eloquent, hãy quan sát số query khi lặp quan hệ và cân nhắc `with()`, `load()` hoặc truy vấn tổng hợp. Queue job cũng cần được đo riêng vì slow log có thể ghi nhiều query ngoài request web.

$users = User::query()
    ->with(['roles:id,name'])
    ->select(['id', 'name'])
    ->where('active', true)
    ->limit(100)
    ->get();

foreach ($users as $user) {
    // Không gọi thêm một query role cho mỗi user nếu đã eager load.
    $roleNames = $user->roles->pluck('name');
}

Đừng biến mọi truy vấn thành eager load lớn. Eager loading quan hệ có cardinality cao có thể tạo result set lớn; hãy chọn cột, giới hạn dữ liệu và đo memory. Với report, có thể dùng aggregate hoặc query chuyên dụng thay vì tải toàn bộ model vào PHP.

9. Tắt hoặc giới hạn slow log sau chẩn đoán

Sau khi đã có mẫu query và kế hoạch sửa, giảm phạm vi logging. Có thể tắt slow log, đưa `long_query_time` về giá trị vận hành, tắt log query không dùng index hoặc cấu hình rotation/retention. Ghi lại trạng thái cuối cùng để người trực ca sau không tưởng rằng log vẫn đang thu thập dữ liệu.

SET GLOBAL log_queries_not_using_indexes = 'OFF';
SET GLOBAL slow_query_log = 'OFF';
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Nếu cần giữ log ở mức giám sát, hãy dùng ngưỡng hợp lý và hệ thống thu thập có quota. Log không được phép làm đầy disk hoặc chứa dữ liệu nhạy cảm không cần thiết. Với managed MySQL, dùng tính năng slow query log của nhà cung cấp nếu quyền SQL bị giới hạn.

10. Checklist xử lý một query chậm

Ghi nhận endpoint/job, thời gian, p95 và điều kiện tái hiện.
Kiểm tra slow log đang ghi đúng file/table và không làm đầy disk.
Đọc thời gian query, lock time, rows sent và rows examined.
Tìm code Laravel tạo query và loại bỏ input không an toàn cho tên cột.
Chạy EXPLAIN hoặc EXPLAIN ANALYZE trong môi trường phù hợp.
Đưa ra một giả thuyết tối ưu: index, select, join, pagination, eager load hoặc cache.
Đo lại cùng dataset và tải gần tương đương, so sánh p50/p95 và tài nguyên.
Ghi rollback và giảm/tắt logging sau khi hoàn tất chẩn đoán.

11. Kết luận

Slow query log hiệu quả nhất khi được dùng như một phần của vòng lặp quan sát: bật có ngưỡng, thu thập vừa đủ, nhóm mẫu, kiểm tra execution plan, sửa một giả thuyết và đo lại. Trong Laravel, tối ưu database phải đi cùng thiết kế query, phân trang, eager loading, queue và logging. Đừng dùng một index hoặc một cấu hình log để chữa mọi loại latency.