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

1、Mysql中有哪幾種鎖？

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

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

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

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

在缺省模式下，MYSQL 是 autocommit 模式的，所有的數(shù)據(jù)庫更新操作都會即時提交，所 以在缺省情況下，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;

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

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

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

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

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

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

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

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

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

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

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

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

過程如下 ：

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

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

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

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

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

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

InnoDB 支持 MVCC, 而 MyISAM 不支持。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

后者的可讀性比前者要高

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

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

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

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

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

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

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

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

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

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

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

MySQL索引的分類

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

18、對MySQL的鎖了解嗎?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最新評論