MySQL 三萬字精華總結 + 面試100 問，和面試官扯皮綽綽有余（收藏系列）

寫在之前：不建議那種上來就是各種面試題羅列，然后背書式的去記憶，對技術的提升幫助很小，對正經面試也沒什么幫助，有點東西的面試官深挖下就懵逼了，

個人建議把面試題看作是費曼學習法中的回顧、簡化的環節，準備面試的時候，跟著題目先自己講給自己聽，看看自己會滿意嗎，不滿意就繼續學習這個點，如此反復，好的offer離你不遠的，奧利給

一、MySQL架構

和其它資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用并發揮良好作用，主要體現在存盤引擎的架構上，插件式的存盤引擎架構將查詢處理和其它的系統任務以及資料的存盤提取相分離，這種架構可以根據業務的需求和實際需要選擇合適的存盤引擎，

（海量免費測驗資料加313782132，群內還會有同行一起交流哦~）

• 連接層：最上層是一些客戶端和連接服務，主要完成一些類似于連接處理、授權認證、及相關的安全方案，在該層上引入了執行緒池的概念，為通過認證安全接入的客戶端提供執行緒，同樣在該層上可以實作基于SSL的安全鏈接，服務器也會為安全接入的每個客戶端驗證它所具有的操作權限，

• 服務層：第二層服務層，主要完成大部分的核心服務功能， 包括查詢決議、分析、優化、快取、以及所有的內置函式，所有跨存盤引擎的功能也都在這一層實作，包括觸發器、存盤程序、視圖等

• 引擎層：第三層存盤引擎層，存盤引擎真正的負責了MySQL中資料的存盤和提取，服務器通過API與存盤引擎進行通信，不同的存盤引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取

• 存盤層：第四層為資料存盤層，主要是將資料存盤在運行于該設備的檔案系統之上，并完成與存盤引擎的互動

畫出 MySQL 架構圖，這種變態問題都能問的出來

MySQL 的查詢流程具體是？or 一條SQL陳述句在MySQL中如何執行的？

客戶端請求 ---> 連接器（驗證用戶身份，給予權限） ---> 查詢快取（存在快取則直接回傳，不存在則執行后續操作） ---> 分析器（對SQL進行詞法分析和語法分析操作） ---> 優化器（主要對執行的sql優化選擇最優的執行方案方法） ---> 執行器（執行時會先看用戶是否有執行權限，有才去使用這個引擎提供的介面） ---> 去引擎層獲取資料回傳（如果開啟查詢快取則會快取查詢結果）

說說MySQL有哪些存盤引擎？都有哪些區別？

二、存盤引擎

存盤引擎是MySQL的組件，用于處理不同表型別的SQL操作，不同的存盤引擎提供不同的存盤機制、索引技巧、鎖定水平等功能，使用不同的存盤引擎，還可以獲得特定的功能，

使用哪一種引擎可以靈活選擇，一個資料庫中多個表可以使用不同引擎以滿足各種性能和實際需求，使用合適的存盤引擎，將會提高整個資料庫的性能 ，

MySQL服務器使用可插拔的存盤引擎體系結構，可以從運行中的 MySQL 服務器加載或卸載存盤引擎 ，

查看存盤引擎

-- 查看支持的存盤引擎SHOW ENGINES -- 查看默認存盤引擎SHOW VARIABLES LIKE 'storage_engine' --查看具體某一個表所使用的存盤引擎，這個默認存盤引擎被修改了！show create table tablename --準確查看某個資料庫中的某一表所使用的存盤引擎show table status like 'tablename'show table status from database where name="tablename"復制代碼

設定存盤引擎

-- 建表時指定存盤引擎，默認的就是INNODB，不需要設定CREATE TABLE t1 (i INT) ENGINE = INNODB;CREATE TABLE t2 (i INT) ENGINE = CSV;CREATE TABLE t3 (i INT) ENGINE = MEMORY; -- 修改存盤引擎ALTER TABLE t ENGINE = InnoDB; -- 修改默認存盤引擎，也可以在組態檔my.cnf中修改默認引擎SET default_storage_engine=NDBCLUSTER;復制代碼

默認情況下，每當 CREATE TABLE 或 ALTER TABLE 不能使用默認存盤引擎時，都會生成一個警告，為了防止在所需的引擎不可用時出現令人困惑的意外行為，可以啟用 NO_ENGINE_SUBSTITUTION SQL 模式，如果所需的引擎不可用，則此設定將產生錯誤而不是警告，并且不會創建或更改表

存盤引擎對比

常見的存盤引擎就 InnoDB、MyISAM、Memory、NDB，

InnoDB 現在是 MySQL 默認的存盤引擎，支持事務、行級鎖定和外鍵

檔案存盤結構對比

在 MySQL中建立任何一張資料表，在其資料目錄對應的資料庫目錄下都有對應表的 .frm 檔案，.frm 檔案是用來保存每個資料表的元資料(meta)資訊，包括表結構的定義等，與資料庫存盤引擎無關，也就是任何存盤引擎的資料表都必須有.frm檔案，命名方式為 資料表名.frm，如user.frm，

查看MySQL 資料保存在哪里：show variables like 'data%'

MyISAM 物理檔案結構為：

• .frm檔案：與表相關的元資料資訊都存放在frm檔案，包括表結構的定義資訊等
• .MYD (MYData) 檔案：MyISAM 存盤引擎專用，用于存盤MyISAM 表的資料
• .MYI (MYIndex)檔案：MyISAM 存盤引擎專用，用于存盤MyISAM 表的索引相關資訊

InnoDB 物理檔案結構為：

• .frm 檔案：與表相關的元資料資訊都存放在frm檔案，包括表結構的定義資訊等

• .ibd 檔案或 .ibdata 檔案： 這兩種檔案都是存放 InnoDB 資料的檔案，之所以有兩種檔案形式存放 InnoDB 的資料，是因為 InnoDB 的資料存盤方式能夠通過配置來決定是使用共享表空間存放存盤資料，還是用獨享表空間存放存盤資料，

  獨享表空間存盤方式使用.ibd檔案，并且每個表一個.ibd檔案 共享表空間存盤方式使用.ibdata檔案，所有表共同使用一個.ibdata檔案（或多個，可自己配置）

ps：正經公司，這些都有專業運維去做，資料備份、恢復啥的，讓我一個 Javaer 搞這的話，加錢不？

面試這么回答

1. InnoDB 支持事務，MyISAM 不支持事務，這是 MySQL 將默認存盤引擎從 MyISAM 變成 InnoDB 的重要原因之一；
2. InnoDB 支持外鍵，而 MyISAM 不支持，對一個包含外鍵的 InnoDB 表轉為 MYISAM 會失敗；
3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引，聚簇索引的檔案存放在主鍵索引的葉子節點上，因此 InnoDB 必須要有主鍵，通過主鍵索引效率很高，但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到資料，因此，主鍵不應該過大，因為主鍵太大，其他索引也都會很大，而 MyISAM 是非聚集索引，資料檔案是分離的，索引保存的是資料檔案的指標，主鍵索引和輔助索引是獨立的，
4. InnoDB 不保存表的具體行數，執行select count(*) from table 時需要全表掃描，而 MyISAM 用一個變數保存了整個表的行數，執行上述陳述句時只需要讀出該變數即可，速度很快；
5. InnoDB 最小的鎖粒度是行鎖，MyISAM 最小的鎖粒度是表鎖，一個更新陳述句會鎖住整張表，導致其他查詢和更新都會被阻塞，因此并發訪問受限，這也是 MySQL 將默認存盤引擎從 MyISAM 變成 InnoDB 的重要原因之一；

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

如果表的型別是MyISAM，那么是18，因為MyISAM表會把自增主鍵的最大ID 記錄到資料檔案中，重啟MySQL自增主鍵的最大ID也不會丟失；

如果表的型別是InnoDB，那么是15，因為InnoDB 表只是把自增主鍵的最大ID記錄到記憶體中，所以重啟資料庫或對表進行OPTION操作，都會導致最大ID丟失，

哪個存盤引擎執行 select count(*) 更快，為什么?

MyISAM更快，因為MyISAM內部維護了一個計數器，可以直接調取，

• 在 MyISAM 存盤引擎中，把表的總行數存盤在磁盤上，當執行 select count(*) from t 時，直接回傳總資料，

• 在 InnoDB 存盤引擎中，跟 MyISAM 不一樣，沒有將總行數存盤在磁盤上，當執行 select count(*) from t 時，會先把資料讀出來，一行一行的累加，最后回傳總數量，

InnoDB 中 count(*) 陳述句是在執行的時候，全表掃描統計總數量，所以當資料越來越大時，陳述句就越來越耗時了，為什么 InnoDB 引擎不像 MyISAM 引擎一樣，將總行數存盤到磁盤上？這跟 InnoDB 的事務特性有關，由于多版本并發控制（MVCC）的原因，InnoDB 表“應該回傳多少行”也是不確定的，

三、資料型別（海量免費測驗資料加313782132，群內還會有同行一起交流哦~）

主要包括以下五大類：

（海量免費測驗資料加313782132，群內還會有同行一起交流哦~）

• 整數型別：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
• 浮點數型別：FLOAT、DOUBLE、DECIMAL
• 字串型別：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
• 日期型別：Date、DateTime、TimeStamp、Time、Year
• 其他資料型別：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

CHAR 和 VARCHAR 的區別？

char是固定長度，varchar長度可變：

char(n) 和 varchar(n) 中括號中 n 代表字符的個數，并不代表位元組個數，比如 CHAR(30) 就可以存盤 30 個字符，

存盤時，前者不管實際存盤資料的長度，直接按 char 規定的長度分配存盤空間；而后者會根據實際存盤的資料分配最終的存盤空間

相同點：

1. char(n)，varchar(n)中的n都代表字符的個數
2. 超過char，varchar最大長度n的限制后，字串會被截斷，

不同點：

1. char不論實際存盤的字符數都會占用n個字符的空間，而varchar只會占用實際字符應該占用的位元組空間加1（實際長度length，0<=length<255）或加2（length>255），因為varchar保存資料時除了要保存字串之外還會加一個位元組來記錄長度（如果列宣告長度大于255則使用兩個位元組來保存長度），
2. 能存盤的最大空間限制不一樣：char的存盤上限為255位元組，
3. char在存盤時會截斷尾部的空格，而varchar不會，

char是適合存盤很短的、一般固定長度的字串，例如，char非常適合存盤密碼的MD5值，因為這是一個定長的值，對于非常短的列，char比varchar在存盤空間上也更有效率，

列的字串型別可以是什么？

字串型別是：SET、BLOB、ENUM、CHAR、TEXT、VARCHAR

BLOB和TEXT有什么區別？

BLOB是一個二進制物件，可以容納可變數量的資料，有四種型別的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

TEXT是一個不區分大小寫的BLOB，四種TEXT型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，

BLOB 保存二進制資料，TEXT 保存字符資料，

標籤：其他

上一篇：不要在簡歷里寫太多精通

下一篇：從業以來最差的一次前端面試體驗