MySQL Variables sort_buffer_size 数据库 参数变量解释及正确配置使用

本站中文解释

MySQL参数sort_buffer_size用于控制查询排序时需要使用的缓冲大小，一般需要设置大一些可提升排序性能，不过同时也增加了服务器内存消耗。

可以在MySQL配置文件my.cnf中添加如下配置来设置：

sort_buffer_size = 64M

官方英文解释

sort_buffer_size

Each session that must perform a sort allocates a buffer of
this size. sort_buffer_size
is not specific to any storage engine and applies in a general
manner for optimization. At minimum the
sort_buffer_size value must
be large enough to accommodate fifteen tuples in the sort
buffer. Also, increasing the value of
max_sort_length may require
increasing the value of
sort_buffer_size. For more
information, see Section 8.2.1.14, “ORDER BY Optimization”

If you see many
Sort_merge_passes per second
in SHOW GLOBAL
STATUS output, you can consider increasing the
sort_buffer_size value to
speed up ORDER BY or GROUP
BY operations that cannot be improved with query
optimization or improved indexing.

The optimizer tries to work out how much space is needed but
can allocate more, up to the limit. Setting it larger than
required globally slows down most queries that sort. It is
best to increase it as a session setting, and only for the
sessions that need a larger size. On Linux, there are
thresholds of 256KB and 2MB where larger values may
significantly slow down memory allocation, so you should
consider staying below one of those values. Experiment to find
the best value for your workload. See
Section B.3.3.5, “Where MySQL Stores Temporary Files”.

The maximum permissible setting for
sort_buffer_size is
4GB−1. Larger values are permitted for 64-bit platforms
(except 64-bit Windows, for which large values are truncated
to 4GB−1 with a warning).