Mysql join联表及id自增实例解析

join的写法

如果用left join 左边的表一定是驱动表吗?两个表的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没有索引,使用的是Block Nexted Loop Join(BNL)算法

  • 把表a的内容读入join_buffer中,因为select * ,所以字段f1,f2都被放入
  • 顺序扫描b,对于每一行数据,判断join条件是否满足,满足条件的记录,作为结果集的一行,如果有where子句,判断where部分满足条件后再返回。
  • 表b扫描完成后,对于没有匹配的表a的行,用null补上,放到结果集中。

Q2语句中,explain结果:

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);

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

(0)

相关推荐

  • Mysql 8.0.18 hash join测试(推荐)

    Hash Join Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效. 下面通过实例代码给大家介绍Mysql 8.0.18 hash join测试,具体内容如下所示: CREATE TABLE COLUMNS_hj as select * from information_schema.`COLUMNS`; INSERT INTO COLUMNS SELECT * FROM COLUMNS; -- 最后一次插入25万行 CREATE TABLE COLUM

  • MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

    在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦. 在优化这个语句之前,我们先了解下SQL查询的基本执行过程: 1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析 2.检查权限.MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(quer

  • MySQL 8.0.18 稳定版发布! Hash Join如期而至

    MySQL 8.0.18 稳定版(GA)已于昨日正式发布,Hash Join 也如期而至. 快速浏览一下这个版本的亮点! 1.Hash Join Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效. 2.EXPLAIN ANALYZE EXPLAIN ANALYZE 将运行查询,然后生成 EXPLAIN 输出,以及有关优化程序估计如何与实际执行相匹配的其他信息. 3.创建用户时可以随机生成密码 为 CREATE USER, ALTER USER和 SET P

  • MySQL 8.0.18 Hash Join不支持left/right join左右连接问题

    在MySQL 8.0.18中,增加了Hash Join新功能,它适用于未创建索引的字段,做等值关联查询.在之前的版本里,如果连接的字段没有创建索引,查询速度会是非常慢的,优化器会采用BNL(块嵌套)算法. Hash Join算法是把一张小表数据存储到内存中的哈希表里,并逐行去匹配大表中的数据,计算哈希值并把符合条件的数据,从内存中返回客户端. 用sysbench生成4张表,并删除默认的k字段索引. 我们用explain format=tree命令可以查看到已经使用到hash join算法. 但目

  • 深入理解mysql的自连接和join关联

    一.mysql自连接 mysql有时在信息查询时需要进行对自身连接(自连接),所以我们需要为表定义别名.我们举例说明,下面是商品采购表,我们需要找到采购价格比惠惠高的所有信息. 一般情况我们看到这张表我们第一时间用语句进行操作: SELECT * FROM shoping WHERE price>27 可想而知,这是有多么简单,假设你并不知道数据库表详细数据或者数据量相当庞大呢?作为一个数据库管理员,我们就要用别的方式迅速找出所需要的数据. 分步查询 最简单的一种方式,也是最容易想到操作: SE

  • mysql中各种常见join连表查询实例总结

    本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns

  • MySQL联表查询基本操作之left-join常见的坑

    概述 对于中小体量的项目而言,联表查询是再常见不过的操作了,尤其是在做报表的时候.然而校对数据的时候,您发现坑了吗?本篇文章就 mysql 常用联表查询复现常见的坑. 基础环境 建表语句 DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(50) DEFAULT NULL COMMENT '角色名', PRIMARY KEY (`i

  • Mysql巧用join优化sql的方法详解

    0. 准备相关表来进行接下来的测试 相关建表语句请看:https://github.com/YangBaohust/my_sql user1表,取经组 +----+-----------+-----------------+---------------------------------+ | id | user_name | comment | mobile | +----+-----------+-----------------+-----------------------------

  • mysql中left join设置条件在on与where时的用法区别分析

    本文实例讲述了mysql中left join设置条件在on与where时的用法区别.分享给大家供大家参考,具体如下: 一.首先我们准备两张表来进行测试. CREATE TABLE `a` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(32) DEFAULT '' COMMENT '名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET

  • MySQL 8.0 新特性之哈希连接(Hash Join)

    MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能.其中最引人注目的莫过于多表连接查询支持 hash join 方式了.我们先来看看官方的描述: MySQL 实现了用于内连接查询的 hash join 方式.例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询: SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; Hash join 不需要索引的支

随机推荐