MySQL优化器特性（三）表关联之BKA（Batched Key Access）优化
单表range查询时，可以使用MRR优化，先对rowid进行排序，然后再回表查询数据。在表关联的时候，也可以使用类似的优化方法，先根据关联条件取出被关联表的rowid，将rowid缓存在join buffer中，对rowid进行排序后，再回表获取数据。
一个例子
执行计划
出于测试的目的，给查询加上BKA hint（或MRR hint），执行计划Extra中显示“Using join buffer (Batched Key Access)”，说明表关联时，使用了BKA优化。
mysql> explain select /*+ BKA(t2) */ * from tb t1, tb t2 where t1.a = t2.a and t1.c=2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: idx_c,idx_ab key: idx_c key_len: 5 ref: const rows: 32 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ref possible_keys: idx_ab key: idx_ab key_len: 5 ref: test.t1.a rows: 32 filtered: 100.00 Extra: Using join buffer (Batched Key Access) 2 rows in set, 1 warning (0.00 sec)
表结构
上述测试使用的表结构和数据：
mysql> show create table tb\G *************************** 1. row *************************** Table: tb Create Table: CREATE TABLE `tb` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL, `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `idx_c` (`c`), KEY `idx_ab` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select a,b,c , count(*) from tb group by a,b,c; +------+------+------+----------+ | a | b | c | count(*) | +------+------+------+----------+ | 1 | 1 | 1 | 32 | | 2 | 2 | 2 | 32 | | 3 | 3 | 3 | 32 | | 4 | 4 | 4 | 32 | | 5 | 5 | 5 | 32 | | 6 | 6 | 6 | 32 | | 7 | 7 | 7 | 32 | | 8 | 8 | 8 | 32 | | 9 | 9 | 9 | 32 | | 10 | 10 | 10 | 32 | +------+------+------+----------+ 10 rows in set (0.00 sec)
使用场景
和单表MRR优化类似，表关联时使用BKA优化有一些前提条件：
1、被驱动表的关联字段存在二级索引。如果使用被驱动表的关联条件是主键，则不会采用BKA优化。
2、需要回表访问被关联表。如果在查询中，被驱动表访问的字段都在索引中，不需要回表，则不会采用BKA优化。
3、使用BKA的执行计划成本比默认的Nest loop关联低，或者查询中添加了BKA或MRR hint。
BKA Join执行过程
使用BKA算法关联，会用到Join Buffer。BKA关联执行过程大致如下：
第一阶段：往join buffer中写入驱动表的数据
1、获取驱动表的数据。将获取到的数据缓存到Join Buffer中。
2、评估驱动表的一条记录，需要占用多少内存。包括记录本身的长度，加上存放对应的被驱动表rowid需要的空间。
3、如果join buffer空间够，则继续往join buffer中加入驱动表记录。否则，分配MRR buffer，进入下一阶段。
第二阶段：填充MRR buffer
根据缓存的驱动表的记录，扫描被驱动表的索引，将得到的rowid缓存到MRR buffer中。
填充完成后对MRR buffer按rowid进行排序。
第三阶段：获取被驱动表的记录
1、根据rowid回表获取数据。
2、读取驱动表缓存在join buffer中的数据。
3、关联数据，并将关联后的数据返回给客户端。
如果数据还没有处理完成，重复以上几个阶段。
总结
BKA Join在实际中可能并不常见。不过通过学习BKA Join的实现方式，可以了解MySQL如何使用join buffer，也能帮我们更好地理解表关联在代码层面是如何实现的。
（本文的描述，以及SQL和执行计划基于mysql 8.0.31版本）
