Index Nested-Loop Join(NLJ)
从驱动表上逐行读取数据，在被驱动表上通过索引匹配数据，假设驱动表N表数据，被驱动表M条数据
| Index Nested-Loop Join | Batched Key Access(BKA，NLJ算法的优化) |
|---|---|
- NLJ算法，每条数据都需要被驱动表两个索引上走一遍，近似时间复杂度为：
\[N + N*2*log_2M
\]
? 其中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中