部分问题扩展

join写法

问题：

1. 如果用left join的话，左边的表一定是驱动表吗？(与straight_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上有索引。  
-- 分别往两个表中插入6条记录，在表a和b中同时存在的数据有4行。

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*/  
-- 这两个left join语句的语义逻辑并不相同  
-- 下方表格分别是Q1和Q2的结果

f1	f2	f1	f2		f1	f2	f1	f2
3	3	3	3		3	3	3	3
4	4	4	4		4	4	4	4
5	5	5	5		5	5	5	5
6	6	6	6		6	6	6	6
1	1	null	null
2	2	null	null

• 语句Q1返回的结果集是6行，表a中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表b的各个字段值填成NULL。
• 语句Q2返回的是4行。从逻辑上理解，最后的两行，由于表b中没有匹配的字段，结果集里面b.f2的值是空，不满足where部分的条件判断，因此不能作为结果集的一部分。

Q1语句分析

Q1的explain结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	simple	a	null	all	null	null	null	null	6	100.00	null
1	simple	b	null	all	null	null	null	null	6	100.00	Using where; Using join buffer (Block Nested Loop)

• 驱动表是表a，被驱动表是表b；
• 由于表b的f1字段上没有索引，所以使用的是Block Nested Loop Join(BNL)算法。

Q1语句执行流程：

1. 把表a的内容读入join_buffer中。因为是select *，所以字段f1和f2都被放入join_buffer了。
2. 顺序扫描表b，对于每一行数据，判断join条件((a.f1=b.f1)and(a.f1=1))是否满足，满足条件的记录，作为结果集的一行返回。如果语句中有where子句，需要先判断where部分满足条件后，再返回。
3. 表b扫描完成后，对于没有被匹配的表a的行(在这个例子中就是(1,1)、(2,2)这两行)，把剩余字段不上null，再放入结果集中。

Q1语句是以表a为驱动表，执行效果与使用straight_join一样。

Q2语句分析

Q2explain结果:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	simple	b	null	all	null	null	null	null	6	100.00	Using where
1	simple	a	null	ref	f1	f1	5	test.b.f1	1	16.67	Using where

Q2语句是以表b为驱动表的：如果一条join语句的Extra字段什么都没写，表示使用的是Index Nested-Loop Join(NLJ)算法，使用了被驱动表的索引a.f1。

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

在MySQL里，NULL跟任何值执行等值判断和不等值判断的结果，都是NULL。(select NULL = NULL的结果也是返回NULL)。

因此语句Q2里面where a.f2=b.f2就表示，查询结果里面不包含b.f2是NULL的行(不匹配)，这样这个left join的语义就是”找到这两个表里面，f1、f2对应相同的行。对于表a中存在，而表b中匹配不到的行，就放弃”。

这条语句虽然用的是left join，但语义跟join是一致的。因此优化器就把这条语句的left join改写成join，然后因为表a的f1上有索引，就把表b作为驱动表，这样就可以用上NLJ算法。

执行explain之后，再执行show warnings查看改写的结果：

Level	Code	Message
Note	1003	/*select#1*/ select test.a.f1 AS ‘f1’, test.a.f2 AS f2, test.b.f1 AS f1, test.b.f2 AS f2 from test.a join test.b where ((test.a.f1=test.b.f1)and(test.a.f2=test.b.f2))

---

即使在SQL语句中写成left join，执行过程还是有可能不是从左到右连接的。使用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*/

根据explain和show warnings发现这两条语句都被改写成:
select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

跟Q2相同

执行计划一样，join语句是否将判断条件全部放在on部分是没有区别的。

Simple Nested Loop Join的性能问题

join语句使用不同的算法，对语句的性能影响会很大。

MySQL-join执行原理

虽然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算法的执行逻辑：
顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。

其实也是把数据读到内存里，然后按照匹配条件进行判断，但性能比BNL差距依然很大。

BNL算法性能比SNL好的原因：

1. 在对被驱动表做全表扫描的时候，如果数据没有在Buffer Pool中，就需要等待着部分数据从磁盘读入；从磁盘读入数据到内存，会影响到正常业务的Buffer Pool命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到Buffer Pool的头部。

   MySQL-join语句优化

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

distinct和group by的性能

[MySQL-内部临时表应用-union与group by](/MySQL/内部临时表应用-union与group by)

select a from t group by a order by null;  
select distinct a from t;  
-- 表t的字段a上没有索引

标准的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. 遍历完成后，将临时表作为结果集返回给客户端 。

备库自增主键问题

在binlog_format=statement时，语句a先获取id=1，语句b获取id=2；语句b提交，写binlog，然后语句a再提交写binlog。备库重放不会不一致。

create table t(id int auto_increment primary key);  
insert into t values(null);

begin
/*!*/;
# at 486
# at 518
#190219 hh:mm:ss server id 1 end_log_pos 518 CRC32 0x6364946b intvar
set insert_id=1/*!*/;
#190219 hh:mm:ss server id 1 end_log_pos 618 CRC32 0xb6277773 query thread_id=4 exec_time=0 error_code=0
set timestamp=1550572969/*!*/;
insert into t values(null)

insert语句的binlog，在insert语句之前，还有一句set insert_id=1。这条命令的意思是，这个线程里下一次需要用到自增值的时候，不论当前表的自增值是多少，固定用1这个值。

这个set insert_id语句是固定跟在insert语句之前的。在备库上语句b用到的insert_id依然是2，跟主库相同。即使两个insert语句在主备库的执行顺序不同，自增主键字段的值也不会不一致。

set insert_id=2;  
语句b;  
set insert_id=1;  
语句a;