本文详细介绍了 MySQL 参数 join_buffer_size 在 INNER JOIN 场景的使用，OUTER JOIN 不包含。在讨论这个 BUFFER 之前，我们先了解下 MySQL 的 INNER JOIN 分类。
如果按照检索的性能方式来细分，那么无论是两表 INNER JOIN 还是多表 INNER JOIN，都大致可以分为以下几类：
今天主要针对第三种场景来分析，也是就全表扫的场景。
回过头来看看什么是 join_buffer_size？
JOIN BUFFER 是 MySQL 用来缓存以上第二、第三这两类 JOIN 检索的一个 BUFFER 内存区域块。一般建议设置一个很小的 GLOBAL 值，完了在 SESSION 或者 QUERY 的基础上来做一个合适的调整。
比如，默认的值为 512K， 想要临时调整为 1G。那么，
mysql>set session join_buffer_size = 1024 * 1024 * 1024;
mysql>select * from ...;
mysql>set session join_buffer_size=default;
或者
mysql>select /*+ set_var(join_buffer_size=1G) */ * from ...;
接下来详细看下 JOIN BUFFER 的用法。
1. Nested-Loop Join 翻译过来就是嵌套循环连接，简称 NLJ。
这种是 MySQL 里最简单、最容易理解的表关联算法。
比如，拿语句 select * from p1 join p2 using(r1) 来说，
先从表 p1 里拿出来一条记录 ROW1，完了再用 ROW1 遍历表 p2 里的每一条记录，并且字段 r1 来做匹配是否相同，以便输出；再次循环刚才的过程，直到两表的记录数对比完成为止。
那看下实际 SQL 的执行计划，
mysql> explain format=json select * from p1 inner join p2 as b using(r1)\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1003179606.87"
},
"nested_loop": [
{
"table": {
"table_name": "b",
"access_type": "ALL",
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "100.00",
"prefix_cost": "101.00",
"data_read_per_join": "15K"
},
"used_columns": [
"id",
"r1",
"r2"
]
}
},
{
"table": {
"table_name": "p1",
"access_type": "ALL",
"rows_examined_per_scan": 9979810,
"rows_produced_per_join": 997981014,
"filtered": "10.00",
"cost_info": {
"read_cost": "5198505.87",
"eval_cost": "99798101.49",
"prefix_cost": "1003179606.87",
"data_read_per_join": "14G"
},
"used_columns": [
"id",
"r1",
"r2"
],
"attached_condition": "(`ytt_new`.`p1`.`r1` = `ytt_new`.`b`.`r1`)"
}
}
]
}
}
1 row in set, 1 warning (0.00 sec)
2. Block Nested-Loop Join ，块嵌套循环，简称 BNLJ
mysql> explain format=json select * from p1 inner join p2 as b using(r1)\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "997986300.01"
},
"nested_loop": [
{
"table": {
"table_name": "b",
"access_type": "ALL",
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "100.00",
"prefix_cost": "101.00",
"data_read_per_join": "15K"
},
"used_columns": [
"id",
"r1",
"r2"
]
}
},
{
"table": {
"table_name": "p1",
"access_type": "ALL",
"rows_examined_per_scan": 9979810,
"rows_produced_per_join": 997981014,
"filtered": "10.00",
"using_join_buffer": "Block Nested Loop",
"cost_info": {
"read_cost": "5199.01",
"eval_cost": "99798101.49",
"prefix_cost": "997986300.01",
"data_read_per_join": "14G"
},
"used_columns": [
"id",
"r1",
"r2"
],
"attached_condition": "(`ytt_new`.`p1`.`r1` = `ytt_new`.`b`.`r1`)"
}
}
]
}
}
1 row in set, 1 warning (0.00 sec)
3. 最近 MySQL 8.0.18 发布，终于推出了新的 JOIN 算法 — HASH JOIN。
mysql> explain format=tree select * from p1 inner join p2 as b using(r1)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (p1.r1 = b.r1) (cost=997986300.01 rows=997981015)
-> Table scan on p1 (cost=105.00 rows=9979810)
-> Hash
-> Table scan on b (cost=101.00 rows=1000)
1 row in set (0.00 sec)
社区近期动态
Mycat 问题免费诊断
诊断范围支持：
Mycat 的故障诊断、源码分析、性能优化
服务支持渠道：
技术交流群，进群后可提问
QQ群（669663113）
社区通道，邮件&电话
osc@actionsky.com
现场拜访，线下实地，1天免费拜访
关注“爱可生开源社区”公众号，回复关键字“Mycat”，获取活动详情。
社区技术内容征稿
征稿内容：
格式：.md/.doc/.txt
主题：MySQL、分布式中间件DBLE、数据传输组件DTLE相关技术内容
要求：原创且未发布过
奖励：作者署名；200元京东E卡+社区周边
投稿方式：
邮箱：osc@actionsky.com
格式：[投稿]姓名+文章标题
以附件形式发送，正文需注明姓名、手机号、微信号，以便小编及时联系