(面試)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中鎖定 粒度最小 的一種鎖,只針對當前操作的行進行加鎖。 行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小,併發度高,但加鎖的開銷也最大,加鎖慢,會出現死鎖。