35道MySQL面試必問題圖解,這樣也太好理解了吧

程序員鴻哥 發佈 2022-07-05T16:25:48.654263+00:00

關於面試題的文章之前發過不少,但讀者們給的反饋是普遍是理解難,看完就忘。buffer pool 是一塊內存區域,為了「提高資料庫的性能」,當資料庫操作數據的時候,把硬碟上的數據加載到 buffer pool,不直接和硬碟打交道,操作的是 buffer pool 裡面的數據,資料庫的增刪改查都是在 buffer pool 上進行。

關於面試題的文章之前發過不少,但讀者們給的反饋是普遍是理解難,看完就忘。所以今天我會用畫圖來給大家分析一下這35道題,希望能夠加深你們的理解。

篇幅所限,本文先寫Mysql的35問,其他的技術棧我也都整理好了,後面會持續更新,點個關注不迷路[看]

當然我知道肯定有等不及的同學,所以我會把整理好的文檔都無償分享出來,如圖

領取方式:轉發本文+關注+私信【512】即可

好了,那廢話不多說,我們一起來揭開這35道題的神秘面紗

1.說一說三大範式

「第一範式」:資料庫中的欄位具有「原子性」,不可再分,並且是單一職責「第二範式」:「建立在第一範式的基礎上」,第二範式要求資料庫表中的每個實例或行必須「可以被惟一地區分」。為實現區分通常需要為表加上一個列,以存儲各個實例的惟一標識。這個唯一屬性列被稱為主鍵「第三範式」:「建立在第一,第二範式的基礎上」,確保每列都和主鍵列直接相關,而不是間接相關不存在其他表的非主鍵信息

但是在我們的日常開發當中,「並不是所有的表一定要滿足三大範式」,有時候冗餘幾個欄位可以少關聯幾張表,帶來的查詢效率的提升有可能是質變的

2.MyISAM 與 InnoDB 的區別是什麼?

「InnoDB支持事務,MyISAM不支持」。「InnoDB 支持外鍵,而 MyISAM 不支持」。「InnoDB是聚集索引」,使用B+Tree作為索引結構,數據文件是和索引綁在一起的,必須要有主鍵。「MyISAM是非聚集索引」,也是使用B+Tree作為索引結構,索引和數據文件是分離的,索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。「InnoDB 不知道保存表的具體行數」。

「MyISAM 用一個變量保存了整個表的行數」。5.Innodb 有 「redolog」 日誌文件,MyISAM 沒有6.「Innodb存儲文件有frm、ibd,而Myisam是frm、MYD、MYI」Innodb:frm是表定義文件,ibd是數據文件Myisam:frm是表定義文件,myd是數據文件,myi是索引文件「InnoDB 支持表、行鎖,而 MyISAM 支持表級鎖」8、「InnoDB 必須有唯一索引(主鍵)」,如果沒有指定的話 InnoDB 會自己生成一個隱藏列Row_id來充當默認主鍵,「MyISAM 可以沒有」

3.為什麼推薦使用自增 id 作為主鍵?

1.普通索引的 B+ 樹上存放的是主鍵索引的值,如果該值較大,會「導致普通索引的存儲空間較大」2.使用自增 id 做主鍵索引新插入數據只要放在該頁的最尾端就可以,直接「按照順序插入」,不用刻意維護3.頁分裂容易維護,當插入數據的當前頁快滿時,會發生頁分裂的現象,如果主鍵索引不為自增 id,那麼數據就可能從頁的中間插入,頁的數據會頻繁地變動,「導致頁分裂維護成本較高」

4.一條查詢語句是怎麼執行的?

1.通過連接器跟客戶端「建立連接」2.通過查詢「緩存查詢」之前是否有查詢過該 sql有則直接返回結果沒有則執行第三步3.通過分析器「分析該 Sql 的語義」是否正確,包括格式,表等等4.通過優化器「優化該語句」,比如選擇索引,join 表的連接順序5.「驗證權限」,驗證是否有該表的查詢權限沒有則返回無權限的錯誤有則執行第六步6.通過執行器調用存儲引擎執行該 sql,然後返回「執行結果」

5.使用 Innodb 的情況下,一條更新語句是怎麼執行的?

用以下語句來舉例,c 欄位無索引,id 為主鍵索引

update T set c=c+1 where id=2;

1.執行器先找引擎去取 id=2 這一行。id 是主鍵,引擎直接用樹搜索找到這一行如果 id=2 這一行所在的數據頁本來就「在內存中」,就「直接返回」給執行器「不在內存」中,需要先從磁碟「讀入內存」,然後再「返回」2.執行器拿到引擎給的行數據,把這個值加上 1,比如原來是 N,現在就是 N+1,得到新的一行數據,再調用引擎接口「寫入這行新數據」3.引擎將這行新數據更新到內存中,同時將這個更新操作「記錄到 redo log 裡面」,此時 redo log 處於 「prepare」 狀態。然後告知執行器執行完成了,隨時可以提交事務4.執行器「生成這個操作的 binlog」,並把 binlog 「寫入磁碟」5.執行器調用引擎的「提交事務」接口,引擎把剛剛寫入的 redo log 改成提交(commit)狀態,「更新完成」

6.Innodb 事務為什麼要兩階段提交?

先寫 redolog 後寫binlog。假設在 redolog 寫完,binlog 還沒有寫完的時候,MySQL 進程異常重啟,這時候 binlog 裡面就沒有記錄這個語句。然後你會發現,如果需要用這個 binlog 來恢復臨時庫的時候,由於這個語句的 「binlog 丟失」,這個臨時庫就會少了這一次更新,恢復出來的這一行 c 的值就是 0,與原庫的值不同。先寫 binlog 後寫 redolog。如果在 binlog 寫完之後 crash,由於 redolog 還沒寫,崩潰恢復以後這個事務無效,所以這一行c的值是0。但是 binlog 裡面已經記錄了「把c從0改成1」這個日誌。所以,在之後用 binlog 來恢復的時候就「多了一個事務出來」,恢復出來的這一行 c 的值就是 1,與原庫的值不同。

可以看到,「如果不使用「兩階段提交」,那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致」。

7.什麼是索引?

相信大家小時候學習漢字的時候都會查字典,想想你查字典的步驟,我們是通過漢字的首字母 a~z 一個一個在字典目錄中查找,最終找到該字的頁數。想想,如果沒有目錄會怎麼樣,最差的結果是你有可能翻到字典的最後一頁才找到你想要找的字。

索引就「相當於我們字典中的目錄」,可以極大地提高我們在資料庫的查詢效率。

8.索引失效的場景有哪些?

以下隨便列舉幾個,不同版本的 mysql 場景不一

1.最左前綴法則(帶頭索引不能死,中間索引不能斷2.不要在索引上做任何操作(計算、函數、自動/手動類型轉換),不然會導致索引失效而轉向全表掃描3.不能繼續使用索引中範圍條件(bettween、<、>、in等)右邊的列,如:

select a from user where c > 5 and b = 4;

4.索引欄位上使用(!= 或者 < >)判斷時,會導致索引失效而轉向全表掃描5.索引欄位上使用 is null / is not null 判斷時,會導致索引失效而轉向全表掃描。6.索引欄位使用like以通配符開頭(『%字符串』)時,會導致索引失效而轉向全表掃描,也是最左前綴原則。7.索引欄位是字符串,但查詢時不加單引號,會導致索引失效而轉向全表掃描8.索引欄位使用 or 時,會導致索引失效而轉向全表掃描

9.為什麼採用 B+ 樹,而不是 B-樹

B+ 樹只在葉子結點儲存數據,非葉子結點不存具體數據,只存 key,查詢更穩定,增大了廣度,而一個節點就是磁碟一個內存頁,內存頁大小固定,那麼相比 B 樹,B- 樹這些「可以存更多的索引結點」,寬度更大,樹高矮,節點小,拉取一次數據的磁碟 IO 次數少,並且 B+ 樹只需要去遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的,效率更高。

10.WAl 是什麼?有什麼好處?

WAL 就是 Write-Ahead Logging,其實就是「所有的修改都先被寫入到日誌中,然後再寫磁碟」,用於保證數據操作的原子性和持久性。

好處:

1.「讀和寫可以完全地並發執行」,不會互相阻塞2.先寫入 log 中,磁碟寫入從「隨機寫變為順序寫」,降低了 client 端的延遲就。並且,由於順序寫入大概率是在一個磁碟塊內,這樣產生的 io 次數也大大降低3.寫入日誌當資料庫崩潰的時候「可以使用日誌來恢復磁碟數據」

11.什麼是回表?

回表就是先通過資料庫索引掃描出該索引樹中數據所在的行,取到主鍵 id,再通過主鍵 id 取出主鍵索引數中的數據,即基於非主鍵索引的查詢需要多掃描一棵索引樹.

12.什麼是索引下推?

如果存在某些被索引的列的判斷條件時,MySQL 將這一部分判斷條件傳遞給存儲引擎,然後由存儲引擎通過判斷索引是否符合 MySQL 伺服器傳遞的條件,「只有當索引符合條件時才會將數據檢索出來返回給 MySQL 伺服器」 。

13.什麼是覆蓋索引?

覆蓋索引(covering index)指一個查詢語句的執行只用從索引中就能夠取得,不必從數據表中讀取,可以減少回表的次數。比如:

select id from t where age = 1;

id 為主鍵索引,age 為普通索引,age 這個索引樹存儲的就是逐漸信息,可以直接返回

14.什麼是最左前綴原則?

最左前綴其實說的是,在 where 條件中出現的欄位,「如果只有組合索引中的部分列,則這部分列的觸發索引順序」,是按照定義索引的時候的順序從前到後觸發,最左面一個列觸發不了,之後的所有列索引都無法觸發。

比如「有一個 (a,b,c) 的組合索引」

where a = 1 and b = 1

此時 a,b 會命中該組合索引

where a = 1 and c = 1

此時 a 會命中該組合索引, c 不會

where b = 1 and c = 1

此時不會命中該組合索引

15.普通索引和唯一索引該怎麼選擇?

查詢當普通索引為條件時查詢到數據會一直掃描,直到掃完整張表當唯一索引為查詢條件時,查到該數據會直接返回,不會繼續掃表更新普通索引會直接將操作更新到 change buffer 中,然後結束唯一索引需要判斷數據是否衝突

所以「唯一索引更加適合查詢的場景,普通索引更適合插入的場景」

16.什麼是事務?其特性是什麼?

事務是指是程序中一系列操作必須全部成功完成,有一個失敗則全部失敗。

特性

「1.原子性(Atomicity)」:要麼全部執行成功,要麼全部不執行。「2.一致性(Consistency)」:事務前後數據的完整性必須保持一致。「3.隔離性(Isolation)」:隔離性是當多個事務同事觸發時,不能被其他事務的操作所干擾,多個並發事務之間要相互隔離。「4.持久性(Durability)」:事務完成之後的改變是永久的。

17.事務的隔離級別?

1.「讀提交」:即能夠「讀取到那些已經提交」的數據2.「讀未提交」:即能夠「讀取到沒有被提交」的數據3.「可重複讀」:可重複讀指的是在一個事務內,最開始讀到的數據和事務結束前的「任意時刻讀到的同一批數據都是一致的」4.「可串行化」:最高事務隔離級別,不管多少事務,都是「依次按序一個一個執行」

「髒讀」髒讀指的是「讀到了其他事務未提交的數據」,未提交意味著這些數據可能會回滾,也就是可能最終不會存到資料庫中,也就是不存在的數據。讀到了並一定最終存在的數據,這就是髒讀「不可重複讀」對比可重複讀,不可重複讀指的是在同一事務內,「不同的時刻讀到的同一批數據可能是不一樣的」。「幻讀」幻讀是針對數據插入(INSERT)操作來說的。假設事務A對某些行的內容作了更改,但是還未提交,此時事務B插入了與事務A更改前的記錄相同的記錄行,並且在事務A提交之前先提交了,而這時,在事務A中查詢,會發現「好像剛剛的更改對於某些數據未起作用」,但其實是事務B剛插入進來的這就叫幻讀

18.binlog 是做什麼的?

binlog 是歸檔日誌,屬於 Server 層的日誌,是一個二進位格式的文件,用於「記錄用戶對資料庫更新的SQL語句信息」。

主要作用

主從複製數據恢復

19.undolog 是做什麼的?

undolog 是 InnoDB 存儲引擎的日誌,用於保證數據的原子性,「保存了事務發生之前的數據的一個版本,也就是說記錄的是數據是修改之前的數據,可以用於回滾」,同時可以提供多版本並發控制下的讀(MVCC)。

主要作用

事務回滾實現多版本控制(MVCC)

20.relaylog 是做什麼的?

relaylog 是中繼日誌,「在主從同步的時候使用到」,它是一個中介臨時的日誌文件,用於存儲從master節點同步過來的binlog日誌內容。

master 主節點的 binlog 傳到 slave 從節點後,被寫入 relay log 里,從節點的 slave sql 線程從 relaylog 里讀取日誌然後應用到 slave 從節點本地。從伺服器 I/O 線程將主伺服器的二進位日誌讀取過來記錄到從伺服器本地文件,然後 SQL 線程會讀取 relay-log 日誌的內容並應用到從伺服器,從而「使從伺服器和主伺服器的數據保持一致」。

21.redolog 是做什麼的?

redolog 是 「InnoDB 存儲引擎所特有的一種日誌」,用於記錄事務操作的變化,記錄的是數據修改之後的值,不管事務是否提交都會記錄下來。

可以做「數據恢復並且提供 crash-safe 能力」

當有增刪改相關的操作時,會先記錄到 Innodb 中,並修改緩存頁中的數據,「等到 mysql 閒下來的時候才會真正的將 redolog 中的數據寫入到磁碟當中」。

22.redolog 是怎麼記錄日誌的?

InnoDB 的 redo log 是固定大小的,比如可以配置為一組4個文件,每個文件的大小是1GB,那麼總共就可以記錄4GB的操作。「從頭開始寫,寫到末尾就又回到開頭循環寫」。

所以,如果數據寫滿了但是還沒有來得及將數據真正的刷入磁碟當中,那麼就會發生「內存抖動」現象,從肉眼的角度來觀察會發現 mysql 會宕機一會兒,此時就是正在刷盤了。

23.redolog 和 binlog 的區別是什麼?

1.「redolog」 是 「Innodb」 獨有的日誌,而 「binlog」 是 「server」 層的,所有的存儲引擎都有使用到2.「redolog」 記錄了「具體的數值」,對某個頁做了什麼修改,「binlog」 記錄的「操作內容」3.「binlog」 大小達到上限或者 flush log 「會生成一個新的文件」,而 「redolog」 有固定大小「只能循環利用」4.「binlog 日誌沒有 crash-safe 的能力」,只能用于歸檔。而 redo log 有 crash-safe 能力。

24.說一說 mvcc 吧,有什麼作用?

MVCC:多版本並發控制,是現代資料庫(包括 MySQL、Oracle、PostgreSQL 等)引擎實現中常用的處理讀寫衝突的手段,目的在於「提高資料庫高並發場景下的吞吐性能」。

在 MVCC 協議下,每個讀操作會看到一個一致性的快照,「這個快照是基於整個庫的」,並且可以實現非阻塞的讀,用於「支持讀提交和可重複讀隔離級別的實現」。

MVCC 允許數據具有多個版本,這個版本可以是時間戳或者是全局遞增的事務 ID,在同一個時間點,不同的事務看到的數據是不同的,這個修改的數據是「記錄在 undolog 中」的。

25.一條 Sql 語句查詢一直慢會是什麼原因?

「1.沒有用到索引」比如函數導致的索引失效,或者本身就沒有加索引「2.表數據量太大」考慮分庫分表吧「3.優化器選錯了索引」「考慮使用」 force index 強制走索引

26.一條 Sql 語句查詢偶爾慢會是什麼原因?

「1. 資料庫在刷新髒頁」比如 「redolog 寫滿了」,「內存不夠用了」釋放內存如果是髒頁也需要刷,mysql 「正常空閒狀態刷髒頁」「2. 沒有拿到鎖」

27.Mysql 主從之間是怎麼同步數據的?

1.master 主庫將此次更新的事件類型「寫入到主庫的 binlog 文件」中2.master 「創建 log dump 線程通知 slave」 需要更新數據3.「slave」 向 master 節點發送請求,「將該 binlog 文件內容存到本地的 relaylog 中」4.「slave 開啟 sql 線程」讀取 relaylog 中的內容,「將其中的內容在本地重新執行一遍」,完成主從數據同步

「同步策略」:

1.「全同步複製」:主庫強制同步日誌到從庫,等全部從庫執行完才返回客戶端,性能差2.「半同步複製」:主庫收到至少一個從庫確認就認為操作成功,從庫寫入日誌成功返回ack確認

28.主從延遲要怎麼解決?

1.MySQL 5.6 版本以後,提供了一種「並行複製」的方式,通過將 SQL 線程轉換為多個 work 線程來進行重放2.「提高機器配置」(王道)3.在業務初期就選擇合適的分庫、分表策略,「避免單表單庫過大」帶來額外的複製壓力4.「避免長事務」5.「避免讓資料庫進行各種大量運算」6.對於一些對延遲很敏感的業務「直接使用主庫讀」

29.刪除表數據後表的大小卻沒有變動,這是為什麼?

在使用 delete 刪除數據時,其實對應的數據行並不是真正的刪除,是「邏輯刪除」,InnoDB 僅僅是將其「標記成可復用的狀態」,所以表空間不會變小

30.為什麼 VarChar 建議不要超過255?

當定義varchar長度小於等於255時,長度標識位需要一個字節(utf-8編碼)

當大於255時,長度標識位需要兩個字節,並且建立的「索引也會失效」

31.分布式式事務怎麼實現?

1.「本地消息表」2.「消息事務」3.「二階段提交」4.「三階段提交」5.「TCC」6.「最大努力通知」7.「Seata 框架」

七種分布式事務的解決方案,一次講給你聽

32.Mysql 中有哪些鎖?

以下並不全,主要理解下鎖的意義即可

基於鎖的屬性分類:共享鎖、排他鎖基於鎖的粒度分類:表鎖、行鎖、記錄鎖、間隙鎖、臨鍵鎖基於鎖的狀態分類:意向共享鎖、意向排它鎖、死鎖

33.為什麼不要使用長事務?

1.並發情況下,資料庫「連接池容易被撐爆」2.「容易造成大量的阻塞和鎖超時」長事務還占用鎖資源,也可能拖垮整個庫,3.執行時間長,容易造成「主從延遲」4.「回滾所需要的時間比較長」事務越長整個時間段內的事務也就越多5.「undolog 日誌越來越大」長事務意味著系統裡面會存在很老的事務視圖。由於這些事務隨時可能訪問資料庫裡面的任何數據,所以這個事務提交之前,資料庫裡面它可能用到的回滾記錄都必須保留,這就會導致大量占用存儲空間。

34.buffer pool 是做什麼的?

buffer pool 是一塊內存區域,為了「提高資料庫的性能」,當資料庫操作數據的時候,把硬碟上的數據加載到 buffer pool,不直接和硬碟打交道,操作的是 buffer pool 裡面的數據,資料庫的增刪改查都是在 buffer pool 上進行

buffer pool 裡面緩存的數據內容也是一個個數據頁

其中「有三大雙向鍊表」:

「free 鍊表」用於幫助我們找到空閒的緩存頁「flush 鍊表」用於找到髒緩存頁,也就是需要刷盤的緩存頁「lru 鍊表」用來淘汰不常被訪問的緩存頁,分為熱數據區和冷數據區,冷數據區主要存放那些不常被用到的數據

預讀機制:

Buffer Pool 有一項特技叫預讀,存儲引擎的接口在被 Server 層調用時,會在響應的同時進行預判,將下次可能用到的數據和索引加載到 Buffer Pool

35.說說你的 Sql 調優思路吧

1.「表結構優化」1.1拆分欄位1.2欄位類型的選擇1.3欄位類型大小的限制1.4合理的增加冗餘欄位1.5新建欄位一定要有默認值2.「索引方面」2.1索引欄位的選擇2.2利用好mysql支持的索引下推,覆蓋索引等功能2.3唯一索引和普通索引的選擇3.「查詢語句方面」3.1避免索引失效3.2合理的書寫where條件欄位順序3.3小表驅動大表3.4可以使用force index()防止優化器選錯索引4.「分庫分表」

Java架構師面試題及其他福利教程領取方式:

1、點讚+評論(勾選「同時轉發」)

2、關注小編。並私信回復關鍵字【Java】

(一定要私信哦~點擊我的頭像就能看到私信按鈕了)

關鍵字:

媽媽們看這邊!讓我家小朋友愛上「口腔保養」的【葡萄美齒液】

2021-10-26T02:47:41.766318+00:00

它天然萃取的口味完全不辣,連我這大人都很喜歡,終於不用來來回回跑牙醫啦

我兒子才升小一,每次叫他做什麼,都心不甘情不願擺張欠揍臉

孩子的爸覺得不用逼,但媽媽我火到不行^^很多習慣不都要從小養成嗎!

【尤其是刷牙!!】

隨隨便便揮兩下就說:「我刷好了(逃)」,用牙線一嚕明明都還有垢,就會叫他再刷乾淨

 

有次小朋友刷完,牙齦居然紅紅的,跟我說牙齒的肉肉好痛,嚇得帶他去看牙醫

牙醫說:「小朋友牙周環境較脆弱,加上很多『眉角』刷牙顧不到,所以刷完和三餐後,一定要搭配漱口水!」

市面大多漱口水太刺激

 醫生推薦【葡萄抑菌美齒液】,是最懂孩子的口腔防護專家 

✔天然溫和植萃+蜂膠去敏,不添加刺激酒精

✔清甜葡萄香全面去味,取代人工甜味劑、辛辣味

✔大廠專利技術,打造99%長效抑菌的環境

我也是長大花大錢跑牙科的過來人!這種小錢媽媽我毫不猶豫地花啦,只擔心小朋友更討厭刷牙…

 

沒想到他很喜歡那個味道餒,說就像不澀的葡萄汁一樣,甜甜涼涼的!

後來要刷牙他都超乖,就只為了早點漱美齒液…看來要讓小朋友培養好習慣,給他「甜頭」準沒錯XDD

 

【也不要以為蛀乳牙就沒關係餒!】牙床的影響是一輩子的~如果不想小朋友有敏感性牙齒,真的很推薦使用!

我自己還有給老公買他們家的「竹炭淨化美齒液」,老公也覺得不錯喔~

 

商品資訊

Huluhulu_口腔防護_葡萄香氛美齒液

https://www.cashin.tw/product/000000000012296