为什么我调整 sort_buffer_size 越大反而变慢，越小反而变快呢？

来源：3-8 ORDER BY语句优化-2

Mr_Raymond

2020-10-07 10:31:56

sql> select *
from employees
where first_name < 'Bader'
order by last_name
[2020-10-07 10:13:29] 22287 rows retrieved starting from 1 in 409ms (execution: 122ms, fetching: 287ms)
sql> select *
from employees
where first_name < 'Bader'
order by last_name
[2020-10-07 10:13:38] 22287 rows retrieved starting from 1 in 338ms (execution: 77ms, fetching: 261ms)
sql> set sort_buffer_size = 1024*1024
[2020-10-07 10:16:20] completed in 5ms
sql> select *
from employees
where first_name < 'Bader'
order by last_name
[2020-10-07 10:16:25] 22287 rows retrieved starting from 1 in 428ms (execution: 72ms, fetching: 356ms)
sql> select *
from employees
where first_name < 'Bader'
order by last_name
[2020-10-07 10:16:40] 22287 rows retrieved starting from 1 in 400ms (execution: 86ms, fetching: 314ms)
sql> set sort_buffer_size = 256*1024
[2020-10-07 10:16:58] completed in 4ms
sql> select *
from employees
where first_name < 'Bader'
order by last_name
[2020-10-07 10:17:04] 22287 rows retrieved starting from 1 in 374ms (execution: 72ms, fetching: 302ms)
sql> select * from `information_schema`.OPTIMIZER_TRACE
where QUERY like '%Bader%'
[2020-10-07 10:17:17] 2 rows retrieved starting from 1 in 36ms (execution: 24ms, fetching: 12ms)
sql> set sort_buffer_size = 2*1024*1024
[2020-10-07 10:18:28] completed in 6ms
sql> set sort_buffer_size = 2*1024*1024
[2020-10-07 10:18:31] completed in 6ms
sql> select *
from employees
where first_name < 'Bader'
order by last_name
[2020-10-07 10:18:36] 22287 rows retrieved starting from 1 in 522ms (execution: 224ms, fetching: 298ms)
sql> select * from `information_schema`.OPTIMIZER_TRACE
where QUERY like '%Bader%'
[2020-10-07 10:18:46] 1 row retrieved starting from 1 in 26ms (execution: 16ms, fetching: 10ms)

简单描述一下，MySQL 版本 8.0.20

测试最开始sort_buffer_size 是 512*1024，查询时间409ms，临时文件7

修改sort_buffer_size = 1024*1024，查询时间428ms，临时文件 2

修改sort_buffer_size = 256*1024，查询时间374ms，临时文件 34

修改sort_buffer_size = 2*1024*1024，查询时间522ms，临时文件 0

写回答

2回答

遇见未来的你

2020-10-08

1。 Sort_Buffer_Size 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。
2。 Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
3。文档说“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation”

Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。