Ứng dụng của bạn đang chậm, và bạn nghi ngờ thủ phạm là database?
Đây là tình huống mà hầu hết developer đều từng đối mặt. Người dùng phàn nàn một chức năng nào đó chạy “chậm như rùa”. Sau khi kiểm tra code application không thấy gì bất thường, mọi ánh mắt nghi ngờ đều đổ dồn về database. Nhưng làm sao để biết chính xác truy vấn SQL nào đang gây tắc nghẽn? Thay vì đoán mò, MySQL cung cấp một công cụ cực kỳ lợi hại: Slow Query Log.
Công cụ này ghi lại tất cả những “kẻ ngáng đường” – các lệnh SQL vượt quá ngưỡng thời gian thực thi do bạn thiết lập. Việc tìm ra chúng là bước đầu tiên và quan trọng nhất để tối ưu hóa hiệu năng database.
Nắm vững các khái niệm cốt lõi
MySQL Slow Query Log là gì?
Hiểu đơn giản, Slow Query Log là một file văn bản. MySQL server dùng nó để ghi lại thông tin về các truy vấn SQL có thời gian thực thi lâu hơn giá trị long_query_time (tính bằng giây). Nó không chỉ ghi lại câu lệnh. File log còn chứa các thông tin giá trị khác như thời gian chạy, số hàng đã quét… giúp bạn chẩn đoán vấn đề chính xác hơn.
Khi nào nên bật Slow Query Log?
Việc ghi log liên tục, đặc biệt với hệ thống có traffic cao, sẽ tiêu tốn tài nguyên I/O và có thể ảnh hưởng nhẹ đến hiệu năng. Do đó, có 2 kịch bản sử dụng phổ biến nhất:
- Môi trường development và testing: Luôn bật. Việc này giúp bạn phát hiện các truy vấn có vấn đề ngay từ giai đoạn phát triển.
- Môi trường production: Chỉ bật khi có báo cáo về hiệu năng chậm, hoặc bật định kỳ (ví dụ: vài giờ mỗi tuần) để “khám sức khỏe” hệ thống. Sau khi tìm và khắc phục sự cố, bạn nên tắt nó đi để tiết kiệm tài nguyên.
Hướng dẫn thực hành chi tiết từ A đến Z
Hãy cùng đi vào phần chính: cách cấu hình, đọc log và tối ưu hóa.
1. Cấu hình Slow Query Log
Đầu tiên, hãy kết nối vào MySQL server và kiểm tra các thiết lập hiện tại.
Kiểm tra trạng thái
Chạy lệnh sau trong MySQL client:
SHOW VARIABLES LIKE '%slow_query_log%';Bạn sẽ thấy kết quả tương tự thế này:
+---------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/your-hostname-slow.log |
+---------------------+--------------------------------------------------+Tiếp theo, kiểm tra ngưỡng thời gian mặc định:
SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+Trong ví dụ trên, log đang tắt (OFF) và ngưỡng thời gian là 10 giây. Đây là một ngưỡng rất cao. Một truy vấn mất 9 giây để tải trang sản phẩm là không thể chấp nhận được, nhưng sẽ không bị ghi lại với cấu hình này. Chúng ta cần hạ thấp nó.
Bật Slow Query Log (tạm thời)
Cách này rất tiện để chẩn đoán nhanh mà không cần khởi động lại server. Tuy nhiên, các thiết lập sẽ mất khi MySQL restart.
SET GLOBAL slow_query_log = 'ON';
-- Đặt ngưỡng là 1 giây, một giá trị hợp lý để bắt đầu
SET GLOBAL long_query_time = 1;Cấu hình vĩnh viễn trong file my.cnf
Để thiết lập có hiệu lực sau mỗi lần khởi động lại, bạn cần chỉnh sửa file cấu hình của MySQL. Vị trí file này có thể khác nhau tùy hệ điều hành:
- Ubuntu/Debian:
/etc/mysql/my.cnfhoặc/etc/mysql/mysql.conf.d/mysqld.cnf - CentOS/RHEL:
/etc/my.cnf
Mở file cấu hình và thêm (hoặc sửa) các dòng sau vào dưới section [mysqld]:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
# (Tùy chọn nhưng rất hữu ích) Ghi lại các truy vấn không sử dụng index
log_queries_not_using_indexes = 1Lưu ý quan trọng: Đảm bảo thư mục /var/log/mysql/ tồn tại và user mysql có quyền ghi vào đó. Sau khi lưu file, bạn cần khởi động lại dịch vụ MySQL để áp dụng thay đổi:
sudo systemctl restart mysql2. Phân tích file log
Sau khi hệ thống chạy một thời gian, file mysql-slow.log sẽ bắt đầu ghi nhận các truy vấn chậm. Bạn có thể xem trực tiếp hoặc dùng công cụ để phân tích.
Đọc file log trực tiếp
Một entry trong file log sẽ có cấu trúc như sau:
# Time: 2023-10-27T10:15:30.123456Z
# User@Host: root[root] @ localhost [] Id: 123
# Query_time: 2.123 Lock_time: 0.000 Rows_sent: 5 Rows_examined: 50000
SET timestamp=1698398130;
SELECT p.post_title, u.display_name
FROM wp_posts p
JOIN wp_users u ON p.post_author = u.ID
WHERE p.post_status = 'publish' AND u.user_email LIKE '%@gmail.com';Các thông tin quan trọng cần chú ý:
- Query_time: Thời gian thực thi truy vấn (2.123 giây).
- Rows_examined: Số hàng mà database đã phải quét qua (50,000 hàng). Đây là chỉ số cực kỳ quan trọng. Tỷ lệ giữa
Rows_examinedvàRows_sent(số hàng trả về) càng cao, truy vấn càng kém hiệu quả. Tưởng tượng bạn phải đọc 50,000 dòng trong một file Excel chỉ để tìm ra 5 dòng thỏa mãn điều kiện. - Câu lệnh SQL cụ thể đã gây ra sự chậm trễ.
Sử dụng công cụ `mysqldumpslow`
Khi file log phình to, đọc thủ công là không thể. `mysqldumpslow` là một tiện ích đi kèm MySQL giúp bạn tổng hợp và sắp xếp các truy vấn chậm.
Một vài lệnh hữu ích:
# Liệt kê 10 truy vấn tốn nhiều thời gian thực thi nhất
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# Liệt kê 10 truy vấn phải quét nhiều hàng nhất
sudo mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log
# Liệt kê 10 truy vấn được chạy nhiều lần nhất (gây chậm lặp đi lặp lại)
sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.logOutput của `mysqldumpslow` sẽ gom nhóm các câu lệnh tương tự nhau, thay thế các giá trị cụ thể bằng ‘S’ (string) và ‘N’ (number). Điều này giúp bạn xác định được *loại* truy vấn nào đang là vấn đề chính, thay vì các truy vấn đơn lẻ.
3. Tối ưu truy vấn chậm
Sau khi đã xác định “thủ phạm” từ slow log, bước tiếp theo là “mổ xẻ” nó bằng lệnh EXPLAIN.
Phân tích bằng EXPLAIN
Giả sử chúng ta tìm được câu lệnh sau trong log:
SELECT * FROM products WHERE category_name = 'Điện thoại';Hãy đặt từ khóa EXPLAIN ở trước nó và chạy trong MySQL client:
EXPLAIN SELECT * FROM products WHERE category_name = 'Điện thoại';MySQL sẽ trả về kế hoạch thực thi của nó. Hãy đặc biệt chú ý đến cột type. Nếu giá trị là ALL, đó là một dấu hiệu rất xấu. Nó có nghĩa là MySQL đang phải quét toàn bộ bảng (full table scan). Cột rows sẽ cho bạn thấy con số ước tính, thường là rất lớn.
Thêm Index để tăng tốc
Khi thấy một full table scan trên mệnh đề WHERE, giải pháp hiệu quả nhất thường là thêm index vào cột được dùng để lọc.
CREATE INDEX idx_products_category_name ON products(category_name);Đây là một ví dụ thực tế. Trên một database production khoảng 50GB, một query tìm kiếm sản phẩm mất tới 4.5 giây do phải quét hàng triệu bản ghi. Sau khi phân tích slow log và thêm một composite index hợp lý, thời gian thực thi đã giảm xuống chỉ còn dưới 200 mili giây.
Kiểm tra lại với EXPLAIN
Sau khi thêm index, hãy chạy lại lệnh EXPLAIN. Rất có thể bạn sẽ thấy một sự cải thiện rõ rệt: cột type chuyển thành ref, cột key hiển thị tên index bạn vừa tạo, và quan trọng nhất, giá trị ở cột rows giảm đáng kể. Điều này chứng tỏ truy vấn đã sử dụng index để đi thẳng đến dữ liệu cần tìm, thay vì phải “dò” toàn bộ bảng.
Kết luận
Tối ưu hóa database không phải là phép thuật, mà là một quy trình có phương pháp. Slow Query Log giúp bạn biến một vấn đề mơ hồ (“app chạy chậm”) thành một danh sách các công việc cụ thể. Quy trình chuẩn thường là: Bật log để thu thập dữ liệu -> Dùng mysqldumpslow để phân tích và tìm truy vấn “nặng” nhất -> Dùng EXPLAIN để hiểu nguyên nhân -> Áp dụng các kỹ thuật tối ưu như thêm index. Đây là một kỹ năng nền tảng và thiết yếu mà bất kỳ developer nào làm việc với database cũng nên thành thạo.
