Mysql join联表及id自增实例解析
join的写法
如果用leftjoin左边的表一定是驱动表吗？两个表的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); 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*/
执行结果：
由于表b没有索引，使用的是BlockNextedLoopJoin(BNL)算法
- 把表a的内容读入join_buffer中，因为select*，所以字段f1，f2都被放入
- 顺序扫描b，对于每一行数据，判断join条件是否满足，满足条件的记录，作为结果集的一行，如果有where子句，判断where部分满足条件后再返回。
- 表b扫描完成后，对于没有匹配的表a的行，用null补上，放到结果集中。
Q2语句中，explain结果：
b为驱动表，如果一条语句EXTRA字段什么都没有的话，就是IndexNested_LoopJoin算法，因此流程是：
顺序扫描b，每一行用b.f1到a中去查，匹配a.f2=b.f2是否满足，作为结果集返回。
Q1与Q2执行流程的差异是因为优化器基于Q2这个查询语义做了优化：在mysql里，null跟任何值执行等值判断和不等值判断的结果都是null，包括selectnull=null也返回null。
在Q2中，wherea.f2=b.f2表示，查询结果里不会包含b.f2是null的行，这样leftjoin语义就是找到两个表里f1f2对应相同的行，如果a存在而b匹配不到，就放弃。因此优化器把这条语句的leftjoin改写成了join，因为a的f1有索引，就把b作为驱动表，这样可以用NLJ算法，所以在使用leftjoin时，左边的表不一定是驱动表。
如果需要leftjoin的语义，就不能把被驱动表的字段放在where条件里做等值判断或不等值判断，必须写在on里面。
NestedLoopJoin的性能问题
BLN算法的执行逻辑
- 将驱动表的数据全部读入join_buffer中，里面是无序数组。
- 顺序遍历被驱动表的所有行，每一行都跟join_buffer做匹配，成功则作为结果集的一部分返回。
SimpleNestedLoopJoin算法逻辑是：顺序去除驱动表的每一行数据，到被驱动表做全表匹配。
两者差异：
在对被驱动表做全表扫描时，如果数据没有在bufferpool中，需要等待部分数据从磁盘读入。会影响正常业务的bufferpool命中率，而且会对被驱动表做多次访问，更容易将这些数据页放到bufferpool头部。所以BNL算法性能会更好。自增id
mysql中自增id定义了初始值，不停的增长，但是有上限，2^32-1，自增的id用完了会怎么样呢。
表定义的自增值达到上限后，再申请下一个id时，得到的值保持不变。再次插入时会报主键冲突错误。所以在建表时，如果有频繁的增删改时，就应该创建8个字节的bigintunsigned。
innodb系统自增row_id
如果创建了Innodb表没有指定主键，那么innodb会创建一个不可见的，长度为6个字节的row_id，所有无主键的innodb表，每插入一行数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，然后自增1。
实际上，代码实现时，row_id是一个长度为8字节的无符号长整形，但是innodb在设计时，给row_id只是6个字节的长度，这样写道数据时只放了最后6个字节。所以：
- row_id写入表的范围是0到2^48-1;
- 当达到最大时，如果再有插入数据的行为来申请row_id，拿到以后再去最后6个字节就是0，然后继续循环。
- 再innodb的逻辑里，达到最大后循环，新数据会覆盖已经存在的数据。
从这个角度看，我们应该主动创建自增主键，这样达到上限后，插入数据会报错。数据的可靠性会更加有保障。
XID
redolog和binlog相互配合的时候，它们有一个共同的字段就是xid，在mysql中对应事务的。xid最大时2^64次方，用尽只存在理论。
thread_id
系统保存了全局变量thread_id_counter，每新建一个连接，就将thread_id_counter赋值给这个新连接的线程变量。thread_id_counter定义的大小是4个字节，因此到2^32-1就会重置为0，然后继续增加。但是showprocesslist里不会看到两个相同的thread_id，这是因为mysql设计了一个唯一数组逻辑，给新线程分配thread_id的时候：
do {
new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。