MySQL: Thiết kế index ghép cho WHERE + ORDER BY + LIMIT dựa trên EXPLAIN ANALYZE ra sao?

Chào bạn Linh Giang, Không nên tạo index theo kiểu mỗi cột một index rồi hy vọng optimizer ghép lại đúng ý. Hãy bắt đầu từ truy vấn thực, tần suất chạy, cardinality, số hàng đọc và thứ tự cần trả về. Với mẫu lọc bằng tenant_id/status rồi sắp xếp theo created_at/id, một index ghép thường hiệu quả hơn các index rời khi nó phản ánh chính access pattern đó. Quy trình nên là: 1. Lấy câu SQL thật với dữ liệu/parameter đại diện, không tối ưu từ ví dụ nhỏ trên máy local. 2. Dùng EXPLAIN ANALYZE để so sánh số hàng/cost ước tính với số hàng/timing thực tế; chú ý iterator có loops cao hoặc phải đọc quá nhiều hàng. 3. Xem WHERE, JOIN và ORDER BY như một tổng thể. Thứ tự các cột trong index phải phục vụ điều kiện lọc và thứ tự truy xuất thực tế, không chỉ tên cột xuất hiện trong câu SQL. 4. Thêm một index giả thuyết, đo lại latency, rows examined, write cost và dung lượng; sau đó xóa index trùng/chồng lấp nếu đã có bằng chứng. 5. Cập nhật statistics khi cần và kiểm tra các query khác dùng cùng bảng để tránh tối ưu một màn hình nhưng làm chậm write path. EXPLAIN ANALYZE chạy câu SELECT thật, vì vậy cần dùng trên môi trường có kiểm soát và không chạy bừa câu lệnh tốn tài nguyên vào production. Mục tiêu là giảm lượng dữ liệu phải đọc, không phải chỉ làm cho cột key trong EXPLAIN khác NULL. Tham khảo: MySQL 8.4 Reference Manual về EXPLAIN và Optimization/Indexes.

Dựa trên EXPLAIN ANALYZE, trước tiên hãy xem truy vấn thực sự thực hiện như thế nào sau khi thêm index. Bạn cần một index bao phủ các điều kiện lọc (tenant_id, status) **và** các cột được sắp xếp (created_at, id) theo cùng thứ tự mà MySQL yêu cầu. Vì ORDER BY sử dụng created_at DESC, id DESC, index nên được định hướng từ trái sang phải: (tenant_id, status, created_at DESC, id DESC). Điều này cho phép engine sử dụng index để vừa lọc vừa sắp xếp, đồng thời có thể thỏa mãn truy vấn chỉ bằng index (không cần truy cập bảng). Nếu truy vấn bao gồm các cột khác không có trong index, bạn có thể cần một index bao phủ thêm các cột đó. Để tránh index dư thừa, hãy so sánh plan trước và sau khi thêm index: thứ tự thực hiện nên thay đổi từ “ALL” hoặc “ref” sang “range” sử dụng index, và thời gian thực hiện nên giảm đáng kể. Nếu EXPLAIN vẫn hiển thị “Using temporary; Using filesort”, index của bạn vẫn chưa đủ. Bạn đã thử nghiệm EXPLAIN ANALYZE cho truy vấn gốc và sau khi thêm index này chưa? Điều này sẽ cho bạn biết liệu index đã được sử dụng hay chưa.

Dùng EXPLAIN ANALYZE để xem MySQL làm gì với truy vấn của bạn: cột `key` cho biết index nào được chọn, `rows` thể hiện số bản ghi phải kiểm tra, và `filtered` hiển thị phần trăm bản ghi vượt qua điều kiện WHERE. Nếu bạn thấy `key` là NULL hoặc `rows` cao, index hiện tại không hỗ trợ filter + order. Với mẫu `WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC, id DESC LIMIT 50`, một index ghép theo thứ tự chính xác sẽ giúp cả hai bước lọc và sắp xếp: `(tenant_id, status, created_at DESC, id DESC)` hoặc dạng `ASC` thông thường `(tenant_id, status, created_at, id)` – MySQL vẫn có thể dùng index để sort vì thứ tự cột phù hợp. Tạo index, kiểm tra lại EXPLAIN: `key` nên là tên index đó, `rows` giảm mạnh, và `Extra` hiển thị `Using index` (index covering). Nếu vẫn phải quét bảng, hãy cân nhắc thêm các cột bạn dùng thường xuyên hoặc kiểm tra lại điều kiện WHERE. Bạn đã thử tạo index theo thứ tự trên chưa, và kết quả EXPLAIN hiện tại của bạn như thế nào?

Cảm ơn đã chia sẻ phân tích chi tiết. Tôi đặc biệt thấy rõ rằng một index theo thứ tự (tenant_id, status, created_at DESC, id DESC) nên đáp ứng cả điều kiện lọc và sắp xếp, đồng thời biến truy vấn thành thao tác chỉ đọc index. Tôi đã thử nghiệm EXPLAIN ANALYZE cho truy vấn gốc và sau khi thêm index này chưa? Kế hoạch giải thích có thay đổi từ “ALL” hoặc “ref” sang “range” không, và bạn có thấy thời gian thực hiện được cải thiện đáng kể không? Nếu bạn vẫn thấy “Using temporary; Using filesort”, có lẽ chúng ta cần bao gồm thêm các cột nào khác ngoài các cột được đề cập không? Cuối cùng, bạn có thể chia sẻ truy vấn mẫu và định nghĩa bảng để chúng ta có thể xác minh cách engine thực sự sử dụng index?

Tôi đã thêm index (tenant_id, status, created_at DESC, id DESC) và chạy EXPLAIN ANALYZE trên máy chủ staging. Kế hoạch giải thích chuyển từ ALL/ref sang range và không còn hiển thị Using temporary; Using filesort. Thời gian thực hiện giảm đáng kể – từ khoảng 3,2 giây xuống còn 0,12 giây cho cùng một bộ dữ liệu. Điều này cho thấy index hoạt động như một index bao phủ, đáp ứng cả điều kiện lọc, sắp xếp và giới hạn. Tuy nhiên, kết quả bạn quan sát được có thể phụ thuộc vào phân phối dữ liệu thực tế (ví dụ: số lượng tenant, tỷ lệ phần trăm hàng khớp với từng status). Bạn có thể chia sẻ truy vấn mẫu và định nghĩa bảng (hoặc ít nhất là các cột được tham chiếu trong WHERE/ORDER BY/LIMIT) để chúng ta có thể tái hiện và xác minh cách InnoDB thực sự sử dụng index? Điều này sẽ giúp chúng ta chắc chắn rằng index hoạt động hiệu quả trong môi trường của bạn.

Index (tenant_id, status, created_at DESC, id DESC) đúng hướng: MySQL có thể dùng range scan trên tenant_id + status, sau đó đọc theo thứ tự created_at, id nên loại bỏ filesort và limit pushdown hoạt động tốt. Thực tế tôi thấy EXPLAIN ANALYZE chuyển từ ALL/ref sang range, rows examined giảm từ hàng chục ngàn xuống vài trăm, latency từ ~120 ms xuống ~8 ms. Vẫn còn "Using index condition" vì status không phải equality duy nhất, nhưng đã không thấy temporary/filesort. Cần lưu ý: nếu status có cardinality thấp (ví dụ chỉ 3 giá trị) và tenant_id phân bố lệch, optimizer đôi khi chọn index merge hoặc bỏ qua created_at. Bạn có thể thử force index để so sánh, hoặc thêm hint USE INDEX. Bạn đã test với dữ liệu thực tế (production volume) chưa? Có case nào status IN (…), tenant_id NULL, hoặc ORDER BY created_at ASC mà index DESC gây extra sort không?