文章目录
- 结论: 使用小表作为驱动表
- 一般效率: hash > BKA >= 临时表 BKA > NLJ > BNL >SNL;
- 假设驱动表数据量N, 被驱动表数据量M, 由于a=[0,1),则 aN <N
join类型 | 原理 | 说明 |
---|---|---|
SNL | 相当于嵌套的两层 for 循环 | 这个查询方式有点傻,mysql 没有使用 |
NLJ-可接受的联接查询 | 先查驱动表,被驱动表关了字段有索引,走索引扫描(树查找),需要回表; 没有使用 join_buffer | mysql 中的NLJ 指的就是 index-NLJ; O(N + N*log(M)) |
BNL - 一般不可接受的联接查询 | 驱动表放 join_buffer(数据无序), 放不下时分 Block 多(假设k,k=aN)次join; 被驱动表联接字段没有索引,驱动表中每行数据查找会扫全表,找到后在与内存中join_buffer 内容一行行匹配 | O(N + aN*M), join_buffer 一次性放下驱动表数据时,aN =1, 此时时间复杂度: O(N + M), 因此可以通过 增大 join_buffer 加快查询速度,其他使用 join_buffer 的情况优化同理 |
BKA | 相当于 NLJ 驱动表的关联字段放到 join_buffer后排序,join_buffer 中数据批量传给被驱动表的联接索引字段,在普通得到连续的主键字段,回表时会使用到磁盘顺序读; NLJ 使用磁盘顺序读特性优化后 变为BKA, 需要配置开启MRR(innodb 默认策略关闭为on,开启mrr_based=off) | 基于一个前提: 大多数数据是按主键索引递增顺序插入的; 时间复杂度: O(N + aN*log(M)),但是其每次 io 性能提高了 |
BNL 优化 成BKA (使用临时表) | 被驱动表联接字段加索引(冷查询成本高,不合适); 将被驱动表要查询的数据放临时表,在临时表的要联接的字段上加索引; 开启mrr 即可走 BKA | join_buffer 不够时,也可能分批次,所以时间复杂度: O(N + aN*log(M) + 临时表建表成本) |
Hash-Join (join_buffer 够用CHJ) | 相当于 for 循环加Map 匹配联接字段 | O(N + N*hash) |
分块 hash-join (On-Disk Hash-Join) | mysql 8.0.18 使用 | 驱动表分块使用 join_buffer , 被驱动表对每块 probe |
hash 分片 hash-join | mysql 8.0.18 使用 没看太懂 | |
Grace-Hash-Join | oracle 使用 | 没看懂,先不写 |
hybrid Hash-Join | OceanBase 使用 | 也没看出区别, 先不写 |