MySQL数据库作为我们常用的数据库，在日常开发过程，也写过不少SQL，SQL优化也是不可避免的，今天我就从join算法角度分析一下join语句该如何优化。
一、join算法
下边按照使用频率依次介绍一下各种算法：
1. Index Nested-Loop Join（索引嵌套循环连接）
原理：
- 使用嵌套循环的方式逐行对比两张表的数据。
- 对外层表（驱动表）中的每一行，在内层表中查找满足条件的记录。
实现：
- 通常选择小表作为外层表（驱动表）。
- 每条记录会触发内层表的查询。
优缺点：
- 优点：简单直接，适用于小规模数据或一张表很小的情况。
- 缺点：时间复杂度较高，性能瓶颈在大数据量时非常明显。
2. Block Nested-Loop Join（块嵌套循环连接）
原理：
- 优化了嵌套循环 JOIN，通过减少内层表的访问次数来提高效率。
- 将外层表的数据分成块（Block），每次加载一块数据，与内层表进行匹配。
实现：
- 外层表的数据被分块加载，每块数据对内层表全表扫描一次。
- 减少了磁盘 IO。
优缺点：
- 优点：减少了内层表的扫描次数，性能高于 Nested-Loop Join。
- 缺点：当内层表较大时，性能仍可能较差。
3. Nested-Loop Join（嵌套循环连接）
原理：
- 使用索引优化内层表的查询。
- 对外层表的每一行，在内层表中使用索引查找匹配的行。
实现：
- 外层表逐行扫描。
- 内层表通过索引快速定位匹配的记录，避免全表扫描。
4. Batched Key Access算法（BKA）
BKA算法是对Index Nested-Loop Join的优化， 适用于使用索引访问的场景，特别是当 MySQL 必须从内表中检索大量数据时。它通过批量获取外表的键值并进行批量索引查找来减少随机 I/O 操作的开销。
原理：
- ** 批量处理**：与传统的 Nested-Loop Join 每次处理一个键值不同，BKA 会从外表中批量提取键值，然后一次性使用这些键值对内表进行索引查找。
- 减少随机 I/O：通过批量索引查找的方式，将随机 I/O 转化为顺序 I/O，提高性能。
- 使用临时缓冲区：外表的键值存储在一个缓冲区中，MySQL 会根据缓冲区的大小选择合适的批量大小。
实现：
- 扫描外表：读取一批记录，将它们的键值存储到缓冲区中。
- 批量索引查找：使用缓冲区中的所有键值，批量地对内表进行索引查找。
- 返回结果：对查找到的结果进行组合后返回。
- 重复操作：如果外表还有未处理的记录，则重复步骤 1 到步骤 3，直到处理完所有记录。
二、优化思路
对Index Nested-Loop Join算法的优化就是在使用Batched Key Access算法，需要先开启：
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';对Block Nested-Loop Join算法的优化还是要从被驱动表需要做多次扫描入手，如果被驱动表是一个冷的数据表，每次扫描都会从磁盘中读取数据页放入内存中的Buffer Pool，当冷表很大的时候就会影响其他频繁访问的表正常使用Buffer Pool，并且这个影响不像IO的影响一样在语句执行结束就结束，对Buffer Pool 的影响是持续的，需要后续查询慢慢恢复Buffer Pool的内存命中率。
所以正确的流程是，执行语句前，通过理论分析和explain结果，确定是否使用Block Nested-Loop Join算法，然后进行优化，，给被驱动表的join字段加上索引，将Block Nested-Loop Join算法转成Batched Key Access算法。
