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

本站中文解释

join_buffer_size参数表示JOIN操作时缓冲使用的尺寸，通常设置为4KB，此参数的取值范围最小为4KB，最大值可以达到2G。由于JOIN操作需要大量的内存作为缓存，因此JOIN_buffer_size参数对于JOIN操作的性能有着至关重要的影响。

在MySQL5.5版本之前，调整此参数的值通常用set global join_buffer_size=数值的方式来进行设置，但从MySQL5.5之后就推荐使用set session join_buffer_size=数值的方式来进行设置，以使此参数的变更只对当前的会话有效。设置值只能在4KB到2G之间，如果设置的值超过2G的话，MySQL服务器会自动重新计算参数为最大值2G。因此对于此参数的调整，可以用如下命令完成设置：

set session join_buffer_size=数值;

官方英文解释

join_buffer_size

The minimum size of the buffer that is used for plain index
scans, range index scans, and joins that do not use indexes
and thus perform full table scans. Normally, the best way to
get fast joins is to add indexes. Increase the value of
join_buffer_size to get a
faster full join when adding indexes is not possible. One join
buffer is allocated for each full join between two tables. For
a complex join between several tables for which indexes are
not used, multiple join buffers might be necessary.

The default is 256KB. The maximum permissible setting for
join_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). The block size is
128, and a value that is not an exact multiple of the block
size is rounded down to the next lower multiple of the block
size by MySQL Server before storing the value for the system
variable. The parser allows values up to the maximum unsigned
integer value for the platform (4294967295 or
2³²−1 for a 32-bit system,
18446744073709551615 or 2⁶⁴−1
for a 64-bit system) but the actual maximum is a block size
lower.

Unless a Block Nested-Loop or Batched Key Access algorithm is
used, there is no gain from setting the buffer larger than
required to hold each matching row, and all joins allocate at
least the minimum size, so use caution in setting this
variable to a large value globally. It is better to keep the
global setting small and change the session setting to a
larger value only in sessions that are doing large joins.
Memory allocation time can cause substantial performance drops
if the global size is larger than needed by most queries that
use it.

When Block Nested-Loop is used, a larger join buffer can be
beneficial up to the point where all required columns from all
rows in the first table are stored in the join buffer. This
depends on the query; the optimal size may be smaller than
holding all rows from the first tables.

When Batched Key Access is used, the value of
join_buffer_size defines how
large the batch of keys is in each request to the storage
engine. The larger the buffer, the more sequential access is
made to the right hand table of a join operation, which can
significantly improve performance.

For additional information about join buffering, see
Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about
Batched Key Access, see
Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.