2019最新21個MySQL高頻面試題介紹

今天給大家分享 21 個 MySQL 面試題。

1、Mysql中有哪幾種鎖？

MyISAM 支持表鎖，InnoDB 支持表鎖和行鎖，默認為行鎖。

表級鎖:開銷小，加鎖快，不會出現死鎖。鎖定粒度大，發生鎖沖突的概率最高，并發量 最低。

行級鎖:開銷大，加鎖慢，會出現死鎖。鎖力度小，發生鎖沖突的概率小，并發度最高。

2、Mysql支持事務嗎？

在缺省模式下，MYSQL 是 autocommit 模式的，所有的數據庫更新操作都會即時提交，所 以在缺省情況下，mysql 是不支持事務的。

但是如果你的 MYSQL 表類型是使用 InnoDB Tables 或 BDB tables 的話，你的 MYSQL 就可以 使用事務處理,使用 SET AUTOCOMMIT=0 就可以使 MYSQL 允許在非 autocommit 模式，在非autocommit 模式下，你必須使用 COMMIT 來提交你的更改，或者用 ROLLBACK 來回滾你的 更改。

示例如下：

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1; 

UPDATE table2 SET summmary=@A WHERE type=1; 

COMMIT;

3、Mysql查詢是否區分大小寫？

不區分。

SELECT VERSION(), CURRENT_DATE;

SeLect version(), current_date;

seleCt vErSiOn(), current_DATE;

這幾個例子都是一樣的，Mysql 不區分大小寫。

4、列設置為 AUTO INCREMENT 時，如果在表中達到最大值，會發生什么情況?

答：它會停止遞增，任何進一步的插入都將產生錯誤，因為密鑰已被使用。

5、一張表，里面有 ID 自增主鍵，當 insert 了 17 條記錄之后，刪除了第 15,16,17 條記錄， 再把 Mysql 重啟，再 insert 一條記錄，這條記錄的 ID 是 18 還是 15 ?

如果表的類型是 MyISAM，那么是 18。因為 MyISAM 表會把自增主鍵的最大 ID 記錄到數據文件里，重啟 MySQL 自增主鍵的最大ID 也不會丟失。

如果表的類型是 InnoDB，那么是 15。InnoDB 表只是把自增主鍵的最大 ID 記錄到內存中，所以重啟數據庫或者是對表進行OPTIMIZE 操作，都會導致最大 ID 丟失。

6、數據庫三范式是什么?

第一范式(1NF):字段具有原子性,不可再分。(所有關系型數據庫系 統都滿足第一范式數據庫表中的字段都是單一屬性的，不可再分)

第二范式(2NF)是在第一范式(1NF)的基礎上建立起來的，即滿足 第二范式(2NF)必須先滿足第一范式(1NF)。要求數據庫表中的每 個實例或行必須可以被惟一地區分。通常需要為表加上一個列，以存儲 各個實例的惟一標識。這個惟一屬性列被稱為主關鍵字或主鍵。

滿足第三范式(3NF)必須先滿足第二范式(2NF)。簡而言之，第三 范式(3NF)要求一個數據庫表中不包含已在其它表中已包含的非主關 鍵字信息。>所以第三范式具有如下特征: >>1. 每一列只有一個 值 >>2. 每一行都能區分。>>3. 每一個表都不包含其他表已經包含 的非主關鍵字信息。

7、mysql 的復制原理以及流程？

答：Mysql 內建的復制功能是構建大型，高性能應用程序的基礎。將 Mysql 的數據 分布到多個系統上去，這種分布的機制，是通過將 Mysql 的某一臺主機的數據 復制到其它主機(slaves)上，并重新執行一遍來實現的。* 復制過程中一 個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將 更新寫入二進制日志文件，并維護文件的一個索引以跟蹤日志循環。這些日志 可以記錄發送到從服務器的更新。當一個從服務器連接主服務器時，它通知主 服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發生 的任何更新，然后封鎖并等待主服務器通知新的更新。

過程如下 ：

主服務器 把更新記錄到二進制日志文件中。

從服務器把主服務器的二進制日志拷貝 到自己的中繼日志(replay log)中。

從服務器重做中繼日志中的時間， 把更新應用到自己的數據庫上。

8、mysql 中 myISAM與 innodb 的區別?

事務支持 > MyISAM:強調的是性能，每次查詢具有原子性,其執行數 度比 InnoDB 類型更快，但是不提供事務支持。> InnoDB:提供事 務支持事務，外部鍵等高級數據庫功能。具有事務(commit)、回滾 (rollback)和崩潰修復能力(crash recovery capabilities)的事務安全 (transaction-safe (ACID compliant))型表。

InnoDB 支持行級鎖，而 MyISAM 支持表級鎖. >> 用戶在操作 myisam 表時，select，update，delete，insert 語句都會給表自動 加鎖，如果加鎖以后的表滿足 insert 并發的情況下，可以在表的尾部插 入新的數據。

InnoDB 支持 MVCC, 而 MyISAM 不支持。

InnoDB支持外鍵，而MyISAM不支持。

表主鍵，MyISAM：允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。InnoDB:如果沒有設定主鍵或者非空唯一索引，就會 自動生成一個 6 字節的主鍵(用戶不可見)，數據是主索引的一部分，附 加索引保存的是主索引的值。

InnoDB 不支持全文索引，而 MyISAM 支持。

可移植性、備份及恢復，MyISAM:數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進 行操作。InnoDB:免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十 G 的時候就相對痛苦了。

存儲結構，MyISAM:每個 MyISAM 在磁盤上存儲成三個文件。第一 個文件的名字以表的名字開始，擴展名指出文件類型。.frm 文件存儲表 定義。數據文件的擴展名為.MYD (MYData)。索引文件的擴展名 是.MYI (MYIndex)。InnoDB:所有的表都保存在同一個數據文件 中(也可能是多個文件，或者是獨立的表空間文件)，InnoDB 表的大 小只受限于操作系統文件的大小，一般為 2GB。

9、MySQL 中 InnoDB 支持的四種事務隔離級別名稱，以及逐級之間的區別?

Read Uncommitted(讀取未提交內容) >> 在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀(Dirty Read)。

Read Committed(讀取提交內容) >> 這是大多數數據庫系統的默認隔離級別(但不是 MySQL 默認的)。它滿足了隔離的簡單定義:一個事務只能看見已經提交事務所做的改變。這種隔離級別也支持所謂的不可重復讀(Nonrepeatable Read)，因為同一事務的其他實例在該實例處理其間可能會有新的 commit，所以同一 select 可能返回不同結果。

Repeatable Read(可重讀) >> 這是 MySQL 的默認事務隔離級別，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題:幻讀(Phantom Read)。簡單的說，幻讀指當用戶讀取某一范圍的數據行時，另一個事務又在該范圍內插入了新行，當用戶再讀取該范圍的數據行時，會發現有新的“幻影”行。InnoDB 和 Falcon 存儲引擎通過多版本并發控制(MVCC，Multiversion Concurrency Control 間隙鎖)機制解決了該問題。注:其實多版本只是解決不可重復讀問題，而加上間隙鎖(也就是它這里所謂的并發控制)才解決了幻讀問題。

Serializable(可串行化) >> 這是最高的隔離級別，它通過強制事務 排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個 讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖 競爭。

10、[SELECT *] 和[SELECT 全部字段]的 2 種寫法有何優缺點?

前者要解析數據字典，后者不需要

結果輸出順序，前者與建表列順序相同，后者按指定字段順序。

表字段改名，前者不需要修改，后者需要改

后者可以建立索引進行優化，前者無法優化

后者的可讀性比前者要高

11、簡述 Mybatis 的插件運行原理，以及如何編寫一個插件?

Mybatis 僅可以編寫針對 ParameterHandler、ResultSetHandler、StatementHandler、 Executor 這 4 種接口的插件，Mybatis 通過動態代理，為需要攔截的接口生成代理對象以實 現接口方法攔截功能，每當執行這 4 種接口對象的方法時，就會進入攔截方法，具體就是 InvocationHandler 的 invoke()方法，當然，只會攔截那些你指定需要攔截的方法。

實現 Mybatis 的 Interceptor 接口并復寫 intercept()方法，然后在給插件編寫注解，指定 要攔截哪一個接口的哪些方法即可，記住，別忘了在配置文件中配置你編寫的插件。

12、#{}和${}的區別是什么?

{}是預編譯處理，${}是字符串替換。

Mybatis 在處理#{}時，會將 sql 中的#{}替換為?號，調用 PreparedStatement 的 set 方法 來賦值。

Mybatis 在處理${}時，就是把${}替換成變量的值。

使用#{}可以有效的防止 SQL 注入，提高系統安全性。

13、什么是索引？請簡述常用的索引有哪些種類?

索引是對數據庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問數據庫表中的特定信息。如果想按特定職員的姓來查找他或她，則在表中搜索所有的行相比，索引有助于更快地獲取信息

通俗的講，索引就是數據的目錄，就像看書一樣，假如我想看第三章第四節的內容，如果有目錄，我直接翻目錄，找到第三章第四節的頁碼即可。如果沒有目錄，我就需要將從書的開頭開始，一頁一頁翻，直到翻到第三章第四節的內容。

MySQL索引的分類

我們根據對以列屬性生成的索引大致分為兩類：

單列索引：以該表的單個列，生成的索引樹，就稱為該表的單列索引

組合索引：以該表的多個列組合，一起生成的索引樹，就稱為該表的組合索引。

單列索引又有具體細的劃分：

主鍵索引：以該表主鍵生成的索引樹，就稱為該表的主鍵索引。

唯一索引：以該表唯一列生成的索引樹，就稱為該表的唯一索引。

普通索引：以該表的普通列（非主鍵，非唯一列）生成的索引樹，就稱為該表的普通索引。

14、索引是個什么樣的數據結構呢?

答：索引的數據結構和具體存儲引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等。而我們經常使用的InnoDB存儲引擎的默認索引實現為:B+樹索引。

15、Hash索引和B+樹所有有什么區別或者說優劣呢?

答：首先要知道Hash索引和B+樹索引的底層實現原理:

hash索引底層就是hash表，進行查找時，調用一次hash函數就可以獲取到相應的鍵值，之后進行回表查詢獲得實際數據。

B+樹底層實現是多路平衡查找樹，對于每一次的查詢都是從根節點出發，查找到葉子節點方可以獲得所查鍵值，然后根據查詢判斷是否需要回表查詢數據。

那么可以看出他們有以下的不同：

hash索引進行等值查詢更快(一般情況下)，但是卻無法進行范圍查詢。因為在hash索引中經過hash函數建立索引之后，索引的順序與原順序無法保持一致，不能支持范圍查詢。而B+樹的的所有節點皆遵循(左節點小于父節點，右節點大于父節點，多叉樹也類似)，天然支持范圍。

hash索引不支持使用索引進行排序，原理同上。

hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因為hash函數的不可預測，AAAA和AAAAB的索引沒有相關性。

hash索引任何時候都避免不了回表查詢數據，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時候可以只通過索引完成查詢。

hash索引雖然在等值查詢上較快，但是不穩定，性能不可預測。當某個鍵值存在大量重復的時候，發生hash碰撞，此時效率可能極差。而B+樹的查詢效率比較穩定，對于所有的查詢都是從根節點到葉子節點，且樹的高度較低。

因此，在大多數情況下，直接選擇B+樹索引可以獲得穩定且較好的查詢速度。而不需要使用hash索引。

16、上面提到了B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據,什么是聚簇索引?

答：在B+樹的索引中，葉子節點可能存儲了當前的key值，也可能存儲了當前的key值以及整行的數據，這就是聚簇索引和非聚簇索引.。在InnoDB中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引，如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引。當查詢使用聚簇索引時，在對應的葉子節點，可以獲取到整行數據,因此不用再次進行回表查詢。

17、非聚簇索引一定會回表查詢嗎?

答：不一定。這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進行回表查詢。

舉個簡單的例子，假設我們在員工表的年齡上建立了索引，那么當進行select age from employee where age < 20的查詢時，在索引的葉子節點上，已經包含了age信息，不會再次進行回表查詢。

18、對MySQL的鎖了解嗎?

答：當數據庫有并發事務的時候，可能會產生數據的不一致，這時候需要一些機制來保證訪問的次序，鎖機制就是這樣的一個機制。

就像酒店的房間，如果大家隨意進出，就會出現多人搶奪同一個房間的情況，而在房間上裝上鎖，申請到鑰匙的人才可以入住并且將房間鎖起來，其他人只有等他使用完畢才可以再次使用。

19、MySQL都有哪些鎖呢?像上面的例子進行鎖定豈不是有點阻礙并發效率了?

答：從鎖的類別上來講，有共享鎖和排他鎖。

共享鎖：又叫做讀鎖，當用戶要進行數據的讀取時，對數據加上共享鎖，共享鎖可以同時加上多個。

排他鎖：又叫做寫鎖，當用戶要進行數據的寫入時，對數據加上排他鎖，排他鎖只可以加一個，他和其他的排他鎖，共享鎖都相斥。

用上面的例子來說就是用戶的行為有兩種，一種是來看房，多個用戶一起看房是可以接受的。一種是真正的入住一晚，在這期間，無論是想入住的還是想看房的都不可以。

鎖的粒度取決于具體的存儲引擎，InnoDB實現了行級鎖，頁級鎖，表級鎖。他們的加鎖開銷從大大小，并發能力也是從大到小。

20、MySQL的binlog有有幾種錄入格式?分別有什么區別?

答：有三種格式,statement,row和mixed。

statement模式下，記錄單元為語句。即每一個sql造成的影響會記錄，由于sql的執行是有上下文的，因此在保存的時候需要保存相關的信息，同時還有一些使用了函數之類的語句無法被記錄復制。

row級別下，記錄單元為每一行的改動，基本是可以全部記下來但是由于很多操作，會導致大量行的改動(比如alter table)，因此這種模式的文件保存的信息太多，日志量太大。

mixed，一種折中的方案，普通操作使用statement記錄，當無法使用statement的時候使用row。

此外，新版的MySQL中對row級別也做了一些優化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。

21、一條sql執行過長的時間，你如何優化，從哪些方面？

1、查看sql是否涉及多表的聯表或者子查詢，如果有，看是否能進行業務拆分，相關字段冗余或者合并成臨時表（業務和算法的優化）。

2、涉及鏈表的查詢，是否能進行分表查詢，單表查詢之后的結果進行字段整合。

3、如果以上兩種都不能操作，非要鏈表查詢，那么考慮對相對應的查詢條件做索引。加快查詢速度。

4、針對數量大的表進行歷史表分離（如交易流水表）。

5、數據庫主從分離，讀寫分離，降低讀寫針對同一表同時的壓力，至于主從同步，mysql有自帶的binlog實現 主從同步。

6、explain分析sql語句，查看執行計劃，分析索引是否用上，分析掃描行數等等。

更多MySQL面試題大家可以查看下面的相關鏈接