MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

俊达2年前技术文章1319

单表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.png


使用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版本)


相关文章

MySQL运维实战之备份和恢复(8.1)xtrabackup全量备份

xtrabackup是percona开源的mysql物理备份工具。xtrabackup 8.0支持mysql 8.0版本的备份和恢复。xtrabackup 2.4支持mysql 5.7及以下版本的备份...

MySQL运维实战(4.6) SQL_MODE之NO_BACKSLASH_ESCAPES

设置NO_BACKSLASH_ESCAPES后,反斜杠(\)不作为转义符。不设置NO_BACKSLASH_ESCAPES时,反斜杠(\)用来转义后一个字符mysql> create&n...

MySQL运维实战之备份和恢复(8.2)xtrabackup备份到云端(OSS)

xtrabackup工具中有一个xbcloud程序,可以将数据库直接备份到S3对象存储中,本地不落盘。这里介绍将数据库直接备份到OSS的一种方法。具体方法如下:1、准备OSS我们使用ossutil工具...

MySQL运维实战(6)用户认证插件caching_sha2_password

MySQL用户认证可以使用几种不同的方式,创建用户时可以制定认证方式:create user 'username'@'%' identif...

MySQL运维实战之备份和恢复(8.4)xtrabackup恢复全量备份

恢复全量备份恢复全量备份大致可以分成以下几步:解压备份文件、prepare备份文件、将数据copy到目标实例相关目录、启动数据库实例。解压文件如果备份时使用了xbstream,需要先解压备份文件。我们...

MySQL运维实战(5.5) 数据导入导出时的字符集问题

mysql可以使用load data/select into outfile或mysqldump工具进行数据导入导出。下面分别分析数据导入导出时的字符集相关问题。准备测试数据创建测试表,2个字段分别使...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。