Trả lời nhanh: Chẩn MySQL chậm theo chuỗi 4 bước: bật slow query log bắt đúng thủ phạm → chạy EXPLAIN từng query xem có full table scan (type=ALL, rows khổng lồ) → thiếu index thì thêm index (chữa được đa số ca) → query đã tối ưu mà vẫn ì thì nhìn xuống tầng máy: buffer pool quá nhỏ so với dữ liệu, hoặc ổ đĩa yếu khiến mỗi lần trượt cache thành một lần chờ. Đừng đảo thứ tự: chỉnh tham số trước khi soi query là dán cao lên chỗ không đau.
"MySQL chậm" là triệu chứng, không phải bệnh, bệnh có thể ở query, index, RAM, ổ đĩa hay chính hàng xóm trên máy chủ. Bài này là quy trình chẩn theo thứ tự loại trừ, dùng toàn công cụ có sẵn trong MySQL, mỗi bước kèm lệnh dán được và cách đọc kết quả.
- Bước 1: slow query log, biết chính xác query nào ăn thời gian, đừng đoán
- Bước 2: EXPLAIN, bắt full scan (type=ALL), index thiếu hiện nguyên hình
- Bước 3: thêm index cho cột trong WHERE/JOIN/ORDER BY, thuốc chữa 70% ca
- Bước 4: tầng máy, innodb_buffer_pool_size theo RAM, ổ NVMe cho phần trượt cache
Bước 1: Bắt thủ phạm bằng slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- ghi query cham hon 0.5s
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';Chạy site vài giờ tải thật rồi mở file log: danh sách query chậm xếp sẵn kèm thời gian và số dòng quét. Quy tắc vàng của cả nghề tối ưu: sửa theo bằng chứng trong log, không sửa theo linh cảm. Rất thường xuyên, thủ phạm là một query không ai ngờ, cái widget "bài viết liên quan" quét cả bảng chẳng hạn.
Bước 2: EXPLAIN - đọc 3 cột là ra bệnh
EXPLAIN SELECT * FROM orders WHERE customer_phone = '0909...' ORDER BY created_at DESC LIMIT 20;Ba cột cần nhìn:
- type: ALL = quét cả bảng (báo động đỏ); ref/range = đang dùng index (tốt); const/eq_ref = tuyệt.
- rows: ước lượng số dòng phải đọc, bảng 2 triệu dòng mà rows ~2 triệu cho một kết quả 20 dòng là bức tranh tự nói.
- Extra: "Using filesort" / "Using temporary" = sort/gom nhóm không nhờ được index, nặng thêm một tầng, và khi dữ liệu lớn sẽ tràn ra ổ đĩa.
Bước 3: Index - thuốc đúng cho đa số ca
ALTER TABLE orders ADD INDEX idx_phone_created (customer_phone, created_at);Nguyên tắc đặt index gọn: cột trong WHERE đứng trước, cột ORDER BY đứng sau (index kép trên chữa cả lọc lẫn sắp xếp một phát); cột JOIN hai bảng đều nên có index; đừng index tràn lan, mỗi index là thuế phải trả khi ghi. Thêm xong chạy lại EXPLAIN xác nhận type đổi từ ALL sang ref/range, rồi nhìn thời gian thật trong slow log giảm, vòng lặp bằng chứng khép kín.
Bước 4: Tầng máy - khi query đã sạch mà vẫn ì
- Buffer pool, RAM của InnoDB:
innodb_buffer_pool_sizemặc định 128MB là trò đùa với database vài GB. Máy dành cho database: đặt 50-60% RAM. Kiểm tra tỷ lệ trượt:SHOW ENGINE INNODB STATUSphần Buffer pool hit rate, dưới 99% là dữ liệu nóng không vừa RAM, mỗi lần trượt là một lượt xuống ổ. - Ổ đĩa, nơi mọi cú trượt hạ cánh: đọc ngẫu nhiên khi cache miss + fsync mỗi commit (
innodb_flush_log_at_trx_commit=1mặc định an toàn). Ổ SATA/network storage biến mỗi cú trượt thành nhiều mili-giây; NVMe đưa về dưới 1ms, cùng một query, khác hẳn cảm giác giờ đông khách. Đo nền bằng fio nếu nghi ngờ. - Hàng xóm: %st trong top cao giờ vàng, bệnh oversell, xem bài CPU steal time.
Bốn lệnh để bắt truy vấn chậm
# 1. Bat ghi truy van cham (khong can khoi dong lai) SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; # 2. Sau vai gio, tong hop file log mysqldumpslow -s t -t 20 /var/log/mysql/slow.log # 3. Xem truy van dang chay ngay luc nay SHOW FULL PROCESSLIST; # 4. Bang nao bi quet toan bo nhieu nhat SELECT object_schema, object_name, count_read, sum_timer_wait FROM performance_schema.table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC LIMIT 10;
Bước 2 quan trọng hơn bước 3. Truy vấn giết máy chủ thường không phải truy vấn chạy 30 giây một lần, mà là truy vấn chạy 0,3 giây nhưng gọi mười nghìn lần mỗi phút.
Đọc kết quả phân tích truy vấn
| Bạn thấy | Nghĩa là | Việc cần làm |
|---|---|---|
| type = ALL | Quét toàn bộ bảng | Thêm chỉ mục cho cột trong điều kiện lọc |
| type = index | Quét toàn bộ chỉ mục, đỡ hơn nhưng vẫn chậm | Chỉ mục chưa khớp thứ tự cột trong truy vấn |
| type = ref hoặc range | Đang dùng chỉ mục đúng cách | Ổn |
| rows rất lớn so với số dòng thực trả về | Đang đọc thừa rất nhiều | Lọc sớm hơn, hoặc thêm chỉ mục ghép nhiều cột |
| Using filesort | Đang sắp xếp ngoài chỉ mục | Thêm cột sắp xếp vào cuối chỉ mục ghép |
| Using temporary | Đang tạo bảng tạm | Thường do gộp nhóm; xem lại cách gom hoặc thêm chỉ mục |
Khi truy vấn đã sạch mà vẫn chậm
- Kiểm vùng đệm có đủ không. Vùng đệm nhỏ hơn phần dữ liệu hay đọc thì mỗi truy vấn phải xuống ổ đĩa. Đây là tham số đáng chỉnh đầu tiên.
- Kiểm ổ đĩa bằng số, không bằng cảm giác: chạy
iostat -x 2, cột %util gần 100 là nghẽn thật. - Kiểm số kết nối đồng thời. Quá nhiều kết nối cùng lúc làm mọi truy vấn chậm đều nhau, và triệu chứng này dễ bị nhầm với truy vấn xấu.
- Kiểm khóa bảng: một giao dịch dài giữ khóa làm hàng loạt truy vấn khác xếp hàng chờ.
Nếu ổ đĩa là điểm nghẽn thì mọi tinh chỉnh truy vấn chỉ mua thêm thời gian. Xem IOPS là gì và trang VPS NVMe.
Câu hỏi thường gặp
WordPress của tôi chậm có phải do MySQL không?
Thường có phần: bật slow log là biết ngay plugin nào bắn query quét bảng. Bảng wp_postmeta phình và autoload options nặng là hai nghi phạm kinh điển, cả hai đều lộ diện trong log.
Thêm index có rủi ro gì không?
Ghi chậm đi một chút mỗi index và tốn thêm dung lượng, đổi lấy đọc nhanh gấp trăm lần thường rất hời. Trên bảng cực lớn, tạo index nên chạy giờ thấp điểm vì có thể khóa/nặng máy lúc build.
OPTIMIZE TABLE có giúp gì không?
Có ích sau khi xóa lượng lớn dữ liệu (thu hồi chỗ, gọn index). Không phải thuốc bổ định kỳ, chạy hàng tuần theo lời đồn chỉ tốn I/O vô ích.
Bao giờ thì nâng máy thay vì tối ưu tiếp?
Khi slow log đã sạch query xấu, buffer pool hit rate vẫn dưới 99% dù đã tăng hết cỡ RAM hiện có, nghĩa là dữ liệu nóng thật sự lớn hơn máy. Lúc đó thêm RAM/nâng ổ là đầu tư đúng, không phải đầu hàng.
