2019最新21個(gè)MySQL高頻面試題介紹

今天給大家分享 21 個(gè) MySQL 面試題。

1、Mysql中有哪幾種鎖？

MyISAM 支持表鎖，InnoDB 支持表鎖和行鎖，默認(rèn)為行鎖。

表級(jí)鎖:開銷小，加鎖快，不會(huì)出現(xiàn)死鎖。鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)量 最低。

行級(jí)鎖:開銷大，加鎖慢，會(huì)出現(xiàn)死鎖。鎖力度小，發(fā)生鎖沖突的概率小，并發(fā)度最高。

2、Mysql支持事務(wù)嗎？

在缺省模式下，MYSQL 是 autocommit 模式的，所有的數(shù)據(jù)庫(kù)更新操作都會(huì)即時(shí)提交，所 以在缺省情況下，mysql 是不支持事務(wù)的。

但是如果你的 MYSQL 表類型是使用 InnoDB Tables 或 BDB tables 的話，你的 MYSQL 就可以 使用事務(wù)處理,使用 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查詢是否區(qū)分大小寫？

不區(qū)分。

SELECT VERSION(), CURRENT_DATE;

SeLect version(), current_date;

seleCt vErSiOn(), current_DATE;

這幾個(gè)例子都是一樣的，Mysql 不區(qū)分大小寫。

4、列設(shè)置為 AUTO INCREMENT 時(shí)，如果在表中達(dá)到最大值，會(huì)發(fā)生什么情況?

答：它會(huì)停止遞增，任何進(jìn)一步的插入都將產(chǎn)生錯(cuò)誤，因?yàn)槊荑€已被使用。

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

如果表的類型是 MyISAM，那么是 18。因?yàn)?MyISAM 表會(huì)把自增主鍵的最大 ID 記錄到數(shù)據(jù)文件里，重啟 MySQL 自增主鍵的最大ID 也不會(huì)丟失。

如果表的類型是 InnoDB，那么是 15。InnoDB 表只是把自增主鍵的最大 ID 記錄到內(nèi)存中，所以重啟數(shù)據(jù)庫(kù)或者是對(duì)表進(jìn)行OPTIMIZE 操作，都會(huì)導(dǎo)致最大 ID 丟失。

6、數(shù)據(jù)庫(kù)三范式是什么?

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

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

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

7、mysql 的復(fù)制原理以及流程？

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

過程如下 ：

主服務(wù)器 把更新記錄到二進(jìn)制日志文件中。

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

從服務(wù)器重做中繼日志中的時(shí)間， 把更新應(yīng)用到自己的數(shù)據(jù)庫(kù)上。

8、mysql 中 myISAM與 innodb 的區(qū)別?

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

InnoDB 支持行級(jí)鎖，而 MyISAM 支持表級(jí)鎖. >> 用戶在操作 myisam 表時(shí)，select，update，delete，insert 語(yǔ)句都會(huì)給表自動(dòng) 加鎖，如果加鎖以后的表滿足 insert 并發(fā)的情況下，可以在表的尾部插 入新的數(shù)據(jù)。

InnoDB 支持 MVCC, 而 MyISAM 不支持。

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

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

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

可移植性、備份及恢復(fù)，MyISAM:數(shù)據(jù)是以文件的形式存儲(chǔ)，所以在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中會(huì)很方便。在備份和恢復(fù)時(shí)可單獨(dú)針對(duì)某個(gè)表進(jìn) 行操作。InnoDB:免費(fèi)的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達(dá)到幾十 G 的時(shí)候就相對(duì)痛苦了。

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

9、MySQL 中 InnoDB 支持的四種事務(wù)隔離級(jí)別名稱，以及逐級(jí)之間的區(qū)別?

Read Uncommitted(讀取未提交內(nèi)容) >> 在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少。讀取未提交的數(shù)據(jù)，也被稱之為臟讀(Dirty Read)。

Read Committed(讀取提交內(nèi)容) >> 這是大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別(但不是 MySQL 默認(rèn)的)。它滿足了隔離的簡(jiǎn)單定義:一個(gè)事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變。這種隔離級(jí)別也支持所謂的不可重復(fù)讀(Nonrepeatable Read)，因?yàn)橥皇聞?wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的 commit，所以同一 select 可能返回不同結(jié)果。

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

Serializable(可串行化) >> 這是最高的隔離級(jí)別，它通過強(qiáng)制事務(wù) 排序，使之不可能相互沖突，從而解決幻讀問題。簡(jiǎn)言之，它是在每個(gè) 讀的數(shù)據(jù)行上加上共享鎖。在這個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖 競(jìng)爭(zhēng)。

10、[SELECT *] 和[SELECT 全部字段]的 2 種寫法有何優(yōu)缺點(diǎn)?

前者要解析數(shù)據(jù)字典，后者不需要

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

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

后者可以建立索引進(jìn)行優(yōu)化，前者無(wú)法優(yōu)化

后者的可讀性比前者要高

11、簡(jiǎn)述 Mybatis 的插件運(yùn)行原理，以及如何編寫一個(gè)插件?

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

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

12、#{}和${}的區(qū)別是什么?

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

Mybatis 在處理#{}時(shí)，會(huì)將 sql 中的#{}替換為?號(hào)，調(diào)用 PreparedStatement 的 set 方法 來賦值。

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

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

13、什么是索引？請(qǐng)簡(jiǎn)述常用的索引有哪些種類?

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

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

MySQL索引的分類

我們根據(jù)對(duì)以列屬性生成的索引大致分為兩類：

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

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

單列索引又有具體細(xì)的劃分：

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

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

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

14、索引是個(gè)什么樣的數(shù)據(jù)結(jié)構(gòu)呢?

答：索引的數(shù)據(jù)結(jié)構(gòu)和具體存儲(chǔ)引擎的實(shí)現(xiàn)有關(guān), 在MySQL中使用較多的索引有Hash索引,B+樹索引等。而我們經(jīng)常使用的InnoDB存儲(chǔ)引擎的默認(rèn)索引實(shí)現(xiàn)為:B+樹索引。

15、Hash索引和B+樹所有有什么區(qū)別或者說優(yōu)劣呢?

答：首先要知道Hash索引和B+樹索引的底層實(shí)現(xiàn)原理:

hash索引底層就是hash表，進(jìn)行查找時(shí)，調(diào)用一次hash函數(shù)就可以獲取到相應(yīng)的鍵值，之后進(jìn)行回表查詢獲得實(shí)際數(shù)據(jù)。

B+樹底層實(shí)現(xiàn)是多路平衡查找樹，對(duì)于每一次的查詢都是從根節(jié)點(diǎn)出發(fā)，查找到葉子節(jié)點(diǎn)方可以獲得所查鍵值，然后根據(jù)查詢判斷是否需要回表查詢數(shù)據(jù)。

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

hash索引進(jìn)行等值查詢更快(一般情況下)，但是卻無(wú)法進(jìn)行范圍查詢。因?yàn)樵趆ash索引中經(jīng)過hash函數(shù)建立索引之后，索引的順序與原順序無(wú)法保持一致，不能支持范圍查詢。而B+樹的的所有節(jié)點(diǎn)皆遵循(左節(jié)點(diǎn)小于父節(jié)點(diǎn)，右節(jié)點(diǎn)大于父節(jié)點(diǎn)，多叉樹也類似)，天然支持范圍。

hash索引不支持使用索引進(jìn)行排序，原理同上。

hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因?yàn)閔ash函數(shù)的不可預(yù)測(cè)，AAAA和AAAAB的索引沒有相關(guān)性。

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

hash索引雖然在等值查詢上較快，但是不穩(wěn)定，性能不可預(yù)測(cè)。當(dāng)某個(gè)鍵值存在大量重復(fù)的時(shí)候，發(fā)生hash碰撞，此時(shí)效率可能極差。而B+樹的查詢效率比較穩(wěn)定，對(duì)于所有的查詢都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)，且樹的高度較低。

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

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

答：在B+樹的索引中，葉子節(jié)點(diǎn)可能存儲(chǔ)了當(dāng)前的key值，也可能存儲(chǔ)了當(dāng)前的key值以及整行的數(shù)據(jù)，這就是聚簇索引和非聚簇索引.。在InnoDB中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個(gè)唯一鍵建立聚簇索引，如果沒有唯一鍵，則隱式的生成一個(gè)鍵來建立聚簇索引。當(dāng)查詢使用聚簇索引時(shí)，在對(duì)應(yīng)的葉子節(jié)點(diǎn)，可以獲取到整行數(shù)據(jù),因此不用再次進(jìn)行回表查詢。

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

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

舉個(gè)簡(jiǎn)單的例子，假設(shè)我們?cè)趩T工表的年齡上建立了索引，那么當(dāng)進(jìn)行select age from employee where age < 20的查詢時(shí)，在索引的葉子節(jié)點(diǎn)上，已經(jīng)包含了age信息，不會(huì)再次進(jìn)行回表查詢。

18、對(duì)MySQL的鎖了解嗎?

答：當(dāng)數(shù)據(jù)庫(kù)有并發(fā)事務(wù)的時(shí)候，可能會(huì)產(chǎn)生數(shù)據(jù)的不一致，這時(shí)候需要一些機(jī)制來保證訪問的次序，鎖機(jī)制就是這樣的一個(gè)機(jī)制。

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

19、MySQL都有哪些鎖呢?像上面的例子進(jìn)行鎖定豈不是有點(diǎn)阻礙并發(fā)效率了?

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

共享鎖：又叫做讀鎖，當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí)，對(duì)數(shù)據(jù)加上共享鎖，共享鎖可以同時(shí)加上多個(gè)。

排他鎖：又叫做寫鎖，當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時(shí)，對(duì)數(shù)據(jù)加上排他鎖，排他鎖只可以加一個(gè)，他和其他的排他鎖，共享鎖都相斥。

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

鎖的粒度取決于具體的存儲(chǔ)引擎，InnoDB實(shí)現(xiàn)了行級(jí)鎖，頁(yè)級(jí)鎖，表級(jí)鎖。他們的加鎖開銷從大大小，并發(fā)能力也是從大到小。

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

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

statement模式下，記錄單元為語(yǔ)句。即每一個(gè)sql造成的影響會(huì)記錄，由于sql的執(zhí)行是有上下文的，因此在保存的時(shí)候需要保存相關(guān)的信息，同時(shí)還有一些使用了函數(shù)之類的語(yǔ)句無(wú)法被記錄復(fù)制。

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

mixed，一種折中的方案，普通操作使用statement記錄，當(dāng)無(wú)法使用statement的時(shí)候使用row。

此外，新版的MySQL中對(duì)row級(jí)別也做了一些優(yōu)化，當(dāng)表結(jié)構(gòu)發(fā)生變化的時(shí)候，會(huì)記錄語(yǔ)句而不是逐行記錄。

21、一條sql執(zhí)行過長(zhǎng)的時(shí)間，你如何優(yōu)化，從哪些方面？

1、查看sql是否涉及多表的聯(lián)表或者子查詢，如果有，看是否能進(jìn)行業(yè)務(wù)拆分，相關(guān)字段冗余或者合并成臨時(shí)表（業(yè)務(wù)和算法的優(yōu)化）。

2、涉及鏈表的查詢，是否能進(jìn)行分表查詢，單表查詢之后的結(jié)果進(jìn)行字段整合。

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

4、針對(duì)數(shù)量大的表進(jìn)行歷史表分離（如交易流水表）。

5、數(shù)據(jù)庫(kù)主從分離，讀寫分離，降低讀寫針對(duì)同一表同時(shí)的壓力，至于主從同步，mysql有自帶的binlog實(shí)現(xiàn) 主從同步。

6、explain分析sql語(yǔ)句，查看執(zhí)行計(jì)劃，分析索引是否用上，分析掃描行數(shù)等等。

更多MySQL面試題大家可以查看下面的相關(guān)鏈接