面試官靈魂一問: 為什么 SQL 語句不要過多的 join?
點擊上方藍色“小哈學Java”，選擇“設為星標”
回復“資源”獲取獨家整理的學習資料！
作者：柯三?鏈接：juejin.im/post/5e0443ae6fb9a0162277a2c3
送分題
面試官：有操作過Linux嗎?
我：有的呀
面試官：我想查看內(nèi)存的使用情況該用什么命令
我：free?或者?top
面試官：那你說一下用free命令都可以看到啥信息
我：那，如下圖所示 可以看到內(nèi)存以及緩存的使用情況
total 總內(nèi)存
used 已用內(nèi)存
free 空閑內(nèi)存
buff/cache 已使用的緩存
- avaiable 可用內(nèi)存
面試官：那你知道怎么清理已使用的緩存嗎(buff/cache)
我：em... 不知道
面試官：sync; echo 3 > /proc/sys/vm/drop_caches就可以清理buff/cache了，你說說我在線上執(zhí)行這條命令做好不好?
我：(送分題,內(nèi)心大喜)好處大大的有，清理出緩存我們就有更多可用的內(nèi)存空間, 就跟pc上面xx衛(wèi)士的小火箭一樣，點一下，就釋放出好多的內(nèi)存
面試官：em...., 回去等通知吧
再談SQL Join
面試官：換個話題，談談你對join的理解
我：好的（再答錯就徹底完了,把握住機會)
回顧
SQL中的join
join
inner join??內(nèi)連接
left join?左連接
right join?右連接
full join?全連接
以上圖片源：https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html
面試官：在項目開發(fā)中如果需要使用join語句，如何優(yōu)化提升性能?
我：分為兩種情況，數(shù)據(jù)規(guī)模小的，數(shù)據(jù)規(guī)模大的。
面試官: ?然后?
我：對于
- 數(shù)據(jù)規(guī)模較小?全部干進內(nèi)存就完事了嗷
- 數(shù)據(jù)規(guī)模較大
可以通過增加索引來優(yōu)化
join語句的執(zhí)行速度 可以通過冗余信息來減少join的次數(shù) 盡量減少表連接的次數(shù)，一個SQL語句表連接的次數(shù)不要超過5次
面試官：可以總結為join
我：是的
面試官: 為什么?
緩沖區(qū)
我: 在執(zhí)行join語句的時候必然要有一個比較的過程
面試官: 是的
我：逐條比較兩個表的語句是比較慢的，因此我們可以把兩個表中數(shù)據(jù)依次讀進一個內(nèi)存塊中, 以MySQL的InnoDB引擎為例，使用以下語句我們必然可以查到相關的內(nèi)存區(qū)域show variables like '%buffer%'
如下圖所示join_buffer_size的大小將會影響我們join語句的執(zhí)行性能
面試官: 除此之外呢?
一個大前提
我：任何項目終究要上線，不可避免的要產(chǎn)生數(shù)據(jù)，數(shù)據(jù)的規(guī)模又不可能太小
面試官: 是這樣的
我：大部分數(shù)據(jù)庫中的數(shù)據(jù)最終要保存到硬盤上,并且以文件的形式進行存儲。
以MySQL的InnoDB引擎為例
- InnoDB以
頁(page)為基本的IO單位，每個頁的大小為16KB - InnoDB會為每個表創(chuàng)建用于存儲數(shù)據(jù)的
.ibd文件
驗證
我：這意味著我們有多少表要連接就需要讀多少個文件，雖然可以利用索引，但還是免不了頻繁的移動硬盤的磁頭
面試官：也就是說頻繁的移動磁頭會影響性能對吧
我：是的，現(xiàn)在的開源框架不都喜歡說自己通過順序讀寫大大的提升了性能嗎，比如hbase、kafka
面試官：說的沒錯，那你認為Linux有對此做出優(yōu)化嗎?提示，你可以再執(zhí)行一次free命令看一下
我：奇怪緩存怎么占用了1.2G多
圖片來源：https://www.linuxatemyram.com/
面試官: 你有沒有想過
buff/cache?里面存的是什么,？- 為什么
buff/cache?占了那么多內(nèi)存，可用內(nèi)存即availlable還有1.1G？ - 為什么你可以通過兩條命令來清理
buff/cache占用的內(nèi)存，而想要釋放used只能通過結束進程來實現(xiàn)?
品，你細品
思考了幾分鐘后
我：這么隨便就釋放了buff/cache所占用的內(nèi)存，說明它就不重要, 清除它不會對系統(tǒng)的運行造成影響
面試官: 不完全對
我：難道是？想起來《CSAPP》（深入理解計算機系統(tǒng)）里面說過一句話
存儲器層次結構的本質(zhì)是，每一層存儲設備都是較低一層設備的緩存
翻譯成人話，就是說Linux會把內(nèi)存當作是硬盤的高速緩存
相關資料：http://tldp.org/LDP/sag/html/buffer-cache.html
面試官：現(xiàn)在知道那道送分題應該怎么回答了吧
我：我....
Join算法
面試官：再給你個機會，如果讓你來實現(xiàn)Join算法你會怎么做?
我：無索引的話,嵌套循環(huán)就完事了嗷。有索引的話，則可以利用索引來提升性能.
面試官：說回join_buffer?你認為join_buffer里面存儲的是什么?
我：在掃描過程中，數(shù)據(jù)庫會選擇一個表把他要返回以及需要進行和其他表進行比較的數(shù)據(jù)放進join_buffer
面試官：有索引的情況下是怎么處理的？
我：這個就比較簡單了，直接讀取兩個表的索引樹進行比較就完事了嗷，我這邊介紹一下無索引的處理方式
Nested Loop Join
嵌套循環(huán)，每次只讀取表中的一行數(shù)據(jù)，也就是說如果outerTable有10萬行數(shù)據(jù), innerTable有100行數(shù)據(jù)，需要讀取10000000次(假設這兩個表的文件沒有被操作系統(tǒng)給緩存到內(nèi)存, 我們稱之為冷數(shù)據(jù)表)
當然現(xiàn)在沒啥數(shù)據(jù)庫引擎使用這種算法（太慢了)
Block nested loop
Block?塊，也就是說每次都會取一塊數(shù)據(jù)到內(nèi)存以減少I/O的開銷
當沒有索引可以使用的時候，MySQL InnoDB 就會使用這種算法
考慮以下兩個表?t_a?和t_b
當無法使用索引執(zhí)行join操作的時候，InnoDB會自動使用Block nested loop?算法
總結
上學時，數(shù)據(jù)庫老師最喜歡考數(shù)據(jù)庫范式，直到上班才學會一切以性能為準，能冗余就冗余，實在冗余不了的就join如果join真的影響到性能。試著調(diào)大你的join_buffer_size, 或者換固態(tài)硬盤。
參考資料
《深入理解計算機系統(tǒng)》- 第6章 存儲器層次結構https://www.linuxatemyram.com/play.html 作者通過幾個例子來說明硬盤緩存對程序執(zhí)行性能的影響https://www.linuxatemyram.com/ Free參數(shù)的解釋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算法的實現(xiàn)
END
有熱門推薦?
1.?為什么不建議把數(shù)據(jù)庫部署在Docker容器內(nèi)？
2.?NullPointerException 的處理新方式，Java14 真的太香了！
3.?談談前后端分離實踐中如何提升RESTful API開發(fā)效率
4.?Service Mesh 和 API Gateway 關系深度探討
最近面試BAT，整理一份面試資料《Java面試BATJ通關手冊》，覆蓋了Java核心技術、JVM、Java并發(fā)、SSM、微服務、數(shù)據(jù)庫、數(shù)據(jù)結構等等。
獲取方式：點“在看”，關注公眾號并回復?Java?領取，更多內(nèi)容陸續(xù)奉上。
文章有幫助的話，在看，轉發(fā)吧。
謝謝支持喲 (*^__^*）