MySQL中，对于SELECT语句中的全连接，行数据会被缓存在join缓冲区。如果没有可用的键来为下一张表找到行，就会发生全连接。如果无法添加索引，可以通过增加join_buffer_size来提升全连接速度。每个全连接的两张表之间会分配一个缓冲区，对于不使用索引的复杂多表连接，可能需要多个缓冲区。全局设置join_buffer_size要谨慎，最好保持较小并在进行大型连接时仅在特定会话中增大，或者按查询更改设置。当使用Block Nested-Loop时，更大的join缓冲区直到存储所有第一张表的列都是有益的。Batched Key Access中，join_buffer_size定义了每次请求存储引擎的键批处理大小，更大的缓冲区可以显著提高性能。
For every “full join” in a SELECT statement the rows found are cached in a join cache. (A “full join” here means there were no keys that could be used to find rows for the next table in the list.) In the worst case, one SELECT query can use many join caches
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.
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
最低0.47元/天 解锁文章
6047
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
