Ví dụ bạn có index (a,b,c) nhưng câu lệnh bạn đang dùng là
SELECT a,b,c FROM some_table ORDER BY a,cThì MySql sẽ không dùng index (a,b,c). MySql dùng index theo kiểu B-tree và index theo hướng trái qua phải. Nghĩa là lúc này c được sort bởi b mà lại không có b nên sẽ không thể sort cùng với a trong index được. Lúc này MySql sẽ dùng thuật toán filesort, bạn có thể kiểm tra bằng cách dùng EXPLAIN và chú ý ở cột Extra.
Fun fact #1
Thuật toán filesort phụ thuộc vào nhiều yếu tố như lượng dữ liệu, kinh nghiệm trước đó, hoàn cảnh và cả query để chọn cách tối ưu nhất. Nhưng tóm lại filesort sẽ dùng 1 trong 2 cách sau:
- Priority queue: MySql liên tục push các kết quả tìm được vào queue, khi queue đầy thì kết quả có độ ưu tiên thấp nhất bị gắp ra và nhét kết quả mới vào vị trí thích hợp. Xử lý kiểu này phức tạp nhưng không cần ổ cứng nhanh + đủ dung lượng.
- Merge sort: chia thành các file nhỏ, sort từng file bằng Quicksort và gộp lại thành kết quả. Xử lý kiểu này đơn giản nhưng cần ổ cứng nhanh + đủ dung lượng.
Bạn không thể lựa chọn cách filesort dùng nhưng có thể xem cách nào đang được dùng bằng The Optimizer Trace. Ví dụ bạn thấy filesort_priority_queue_optimization.usable: false, thì có nghĩa là MySql đang dùng merge sort.
SET optimizer_trace="enabled=on";Bạn có thể đọc cách MySql làm ra filesort ở đây
Lưu ý: filesort chỉ dùng ổ cứng hay cụ thể là temporary file nếu lượng dữ liệu cần sort lớn hơn tham số sort_buffer_size (Hiện tại mặc định của mysql 8 là 256KB) nếu không sẽ dùng RAM.
Fun fact #2
Có nhiều khi thuật toán filesort dùng không quá quan trọng như cách bạn thiết lập filesort. Ví dụ sau khi explain bạn có các thông tin như sau
Hãy chú ý tới thông số: sort_mode.
- < sort_key, rowid > : sort 2 chiều.
- Đầu tiên dựa vào các điều kiện sort thì MySql dùng field được dùng để sort và ID (primary key) để lọc dữ liệu từ bảng.
- Sort ở sort buffer.
- Sau khi đã sort thì lấy tiếp các trường theo ID.
- < sort_key, additional_fields >: Sort 1 chiều. Lấy hết các trường thỏa mãn điều kiện và sort ở sort bufer.
- < sort_key, packed_additional_fields > Sort 1 chiều. Đóng gói dữ liệu kiểu char và varchar cho nhỏ gọn hơn ở sort buffer so với cách 2.
Chúng ta có thể thấy < sort_key, rowid > sẽ chậm nhất. < sort_key, rowid > sẽ được dùng khi tổng kích thước của sort key và row vượt quá thông số max_length_for_sort_data. Ví dụ nếu max_length_for_sort_data = 1024 thì chỉ cần row có 2 field là VARCHAR(255) (=255*4=1020 bytes) và VARCHAR(2) (=2*4=8 bytes) thì với tổng 1028 sẽ vượt quá 1024. Lúc này filesort sẽ chọn < sort_key, rowid >. Hãy nhớ giữ sort keys bé nhất có thể để không vượt quá giới hạn này.
