什么是MySQL 连接查询

什么是MySQL 连接查询？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

在数据库中join操作被称为连接，作用是能连接多个表的数据（通过连接条件），从多个表中获取数据合并在一起作为结果集返回给客户端。例如:表A：表B：通过连接可以获取到合并两个表的数据：连接两个表我们可以用两个关键字：on，using。on可以指定具体条件，using则指定相同名字和数据类型的列作为等值判断的条件，多个则通过逗号隔开。
如下:MySQL不支持全外连接，只支持左外连接和右外连接。如果要获取全连接的数据，要可以通过合并左右外连接的数据获取到，如 select * from A left join B on A.name = B.name union select * from A right join B on B.name = B.name;。这里union会自动去重，这样取到的就是全外连接的数据了。在连接过程中，MySQL各关键字执行的顺序如下：可以看到，连接的条件是先于where的，也就是先连接获得结果集后，才对结果集进行where筛选，所以在使用join的时候，我们要尽可能提供连接的条件，而少用where的条件，这样才能提高查询性能。join有三种算法，分别是Nested Loop Join，Hash join，Sort Merge Join。MySQL官方文档中提到，MySQL只支持Nested Loop Join这一种算法。具体来说Nested Loop Join又分三种细分的算法：我们来看下对于连接语句select * from A left join B on A.id=B.tid，这三种算法是怎么连接的。SNLJ是在没有使用到索引的情况下，通过两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出。也就是让两张表做笛卡尔积进行扫描，是比较暴力的算法，会比较耗时。其过程如下：当然，MySQL即使在无索引可用，或者判断全表扫描可能比使用索引更快的情况下，还是不会选择使用过于粗暴的SNLJ算法，而是采用下面的算法。INLJ是MySQL无法使用索引的时候采用的join算法。会将外层循环的行分片存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数，具体逻辑如下:相比于SNLJ算法，BNLJ算法通过外层循环的结果集的分块，可以有效的减少内层循环的次数。原理举例来说，外层循环的结果集是100行，使用SNLJ算法需要扫描内部表100次，如果使用BNLJ算法，假设每次分片的数量是10，则会先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中每次循环都直接匹配这10行数据，这样内层循环只需要10次，对内部表的扫描减少了9/10，所以BNLJ算法就能够显著减少内层循环表扫描的次数。当然这里，不管SNLJ还是BNLJ算法，他们总的比较次数都是一样的，都是要拿外层循环的每一行与内层循环的每一行进行比较。BNLJ算法减少的是总的扫描行数，SNLJ算法是外层循环要一行行扫描A表的数据，然后取A.id去表B一行行扫描看是否匹配。而BNLJ算法则是外层循环要一行行扫描A表的数据，然后放到内存分块里，然后去表B一行行扫描，扫描出来的B的一行数据与内存分块里的A的数据块进行比较。这里可以一次就是很多行A的数据与B的数据进行比较，而且是在内存中进行比较，速度更加快了。影响因素这里BNLJ算法总的扫描行数是由外层循环的数据量N，和分块数量K还有内层循环的数据量M决定的。其中分块数量K与外层循环的数据量N又是息息相关的，我们可以表示为N，其中取值为(0~1)。则总扫描次数C=N+NM。可以看出，在这个式子里，N和的大小都会影响扫描行数，但是才是影响扫描行数的关键因素，这个值越小越好（除非N和M的差值非常大，这开发云主机域名时候N才会成为关键影响因素）。那什么会影响 的大小呢？那就是 MySQL的join_buffer_size设置项的大小了。和join_buffer_size成倒数关系,join_buffer_size越大，分块越大，越小，分块数量也就越少，也就是外层循环的次数也越少。所以在使用不上索引的时候，我们要优先考虑扩大join_buffer_size的大小，这样优化效果会更明显。而在能使用上索引的时候，MySQL会使用以下算法来进行join。INLJ是MySQL判断能使用到被驱动表的索引的情况下采用的算法。假设A表的数据行为10，B表的数据行为100，且B.tid建立了索引，则对于select * from A left join B on A.id=B.tid，MySQL会采用Index Nested Loop Join。其过程如下：总共需要循环10次A，每次循环的时候通过索引查询一次B的数据。而如果我们反过来是B left join A的话，总共要循环100次B，由此可见如果使用join的话，需要让小表做驱动表，这样才能有效减少循环次数。但是需要注意的是，这个结论的前提是可以使用被驱动表的索引。INLJ内层循环读取的是索引，可以减少内存循环的次数，提高join效率，但是也有缺点的，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。例如上面在索引里匹配到了tid，还要去找tid所在的行在磁盘所在的位置，具体可以见我以前的文章：MySQL索引详解之索引的存储方式。Q：如果想筛选驱动表的数据，例如左连接筛选左表的数据，该在连接条件还是where筛选？
A：要通过where筛选，连接条件只影响连接过程，不影响连接返回的结果数（某些情况下连接条件会影响连接返回的结果数，例如左连接中，右侧匹配的数据不唯一的时候）Q：被驱动表匹配的数据行不唯一导致最终连接数据超过驱动表数据量该怎么办？例如对于左连接，右表匹配的数据行不唯一。
A：join之前先对被驱动表去重，例如通过group by去重：A lef join (select * from B group by name)。
看完上述内容，你们掌握什么是MySQL 连接查询的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注开发云行业资讯频道，感谢各位的阅读！

相关推荐: centos7 编译安装 mysql-5.7.25

安装依赖组件获取mysql-5.7.25.tar.gz 并开发云主机域名解压缩我的编译参数选项，里面的安装路径可以参照实际情况修改相关推荐: Mycat学习实战-Mycat全局主键@(学习)[mycat, mysql]1. Mycat全局主键介绍2. Myca…