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

曾经终败给现在 2023-02-18 09:37 85阅读 0赞

点击上方“程序猿技术大咖”，关注加群讨论

## 送分题 ##

**面试官**：有操作过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中的[``][Link 1]`join`可以根据某些条件把指定的表给结合起来并将数据返回给客户端

*  `inner join`  内连接

![format_png 2][]

*  `left join` 左连接

![format_png 3][]

*  `right join` 右连接

![format_png 4][]

*  `full join` 全连接

![format_png 5][]

**面试官**：在项目开发中如果需要使用`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][]

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

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

**品，你细品**

思考了几分钟后

![format_png][]

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

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

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

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

![format_png 10][]

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

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

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

**我**：我....

![format_png 11][]

### Join算法 ###

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

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

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

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

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

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

#### Nested Loop Join ####

![format_png 12][]

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

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

#### Block nested loop ####

![format_png 13][]

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

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

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

![format_png 14][]

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

![format_png 15][]

## 总结 ##

上学时，数据库老师最喜欢考数据库范式，直到上班才学会一切以性能为准，能冗余就冗余，实在冗余不了的就`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算法的实现

![format_png 16][]

![format_png 17][]

*  [JDK1.8新特性(四)：函数式接口][JDK1.8]
 *  [MySQL性能优化(七)：MySQL执行计划，真的很重要，来一起学习吧][MySQL_MySQL]
 *  [微服务架构下的核心话题 (三)：微服务架构的技术选型][Link 2]

![format_png 18][]

喜欢就点个"在看"呗，留言、转发朋友圈

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOS0lQRXJNV2VtOGdIbGJtYzZPaWF6UFQzbG9qbEJNd2FZNGpzampOWm92T2liS3hLd2RrNm12dUEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOcmFDekkyVDhSbm1pYnBBdjdHbFNVclY5SVFMSWMxRjdaV0hZaWNMdDk0M2MwN3FWTm0zYlh3R0EvNjQw?x-oss-process=image/format,png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb538c37dc240521dd10df0498404a37fb81c61aef665efcae142831ca7b7bbfadfcdb53ff4e&idx=1&mid=2247485697&scene=21&sn=aa41e25d02a92d0d83a597074f6d579c#wechat_redirect
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOSHZKd1dCdUhudFR2ZUk2bGFZcEJhUlhIaFhESDlIdGd4UHVlNXJxTmtZV1dqUllNOUdsNFVBLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOcnVNaWFGckZYanFiN01WTG9TeFBGRDJlbVlxVjdUck1FZXFHS0k1V2dRaWNpYUwxM2ZsQTJ6aWFzZy82NDA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOU3B2R1pMT2JkQjZraWJyQnJMc1hpYjNBNE9ralFrWHMwYWNWMktmek43SDFHc1pxRWxNcTJrWVEvNjQw?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOdHBPWVJxRVdrUk1FaDU0UUlqNWFsdlAzOXVsUFoyNmpqYzc4dkpHZDNFbVZPbmxhd2ljQkpOZy82NDA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOdUl0OHBYNzVFaWNraWIzeXZpYnFRNlRpYzljaWFVOHhHdkRyVFpmMUhLSGxlbVUyVGM3YmJuQThVZWcvNjQw?x-oss-process=image/format,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]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOZ1lmSU9hQmI3c3ppYjFyT0RjbzVmUHJjaEhLWEpSV2JicjBTWUJzR0JXb2pPUnpOR2x3T2RSQS82NDA?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOMVZzN2NNT056NmliWXl6UWw2ZTVKbTk0eWt4dVdjV2FpY2pYbm42dFdjRFpGTHJ5c0dodVlpY1NRLzY0MA?x-oss-process=image/format,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]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOSFdOOWZLWGdIak1xNDdUb2lhNjJ6Tk5DeTlYZlN1WWFwQWljVk53aWJBZlZQbWFJcEdOSHc2WDJBLzY0MA?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOemlhSlhhRnhSZnBRdFBYT25kRzFtekNpYmpxOFQyZ2RrYm9pYXRwOGJmUjZNUlJqVHdmNUs5bzhnLzY0MA?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOMGljc2ZaaWNOcmliR1RBOWlhcXZqdXhSTDZaTFZKYUpuNWIzRjlBcXZpY2o1RDB5bDI4TkZjcnpsU2cvNjQw?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOaWNDdHBCbWljcXdhREZGVlZVTmhadlBpY2VPSlBJTmtDd0RrSUlJN0JsSkw1Q3d2aEdpYTU5VXVSQS82NDA?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hOaDhaR3A3T2pLSzRkdmpOd2JBNDV1TUFpY3l2TU1jZFF1QzZ5Q1MzUmpYNHpFNUtvSm9ERlUzdy82NDA?x-oss-process=image/format,png
[format_png 15]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L1ROVXdLaFYwSnBTcFp2MHIwWUU2andNMFJVV3ZFN2hObk1rTU1scVpnekQyTGZacXNpYkc2YTU0QjB1UzFnS3NOT01CR2ZmMjZTelkyM2pTa2xwSDNiZy82NDA?x-oss-process=image/format,png
[format_png 16]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy91ZndmMmRONDh6U2RIUGRGSGlhYVNDVGF0aE9WcE5lTmZ4aWFYc0dIZUNpYjZzd2w1SFpvaWFEM2pVdmZxRGVib2xNZTV4aWJWRkdDNk1qVDgxNnprRXRYenF3LzY0MA?x-oss-process=image/format,png
[format_png 17]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9MaWFoaWNudEoyV0RxOE5qWmdoNU1xdmtHdlA4ektLaWNLZnJmMEhpYU1wSEJJN0p4T3dScUVsV0Eya0ppYzFsWURPZkxSUDlnRUtpYk1iSlh6a2NMQkhPMnVvZy82NDA?x-oss-process=image/format,png
[JDK1.8]: https://blog.csdn.net/xcbeyond/article/details/106308973
[MySQL_MySQL]: https://blog.csdn.net/xcbeyond/article/details/106042039
[Link 2]: https://blog.csdn.net/xcbeyond/article/details/102511891
[format_png 18]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi91ZndmMmRONDh6UWhmVThyQ2IyaWJ2VWliVWVSMGxFQVcwS3VKYVFXVkZpY1NxR0cwWjlJTTdxc2dJOWNpYkFnVGZwOFRIUFltaWNaOUdrMnduUWlhZzFrQ0w3US82NDA?x-oss-process=image/format,png