面試官問我Mysql的儲存引擎瞭解多少

2022-08-03 14:04:19

一、MySQL體系結構

如下圖，Mysql總共分為了四層：

連線層： 最上層是一些使用者端和連結服務,主要完成一些類似於連線處理、授權認證、及相關的安全方案。伺服器也會為安全接入的每個使用者端驗證它所具有的操作許可權。
服務層： 第二層架構主要完成大多數的核心服務功能，如SQL介面，並完成快取的查詢，SQL的分析和優化，部分內建函數的執行。所有跨儲存引擎的功能也在這一層實現，如過程、函數等。
引擎層： 儲存引擎真正的負責了MySQL中資料的儲存和提取，伺服器通過API和儲存引擎進行通訊。不同的儲存引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的儲存引擎。
儲存層： 主要是將資料儲存在檔案系統之上，並完成與儲存引擎的互動。

二、儲存引擎簡介

引擎就是發動機，引擎就是一個機器的核心部分，不同的引擎有著不同的應用場景，例如飛機有飛機的引擎，火箭有火箭的引擎，他們之間是沒有好壞之分的，我們只需要在合適的場景使用合適的引擎就可以了。

Mysql 儲存引擎就是儲存資料、建立索引、更新/查詢資料等技術的實現方式。儲存引擎是基於表的，而不是基於庫的，所以儲存引擎也可被稱為表型別。

三、儲存引擎的使用

（1）建立表的時候可以通過ENGINE來指定儲存引擎：

（2）檢視當前資料庫支援的儲存引擎：

建立表的時候假如不指定引擎，預設就是InnoDB，在mysql早期的時候預設儲存引擎是MyISAM。其中comment列就是官方對當前儲存引擎的特性描述！

（3）檢視某張表使用的儲存引擎

如下sql可以檢視當前表結構：

show create table 表名;

ENGINE：代表的是當前表的儲存引擎
AUTO_INCREMENT：代表的是自增id目前已經增到多少了
CHARESET：代表的是字元集
COLLATE：代表的是排序規則

四、儲存引擎特點

這裡重點解釋三個儲存引擎，同時也是面試當中經常會問的！

1、InnoDB

InnoDB是一種兼顧高可靠性和高效能的通用儲存引擎，在MySQL5.5之後，InnoDB是預設的MySQL儲存引擎。

（1）特點：

DML操作遵循ACID模型，支援事務;
行級鎖，提高並行存取效能;
支援外來鍵FOREIGN KEY約束，保證資料的完整性和正確性

（2）不管是哪個引擎，資料肯定都是儲存在硬碟的，我們可以通過以下命令檢視儲存位置：

SHOW VARIABLES LIKE 'datadir';

根據查詢的位置開啟，然後會發現每個資料庫就是一個資料夾

然後任意開啟一個庫進去會發現都是frm檔案！

frm檔案是用來儲存每個資料表的後設資料資訊，包括表結構的定義等。根本沒有找到對應的資料檔案，這是為什麼呢，繼續往下看！

（3）儲存檔案：

正常innodb引擎儲存表的時候會有兩個檔案，一個是frm檔案，一個是ibd檔案。

Xxx.ibd: xxx代表的是表名, innoDB引擎的每張表都會對應這樣一個表空間檔案,儲存該表的資料和索引。

可是我們在上面並沒有看到ibd檔案，這是因為mysql將儲存方式分為了兩種：

共用表空間：所謂共用表空間，就是所有資料庫的表資料都存在了一個地方獨立表空間：每個表都對應了一個ibd檔案，儲存資料

可以通過innodb_file_per_table引數來開啟獨立表空間，mysql8.0預設是開啟了獨立表空間，其餘貌似預設都是關閉的。

innodb_file_per_table 的簡要說明：

在很久很久以前也就是說還沒有innodb_file_per_table 的那個年代，所有的innodb表的資料都是儲存在共用表空間，在有了innodb_file_per_table引數後innodb可以把每個表的資料單獨儲存。單獨儲存有兩方面的優勢一個是方便管理，二個是提長效能。

show global variables like 'innodb_file_per_table';  -- 檢視
set @@global.innodb_file_per_table=off;      -- 關閉
set @@global.innodb_file_per_table=on;       -- 開啟innodb_file_per_table

innodb_file_per_table引數，mysql8.0預設是開啟的，如果開啟就代表的是每一張表對應了一個表空間。我的mysql是5.5版本的，所以根本沒有開啟，沒有開啟著意味著所有資料共用了一個表空間（也可以稱為資料檔案）。

（4）共用表空間檔案上哪找？

預設設定下有一個初識大小為10M，名為ibdata1的檔案，預設的表空間檔案（tablespace file）。通過引數innodb_data_file_path可以設定檔案

show global variables like 'innodb_data_file_path'; #檢視表空間檔案設定

解釋ibdata1:10M：autpextend：代表的是表空間檔名為ibdata1，然後初始大小為10M，檔案可以自動增長（autoextend）。

（5）通過以上命令我們知道表空間檔名稱為ibdata1，那麼他儲存在什麼地方？

他儲存在我們安裝mysql的時候指定的安裝目錄下，如果忘記安裝目錄了，可以通過全域性搜檔名稱，來尋找檔案！

整個mysql所有庫的資料都存放在下面檔案當中！

（6）可以修改共用表空間設定：

表示將/db/ibdata1和/dr2/db/ibdata2兩個檔案來組成表空間，其中ibadata1的大小為2000M，檔案ibdata2的大小為2000MB，如果用完了這2000MB，該檔案可以自動增長（autoextend）。

innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

（7）將innodb_file_per_table開啟

set @@global.innodb_file_per_table=on;       -- 開啟

開啟後他不會將歷史的表已經共用的給單獨獨立起來，而是指的以後在新建表的時候，那麼新建的表就是單獨的表空間。

開啟後新建了一個test表：

通過MySQL的 ibd2sdi 工具可以解析ibd檔案，可以解析成json資料。

（8）innodb儲存結構：

說是innodb儲存結構，不如說是ibd檔案的儲存結構！

一個表空間可以包含多個段
一個段當中又可以包含多個區
一個區當中包含多個頁，頁包含了索引頁和資料頁，一個區固定的是1M，一個page頁大小是16k，一個區當中可以包含64個頁
一個頁當中又包含了多個行，一個row行當中包含了最後一次事務id、指標、最後就是一個一個的欄位了

page頁是innodb儲存結構當中最小的單元

具體瞭解儲存結構的話內容有很多，後續寫一篇專門分析儲存結構的文章！

2、MyISAM

MyISAM是MySQL早期的預設儲存引擎。這也就是經常面試問Innodb和MyISAM區別的原因！

（1）特點：

不支援事務
不支援外來鍵
支援表鎖,不支援行鎖存取速度快

（2）儲存檔案：

一個表對應了三個檔案，而innodb對應了兩個檔案！

3、MEMORY

MEMORY引擎的表資料是儲存在記憶體中的,由於受到硬體問題、或斷電問題的影響,只能將這些表作為臨時表或快取使用。

（1）特點：

記憶體存放
hash索引(預設)

（2）儲存檔案：

xxx.sdi:儲存表結構資訊

五、儲存引擎選擇

在選擇儲存引擎時，應該根據應用系統的特點選擇合適的儲存引擎。對於複雜的應用系統，還可以根據實際情況選擇多種儲存引擎進行組合。

InnoDB：是Mysql的預設儲存引擎，支援事務、外來鍵。如果應用對事務的完整性有比較高的要求，在並行條件下要求資料的一致性，資料操作除了插入和查詢之外，還包含很多的更新、刪除操作，那麼InnoDB儲存引擎是比較合適的選擇。
MyISAM：如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並行性要求不是很高，那麼選擇這個儲存引攀是非常合適的。
MEMORY:將所有資料儲存在記憶體中,存取速度快,通常用於臨時表及快取。MEMORY的缺陷就是對錶的大小有限制,太大的表無法快取在記憶體中,而且無法保障資料的安全性。

在問你InnoDB和MyISAM的區別的時候，只需要圍繞這三點來回答：事務、外來鍵、行級鎖