mysql - mysql 中 join_buffer_size 的推荐最大值是多少？

Question

我正在按照这个问题的答案中给出的建议来优化我的临时数据: Mysql tmp_table_size max_heap_table_size

mysql 中 join_buffer_size 和 sort_buffer_size 的推荐值是多少？

我的实际值是:

join_buffer_size: 128 Kio;
sort buffer size: 512 Kio;

Answer 1

一般情况下不可能回答你的问题。这实际上取决于您的查询配置文件。查看手册，了解他们的目的，正如他们所说的那样:

( sort_buffer_size )

Setting it larger than required globally will slow 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 (...). Experiment to find the best value for your workload.

( join_buffer_size )

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 to a larger setting 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.