mysql主动自增可以_Mysql join联表及id自增实例解析

news/2025/2/13 21:38:22/

join的写法

如果用left join 左边的表一定是驱动表吗?两个表的join包含多个条件的等值匹配,都要写道on还是只把一个写到on,其余写道where部分?

createtablea(f1int,f2int,index(f1))engine=innodb;

createtableb(f1int,f2int)engine=innodb;

insertintoavalues(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

insertintobvalues(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

select*fromaleftjoinbon(a.f1=b.f1)and(a.f2=b.f2);/*Q1*/

select*fromaleftjoinbon(a.f1=b.f1)where(a.f2=b.f2);/*Q2*/

执行结果:

wolpal2zlq4.jpg

由于表b没有索引,使用的是Block Nexted Loop Join(BNL)算法

把表a的内容读入join_buffer中,因为select * ,所以字段f1,f2都被放入

顺序扫描b,对于每一行数据,判断join条件是否满足,满足条件的记录,作为结果集的一行,如果有where子句,判断where部分满足条件后再返回。

表b扫描完成后,对于没有匹配的表a的行,用null补上,放到结果集中。

e3isudelg0i.jpg

Q2语句中,explain结果:

c0dfbff20c027e893abba23e5d0e3f0f.png

b为驱动表,如果一条语句EXTRA字段什么都没有的话,就是Index Nested_Loop Join算法,因此流程是:

顺序扫描b,每一行用b.f1到a中去查,匹配a.f2=b.f2是否满足,作为结果集返回。

Q1与Q2执行流程的差异是因为优化器基于Q2这个查询语义做了优化:在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,因为a的f1有索引,就把b作为驱动表,这样可以用NLJ算法,所以在使用left join时,左边的表不一定是驱动表。

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

Nested Loop Join的性能问题

BLN算法的执行逻辑

将驱动表的数据全部读入join_buffer中,里面是无序数组。

顺序遍历被驱动表的所有行,每一行都跟join_buffer做匹配,成功则作为结果集的一部分返回。

Simple Nested Loop Join算法逻辑是:顺序去除驱动表的每一行数据,到被驱动表做全表匹配。

两者差异:

在对被驱动表做全表扫描时,如果数据没有在buffer pool中,需要等待部分数据从磁盘读入。会影响正常业务的buffer pool命中率,而且会对被驱动表做多次访问,更容易将这些数据页放到buffer pool头部。所以BNL算法性能会更好。自增id

mysql中自增id定义了初始值,不停的增长,但是有上限,2^32-1,自增的id用完了会怎么样呢。

表定义的自增值达到上限后,再申请下一个id时,得到的值保持不变。再次插入时会报主键冲突错误。所以在建表时,如果有频繁的增删改时,就应该创建8个字节的bigint unsigned。

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

redo log 和 binlog相互配合的时候,它们有一个共同的字段就是xid,在mysql中对应事务的。xid最大时2^64次方,用尽只存在理论。

thread_id

系统保存了全局变量thread_id_counter,每新建一个连接,就将thread_id_counter赋值给这个新连接的线程变量。thread_id_counter定义的大小是4个字节,因此到2^32-1就会重置为0,然后继续增加。但是show processlist里不会看到两个相同的thread_id,这是因为mysql设计了一个唯一数组逻辑,给新线程分配thread_id的时候:

do{

new_id=thread_id_counter++;

}while(!thread_ids.insert_unique(new_id).second);

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持聚米学院。


http://www.ppmy.cn/news/858359.html

相关文章

java创建map对象吗_[Java教程]JavaScript创建Map对象(转)

/* * MAP对象,实现MAP功能 * * 接口: * size() 获取MAP元素个数 * isEmpty() 判断MAP是否为空 * clear() 删除MAP所有元素 * put(key, value) 向MAP中增加元素(key, value) * remove(key) 删除指定KEY的元素,成功返回True&am…

Unidbg适合做算法还原吗?(一)

Unidbg是模拟执行的强大工具,这是毋庸置疑的,可是,它在算法还原上是否依然是得力的助手?或者说,当我们想要对一个样本进行算法还原而非模拟执行呢,需要关注Unidbg吗? 我们看一下AB两人的辩论&a…

php基本程序打印方法,PHP教程.程序控制-PHP教程,PHP基础

程序控制 本章深入php内部,讲述如何使用函数、表达式和语句以实现对程序的控制。 前面的章节初步介绍了怎样操作数据,如果我们将操作数和操作符看作是构筑元件的话,那么它们组合起来即可形成表达式。进一步讲,表达式可以构成语句&…

matlab保存bln文件,气象万千(冯锦明课题组)-软件程序

气象研究中几种流行的数据分析与绘图软件 GRADS - 简捷易用,但功能较弱,尤其是数据处理功能。 NCL - 针对气象,专业功能强大,实例丰富。函数名难记,部分功能灵活性偏弱。 IDL - 针对地学,功能强大。使用便捷…

global mapper 导入bln文件和tif文件

打开数据文件,注意在bln文件中,这个投影一定要选对。这样就可以将两个文件的信息叠加在一起了。

Golden Software BLN文件格式

Golden Software的BLN文件一种ASCII格式文件,用于存储包括多边形、多段线和点在内的地理信息。空间信息只关心空间中的对象的位置(即它们的坐标),而不是它们的属性(例如,行或填充样式,使用的标记符号,文本标签等等)。尽…

丰密面单打印 隐私面单 C#开发

目录 前言**图一****图二** 1.完成前期准备工作2.API接口3.请求参数(Headers)4.请求参数(Body)5.返回参数(Return)6.请求报文(示例)7.成功返回报文(示例)8.失…

CUDA编译问题-Dual BLN

CUDA编译问题-Dual BLN 问题: Fix “identifier “__builtin_is_constant_evaluated” is undefined” error on Arch Linux The Arch Linux has shipped gcc 9, but the newest CUDA V10.1.168 still only supports gcc 8. So I met following error after upgra…