MySQL常見6個考題在實際工作中的運用

2020 年 5 月 26 日
筆記
求職面試

題目一

MyISAM和InnoDB的區別，什麼時候選擇MyISAM

參考回答

InnoDB是目前MySQL主流版本(5.6、5.7、8.0)默認的存儲引擎，支持事務、外鍵、行級鎖，對於並發條件下要求數據的一致性，適用於對數據準確性要求高的場景。

MyISAM只支持表級鎖、數據排列是按照插入順序，沒有做規則排序。適合應用以查詢和插入為主，只有很少量的更新和刪除操作，對事務的完整性和並發性要求不是很高的場景。

實際運用

看到很多人在選擇存儲引擎的時候會無腦的選擇InnoDB，這個選擇合理的一點是如果對數據準確性要求沒有那麼高，直接用NoSQL就好了。用MySQL就是為了可靠啊。

但是實際工作中，我設計的數據庫中通常都會有幾張MyISAM的數據表，通常用來存儲歷史記錄，與使用InnoDB存儲實時記錄信息的配合使用。

舉個例子：比如一條物流信息，在實時的表裡存着目前物流的狀態：比如配送中。這條物流在歷史上經過了：正在通知快遞公司取件、XXX已收攬等，這張記錄表基本只有插入和查詢，並且丟失一個中間狀態不影響當前結果，這就很合適用MyISAM。

題目二

簡述MySQL的MVCC多版本並發控制

參考回答

MVCC是對於事務隔離級別的讀已提交RC和可重複讀RR，基於樂觀鎖的實現。在LBCC(基於鎖的並發控制)RC、RR和串行化分別是通過加行鎖、間隙鎖和表鎖來基於悲觀鎖實現。而樂觀鎖的原理就是在特定的時間點(RC是每次讀時，RR是事務開始時)生成一個當前快照，讀數據讀取快照，只在提交時判斷是否有衝突，類似於git的branch和commit。

MVCC會在新開啟一個事務時，給事務里包含的每行記錄添加一個當前事務ID和回滾指針。並包含一個Read View，Read View里保存了當前活躍的事務列表，小於這些列表的最近的事務ID才是可見的。這樣保證了讀到的都是已提交的事務。

實際運用

MVCC不僅可以用於數據庫，也是很常見的一種並發控制手段。比如使用有限狀態自動機來控制的訂單狀態，在更新訂單狀態的時候先查詢當前狀態，比如當前狀態是訂單未提交，則更新時update XXX set status=’訂單已提交’ where status=’訂單未提交’，如果執行這條語句時，status已經發生了改變，這條語句就執行失敗了。這樣不通過數據庫自身事務的MVCC，在業務邏輯里也實現了MVCC思想的樂觀鎖設計。

題目三

分佈式鎖的實現方式

參考回答

主流有三種

1>基於數據庫

1.1>基於數據庫主鍵：插入一條數據，指定主鍵。如果有兩條插入會主鍵衝突，並發執行失敗

1.2>基於數據庫排他鎖：提交一個update事務，如果這個事務不提交，其他也對鎖定範圍內執行update就會阻塞，解決並發問題

2>基於緩存比如redis的setNX

3>基於zookeeper

實際運用

相信很多人選擇分佈式鎖都是選擇第二種，第三種雖然並發性差一下，如果本來就引入了zk，而沒有緩存，而分佈式鎖應用量又不那麼大，為了減少引入新組件帶來的風險和維護成本，也有可能選擇zk。很多人大概認為自己沒有用過基於數據庫的分佈式鎖，實際上在不使用MVCC的時代並不是這樣。

在使用spring進行業務開發的時候，常見的一種場景就是使用spring配置事務。默認級別是Repeatable Read可重複讀。在這裏面如果使用的是LBCC，一進入事務就加入一個排他鎖，比如insert、update、delete或者select XXX for update。然後做其他的，比如進行一個RPC調用。這時候一旦出現並發，只有一個能順利執行，其他都會被阻塞。實際上就相當於使用了分佈式鎖。

題目四

為什麼採用B+樹作為索引結構?

參考回答

如果採用Hash表，範圍查找需要全表掃描；如果採用二叉查找樹，由於無法保證平衡，可能退化為鏈表；如果採用平衡二叉樹，通過旋轉解決了平衡的問題，但是旋轉操作效率太低；如果採用紅黑樹，樹太高，IO次數多；如果採用普通B樹，節點要存數索引和數據，一個內存頁可存儲的數據還是少，另外範圍查找也需要多次IO；

而B+Tree有三個特性：

1>非葉子節點不存儲data，只存儲索引(冗餘)，可以放更多的索引

2>葉子節點包含所有索引字段

3>葉子節點用指針鏈接，提高範圍查詢的性能

實際運用

在分佈式場景下，我們的業務ID都是全局唯一的字符串。如果單純從業務上來考慮，用業務ID作為數據庫的主鍵就足夠了。可以DBA往往要求使用整型的自增主鍵作為數據庫主鍵，而這個主鍵對業務來說就是個浪費，沒有任何業務含義。

如果了解了索引的底層結構就不難理解

1>整型比字符串佔用更少的空間

2>同時大小比較也很快

3>之所以要自增是每次插入新的記錄，對於葉子節點來說：記錄會順序的添加到當前索引節點的後續位置，當一頁寫滿，會自動開闢一個新的頁。而如果使用非自增主鍵，就需要插入的時候移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要讀回來。分頁操作造成大量的碎片，必須通過優化操作重建表並優化填充頁面。

題目五

什麼叫做覆蓋索引？

參考回答

只需要在一棵輔助索引樹上就可以獲取SQL所需要的所有列數據，不需要回表。

實際運用

一些持久層框架比如mybatis的generator插件可以自動生成sql配置文件，這些配置文件往往效率很低。但是剛畢業的同學很多都不會去改這個文件，比如只需要個別列的時候會用java的lambda表達式等方式從邏輯上做處理。結果造成一些性能的問題。

我在根據一些條件進行範圍查找的時候，如果只需要返回ID或者個別列，會自己去改mybatis的generator自動生成的文件，原因是盡量使用覆蓋索引，較回錶速度快。

想驗證是否使用了覆蓋索引，可以用explain執行計劃，查看extra字段，如果只顯示Using index說明正確使用了覆蓋索引。如果extra為空或者除了using index還有filesort說明觸發了回表。

題目六

查詢在什麼時候不走索引

參考回答

主要三種情況