mysql实战45讲--- 44 答疑（三）--join的写法/Simple nested loop join的性能问题/Distinct和group by的性能/备库自增主键问题

44 答疑（三）

Join的写法

35节介绍了join执行顺序，加了straight_join，两个问题：

--1 如果用left join，左边的表一定是驱动表吗

--2 如果两个表的join包含多个条件的等值匹配，是都要写到on里面呢，还是只把一个写到on，把其他的条件写到where部分？

create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表a和b都有两个字段f1和f2，不同的是表a的字段上有f1的索引，两种写法

select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

可以看到q1查询返回的数据集是6行，表a中没有满足匹配条件的记录，查询结果中也会返回，并把b的各个字段为null，语句q2返回4行，where是过滤条件，最后执行。

--驱动表是a，被驱动表是b

--由于表b的f1字段上没有索引，所以使用的是block nested loop(BNL)算法，具体执行流程

---1 把表a的内如读入join_buffer，这里select *，所有字段f1和f2放入了join_buffer

---2 顺序扫描表b，对于每一行数据，判断join条件（也就是a.f1=b.f1 and a.f2=b.f2）是否满足，满足条件的记录，作为结果集的一行返回，如果语句中有where条件，需要先判断where部分，满足条件后在返回

--3 表b扫描完成后，对于没有被匹配的表a的行，把剩余字段补上null，放入结果集中。

看q2的执行计划

这个语句以表b为驱动表，如果一个join语句的extra中扫描都没有写的话，表示使用的是index nested-loop join（NLJ）算法。Q2的执行流程

--顺序扫描表b，每一行用b.f1到a中去查，匹配到记录后判断a.f2=b.f2，满足条件的话就作为结果集的一部分返回。

在语句q2里面，where a.f2=b.f2表示，结果集不会包含b.f2=null的行，从语义上跟join一致，优化器改成了join，然后因为表a上f1有索引，就把b作为驱动表，a为被驱动表，使用nlj算法，使用show warnings可以看到优化器改写之后的语句，跟参数sql_mode有关。

在left join中，左边的表不一定是驱动表。

如果需要left join的语义，就不能把被驱动表的字段放在where条件里面做等值判断或者不等值判断，必须都写在on里面。

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/

2个语句的执行计划是一样的。

Simple nested loop join的性能问题

35节中提到bnl算法和simple nested loop join算法都是需要判断M*N次（m，n分别是join的表的行数），但是在simple nested loop join算法的每轮判断都需要走全表扫描，因此性能上bnl算法执行起来会快很多。

BNL算法的执行逻辑：

--1首先，将驱动表的数据全部读入内存join_buffer中，这里join_buffer是无序数组

--2 然后，顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回。

Simple nested loop join算法执行的逻辑是：顺序取出驱动表中的每一行数据，到被驱动表中做全表扫描匹配，匹配成功则作为结果集的一部分返回。

Simple nested loop join算法其实也是把数据读入内存里，然后按照条件进行判断，为什么性能差距胡这么大

--解释这个问题，需要用到mysql中索引结构和buffer pool的相关知识

--1 在对被驱动表做全表扫描的时候，如果数据没有在buffer pool中，就需要等待这部分数据从磁盘读入：从磁盘读取数据到内存，会影响正常业务的buffer pool命中率，

而且这个算法会对驱动表的数据做多次访问，更容易将这些数据页放到buffer pool的头部，

--2 即使被驱动表数据都在内存中，每次查找”下一个记录操作”，都是类似指针操作。而join_buffer中是数组，遍历的成本更低。

所以说，BNL算法的性能会更好些

Distinct和group by的性能

如果只需要去重，不需要执行聚合函数，distinct和group by那种效率更高？

select a from t group by a order by null;

select distinct a from t;

首先要说明，这种group by的写法，并不是sql标准写法，标准的group by语句是需要在select部分加一个聚合函数

select a,count(*) from t group by a order by null;

按照字段a分组，计算每组的a出现的次数，在这个结果里，由于做的是聚合计算，相同的a只出现一次。没有了count(*)之后，就不再需要执行”计算总数”的逻辑第一条语句就变成，

按照字段a做分组，相同的a的值只返回一行，而这就是distinct的语义，所以不需要执行聚合函数时，distinct和group by这两条语句的语义和执行流程是相同，因此性能也相同。

--1 创建一个临时表，临时表有一个字段a，并且这个字段a上创建一个唯一索引

--2 遍历表t，依次取出数据插入临时表中：

---如果发现唯一键冲突，就跳过

---否则插入成功

--3 遍历完成后，将临时表作为结果集返回给客户端。

备库自增主键问题

create table t(id int auto_increment primary key);

insert into t values(null);

在binlog_format=statement时，语句a先获取id=1，然后语句b获取id=2，b提交，写binlog，然后a在提交写binlog，如果在备库重放，是不是会发生语句b获取的id为1，语句a获取的id为2的情况？

通过解析binlog文件发现，在insert语句之前，有set insert_id=1，这条命令的意思，这个线程下一次需要用到自增值的时候，不论当前表的自增值是多少，固定用1这个值，所以如果b语句先提交，在binlog就是这样

SET INSERT_ID=2;

语句 B；

SET INSERT_ID=1;

语句 A；