SQL- Join
SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）、SQL LEFT JOIN、SQL RIGHT JOIN、SQL FULL JOIN，其中前一种是内连接，后三种是外链接。
假设我们有两张表，Table A是左边的表，Table B是右边的表。
一、INNER JOIN
内连接是最常见的一种连接，只连接匹配的行。
inner join语法
select column_name(s) from table 1 INNER JOIN table 2 ON table 1.column_name=table 2.column_name
注释：INNER JOIN与JOIN是相同
INNER JOIN产生的结果集中，是1和2的交集。
select * from Table A inner join Table B on Table A.id=Table B.id
执行以上SQL输出结果如下：
| id | name | address |
| 1 | 美国 | |
| 3 | 微博 | 中国 |
二、LEFT JOIN
LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
LEFT JOIN 语法
select column_name(s) from table 1 LEFT JOIN table 2 ON table 1.column_name=table 2.column_name
注释：在某些数据库中，LEFT JOIN 称为LEFT OUTER JOIN
LEFT JOIN产生表1的完全集，而2表中匹配的则有值，没有匹配的则以null值取代。
select * from Table A left join Table B on Table A.id=Table B.id
执行以上SQL输出结果如下：
| id | name | address |
| 1 | 美国 | |
| 2 | 淘宝 | null |
| 3 | 微博 | 中国 |
| 4 | null |
三、RIGHT JOIN
RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。
RIGHT JOIN语法
select column_name(s) from table 1 RIGHT JOIN table 2 ON table 1.column_name=table 2.column_name
注释：在某些数据库中，RIGHT JOIN 称为RIGHT OUTER JOIN
RIGHT JOIN产生表2的完全集，而1表中匹配的则有值，没有匹配的则以null值取代。
select * from Table A right join Table B on Table A.id=Table B.id
执行以上SQL输出结果如下：
| id | name | address |
| 1 | 美国 | |
| 5 | null | 中国 |
| 3 | 微博 | 中国 |
| 6 | null | 美国 |
四、FULL OUTER JOIN
FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替
FULL OUTER JOIN语法
select column_name(s) from table 1 FULL OUTER JOIN table 2 ON table 1.column_name=table 2.column_name
FULL OUTER JOIN产生1和2的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。
select * from Table A full outer join Table B on Table A.id=Table B.id
执行以上SQL输出结果如下：
| id | name | address |
| 1 | 美国 | |
| 2 | 淘宝 | null |
| 3 | 微博 | 中国 |
| 4 | null | |
| 5 | null | 中国 |
| 6 | null | 美国 |
为什么代码规范要求SQL语句不要过多的join？
面试官：有操作过Linux吗?
我：有的呀
面试官：我想查看内存的使用情况该用什么命令
我：free 或者 top
面试官：那你说一下用free命令都可以看到啥信息
我：那，如下图所示 可以看到内存以及缓存的使用情况
- total 总内存
- used 已用内存
- free 空闲内存
- buff/cache 已使用的缓存
- avaiable 可用内存
面试官：那你知道怎么清理已使用的缓存吗(buff/cache)
我：em... 不知道
面试官：sync; echo 3 > /proc/sys/vm/drop_caches就可以清理buff/cache了，你说说我在线上执行这条命令做好不好?
我：(送分题,内心大喜)好处大大的有，清理出缓存我们就有更多可用的内存空间, 就跟pc上面xx卫士的小火箭一样，点一下，就释放出好多的内存
面试官：em...., 回去等通知吧
再谈SQL Join
面试官：在项目开发中如果需要使用join语句，如何优化提升性能?
我： 分为两种情况，数据规模小的，数据规模大的。
面试官: 然后?
我：对于
-
数据规模较小 全部干进内存就完事了嗷
-
数据规模较大
可以通过增加索引来优化
join语句的执行速度 可以通过冗余信息来减少join的次数 尽量减少表连接的次数，一个SQL语句表连接的次数不要超过5次
面试官：可以总结为join语句是相对比较耗费性能，对吗？
我：是的
面试官: 为什么?
缓冲区
我: 在执行join语句的时候必然要有一个比较的过程
面试官: 是的
我：逐条比较两个表的语句是比较慢的，因此我们可以把两个表中数据依次读进一个内存块中, 以MySQL的InnoDB引擎为例，使用以下语句我们必然可以查到相关的内存区域show variables like '%buffer%'
如下图所示join_buffer_size的大小将会影响我们join语句的执行性能
面试官: 除此之外呢?
一个大前提
我：任何项目终究要上线，不可避免的要产生数据，数据的规模又不可能太小
面试官: 是这样的
我：大部分数据库中的数据最终要保存到硬盘上,并且以文件的形式进行存储。
以MySQL的InnoDB引擎为例
- InnoDB以
页(page)为基本的IO单位，每个页的大小为16KB - InnoDB会为每个表创建用于存储数据的
.ibd文件
验证
我：这意味着我们有多少表要连接就需要读多少个文件，虽然可以利用索引，但还是免不了频繁的移动硬盘的磁头
面试官：也就是说频繁的移动磁头会影响性能对吧
我：是的，现在的开源框架不都喜欢说自己通过顺序读写大大的提升了性能吗，比如hbase、kafka
面试官：说的没错，那你认为Linux有对此做出优化吗?提示，你可以再执行一次free命令看一下
我：奇怪缓存怎么占用了1.2G多
面试官: 你有没有想过
buff/cache里面存的是什么,？- 为什么
buff/cache占了那么多内存，可用内存即availlable还有1.1G？ - 为什么你可以通过两条命令来清理
buff/cache占用的内存，而想要释放used只能通过结束进程来实现?
品，你细品
思考了几分钟后
我：这么随便就释放了buff/cache所占用的内存，说明它就不重要, 清除它不会对系统的运行造成影响
面试官: 不完全对
我：难道是？想起来《CSAPP》（深入理解计算机系统）里面说过一句话
存储器层次结构的本质是，每一层存储设备都是较低一层设备的缓存
翻译成人话，就是说Linux会把内存当作是硬盘的高速缓存
面试官：现在知道那道送分题应该怎么回答了吧
我：我....
Join算法
面试官：再给你个机会，如果让你来实现Join算法你会怎么做?
我：无索引的话,嵌套循环就完事了嗷。有索引的话，则可以利用索引来提升性能.
面试官：说回join_buffer 你认为join_buffer里面存储的是什么?
我：在扫描过程中，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer
面试官：有索引的情况下是怎么处理的？
我：这个就比较简单了，直接读取两个表的索引树进行比较就完事了嗷，我这边介绍一下无索引的处理方式
Nested Loop Join
嵌套循环，每次只读取表中的一行数据，也就是说如果outerTable有10万行数据, innerTable有100行数据，需要读取10000000次(假设这两个表的文件没有被操作系统给缓存到内存, 我们称之为冷数据表)
当然现在没啥数据库引擎使用这种算法（太慢了)
Block nested loop
Block 块，也就是说每次都会取一块数据到内存以减少I/O的开销
当没有索引可以使用的时候，MySQL InnoDB 就会使用这种算法
考虑以下两个表 t_a 和t_b
当无法使用索引执行join操作的时候，InnoDB会自动使用Block nested loop 算法
总结
上学时，数据库老师最喜欢考数据库范式，直到上班才学会一切以性能为准，能冗余就冗余，实在冗余不了的就join如果join真的影响到性能。试着调大你的join_buffer_size, 或者换固态硬盘。
作者：柯三
链接：https://juejin.im/post/5e0443ae6fb9a0162277a2c3
来源：掘金
