为什么代码规范要求SQL语句不要过多的join？

墨蓝 2024-03-27 18:55 6阅读 0赞

### **送分题** ###

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

**我**：有的呀

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

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

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

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

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

![图片][8237c9bf6b5459ce9c2dc08235f3d4cb.jpeg]

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

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

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

![图片][58f7a8e4dabe710aace6b87e07349e75.jpeg]

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

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

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

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

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

#####  #####

#### **回顾** ####

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

`join`的方式有

*  `inner join` 内连接

![图片][548f48b0a09571cd144a3ad69df28853.jpeg]

*  `left join` 左连接

![图片][057ed8204dc4493ce8e20ea2db3d1a9e.jpeg]

*  `right join` 右连接

![图片][373255c7831a40aa361559bf9c72e67d.jpeg]

*  `full join` 全连接

![图片][3e19e3dfe86df61e805f64d642a21039.jpeg]

> 以上图片源自这里

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

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

**面试官**: 然后?

**我**：对于

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

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

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

**我**：是的

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

#####  #####

#### **缓冲区** ####

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

**面试官**: 是的

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

![图片][63d6fe7c6abdd359d67bb54ddc610d2c.jpeg]

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

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

#####  #####

#### **一个大前提** ####

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

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

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

以MySQL的InnoDB引擎为例

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

![图片][7e9ed900c5b51adbd024f28a4f10b68a.jpeg]

验证

![图片][cf916b9ffe02c0bd3b218b298e5b748a.jpeg]

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

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

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

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

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

![图片][422e331cff3591ec85aa8b5db543d592.jpeg]

![图片][bb09dcd536fcf2159175ad041a98a90f.jpeg]

> 图片来源这里

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

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

**品，你细品**

思考了几分钟后

![图片][42c48dd9099ab5eb0ecf3df72c6ec5e2.jpeg]

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

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

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

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

![图片][eeeeba5c31fc58fb918108b2de0c273a.jpeg]

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

> 相关资料 tldp.org/LDP/sag/htm…

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

**我**：我....

![图片][6ee798f6953b826fdf4c4ff0139f578e.jpeg]

#### **Join算法** ####

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

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

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

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

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

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

#####  #####

##### **Nested Loop Join** #####

![图片][c8731a10c738a5279a7bad5615ad2b5e.jpeg]

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

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

#####  #####

##### **Block nested loop** #####

![图片][e0b09579b6bf08e55af24fc8e6782a4d.jpeg]

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

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

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

![2b588192d78342389d5af12e0f886782.png][]

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

![图片][3929060d58c1809019fa341509c34008.jpeg]

### **总结** ###

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

[8237c9bf6b5459ce9c2dc08235f3d4cb.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/ce60fbd5fbf5405ab6e45c9540bc499d.png
[58f7a8e4dabe710aace6b87e07349e75.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/88fc92c6370f4a9baac82c7e9f836d20.png
[548f48b0a09571cd144a3ad69df28853.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/0d09ab7238814e7dab8502ca479aa8b8.png
[057ed8204dc4493ce8e20ea2db3d1a9e.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/cc46dbf8c4bf4f9b8f40392a41262234.png
[373255c7831a40aa361559bf9c72e67d.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/494cd4a8f4204823952312747fbd20a9.png
[3e19e3dfe86df61e805f64d642a21039.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9652a37fba76477da42151c5e55b1373.png
[63d6fe7c6abdd359d67bb54ddc610d2c.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/6d82099f16dc4407b6930e42b1874a9c.png
[7e9ed900c5b51adbd024f28a4f10b68a.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/6e38ec906d6541a3bdedd985ae7baa84.png
[cf916b9ffe02c0bd3b218b298e5b748a.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/0b8afcc88f7f422cb766d953acb8303e.png
[422e331cff3591ec85aa8b5db543d592.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7ac062680ade4d81af1e3ec8607a6c5b.png
[bb09dcd536fcf2159175ad041a98a90f.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/bf6e2c8928ea4c1b90116822cd976298.png
[42c48dd9099ab5eb0ecf3df72c6ec5e2.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/585cb1f65bcc4f218dea220c6b2292db.png
[eeeeba5c31fc58fb918108b2de0c273a.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/34edc701b1b244829595dd831acefb36.png
[6ee798f6953b826fdf4c4ff0139f578e.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7a55c24182ae40a2bb80bcea7f7c5105.png
[c8731a10c738a5279a7bad5615ad2b5e.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/211734de94064f5aaa92ce9b3c6529cd.png
[e0b09579b6bf08e55af24fc8e6782a4d.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/880f8ccca2af4cffaa093e4f0c85bcfd.png
[2b588192d78342389d5af12e0f886782.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/71fbcd36e53345f38ffc0464e2698d98.png
[3929060d58c1809019fa341509c34008.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/18f53d86032a4644839ad61494a67e2a.png