为么 SQL 语句不要太多的 join？

6257rv7

作者：柯三链接：juejin.im/post/5e0443ae6fb9a0162277a2c3

送分题

面试官：有操作过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的方式有：5 种 Join 连接及实战案例！

inner join  内连接left join 左连接right join 右连接full join 全连接

以上照片源：https://www.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, 或换固态硬盘。

参考资料

《深入理解计算机系统》- 第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/section/5c061a4de51d451df113c10d MySQL 是怎么样运行的：从根儿上理解 MySQL

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

重磅！程序员交流群已成立

公众号运营迄今，离不开小伙伴们的支持。

为给小伙伴们供给一个互相学习交流的平台，特地开通了程序员交流群

群里有干了10年的技术大佬在线答疑，不时会分享有些技术要点，不时分享有些优秀的学习资料，还有送书活动。。（群完全免费）

必须进群的伴侣，可长按扫描二维码。

▲长按扫码

流星的美

顶楼主，说得太好了！

nykek5i

系统提示我验证码错误1500次 ＼~゛，

nykek5i

我完全赞同你的观点，思考很有深度。

nqkk58

期待更新、坐等、迫不及待等。