java架構之路-（面試篇）Mysql面試大全

• 2019 年 10 月 9 日
• 筆記

　　說一下mysql比較宏觀的面試，具體咋寫sql的這裡就不過多舉例了。後面我還會給出一個關於mysql面試優化的試題，這裡主要說的索引和B+Tree結構，很少提到我們的集群配置優化方案。

1.索引是什麼？有什麼作用以及缺點

　　答：索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定資訊。也可以理解為索引就是一本書的目錄，創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。索引需要佔物理空間，除了數據表占數據空間之外，每一個索引還要佔一定的物理空間，如果要建立聚集索引那麼需要的空間就會更大。

2.索引的目的是什麼

　　答：為了高效的查找得到我們所需要的數據，減少分組和排序時間，提高我們的mysql的性能

3.索引對資料庫系統的負面影響

　　答：雖然索引對於資料庫的查詢提高了效率，但一定程度上增加了空間的佔用，同時寫入的速度降低了不少，和原有寫入數據相比較，多了一步去維護索引的操作。

4.建立索引的原則

　　答：選擇唯一性索引，為經常需要查詢、排序、分組和聯合操作的欄位建立索引，限制索引的數目，最左前綴匹配原則（非常重要的原則），盡量選擇區分度高的列作為索引，欄位儘力設置不為null，索引列上不計算。

5.主鍵、外鍵和唯一索引的區別

　　答：定義：

　　　　　　主鍵：唯一標識一條記錄，不能有重複的，不允許為空

　　　　　　外鍵：表的外鍵是另一表的主鍵, 外鍵可以有重複的, 可以是空值

　　　　　　索引：該欄位沒有重複值，但可以有一個空值 

　　　　作用：

　　　　　　主鍵：用來保證數據完整性

　　　　　　外鍵：用來和其他表建立聯繫用的

　　　　　　索引：是提高查詢排序的速度 

　　　　個數：

　　　　　　主鍵：主鍵只能有一個

　　　　　　外鍵：一個表可以有多個外鍵

　　　　　　索引：一個表可以有多個唯一索引

6.MySQL底層實現，MySQL有什麼引擎

　　答：mysql底層採用B+tree的存儲結構，也就是只有葉子節點攜帶真實數據，每個節點大小為16Kb，大致三層的B+tree就可以存2000W左右的數據，大大的減少了磁碟的IO。我們常見的存儲引擎有InnoDB和MyISAM。

7.InnoDB和MyISAM區別，InnoDB替代了MyISAM，那麼MyISAM是否一無是處。

　　答：InnoDB支援事務，支援行鎖，在磁碟上只存兩個文件，一個是索引文件，另一個是數據文件，在B+Tree的主鍵索引上，葉子節點攜帶全部數據，MyISAM不支援事務，不支援行鎖，磁碟上存了三個文件，一個是索引文件，另一個是數據文件，還有一個存放的對應關係文件，從查詢的角度來說，InnoDB沒有MyISAM的單條查詢速度高，MyISAM採用Hash存儲回行得到數據的查詢過程，單MyISAM對於範圍查詢不是很友好。因此我們可以看出InnoDB用的更廣一些，但同時MyISAM對於非範圍查詢的高效還是有很大用處的，而且MyISAM對於表內的總數查詢，維護了單獨的數據，也是很高效的。

8.什麼是事務，事務特性

　　答：事務是指作為單個邏輯工作單元執行的一系列操作，要麼完全地執行，要麼完全地不執行。在我們的mysql里也是如此，也就是我們的ACID原則。A原子性，把一系列的動作視為一個最小的操作（原子操作）C一致性，從一個狀態到另一個狀態是一致的，I隔離性：事務與事務之間是不可見相互隔離的，D持久性：一旦事務提交，則所做修改就會被永久保存到資料庫中。可以簡單說一下可重複讀的MVCC機制，面試官也懵圈。

9.如何設計一個高並發的系統（對於mysql來講）

　　答：這個後面會結合別的技術來說，只講mysql不太好說。大致就是我們首先應該考慮到的是讀寫分離操作（過幾天部落格里會詳細說這個），再就是我們常見的分庫分表操作，水平切分垂直切分。還可以加入快取redis操作。合理使用索引，explain進行sql優化。

10.鎖的優化策略

　　答：優化，也就是最小力度的鎖我們的數據，也就是行鎖，InnoDB的行鎖其實是加在索引欄位的，避免行鎖的升級為表鎖，再就是我們盡量避免間隙鎖，盡量避免我們的範圍修改，如果真的必須範圍修改，那麼我應該儘可能的縮小到最小的範圍。

 11.優化SQL的方法

　　答：設置一個主鍵索引，需主要主鍵索引一般沒有真正業務含義，使用int類型自動增長的，而且不能為null，非主鍵索引欄位優先考慮區分度高的業務情況和最左前綴原則，設置為null。如果真的數據量不大，不建議加索引，反而會影響效率的。選取最適用的欄位屬性，儘可能減少定義欄位寬度，盡量把欄位設置NOTNULL，使用連接(JOIN)來代替子查詢，適用聯合(UNION)來代替手動創建的臨時表。學會使用explain進行SQL分析，實在不行可以打開trace進行分析SQL情況，用完記得關閉。

12.談談三大範式，什麼時候使用反範式設計

　　答：第一範式（1NF）：確保每列保持原子性即列不可分

　　　　第二範式（2NF）：屬性完全依賴於主鍵，也就是說在一個資料庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張資料庫表中。

　　　　第三範式（3NF）：屬性和主鍵不能間接相關（減少數據冗餘，這樣就可以通過主外鍵進行表之間連接）

　　比如我們表比較多，需要關聯時，但我們的A表只需要關聯B表的一個欄位，而且每次都需要關聯查詢你，這時我們可以採用A表放置一個冗餘欄位來存B表的那個欄位。這個操作其實就是一個反範式的。

13.說幾個mysql中你常用的函數答：

　　答：sum、count 、avg、min、max

14.varchar(100)和varchar(200)的區別

　　答：佔用記憶體空間大小肯定是不一致的，但是佔用我們磁碟的大小是一致的，我們存儲字元串”abc”，完全是一樣的磁碟空間，但是對於varchar(100)來說，接收到的字元串長度太長了就會報錯的。後面的數字代表可存儲的位元組數。

15.varchar(20)和int(20)中的20含義一樣嗎

　　答：顯然不一致，int(M) M表示的不是數據的最大長度，只是數據寬度，並不影響存儲多少位長度的數據；varchar(M) M表示的是varchar類型數據在資料庫中存儲的最大長度，超過則不存； 

16.如何開啟慢日誌查詢？

　　答：有2種方式，一是修改mysql的配置文件，二是通過set global語句來實現。slow_query_log = ON，打開日誌，long_query_time = 2，設置時間，2秒就算是慢查詢，然後重啟mysql服務即可，進入mysql控制台，輸入SET GLOBAL slow_query_log = ‘ON’;SET GLOBAL long_query_time = X;不需要重啟服務就可以得到慢查詢日誌。

總結一下：

　　只要我們熟知，我們mysql的底層是B+tree的，B+tree是什麼樣子的，很多面試題都是圍繞這個東西來出題的，為什麼不為空，空不好找B+tree的位置啊等等。再就是我們要學會使用Explain來分析sql，有的時候即使有覆蓋索引，mysql也不一定去走的，他內部底層會判斷走這個索引的代價。可以自行嘗試。關於索引和結構的文章https://www.cnblogs.com/cxiaocai/p/11417362.html。然後就是我們的事務，也是很重要的一部分。總之不管他怎麼問我們，我們真的理解了其中的原理，就算是題再怎麼變化，也脫離不了底層原理的，我們要學會加法，而不是要學會1+1。

最進弄了一個公眾號，小菜技術，歡迎大家的加入