MySQL八股整理

1. 如何定位慢查詢？

慢查詢一般發生在聯表查詢或者表中數據量較大時，當響應時間較長或者壓測時間超過2s時，就認為是慢查詢。定位慢查詢的話一般有兩種方法，一種是使用專門的分析工具去定位。另一種也是我們項目中之前使用過的方法：MySQL自帶的慢查詢日志。想要想要使用自帶的慢查詢日志功能，需要在MySQL的配置文件中將慢查詢日志的開關打開，即對應的字段的值置為1.其次需要給定一個上限值，當SQL語句的執行時常超過該值時，就會在日志中記錄信息，記錄的信息包括該條語句的執行時長以及這條語句具體的SQL代碼。這樣就可以準確定位到這條SQL語句從而進行優化。

?2. 如果一個SQL語句執行很慢，如何分析？

如果一條SQL語句執行很慢的話，一般就通過在查詢語句前添加Explain關鍵字得到這條SQL語句對應的性能分析表格。其中該表格中有四個關鍵字段需要關注。首先是key和key_len字段，可以檢查這條SQL語句是否命中了索引。如果沒有命中，試著分析原因，可以通過添加索引或者其他手段使得其根據索引去查詢；其次是type字段可以反應這條SQL還有無優化空間，判斷是否出現了全表掃描和全索引掃描；還有一個額外建議的字段反映了這條語句是否出現了回表現象。如果出現了，就通過添加索引或者修改查詢語句，避免回表。

3. 什么是索引？

?索引是一種數據結構，可以幫助SQL語句查詢時提高查詢效率。索引類似于我們一本書籍的目錄，通過目錄可以快速找到對應的數據。同樣，通過索引也可以實現數據的高效查詢，尤其是在數據量較大時，往往效果更加明顯。

4. 索引的底層數據結構？

InnoDB引擎下的MySQL索引的數據結構是通過B+樹實現的。B+樹相比較于二叉樹、二叉搜索樹、紅黑樹等，更加矮胖，就是每一層的節點數更多，但是層數較少。在數據不平衡的情況下二叉樹可能退化成鏈表，這樣在查詢時，查詢效率就比較低。并且二叉樹每一個節點只有兩個子節點，這樣在查詢時就會導致效率更低。而B樹實現的就是每一個節點可以不僅僅只有兩個節點，每一個B樹的節點既包含數據也包含下一個節點的指針，如果想要查詢的數據在最底層時，B樹的效率就比較低，因為每次查詢都需要將所有節點的數據讀取一次，降低了讀取效率。而B+樹是B樹的變種，B+樹相比于B樹，只有在葉子節點中才存儲數據，在非葉子節點中只存儲了子節點的指針。并且在葉子節點中，數據之間都擁有指針，類似一個雙向鏈表，所以比較適合范圍查詢。當查詢到一個數據時，就可以通過指針查找到其他數據，不需要返回根節點重新查詢，這樣可以大大提高查詢的效率和CPU的消耗。

5. 什么是聚集索引，什么是非聚集索引？什么是回表查詢？

聚集索引就是指數據與索引放在一塊，在B+樹的葉子節點中，存儲了整行數據（B+樹是InnoDB引擎下索引的數據結構），這樣的索引有且只能有一個一般為主鍵，因為全部數據已經與該索引存儲在一起了。非聚集索引又叫做二級索引，與聚集索引相反即索引與數據不是存儲在一起，而是葉子節點中存儲了該行數據對應的主鍵值，如果想要這個索引對應的其他數據，那么就需要回表查詢。這樣的索引可以有多個。

回表查詢就是指，通過二級索引找到主鍵值，再返回到聚集索引中去查詢完整數據，這個過程就是回表查詢。

6. 什么是覆蓋索引？

覆蓋索引就是指所查詢的字段中，可以通過索引全部找到，這樣的查詢就叫做覆蓋索引，并且通過覆蓋索引可以避免回表查詢，從而提高查詢的效率。所以在我們編寫SQL語句時，也要盡量做到覆蓋索引，比如說使用根據主鍵值進行查詢，避免使用select *。

7. MySQL超大分頁怎么處理？

超大分頁指的是數據量較大時，使用limit進行分頁處理查詢，這時需要對數據進行排序，所以效率一般都比較低。此時通過覆蓋索引+子查詢來解決。比如說，先通過查詢滿足這一頁數據的id值，再去查詢這部分id值對應的數據即可完成查詢。在這次查詢中，查詢id值是覆蓋索引，所以效率較高，再去進行子查詢時，查詢的數據量就大大減少，這樣可以提高查詢的效率。

8. 索引創建原則有哪些？

(1) 針對數據量較大且查詢比較頻繁的表建立索引；

(2) 針對于常作為查詢條件(Where)、排序(Order By)、分組(group by)操作的字段建立索引；

(3) 盡量選擇區分度較高的列作為索引，盡量建立唯一索引，區分度越高，使用索引的概率就越大；

(4)?如果是字符串數據，當字符串較長時，可以根據字段的特點，建立前綴索引；

(5)?盡量使用聯合索引，減少單列索引，查詢時聯合索引覆蓋索引的概率較大；

(6) 要控制索引，并不是越多越好，因為索引越多意味著維護索引結構所需的代價就越大，會影響增刪改的效率；

(7) 如果某一列不能出現NULL值，在創建表示給該列添加Not Null約束，優化器會通過是否包含Null值，優化哪個索引可以更有利于查詢。

9. 什么情況下索引會失效？

(1) 違反最左前綴原則；

(2) 范圍查詢右邊的列，索引會失效；

(3) 不要在索引列上進行運算操作，否則索引會失效；

(4)?字符串不加單引號會導致索引失效(實際是發生了類型轉換)；

(5)?以%開頭的Like模糊查詢，會導致索引失效。

10. 談一談你對SQL的優化經驗

首先是表的設計優化，在我們項目過程中，所參考的是阿里的開發手冊，比如說用到的優化策略有：在創建字段時，盡量給出滿足數據的數值類型，比如說整型：tinyint、int、bigint，還有字符串的選擇：char、varchar，char的定長效率更高，varchar變長，效率較低。

SQL語句的優化：盡量避免使用select *；盡量避免造成索引失效的寫法；避免對where字段后的條件進行表達式操作；在表連接時，盡量使用內部連接，避免使用左右連接。

主從復制，讀寫分離：如果數據庫的使用場景中，使用讀數據的操作較多時，為了避免寫的操作所造成的影響，可以采用主從分離的結構進行數據庫的設計，這樣可以避免由于寫操作對讀操作造成的性能影響。

索引優化(參考索引創建原則)；

分庫分表。

11. 事務的特性是什么？

首先解釋一下什么是事務。事務是一個不可分割的工作單位，事務會將所有操作作為一個整體，一起向系統提交或者撤銷。即要么同時成功，要么同時失敗。最經典的一個例子就是指，轉賬業務。A向B轉賬500元，當A的余額扣減500時，B的余額就必須增加500，不能增加300也不能保持不變。這也體現了事務的特性，接下來就說一下事務的特性：

原子性：原子性就是指事務是一個不可分割的工作單位，事務中的所有操作要么全部成功要么全部失敗。

一致性：指事務完成時，所有數據都必須保持一致的狀態，比如說A向B轉賬500元，當事務結束時，要么就是A減少500并且B增加500，要么就是都不變。

隔離性：隔離性是指一個事務運行時不受外部其他事務的影響。

持久性：事務一旦提交或者回滾，那么他對數據庫中數據的改變就是持久性的。

12. 并發事務帶來哪些問題？怎么解決這些問題？MySQL的默認隔離級別是什么？

并發事務可能帶來的問題包括：臟讀、不可重復讀、幻讀。其中臟讀是指，某一個事務A在執行時，另外又有一個事務B并發執行，當B事務將數據庫中的某條數據修改后，并未提交事務，此時，事務A讀取該數據，就可能讀到B事務修改后的數據，可是此時B事務還沒有提交。不可重復讀是指，當A事務剛開始執行時，讀取了數據，B事務對數據進行修改后，提交了事務，那么此時A再次查詢該數據時，拿到的結果與上次查詢得到的結果不一致了。幻讀指的是當事務A查詢某條數據時提示數據不存在，就在這時B事務向數據庫中寫入了這條數據，并且提交了事務(默認此時已經解決了不可重復讀問題)，此時A向數據庫寫入數據時，就會寫入失敗，因為此時數據庫中已經有這條數據了，就好像出現了幻覺一般。

通過對事物進行隔離，解決上述問題。其中隔離級別包括四級：讀未提交、讀已提交、可重復讀、串行化，越往后隔離界別越高，但是性能就越低。

可重復讀是MySQL的默認隔離級別。

13. undo log 和redo log分別是什么？

?先解釋一下兩個概念，一個是緩沖池一個是數據頁。其中緩沖池是主內存中的一個區域，里邊可以緩存磁盤上經常操作的數據，這樣以后每次增刪改對應的數據時，就可以直接操作緩沖池中的數據，減少了磁盤IO的次數，提高了效率，以一定頻率刷新磁盤即可。而數據頁是innoDB存儲引擎磁盤管理的最小單元，每個頁的大小默認為16KB，頁中存儲的是行數據。在一個事務中，數據先在內存結構中寫好以后，準備寫往磁盤中，但是就在這時服務宕機了，就導致數據丟失，這違背了事務的特性持久性。所以引出了redo log和undo log。

redo log記錄的是事務提交時數據頁的物理修改，是用來實現事務的持久性的，該日志文件有兩部分組成，其中一部分是重做日志緩存，是放在內存結構中，負責記錄數據頁的物理修改。另一部分叫做日志文件，是放在磁盤中，重做日志緩存會每隔一段時間就去將數據寫入日志文件中。這樣當刷新臟頁到磁盤時，即使發生錯誤，也可以通過日志緩存文件來恢復數據。

undo log又叫做回滾日志，用于記錄數據被修改前的信息，主要作用有兩個，一個是提供回滾，另一個是MVCC。比如說，當刪除一條數據時，在該日志文件中就會記錄一條插入語句，在更新數據時，就會寫入一條更新前的數據的寫入語句。這樣，當發生回滾時，就可以通過undo log來進行數據的回滾。undo log保證了事務的一致性和原子性。

14. 解釋一下MVCC？

首先說一下事務中的隔離性是如何保證的：鎖：排他鎖，如一個事務獲取了一個數據行的排他鎖，那么其他事物就不能再獲取該行的其他鎖了；MVCC：多版本并發控制。

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

隱藏字段是指，在mysql中給每個字段都設置了隱藏字段，一個是記錄事務id，記錄每一條操作的事務id，是自增的。另一個是回滾指針，應該指向的是上個版本事務版本的記錄地址。

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

readview解決的是一個事務查詢選擇版本的問題，在內部定義了一些選擇應該訪問哪個版本的規則，不同隔離級別的快照讀是不一樣的。如果是RC隔離級別，每一次執行快照讀時都會生成readview，而rr隔離級別，只會在事務第一次執行快照讀時生成readview，后續都會復用這個readview。

15. MySQL主從同步原理？

首先解釋一下主從同步是什么意思。如果在一個業務中，讀取數據庫的操作較多時，就建議部署MySQL的主從機制，主數據庫負責寫數據，而從數據庫負責讀數據，每次寫入數據后，主數據庫就需要將數據同步到從數據庫。這樣可以避免由于寫對讀造成性能影響。

MySQL的主從同步的核心是二進制日志文件。二進制日志中會記錄所有的DDL和DML預計，但不包括查詢語句。主數據庫在事務提交時，會將所有的數據變更記錄寫入到日志文件BinLog中。從數據庫會有一個專門的IO線程從主數據庫中的二進制文件讀取內容，寫入到從庫中的中繼日志文件中。從庫重做中繼日志中的事件，就可以實現與主庫數據的同步。