MySQL面試題彙總

文章目錄

• 1、資料庫三大範式是什麼
• 2、Char和VarChar的差別
• 3、引擎相關
• 4、使用索引查詢一定能提高查詢的性能嗎？為什麼
• 5、事物的四大特性(ACID)介紹一下?
• 6、什麼是髒讀？幻讀？不可重複讀？
• 7、什麼是事務的隔離級别？MySQL的預設隔離級别是什麼？
• 8、隔離級别與鎖的關系
• 9、按照鎖的粒度分資料庫鎖有哪些？鎖機制與InnoDB鎖算法
• 10、什麼是死鎖？怎麼解決？
• 11、資料庫的樂觀鎖和悲觀鎖是什麼？怎麼實作的？

1、資料庫三大範式是什麼

第一範式：每個列都不可以再拆分。

第二範式：在第一範式的基礎上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。

第三範式：在第二範式的基礎上，非主鍵列隻依賴于主鍵，不依賴于其他非主鍵。

在設計資料庫結構的時候，要盡量遵守三範式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協資料庫的設計。

2、Char和VarChar的差別

VARCHAR用于存儲可變長字元串，它比定長類型更節省空間。

VARCHAR使用額外1或2個位元組存儲字元串長度。列長度小于255位元組時，使用1位元組表示，否則使用2位元組表示。

VARCHAR存儲的内容超出設定的長度時，内容會被截斷。

CHAR是定長的，根據定義的字元串長度配置設定足夠的空間。

CHAR會根據需要使用空格進行填充友善比較。

CHAR适合存儲很短的字元串，或者所有值都接近同一個長度。

CHAR存儲的内容超出設定的長度時，内容同樣會被截斷。

使用政策：

對于經常變更的資料來說，CHAR比VARCHAR更好，因為CHAR不容易産生碎片。

對于非常短的列，CHAR比VARCHAR在存儲空間上更有效率。

使用時要注意隻配置設定需要的空間，更長的列排序時會消耗更多記憶體。

盡量避免使用TEXT/BLOB類型，查詢時會使用臨時表，導緻嚴重的性能開銷。

3、引擎相關

MySQL引擎詳解

4、使用索引查詢一定能提高查詢的性能嗎？為什麼

通常，通過索引查詢資料比全表掃描要快。但是我們也必須注意到它的代價。

• 索引需要空間來存儲，也需要定期維護， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。 這意味着每條記錄的INSERT，DELETE，UPDATE将為此多付出4，5 次的磁盤I/O。 因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能，索引範圍查詢(INDEX RANGE SCAN)适用于兩種情況:
• 基于一個範圍的檢索，一般查詢傳回結果集小于表中記錄數的30%
• 基于非唯一性索引的檢索

5、事物的四大特性(ACID)介紹一下?

關系性資料庫需要遵循ACID規則，具體内容如下：

原子性： 事務是最小的執行機關，不允許分割。事務的原子性確定動作要麼全部完成，要麼完全不起作用；

一緻性： 執行事務前後，資料保持一緻，多個事務對同一個資料讀取的結果是相同的；

隔離性： 并發通路資料庫時，一個使用者的事務不被其他事務所幹擾，各并發事務之間資料庫是獨立的；

持久性： 一個事務被送出之後。它對資料庫中資料的改變是持久的，即使資料庫發生故障也不應該對其有任何影響。

6、什麼是髒讀？幻讀？不可重複讀？

髒讀(Drity Read)：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由于某些原因，前一個RollBack了操作，則後一個事務所讀取的資料就會是不正确的。

不可重複讀(Non-repeatable read):在一個事務的兩次查詢之中資料不一緻，這可能是兩次查詢過程中間插入了一個事務更新的原有的資料。

幻讀(Phantom Read):在一個事務的兩次查詢中資料筆數不一緻，例如有一個事務查詢了幾列(Row)資料，而另一個事務卻在此時插入了新的幾列資料，先前的事務在接下來的查詢中，就會發現有幾列資料是它先前所沒有的。

7、什麼是事務的隔離級别？MySQL的預設隔離級别是什麼？

SQL 标準定義了四個隔離級别：

READ-UNCOMMITTED(讀取未送出)： 最低的隔離級别，允許讀取尚未送出的資料變更，可能會導緻髒讀、幻讀或不可重複讀。

READ-COMMITTED(讀取已送出)： 允許讀取并發事務已經送出的資料，可以阻止髒讀，但是幻讀或不可重複讀仍有可能發生。

REPEATABLE-READ(可重複讀)： 對同一字段的多次讀取結果都是一緻的，除非資料是被本身事務自己所修改，可以阻止髒讀和不可重複讀，但幻讀仍有可能發生。

SERIALIZABLE(可串行化)： 最高的隔離級别，完全服從ACID的隔離級别。所有的事務依次逐個執行，這樣事務之間就完全不可能産生幹擾，也就是說，該級别可以防止髒讀、不可重複讀以及幻讀。

這裡需要注意的是：Mysql 預設采用的 REPEATABLE_READ隔離級别 Oracle 預設采用的 READ_COMMITTED隔離級别

事務隔離機制的實作基于鎖機制和并發排程。其中并發排程使用的是MVVC（多版本并發控制），通過儲存修改的舊版本資訊來支援并發一緻性讀和復原等特性。

因為隔離級别越低，事務請求的鎖越少，是以大部分資料庫系統的隔離級别都是READ-COMMITTED(讀取送出内容):，但是你要知道的是InnoDB 存儲引擎預設使用 **REPEATABLE-READ（可重讀）**并不會有任何性能損失。

InnoDB 存儲引擎在 分布式事務 的情況下一般會用到**SERIALIZABLE(可串行化)**隔離級别。

8、隔離級别與鎖的關系

在Read Uncommitted級别下，讀取資料不需要加共享鎖，這樣就不會跟被修改的資料上的排他鎖沖突

在Read Committed級别下，讀操作需要加共享鎖，但是在語句執行完以後釋放共享鎖；

在Repeatable Read級别下，讀操作需要加共享鎖，但是在事務送出之前并不釋放共享鎖，也就是必須等待事務執行完畢以後才釋放共享鎖。

SERIALIZABLE 是限制性最強的隔離級别，因為該級别鎖定整個範圍的鍵，并一直持有鎖，直到事務完成。

9、按照鎖的粒度分資料庫鎖有哪些？鎖機制與InnoDB鎖算法

在關系型資料庫中，可以按照鎖的粒度把資料庫鎖分為行級鎖(INNODB引擎)、表級鎖(MYISAM引擎)和頁級鎖(BDB引擎 )。

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

MyISAM采用表級鎖(table-level locking)。

InnoDB支援行級鎖(row-level locking)和表級鎖，預設為行級鎖

行級鎖，表級鎖和頁級鎖對比

行級鎖 行級鎖是Mysql中鎖定粒度最細的一種鎖，表示隻針對目前操作的行進行加鎖。行級鎖能大大減少資料庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖 和 排他鎖。

特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。

表級鎖 表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對目前操作的整張表加鎖，它實作簡單，資源消耗較少，被大部分MySQL引擎支援。最常使用的MYISAM與INNODB都支援表級鎖定。表級鎖定分為表共享讀鎖（共享鎖）與表獨占寫鎖（排他鎖）。

特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖沖突的機率最高，并發度最低。

頁級鎖 頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。是以取了折衷的頁級，一次鎖定相鄰的一組記錄。

特點：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

10、什麼是死鎖？怎麼解決？

死鎖是指兩個或多個事務在同一資源上互相占用，并請求鎖定對方的資源，進而導緻惡性循環的現象。

常見的解決死鎖的方法

1、如果不同程式會并發存取多個表，盡量約定以相同的順序通路表，可以大大降低死鎖機會。

2、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖産生機率；

3、對于非常容易産生死鎖的業務部分，可以嘗試使用更新鎖定顆粒度，通過表級鎖定來減少死鎖産生的機率；

如果業務處理不好可以用分布式事務鎖或者使用樂觀鎖

11、資料庫的樂觀鎖和悲觀鎖是什麼？怎麼實作的？

資料庫管理系統（DBMS）中的并發控制的任務是確定在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀并發控制（樂觀鎖）和悲觀并發控制（悲觀鎖）是并發控制主要采用的技術手段。

悲觀鎖：假定會發生并發沖突，屏蔽一切可能違反資料完整性的操作。在查詢完資料的時候就把事務鎖起來，直到送出事務。實作方式：使用資料庫中的鎖機制

樂觀鎖：假設不會發生并發沖突，隻在送出操作時檢查是否違反資料完整性。在修改資料的時候把事務鎖起來，通過version的方式來進行鎖定。實作方式：樂一般會使用版本号機制或CAS算法實作。

兩種鎖的使用場景

從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好于另一種，像樂觀鎖适用于寫比較少的情況下（多讀場景），即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。

但如果是多寫的情況，一般會經常産生沖突，這就會導緻上層應用會不斷的進行retry，這樣反倒是降低了性能，是以一般多寫的場景下用悲觀鎖就比較合适。

參考文獻：https://blog.csdn.net/ThinkWon/article/details/104778621