面试官灵魂拷问：为什么代码规范要求 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的理解

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

回顾

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

​​join​​的方式有

• ​​inner join​​  内连接

• ​​left join​​ 左连接

• ​​right join​​ 右连接

• ​​full join​​ 全连接

以上图片源自：cnblogs.com/reaptomorrow-flydream/p/8145610.html

面试官：

在项目开发中如果需要使用​​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多

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

面试官: 你有没有想过

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

品，你细品

思考了几分钟后

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

面试官: 不完全对

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

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

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

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

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

我：我....

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​​, 或者换固态硬盘。

作者：柯三Go

完

有道无术，术可成；有术无道，止于术

欢迎大家关注Java之道公众号