JOIN命令
第一部分：到底可不可以使用 JOIN？
一、 背景与核心问题
-
DBA 为何可能不让使用 JOIN？
-
多表 JOIN 时，如何选择驱动表？
二、 MySQL JOIN 的执行算法
-
适用场景： 被驱动表的 JOIN 字段上 有可用索引。
-
执行流程：
-
从驱动表 t1 逐行读取数据 R。
-
用 R 中的 JOIN 字段值，通过被驱动表 t2 的索引查找匹配行。
-
合并结果。
-
-
性能分析：
-
驱动表全表扫描 (N行)。
-
被驱动表索引查找 (每次约 2*log₂M 次操作，M为被驱动表行数)。
-
总复杂度近似：N + N * 2 * log₂M。
-
-
结论1 (能否使用 JOIN)： 可以使用。 性能通常优于应用层拆分（减少网络交互和客户端拼接开销）。
-
结论2 (驱动表选择)： 应选择小表作为驱动表。 (驱动表行数 N 对整体性能影响更大)。
-
适用场景： 被驱动表的 JOIN 字段上 无可用索引。
-
执行流程 (理论)：
-
从驱动表 t1 逐行读取数据 R。
-
对被驱动表 t2 进行 全表扫描，查找与 R 匹配的行。
-
-
性能分析：
-
扫描行数：N (驱动表) + N * M (被驱动表扫描 M 次) = N * (1+M)。
-
MySQL 实际并未使用此算法，因其效率极低。
-
-
适用场景： 被驱动表的 JOIN 字段上 无可用索引。
-
执行流程：
-
将驱动表 t1 的数据读入 join_buffer (内存)。
-
扫描被驱动表 t2，逐行与 join_buffer 中的数据进行比较。
-
满足条件的合并结果。
-
-
join_buffer 大小与分块：
-
由 join_buffer_size 参数控制 (默认 256k)。
-
若驱动表数据无法一次性放入 join_buffer，则会分块处理：
-
驱动表的一部分数据放入 join_buffer。
-
扫描整个被驱动表进行匹配。
-
清空 join_buffer，放入驱动表下一部分数据。
-
再次扫描整个被驱动表进行匹配。
-
以此类推，导致被驱动表被多次扫描。
-
-
-
性能分析 (不分块)：
-
扫描行数：N (驱动表) + M (被驱动表) = N + M。
-
内存比较次数：N * M。
-
-
性能分析 (分块，驱动表N行，分K段，被驱动表M行)：
-
扫描行数：N + K * M (K ≈ N / (join_buffer_size / 平均行大小))。
-
内存比较次数：N * M。
-
-
结论1 (能否使用 JOIN)： 尽量避免。 扫描行数过多，占用大量系统资源，特别是大表 JOIN。判断依据：EXPLAIN 结果的 Extra 字段出现 "Block Nested Loop"。
-
结论2 (驱动表选择)： 应选择小表作为驱动表。
-
当 join_buffer 足够大时，驱动表选择影响不大。
-
当 join_buffer 不够大时 (常见情况)，选择小表作为驱动表可以显著减少被驱动表的扫描次数。
-
三、 如何定义 "小表"？
-
不仅仅是原始表行数。
-
准确定义： 两个表按照各自的 WHERE 条件过滤后，实际参与 JOIN 操作的各个字段的总数据量，数据量小的那个表是 "小表"。
-
考虑因素1：过滤后的行数。
-
考虑因素2：参与 JOIN 的字段数量和大小。 (需要放入 join_buffer 的数据量)
-
第二部分：JOIN 语句怎么优化？
一、 Multi-Range Read (MRR) 优化 (优化回表)
-
背景： 普通索引查询后的回表操作，如果按索引顺序得到的主键 ID 是随机的，会导致磁盘随机读，性能较差。
-
MRR 原理：
-
根据辅助索引定位到满足条件的记录，将主键 ID 放入 read_rnd_buffer。
-
对 read_rnd_buffer 中的主键 ID 进行排序。
-
按排序后的主键 ID 顺序到主键索引中查找数据，尽可能将随机读转换为顺序读。
-
-
read_rnd_buffer_size： 控制 read_rnd_buffer 的大小。
-
启用： set optimizer_switch="mrr_cost_based=off" (强制使用 MRR，而非基于成本判断)。
-
核心收益： 适用于范围查询（多值查询），通过排序主键 ID 提升回表效率。
二、 Batched Key Access (BKA) 优化 (NLJ 算法的优化)
-
NLJ 痛点： 逐行从驱动表取值到被驱动表匹配，无法利用 MRR 的批量优势。
-
BKA 原理：
-
从驱动表一次性取出多行数据 (的 JOIN 字段值) 放入 join_buffer。
-
将 join_buffer 中的这些值批量传给被驱动表。
-
被驱动表利用 MRR 机制进行优化查找。
-
-
启用： set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; (BKA 依赖 MRR)。
三、 BNL 算法的性能问题与优化
-
BNL 算法对系统的主要影响：
-
磁盘 IO 压力： 可能多次扫描被驱动表。
-
CPU 消耗： M*N 次内存比较，大表时非常高。
-
Buffer Pool 污染：
-
若被驱动表是冷数据表且执行时间超过1秒，多次扫描会将其数据页移入 LRU 链表头部 (young 区域)，挤占热数据。
-
若冷数据表过大，可能导致正常业务的热数据页在 old 区域停留时间不足1秒就被淘汰，无法进入 young 区域。
-
影响：内存命中率下降，恢复较慢。
-
-
-
优化方向：将 BNL 转化为 BKA (或 NLJ)。
- 主要手段：给被驱动表的 JOIN 字段添加索引。
-
BNL 转 BKA 的临时表方案 (当不适合在原表建索引时)：
-
场景： JOIN 条件涉及的被驱动表数据经过 WHERE 过滤后数据量较小，但原表很大，不宜为低频查询建索引。
-
思路：
-
将被驱动表中满足 WHERE 条件的数据筛选出来，存入一个临时表 temp_t。
-
在临时表 temp_t 的 JOIN 字段上创建索引。
-
让驱动表与这个带有索引的临时表 temp_t 进行 JOIN (此时会使用 BKA/NLJ)。
-
-
效果： 大幅提升性能，减少了不必要的比较次数。
-
四、 扩展：Hash Join
-
概念： 一种更高效的 JOIN 算法，MySQL 目前原生不支持（截至文章撰写时，较新版本如 MySQL 8.0.18+ 已开始支持 Hash Join）。
-
原理 (应用层模拟)：
-
将小表 (驱动表) 的数据加载到应用层内存中的哈希表结构 (key 为 JOIN 字段，value 为行数据或所需字段)。
-
扫描大表 (被驱动表)，对每一行数据，用其 JOIN 字段值去哈希表中查找。
-
匹配成功则合并结果。
-
-
优势： 内存查找效率远高于 BNL 的遍历比较。
-
注意： 需要应用层实现，且有内存消耗。
五、 JOIN 优化总结
-
BKA 优化： MySQL 内置，默认开启，建议使用。
-
BNL 算法： 效率低下，尽量通过给被驱动表的关联字段加索引转为 BKA/NLJ。
-
临时表方案： 适用于能提前大幅过滤被驱动表数据的场景。
-
Hash Join： (若 MySQL 版本不支持) 可在应用层模拟，通常性能更优。
六、三表 JOIN 的索引设计与连接顺序
-
语句： select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;
-
索引设计考虑：
-
t1: 考虑在 (a, c) 或 (c, a) 上建联合索引，或单独在 a 和 c 上建索引。
-
t2: 考虑在 (a, b, c) 或其子集上建联合索引，如 (a, c) 和 (b, c) 或 (a,b) (c)。JOIN 字段 a 和 b 很重要。
-
t3: 考虑在 (b, c) 或 (c, b) 上建联合索引，或单独在 b 和 c 上建索引。
-
目标是让每个 JOIN 操作都能用上被驱动表的索引 (NLJ/BKA)，并且 WHERE 条件能有效过滤。
-
-
straight_join 连接顺序考虑因素：
-
优先选择过滤后结果集最小的表作为第一个驱动表。
-
对于后续的 JOIN，依然遵循小表驱动大表的原则（这里的“小表”是上一步 JOIN 结果集与当前表 JOIN 后，参与 JOIN 的数据量）。
-
确保每一步 JOIN，被驱动表的 JOIN 字段都有索引可用。
-
例如，如果 t1.c>=X 过滤性最好，可以 t1 作为起始驱动表。然后看 t1 JOIN t2 和 t1 JOIN t3 (如果可以直接 JOIN 的话) 哪个的中间结果集更小，或者哪个能更好地利用后续索引。
-
一种可能的顺序 (具体取决于数据分布和过滤条件的效果)：
-
t1 (经过 t1.c>=X 过滤)
-
JOIN t2 ON (t1.a=t2.a) (经过 t2.c>=Y 过滤)
-
JOIN t3 ON (t2.b=t3.b) (经过 t3.c>=Z 过滤)
-
-
需要确保 t2.a 有索引，t3.b 有索引。同时，t2.c 和 t3.c 的条件也要考虑能否利用索引。
-