精华内容
下载资源
问答
  • 连接
    万次阅读 多人点赞
    2021-08-13 11:58:37

    数据库连表方式

    • 内连接 :innerinner join
    • 外连接 :outer join
      • 左外连接 :left outer join
      • 左连接 :left join
      • 右外连接 right outer join
      • 右连接: right join
    • 全连接 full joinunion

    准备

    现在有2张表,A表和B表,数据和表结构如下
    在这里插入图片描述

    内连接

    内连接查询的是两张表的并集,也就是A表和B表都必须有数据才能查询出来;
    在这里插入图片描述

    以下三个查询的结果是一样的,针对上面的表数据,能查询出id为1、2、3的数据

    -- join
    select * from A  join B on A.id = B.id 
    
    -- inner join
    select * from A inner join B on A.id = B.id 
    
    -- 逗号的连表方式就是内连接
    select * from A , B where A.id = B.id 
    

    左外连接 和 左连接

    是以左表为基础,根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出,而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接,是外连接的一种。
    在这里插入图片描述

    下面2个查询的结果是一样的,针对上面的表数据,能查询出id为1、2、3、4的数据

    -- left join
    select * from A left join B on  A.id = B.id 
    
    -- left outer join
    select * from A left outer join B on  A.id = B.id 
    

    右外连接 和 右连接

    是以右表为基础,根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出,而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接,是外连接的一种。
    在这里插入图片描述

    下面2个查询的结果是一样的,针对上面的表数据,能查询出id为1、2、3的数据

    -- right join
    select * from A right join B on  A.id = B.id 
    
    -- right outer join
    select * from A right outer join B on  A.id = B.id 
    

    全连接

    全连接显示两侧表中所有满足检索条件的行。
    在这里插入图片描述

    oracle的全连接

    oracle的全连接查询可以直接用full join,

    select * from a full join b on a.id = b.id
    

    mysql的全连接

    mysql中没有full join,mysql可以使用union实现全连接;

    select * from a left join b on a.id = b.id
    union
    select * from a right join b on a.id = b.id
    

    更多相关内容
  • win10远程桌面连接工具

    千次下载 热门讨论 2018-03-26 13:50:16
    用于win10远程桌面连接工具,方便多用户远程连接win10
  • 什么是连接查询?笛卡尔积如何避免?内连接和外连接的概念是什么?表连接的原理是什么?Simple Nested-Loop Join、Index Nested-Loop Join、 Block Nested-Loop Join分别是什么概念?Nested-Loop Join和Block Nested...

    一、表连接的简介

    create table t1(m1 int, n1 char(1));
    create table t2(m2 int, n2 char(1));
    
    insert into t1 values(1,'a'),(2,'b'),(3,'c');
    insert into t2 values(2,'b'),(3,'c'),(4,'d');
    

    t1表数据如下

    t2表数据如下

    我们知道,所谓表连接就是把各个表中的记录都取出来进行依次匹配,最后把匹配组合的记录一起发送给客户端。比如下面把t1表和t2表连接起来的过程如下图

    什么是连接查询?

    比如上面t1t2表的记录连接起来组成一个新的更大的记录,这个查询过程就称为连接查询。

    什么是笛卡尔积?

      如果连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配组合的记录,那么这样的结果集就可以称为笛卡尔积。

    # 这三者效果一样,只要不写条件,就产生笛卡尔积,结果集的数量一样。
    select * from t1, t2;
    # 内连接
    select * from t1 inner join t2;
    # 全连接
    select * from t1 cross join t2;
    

    t1中有3条记录,表t2中也有3条记录,两个表连接后的笛卡尔积就有3 x 3 = 9条记录,只要把两个表的记录数相乘,就能得到笛卡尔积的数量。


    二、表连接的过程

      笛卡尔积也是一个很大的问题,不加限制条件,结果集的数量就会很大。比如你在开发过程中需要2个表的连接,表120000条记录,表210000条记录,表3100条记录,那么3张表连接后产生的笛卡尔积就有20000 x 10000 x 100 = 20000000000条记录(两百亿条记录)。

      所以在连接时过滤掉特定的记录组合是很有必要的,为了避免笛卡尔积,一定要在表连接的时候加上条件!

    下面来看一下有过滤条件的表连接的执行过程。

    # 下面两种写法都一样,执行效率没有区别,看看自己习惯于哪种写法
    select * from t1 join t2 on t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';
    
    select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';
    

    注意:先说明条件的概念,要区分什么是连接条件过滤条件!!

    连接条件是针对两张表而言的,比如t1.m1 = t2.m2t1.n1 > t2.n2,表达式两边是两个表的字段比较。

    过滤条件是针对单表而言的,比如t1.m1 > 1是针对t1表的过滤条件,t2.n2 < 'd'是针对t2表的过滤条件。

    1.首先确定第一个需要查询的表,这个表称之为驱动表

      在单表中选择代价最小的查询方式,简单理解就是走合适的索引即可。此处假设使用t1作为驱动表,那么就需要到t1表中找满足过滤条件t1.m1 > 1的记录,因为表中的数据太少,我们也没在表上建立索引,所以此处查询t1表的查询的方式就是all,也就是采用全表扫描的方式执行单表查询,筛选出符合条件的驱动表记录。

    这里筛选出来的t1驱动表记录有2条。

    2.从第1步中驱动表筛选出来的每一条记录,都要到t2表中查询匹配记录。

      匹配记录就是找到满足连接条件和过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录,所以t2表也可以称为被驱动表。上一步从驱动表筛选出了2条记录,意味着需要从头到尾将t2表查询2次,此时就得看两表之间的连接条件了,这里就是t1.m1 = t2.m2

      对于从t1表查询得到的第一条记录,而这条记录t1.m1=2,根据连接条件t1.m1 = t2.m2,就相当于在t2表加上过滤条件t2.m2 = 2,此时t2表相当于有了两个过滤条件t2.m2 = 2 and t2.n2 < 'd',然后到t2表执行单表查询,每当匹配到满足条件的一条记录后立即返回给MySQL客户端,以此类推。

      所以整个连接查询的执行过程如下:

    最后连接查询的结果只有2条记录。

    如果把t1.m1 > 1这个过滤条件去掉了,那么从t1表查出的记录就有3条,就需要从头到尾扫3t2表了。

    其实这个流程的套路就是用伪代码说明非常合适,你细品,看懂这个伪代码,你就理解了表连接的步骤。

    for  筛选 驱动表 满足条件的每条记录 {
    	for 筛选 被驱动表 满足条件的每条记录 {
    		发送到MySQL客户端;
    	}
    }
    

    从这个伪代码可以看出,驱动表的每一条记录都会尝试遍历被驱动表的每条记录并匹配连接,每成功连接一条就返回给MySQL客户端。


    总结:

    1.在两表连接查询中,驱动表只需访问一次,而被驱动表可能需要访问多次。

    2.并不是将所有满足过滤条件的驱动表记录先查询出来放到一个地方,然后再去被驱动表查询的(因为如果满足过滤条件的驱动表很大,需要的临时存储空间就会非常大)。而是每获得一条满足过滤条件的驱动表记录,就立即到被驱动表中查询匹配的记录。


    三、内连接和外连接

    1.内连接

    上面第二节所讲的,都是内连接。

    先建立2张表,后续根据这2张表来讲解。

    CREATE TABLE student (
        stu_no INT NOT NULL AUTO_INCREMENT COMMENT '学号',
        name VARCHAR(5) COMMENT '姓名',
        major VARCHAR(30) COMMENT '专业',
        PRIMARY KEY (stu_no)
    ) Engine=InnoDB CHARSET=utf8mb4 COMMENT '学生信息表';
    
    CREATE TABLE score (
        stu_no INT COMMENT '学号',
        subject VARCHAR(30) COMMENT '科目',
        score TINYINT COMMENT '成绩',
        PRIMARY KEY (stu_no, subject)
    ) Engine=InnoDB CHARSET=utf8mb4 COMMENT '学生成绩表';
    

    插入一些数据

    insert into student values(20210901, '王大个', '软件工程');
    insert into student values(20210902, '刘帅哥', '物联网工程');
    insert into student values(20210903, '张小伟', '电子工程');
    
    insert into score values(20210901, '数据结构', 92);
    insert into score values(20210901, '计算机网络', 94);
    insert into score values(20210902, '计算机网络', 88);
    insert into score values(20210902, '数据结构', 80);
    

    student表数据如下:

    score表数据如下:

      如果想要把学生的成绩都查出来,就需要表连接(score表中没有姓名,所以不能只查score表),连接过程就是从student表取出记录,然后在score表中查找number相同的成绩记录,连接条件是student.stu_no= score.stu_no;

    select * from student join score where student.stu_no = score.stu_no;
    

    表连接的全部字段就在这里了,字段有点多,stu_no是重复的,我们修改一下

    select s1.stu_no, s1.name, s2.subject, s2.score from student as s1 join score as s2 on s1.stu_no = s2.stu_no;
    

      可以看到,学生的各科成绩都被查出来了。但是张小伟(学号为20210903的同学)因为缺考,在score表中没有记录。要是老师想查看所有学生的成绩(包括缺考的同学)该怎么办呢?也就是说,哪怕成绩为空,也要显示这位同学在这个表里面,咱们不能把他给踢了吧!

      这个问题就化为这个模型:对于驱动表中的某条记录,哪怕根据连接条件或者过滤条件在被驱动表中没有找到对应的记录,也还是要把该驱动表的记录加到结果集。

    这就是内连接的局限性。

    其实我们想要看到的结果集是这样的

    为了解决这个问题,就有了内连接外连接的区别。

      对于内连接来说,若驱动表中的记录按照连接条件或者过滤条件在被驱动表中找不到匹配的记录,则该记录不会加入到最后的结果集。

      前面提到的都是内连接,比如前面例子中,当t1.m1 = 2时,根据连接条件t1.m1 = t2.m2,在被驱动表中如果没有记录满足过滤条件t2.m2 = 2 and t2.n2 < 'd',驱动表的记录就不会加到最后的结果集。

    注意:我们说过,内连接语法有很多种。对于内连接来说,连接条件选择on或者where都可以,凡是不符合on子句或者where子句条件的记录都会被过滤掉,不会被连接,更不会在最后的结果集。

    # 以下三者效果一样,当用join进行内连接时,条件用on或者where连接都可以。
    select * from student join score on student.stu_no= score.stu_no;
    
    select * from student join score where student.stu_no= score.stu_no;
    
    select * from student, score where student.stu_no= score.stu_no;
    



    2.外连接

      对于外连接来说,即使驱动表中的记录按照连接条件和过滤条件在被驱动表中找不到匹配的记录,该记录也仍然需要加入到结果集。

    对于外连接来说,又有左(外)连接和右(外)连接的区别

    左(外)连接:选取左侧的表为驱动表。

    右(外)连接:选取右侧的表为驱动表。

    重点强调:对于内连接来说,选取哪个表为驱动表都没关系。而外连接的驱动表是固定的,左(外)连接的驱动表就是左边那个表,右(外)连接的驱动表就是右边那个表。

    左(外)连接的语法:

    比如要把t1表和t2表进行左连接查询。

    select * from t1 
    left [outer] join t2
    on 条件
    [where 普通过滤条件]
    
    # 注意这个on条件包括连接条件和驱动表与被驱动表的单表过滤条件。
    # []括号代表可以省略
    

    左表所有记录都会有,右表没有与之匹配的则用NULL填充。

    对于外连接来说,onwhere是有区别的。

      即使被驱动表中的记录无法匹配on子句的条件,该驱动表的记录仍然是满足条件的一条记录,对应被驱动表的各个字段用NULL填充。

      简言之,对于外连接,驱动表的记录一定都有,被驱动表不匹配就用NULL填充。

      而where过滤条件是在记录连接过后的普通过滤条件,即连接的记录会再次判断是否符合条件,不符合就从结果集中剔除。

    回到刚刚的问题,要把所有学生成绩显示出来(包括缺考的学生)

    select s1.stu_no, s1.name, s2.subject, s2.score from student as s1 
    left join 
    score as s2 
    on s1.stu_no = s2.stu_no;
    

    从上面结果集可以看出,虽然张小伟缺考,但是还是在结果集中,只不过对应的科目成绩用NULL填充。

    右(外)连接的语法

    select * from t1 
    right [outer] join t2
    on 条件
    [where 普通过滤条件]
    
    # 注意这个on条件包括连接条件和驱动表与被驱动表的单表过滤条件。
    # []括号代表可以省略
    

    右连接中,驱动表是右边的表,被驱动表是左边的表,右表所有记录都会有,左表没有与之匹配的则用NULL填充。这里就不举例了。


    四、表连接的原理

    1.简单的嵌套循环连接(Simple Nested-Loop Join)

      我们前边说过,对于两表连接来说,驱动表只会访问一遍,但被驱动表要被访问到好多遍,具体访问几遍取决于驱动表执行单表查询后满足条件的记录条数。

    假设t1表和t2表都没有索引,t1表和t2表内连接的大致过程如下:

    步骤1:选取驱动表t1,使用与驱动表t1相关的过滤条件,选取成本最低的单表访问方法来执行对驱动表的单表查询。(根据你的索引和记录数量,查询优化器会选择成本最低的访问方法,这里没有索引则全表扫描)

    步骤2:对上一步中查询驱动表得到的每一条满足条件的记录,都分别到被驱动表t2中查找匹配的记录。

    具体细节在第二节说过,这里就不细致展开。

      如果有第3个表t3进行连接的话,那么总体查询过程就是,查找t1表满足单表过滤条件的第一条记录,匹配连接t2表满足单表过滤条件的第一条记录(此时驱动表是t1,被驱动表是t2),然后匹配连接t3表满足单表过滤条件的第1条记录(此时驱动表是t2,被驱动表是t3),将这条满足所有条件的一条记录返回给MySQL客户端;前面条件不变,接着匹配连接t3表满足单表过滤条件的第2条记录…

    这个过程最适合用伪代码来说明了

    for  筛选t1表满足条件的每条记录 {
    	for 筛选t2表满足条件的每条记录 {
    		for 筛选t3表满足条件的每条记录 {
    			发送到MySQL客户端;
    		}
    	}
    }
    

      这个过程就像是一个嵌套的循环,驱动表每一条记录,都要从头到尾扫描一遍被驱动表去尝试匹配。这种连接执行方式称之为简单的嵌套循环连接(Simple Nested-Loop Join),这是比较笨拙的一种连接查询算法。

    注意:对于嵌套循环连接算法来说,每当从驱动表获得一条记录,就根据这条记录立即到被驱动表查一次,如果得到匹配连接记录,那就把这条连接的记录立即发送给MySQL客户端,而不是等查询完所有结果后才返回。然后再到被驱动表获取下一条符合条件的记录,直到被驱动表遍历完成,就切换到驱动表的下一条记录再次遍历被驱动表的每条记录,以此类推。

    2.基于索引的嵌套循环连接(Index Nested-Loop Join)

      在上一小节嵌套循环连接的步骤2中可能需要访问多次被驱动表,如果访问被驱动表的方式都是全表扫描,扫描次数就非常多。

      幸好MySQL优化器会找出所有可以用来执行该语句的方案,并会对比之后找出成本最低的方案,简单理解就是使用哪个索引最好。所以既然会多次访问被驱动表,索引好不好就是性能的瓶颈。

    查询被驱动表其实就相当于一次单表扫描,那么我们可以利用索引来加快查询速度。

    回到最开始介绍的t1表和t2表进行内连接的例子:

    select * from t1 join t2 on t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';
    

    这其实是嵌套循环连接算法执行的连接查询,再把上边那个查询执行过程拿下来给大家看一下:

    查询驱动表t1后的结果集中有2条记录,嵌套循环连接算法需要查询被驱动表2次:

    t1.m1 = 2时,去查询一遍t2表,对t2表的查询语句相当于:

    select * from t2 where t2.m2 = 2 and t2.n2 < 'd';
    

    t1.m1 = 3时,再去查询一遍t2表,此时对t2表的查询语句相当于:

    select * from t2 where t2.m2 = 3 and t2.n2 < 'd';
    

    可以看到,原来的t1.m1 = t2.m2这个涉及两个表的过滤条件在针对t2表进行查询时,选出t1表的一条记录之后,t2表的条件就已经确定了,即t2.m2 = 常数值,所以我们只需要优化对t2表的查询即可,上述两个对t2表的查询语句中利用到的列是m2n2列,我们可以进行如下尝试:

    1. m2列上建立索引,因为对m2列的条件是等值查找,比如t2.m2 = 2t2.m2 = 3等,所以可能使用到ref的访问方法,假设使用ref的访问方法去执行对t2表的查询的话,需要回表之后再判断t2.n2 < 'd'这个条件是否成立。

    2. n2列上建立索引,涉及到的条件是t2.n2 < 'd',可能用到range的访问方法,假设使用range的访问方法对t2表进行查询,需要在回表之后再判断在m2列的条件是否成立。

      假设m2n2列上都存在索引,那么就需要从这两个里面挑一个代价更低的索引来查询t2表。也有可能不使用m2n2列的索引,只有在非聚集索引 + 回表的代价比全表扫描的代价更低时才会使用索引。

      Index Nested-Loop JoinSimple Nested-Loop Join的不同就是被驱动表加了索引,后面只说Index Nested-Loop Join

    扩展思考:假设驱动表全表扫描,行数是N,被驱动表走索引,行数是M。那么

    • 1.每次在被驱动表查一行数据,则要先搜索索引,再回表查找主键索引。
    • 2.每次被驱动表查找次数是以2为底的M的对数,记为log M,所以在被驱动表上查一行的扫描次数是 2*log M(因为要回表查找利用到主键索引)。 驱动表执行过程就要扫描驱动表N行,然后对于每一行,到被驱动表上匹配一次。因此整个执行过程,查找的总次数是 N+N*2*log M
    • 3.显然N对扫描行数的影响更大,因此应该让小表来做驱动表。N扩大1000倍的话,扫描行数就会扩大 1000倍;而M扩大1000倍,扫描行数扩大不到10倍。

      总结:如果被驱动表可以使用索引,那么驱动表一定要选择数据量小的小表。

    3.基于块的嵌套循环连接(Block Nested-Loop Join)

      扫描一个表的过程其实是先把这个表从磁盘上加载到内存中,然后从内存中比较匹配条件是否满足。

      实际开发中的表可不像t1t2这种只有3条记录,几千万甚至几亿条记录的表到处都是。现在假设我们不能使用索引加快被驱动表的查询过程,所以对于驱动表的每一条记录,都需要对被驱动表进行全表扫描。而对被驱动表全表扫描时,可能表前面的记录还在内存中,表后边的记录可能还在磁盘上。等扫描到后边记录的时候,可能由于内存不足,所以需要把表前面的记录从内存中释放掉给正在扫描的记录腾地方,这样就非常消耗性能。

      采用嵌套循环连接算法的两表连接过程中,被驱动表是要被访问好多次的,所以我们得想办法,尽量减少被驱动表的访问次数。

      驱动表中满足筛选条件的有多少条记录,就得把被驱动表中的所有记录从磁盘上加载到内存中多少次。

      读磁盘代价太大,能不能在内存中操作呢?于是一个Join Buffer(连接缓冲区)的概念就出现了,Join Buffer就是执行连接查询前申请的一块固定大小的内存(默认256K),先把满足条件的若干条驱动表的记录装在这个Join Buffer中,然后开始扫描被驱动表,每一条被驱动表的记录一次性与Join Buffer中的所有记录进行匹配,因为匹配的过程都是在内存中完成的,所以这样可以显著减少被驱动表的I/O代价。使用Join Buffer的过程如下图所示:

      为什么Join Buffer要装驱动表而不是被驱动表呢?上面说过,小表作为驱动表,Join Buffer装小表更容易装得下,下一节会讲这个原因。

      其实很好记忆,想想笛卡尔积顺序也很奇妙。笛卡尔积的顺序就是一条被驱动表记录匹配多条驱动表记录的顺序,而不是一条驱动表记录去匹配被驱动表的记录的顺序,你看看这个顺序是不是很神奇,可以自行键两张表连接看看笛卡尔积,观察一下。

    笛卡尔积顺序是
    1 a 2 b
    2 b 2 b
    3 c 2 b
    .....
    而不是
    1 a 2 b
    1 a 3 c
    1 a 4 d
    ...
    你发现了吗?
    

      其实最好的情况是Join Buffer足够大,能容纳驱动表结果集中的所有记录,这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了Join Buffer的嵌套循环连接算法称之为基于块的嵌套连接(Block Nested-Loop Join)算法。

      这个Join Buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置,默认大小为262144字节(也就是256KB),最小可以设置为128字节。对于被驱动表,最好是为被驱动表加上效率高的索引,如果实在不能使用索引,可以尝试调大join_buffer_size的值来对连接查询进行优化。

      另外需要注意的是,只有满足条件的select中的列才会被放到Join Buffer中,所以再次提醒我们,最好不要把*作为查询列表,这样还可以在Join Buffer中放置更多的记录。

    4.Nested-Loop Join和Block Nested-Loop Join对比说明

      假设t1表的行数是Nt2表的行数是Mt1表是小表,即N < M

    Simple Nested-Loop Join算法:

    1. 驱动表的每一条记录都会去被驱动表逐一匹配,所以总的扫描行数是N * M(开头说了,扫描表就是把表从磁盘加载到内存中);
    2. 内存中的判断次数是N * M(扫描一次就会在内存中判断一次)。

    别纠结了,这种方法太笨了,不管选择哪个表作为驱动表,最后扫描和内存中判断的成本都是一样的。

    Index Nested-Loop Join算法

    该算法被驱动表的查询条件字段加上了合适的索引。

    1. 驱动表的每一条记录都会去被驱动表逐一匹配,所以总的扫描行数是N * log M(扫描行数不变,但是因为被驱动表有索引,扫描速度会大大增加);
    2. 内存中的判断次数是M * N(扫描一次就会在内存中判断一次)。

    Block Nested-Loop Join算法:

    该算法又得区分Join Buffer装得下和装不下的情况。

      Join Buffer装得下的情况

    1. t1表和t2表都做一次全表扫描,将t1表记录都装入Join Buffer,总的扫描行数是M + N(开头说了,扫描表就是把表从磁盘加载到内存中,驱动表扫描M行一次性装到Join Buffer,被驱动表扫描一行会在Join Buffer进行比较,最终扫描N行);
    2. 内存中的判断次数是M * N,由于Join Buffer是以无序数组的方式组织的,因此对t2表中的每一行数据,都要与Join Buffer中的记录相比较。

    可以看到,调换这两个算式中的MN没差别,因此这时候选择t1还是t2表做驱动表,成本都是一样的。

      Join Buffer装不下的情况

    我们先用直观的数据说明过程,假如表t1100行,而Join Buffer放不下,此时就分段放,执行过程就变成了:

    1. 扫描表t1,顺序读取数据行放入Join Buffer中,放完第80Join Buffer满了,继续第2步;
    2. 扫描表t2,把t2中的每一行取出来,跟Join Buffer中的所有记录做对比,满足join条件的,返回该条记录给MySQL客户端;
    3. 清空Join Buffer
    4. 继续扫描表t1,顺序读取最后的20行数据放入Join Buffer中,继续执行第2步。

    这个流程体现出了这个算法名字中“Block”的由来,表示“分块的join”。

    现在总结一下这个过程。驱动表t1的数据行数是N,假设需要分K次才能完成算法流程,被驱动表t2的数据行数是M

      注意,这里的K不是常数,N越大K就会越大。所以,在这个执行过程中:

    1. 扫描行数是N + K * M,每次装完一次Join Buffer,被驱动表t2M条记录就会从头到尾去Join Buffer匹配,Join Buffer需要装K次,则扫描Kt2表;
    2. 内存判断N * M次,由于Join Buffer是以无序数组的方式组织的,因此对t2表中的每一行数据,都要与Join Buffer中的记录相比较。

      显然,内存判断次数是不受选择哪个表作为驱动表影响的。而扫描行数考虑到Join Buffer的大小,在MN大小确定的情况下,驱动表的数据行数N小一些,则分段K就少一些,那么整个表达式的结果会更小。

      总结:如果Join Buffer能装任意一张表里的所有数据,那么不管选择哪个表作为驱动表,执行成本都一样。对于Join Buffer一次装不下驱动表的情况下,应该让小表当驱动表,因为小表记录总行数N越小,Join Buffer装完所需的次数K就越小,在N + K * M这个式子里,表达式的值越小。

      刚刚我们说了N越大,分段数K越大。那么N固定的时候,什么参数会影响K的大小呢?答案是join_buffer_sizejoin_buffer_size越大,Join Buffer中一次可以放入的行越多,分成的段数K也就越少,对被驱动表的全表扫描次数就越少。

      join_buffer_size默认256K,我所在的公司配置的是4M

    1.不能使用被驱动表的索引,只能使用Block Nested-Loop Join算法,这样的语句就尽量不要使用
    2.Explain下,没用Index Nested-Loop 的全要优化

      综上:从上面1234小节来看,无论哪种情况,总是应该选择小表作为驱动表。并且两张表有个各自的索引,这样表连接才能达到更好的性能。在内连接中,你可以使用STRAIGHT_JOIN替换JOIN,这样在内连接中就是强制左表为驱动表。



    欢迎一键三连~

    有问题请留言,大家一起探讨学习

    ----------------------Talk is cheap, show me the code-----------------------
    展开全文
  • Java实现Socket长连接和短连接

    千次下载 热门讨论 2014-09-21 11:32:43
    Java实现Socket长连接和短连接,实现原理可参见个人博客
  • 文章目录内连接自然连接和等值连接的区别内连接的实现方式外连接连接连接连接连接连接INNERJOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句...

    🔎这里是数据库加油站
    👍如果对你有帮助,给博主一个免费的点赞以示鼓励
    欢迎各位🔎点赞👍评论收藏⭐️

    数据库版本:mysql8。0.27
    如果以下代码执行有问题欢迎留言,一起探讨

    内连接

    • 内连接INNERJOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。
    • 在我看来内连接和等值连接差不多,自然连接是内连接中的一个特殊连接

    自然连接和等值连接的区别

    什么是自然连接?

    • 自然连接(Naturaljoin)是一种特殊的等值连接,它要求两个关系中进行比较的分量必须是相同的属性组,并且在结果中把重复的属性列去掉。

    什么是等值连接?

    • 等值连接是关系运算-连接运算的一种常用的连接方式。是条件连接(或称θ连接)在连接运算符为“=”号时,即θ=0时的一个特例

    举例分析
    表aaa
    在这里插入图片描述
    表bbb:
    在这里插入图片描述

    查询表aaa和表bbb中属性C B D相等的数据
    等值连接查询:

    SElect*from aaa,bbb WHERE aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E 
    

    结果:在这里插入图片描述
    自然连接查询:

    -- 自然连接用关键字 natural join
    SELECT*from aaa natural join bbb;
    

    结果:
    在这里插入图片描述
    由上面两个结果可以看出,等值连接就是从两个表数据的交集中取指定值相等的数据。而自然连接就是取两个表中相同属性,并且值相等的数据,而且消除了相同属性列。

    内连接的实现方式

    第一种:where

    SElect*from aaa,bbb WHERE aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E 
    

    第二种:inner join

    select*from aaa inner join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
    

    第三种:join

    select*from aaa join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
    

    第四种:STRAIGHT_JOIN

    select*from aaa STRAIGHT_JOIN bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
    

    后面三种效果一样,第三种可以理解为第二种的缩写。第四种和第二种主要区别是插入方式不同,第四种性能略低

    外连接

    • 外连接分为三种:左外连接,右外连接,全外连接。对应SQL:LEFT/RIGHT/FULL OUTER JOIN。

    左连接

    什么是左连接?

    • 左连接LEFT JOIN的含义就是求两个表A表和B表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表A表中剩余的记录

    在这里插入图片描述
    代码实现

    select*from aaa left join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
    

    结果
    在这里插入图片描述

    右连接

    什么是左连接?

    • 同理右连接RIGHT JOIN就是求两个表A和B表的交集外加右表B剩下的数据。再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录
      在这里插入图片描述
      代码实现
    select*from aaa right join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
    

    结果
    在这里插入图片描述

    全连接

    • 全外连接就是求两个表A和B集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做 UNION 操作来实现。
      在这里插入图片描述

    代码实现:

    select*from aaa left join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
    union 
    select*from aaa right join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
    

    结果:
    在这里插入图片描述

    展开全文
  • Mysql—— 内连接、左连接、右连接以及全连接查询

    万次阅读 多人点赞 2018-10-05 23:03:31
    一、内连接查询 inner join 关键字:inner join on 语句:select * from a_table a inner join b_table b on a.a_id = b.b_id; 说明:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集...

    获取 boy 、girl 表下载地址

    一、内连接查询  inner join

    关键字:inner  join   on

    语句select * from a_table a inner join b_table b on a.a_id = b.b_id;

    说明:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。

    案例解释:在boy表和girl 表中查出两表 hid 字段一致的姓名(gname,bname),boy表和girl 表如下:

          

    采用内连接查询方式:

    SELECT boy.hid,boy.bname,girl.gname FROM boy INNER JOIN girl ON girl.hid = boy.hid;

    查询结果如下:

    二、左连接查询 left join

    关键字:left join on / left outer join on

    语句:SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id;

    说明: left join 是left outer join的简写,它的全称是左外连接,是外连接中的一种。 左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

    案例解释:在boy表和girl 表中左连接查询,boy表和girl 表如下:

          

    采用内连接查询方式:

    SELECT boy.hid,boy.bname,girl.gname FROM boy LEFT JOIN girl ON girl.hid = boy.hid;

    查询结果如下:

    三、右连接 right join

    关键字:right join on / right outer join on

    语句:SELECT  * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

    说明:right join是right outer join的简写,它的全称是右外连接,是外连接中的一种。与左(外)连接相反,右(外)连接,左表(a_table)只会显示符合搜索条件的记录,而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

    案例解释:在boy表和girl 表中右连接查询,boy表和girl 表如下:

          

    采用内连接查询方式:

    SELECT boy.hid,boy.bname,girl.gname FROM boy RIGHT JOIN girl ON girl.hid = boy.hid;

    查询结果如下:

    四、全连接 union

    关键字:union /union all

    语句:(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )

             或 (select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB );

    union语句注意事项:

             1.通过union连接的SQL它们分别单独取出的列数必须相同;

             2.不要求合并的表列名称相同时,以第一个sql 表列名为准;

             3.使用union 时,完全相等的行,将会被合并,由于合并比较耗时,一般不直接使用 union 进行合并,而是通常采用union all 进行合并;

             4.被union 连接的sql 子句,单个子句中不用写order by ,因为不会有排序的效果。但可以对最终的结果集进行排序;

               (select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果

               (select id,name from A ) union all (select id,name from B ) order by id; //有排序效果

    案例解释:将a表和b表合并,表结构如下:

              

    采用 union 全连接:

    union会自动将完全重复的数据去除掉,a、b表中"c"的值都为15,所以只显示一行。

    采用 union all 全连接:

    union all会保留那些重复的数据;

    左右连接练习题:

    根据给出的表结构按要求写出SQL语句。t 表(即Team表)和 m 表(即Match表) 的结构如下:

         

    t 表(即Team表)和 m 表(即Match表) 的内容如下:

             

    t 表和 m 表下载地址

    m 表(即Match表) 的 hostTeamID 与 guestTeamID 都与 t 表(即Team表) 中的 teamID 关联。请查出 2006-6-1 到2006-7-1之间举行的所有比赛,并且用以下形式列出: 拜仁   2:0  不来梅  2006-6-21

    ===============================================================================

    解决方案:

    第一步:先以 m 表左连接 t 表,查出 m 表中 hid 这列对应的比赛信息:

    SELECT m.mid,t.tname,m.mres,m.matime as time FROM m LEFT JOIN t ON t.tid = m.hid;

    查询结果记为结果集 t1 ,t1 表如下:

    第二步:先以 m 表左连接 t 表,查出 m 表中 gid 这列对应的比赛信息:

    SELECT m.mid,t.tname,m.mres,m.matime FROM m LEFT JOIN t ON t.tid = m.gid;

    查询结果记为结果集 t2 ,t2 表如下:

    第三步:以结果集 t1 为基础左连接查询结果集 t2,查询条件为两者比赛序号(mid)相同。

    SELECT t1.tname,t1.mres,t2.tname,t1.time FROM 
    (SELECT m.mid,t.tname,m.mres,m.matime as time FROM m LEFT JOIN t ON t.tid = m.hid) 
    as t1 
    LEFT JOIN 
    (SELECT m.mid,t.tname,m.mres,m.matime as time FROM m LEFT JOIN t ON t.tid = m.gid) 
    as t2 
    ON t1.mid = t2.mid WHERE t1.time BETWEEN '2006-06-01' AND '2006-07-01';

    查询结果如下:

    全连接练习题:

    A表和B表结构如下,请将两表合并:

                

    合并要求:A表中a:5,B表中a:5,因此合并后表中a对应的值为10;要求查出的结果样本如下:

    采用 union all 全连接,然后使用from 子查询:

    SELECT id,SUM(num) as num FROM ((SELECT id,num FROM a) UNION ALL(SELECT id,num FROM b)) as tb GROUP BY id;
    

     

     

    展开全文
  • 连接池原理解读,各个连接池对比

    千次阅读 2020-07-06 14:28:47
    1.JDBC规范的实现 JDBC(Java DataBase Connectivity)是Java和数据库之间的一个桥梁,是一个规范而...2.建立JDBC和数据库之间的Connection连接; 3.创建Statement或者PreparedStatement接口; 4.执行SQL语句; 5.处理和显
  • 大脑中的结构连接、功能连接和有效连接

    千次阅读 多人点赞 2020-11-26 23:24:49
    《本文同步发布于“脑之说”微信公众号,欢迎搜索关注~~》 大脑,这个“三磅宇宙”,似乎可以说是目前我们人类正在研究的最为复杂的系统之一。...大脑内的这种连接,可以分为三种,即结构连接(Structural
  • 用两个表(a_table、b_table),关联字段a_table.a_id和b_table.b_id来演示一下MySQL的内连接、外连接( 左(外)连接、右(外)连接、全(外)连接)。 MySQL版本:Server version: 5.6.31 MySQL Community Server ...
  • 数据库连接

    万次阅读 2022-02-21 11:33:05
    连接池实际就是一个容器(集合),其中存放着数据库连接,当系统初始化好后,容器被创建,容器会申请一些连接对象,当用户访问数据库的时候,从容器中获取连接对象,用户访问完之后,会将连接对象归还给容器。...
  • Android开发--实现输入密码连接WIFI

    千次下载 热门讨论 2014-04-06 23:18:13
    project中简单实现了连接WIFi的功能,首次连接WIFI信号时,可以输入密码并保存,第二次可以直接连接。实现功能比较简单,需要的可以下载。研究不深,有错误地方希望多多交流。
  • TCP连接、Http连接与Socket连接的区别

    千次阅读 多人点赞 2019-06-08 17:45:32
    文章目录HTTP、Socket、TCP的区别Socket(套接字)Socket长连接什么时候用长连接,短连接TCP连接和HTTP连接的区别TCP连接与Socket连接的区别HTTP连接与Socket连接的区别常见问题什么时候该用HTTP,什么时候该用...
  • 目录1....由于TCP不存在连接的概念,只存在请求和响应,请求和响应都是数据包,它们之间都是经过由TCP创建的一个从客户端发起,服务器接收的类似连接的通道,这个连接可以一直保持,http请求是在这个连
  • 阿里巴巴主流数据库连接池Druid入门

    千次阅读 多人点赞 2022-04-09 09:45:36
    一.数据库连接池的必要性 (一).传统数据库连接模式的的步骤 ...3.存在内存泄漏问题:每次连接都需要断开连接,如果不断开,程序运行结束,会有创建的连接对象存在内存中一直无法关闭,就会导致java内存...
  • 什么是连接池 数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个。 为什么要使用连接池  数据库连接是一种关键的有限的昂贵的资源,这一点在多...
  • springboot 配置德鲁伊连接

    万次阅读 2021-09-18 08:55:15
    一、数据库连接池 1.1什么是数据库连接池 数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个;释放空闲时间超过最大空闲时间的数据库连接来避免...
  • 连接,右连接,内连接,全连接的区别及使用

    万次阅读 多人点赞 2021-01-15 17:09:35
    连接,右连接,内连接,全连接的区别及使用 众所周知,我们在写sql时经常会用到多表查询数据,这就是涉及到连接的问题包括,左连接,右连接,内连接,全外连接。 定义: 左连接 (left join):返回包括左表的所有...
  • http的长连接和短连接(史上最通俗!)

    万次阅读 多人点赞 2019-02-15 10:36:08
    ...但终究觉得对于长连接一直懵懵懂懂的,有种抓不到关键点的感觉。 今天通过一番研究,终于明白了这其中的奥秘。而之前,也看过长连接相关的内容,但一直都是云里雾里的。这次之所以能在这么...
  • 连接和外连接的区别和详解(全)

    万次阅读 2019-04-29 18:07:53
    自然连接无需指定连接列,SQL会检查两个表中是否相同名称的列,且假设他们在连接条件中使用,并且在连接条件中仅包含一个连接列。不允许使用ON语句,不允许指定显示列,显示列只能用*表示(ORACLE环境下测试的)。...
  • SQL语句的内连接,外连接,左连接,右连接详解例子 1、关系:链接分为内连接,外连接;外连接又分为左连接、右连接和全连接 分别为 内连接:(Inner Join):参与表格以JOIN关键词连在一起, ON指定联结的条件 左连接...
  • 连接 ,右连接,内连接和全外连接的4者区别

    万次阅读 多人点赞 2018-07-24 22:36:02
     left join (左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。  right join (右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。  inner join (等值连接或者叫内连接):只...
  • android手机蓝牙自动配对,连接,互发消息示例

    千次下载 热门讨论 2012-08-27 09:00:27
    内含可直接附加的项目,一个说明文档,代码中注释齐全 程序功能: 1.一台手机运行程序后选择服务端,...PS:谁有蓝牙快速连接的例子(根据蓝牙波段进行连接,无需配对,直接连接,类似蓝牙耳机)请与作者联系,谢谢
  • 用于解决SQLSERVER连接问题驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接问题JAR包。
  • 三、数据库连接池的工作原理 连接池的工作原理主要由三部分组成,分别为 连接池的建立 连接池中连接的使用管理 连接池的关闭 第一、连接池的建立。 一般在系统初始化时,连接池会根据系统配置建立,并在池中创建了...
  • 数据库左连接、右连接、内连接、全连接笔记

    万次阅读 多人点赞 2018-03-29 15:10:14
    上面代码在mysql执行是报错的,因为mysql不支持全连接,只能用以下代码实现效果,含义是左连接+右连接+去重=全连接: SELECT A.PK AS A_PK,A. Value AS A_Value,B.PK AS B_PK,B. Value AS B_Value ...
  • BLE连接简介

    千次阅读 2021-10-13 20:14:15
      主设备管理着连接, 并且可以连接多个从机设备, cc254x的主设备可以最多连接3个从设备, 但一个从设备只能链接一个主设备 六种设备状态   待机状态(Standby): 设备没有传输和发送数据, 并且没有连接到...
  • Pandas DataFrame 中的自连接和交叉连接

    千次阅读 2022-03-12 10:20:31
    在 SQL 中经常会使用JOIN操作来组合两个或多个表。有很多种不同种类的 JOINS操作,并且...顾名思义,自连接是将 DataFrame 连接到自己的连接。也就是说连接的左边和右边都是同一个DataFrame 。自连接通常用于查询分层
  • 深入分析内连接、外连接、左连接、右连接、等值连接、自然连接和自连接之间的区别,看这篇就够了!
  • 连接(等值、非等值连接)、外连接(左外连接、右外连接、全连接)、自然连接、自连接

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,724,587
精华内容 3,089,834
关键字:

连接

友情链接: gamma.rar