MySQL 面試篇

MySQL相關面試題

在這里插入圖片描述

定位慢查詢

**面試官：**MySQL中，如何定位慢查詢?

我們當時做壓測的時候有的接口非常的慢，接口的響應時間超過了2秒以上，因為我們當時的系統部署了運維的監控系統Skywalking ，在展示的報表中可以看到是哪一個接口比較慢，并且可以分析這個接口哪部分比較慢，這里可以看到SQL的具體的執行時間，所以可以定位是哪個sql出了問題。

如果，項目中沒有這種運維的監控系統，其實在MySQL中也提供了慢日志查詢的功能，可以在MySQL的系統配置文件中開啟這個慢日志的功能，并且也可以設置SQL執行超過多少時間來記錄到一個日志文件中，我記得上一個項目配置的是2秒，只要SQL執行的時間超過了2秒就會記錄到日志文件中，我們就可以在日志文件找到執行比較慢的SQL了。

**面試官：**那這個SQL語句執行很慢, 如何分析呢？

如果一條sql執行很慢，我們通常會使用mysql自動的執行計劃explain來去查看這條sql的執行情況，比如在這里面可以通過key和key_len檢查是否命中了索引，如果本身已經添加了索引，也可以判斷索引是否有失效的情況。第二個，可以通過type字段查看sql是否有進一步的優化空間，是否存在全索引掃描或全盤掃描，第三個可以通過extra建議來判斷，是否出現了回表的情況，如果出現了，可以嘗試添加索引或修改返回字段來修復。

索引

**面試官：**了解過索引嗎？（什么是索引）

索引在項目中還是比較常見的，它是幫助MySQL高效獲取數據的數據結構，主要是用來提高數據檢索的效率，降低數據庫的IO成本，同時通過索引列對數據進行排序，降低數據排序的成本，也能降低了CPU的消耗。

**面試官：**索引的底層數據結構了解過嘛 ?

MySQL的默認的存儲引擎InnoDB采用的B+樹的數據結構來存儲索引，選擇B+樹的主要的原因是：第一階數更多，路徑更短。第二個磁盤讀寫代價B+樹更低，非葉子節點只存儲指針，葉子階段存儲數據。第三是B+樹便于掃庫和區間查詢，葉子節點是一個雙向鏈表。

**面試官：**B樹和B+樹的區別是什么呢？

第一：在B樹中，非葉子節點和葉子節點都會存放數據，而B+樹的所有的數據都會出現在葉子節點，在查詢的時候，B+樹查找效率更加穩定

第二：在進行范圍查詢的時候，B+樹效率更高，因為B+樹都在葉子節點存儲，并且葉子節點是一個雙向鏈表

**面試官：**索引創建原則有哪些？

這個情況有很多，不過都有一個大前提，就是表中的數據要超過10萬以上，我們才會創建索引，并且添加索引的字段是查詢比較頻繁的字段，一般也是像作為查詢條件，排序字段或分組的字段這些。

還有就是，我們通常創建索引的時候都是使用復合索引來創建，一條sql的返回值，盡量使用覆蓋索引，如果字段的區分度不高的話，我們也會把它放在組合索引后面的字段。

如果某一個字段的內容較長，我們會考慮使用前綴索引來使用，當然并不是所有的字段都要添加索引，這個索引的數量也要控制，因為添加索引也會導致新增改的速度變慢。

**面試官：**什么情況下索引會失效 ?

索引在使用的時候沒有遵循最左匹配法則，
模糊查詢，如果%號在前面也會導致索引失效。
添加索引的字段上進行了運算操作或者類型轉換也都會導致索引失效。
如果使用了復合索引，中間使用了范圍查詢，右邊的條件索引也會失效。

所以，通常情況下，想要判斷出這條sql是否有索引失效的情況，可以使用explain執行計劃來分析。

聚集索引&二級索引

**面試官：**什么是聚簇索引什么是非聚簇索引 ?

聚簇索引：數據與索引放到一塊，B+樹的葉子節點保存了整行數據，有且只有一個，一般情況下主鍵在作為聚簇索引的。

非聚簇索引：數據與索引分開存儲，B+樹的葉子節點保存對應的主鍵，可以有多個，一般我們自己定義的索引都是非聚簇索引。

回表查詢

**面試官：**知道什么是回表查詢嘛 ?

剛才介紹的聚簇索引和非聚簇索引是有關系的，回表的意思就是通過二級索引找到對應的主鍵值，然后再通過主鍵值找到聚集索引中所對應的整行數據，這個過程就是回表。

【備注：如果面試官直接問回表，則需要先介紹聚簇索引和非聚簇索引】

**面試官：**知道什么叫覆蓋索引嘛 ?

覆蓋索引是指select查詢語句使用了索引，在返回的列，必須在索引中全部能夠找到。

如果我們使用id查詢，它會直接走聚集索引查詢，一次索引掃描，直接返回數據，性能高。

如果按照二級索引查詢數據的時候，返回的列中沒有創建索引，有可能會觸發回表查詢，盡量避免使用select *，盡量在返回的列中都包含添加索引的字段。

**面試官：**MYSQL超大分頁怎么處理 ?

超大分頁一般都是在數據量比較大時，我們使用了limit分頁查詢，并且需要對數據進行排序，這個時候效率就很低，我們可以采用覆蓋索引 + 子查詢來解決。

先分頁查詢數據的id字段，確定了id之后，再用子查詢來過濾，只查詢這個id列表中的數據就可以了。

因為查詢id的時候，走的覆蓋索引，所以效率可以提升很多

SQL優化

**面試官：**sql的優化的經驗

表的設計優化，數據類型的選擇
索引優化，索引創建原則
sql語句優化，避免索引失效（不要使用select * …）
主從復制，讀寫分離，不讓數據的寫入，影響讀操作
分庫分表

**面試官：**創建表的時候，你們是如何優化的呢？

我們主要參考的阿里出的那個開發手冊《嵩山版》，就比如，在定義字段的時候需要結合字段的內容來選擇合適的類型，如果是數值的話，像tinyint、int 、bigint這些類型，要根據實際情況選擇。如果是字符串類型，也是結合存儲的內容來選擇char和varchar或者text類型。

**面試官：**那在使用索引的時候，是如何優化呢？

【參考索引創建原則進行描述】

**面試官：**你平時對sql語句做了哪些優化呢？

這個也有很多，比如SELECT語句務必指明字段名稱，不要直接使用select * ，還有就是要注意SQL語句避免造成索引失效的寫法；如果是聚合查詢，盡量用union all代替union ，union會多一次去重過濾，效率比較低；如果是表關聯的話，盡量使用innerjoin ，不要使用用left join right join，如必須使用一定要以小表為驅動

事務

**面試官：**事務的特性是什么？可以詳細說一下嗎？

ACID，分別指的是：原子性、一致性、隔離性、持久性；

原子性（Atomicity）：事務是不可分割的最小操作單元，要么全部成功，要么全部失敗。
一致性（Consistency）：事務完成時，必須使所有的數據都保持一致狀態。
隔離性（Isolation）：數據庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行。
持久性(Durability）：事務一旦提交或回滾，它對數據庫中的數據的改變就是永久的。

我舉個例子：A向B轉賬500，轉賬成功，A扣除500元，B增加500元，原子操作體現在要么都成功，要么都失敗。

在轉賬的過程中，數據要一致，A扣除了500，B必須增加500。

在轉賬的過程中，隔離性體現在A像B轉賬，不能受其他事務干擾。

在轉賬的過程中，持久性體現在事務提交后，要把數據持久化。

面試官：并發事務帶來哪些問題？

我們在項目開發中，多個事務并發進行是經常發生的，并發也是必然的，有可能導致一些問題

臟讀：一個事務讀到了另一個事務還沒有提交的數據。比如當一個事務正在訪問數據并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時另外一個事務也訪問了這個數據，因為這個數據是還沒有提交的數據，那么另外一個事務讀到的這個數據是“臟數據”，依據“臟數據”所做的操作可能是不正確的。
不可重復讀：一個事務先后讀到同一條記錄，但兩個讀取的數據不同。比如在一個事務內多次讀同一數據。在這個事務還沒有結束時，另一個事務也訪問該數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改導致第一個事務兩次讀取的數據可能不太一樣。這就發生了在一個事務內兩次讀到的數據是不一樣的情況，因此稱為不可重復讀。
幻讀：一個事務查詢數據時，沒有該數據，但是插入數據時，又發現這行數據已經存在，好像出現了“幻影”。它發生在一個事務（T1）讀取了幾行數據，接著另一個并發事務（T2）插入了一些數據時。在隨后的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。

面試官：怎么解決這些問題呢？MySQL的默認隔離級別是？

解決方案是對事務進行隔離。MySQL支持四種隔離級別從低到高分別有：

未提交讀（read uncommitted）它解決不了剛才提出的所有問題，一般項目中也不用這個。
讀已提交（read committed）它能解決臟讀的問題的，但是解決不了不可重復讀和幻讀。
可重復讀（repeatable read）它能解決臟讀和不可重復讀，但是解決不了幻讀，這個也是mysql默認的隔離級別。
串行化（serializable）它可以解決剛才提出來的所有問題，但是由于讓是事務串行執行的，性能比較低。

注意：事務隔離級別越高，數據越安全，但是性能越低。所以，我們一般使用的都是mysql默認的隔離級別：可重復讀

面試官：undo log和redo log的區別

redo log日志記錄的是數據頁的物理變化，服務宕機可用來同步數據。

undo log 不同，它主要記錄的是邏輯日志，當事務回滾時，通過逆操作恢復原來的數據，比如我們刪除一條數據的時候，就會在undo log日志文件中新增一條delete語句，如果發生回滾就執行逆操作。

redo log保證了事務的持久性，undo log保證了事務的原子性和一致性。

面試官：事務中的隔離性是如何保證的呢？(你解釋一下MVCC)

事務的隔離性是由鎖和mvcc實現的。

其中mvcc的意思是多版本并發控制。指維護一個數據的多個版本，使得讀寫操作沒有沖突，它的底層實現主要是分為了三個部分，第一個是隱藏字段，第二個是undo log日志，第三個是readView讀視圖。

隱藏字段是指：在mysql中給每個表都設置了隱藏字段，有一個是trx_id(事務id)，記錄每一次操作的事務id，是自增的；另一個字段是roll_pointer(回滾指針)，指向上一個版本的事務版本記錄地址。

undo log主要的作用是記錄回滾日志，存儲老版本數據，在內部會形成一個版本鏈，在多個事務并行操作某一行記錄，記錄不同事務修改數據的版本，通過roll_pointer指針形成一個鏈表。

readView解決的是一個事務查詢選擇版本的問題，在內部定義了一些匹配規則和當前的一些事務id判斷該訪問那個版本的數據，不同的隔離級別快照讀是不一樣的，最終的訪問的結果不一樣。如果是rc隔離級別，每一次執行快照讀時生成ReadView，如果是rr隔離級別僅在事務中第一次執行快照讀時生成ReadView，后續復用。

面試官：MySQL主從同步原理

在這里插入圖片描述