Mỗi hệ quản trị cơ sở dữ liệu đều có một vùng nhớ (memory / RAM) riêng dành cho việc sort data.
Khi data cần sort yêu cầu bộ nhớ > bộ nhớ được dành riêng này thì nó sẽ chuyển qua lưu dữ liệu sort tạm vào bộ nhớ (disk). Do disk có tốc độ truy xuất và ghi chậm hơn so với memory nên từ đó làm giảm tốc độ truy vấn.
Có 2 cách để giải quyết khi query bị slow do nguyên nhân này:
Khi data cần sort yêu cầu bộ nhớ > bộ nhớ được dành riêng này thì nó sẽ chuyển qua lưu dữ liệu sort tạm vào bộ nhớ (disk). Do disk có tốc độ truy xuất và ghi chậm hơn so với memory nên từ đó làm giảm tốc độ truy vấn.
Có 2 cách để giải quyết khi query bị slow do nguyên nhân này:
- Tăng dung lượng bộ nhớ sort trên mysql
- Đánh index để mysql có thể đổi execution plan sang dùng index này cho việc sort
1. Config dung lượng bộ nhớ sort trên mysql
- Có thể config sort_buffer_size trong mysql để tăng dung lương bộ nhớ dành cho sort lên. Mặc định mysql sẽ dành 262144 bytes cho vùng nhớ sort.
- Vùng nhớ cho sort được cấp phát mỗi lần mysql chạy order (chứ không phải cấp phát duy nhất 1 lần xong query sort sau phải xếp hàng chờ query sort trước đó sort xong). Mysql có thể cấp phát dung lượng bộ nhớ <= sort_buffer_size tùy theo câu query
- Tăng sort_buffer_size có thể làm tăng tốc độ trong trường hợp query cần một lượng lớn bộ nhớ (lớn hơn bộ nhớ config cho việc sort), đồng thời làm giảm tốc độ sort của những query khác (do việc cấp phát bộ nhớ sẽ mất nhiều thời gian hơn).
- Mysql khuyến nghị chỉ nên tăng bộ nhớ sort trong trường hợp không còn cách nào tối ưu được câu lệnh nữa.
- Nên chỉ thay đổi chỉ số này cho một sessions / query nhất định (session yêu cầu sort dùng nhiều bộ nhớ). Có thể sử dụng optimizer_hints để set biến này cho từng câu query
- Giá trị max cho sort_buffer_size là 4GB
- Vùng nhớ cho sort được cấp phát mỗi lần mysql chạy order (chứ không phải cấp phát duy nhất 1 lần xong query sort sau phải xếp hàng chờ query sort trước đó sort xong). Mysql có thể cấp phát dung lượng bộ nhớ <= sort_buffer_size tùy theo câu query
- Tăng sort_buffer_size có thể làm tăng tốc độ trong trường hợp query cần một lượng lớn bộ nhớ (lớn hơn bộ nhớ config cho việc sort), đồng thời làm giảm tốc độ sort của những query khác (do việc cấp phát bộ nhớ sẽ mất nhiều thời gian hơn).
- Mysql khuyến nghị chỉ nên tăng bộ nhớ sort trong trường hợp không còn cách nào tối ưu được câu lệnh nữa.
- Nên chỉ thay đổi chỉ số này cho một sessions / query nhất định (session yêu cầu sort dùng nhiều bộ nhớ). Có thể sử dụng optimizer_hints để set biến này cho từng câu query
- Giá trị max cho sort_buffer_size là 4GB
SET SESSION sort_buffer_size=value
2. Trong trường hợp phải sử dụng bộ nhớ để sort thì dữ liệu sẽ được mysql lưu vào đâu (location của file).
- Vị trí lưu file temp thay đổi tùy theo hệ thống:
- Unix: /tmp, /var/tmp, or /usr/tmp
- Windows: C:\windows\temp\
- Có thể thay đổi bằng cách set lại tmp_dir