Triệu chứng
Query trông đơn giản nhưng chạy 8–30 giây:
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
Bảng có 2 triệu dòng, đã có index trên status. EXPLAIN vẫn trả về Using filesort. Thêm index trên created_at cũng không giúp được gì.
Tại sao ORDER BY + LIMIT lại chậm
MySQL phải sort trước khi cắt
Khi không có index phù hợp cho cả WHERE lẫn ORDER BY, MySQL phải:
- Scan toàn bộ rows thỏa
WHERE status = 'pending'— có thể hàng trăm nghìn dòng - Sort kết quả đó theo
created_at DESC— filesort trong memory hoặc trên disk - Lấy 20 dòng đầu
LIMIT 20 không giúp ích gì nếu MySQL không thể dừng sớm — nó vẫn phải sort xong toàn bộ tập dữ liệu trước.
Filesort là gì
Using filesort trong EXPLAIN không có nghĩa là sort ra file. Đây là thuật ngữ MySQL dùng cho mọi sort không thể dùng index — có thể chạy trong sort_buffer_size (RAM), hoặc spill ra disk nếu tập quá lớn.
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
id | type | key | rows | Extra
1 | ref | idx_st | 450000 | Using index condition; Using filesort
rows: 450000 — MySQL estimate phải xử lý 450k dòng trước khi sort và cắt LIMIT.
Nguyên nhân thường gặp
1. WHERE và ORDER BY dùng cột khác nhau
-- WHERE trên status, ORDER BY trên created_at
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;
Index trên (status) giúp filter, nhưng sau khi filter xong kết quả không có thứ tự theo created_at → phải filesort.
Index trên (created_at) giúp sort, nhưng MySQL có thể không dùng nó vì đánh giá cost của việc scan range theo created_at rồi filter status cao hơn cost filesort.
2. Offset lớn trong pagination
-- Trang 5000, mỗi trang 20 dòng
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
MySQL phải đọc 100.020 dòng, bỏ 100.000 dòng đầu, giữ lại 20. Offset càng lớn, query càng chậm — dù index có được dùng.
3. ORDER BY trên expression hoặc function
-- Sort theo biểu thức → không dùng được index
SELECT * FROM orders ORDER BY DATE(created_at) DESC LIMIT 20;
SELECT * FROM products ORDER BY price * 0.9 DESC LIMIT 20;
Index trên created_at không apply được cho DATE(created_at).
4. SELECT * kéo quá nhiều data
Với covering index, MySQL có thể hoàn toàn xử lý trong index mà không cần đọc row data. Nhưng SELECT * phá vỡ điều này — MySQL buộc phải truy cập table để lấy hết các cột.
Cách kiểm tra
Đọc EXPLAIN đúng chỗ
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20\G
Những gì cần chú ý:
| Field | Dấu hiệu xấu | Dấu hiệu tốt |
|---|---|---|
type |
ALL, index |
ref, range, eq_ref |
rows |
Số lớn (> 10.000) | Số nhỏ gần với LIMIT |
Extra |
Using filesort, Using temporary |
Using index |
key |
NULL |
Tên index cụ thể |
EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
Trả về actual rows và thời gian thực tế — hữu ích hơn EXPLAIN thuần vì cho biết estimate có sát thực tế không.
Cách tối ưu
1. Composite index bao gồm cả WHERE và ORDER BY
Tạo index (status, created_at):
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
Giờ MySQL có thể:
- Dùng
statusđể vào đúng partition trong index - Dùng
created_atđã được sort sẵn trong index để trả về 20 dòng đầu mà không cần filesort
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
-- Extra: Using index condition ← không còn filesort
-- rows: 20 ← chỉ đọc đúng 20 dòng
Thứ tự column trong composite index quan trọng: cột dùng trong WHERE với điều kiện equality (=) đặt trước, cột dùng trong ORDER BY đặt sau.
-- Đúng: equality trước, range/sort sau
INDEX (status, created_at)
-- Sai thứ tự: không tận dụng được cho sort
INDEX (created_at, status)
2. Cursor-based pagination thay OFFSET
Thay vì:
-- Chậm khi offset lớn
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
Dùng cursor (last seen value):
-- Lần đầu
SELECT id, created_at, status FROM orders
ORDER BY created_at DESC
LIMIT 20;
-- Lần sau: truyền vào created_at của dòng cuối cùng vừa lấy
SELECT id, created_at, status FROM orders
WHERE created_at < '2026-05-10 14:30:00' -- cursor từ trang trước
ORDER BY created_at DESC
LIMIT 20;
MySQL sẽ dùng index trên created_at, seek đến đúng vị trí, và đọc đúng 20 dòng — không cần skip bất kỳ row nào.
Lưu ý: cursor pagination chỉ phù hợp khi không cần nhảy đến trang tùy ý. Với admin dashboard cần "đến trang 500", cần dùng cách khác.
3. Deferred join cho OFFSET lớn
Khi buộc phải dùng OFFSET, giảm data đọc bằng cách chỉ lấy id trước, join lại sau:
-- Chậm: đọc toàn bộ row rồi bỏ đi
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- Nhanh hơn: chỉ scan index để lấy id, rồi mới fetch row
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000
) sub ON o.id = sub.id;
Subquery chỉ đọc index (covering), không cần truy cập row data. JOIN bên ngoài chỉ fetch 20 row cụ thể. Với bảng có nhiều cột, cách này nhanh hơn đáng kể.
4. Covering index
Nếu query chỉ cần một số cột nhất định, tạo index bao gồm tất cả các cột đó:
-- Query
SELECT id, status, created_at, total FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
-- Covering index chứa đủ tất cả cột trong query
ALTER TABLE orders ADD INDEX idx_covering (status, created_at, id, total);
MySQL xử lý hoàn toàn trong index tree, không cần đọc row data (Extra: Using index). Nhanh hơn đặc biệt với bảng nhiều cột hoặc row size lớn.
Khi optimizer chọn sai index
Đôi khi MySQL có đủ index nhưng vẫn chọn sai — estimate cost không chính xác.
-- Force dùng index cụ thể để kiểm tra
SELECT * FROM orders USE INDEX (idx_status_created)
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
Nếu query nhanh hơn khi force index, có thể thống kê đang lỗi thời:
ANALYZE TABLE orders;
ANALYZE TABLE cập nhật lại index statistics, giúp optimizer estimate chính xác hơn.
Checklist tối ưu ORDER BY + LIMIT
EXPLAINcònUsing filesortkhông → cần composite indexrowstrong EXPLAIN gần LIMIT hay xa LIMIT → gần là tốt- OFFSET có lớn không → cân nhắc cursor pagination
SELECT *có cần thiết không → chỉ lấy cột cần, tạo covering index- Có sort theo expression/function không → bỏ function ra ngoài hoặc dùng generated column
- Đã
ANALYZE TABLEgần đây chưa → index stats có thể lỗi thời sau insert/delete nhiều