Index Nested-Loop Join(NLJ)

从驱动表上逐行读取数据，在被驱动表上通过索引匹配数据，假设驱动表N表数据，被驱动表M条数据

Index Nested-Loop Join	Batched Key Access(BKA，NLJ算法的优化)

• NLJ算法，每条数据都需要被驱动表两个索引上走一遍，近似时间复杂度为：

? 其中N对最终结果影响较大，所以，该算法建议小表作为驱动表

• 使用BKA优化后，使用批量数据在被驱动表上进行匹配，近似时间复杂度为：

  \[N + K*2*log_2M \]

  其中K为驱动表分段数，join buffer可能一次性放不下所有数据。如果使用 BKA 优化算的话，设置如下：

  -- BKA 算法的优化要依赖于MRR(Multi-Range Read)，也需要开启
  -- MRR的本质是二级索引获取尽可能多的主键id，经过排序后，在主键索引上顺序查找数据，减少了随机磁盘IO和主键索引访问次数
  set optimizer_switch=‘mrr=on,mrr_cost_based=on,batched_key_access=on‘;
  

  

  注意：上图中如果结果集需要对a进行排序，使用MRR优化，经过排序的主键回表后得到的结果需要进行二次排序，可能得不偿失

Block Nested-Loop Join(BNL)

把驱动表中数据读入内存join_buffer中，再把被驱动表中每一行数据取出来与join_buffer中数据比对（即使被驱动表上有过滤条件）

• 总的数据扫描行数为：N+K*M，K为驱动表分段数量，因为join_buffer可能不足以一次放下驱动表数据
• 内存判断次数为：N * M
• 驱动表分段会导致被驱动表多次读取
• 优化方案
  • 在被驱动表上建索引，直接转成BKA算法。不适合建索引的表，可通过有索引的临时表来触发BKA算法，提升查询性能
  • 可通过应用端配合模拟hash join，即把数据缓存的应用端hash结构中

驱动表选择

从以上两种算法的影响因子来看N对性能影响较大，因此建议小表作为驱动表。更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

临时表

create temporary table …

临时表特点：

• 只对创建它的session可见
• 临时表可以与普通表同名，且同名情况下默认访问的是临时表（table_def_key在“库名+表名”的基础上又加入了“server_id+thread_id”）
• show tables不显示临时表
• 不需要担心数据删除问题，自动回收
• 内存临时表的大小由tmp_table_size决定，默认16M，超过后使用磁盘临时表
• 建议使用binlog_format=row，临时表的操作不记录到 binlog 中
• 只能使用alter table temp_t rename to temp_t2，不能使用rename table temp_t2 to temp_t3（基于“库名+表名”查找表）

应用场景：分库分表系统的跨库查询，例如：将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上

select v from ht where k >= M order by t_modified desc limit 100;

临时表当前线程可见，为什么写binlog

create table t_normal(id int primary key, c int)engine=innodb;

create temporary table temp_t like t_normal;

insert into temp_t values(1,1);/

insert into t_normal select * from temp_t;

如果binlog_format=statment/mixed，binlog不记录临时表的操作，从库只记录以下语句

create table t_normal(id int primary key, c int)engine=innodb;

insert into t_normal select * from temp_t;

上述两条语句回放， insert into t_normal 的时候，就会报错“表 temp_t 不存在”。因此，需要在binlog中记录临时表的操作，且最后还要写一条drop temporary table在从库上清除临时表。所以，建议binlog_format=row，因为insert时会记录操作的数据，临时表操作就不需要记录在binlog中

Mysql之join机制

原文：https://www.cnblogs.com/sheung/p/14622666.html