(二十八)MySQL面試通關祕籍：這次你也可以在簡歷寫上精通MySQL！

語言: CN / TW / HK
時間 2022-12-29 23:15:04

theme: channing-cyan

引言

本文為掘金社群首發簽約文章，14天內禁止轉載，14天后未獲授權禁止轉載，侵權必究！

   從開設《MySQL專欄》至今，前面二十多個大章節中叨叨絮絮了許多內容，看到這裡也意味著本專欄即將接近尾聲，由於前面的每章內容都較為全面，因此每章的篇幅都並不算短，這對於一些想要面試前作鞏固複習的小夥伴並不友好，畢竟篇幅過長很難將核心知識點提煉出來，因此本章會重點提煉核心內容，將專欄中涉及的所有重點凝練成一份 《MySQL面試通關祕籍》。
面試

由於主要講解MySQL面試的核心知識點，因此不會對某些技術細節進行深入講解，但文中提到的技術點都會附上具體講解的連結，因而當大家看到自己不理解的面試點時，可直接戳進對應的連結中做深入研究。

同時，為了避免出現背誦“面試八股文”那樣的枯燥感，接下來通篇都會採用對話面試官的形式闡述！當然，看完如若感覺對你面試會有些許幫助（現在不用以後遲早會用的），記得點贊、收藏、關注三連支援一下噢~

一、好戲開場：MySQL底層架構與庫表設計

  • 自我感慨：閉關修煉半載、精通MySQL的我又回來啦！雖然迄今為止我的面試大寫著失敗，但這並不妨礙我繼續失敗！
  • 開幕場景：此時正挺著啤酒肚迎面向我走來的性感帥哥，正是本次負責考驗我的面試官，微風吹過他那一頭濃密的秀髮，從空氣中我嗅到了一絲並非強者的氣息，我內心不由自主的稱道：“呵，真弱”！
  • 震驚的我：刷~，很快面試官就在我面前緩緩落座！突然！他朝我微微一笑，接著將雙手舉過頭頂，在我一副震驚的神色中，從他那兒48碼的大頭上，漸漸取下了一頂假髮帽，露出了在陽光下略帶反光的地中海！該死，這氣息的壓迫感....好強大！
  • 面試官溫文爾雅道：候選者早上好呀，請先做個簡單的自我介紹。
  • 我：咳咳，好的。我叫竹子，是一位具有三十年開發經驗的三十三歲程式設計師，精通Java、Golang、Rust、PHP、Scala、C/C++、Spring、Redis....MySQL等技術棧的單詞拼寫！
  • 面試官：啥？精通MySQL？好，那我接下來考考你。
  • 我：不是，精通MySQL單詞的拼寫......
  • 面試官：先跟我說說你理解中的MySQL底層架構哈。
  • 我內心：靠，難怪當初在流水線做娃娃時，組長非得安排我去裝頭，原來我不是裝逼那塊料啊。
  • 面試官：竹子先生，為了有效開展本場面試，請儘快開始你的回答！
  • 我：我個人理解中的MySQL整體架構，自頂向下分為連線層、服務層、引擎層以及檔案層，其作用如下：
    • ①連線層：主要是指資料庫連線池，會負責處理所有客戶端接入的工作，如使用者登入、授權、連線等。
    • ②服務層：這是最重要的一層，所有跨引擎的操作都會放在這裡完成，如SQL解析、結果合併、執行計劃生成等。
    • ③引擎層：這依舊是MySQL較為重要的一層，服務層主要是制定執行計劃和等待結果，但讀寫資料的具體操作都需要通過引擎層來完成，引擎層決定著表資料讀寫方式和儲存方式。
    • ④檔案層：這是MySQL的基礎層，對上層服務提供最基礎的檔案服務，如日誌、資料、索引等檔案的支援。
    • 順手畫了個流程圖，並甩給了面試官，如下：
    • MySQL整體架構
  • 面試官：嗯，回答的還算不錯，你具體說說客戶端是怎麼和MySQL服務建立連線的呢？
  • 我：這個會比較複雜一些，客戶端與MySQL建立連線時，會先經過TCP/IP的三次握手過程，如果採用了加密連線的方式，還會經過SSL的握手過程，握手完成後MySQL和客戶端會建立session連線。
  • 我：接著MySQL會查詢自身的mysql.user表，來驗證客戶端的使用者名稱和密碼，如果有誤則會報錯。
  • 我：在都正確的情況下，首先會根據登入的使用者名稱，對客戶端連線進行授權，完成後即表示連線建立成功，後續的互動會採用半全工模式通訊，也就是同一時刻內，單方要麼只能傳送資料，要麼只能接受資料。
  • 面試官：客戶端獲取到的資料庫連線本質是什麼？每個連線用完後會立馬被丟棄嗎？
  • 我：資料庫連線的本質是一條條執行緒，比如當一個客戶端和MySQL成功建立連線之後，MySQL會先儲存客戶端的網路連線資訊，即session會話資訊。
  • 我：然後為了維護與客戶端之間的連線，在內部都會開啟一條條的執行緒繫結對應的會話資訊，以此來維護現有的連線，當客戶端發來一條SQL語句時，維護對應連線的執行緒則會去執行，執行過程中也會由對應的執行緒處理結果集並返回。
  • 我：當執行完客戶端的SQL語句後，MySQL預設會將連線維護八小時，在這八小時內不會銷燬，除非客戶端主動傳送了quit指令，這時MySQL才會主動銷燬連線，但這裡的銷燬也並非真正意義上的銷燬。
  • 我：因為執行緒在任何系統中都屬於珍貴資源，頻繁建立和銷燬的代價比較高，當客戶端主動退出連線後，MySQL只會將對應執行緒繫結的會話資訊清空，然後將“空閒”的執行緒放入自身的連線池當中，以備下次客戶端連線時使用。
  • 面試官：可以啊，你小子挺細，那接著說說解析器和優化器的作用。
  • 我：解析器和優化器一般是所有語言都具備的元件，前者主要用來詞義、語義分析和語法樹生成，說人話就是檢測SQL語法是否正確。
  • 我：優化器主要會對解析器生成的語法樹，選出一套SQL執行的最優方案，如選擇合適的索引、選擇合適的join方式等，對於優化器最終選擇的執行計劃可以通過explain工具來檢視。
  • 面試官：嗯，基礎還算紮實，那你再說說MySQL執行是如何執行一條SQL語句的呢？
  • 我：這要看具體情況，畢竟寫語句和讀語句的執行流程會存在些許差異，請問具體是哪個呢？
  • 面試官內心OS：喲，你小子給我顯擺上了，小樣，看我整不整你就完事！
  • 面試官：都給我先簡單的說一遍。
  • 我：好的，其實兩者大體上並無差異，主要區別在於一些細節上的變化，先說說讀語句的執行流程吧。
  • 讀語句：
    • ①先將SQL傳送給SQL介面，SQL介面會對SQL語句進行雜湊處理。
    • ②SQL介面在快取（QueryCache）中根據雜湊值檢索資料，如果快取中有則直接返回資料。
    • ③快取中未命中時會將SQL交給解析器，解析器會判斷SQL語句是否正確：
      • 錯誤：丟擲1064錯誤碼及相關的語法錯誤資訊。
      • 正確：將SQL語句交給優化器處理，進入第④步。
    • ④優化器根據SQL制定出不同的執行方案，並擇選出最優的執行計劃。
    • ⑤工作執行緒根據執行計劃，呼叫儲存引擎所提供的API獲取資料。
    • ⑥儲存引擎根據API呼叫方的操作，去磁碟中檢索資料（索引、表資料....）。
    • ⑦傳送磁碟IO後，對於磁碟中符合要求的資料逐條返回給SQL介面。
    • ⑧SQL介面會對所有的結果集進行處理（剔除列、合併資料....）並返回。
    • 我嘴裡一邊唸叨，同時拿出了紙筆，然後唰唰兩下畫出了一幅大體的流程圖，如下：
    • SQL執行過程
  • 寫語句：
    • ①先將SQL傳送給SQL介面，SQL介面會對SQL語句進行雜湊處理。
    • ②在快取中根據雜湊值檢索資料，如果快取中有則將對應表的所有快取全部刪除。
    • ③經過快取後會將SQL交給解析器，解析器會判斷SQL語句是否正確：
      • 錯誤：丟擲1064錯誤碼及相關的語法錯誤資訊。
      • 正確：將SQL語句交給優化器處理，進入第④步。
    • ④優化器根據SQL制定出不同的執行方案，並擇選出最優的執行計劃。
    • ⑤在執行開始之前，先記錄一下undo-log日誌和redo-log(prepare狀態)日誌。
    • ⑥在緩衝區中查詢是否存在當前要操作的行記錄或表資料（記憶體中）：
      • 存在：
        • ⑦直接對緩衝區中的資料進行寫操作。
        • ⑧然後等待後臺執行緒將資料刷寫到磁碟。
      • 不存在：
        • ⑦根據執行計劃，呼叫儲存引擎的API。
        • ⑧發生磁碟IO，讀取磁碟中的資料做寫操作。
    • ⑨寫操作完成後，記錄bin-log日誌，同時將redo-log日誌中的記錄改為commit狀態。
    • ⑩將SQL執行耗時及操作成功的結果返回給SQL介面，再由SQL介面返回給客戶端。
    • 同時大筆一揮，又在紙上畫出了一幅流程圖，如下：
    • 寫SQL執行流程
  • 面試官內心OS：就知道你小子會顯擺，說的越詳細坑就越多，你小子等著吧，嘿嘿嘿.....
  • 面試官：聽你剛剛說讀語句的執行流程時，似乎提到了一個叫做查詢快取的東西，你確定這玩意一定在嗎？
  • 我：不用懷疑，我敢說！它....不一定在。
  • 面試官：哦！？什麼情況下不在呢？
  • 我：手動關閉的情況下不會在，8.0之後的版本中想開也開不了，因為被移除了。
  • 面試官：那你說說官方為什麼要移除呢？快取不是能很好的提升查詢效能嗎？
  • 我：快取的確能夠很好的提升查詢效能，但MySQL的查詢快取就一言難盡，有多方面原因吧，如下：
    • ①快取命中率低：幾乎大部分SQL都無法從查詢快取中獲得資料。
    • ②佔用記憶體高：將大量查詢結果放入到記憶體中，會佔用至少幾百MB的記憶體。
    • ③增加查詢步驟：查詢表之前會先查一次快取，查詢後會將結果放入快取，額外多幾步開銷。
    • ④快取維護成本不小，需要LRU演算法淘汰快取，同時每次更新/插入/刪除資料時，都要清空快取中對應的資料。
    • ⑤查詢快取是專門為MyISAM引擎設計的，而InnoDB構建的緩衝區完全具備查詢快取的作用。
    • ⑥同時專案中一般都會用Redis做業務快取，能來到MySQL查詢的語句十有八九是要走磁碟的，因此查詢快取的存在，反而弊大於利。
  • 面試官：嗯，回答的不錯，那說說這個查詢快取和你前面提到的緩衝，兩者有什麼區別呢？
  • 我：查詢快取只能給讀語句使用，而緩衝讀寫語句都能用（緩衝區的知識後面聊）。
  • 面試官：你剛剛說的是SQL執行流程，那你能不能跟我說一下SQL執行之前會發生什麼呢？
  • 我：當然可以，程式上線後，任何一條SQL語句的誕生，都源自於平臺使用者的操作，使用者傳送的請求最終會轉變為一條條具體的SQL語句。
  • 我：生成SQL之後接著會去配置好的資料庫連線池，如Druid中獲取一個數據庫連線，然後發給MySQL執行，但執行前還會先判斷當前連線的使用者，是否具備SQL要操作的表許可權。
  • 面試官：不錯，那你剛剛提到的Druid這類連線池，和MySQL自己維護的連線池，會不會衝突呢？
  • 我：不會呀，雖然兩個都叫連線池，但一個是位於客戶端，一個是位於服務端，兩者的區別在於：
    • 客戶端連線池：減少多次建立資料庫連線時，頻繁出現的TCP三次握手、四次揮手、SSL握手等過程。
    • 服務端連線池：減少多次建立資料庫連線時，頻繁建立和銷燬工作執行緒造成的資源開銷。
  • 我：同時這兩個連線池都能帶來不小的速度提升呢，前者避免了等待網路握手的時間，後者避免了等待執行緒建立的時間，如果沒有這些連線池，每次SQL執行時，光網路握手和建立執行緒就需要耗費不少時間。
  • 面試官：嗯嗯，那在高併發情況下，是不是把客戶端連線池的最大連線數，調的越大越好呢？
  • 我：理論上是的，因為連線數越大，代表同一時間可以執行更多的SQL語句，也就意味著同一時間可以處理更多的使用者請求，但理想很豐滿，現實很骨感，由於硬體配置的原因，這種做法是不行的。
  • 面試官：此話怎講吶？談談你的看法。
  • 我：因為一個數據庫連線，本質上對端都需要各自開啟一條執行緒維護，將最大連線數配置成100，這也意味著應用程式和MySQL各自都需要開啟100條執行緒維護這些連線。
  • 我：如果一臺八核的機器，因為只有八個核心，無法在同一時刻內支援這麼多執行緒執行，所以OS只能頻繁的在每條執行緒之間切換CPU資源，確保每條執行緒能夠正常運轉。
  • 我：這最終會導致：每條執行緒等待CPU資源的總時長，反而會超出實際執行SQL的時間，所以根據機器的硬體來配置最大執行緒數，這才是最合理的方案，目前業界主流的配置計算公式為：CPU核心數*2，如果硬碟材質是SSD的，那麼還可以再加個一，這屬於最佳配置。
  • 面試官：可以嘛，看樣子你還懂效能調優呀，這都直接給我聊到連線層調優來了。
  • 面試官：你再跟我說說，MySQL一條執行緒執行完成後，它是如何知道自己該向誰返回資料的？
  • 我：這倒不難，之前不是說過資料庫連線對應的工作執行緒，自身會繫結客戶端的會話資訊嘛？這個會話資訊就包含了客戶端的IP地址、埠等資訊，當一條執行緒執行完成後，只需要根據這個地址去封裝資料報文就好啦，如果要返回的結果集比較大，MySQL會把一個大的資料包拆分成多個小的資料報文分批返回。
  • 面試官：嗯呢，這塊就此打住，有了解過資料庫的三正規化嗎？它是做什麼用的呢？
  • 我：瞭解過啊，三正規化主要是在設計庫表結構時，需要遵循的一些原理原則：
    • 第一正規化：要求一張表的每個欄位，設計時都必須具備原子性，即單個列只表示一個值，不可再分。
    • 第二正規化：要求一張表的所有欄位，都必須依賴於主鍵，也就是一張表只能存同一個業務屬性的欄位。
    • 第三正規化：要求表中每一列資料不能與主鍵之外的欄位有直接關係，也就是表中只允許一個主屬性存在。
    • 除開上述基本的三正規化外，還有一些用的比較少的巴斯-科德正規化/3.5正規化、第四正規化、第五正規化。
  • 面試官：那在設計庫表結構的時候，一定要遵循這些正規化原則去設計嗎？
  • 我：不需要，正規化只是設計庫表的方法論，但如若業務需要或效能需要，不遵循正規化設計也可以，這種不遵循正規化設計的手段則被稱之為反正規化設計。
  • 面試官：小夥子，很不錯嘛！
  • 我微微一擡手道：咳咳，低調低調，基操勿六~
  • 面試官：........
  • 我：咳咳，您接著問~

二、小試牛刀：細聊MySQL索引機制

  • 面試官：你知道MySQL是如何從磁碟中按條件讀取資料的嗎？
  • 我：這個很簡單，MySQL會預設會觸發磁碟IO來讀取表資料，但InnoDB引擎讀取時，會利用區域性性原理，也就是預讀思想，一次IO會讀取16KB磁碟資料放入記憶體，接著和SQL語句的條件做對比，符合條件的留在記憶體，其他的丟棄，然後繼續去磁碟中讀其他的表資料，直到把整張表的資料檔案都找一次後，最後才會把符合條件的資料返回，這個過程也被稱作全表掃描。
  • 面試官：你這小嘴叭叭太多了，聽的腦瓜疼，麻煩給我講簡單點。
  • 我：.....，相當於小學讀書，在字典中找一個漢字，是靠一頁頁的翻，最終找到需要的目標漢字。
  • 面試官：哦，那怎麼才能快一點呢？
  • 我：字典不是有那個目錄索引頁麼，通過音節、偏旁等方式查詢就行。
  • 面試官：咳，我是問MySQL查資料，怎麼才能更快一點。
  • 我：同樣的思想，書籍有目錄，MySQL中也有索引，我們可以在經常查詢的欄位上建立索引，查詢時就能直接走索引查找了。
  • 面試官：那MySQL中有哪些索引呢？
  • 我：這要看以啥維度來分，不同維度可以劃分為不同的索引叫法，比如：
    • 以資料結構來分：Hash索引、B+Tree索引、R-Tree索引、T-Tree索引。
    • 以欄位數量來分：單列索引（由單個欄位組成）、聯合索引（由多個欄位組成）、字首索引（由單/多個欄位的前面一部分組成）。
    • 以功能邏輯來分：普通索引、唯一索引、主鍵索引、全文索引、空間索引。
    • 以儲存方式來分：聚簇索引、非聚簇索引。
  • 面試官：小夥子就是不一樣，整這麼細~，如果我線上業務經常使用like模糊查詢，你有好辦法優化不？
  • 我：很簡單呀，可以使用ES這類搜尋引擎來完成模糊查詢工作，如果不想用，則可在對應欄位上建立全文索引，全文索引會比like查詢的效率更高，並且支援全模糊、左模糊查詢走索引。
  • 面試官：你知道MySQL索引的底層是什麼資料結構麼？
  • 我：這要根據具體的儲存引擎來決定，常用引擎一般支援Hash、B+Tree兩種結構，通常是B+樹。
  • 面試官：嗯嗯，那為什麼MySQL不選擇二叉平衡樹、紅黑樹、B樹等結構呢？
  • 我：您所提到的這些資料結構都屬於樹結構，選擇這些樹結構作為索引的底層實現，在資料量較大的情況下，尤其是索引欄位具備順序遞增特性時，索引樹的高度會呈直線型增長，也就是樹高會變得很大。
  • 我：而走索引查詢時，一層樹高就需要觸發一次磁碟IO，索引樹的樹高決定著磁碟IO的次數，磁碟IO的次數越多，意味著查詢耗時、資源開銷會更大，所以您所提及到的這些樹結構，並不適合作為索引結構的實現。
  • 面試官：我提到的前兩個樹結構的確如此，但為何B樹結構也不合適呢？它單個葉子節點不是會儲存多個數據嗎？
  • 我：沒錯，但關係型資料庫經常會執行一些範圍查詢操作，而普通的B樹結構，各個葉子節點之間沒有指標連線，所以對於範圍查詢支援不友好。
  • 我：而B+樹則不同，每個葉子節點都會有一根指向下個節點的指標，範圍查詢時可以基於這些指標快捷查詢。
  • 我：不過值得一提的是：MySQL也並未選擇傳統的B+Tree結構來實現索引，而是又對其進行了改良，畢竟B+樹只有指向下個節點的指標，所以只支援正向範圍查詢，而不支援反向範圍查詢。
  • 我：因此MySQL在傳統的B+Tree結構中，又在每個節點中加了一個指向上個節點的指標，這樣做之後也支援反向範圍查詢。
  • 面試官：你的意思是MySQL索引用了變種B+Tree咯？再問一下你們專案一般選什麼欄位作為主鍵？
  • 我：通常會選一個數值型別、且具備順序遞增特性的欄位作為主鍵，如果表中沒有符合條件的欄位，則通常會額外設計一個跟業務無關的ID欄位作為主鍵。
  • 面試官：哦？為什麼寧願額外設計也不從表中選擇其他欄位呢？
  • 我：這主要是為了維護索引的樹結構，如果選擇值無序的欄位作為索引鍵，這絕對會造成索引樹頻繁的發生分裂，從而導致索引的效能下降。
  • 面試官：嗯哼？為什麼索引樹分裂會導致效能下降呢？而順序自增又能維護樹結構呢？
  • 我內心：......，希望你除開嘴上一口一個B樹的問之外，心裡最好也要有個B樹。
  • 我：因為當一個葉子節點存滿後，此時又新增一個新的值，也要插入到這個節點中，那麼該節點中的最後一個數據只能往後面的節點移動，而後面的節點又需要繼續往後移動，最終才能給新增的值騰出位置。
  • 我：因為這個過程索引樹的結構在發生變更，所以會加鎖防止其他事務讀到不對的資料。而挪動資料、加鎖阻塞都需要時間，因此樹分裂會導致索引下降。
  • 我：但如果選擇按序遞增的欄位就不會有這個問題，畢竟每次新增的值，都會直接放到最後面去插入，並不會導致樹結構發生分裂。
  • 面試官：不錯不錯，你再跟我說說聚簇索引和非聚簇索引的區別。
  • 我：聚簇索引是物理空間+邏輯上的連續，索引資料和表資料會放在磁碟的同一塊位置上儲存；而非聚簇索引則是單純邏輯上的連續，索引資料和表資料是分開的，通過地址指標的形式指向資料。
  • 我：同時InnoDB引擎的非聚簇索引和傳統的非聚簇索引不同，例如MyISAM引擎中的非聚簇索引，索引值儲存的是行資料的磁碟地址。
  • 我：而InnoDB的非聚簇索引的索引值，因為表資料和聚簇索引鍵儲存在一起，儲存的則是對應行資料的聚簇索引鍵。
  • 面試官：你既然都聊到了這個，一定知道啥是回表問題吧？
  • 我：知道的，回表查詢指需要經過兩次完整的查詢過程後，才能夠讀取到目標資料，這也是InnoDB引擎獨有的壞毛病，基於非聚簇索引/次級索引查詢資料時，從索引中查詢索引值後，會接著再通過查到的聚簇索引鍵再查一次聚簇索引，從而得到最終需要的行資料。
  • 面試官：嗯嗯，那有什麼好的辦法減少回表查詢嗎？
  • 我：有的，儘量建立聯合索引來代替單列索引，再結合查詢資料時不要用*來表示所有欄位，這樣可以重複利用索引覆蓋機制來獲取資料，從而減少回表查詢的次數。
  • 面試官：你提到的這個索引覆蓋機制，可以展開講講嗎？
  • 我：這個是MySQL的一種優化手段，假設通過name、sex、age三個欄位建立了一個聯合索引，當基於聯合索引查詢時只需要返回name、age，因為這兩個欄位值在聯合索引中都包含了，那就可以直接從索引鍵中讀取資料返回。
  • 我：但如果使用*時，因為聯合索引中不具備完整資料，所以只能觸發回表動作得到完整的行資料。
  • 面試官：那你知道建立一個索引之後，MySQL會幹什麼工作麼？
  • 我：分情況，如果是基於空表建立索引，會直接根據建立的索引型別、儲存引擎、欄位型別等資訊，在本地的表文件/索引檔案中，直接建立一個樹結構即可。但如果表中有資料，情況會略微複雜一些，如下：
    • ①首先根據索引型別，對索引欄位的資料進行對應處理：
      • 唯一索引：判斷索引欄位的每個值是否存在重複值，如果有則丟擲錯誤碼和資訊。
      • 主鍵索引：判斷主鍵欄位的每個值是否重複、是否有空值，有則丟擲錯誤資訊。
      • 全文索引：判斷索引欄位的資料型別是否為文字，對索引欄位的值進行分詞處理。
      • 字首索引：對於索引欄位的值進行擷取工作，選用指定範圍的值作為索引鍵。
      • 聯合索引：對於組成聯合索引的多個列進行值拼接，組成多列索引鍵。
      • ........
    • ②接著根據索引的資料結構，再對索引欄位的資料進行處理：
      • B+Tree：對索引欄位的值進行排序，按照順序組成B+樹結構。
      • Hash：對索引欄位的值進行雜湊計算，處理相應的雜湊衝突，方便後續查詢。
      • .......
    • ③根據表的儲存引擎、索引欄位再進行相應處理：
      • InnoDB主鍵索引：對.ibd檔案中的表資料進行重構，將索引鍵和行資料調整到一塊區域中儲存。
      • InnoDB次級索引：因為有聚簇索引，將非聚簇索引的索引值，與行資料對應的聚簇索引鍵的關聯起來。
      • MyISAM：由於表資料在單獨的.MYD檔案中，因此可以直接以磁碟指標的關聯表資料。
    • 經過上述處理後，建立索引就完成啦！
  • 面試官：講的蠻好，再獎勵你回答一下：基於索引的查詢資料過程。
  • 我：這個還算比較簡單，面試官你認真聽好咯！
    • ①首先根據查詢語句的條件欄位，去記憶體中找到對應的索引根節點。
    • ②通過根節點中記錄的葉節點地址，逐步去遍歷查詢索引樹，最終定位到目標資料所在的葉子節點。
    • ③但遍歷索引樹的過程，採用的是二分查詢法，拿到一個葉節點後，如果SQL條件比它大，會去繼續讀取右邊的葉節點，反之則讀取左邊的葉節點，然後再進行判斷。
    • ④使用二分查詢法，找到一個目標資料後，這裡會根據不同的索引，來執行不同的操作：
      • InnoDB聚簇索引：直接從索引樹中得到行資料，因為行資料和聚簇索引儲存在一塊。
      • InnoDB次級索引：看是否能夠使用索引覆蓋機制獲取資料，不行則觸發回表動作得到資料。
      • MyISAM的索引：根據索引鍵中記錄的磁碟地址，直接去磁碟中讀取行資料。
    • ⑤讀取到一個目標資料後，如果是基於主鍵/唯一索引在查詢，則會立馬停止查詢，如果是普通索引則會繼續向下遍歷。
    • ⑥如果是範圍查詢操作，會直接根據葉子節點的前後指標，獲取其他的索引鍵資料，然後重複第④步，得到目標行資料。
  • 面試官：可以，再說說寫入語句執行時，會對索引產生什麼影響呢？
  • 我：好的，但插入、刪除、修改資料時，都會存在細微不同，我都簡單說說吧：
    • 增：
      • 如果索引欄位具備自增特性，直接把插入的欄位資料作為索引鍵，追加到索引樹最後一個節點儲存。
      • 如果索引欄位是無序的，則先對其排序計算（字串轉換為ascii碼），計算出一個位置並插入。
    • 刪：
      • 會先根據刪除的條件查詢索引樹，接著去聚簇索引樹找到對應的行資料，先刪行資料和聚簇索引鍵，接著再刪除其他次級索引的資料。
    • 改：
      • 和刪操作類似，先找到資料，再改行資料，再改聚簇索引鍵，再改次級索引資料。
  • 面試官：嗯嗯，自己有了解過索引的最左匹配原則麼？
  • 我：索引最左匹配原則是基於聯合索引而言的，好比一個聯合索引由A、B、C三個欄位組成，那麼在寫SQL語句時，最好按照索引欄位的順序來使用索引，如果寫的SQL中不包含第一個A欄位，一般都無法使用這個聯合索引查詢資料。
  • 我：同時，如果查詢語句中使用了A、C欄位，但沒有使用B欄位，也無法完全利用聯合索引。
  • 我：因為MySQL的聯合索引會從左往右匹配資料，所以在設計索引時，最好把查詢頻率高的欄位放在前面，這樣才能充分利用最左匹配原則查詢資料，但MySQL8.0中也推出了一種名為索引跳躍式掃描的機制，可以打破聯合索引的最左匹配原則查詢資料。
  • 面試官：嗯呢，那MySQL索引除開索引覆蓋、跳躍掃描外，還有別的優化機制嗎？
  • 我：還有MySQL5.6中引入的索引下推機制、MRR機制，這兩種機制能夠在很大程度上減少索引查詢的磁碟IO，以及離散性的磁碟IO。
  • 面試官：嗯，聊了這麼多理論，考你一些實踐性的知識，怎麼才能很好的使用索引呢？
  • 我：這個也不難，可以從兩個角度出發，一是合理的建立索引，二是編寫合理的SQL語句使用索引，先來說說什麼是合理的建立索引：
    • 建立索引時需要遵守的原則：
      • ①經常頻繁用作查詢條件的欄位應酌情考慮為其建立索引。
      • ②表的主外來鍵或連表字段，必須建立索引，因為能很大程度提升連表查詢的效能。
      • ③建立索引的欄位，一般值的區分性要足夠高，這樣才能提高索引的檢索效率。
      • ④建立索引的欄位，值不應該過長，如果較長的欄位要建立索引，可以選擇字首索引。
      • ⑤建立聯合索引，應當遵循最左字首原則，將多個欄位之間按優先順序順序組合。
      • ⑥經常根據範圍取值、排序、分組的欄位應建立索引，因為索引有序，能加快排序時間。
      • ⑦對於唯一索引，如果確認不會利用該欄位排序，那可以將結構改為Hash結構。
      • ⑧儘量使用聯合索引代替單值索引，聯合索引比多個單值索引查詢效率要高。
    • 不適合建立索引的一些情況：
      • ❶值經常會增刪改的欄位，不合適建立索引，因為每次改變後需維護索引結構。
      • ❷一個欄位存在大量的重複值時，不適合建立索引，比如之前舉例的性別欄位。
      • ❸索引不能參與計算，因此經常帶函式查詢的欄位，並不適合建立索引。
      • ❹一張表中的索引數量並不是越多越好，一般控制在3，最多不能超過5。
      • ❺建立聯合索引時，一定要考慮優先順序，查詢頻率最高的欄位應當放首位。
      • ❻當表的資料較少，不應當建立索引，因為資料量不大時，維護索引反而開銷更大。
      • ❼索引的欄位值無序時，不推薦建立索引，因為會造成頁分裂，尤其是主鍵索引。
  • 我：咱們在寫SQL語句的時候，可以刻意避開這些會導致索引失效的場景即可。
  • 面試官：哦喲，你小子不賴呀，那你最後再給我說說，使用索引的好處和壞處吧。
  • 我：好，凡事有利弊，索引也不例外，除開帶來了好處之外也帶來了影響，如下：
    • 使用索引帶來的優勢：
      • ①整個資料庫中，資料表的查詢速度直線提升，資料量越大時效果越明顯。
      • ②通過建立唯一索引，可以確保資料表中的資料唯一性，無需額外建立唯一約束。
      • ③在使用分組和排序時，同樣可以顯著減少SQL查詢的分組和排序的時間。
      • ④連表查詢時，基於主外來鍵欄位上建立索引，可以帶來十分明顯的效能提升。
      • ⑤索引預設是B+Tree有序結構，基於索引欄位做範圍查詢時，效率會明顯提高。
      • ⑥從MySQL整體架構而言，減少了查詢SQL的執行時間，提高了資料庫整體吞吐量。
    • 使用索引帶來的影響：
      • ①建立索引會生成本地磁碟檔案，需要額外的空間儲存索引資料，磁碟佔用率會變高。
      • ②寫入資料時，需要額外維護索引結構，增、刪、改資料時，都需要額外操作索引。
      • ③寫入資料時維護索引需要額外的時間開銷，執行寫SQL時效率會降低，效能會下降。
  • 面試官：OK，那咱們這塊就此打住，嘿嘿，接下來我再問你一點特殊的東西~

三、嶄露頭角：詳談MySQL事務與鎖機制

  • 我：咔！打住！你想要幹嗎？我賣藝不賣身！
  • 面試官：額.....，我就想問問你懂不懂事務與鎖機制。
  • 我：啊？阿，那我白高興了.....
  • 面試官：？！？？
  • 我：嘿嘿，沒事，您接著問。
  • 面試官：那先跟我說說什麼為什麼需要事務機制，以及事務的ACID原則吧。
  • 我：需要事務機制的道理很簡單，比如目前有一個轉賬業務，整個業務由減A賬戶餘額、加B賬戶餘額這兩個操作組成，假設現在扣完A的餘額後，結果程式執行時拋Bug了，但此時B的餘額還沒有增加，這最終會造成A賬戶的錢平白無故消失了！所以也正因如此，才需要事務機制來確保一組操作的資料一致性。
  • 我：而所謂的ACID原則，則是資料庫事務機制要滿足的四個特性：
    • A/Atomicity：原子性，指組成一個事務的一組SQL要麼全部執行成功，要麼全部執行失敗。
    • C/Consistency：一致性，指任何一個事務發生的前後，庫中的資料變化必須一致。
    • I/Isolation：獨立性/隔離性，指同時存在多個併發事務時，各個事務之間執行的操作不會相互影響。
    • D/Durability：永續性，指一個事務但凡提交之後，就必須確保事務變更過的資料永遠不會丟失。
  • 面試官：嗯呢，那你再跟我說說事務的隔離級別。
  • 我：MySQL的事務隔離級別有四個，每個級別分別能夠解決不同的問題，如下：
    • ①讀未提交/RU：處於該隔離級別的資料庫，髒讀、不可重複讀、幻讀問題都有可能發生。
    • ②讀已提交/RC：該級別中解決了髒讀問題，不可重複讀、幻讀問題依舊存在。
    • ③可重複讀/RR：該級別中解決了髒讀、不可重複讀問題，幻讀問題依舊存在。
    • ④序列化/Serializable：該級別中解決了髒讀、不可重複讀、幻讀問題都不存在。
  • 面試官：等等，你所說的髒讀、幻讀、不可重複讀問題是什麼意思呢？
  • 我：這是指併發事務執行過程中，可能會碰到的一些問題，我展開說說吧。
    • 髒讀問題：指一個事務讀到了其他事務還未提交的資料，其他事務可能會回滾這些資料。
    • 不可重複讀問題：指在一個事務中，多次讀取同一資料，先後讀取到的資料不一致。
    • 幻讀問題：指一個事務中，批量變更了某類資料，變更完成後再次查詢，表中依舊存在變更前的資料，就好比發生了幻覺一樣。
  • 面試官：那你知道MySQL的事務機制是怎麼實現的嗎？
  • 我：首先糾正一下你的問題，MySQL-Server本身沒有提供事務機制，事務機制是InnoDB引擎獨有的特性，而事務機制是基於Undo-log日誌實現的，InnoDB預設會開啟事務的自動提交，將每條SQL都視作一個單獨的事務，而通過begin開啟事務後，需要手動提交後才能生效，可以將多條SQL語句組成一個事務。
  • 我：之前咱們在聊寫入語句的執行流程時，說過寫入語句執行時會記錄Undo-log日誌，更新資料前，會把原本的老資料放到Undo-log日誌中，然後在表的資料行上記錄一個回滾指標，這個指標會指向Undo-log中的舊資料。
  • 我：當事務需要回滾時，InnoDB會直接根據回滾指標的地址，找到原本的老資料，然後直接複製過來，將變更過的新資料覆蓋掉。
  • 面試官：OK，那你能不能簡單說一下MySQL中的鎖機制呢？
  • 我：可以呀，其實鎖的叫法有很多，但本質上就只有共享鎖、排他鎖這兩種，只不過加的粒度不同、時機不同、方式不同，就演變出了很多叫法，整個體系如下：
    • 以鎖粒度的維度劃分：
      • ①表鎖：
        • 全域性鎖：加上全域性鎖之後，整個資料庫只能允許讀，不允許做任何寫操作。
        • 元資料鎖 / MDL鎖：基於表的元資料加鎖，加鎖後整張表不允許其他事務操作。
        • 意向鎖：這個是InnoDB中為了支援多粒度的鎖，為了相容行鎖、表鎖而設計的。
        • 自增鎖 / AUTO-INC鎖：這個是為了提升自增ID的併發插入效能而設計的。
      • ②頁面鎖
      • ③行鎖：
        • 記錄鎖 / Record鎖：也就是行鎖，一條記錄和一行資料是同一個意思。
        • 間隙鎖 / Gap鎖：InnoDB中解決幻讀問題的一種鎖機制。
        • 臨建鎖 / Next-Key鎖：間隙鎖的升級版，同時具備記錄鎖+間隙鎖的功能。
    • 以互斥性的維度劃分：
      • 共享鎖 / S鎖：不同事務之間不會相互排斥、可以同時獲取的鎖。
      • 排他鎖 / X鎖：不同事務之間會相互排斥、同時只能允許一個事務獲取的鎖。
      • 共享排他鎖 / SX鎖：MySQL5.7版本中新引入的鎖，主要是解決SMO帶來的問題。
    • 以操作型別的維度劃分：
      • 讀鎖：查詢資料時使用的鎖。
      • 寫鎖：執行插入、刪除、修改、DDL語句時使用的鎖。
    • 以加鎖方式的維度劃分：
      • 顯示鎖：編寫SQL語句時，手動指定加鎖的粒度。
      • 隱式鎖：執行SQL語句時，根據隔離級別自動為SQL操作加鎖。
    • 以思想的維度劃分：
      • 樂觀鎖：每次執行前認為自己會成功，因此先嚐試執行，失敗時再獲取鎖。
      • 悲觀鎖：每次執行前都認為自己無法成功，因此會先獲取鎖，然後再執行。
  • 面試官：那行鎖和表鎖之間有啥區別呢？
  • 我：主要是粒度不同，表鎖是指對一整張表加鎖，當加鎖後，其他來訪問該表的事務都會被阻塞，而行鎖的粒度則小很多，是指標對於一條/多條資料加鎖，並不會阻塞操作同一表的事務，而僅僅只會阻塞操作相同行資料的事務。
  • 面試官：嗯呢，那你所說的共享鎖和排他鎖，兩者的區別是啥？
  • 我：共享鎖允許多個事務一起持有，而排他鎖在同一時間內只能允許一個事務持有，也就是但凡出現排他鎖的場景，其他事務都需要阻塞等待。
  • 面試官：那MySQL的表鎖、行鎖有哪些呢？
  • 我：表鎖有元資料鎖、意向鎖、自增鎖、全域性鎖這四種，行鎖有記錄鎖、間隙鎖、臨鍵鎖、插入意向鎖這四類，行鎖在MySQL中是InnoDB引擎獨有的，並且InnoDB的行鎖和表鎖之間，是相互相容的。
  • 面試官：你說到的記錄鎖、間隙鎖、臨鍵鎖這三種行鎖有什麼區別呢？
  • 我：記錄鎖是指對一條資料上鎖、間隙鎖是指對一條資料和下一條資料之間的空隙上鎖、臨鍵鎖則是前兩者的結合體，InnoDB的行鎖預設就是臨鍵鎖型別，這三種鎖都屬於InnoDB的行鎖演算法，InnoDB會根據情況來選擇不同的行鎖演算法獲取鎖。
  • 面試官：好的，但你說了這麼多鎖，可是我們在用MySQL的時候似乎沒有使用呀？
  • 我：對的，我們不會主動去使用鎖，這些都是MySQL在執行語句時，自動根據情況來加的鎖，因此也被稱之為隱式鎖，但我們也可以在SQL語句中，通過for update、for share這種語法手動加鎖。
  • 面試官：那請問隱式鎖、或手動加鎖後，什麼時候會釋放鎖呢？
  • 我：幾乎所有釋放鎖的工作都是MySQL自動完成的，但不同事務隔離級別中，釋放鎖的時機也不同，如果目前是讀未提交級別，MySQL執行完一條語句後就會立馬釋放鎖。如果是其他級別中，基本上都需要等待持有鎖的事務結束（commit/rollback）後才會釋放。
  • 面試官：那你前面提到過一句，MySQL5.7中引入了一種共享排他鎖，這是幹嘛的？
  • 我：因為索引樹的結構會發生變更，比如一個無序資料插入時，就會導致樹節點的分裂，這時需要挪動樹中的一些節點位置，為了防止其他事務再次破壞樹結構、或從索引樹中讀到不對的資料，所以會對整棵樹上鎖，這個問題被稱為SMO問題，共享排他鎖主要就是用來解決SMO問題。
  • 面試官：嗯呢，MVCC機制有了解過嗎？
  • 我：有的，但我先給你講個故事吧，比如拿一個新聞網站舉例說明，首先小編髮布了一則新聞報道，等待稽核通過後，A、B、C使用者看到後開始閱讀這篇新聞。
  • 我：但小編突然發現文中有錯別字，就更新了一次新聞，因此這則新聞又進入了稽核狀態，但此時A、B、C使用者正在看新聞呀！肯定不能直接給它們顯示一個稽核中的狀態，所以就會採用多版本方案，新版本進入稽核狀態，而使用者則讀老版本的新聞。
  • 我：而MVCC機制翻譯過來也就是多版本併發控制技術，是InnoDB中用來解決讀-寫事務併發衝突問題的，對於多事務併發執行的情況下，InnoDB引擎的表在更新某條資料時，並不會阻塞嘗試讀取這條資料的事務，而是會讓讀資料的事務去拿更新前的資料記錄，和前面我給您的舉例類似，從而實現了讀寫事務併發執行。
  • 面試官：說的倒是蠻清晰的，那跟我說說MVCC機制是怎麼實現的呢？
  • 我：MVCC機制是通過Undo-log日誌的版本鏈、資料表上的隱藏欄位、以及ReadView讀檢視實現的，簡單來說就是：寫操作會直接對錶資料進行變更，而讀操作會根據回滾指標，去找到Undo-log中的舊資料讀取。
  • 面試官：嗯，你有了解過MySQL鎖機制的底層實現嗎？
  • 我：瞭解過的，MySQL鎖機制是基於事務實現的，一個事務嘗試獲取鎖時，就會在記憶體中生成一個鎖結構，鎖結構中會記錄著當前事務，要加鎖的資料地址，會精確到表空間、資料段、資料頁、行數的資訊。同時鎖結構中有一個is_waiting資訊，為0表示當前鎖結構對應事務持有著鎖，而為1表示當前鎖結構對應的事務在阻塞等待獲取鎖。
  • 我：一個事務嘗試獲取鎖時，會根據要上鎖的資料位置，去記憶體中看看是否已有對應資料位置的鎖結構，如果有就代表自己要獲取的鎖，已經被其他事務佔有了，這時還要去具體看一下鎖的位元位，看一下自己要獲取的行鎖，具體有沒有被加鎖，如果沒有，當前事務直接獲取鎖執行，如果有，當前事務阻塞等待，對應鎖結構中的is_waiting=1。
  • 面試官：嗯呢，那你有了解過事務隔離機制的底層實現嗎？
  • 我：這塊也略懂一些，每個隔離級別都是基於鎖和MVCC機制實現的，如下：
    • ①讀未提交/RU：寫操作加排他鎖，讀操作不加鎖。
    • ②讀已提交/RC：寫操作加排他鎖，讀操作使用MVCC，但每次select都生成讀檢視。
    • ③可重複讀/RR：寫操作加排他鎖，讀操作依舊採用MVCC機制，但一次事務中只生成一個讀檢視。
    • ④序列化/Serializable：所有寫操作加臨鍵鎖（具備互斥特性），所有讀操作加共享鎖。
  • 面試官：可以，先考你到這裡，時間不早了，我帶你去吃個午飯回來接著聊~
  • 我：好的，開我車去吧（一邊說一邊從口袋裡慢慢掏出了蘭博基尼的車鑰匙）。
  • 面試官：........

因為今晚臨時有事，所以得出去一趟，目前這章大概有1.2W字，全部寫完預計會有3~4W字左右，我忙完之後會回來繼續更，諸位對後續內容感興趣，可點個關注或收藏，耐心等待一小會兒時間~（時間也不會太久，大概明後天左右會徹底更完！本章屬於整個MySQL專欄的總結篇，所以寫起來速度會比較快，拋開事實不談的情況下，日更十萬字簡直不在話下，哈哈哈）

四、大展身手：對線MySQL日誌、記憶體與儲存引擎

  • 面試官：怎麼樣？我們公司樓下的炒飯味道還不錯吧？
  • 我：額.....，如果味道不難吃的話，應該還挺好吃的。
  • 我：咱們不耽誤時間了，接下來咱們聊什麼呢？
  • 面試官：對MySQL的日誌熟悉麼？
  • 我：略懂略懂，MySQL中的日誌種類不少，但常用的主要有六種：
    • ①Undo-log撤銷日誌：當有操作變更資料前，都會把老資料放入該日誌中。
    • ②Redo-log重做日誌：該日誌記錄著InnoDB所有表的變更語句，也可用來做災難恢復。
    • ③Bin-log變更日誌：這裡面記錄著所有對資料庫會產生變更的語句。
    • ④Error-log錯誤日誌：記錄著MySQL啟動、執行期間所有的報錯、警告資訊。
    • ⑤Slow-log慢查詢日誌：記錄著所有執行時長超出指定閾值的查詢語句。
    • ⑥Relay-log中繼日誌：主從叢集中，叢節點用於儲存主節點Bin-log資料的日誌。
  • 面試官：你跟我說說，為什麼有了Bin-log日誌還需要Redo-log日誌呢？
  • 我：這兩個日誌都會記錄資料庫的寫操作，但Redo-log是InnoDB引擎獨有的日誌，主要功能在於做災難恢復，每條寫入語句在執行前，都會先記錄一條prepare狀態的日誌，然後再執行SQL語句，執行完成後會記錄bin-log日誌，接著再把Redo-log日誌的狀態從prepare改為commit。如果一個事務提交後，資料在記憶體中還未刷盤，此時MySQL宕機了，後續重啟時也可以根據Redo-log來恢復資料。
  • 面試官：嗯呢，那你跟我說說這兩個日誌有啥區別呢？
  • 我：對於Redo-log、Bin-log兩者的區別，主要可以從四個維度上來說：
    • ①生效範圍不同，Redo-log是InnoDB專享的，Bin-log是所有引擎通用的。
    • ②寫入方式不同，Redo-log是用兩個檔案迴圈寫，而Bin-log是不斷建立新檔案追加寫。
    • ③檔案格式不同，Redo-log中記錄的都是變更後的資料，而Bin-log會記錄變更SQL語句。
    • ④使用場景不同，Redo-log主要實現故障情況下的資料恢復，Bin-log則用於資料災備、同步。
  • 面試官：那日誌是直接寫磁碟的嗎？
  • 我：不是的，MySQL、InnoDB專門在記憶體中設計了日誌緩衝區，不同日誌有不同的緩衝區，日誌也是先寫記憶體，然後由後臺執行緒來完成刷盤。
  • 面試官：嗯呢，你對Redo-log、Bin-log日誌的刷盤機制瞭解過麼？
  • 我：redo-log日誌的刷盤策略由innodb_flush_log_at_trx_commit引數控制，而bin-log日誌的刷盤策略則可以通過sync_binlog引數控制：
    • innodb_flush_log_at_trx_commit：
      • 0：間隔一段時間，然後再刷寫一次日誌到磁碟（效能最佳）。
      • 1：每次提交事務時，都刷寫一次日誌到磁碟（效能最差，最安全，預設策略）。
      • 2：有事務提交的情況下，每間隔一秒時間刷寫一次日誌到磁碟。
    • sync_binlog：
      • 0：同上述innodb_flush_log_at_trx_commit引數的2。
      • 1：同上述innodb_flush_log_at_trx_commit引數的1，每次提交事務都會刷盤，預設策略。
  • 同時，Redo-log是通過兩個日誌來迴圈寫，而Bin-log是通過多個日誌檔案來追加寫。
  • 面試官：再問一下，Bin-log日誌格式有哪些呢？
  • 我：總共有三種格式，如下：
    • Statment：記錄每一條會對資料庫產生變更操作的SQL語句（預設格式）。
    • Row：記錄具體出現變更的資料（也會包含資料所在的分割槽以及所位於的資料頁）。
    • Mixed：Statment、Row的結合版，可複製的記錄SQL語句，不可複製的記錄具體資料。
  • 面試官：如果你在線上因為不小心，誤刪了大量表資料或庫資料，你會怎麼做呢？
  • 我：這不用說啊，肯定是捲鋪蓋跑路！
  • 面試官：如果不能跑路呢？
  • 我：那就硬著頭皮上，由於bin-log日誌是按順序追加寫，一個日誌檔案滿了之後，會建立一個新的日誌檔案來存放記錄，在本地會呈現bin-log.0001、bin-log.0002、bin-log.000x.....這種形式，所以咱們只需要找到誤刪命令執行前的日誌檔案，然後通過日誌來恢復資料即可。
  • 面試官：那你有了解過Redo-log日誌的兩階段提交嘛？
  • 我：有的，這個主要是因為單階段提交，如果放在bin-log前寫入日誌，可能會導致主從叢集資料同步不一致，但如果放在bin-log後寫入日誌，則無法實現災難恢復，所以被設計成了在bin-log前後都寫入一次。
  • 面試官：嗯呢，那你們一般在線上環境中，如何定位哪些執行比較緩慢的查詢語句呢？
  • 我：會開啟慢查詢日誌，通過long_query_time引數指定時間閾值，MySQL會自動將超出閾值的查詢語句記錄進去。
  • 面試官：好，眾人都說MySQL是基於磁碟工作的，你對此怎麼看呢？
  • 我：我覺得它們說的沒有錯！
  • 面試官：......，有沒有一些別的見解呢？
  • 我：那我簡單發表一下我的看法吧，MySQL在設計的時候的確是基於磁碟工作，但因為MySQL的儲存引擎支援可拔插式，所以如果庫使用的是InnoDB引擎，這時情況就不同了。
  • 我：InnoDB會在記憶體中構建出一個BufferPool緩衝區，只要為其分配的記憶體足夠大，InnoDB基本上會把所有操作都放在記憶體中完成。
  • 面試官：何出此言吶？能否跟我細緻聊聊這BufferPool緩衝區其中的門道？
  • 我：可以的，InnoDB構建出的緩衝區，會把記憶體劃分為一個個的「頁」，每個頁的預設大小為16KB，以頁作為記憶體和磁碟互動的基本單位，這些緩衝頁會分為三種：
    • 空閒頁：指沒有被使用過的記憶體緩衝頁。
    • 資料頁：指已經被使用、用於存放磁碟表資料、索引資料以及其他各類的緩衝頁。
    • 變更頁（標記頁、髒頁）：指頁中資料發生過變更、還未被後臺執行緒刷寫到磁碟的緩衝頁。
  • 我：同時為了便於管理所有緩衝頁，每個緩衝頁都會有一個控制塊與其對應，其中記錄著磁碟資料的表空間、頁號、緩衝頁地址、連結串列節點指標等資訊，InnoDB可以基於控制塊去管理每一塊緩衝頁。
  • 面試官：那InnoDB具體會如何管理緩衝頁呢？
  • 我：InnoDB會基於三個連結串列來管理所有緩衝頁，所有緩衝頁會根據型別不同，分別加入到不同的連結串列中，每個緩衝頁通過控制塊中的指標，形成邏輯連續的連結串列結構：
    • Free List：負責記錄空閒頁，為了使用時能更快的找到空閒緩衝頁。
      • 當需要使用緩衝頁時，會從空閒連結串列中拿出一個空閒頁使用。
      • 當清空一個數據頁後，又會將沒有資料的緩衝頁加入到空閒連結串列。
    • Lru List：記錄所有已經使用過的緩衝頁，為了方便淘汰已使用的記憶體頁。
    • Flush List：負責記錄所有變更頁，為了刷盤時能夠更快的找到變更資料頁。
      • 當一個緩衝頁中的資料發生變更後，會從Lru連結串列移動到Flush連結串列中。
      • 當變更頁的資料刷寫完成後，又會從Flush連結串列中移回Lru連結串列。
  • 面試官：那一個磁碟資料被載入記憶體緩衝頁，會一直停留在記憶體嗎？
  • 我：不會的，因為這樣做會導致記憶體佔用無限增長，最終引發OOM記憶體溢位，所以有些資料頁會被淘汰出記憶體。
  • 面試官：那記憶體中的資料頁是如何被淘汰出去的呢？
  • 我：這就需要用到前面所說的Lru連結串列了，InnoDB會採用末尾淘汰機制，這正如大部分企業中推行的KPI績效機制類似，每個員工都會有KPI績效，到了年底時會淘汰一部分績效較低的員工，來年後再招聘新員工，吸收新鮮血液入職。
  • 我：而InnoDB中也類似，所有使用過的資料頁都會加入Lru連結串列中，但每當一個數據頁被訪問後，都會將其移動到連結串列的最前面，這樣就能夠保證熱度較高的資料頁長久留在記憶體中，及時淘汰掉那些熱度較低的資料頁。
  • 面試官：那如果在緩衝區記憶體較大的情況下，如果資料頁比較多，對於一些只使用過1~2次的資料，豈不是需要很久才能被淘汰嗎？
  • 我：對的，所以InnoDB把Lru連結串列分為了young、old兩個區域，預設比例為63:37：
    • young區域：存放經常被訪問的熱點資料頁。
    • old區域：存放剛從磁碟中加在的資料頁。
  • 我：LRU連結串列被劃分為兩個區域後，從磁碟中預讀的資料頁會加入到old區域的頭部，當這個資料頁被真正訪問時，才會將其插入young區的頭部。
  • 我：如果預讀的這頁在後續一直沒有被訪問，就會從old區域移除，從而不會影響young區域中的熱點資料。
  • 面試官：那假設此時有一個查詢操作，對一張千萬級別的表走了全表查詢，記憶體中的熱點資料頁全部被換出去了怎麼辦呢？
  • 我：這也不用擔心，前面說過首次載入記憶體的資料頁，會先放到old區域，而想要從old移到young區域，這是有晉升限制的。
  • 我：所謂的晉升限制，就是加了一個停留時間的限制，如果一個數據頁想從old晉升到young區，必須要在old區中存活一定時間，這個時間預設為1000ms。
  • 我：結合前面old進入young區的條件，資料頁想從Old轉到Young得滿足兩個條件：
    • ①在old區中停留的時間超過了1000ms。
    • ②在old區中，一秒後有執行緒再次訪問了這個資料頁。
  • 我：通過這兩個條件限制，就能夠有效避免你前面所說的緩衝池汙染問題，也就是大查詢操作替換掉熱點資料頁的問題。
  • 面試官：很不錯嘛，那你跟我說說InnoDB內部的執行過程吧。
  • 我：這個不難，InnoDB在處理讀寫語句時也會有細微差距：
    • 讀語句：首先會根據SQL語句，將目標資料從磁碟載入記憶體，經過條件篩選後返回。
    • 寫語句：首先會將語句要操作的資料頁載入到記憶體，然後更改記憶體資料頁，由後臺執行緒去把變更資料刷寫到磁碟。
  • 面試官：嗯呢，如果是insert新增操作呢？之前磁碟沒有資料呀，如何處理呢？
  • 我：這也不用擔心，InnoDB有一個插入緩衝區，5.6之後叫做寫入緩衝區，專門用來處理新增操作，insert的資料會被放到這個緩衝區中，然後由後臺執行緒完成刷盤工作。
  • 面試官：嗯呢，那你有了解過InnoDB的自適應雜湊索引嗎？
  • 我：這個很簡單，因為Hash結構是所有資料型別中最快的，所以InnoDB會在執行期間，統計出一些經常走索引查詢的熱點資料，然後針對這些熱點索引資料，去為其建立雜湊索引，以此提升查詢效能。
  • 面試官：那你最後給我總結一下，MySQL記憶體中有什麼？
  • 我：......，您直接看圖吧。
    • MySQL記憶體結構
  • 面試官：說說MySQL常用的InnoDB、MyISAM兩款引擎之間的區別吧。
  • 我：可以，兩者之間其實有許許多多的細微差距，但我就簡單從七個大方向總結一下：
    • ①磁碟檔案不同：
      • MyISAM引擎的表會生成三個磁碟檔案：
        • table_name.frm：該檔案中儲存表的結構資訊。
        • table_name.MYD：該檔案中儲存表的行資料。
        • table_name.MYI：該檔案中儲存表的索引資料。
      • 而InnoDB引擎的表只會生成兩個磁碟檔案：
        • table_name.frm：該檔案中儲存表的結構資訊。
        • table_name.ibd：該檔案中儲存表的行資料和索引資料。
    • ②InnoDB支援聚簇索引，而MyISAM只支援非聚簇索引，因為它索引資料和表資料是分開儲存的。
    • ③InnoDB基於Undo-log日誌實現了事務機制，但MyISAM沒有，所以不支援事務。
    • ④InnoDB基於Redo-log日誌實現了故障恢復機制，但MyISAM則只能依靠Bin-log，因此會有丟失資料的風險。
    • ⑤InnoDB可以基於聚簇索引實現行鎖，同時還兼容表鎖，但MyISAM僅支援表鎖。
    • ⑥InnoDB因為支援行鎖以及MVCC機制，所以併發場景下的效能會遠超MyISAM引擎。
    • ⑦InnoDB由於設計了BufferPool緩衝池，所有記憶體利用度會遠超MyISAM引擎。
  • 面試官：好的，回答的蠻不錯。

五、戲入高潮：高階特性、效能調優與線上排查

  • 面試官：有用過儲存過程嗎？能否說說儲存過程的優劣勢呢？
  • 我：之前在專案中實現一些特殊需求時用過，個人理解中，儲存過程的優劣勢如下：
    • 使用儲存過程的好處：
      • 複用性：儲存過程被建立後可以被反覆呼叫，表結構發生變更也只需修改儲存過程。
      • 靈活性：儲存過程中可以用變數、流程控制語句，可以完成複雜的條件查詢和較繁瑣的運算。
      • 省資源：呼叫儲存過程只需要傳輸呼叫語句和引數，無需傳輸一條體積較大的SQL語句。
      • 高效能：執行多次後，會被編譯成機器碼駐留線上程緩衝區，後續執行時無需重新編譯。
      • 安全性：對於不同的儲存過程，可設定使用者的執行許可權，包括內部語句對客戶端來說是黑盒操作，減小了SQL被暴露的風險。
    • 使用儲存過程帶來的問題：
      • CPU開銷大：如果其中涉及大量邏輯運算工作，會導致MySQL所在的伺服器CPU飆升。
      • 記憶體佔用高：多次執行後的儲存過程會被編譯成機器碼，長期駐留在記憶體中，佔用不小空間。
      • 維護性差：過於複雜的儲存過程閱讀性較差，並且不支援Debug除錯，出錯時難以排查。
  • 面試官：那我如果想在新增資料之後，再做某個操作，不用AOP切面的話怎麼實現呢？
  • 我：可以用MySQL的觸發器來完成，insert、delete、update三個操作都可以新增前/後置處理器，效果與AOP切面類似。
  • 面試官：那如果我想定時執行某段SQL語句，不想用定時排程框架怎麼辦呢？
  • 我：可以用MySQL的定時器，支援按年、季、月、周、日、時、分、秒、毫秒等精度觸發。
  • 面試官：那一般你寫SQL時，經常會用那些語句、關鍵字、和函式呢？
  • 我：......，我給你一個命令大全，您自己看吧。
  • 面試官：前面看你有提到連線層優化，你對MySQL效能優化這塊熟悉嗎？
  • 我：同樣略知一二，效能調優是一門大學問，沒有通用的方案，調優都要取決於現有環境、業務來談，MySQL的效能優化可以從五個維度來說：
    • ①連線層的優化：調整客戶端DB連線池的引數和DB連線層的引數。
    • ②結構的優化：合理的設計庫表結構，表中欄位根據業務選擇合適的資料型別、索引。
    • ③引數/配置優化：調整引數的預設值，根據業務將各類引數調整到合適的大小。
    • ④架構優化：引入中介軟體減輕資料庫壓力，優化MySQL架構提高可用性。
    • ⑤編碼優化：根據庫表結構、索引結構優化業務SQL語句，提高索引命中率。
  • 面試官：你前面說的連線層優化，怎麼配置連線數來著？
  • 我：CPU核心數*2，如果硬碟材質是SSD的，那麼還可以再加個一，總的來說就是根據硬體來配置。
  • 面試官：好，那我這個連線數是配在客戶端還是MySQL呢？
  • 我：如果MySQL只有一個應用程式訪問，在客戶端配好最大連線數就行，如果提供給多個應用訪問，則需要限制一下MySQL的最大連線數。
  • 面試官：那你再詳細說說你提到的結構優化是咋回事呢？
  • 我：主要是表結構、欄位結構、索引結構這三方面的設計，我簡單的聊一聊：
    • 表結構優化：選擇合適的引擎、精細化欄位數量、選擇合適的主鍵、適當冗餘欄位和中間表。
    • 欄位結構優化：
      • ①在保證足夠使用的範圍內，選擇最小資料型別，如能用int就別用bigint。
      • ②儘量避免索引欄位值為NULL，因為欄位空值過多會影響索引效能。
      • ③在條件允許的情況下，儘量使用最簡單的型別代替複雜的型別。
    • 索引結構優化：
      • ①儘量避免建立過多的單列索引，對於多個欄位要用索引，可以用聯合索引代替。
      • ②對於一個值較長的欄位建立索引，可以使用前N個位元組，建立字首索引。
      • ③索引型別一定要選擇合理，如經常做模糊查詢的欄位，可用全文索引代替普通索引。
      • ④某個欄位在業務中無需用作範圍查詢時，可以通過Hash結構代替B+Tree結構。
  • 面試官：引數優化呢？
  • 我：引數優化不太熟，有專門的DBA負責，個人一般就是調大緩衝區、執行緒緩衝區這些。
  • 面試官：架構優化呢？
  • 我：這塊還比較熟悉，主要分為引入第三方技術棧調整業務架構，以及調整資料庫的部署架構：
    • 引入第三方技術棧調整業務架構：
      • 引入Redis做快取，來減少落入資料庫中的讀請求，分擔大部分讀壓力。
      • 引入MQ做削峰，來將併發情況下的寫壓力，平緩到資料庫可承載的級別。
    • 調整資料庫的部署架構：
      • 讀寫分離架構：搭建主從叢集，主機負責處理外部寫請求，從機負責處理外部讀請求。
      • 多主多寫架構：搭建多主叢集，叢集內任意節點，都具備獨立處理讀寫請求的能力。
      • 垂直分庫架構：對整庫按照業務進行拆分，不同業務屬性的流量分發到不同庫處理。
      • 水平分庫架構：在垂直分庫基礎上，再對核心業務庫做橫向拓展，提升核心庫的吞吐。
  • 面試官：回答的都挺不錯的，再聊聊SQL優化吧。
  • 我：所謂的SQL優化，核心就是減小查詢的資料量、提升SQL的索引命中率，在寫SQL的時候刻意注意下述一些原則即可：
  • 我：一般在寫SQL時注意上述十六條原則，通常寫出的語句，其效率都不會太差。
  • 面試官：一般你們線上的SQL執行標準是多久呢？
  • 我：普通語句要控制在200ms以內，超出這個時間，就會導致客戶端長時間無響應。
  • 面試官：嗯呢，當線上有些語句執行較為緩慢時，你會如何定位呢？
  • 我：部署MySQL前會開啟慢查詢日誌，直接檢視慢查詢日誌即可。
  • 面試官：那定位到慢查詢語句後，你會怎麼排查問題呢？
  • 我：定位到具體的慢查詢日誌後，接著會去通過explain工具分析語句，到底是沒走索引、還是由於掃描的資料量較大，然後對症下藥解決問題。
  • 面試官：你在線上有碰到過客戶端連線異常的情況麼？連線異常會有哪些原因導致呢？
  • 我：連線異常這種情況比較少見，一般出現的原因如下：
    • ①總體的現有連線數，超出了MySQL中的最大連線數，此時再出現新連線時就會出異常。
    • ②客戶端資料庫連線池與MySQL版本不匹配，或超時時間過小，也可能導致出現連線中斷。
    • ③MySQL、Java程式所部署的機器不位於同一個網段，兩臺機器之間網路存在通訊故障。
    • ④部署MySQL的機器資源耗盡，如CPU、硬碟過高，導致MySQL沒有資源分配給新連線。
  • 面試官：對於每種情況出現時，該怎麼解決呢？
  • 我：前面兩種比較簡單，檢查一下相關的配置，然後調整一下配置項即可，如果是網路問題，排查的思路如下：
    • 檢測防火牆與安全組的埠是否開放，或與外網機器是否做了埠對映。
    • 檢查MySQL的機器白名單，及登入的使用者IP限制，可能是IP不在白名單範圍內。
    • 如果整個系統各節點部署的網段不同，檢查各網段之間交換機的連線超時時間是多少。
    • 檢查不同網段之間的頻寬大小，及具體的頻寬使用情況，有時會因頻寬佔滿出現問題。
    • 如果用了MyCat這類代理中介軟體，記得檢查中介軟體的白名單、超時時間等配置。
  • 我：如果是機器資源被耗盡，那首先需要找到資源消耗較高的語句，然後對語句進行優化後才能解決連線異常。
  • 面試官：有在線上排查過MySQL死鎖問題麼？
  • 我：有的，雖然MySQL會有死鎖檢測機制主動解除已發生的死鎖，但無法徹底根治死鎖，想要根治必須要先找到頻繁觸發死鎖的事務，步驟如下：
    • ①先通過SHOW ENGINE INNODB STATUS\G命令，查詢InnoDB的執行時日誌。
    • ②找到日誌中的LATEST DETECTED DEADLOCK區域，這其中會記錄發生過的死鎖。
    • ③檢視日誌中觸發死鎖次數最多的語句，分析SQL為何會產生死鎖，然後調整即可。
  • 面試官：那伺服器CPU佔用100%該怎麼排查呢？
  • 我：這個很簡單，屬於一種比較常見的線上問題，排查思路如下：
    • ①先找到CPU過高的伺服器。
    • ②然後在其中定位到具體的程序。
    • ③再定位到程序中具體的執行緒。
    • ④再檢視執行緒正在執行的程式碼邏輯。
    • ⑤最後從程式碼層面著手優化掉即可。
  • 面試官：MySQL磁碟利用率達到100%呢，什麼情況下會出現，出現後如何解決？
  • 我：MySQL磁碟利用率達到100%的原因，通常是因為磁碟佔用過高導致，佔用過高的情況有很多種，如下：
    • ①突然大量變更庫中資料，需要執行大量寫入操作，如主從資料同步時就會出現此問題。
    • ②MySQL整體併發過高，磁碟I/O頻率跟不上，比如是機械硬碟材質，讀寫速率過慢。
    • ③記憶體中的BufferPool緩衝池過小，大量讀寫操作落入磁碟處理，導致磁碟利用率過高。
    • ④頻繁使用臨時表，導致記憶體無法存下臨時表資料，因而轉到磁碟儲存，導致磁碟飆升。
    • ⑤執行某些語句從磁碟載入海量資料，如多表聯查時，每張表資料較大，導致IO打滿。
    • ⑥日誌刷盤頻率過高，這條是①、②的附帶情況，畢竟日誌的刷盤頻率，跟整體併發掛鉤。
  • 我：解決方案如下：
    • ①如果磁碟不是SSD材質，請先將磁碟升級成固態硬碟，MySQL對SSD硬碟有特殊優化。
    • ②在專案中記得引入Redis降低讀壓力，引入MQ對寫操作做流量削峰。
    • ③調大記憶體中BufferPool緩衝池的大小，最好設定成機器記憶體的70~75%左右。
    • ④撰寫SQL語句時儘量減少多張大表聯查，不要頻繁的使用和銷燬臨時表。
  • 面試官：很不錯，你小子和我胃口！

六、愈演愈熱：論述表分割槽、新特性與高可用叢集

  • 面試官：如果一張表的資料較大，但歷史資料又很少使用，你會如何處理呢？
  • 我：做表分割槽，可以對資料按時間或者按資料量級分割槽，將不同範圍的資料劃分到不同分割槽中儲存。
  • 面試官：嗯呢，對MySQL各版本的新特性有了解過嗎？
  • 我：版本太多了沒完全瞭解，但研究過MySQL5.6、5.7、8.0這三個重量級版本。
  • 面試官：那你先說說MySQL5.6有什麼重要的特性呢？
  • 我：MySQL5.6屬於一個里程碑式的版本，在這個版本中效能改善很大，重點有六個改進：
  • 面試官：嗯呢，再接著給我說說MySQL5.7中的特性吧。
  • 我：5.7版本中更多是在改善5.6中的問題，因為優化太多，所以有很多細節需要改進，對於新特性就兩個較為重要的：
  • 面試官：好，最後說說MySQL8.0的新特性吧。
  • 我：MySQL8.0是改進較大的一個版本，其中發生的變更比較多，主要也有七點：
  • 面試官：你說提到的這個雜湊連線是什麼東西？我怎麼沒用過呢？
  • 我：這個是連表查詢的一種連線演算法，之前版本中的聯表查詢都採用Nest-Loop-Join巢狀迴圈連線演算法，而8.0中，在適當情況下會選擇Hash-Join演算法提升查詢效能。
  • 面試官：你說的Nest-Loop-Join巢狀迴圈連線演算法，自己有深入瞭解過嗎？
  • 我：有的，在這種聯表查詢演算法中，會分為驅動表和被驅動表，驅動表則是作為條件的表，被驅動表則是需要去檢索資料的表。
  • 我：Nest-Loop-Join演算法執行時，會通過迴圈巢狀的模式工作，外層迴圈遍歷驅動表的資料，內層迴圈遍歷被驅動表的資料，然後再進行目標資料的檢索，最終得到目標資料。
  • 面試官：嗯呢，那為什麼需要引入Hash-join演算法呢？
  • 我：因為Nest-Loop-Join演算法執行時，因為採用的是迴圈巢狀，所以效能方面並不高。
  • 面試官：那你說的Hash-Join演算法效率很高麼？
  • 我：在用=符號做等值連線查詢時的確如此，在雜湊演算法中會分為構建表和探測表，構建表則是作為條件的表，探測表是需要檢索資料的表。
  • 我：首先MySQL會對構建表的每行資料生成雜湊值，然後最終得到一張雜湊表，接著只需要迴圈探測表的資料，將每條資料計算出雜湊值，然後去雜湊表中匹配即可。
  • 面試官：好的。
  • 面試官：如果你線上單臺節點抗不住併發，你會如何處理呢？
  • 我：首先我會考慮搭建主從叢集來解決併發問題。
  • 面試官：那你說說使用主從叢集有啥好處？
  • 我：使用主從複製方案，可以進一步提升資料庫的可用性和效能，主要有四大好處：
    • ①在主機宕機或故障的情況下，從節點能自動升級成主機，從而繼續對外提供服務。
    • ②提供資料備份的功能，當主節點的資料發生損壞時，從節點中依舊儲存著完整資料。
    • ③可以實現讀寫分離，主節點負責處理寫請求，從節點處理讀請求，進一步提升效能。
    • ④可以實現多主多寫，資料庫系統可以由多個節點組成，共同對外提供讀寫處理的能力。
  • 面試官：那你認為主從叢集這種方案，有沒有啥問題呢？
  • 我：有的，主要有三個問題：
    • ①硬傷：木桶效應，主從叢集中所有節點的容量，受限於容量最低的那臺伺服器。
    • ②資料一致性問題：同步資料的過程是基於網路傳輸完成的，所以存在資料延遲。
    • ③腦裂問題：從機會通過心跳機制來判斷主機是否存活，網路故障情況下會產生多主。
  • 面試官：嗯呢，對MySQL主從叢集的資料同步的原理熟悉麼？
  • 我：這塊還比較熟悉，我先給您畫個圖：
    • 主從複製原理
    • ①客戶端將寫入資料的需求交給主節點，主節點先向自身寫入資料。
    • ②資料寫入完成後，緊接著會再去記錄一份Bin-log二進位制日誌。
    • ③配置主從架構後，主節點上會建立一條專門監聽Bin-log日誌的log dump執行緒。
    • ④當log dump執行緒監聽到日誌發生變更時，會通知從節點來拉取資料。
    • ⑤從機會有專門的I/O執行緒等待主節點的通知，當收到通知時會去請求一定範圍的資料。
    • ⑥當從機在主機上請求到一定資料後，接著會將得到的資料寫入到relay-log中繼日誌。
    • ⑦從機上也會有專門負責監聽relay-log變更的SQL執行緒，當日志出現變更會開始工作。
    • ⑧監聽到變更後，接著會從中讀取日誌記錄，然後解析日誌並將資料寫入到自身磁碟中。
  • 面試官：一般搭建主從叢集時，有幾種架構可選呢？
  • 我：有一主一從/多從、雙主/多主、多主一從、級聯複製這四種架構可選：
    • ①一主一從/多從：可以基於此結構的叢集實現讀寫分離，適用於讀大於寫的場景。
    • ②雙主/多主：各節點間互為主從，各節點都具備處理讀寫的能力，適用於讀寫參半的場景。
    • ③多主一從：一個從節點同步多個主節點的資料，適用於寫大於讀的場景。
    • ④級聯複製：方案①的改良版，一個節點同步主機資料，其他節點同步該節點的資料。
  • 面試官：MySQL主從同步資料時，是主節點推送還是從節點拉取？
  • 我：主推+從拉的結合方案，當主節點資料發生變更時，主機會通知從機，然後從機來拉取資料。
  • 面試官：嗯呢，那你能跟我說說，主從同步複製資料時，有哪些方式嗎？
  • 我：MySQL中主從之間的資料複製，支援四種同步方式：
    • ①同步複製：主機收到客戶端寫入請求後，需要等待所有從機都寫入完成後再返回。
    • ②非同步複製：主機收到客戶端寫入請求後，自身寫入資料就立即返回。
    • ③半同步複製：主機收到客戶端寫入請求後，自身寫入成功+一個從節點寫入成功就返回。
    • ④無損複製：第③種的增強版，在未收到從機寫入成功的ACK之前，不會提交事務。
  • 我：從效能來說① < ④ < ③ < ②，從資料一致性來說② < ③ < ④ < ①。
  • 面試官：那你在說5.6特性時，提到的延遲複製、GTID複製、並行複製是啥意思？
  • 我：這是對於主從複製的三種增強機制：
    • 延遲複製：支援從機同步資料後，並不立刻執行寫入操作，而是等待指定時間後再寫入。
    • GTID複製：主從的同步點依靠全域性事務ID來實現，開啟後無需人工指定資料同步點。
    • 並行複製：指主節點上怎麼併發寫入資料的，從節點也開啟相同數量的執行緒寫入資料。
  • 面試官：那你們線上主從庫之間同步存在延遲，資料一致性問題如何解決的呢？
  • 我：前面講過，主從資料是依靠網路來同步的，所以有延遲很正常，處理一致性問題共有五種方案：
    • ①更改業務邏輯：當用戶變更資料後，先顯示「稽核中」的狀態，同步到從庫後再讓查詢。
    • ②更改同步方式：將複製資料的方式改為同步複製，這樣能夠保障主從資料的嚴格一致性。
    • ③調整部署架構：將部署結構升級到分庫分表，按業務對資料進行分片，每次讀對應資料。
    • ④引入中介軟體：通過Canal來監控主機的Bin-log日誌，一發生變化就立馬同步資料。
    • ⑤調整分發規則：對於一致性較為敏感的資料，強制改寫並路由到主節點上讀取資料。
  • 面試官：嗯呢，那你自己做過主從叢集的搭建麼，具體過程能跟我說一下嗎？
  • 我：......，這個主要是改配置檔案，我給你一個搭建教程看吧。

七、戲至巔峰：共探分庫分表的奧妙

  • 面試官：如果你的業務規模，使用主從叢集無法解決併發壓力怎麼辦呢？
  • 我：會直接選用分庫分表方案。
  • 面試官：嗯呢，那你跟我說說為什麼要分庫分表呢？
  • 我：併發請求過高、資料查詢較慢、單表/單庫資料量過大、資料庫出現效能瓶頸。
  • 面試官：那分庫分表究竟是怎麼回事，如何拆分呢？
  • 我：分庫、分表、分庫分表這是三個概念：
    • 分庫：不對庫內的表做拆分，所有節點的表結構完全一致，主從叢集就是這種形式。
    • 分表：不分庫，而是在單庫內對一張大表做拆分。
    • 分庫分表：先拆分庫，再對庫中的表做拆分。
  • 我：分庫分表主要有兩種拆分方式，水平拆分和垂直拆分，好比一個西瓜，橫著切叫水平，豎著切叫垂直：
    • 垂直分表：將一張欄位過多的表，拆分成多張欄位更精細化的小表。
    • 水平分表：將一張資料量過大的表，按時間或資料量等方式，拆分為多張資料量較小的表。
    • 垂直分庫：對一個壓力較高的大庫，按業務屬性來分成不同的業務庫。
    • 水平分庫：對一個壓力較高的核心業務庫，再對其做水平拓展，分成多個核心業務庫。
  • 面試官：那分庫分表能夠帶來什麼好處呢？
  • 我：分庫分表能夠讓資料儲存層真正達到高可用、高效能、高拓展的三高水準：
    • ①能夠得到最大的效能收益，吞吐量會隨機器數量呈直線性增長。
    • ②能夠最大程度上保障儲存層的高可用，任意節點宕機都不會影響整體業務的運轉。
    • ③具備很強的容錯率，當一個庫存在問題需要重構時，無需將所有業務停機更新。
    • ④具備高穩定性，分庫+配備完善的監控重啟策略後，能確保線上無需人工介入管理。
  • 面試官：對於一張日均資料量增長超百萬的表，你會如何處理呢？
  • 我：會採用水平分表策略，按幾天為一張表做拆分，劃分為一張張的小表。
  • 面試官：具體如何做的呢？
  • 我：用儲存過程+定時器自動按日期建立表，再將業務層的語句做改寫，具體可以看這個。
  • 面試官：那分庫分表之後，SQL語句該如何執行呢？
  • 我：這需要配置好路由鍵和分片演算法，只要根據配置好的分片規則，定位到資料表即可。
  • 面試官：那水平拆分出多個數據表之後，如何做聯表查詢呢？
  • 我：聯表查詢有多種方案來實現，但效能肯定比不上傳統的單庫查詢，方案如下：
    • ①如果分表數量是固定的，直接對所有表進行連線查詢，但這樣效能開銷較大。
    • ②如果表數量會隨時間不斷變多，那就先根據分表規則，去確定要連線哪張表後再查詢。
    • ③如果每次連表只需要從中獲取1~3個欄位，可直接在另一張表中設計冗餘欄位。
  • 面試官：那水平分表後，如何使用count()、sum()...這類聚合函式呢？
  • 我：有三種方案，可以靠中介軟體、中間表、資料聚合解決：
    • ①提前聚合好資料放入第三方中介軟體中，然後依賴於第三方中介軟體完成，如ES、Redis。
    • ②定期跑指令碼查詢出一些常用的聚合資料，然後建立中間表，每次查詢從中間表中讀取。
    • ③首先從所有表中統計出各自的資料，然後在Java中作聚合操作，從而得到出最終資料。
  • 面試官：那垂直分庫之後的跨庫Join問題該怎麼解決呢？
  • 我：不同業務表之間需要做關聯查詢，這種情況很常見，解決方案有四種：
    • ①在不同庫的表中冗餘欄位，把常用的欄位放到需要要資料的表中，避免跨庫連表。
    • ②選擇同步資料，通過全域性表/廣播表等方式，將需要的表資料直接完全同步到相應庫中。
    • ③在設計庫表拆分時配置繫結表，具備主外來鍵的表放在一個庫，保證資料落到同一資料庫。
    • ④業務系統中組裝資料，呼叫相應服務的API介面獲取資料，然後在程式中組裝後返回。
  • 面試官：那垂直分庫之後的分散式事務問題，又該如何解決呢？
  • 我：目前業內的主流方案是使用Seata框架，內部提供了多種模式支援，思想如下：
    • ①Best Efforts 1PC模式。
    • ②XA 2PC、3PC模式。
    • ③TTC事務補償模式。
    • ④MQ最終一致性事務模式。
  • 面試官：如果垂直分庫之後，某些核心庫的訪問壓力依舊比較大怎麼辦？
  • 我：對核心庫再做水平拆分，將核心庫再橫向拓展出多個節點。
  • 面試官：嗯呢，那水平分庫之後，如果做資料分頁呢？
  • 我：處理分頁問題依舊有三種方案：
    • ①常用的分頁資料提前聚合到ES或中間表，執行期間跑按時更新其中的分頁資料。
    • ②搭建大資料中臺，將所有子庫資料匯聚到其中，後續的分頁資料直接從中獲取。
    • ③先從所有子庫中先拿到目標頁的資料，然後在Service層再做過濾處理。
  • 面試官：那水平分庫之後如何保障主鍵欄位的唯一性？
  • 我：可以設定自增步長、使用分散式序列演算法、或設計第三方主鍵生成器：
    • ①通過設定自增機制的起始值和步長，來控制不同節點的ID交叉增長，保證唯一性。
    • ②在業務系統中，利用特殊演算法生成有序的分散式ID，比如Snowflake雪花演算法等。
    • ③設計ID生成器，如使用Redis的incr命令、或建立獨立的庫專門做自增ID工作。
  • 面試官：那水平分庫之後資料該具體落入哪個庫中呢？
  • 我：這依舊要根據配置好的分片規則來決定，如：
    • ①隨機分片：隨機分發資料，但查詢時需要讀取全部節點才能拿取資料，一般不用。
    • ②連續分片：每個節點負責儲存一個範圍內的資料，如DB1:1~500W、DB2:500~1000W....。
    • ③取模分片：通過數值ID或雜湊值與節點數量做取模運算，最終得到資料落入的節點。
    • ④一致性雜湊：根據某個具備唯一特性的欄位值計算雜湊值，再通過雜湊值做取模分片。
    • ..........
  • 面試官：那如果水平分庫後，系統再次出現瓶頸時，你會如何擴容呢？
  • 我：如果是對單個業務庫做水平分庫，採用非同步雙寫法，如果是對水平分庫的業務庫再擴容，則採用水平雙倍擴容法。
  • 面試官：那如果水平分庫後如何做多維度查詢？
  • 我：設計多個列組成路由鍵，或者為多個列規劃多個水平庫叢集，也可以維護二級索引。
  • 面試官：那你在做分庫分表的時候，會考慮哪些技術棧？
  • 我：MyCat和Apache-Sharding-Sphere，個人更傾向於後者。
  • 面試官：好的，那分庫分表之後，應用程式訪問資料庫的過程，具體是什麼樣的呢？
  • 我：我先給你再畫張圖吧：
    • 分庫分表工作流程
    • ①應用程式的SQL不需要改變，只需要配置好分片策略中的邏輯表後就可以了。
    • ②首先會根據配置好的路由鍵以及分片演算法，去定位到最終的資料節點（資料表）。
    • ③改寫原本的邏輯SQL語句，然後發往具體的資料節點執行。
  • 面試官：那你知道Apache-Sharding-Sphere的工作原理嘛？
  • 我：我簡單說一下大體流程哈，如下：
    • 配置載入：在程式啟動時，會讀取使用者的配置好的資料來源、資料節點、分片規則等資訊。
    • SQL解析：SQL執行時，會先根據配置的資料來源來呼叫對應的解析器，然後對語句進行拆解。
    • SQL路由：拆解SQL後會從中得到路由鍵的值，接著會根據分片演算法選擇單或多個數據節點。
    • SQL改寫：選擇了目標資料節點後，接著會改寫、優化使用者的邏輯SQL，指向真實的庫、表。
    • SQL執行：對於要在多個數據節點上執行的語句，內部開啟多執行緒執行器非同步執行每條SQL。
    • 結果歸併：持續收集每條執行緒執行完成後返回的結果集，最終將所有執行緒的結果集合並。
    • 結果處理：如果SQL中使用了order by、max()、count()...等操作，對結果處理後再返回。
  • 面試官：MyCat和Apache-Sharding-Sphere有啥區別呢？
  • 我：Sharding-Sphere是由Sharding-Porxy、MyCat兩款產品組成的，三者對比如下：

| 對比項 | Sharding-JDBC | Sharding-Proxy | MyCat | | :-: | :-: | :-: | :-: | | 效能開銷 | 較低 | 較高 | 高 | | 異構支援 | 不支援 | 支援 | 支援 | | 網路次數 | 最少一次 | 最少兩次 | 最少兩次 | | 異構語言 | 僅支援Java | 支援異構 | 支援異構 | | 資料庫支援 | MySQL、PgSQL | 任意資料庫 | 任意資料庫 | | 配置管理 | 去中心化 | 中心化 | 中心化 | | 部署方式 | 依賴工程 | 中介軟體 | 中介軟體 | | 業務侵入性 | 較低 | 無 | 無 | | 連線開銷 | 高 | 低 | 低 | | 事務支援 | XA、Base、Local事務 | 同前者 | XA事務 | | 功能豐富度 | 多 | 多 | 一般 | | 社群活躍性 | 活躍 | 活躍 | 一言難盡 | | 版本迭代性 | 高 | 高 | 極低 | | 多路由鍵支援 | 2 | 2 | 1 | | 叢集部署 | 支援 | 支援 | 支援 | | 分散式序列 | 雪花演算法 | 雪花演算法 | 自增序列 |

八、大戲落幕：放在最後的結語

  • 面試官：可以呀，你小子果然是真的精通MySQL！
  • 我：謬讚了！不敢當，不敢當~
  • 面試官：最後問你一下，看過MySQL原始碼沒有？
  • 我：沒看過太多。
  • 面試官：啊？這樣啊，那你期望薪資是多少呢？
  • 我：你看著給就行！
  • 面試官：嗯，因為你對MySQL原始碼還不熟悉，所以給你開三千五，幹不幹！
  • 我：我..............，幹！您看人可真準~，嘿嘿
  • 面試官：好，那你這邊最快多久能入職呢？
  • 我一臉嚴謹，站起身來鄭重的道：請現在立刻帶我去到我的工位！！！

結語

   疫情當下，這讓原本很多一年一跳一漲薪的開發者，從此進入了網際網路寒冬，企業縮招、停招、裁員等情況屢見不鮮，雖然相較於其他傳統行業而言，IT開發行業受影響範圍小很多，但依舊造成了一系列的連鎖反應，隨著應屆畢業生越來越多，這也讓諸多崗位的要求越來越高，但薪資反而越來越低。

同時，無論是工作一段時間、或工作多年的程式設計師，亦或是剛從校園畢業的應屆生，為了能夠更好的找到符合意願的工作，近兩年的內卷更為嚴重，離職待業的開發者在家學技術，在職工作的程式設計師為了應對隨時可能發生的“優化”，也仍然在學習的路上不斷前行，也包括了一些畢業後沒有找到理想工作的應屆生，幾乎各自身上都有著學習的影子。

但許許多多在學習路途上“埋頭苦幹”的小夥伴，基本上都只是為了應付面試而在學習，諸多時候為了使自己面試造火箭的能力更上一層樓，而這章關於MySQL面試的文章，也真心希望能夠幫助到每一位準備或正在面試的後端小夥伴，助力於每一位求職者拿到心滿意足的Offer，我與諸君共勉之！

「其他文章」
「」
「」