为什么 SQL 语句不要过多的 join?
< 返回列表时间: 2020-07-14来源:OSCHINA
作者:柯三 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算法的实现
关注公众号Java技术栈回复"面试"获取我整理的2020最全面试题及答案。
推荐去我的博客阅读更多:
1. Java JVM、集合、多线程、新特性系列教程
2. Spring MVC、Spring Boot、Spring Cloud 系列教程
3. Maven、Git、Eclipse、Intellij IDEA 系列工具教程
4. Java、后端、架构、阿里巴巴等大厂最新面试题
觉得不错,别忘了点赞+转发哦!
热门排行