飯可以多吃,SQL JOIN 要少用

2021-02-21 猿天地
轉自：https://juejin.im/post/5e0443ae6fb9a0162277a2c3送分題

面試官：有操作過Linux嗎?

我： 有的呀

面試官： 我想查看內存的使用情況該用什麼命令

我： free 或者 top

面試官： 那你說一下用free命令都可以看到啥信息

我： 那，如下圖所示 可以看到內存以及緩存的使用情況

面試官： 那你知道怎麼清理已使用的緩存嗎(buff/cache)

我： em... 不知道

面試官： sync; echo 3 > /proc/sys/vm/drop_caches就可以清理buff/cache了，你說說我在線上執行這條命令做好不好?

我： (送分題,內心大喜)好處大大的有，清理出緩存我們就有更多可用的內存空間, 就跟pc上面xx衛士的小火箭一樣，點一下，就釋放出好多的內存

面試官： em...., 回去等通知吧

再談SQL Join

面試官： 換個話題，談談你對join的理解

我： 好的（再答錯就徹底完了,把握住機會)

回顧

SQL中的join可以根據某些條件把指定的表給結合起來並將數據返回給客戶端join的方式有

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

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

面試官:  然後?我： 對於

儘量減少表連接的次數，一個SQL語句表連接的次數不要超過5次

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

我： 是的

面試官:  為什麼?

緩衝區

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

面試官: 是的

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

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

面試官: 除此之外呢?

一個大前提

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

面試官: 是這樣的

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

InnoDB以頁(page)為基本的IO單位，每個頁的大小為16KBInnoDB會為每個表創建用於存儲數據的.ibd文件驗證


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

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

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

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

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

面試官: 你有沒有想過

buff/cache 裡面存的是什麼,？為什麼buff/cache 佔了那麼多內存，可用內存即availlable還有1.1G？為什麼你可以通過兩條命令來清理buff/cache佔用的內存，而想要釋放used只能通過結束進程來實現?

品，你細品

思考了幾分鐘後

我： 這麼隨便就釋放了buff/cache所佔用的內存，說明它就不重要, 清除它不會對系統的運行造成影響

面試官: 不完全對

我： 難道是？想起來《CSAPP》（深入理解計算機系統）裡面說過一句話

存儲器層次結構的本質是，每一層存儲設備都是較低一層設備的緩存


翻譯成人話，就是說Linux會把內存當作是硬碟的高速緩存

相關資料 tldp.org/LDP/sag/htm…

面試官： 現在知道那道送分題應該怎麼回答了吧

我： 我....

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, 或者換固態硬碟。

參考資料

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

相關焦點

  • 【面試必備】SQL中left join、right join、inner join的區別
    3　　　　　a20050113　　　　3　　　　　20060324034　　　　　a20050114　　　　4　　　　　20060324045　　　　　a20050115　　　　NULL　　　　　NULL（所影響的行數為 5 行）結果說明:left join是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left
  • 面試必知的 Spark SQL 幾種 Join 實現
    從上圖可以看到，不用做shuffle，可以直接在一個map中完成，通常這種join也稱之為map join。那麼問題來了，什麼時候會用broadcast join實現呢？這個不用我們擔心，spark sql自動幫我們完成，當buildIter的估計大小不超過參數spark.sql.autoBroadcastJoinThreshold設定的值(默認10M)，那麼就會自動採用broadcast join，否則採用sort merge join。
  • inner join 與 left join 之間的區別
    問題就是我用錯了聯結方式。一、sql的left join 、right join 、inner join之間的區別　　left join(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄 　　right join(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄　　inner join(等值連接) 只返回兩個表中聯結欄位相等的行舉例如下：
  • 我想說:mysql的join 真的很弱
    這樣這3個語句的就可以查詢出來 成績最高的學生姓名及其成績 。 接下來我會分別造數據：1千萬選課記錄(一個學生選修2門課),造500萬學生，100萬老師(一個老師帶5個學生，挺高端的吧)，1000門課，。用上面查詢語句查詢。其中sc表我測試了下有索引和沒有索引情況，具體見下表。
  • python數據分析我覺得可以用pandasql,真香!
    1df2.agg({'beef':max,'pork':min,'turkey':sum,'veal':'count'})# Resultbeef 2512.0pork 124.0turkey 185937.3veal 827.0dtype: float64
  • SQL-JOIN全解析
    例如下面這張用爛了的圖，可以幫你快速理解每個join用法的效果：這張圖描述了left join（左連接）、right join（右連接） 、inner join（內連接）、outer join（外連接）相關的7種用法。
  • mysql 如何優化left join
    and c.hotel_id =h.hotel_id where h.hotel_id is null    這個sql是用來查詢出c表中有h表中無的記錄，所以想到了用left join的特性（返回左邊全部記錄，右表不滿足匹配條件的記錄對應行返回null）來滿足需求，不料這個查詢非常慢。
  • 超詳細mysql left join,right join,inner join用法分析
    是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left join是以左表為準的. 2.right join(右聯接) sql語句如下:  SELECT  * FROM a RIGHT JOING b  ON a.aID = b.bID 結果如下: aID        aNum                   bID           bName 1            a20050111
  • 資料庫SQL腳本優化
    1、儘量用 a join b on a.id=b.id where + 條件代替 a join b on a.id=b.id and+條件 因為後者的執行流程是：1、把表a的內容讀入join_buffer中。
  • SQL join語句總結
    語句》，講的是用Venn圖圖解SQL join語句。看的過程中有點疑惑，乾脆就用MySQL實踐一下，順帶複習下MySQL嘛。這一實踐下來，還發現了另外的有趣的問題，比如MySQL中並沒有full outer join, 那要怎麼在MySQL中模擬full outer join的問題。這麼一來二去，涉及的內容就更加有意思了，不記下來有點可惜，另外國慶實在是一個放鬆的好時機啊，又想偷懶了，那就寫博客來防止自己偷懶吧！
  • 美團開源 SQL 優化工具 SQLAdvisor,與內部版本保持一致
    它基於 MySQL 原生詞法解析，再結合 SQL 中的 where 條件以及欄位選擇度、聚合條件、多表 Join 關係等最終輸出最優的索引優化建議。開發團隊稱目前 SQLAdvisor 在美團內部大量使用，較為成熟、穩定，且開源版本和內部使用版本保持完全一致，希望與業內有類似需求的團隊，一起打造一款優秀的 SQL 優化產品。
  • 「看這篇就夠了」Mysql join條件是要寫在on裡還是在where裡?
    我們先建兩個表和添加一批數據，注意只有a表的f1有索引，a表和B表的數據不完全一致：結果集區別上圖可以看出，結果集是不一樣的，條件寫在ON裡，數據有6條，比條件放在where裡面多出2條。算法區別select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2)語句執行順序是：1、先掃描a表的數據，放到join_buffer中，join_buffer的數據結構是數組。
  • SQL是一門手藝
    實際上也有戲稱是標準查詢語言的，並不為過，SQL誕生於1974年，基於關係資料庫（也就是表格），發展至今毫無衰落跡象，反而滲透到各個數據處理工具/系統中：傳統資料庫、Hive數據倉庫、MongoDB支持類SQL，甚至玩dataframe表格的R語言也可以通過sqldf包寫sql，Spark、Flink也可以寫SQL，等等。玩數據分析，學好SQL無往而不利。
  • 單機SQL Join的原理分析
    在多表Join時，使用嵌套循環匹配，在最內層獲得最終的結果，名字由此而來。上面被驅動表用了索引，所以被稱作Index Nested Loop Join。我們可以對它的時間複雜度進行估算。根據執行計劃，驅動表走的是全表掃描，假設驅動表的行數為N；假設被驅動表行數為M，被驅動表進行B樹搜索(InnoDB)，可以近似它的搜索時間複雜度為logM。
  • sql語句總結,大家可以收藏了面試一定有用!!!
    b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where 12、日程安排提前五分鐘提醒 select * from 日程安排 where datediff('minute',f開始時間,getdate())>5
  • SQL表連接查詢(inner join、full join、left join、right join)
    一、內連接-inner jion :最常見的連接查詢可能是這樣，查出學生的名字和成績：select s.name,m.mark from student s,mark m where s.id=m.studentid上面就是我們最常見的inner join
  • 阿里規範不建議多表Join,可這SQL要怎麼寫?
    阿里開發手冊的描述，禁止多表join：但是關鍵問題是：不用join，這SQL究竟要怎麼寫？！分解關聯查詢即對每個要關聯的表進行單表查詢，然後將結果在應用程式中進行關聯。>打破範式標準建議建表的時候，就把這些列放在一個表裡，比如一開始有student(id, name)，class(id, description)，student_class(student_id, class_id)三張表，這樣是符合資料庫範式的(第一範式，第二範式，第三範式，BC範式等)，沒有任何冗餘，但是馬上就不符合「編程規範「了，那我們可以用一張大表代替它
  • SQL 中的(+)用法
    今天在代碼review的時候，發現sql中很多 （+）的寫法，看得頭暈，記錄下。對於sql邏輯比較複雜的場景不推薦是有 （+），容易自己暈掉。Oracle sql 中 (+)號可以理解為補充的意思，即哪個表有加號，這個表就是匹配表。
  • SQL 學習筆記
    去年轉做分析師後，在工作上要用 SQL 的場景變多了，利用春節假期，結構化的梳理了 SQL知識，在此分享給有緣人。
  • Spark SQL重點知識總結
    4、可以通過將DataFrame註冊成為一個臨時表的方式，來通過Spark.sql方法運行標準的SQL語句來查詢。(filename,org.apache.spark.sql.types.StringType,nullable = true))val schema=org.apache.spark.sql.types.StructType(filed)peopleRDD.map(_.split(",")).map(para=>org.apache.spark.sql.Row(para(0).trim,para