(面試)Mysql常見問題

語言: CN / TW / HK

1.一張自增表裏面總共有 7 條數據,刪除了最後 2 條數據,重啟 MySQL 數據庫,又插入了一條數據,此時 id 是幾?

表類型如果是 MyISAM ,那 id 就是 8。//因為 因為MylSAM表會把自增主鍵的最大ID記錄到數據文件裏面 ,重啟不會丟失。

表類型如果是 InnoDB,那 id 就是 6。//InnoDB 表只會把自增主鍵的最大 id 記錄在內存中,所以重啟之後會導致最大 id 丟失。

2.MySQL 問題排查都有哪些手段?

使用 show processlist 命令查看當前所有連接信息。

使用 explain 命令查詢 SQL 語句執行計劃。

開啟慢查詢日誌,查看慢查詢的 SQL。

3.MySQL 索引使用什麼數據結構?為什麼用 B+做索引?

大部分程序主要的功能都是對數據的處理,寫入、查詢、轉化、輸出。最形象的比喻就是樹和內容和目錄的關係,目錄就是索引,我們根據目錄能快速拿到想要內容的頁碼。

為什麼是B+樹,有這個幾個理由:

  • 如果是用AVL平衡二叉樹,樹高度太高,索引查詢需要訪問磁盤,每次訪問以節點為單位進行磁盤I/O ,需要儘量減少數據讀取的I/O操作,所以樹高度一定不能太高,存儲千萬級別的數據,實踐中 B+ 樹的高度也就 4或者5。

  • B+樹經常用來比較的是B樹,B+樹相比B樹有個很大的特點是B+樹所有關鍵字都出現在葉子結點的鏈表中(稠密索引),且鏈表中的關鍵字恰好是有序的,對於範圍查找,比如15~50,B樹需要中序遍歷二叉樹,但是B+樹直接在葉子節點順序訪問就可以了。

  • B樹在提高了IO性能的同時並沒有解決元素遍歷的我效率低下的問題,正是為了解決這個問題,B+樹應用而生。B+樹只需要去遍歷葉子節點就可以實現整棵樹的遍歷。而且在數據庫中基於範圍的查詢是非常頻繁的,而B樹不支持這樣的操作或者説效率太低。

4 .什麼是最左匹配原則?

最左前綴匹配原則:在MySQL建立聯合索引時會遵守最左前綴匹配原則,即最左優先,在檢索數據時從聯合索引的最左邊開始匹配。

打個比方,我們有張student 表,我們根據學院編號+班級建立了一個聯合索引 index_magor_class(magor,class), 這個索引由二個字段組成。

索引的底層是一顆B+樹,那麼聯合索引的底層也就是一顆B+樹,只不過聯合索引的B+樹節點中存儲的是逗號分隔的多個值。

我們查詢的where 條件如果只傳入了班級,是走不到聯合索引的,但是如果只傳了學院編號,是可能會走到聯合索引的。(為什麼説可能,MYSQL的執行計劃和查詢的實際執行過程並不完全吻合,比如你數據庫數據量很少,可能直接全量遍歷速度更快,就不走索引了)

 

5. 支持的數據庫引擎

MyISAM是MySQL的默認數據庫引擎(5.5版之前)。雖然性能極佳,而且提供了大量的特性,包括全文索引、壓縮、空間函數等,但MyISAM不支持事務和行級鎖,而且最大的缺陷就是崩潰後無法安全恢復。不過,5.5版本之後,MySQL引入了InnoDB(事務性數據庫引擎),MySQL 5.5版本後默認的存儲引擎為InnoDB。

大多數時候我們使用的都是 InnoDB 存儲引擎,但是在某些情況下使用 MyISAM 也是合適的比如讀密集的情況下。(如果你不介意 MyISAM 崩潰恢復問題的話)。

兩者的對比:

  1. 是否支持行級鎖 : MyISAM 只有表級鎖(table-level locking),而InnoDB 支持行級鎖(row-level locking)和表級鎖,默認為行級鎖。
  2. 是否支持事務和崩潰後的安全恢復: MyISAM 強調的是性能,每次查詢具有原子性,其執行速度比InnoDB類型更快,但是不提供事務支持。但是InnoDB 提供事務支持事務,外部鍵等高級數據庫功能。 具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。
  3. 是否支持外鍵: MyISAM不支持,而InnoDB支持。
  4. 是否支持MVCC :僅 InnoDB 支持。應對高併發事務, MVCC ( 多版本併發控制機制 )比單純的加鎖更高效;MVCC只在 READ COMMITTED  REPEATABLE READ 兩個隔離級別下工作;MVCC可以使用 樂觀(optimistic)鎖 和 悲觀(pessimistic)鎖來實現;各數據庫中MVCC實現並不統一。

6. MySQL的默認事務隔離級別是?

 默認是RR.

  1. 讀未提交(RU): 一個事務還沒提交時, 它做的變更就能被別的事務看到.
  2. 讀提交(RC): 一個事務提交之後, 它做的變更才會被其他事務看到.
  3. 可重複讀(RR): 一個事務執行過程中看到的數據, 總是跟這個事務在啟動時看到的數據是一致的. 當然在可重複讀隔離級別下, 未提交變更對其他事務也是不可見的.
  4. 串行化(S): 對於同一行記錄, 讀寫都會加鎖. 當出現讀寫鎖衝突的時候, 後訪問的事務必須等前一個事務執行完成才能繼續執行.

7.如何進行大表優化?

當MySQL單表記錄數過大時,數據庫的CRUD性能會明顯下降,一些常見的優化措施如下:

1. 限定數據的範圍

務必禁止不帶任何限制數據範圍條件的查詢語句。比如:我們當用户在查詢訂單歷史的時候,我們可以控制在一個月的範圍內;

2. 讀/寫分離

經典的數據庫拆分方案,主庫負責寫,從庫負責讀;

3. 垂直分區

根據數據庫裏面數據表的相關性進行拆分。 例如,用户表中既有用户的登錄信息又有用户的基本信息,可以將用户表拆分成兩個單獨的表,甚至放到單獨的庫做分庫。

簡單來説垂直拆分是指數據表列的拆分,把一張列比較多的表拆分為多張表。 如下圖所示,這樣來説大家應該就更容易理解了。 數據庫垂直分區

  • 垂直拆分的優點: 可以使得列數據變小,在查詢時減少讀取的Block數,減少I/O次數。此外,垂直分區可以簡化表的結構,易於維護。
  • 垂直拆分的缺點: 主鍵會出現宂餘,需要管理宂餘列,並會引起Join操作,可以通過在應用層進行Join來解決。此外,垂直分區會讓事務變得更加複雜;

4. 水平分區

保持數據表結構不變,通過某種策略存儲數據分片。這樣每一片數據分散到不同的表或者庫中,達到了分佈式的目的。 水平拆分可以支撐非常大的數據量。

水平拆分是指數據錶行的拆分,表的行數超過200萬行時,就會變慢,這時可以把一張的表的數據拆成多張表來存放。舉個例子:我們可以將用户信息表拆分成多個用户信息表,這樣就可以避免單一表數據量過大對性能造成影響。

水平拆分可以支持非常大的數據量。需要注意的一點是:分表僅僅是解決了單一表數據過大的問題,但由於表的數據還是在同一台機器上,其實對於提升MySQL併發能力沒有什麼意義,所以 水平拆分最好分庫 

水平拆分能夠 支持非常大的數據量存儲,應用端改造也少,但 分片事務難以解決 ,跨節點Join性能較差,邏輯複雜。《Java工程師修煉之道》的作者推薦 儘量不要對數據進行分片,因為拆分會帶來邏輯、部署、運維的各種複雜度 ,一般的數據表在優化得當的情況下支撐千萬以下的數據量是沒有太大問題的。如果實在要分片,儘量選擇客户端分片架構,這樣可以減少一次和中間件的網絡I/O。

下面補充一下數據庫分片的兩種常見方案:

  • 客户端代理: 分片邏輯在應用端,封裝在jar包中,通過修改或者封裝JDBC層來實現。 噹噹網的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實現。
  • 中間件代理: 在應用和數據中間加了一個代理層。分片邏輯統一維護在中間件服務中。 我們現在談的 Mycat 、360的Atlas、網易的DDB等等都是這種架構的實現。

8.談談鎖機制與InnoDB鎖算法?

MyISAM和InnoDB存儲引擎使用的鎖:

  • MyISAM採用表級鎖(table-level locking)。
  • InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖

表級鎖和行級鎖對比:

  • 表級鎖: MySQL中鎖定 粒度最大 的一種鎖,對當前操作的整張表加鎖,實現簡單,資源消耗也比較少,加鎖快,不會出現死鎖。其鎖定粒度最大,觸發鎖衝突的概率最高,併發度最低,MyISAM和 InnoDB引擎都支持表級鎖。
  • 行級鎖: MySQL中鎖定 粒度最小 的一種鎖,只針對當前操作的行進行加鎖。 行級鎖能大大減少數據庫操作的衝突。其加鎖粒度最小,併發度高,但加鎖的開銷也最大,加鎖慢,會出現死鎖。