为什么 SQL 语句不要过多的 join？

向右看齐 2021-08-31 05:06 370阅读 0赞

点击上方 [Java后端][Java]，选择 设为星标

优质文章，及时送达

--------------------

作者：柯三

链接：juejin.im/post/5e0443ae6fb9a0162277a2c3

## **送分题** ##

**面试官**：有操作过Linux吗?

**我**：有的呀

**面试官**：我想查看内存的使用情况该用什么命令

**我**：`free` 或者 `top`

**面试官**：那你说一下用free命令都可以看到啥信息

**我**：那，如下图所示 可以看到内存以及缓存的使用情况

*  total 总内存
 *  used 已用内存
 *  free 空闲内存
 *  buff/cache 已使用的缓存
 *  avaiable 可用内存

![format_png][]

**面试官**：那你知道怎么清理已使用的缓存吗(buff/cache)

**我**：em... 不知道

**面试官**：`sync; echo 3 > /proc/sys/vm/drop_caches`就可以清理`buff/cache`了，你说说我在线上执行这条命令做好不好?

![format_png 1][]

**我**：(送分题,内心大喜)好处大大的有，清理出缓存我们就有更多可用的内存空间, 就跟pc上面xx卫士的小火箭一样，点一下，就释放出好多的内存

**面试官**：em...., 回去等通知吧

## **再谈SQL Join** ##

**面试官**：换个话题，谈谈你对join的理解

**我**：好的（再答错就彻底完了,把握住机会)

### 回顾 ###

SQL中的`join`可以根据某些条件把指定的表给结合起来并将数据返回给客户端

`join`的方式有：5 种

*  `inner join`  内连接

![format_png 2][]

*  `left join` 左连接

![format_png 3][]

*  `right join` 右连接

![format_png 4][]

*  `full join` 全连接

![format_png 5][]

> 以上图片源：https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html

**面试官**：在项目开发中如果需要使用`join`语句，如何优化提升性能?

**我**：分为两种情况，数据规模小的，数据规模大的。

**面试官**:  然后?

**我**：对于

*  **数据规模较小** 全部干进内存就完事了嗷
 *  **数据规模较大**

> 可以通过增加索引来优化`join`语句的执行速度 可以通过冗余信息来减少`join`的次数 尽量减少表连接的次数，一个SQL语句表连接的次数不要超过5次

**面试官**：可以总结为`join`语句是相对比较耗费性能，对吗？

**我**：是的

**面试官**: 为什么?

### 缓冲区 ###

**我**: 在执行join语句的时候必然要有一个比较的过程

**面试官**: 是的

**我**：逐条比较两个表的语句是比较慢的，因此我们可以把两个表中数据依次读进一个`内存块`中, 以MySQL的InnoDB引擎为例，使用以下语句我们必然可以查到相关的内存区域`show variables like '%buffer%'`

![format_png 6][]

如下图所示`join_buffer_size`的大小将会影响我们`join`语句的执行性能

**面试官**: 除此之外呢?

### 一个大前提 ###

**我**：任何项目终究要上线，不可避免的要产生数据，数据的规模又不可能太小

**面试官**: 是这样的

**我**：大部分数据库中的数据最终要保存到`硬盘`上,并且以文件的形式进行存储。

以[MySQL][]的InnoDB引擎为例

*  InnoDB以`页`(page)为基本的IO单位，每个页的大小为16KB
 *  InnoDB会为每个表创建用于存储数据的`.ibd`文件

![format_png 7][]

验证

![format_png 8][]

**我**：这意味着我们有多少表要连接就需要读多少个文件，虽然可以利用索引，但还是免不了频繁的移动硬盘的磁头

**面试官**：也就是说频繁的移动磁头会影响性能对吧

**我**：是的，现在的开源框架不都喜欢说自己通过顺序读写大大的提升了性能吗，比如`hbase`、`kafka`

**面试官**：说的没错，那你认为[`Linux`][Linux]有对此做出优化吗?提示，你可以再执行一次`free`命令看一下

**我**：奇怪缓存怎么占用了1.2G多

![format_png 9][] ![format_png 10][]

> 图片来源：https://www.linuxatemyram.com/

**面试官**: 你有没有想过

*  `buff/cache` 里面存的是什么,？
 *  为什么`buff/cache` 占了那么多内存，可用内存即`availlable`还有`1.1G`？
 *  为什么你可以通过两条命令来清理`buff/cache`占用的内存，而想要释放`used`只能通过结束进程来实现?

**品，你细品**

思考了几分钟后

![format_png][]

**我**：这么随便就释放了`buff/cache`所占用的内存，说明它就不重要, 清除它不会对系统的运行造成影响

**面试官**: 不完全对

**我**：难道是？想起来《CSAPP》（深入理解计算机系统）里面说过一句话

> 存储器层次结构的本质是，每一层存储设备都是较低一层设备的缓存

![format_png 11][]

翻译成人话，就是说**Linux会把内存当作是硬盘的高速缓存**

> 相关资料：http://tldp.org/LDP/sag/html/buffer-cache.html

**面试官**：现在知道那道送分题应该怎么回答了吧

**我**：我....

![format_png 12][]

### Join算法 ###

**面试官**：再给你个机会，如果让你来实现Join算法你会怎么做?

**我**：无索引的话,嵌套循环就完事了嗷。有索引的话，则可以利用索引来提升性能.

**面试官**：说回`join_buffer` 你认为`join_buffer`里面存储的是什么?

**我**：在扫描过程中，数据库会选择一个表把他**要返回以及需要进行和其他表进行比较的数据**放进`join_buffer`

**面试官**：有索引的情况下是怎么处理的？

**我**：这个就比较简单了，直接读取两个表的索引树进行比较就完事了嗷，我这边介绍一下无索引的处理方式

#### Nested Loop Join ####

![format_png 13][]

嵌套循环，每次只读取表中的一行数据，也就是说如果outerTable有10万行数据, innerTable有100行数据，需要读取`10000000`次(假设这两个表的文件没有被操作系统给缓存到内存, 我们称之为冷数据表)

当然现在没啥数据库引擎使用这种算法（太慢了)

#### Block nested loop ####

![format_png 14][]

`Block` 块，也就是说每次都会取一块数据到内存以减少I/O的开销

当没有索引可以使用的时候，MySQL InnoDB 就会使用这种算法

考虑以下两个表 `t_a` 和`t_b`

![format_png 15][]

当无法使用索引执行join操作的时候，InnoDB会自动使用`Block nested loop` 算法

![format_png 16][]

## **总结** ##

上学时，数据库老师最喜欢考数据库范式，直到上班才学会一切以性能为准，能冗余就冗余，实在冗余不了的就`join`如果`join`真的影响到性能。试着调大你的`join_buffer_size`, 或者换固态硬盘。

### 参考资料 ###

### 《深入理解计算机系统》- 第6章 存储器层次结构https://www.linuxatemyram.com/play.html 作者通过几个例子来说明硬盘缓存对程序执行性能的影响https://www.linuxatemyram.com/ Free参数的解释https://www.thegeekdiary.com/how-to-clear-the-buffer-pagecache-disk-cache-under-linux/ 文章开头送分题命令的解释 ###

### https://juejin.im/book/5bffcbc9f265da614b11b731/p/5c061a4de51d451df113c10d MySQL 是怎样运行的：从根儿上理解 MySQL ###

### https://mariadb.com/kb/en/block-based-join-algorithms/ 来自MariaDB官方文档解释了Block-Nested-Loop算法的实现 ###

[Java]: https://blog.csdn.net/qq_37217713/article/details/101445094
[format_png]: /images/20210730/fbbdda61f1dd404bb49780b6d5b71164.png
[format_png 1]: /images/20210730/23abb3429073423f8435c3186c6ae28d.png
[format_png 2]: /images/20210730/672ecac4c9f54bf898feaa3da9731ae9.png
[format_png 3]: /images/20210730/53a25f1d3727472482d9cb026003836c.png
[format_png 4]: /images/20210730/8469b597ecd74fb8ba88123879fcfc5f.png
[format_png 5]: /images/20210730/2f84beb7ad514a9a832be9987b9f5db6.png
[format_png 6]: /images/20210730/38c48a591b4446e2ae7e4ffabff8eff7.png
[MySQL]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb506c1cdc27e50af1df2f509c53af5b2e087aab6a44ccdd852c1314e8647bf5e4d6704e2915&idx=2&mid=2247493930&scene=21&sn=e3fe720755de690d7780ca3c82fc36fc#wechat_redirect
[format_png 7]: /images/20210730/2da007f6fb484463b851c8e6fee55062.png
[format_png 8]: /images/20210730/fb7e229d0ce546e48ed630ab5d0f1e6b.png
[Linux]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb539954dc24104250f8f7123874668bb64d70bd1b6dbf7fedc05e97778c8667904061379eff&idx=3&mid=2247490658&scene=21&sn=f34fce41db5ee215a23940153496107f#wechat_redirect
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOZTlybzZ4NjZ2eUtDN2lieThpY1dPSDhpYkd3R1lPckRpYzFLQ01mdHVuVjN3YUtNMUpmaEFHbWhmQS82NDA?x-oss-process=image/format,png
[format_png 10]: /images/20210730/d5f3c40d2c2c4911b93ae6c23c115ace.png
[format_png 11]: /images/20210730/5b6ffd3e0e964c36ae3435454b046637.png
[format_png 12]: /images/20210730/980c8e7b9002433690dbe5491c7f8a23.png
[format_png 13]: /images/20210730/e350685415c04c73a7f7e466087e262c.png
[format_png 14]: /images/20210730/0c2da59c95484a7283ddbd9d31fb5cb6.png
[format_png 15]: /images/20210730/b4f8adbb036948cca0507e34213fa5b2.png
[format_png 16]: /images/20210730/6e44299714db44e4a75aee73336a0a0d.png