SQL語句別再帶過多的JOIN了,這樣寫才優秀!

2021-02-21 架構之路
送分題

面試官：有操作過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 全連接

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

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

面試官:然後?

我：對於

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

2.數據規模較大

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

我：是的

面試官:為什麼?

緩衝區

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

面試官:是的

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

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

面試官:除此之外呢?

一個大前提

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

面試官:是這樣的

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

以MySQL的InnoDB引擎為例

驗證

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

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

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

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

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



面試官：你有沒有想過

品，你細品

思考了幾分鐘後

我：這麼隨便就釋放了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算法的實現

相關焦點

  • 為什麼代碼規範要求SQL語句不要過多的join?
    ., 回去等通知吧再談SQL Join面試官：換個話題，談談你對join的理解我：好的（再答錯就徹底完了，把握住機會)回顧SQL中的join可以根據某些條件把指定的表給結合起來並將數據返回給客戶端join的方式有
  • SQL join語句總結
    語句》，講的是用Venn圖圖解SQL join語句。全外連接可以理解為left join與right join的併集。對應Venn圖求並。由於MySQL中沒有提供full outer join關鍵字，我們需要用left join 與right join 再結合 union來模擬full outer join，用union當然是因為union會去重。
  • 面試官靈魂一問: 為什麼 SQL 語句不要過多的 join?
    ., 回去等通知吧再談SQL Join面試官：換個話題，談談你對join的理解我：好的（再答錯就徹底完了,把握住機會)回顧SQL中的join可以根據某些條件把指定的表給結合起來並將數據返回給客戶端join的方式有：5 種
  • 面試必知的 Spark SQL 幾種 Join 實現
    inner joininner join是一定要找到左右表中滿足join條件的記錄，我們在寫sql語句或者使用DataFrame時，可以不用關心哪個是左表，哪個是右表，在spark sql查詢優化階段，spark會自動將大表設為左表，即streamIter，將小表設為右表，即buildIter。
  • 【面試必備】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
  • Oracle優化:sql語句的執行順序
    理解 sql 語句的執行順序對我們優化 sql 有很大的幫助，那麼 sql 語句的執行順序是怎樣的呢，以一條簡單的的語句做分析：① 先執行 from 子句，明確數據的來源，從哪個表或哪個視圖來查詢② 接著執行
  • 我想說:mysql的join 真的很弱
    查詢sql是： 我來分析一下這個語句：4張表等值join，還有一個子查詢。算是比較簡單的sql語句了（相比ERP動就10張表的哦，已經很簡單了）。我 還會分解這個語句成3個簡單的sql： 我來分析下：第一句，就是查詢最高分，得到最高分590分。
  • 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、說明：日程安排提前五分鐘提醒SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>513、說明：一條sql 語句搞定資料庫分頁
  • 詳細匯總 SQL 語句 Join 連接方式與用法.
    相當於自動添加了where語句，自動檢查了兩個表的哪幾個屬性相同。：select name, title from takes join course using(course_id, course_name);join...using...形式的語句可以很方便地指定「相等」條件，SQL同樣提供了指定其他條件的方式，也就是join...on...。
  • 優化SQL查詢:如何寫出高性能SQL語句
    一般，將一個Select語句的結果作為子集，然後從該子集中再進行查詢，這種一層嵌套語句還是比較常見的，但是根據經驗，超過3層嵌套，查詢優化器就很容易給出錯誤的執行計劃。因為它被繞暈了。像這種類似人工智慧的東西，終究比人的分辨力要差些，如果人都看暈了，我可以保證資料庫也會暈的。
  • 經典SQL語句大全
    9、說明：創建視圖：create view viewname as select statement刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句選擇：select * from table1 where 範圍插入：insert into table1(field1,field2
  • 一道簡單的sql語句題
    結果才發現，數據分析崗位大多注重的是資料庫的能力，比如sql語句的考察，hive的考察，以及一些運營思維的考察，所以第一次面試就很悲劇啦，不過題目還是很有代表性的。其他的不寫了，這裡只分享一個關於sql的題目。1、問題引出現在有兩個數據表，一個數據表記錄司機的信息，比如司機id，司機姓名，司機註冊時間等等，一個數據表記錄一天的訂單情況，比如訂單ID，訂單司機id，訂單時間。
  • inner join 與 left join 之間的區別
    ，才知道問題出在inner join 上了。一、sql的left join 、right join 、inner join之間的區別　　left join(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄 　　right join(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄　　inner join(等值連接) 只返回兩個表中聯結欄位相等的行舉例如下：
  • SQL數據分析實戰(三):Join語句介紹
    SQL語句的使用很簡單，重點在於理解資料庫表（結果集）的行列結構，這是關係型資料庫的核心概念之一。對分析師來說，理解了行列結構，掌握並能靈活應用join語句，在取數據工作中，能起到事半功倍的效果。在SQL專題文章中，我將SQL分成了三個部分，分別是JOIN、UNION、和其他函數。
  • mysql 如何優化left join
    如果還有第三個參與 Join，則再通過前兩個表的 Join 結果集作為循環基礎數據，再一次通過循環查詢條件到第三個表中查詢數據，如此往復，基本上MySQL採用的是最容易理解的算法來實現join。所以驅動表的選擇非常重要，驅動表的數據小可以顯著降低掃描的行數。 那麼為什麼一般情況下join的效率要高於left join很多？很多人說不明白原因，只人云亦云，我今天下午感悟出來了一點。
  • SQL | 數據分析面試必備SQL語句+語法
    我本人曾在滴滴、美團、平安科技的數據分析類崗位實習過，實習期間會大量運用sql進行取數。也參與了2018年的秋招，做過網易、拼多多、新浪等等公司的數據分析筆試題，還是比較了解SQL常考的題目類型的。寫這篇文章是希望幫助還沒有實戰過SQL的小夥伴、或者了解一些SQL語句，但是擔心自己了解的太片面的小夥伴。
  • mysql常用sql語句總結
    再執行外層語法：select 欄位 from 表 where 內層結果eg:select goods_name,goods_id from goods where goods_id =(select max(goods_id) from goods);from子查詢先執行from後的語句，得到內層結果。
  • 15000 字的 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、說明：日程安排提前五分鐘提醒 SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate(
  • 新鮮出爐的SQL語句大全
    C：full/cross（outer） join：全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。 b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.dwhere 12、說明：日程安排提前五分鐘提醒
  • 最強解讀MyBatis是如何執行SQL語句的?
    MyBatis是一款優秀的持久層框架，可以替代JDBC幫助我們更好地進行開發。要了解MyBatis的實現原理，首先我們要明白MyBatis的大致操作步驟。資料庫源告訴我們連接哪個資料庫，獲得要執行的SQL語句，再進行操作，這點者缺一不可。接下來要看的就是這三點在底層如何實現。