jion

Index Nested-Loop Join

• 被驱动表走索引
• 查询次数 N + N * log M
• 用小表作为驱动表
• MRR优化
  • 根据索引，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中
  • 将 read_rnd_buffer 中的 id 进行递增排序
  • 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回
  • set optimizer_switch=“mrr_cost_based=off”
• BKA优化
  • 将驱动表的数据分批放入join_buffer
  • set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;

Block Nested-Loop Join

• 被驱动表不走索引
• 驱动表被分块装入join_buffer，然后跟被驱动表的数据进行对比
• 如果驱动表的数据可以全部装入join_buffer，那么扫描次数为 N + M，内存对比次数N * M
• 如果驱动表的数据分K次装入join_buffer，那么扫描次数为 N + k * M，内存对比次数N * M
• 用小表作为驱动表
• 尽量优化，不使用
• 可能会多次扫描被驱动表，占用磁盘 IO 资源
• 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会 占用非常多的 CPU 资源
• 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率