靈魂拷問:你寫的SQL一般有幾個join ?​

2021-02-21 程式設計師編程錄

來源：柯三

連結：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的方式有

inner join 內連接

left join 左連接

right join 右連接

full join 全連接

圖片來源：https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html

面試官：在項目開發中如果需要使用join語句，如何優化提升性能?

我：分為兩種情況，數據規模小的，數據規模大的。

面試官: 然後?

我：對於

1.數據規模較小 全部幹進內存就完事了嗷

2.數據規模較大

面試官：可以總結為join語句是相對比較耗費性能，對嗎？

我：是的

面試官: 為什麼?

緩衝區

我: 在執行join語句的時候必然要有一個比較的過程

面試官: 是的

我：逐條比較兩個表的語句是比較慢的，因此我們可以把兩個表中數據依次讀進一個內存塊中, 以MySQL的InnoDB引擎為例，使用以下語句我們必然可以查到相關的內存區域show variables like '%buffer%'

如圖所示join_buffer_size的大小將會影響我們join語句的執行性能

面試官: 除此之外呢?

一個大前提

我：任何項目終究要上線，不可避免的要產生數據，數據的規模又不可能太小

面試官: 是這樣的

我：大部分資料庫中的數據最終要保存到硬碟上,並且以文件的形式進行存儲。

以MySQL的InnoDB引擎為例

驗證

我：這意味著我們有多少表要連接就需要讀多少個文件，雖然可以利用索引，但還是免不了頻繁的移動硬碟的磁頭

面試官：也就是說頻繁的移動磁頭會影響性能對吧

我：是的，現在的開源框架不都喜歡說自己通過順序讀寫大大的提升了性能嗎，比如hbase、kafka

面試官：說的沒錯，那你認為Linux有對此做出優化嗎?提示，你可以再執行一次free命令看一下

我：奇怪緩存怎麼佔用了1.2G多

圖片來源：https://www.linuxatemyram.com/

面試官：你有沒有想過

品，你細品

思考了幾分鐘後

我：這麼隨便就釋放了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章 存儲器層次結構
《Experiments and fun with the Linux disk cache》作者通過幾個例子來說明硬碟緩存對程序執行性能的影響
《Linux ate my ram》 Free參數的解釋
How to clear the buffer/pagecache (disk cache) under Linux 文章開頭送分題命令的解釋
MySQL 是怎樣運行的：從根兒上理解 MySQL
Block bested loop 來自MariaDB官方文檔解釋了Block-Nested-Loop算法的實現

END

相關焦點

  • 面試官:你的SQL一般有幾個join?​
    >em…., 回去等通知吧再談SQL Join面試官：換個話題，談談你對join的理解我：好的（再答錯就徹底完了，把握住機會)回顧SQL中的join可以根據某些條件把指定的表給結合起來並將數據返回給客戶端join的方式有inner join 內連接
  • 面試必知的 Spark SQL 幾種 Join 實現
    SparkSQL總體流程介紹在闡述Join實現之前，我們首先簡單介紹SparkSQL的總體流程，一般地，我們有兩種方式使用SparkSQL，一種是直接寫sql語句，這個需要有元資料庫支持，例如Hive等，另一種是通過Dataset/DataFrame編寫Spark應用程式。
  • 【面試必備】SQL中left join、right join、inner join的區別
    sql語句如下:select * from Aleft join B on A.aID = B.bID結果如下:aID　　　　　aNum　　　　　bID　　　　　bName1　　　　　a20050111　　　　1　　　　　20060324012　　　　　a20050112　　　　2　　　　　2006032402
  • 「看這篇就夠了」Mysql join條件是要寫在on裡還是在where裡?
    對於join系列語句，大部分開發人員都經常用到。但是對於裡面的運行原理，我相信很少人真正認識，下面我們從幾個方面介紹下。為了能夠覆蓋更多的點，這裡複製一位大佬的表和圖。我們先建兩個表和添加一批數據，注意只有a表的f1有索引，a表和B表的數據不完全一致：結果集區別上圖可以看出，結果集是不一樣的，條件寫在ON裡，數據有6條，比條件放在where裡面多出2條。
  • MySQL中left join的幾個SQL對比
    ，知道邏輯，有預期的結果，好像沒什麼特別要注意的，今天在寫一條SQL邏輯的時候，覺得對於left join的部分還是存在一些誤解。left join的SQL,這個時候我們使用name='bb'來作為過濾條件，id作為關聯條件。
  • SQL是一門手藝
    實際上也有戲稱是標準查詢語言的，並不為過，SQL誕生於1974年，基於關係資料庫（也就是表格），發展至今毫無衰落跡象，反而滲透到各個數據處理工具/系統中：傳統資料庫、Hive數據倉庫、MongoDB支持類SQL，甚至玩dataframe表格的R語言也可以通過sqldf包寫sql，Spark、Flink也可以寫SQL，等等。玩數據分析，學好SQL無往而不利。
  • 我想說:mysql的join 真的很弱
    二、問題分析： 對這個結論，你是否有懷疑呢？也不知道是哪位先哲說的不要人云亦云，今天我設計sql，來驗證這個結論。（實驗沒有從代碼角度分析，目前達不到。可以把mysql當一個黑盒，使用角度來驗證這個結論） 驗證結論的時候，會有很多發現，各位往後看。
  • SQL-JOIN全解析
    例如下面這張用爛了的圖，可以幫你快速理解每個join用法的效果：這張圖描述了left join（左連接）、right join（右連接） 、inner join（內連接）、outer join（外連接）相關的7種用法。
  • inner join 與 left join 之間的區別
    需求是從資料庫查數據，在前端以柱形圖的形式展現出來，查到的數據按行業分組，顯示每個行業的戶數及戶數佔比，涉及到的欄位有A表的用戶數、總用戶數和B表的行業名稱。本來是不管查不查的到數據，在X軸都應該顯示行業名稱的，結果是X、Y軸都沒有任何數據顯示。問題就是我用錯了聯結方式。
  • mysql 如何優化left join
    and c.hotel_id =h.hotel_id where h.hotel_id is null    這個sql是用來查詢出c表中有h表中無的記錄，所以想到了用left join的特性（返回左邊全部記錄，右表不滿足匹配條件的記錄對應行返回null）來滿足需求，不料這個查詢非常慢。
  • 面試官靈魂一問: 為什麼 SQL 語句不要過多的 join?
    ...., 回去等通知吧再談SQL Join面試官：換個話題，談談你對join的理解我：好的（再答錯就徹底完了,把握住機會)回顧SQL中的join可以根據某些條件把指定的表給結合起來並將數據返回給客戶端join的方式有：5 種
  • 靈魂拷問！孩子的人生起跑線是什麼？
    然而卻很少人會思考這幾個問題：孩子的人生起跑線究竟是什麼？孩子將要參與的人生比賽，規則又是什麼？未來人才的標準又有哪些？9月6日，在深圳市龍華區的北站社區的親職賦能講座上，主講人張衛老師拋出了上述問題，被家長稱為「靈魂拷問」。「此刻的你已然退休在家，此刻的孩子已完成全部的學業等待進入職場，你們談論的話題會是什麼？你將用怎樣的態度向孩子傳遞你的思想？此時的你對孩子還有影響力麼？」
  • 你知道 Sql 中 left join 的底層原理嗎?
    總第165篇/張俊紅01.前言寫過或者學過 Sql 的人應該都知道 left join，知道 left join 的實現的效果
  • hive sql 優化心得
    如何寫好一個hql作為一個數據開發工程師，hive sql是我們必備的技能，可能大家都知道一些基本的優化方法（例如：使用分區、小表join大表、不使用distinct、where條件儘量寫到子查詢裡面減少數據量等等），但是你有沒有想過為什麼？是不是真的對執行效率有提升。
  • 英國人靈魂拷問中國人
    當被問到「中國人有哪些行為讓你覺得無法理解」時，幾位英國朋友發出了如下「靈魂拷問」：來自Charlotte的「靈魂拷問」：「為什麼中國人都有兩個名字？這很令我們費解！    韓國人叫韓國名字，日本人叫日本名字，但是中國人都叫英語名字！
  • 十月份靈魂拷問:英語作文,你背了嗎?
    【新傳免費課】10.13務實寫作題複習 　　【教育免費課】10.13最後衝刺複習要點 　　轉眼間，都進入十月份了，先不說考研人有沒有國慶假期，學姐要向大家先發出靈魂拷問：考研英語作文，你背了嗎？聽到這個提問是不是就沒心情思考給自己放假的事了？
  • 【母親節】來自靈魂的拷問,你了解你的媽媽嗎?
    【母親節】來自靈魂的拷問，你了解你的媽媽嗎？口是心非的媽媽們，總是藏起自己的心思，那麼今天就和小編一起，接受來自靈魂的拷問吧，看看媽媽們到底最關心的是什麼呢？
  • 當pandas撞上了sql,於是一個強大的pandasql庫產生了!
    2. pandasql的使用1）簡介pandas中的DataFrame是一個二維表格，資料庫中的表也是一個二維表格，因此在pandas中使用sql語句就顯得水到渠成，pandasql使用SQLite作為其操作資料庫，同時Python自帶SQLite模塊，不需要安裝，便可直接使用。
  • 詳細匯總 SQL 語句 Join 連接方式與用法.
    只要我們明確，「使用相等條件篩選」是有直接SQL支持的，就可以了。說了這麼多，還沒說SQL怎麼寫。最簡單的是直接使用SQL提供的關鍵字natural join。相當於自動添加了where語句，自動檢查了兩個表的哪幾個屬性相同。
  • 美團開源 SQL 優化工具 SQLAdvisor,與內部版本保持一致
    開發團隊稱目前 SQLAdvisor 在美團內部大量使用，較為成熟、穩定，且開源版本和內部使用版本保持完全一致，希望與業內有類似需求的團隊，一起打造一款優秀的 SQL 優化產品。SQLAdvisor 架構流程圖：