共計 5631 個字符，預計需要花費 15 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章將為大家詳細講解有關 MySQL 進階之體系結構知識點，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

5 MySQL 體系結構

下面我們選一些比較常用的存儲引擎進行簡單的說明，mysql 所使用的存儲引擎會對數據庫的性能產生直接的影響，還希望各位能仔細的了解存儲引擎的一些特點，完了之后才使用存儲引擎。

5.1 MyISAM

MyISAM 在 MySQL5.5 之前版本是默認的存儲引擎。由于這個原因，還有很多服務器在使用 MyISAM 這個存儲引擎。同時，MyISAM 目前是很多系統表，臨時表所使用的存儲引擎，這里說的臨時表不是我們通過 create table 創建出來的表，是指在排序、分組等操作中，當數量超過一定的大小之后，有查詢優化器建立的臨時表。
MyISAM 存儲引擎是由 MYD 和 MYI 組成，MYD 是數據文件的擴展名，MYI 是索引文件的擴展名，這個存儲引擎是將表存儲在以這兩個為擴展名的數據文件和索引文件中。

特性：

并發性與鎖級別
MyISAM 使用的是表級鎖，并不是行級鎖，這也就意味著對表中的數據進行修改時，需要對整個表進行加鎖，而在對表的讀取時也對所有的表加共享鎖，從這里我們可以看到，使用 MyISAM 做引擎的表讀取和寫入兩種操作是互斥的。由此可以看到 MyISAM 對于讀寫的并發操作并不會很好。如果只對于只讀取操作的話，就并發性而言，性能也還不錯，因為共享鎖不會阻塞共享鎖。

表損壞修復
MyISAM 支持由于任意意外關閉而損壞的 MyISAM 表，進行檢查和修復操作，但是這里說的修復并不是數據的恢復，因為 MyISAM 并不是一種事務性的存儲引擎，所以它不能進行事務恢復所需要的相關日志，所以要注意 MyISAM 表的恢復可能會造成數據的丟失。
我們可以通過 check table tablename 對表進行檢查，通過 repair table tablename 對表進行恢復。

MyISAM 表支持的索引類型
MyISAM 支持全文索引，并且在 mysql5.7 版本之前唯一原生就支持全文索引的官方存儲引擎。

MyISAM 表支持數據壓縮
如果 MyISAM 表示一張很大的只讀表，也就是在表創建完導入數據后，就不會對表進行任何修改操作了，那我們就可以對表進行壓縮了，以減少磁盤 I /O。我們可以使用 myisampack 命令來對表進行壓縮。壓縮是對表獨立進行壓縮的，因此讀取一行數據的時候，不必對整個表進行解壓。

限制：

版本 mysql5.0 時默認表大小為 4G

如存儲大表則要修改 MAX_Rows 和 AVG_ROW_LENGTH

版本 mysql5.0 時默認支持位 256TB

適用場景：

非事務性應用

只讀類應用（報表之類）

空間類應用

5.2 Innodb

Innodb 是 MySQL5.5 及之后版本默認的存儲引擎，Innodb 是事務存儲的存儲引擎，也就是說支持事務的處理。
Innodb 有自己的表空間的概念，然后數據是存儲在表空間之中的，是由 innodb_file_per_table 這個參數來決定的，如果這個參數為 ON，則會對每個 Innodb 表建立一個擴展名為 ibd 的系統文件，如果這個參數為 OFF 時，則會把數據存儲到系統的共享表空間，也就是 ibdataX，X 代表的時一個數字，默認從 1 開始。
查看這個參數的命令為：
show variables like innodb_file_per_table
修改這個參數的命令為：
set global innodb_file_per_table=off;

5.2.1 系統表空間和獨立表空間要如何選擇

比較：

系統表空間獨立表空間無法簡單的收縮文件大小可以通過 optimize table 命令收縮系統文件會產生 IO 瓶頸可以同時向多個文件刷新數據

建議：

對 Innodb 使用獨立表空間

把原來存在于系統表空間中的表轉移到獨立表空間中的方法。
步驟：

使用 mysqldump 導出所有數據庫表數據

停止 MySQL 服務，修改參數，并刪除 Innodb 相關文件

重啟 MySQL 服務，重建 Innodb 系統表空間

重新導入數據

5.2.2 Innodb 存儲引擎的特性

Innodb 是一種事務性存儲引擎

完全支持事務的 ACID 特性（之前介紹過的原子性，一致性等）

Redo Log 和 Undo Log
Redo Log 實現了事務的持久性，有兩部分組成，其中一個是內存中的工作日志持久緩沖區，是由 innodb_log_buffer_size 決定它的大小，另一個是重構日志文件，也就是我們在文件系統中看到的 ib_logflie 的相關文件。Undo Log 實現了事務的原子性，在事務失敗時進行回滾操作。Redo Log 是順序讀寫的，Undo Log 是隨機讀寫的，如果可以的話可以將數據存儲在固態硬盤中，以提高性能。

Innodb 支持行級鎖
行級鎖和表級鎖是不一樣的，行級鎖的特點就是可以最大程度的支持并發，行級鎖是由存儲引擎層實現的。

5.2.3 Innodb 狀態檢查

可以使用以下命令對 Innodb 狀態進行檢查：
show engine innodb status

5.2.4 適用場景

Innodb 適合于大多數 OLTP 應用，因為在 mysql5.7 版本之后，Innodb 已經支持了全文索引和空間函數。

5.2.4 (拓展) 什么是鎖

5.2.3.1 什么是鎖？

鎖最主要作用是管理共享資源的并發訪問

鎖用于實現事務的隔離性

5.2.3.2 鎖的類型：

共享鎖（也稱讀鎖）

獨占鎖（也稱寫鎖）

5.2.3.3 寫鎖和讀鎖的兼容關系（對一行的兼容性情況）
寫鎖讀鎖寫鎖不兼容不兼容讀鎖不兼容兼容

在實際情況中，可能與上表的結果會有所不同，主要是因為 Innodb 中的鎖機制是很復雜的一樣東西，還有很多鎖的存在影響最終的結果。

5.2.3.4 鎖的粒度：

表級鎖

行級鎖

5.2.3.5 阻塞和死鎖

阻塞：阻塞是因為不同鎖之間的兼容性的關系，在有些時刻一個事務中的鎖需要等待另一事務的鎖釋放，它所占用的資源形成了阻塞。
死鎖：死鎖是指兩個或兩個以上的事務執行過程中，相互占用了對方等待的資源而產生的一種異常。從定義中可以看到，處在阻塞中的多個事務占用了被阻塞的事務等待的資源，而死鎖是多個阻塞的事務互相占用了對方等待的資源。

5.3 CSV 存儲引擎

CSV 存儲引擎可以將 csv 文件作為 mysql 的表文件來處理，這種存儲引擎的存儲格式就是普通的 csv 文件，在 csv 存儲引擎的數據存儲方式非常的由特點，如果我們把表存儲在 MyISAM 或者 Innodb 中，其數據文件我們是不能直接查看的，因為這兩種文件的存儲是以二進制的格式來存儲的，而 CSV 存儲引擎則不同，CSV 的數據是以文本的方式存儲在文件中的，也就是我們可以通過查看文件的命令來查看，如 more，或者使用 vi 命令來查看編輯 csv 存儲引擎中的表，只要符合 CSV 文件的格式和要求，我們就不用擔心損壞數據。
當我們在 mysql 中建立了 CSV 存儲引擎表時，我們應該可以看到 3 個文件系統中的文件。這 3 個都是以表名為文件名，但是會分別以 csv，csm，frm 為后綴，其中 csv 文件就是 CSV 存儲引擎中的數據文件。csm 文件存儲表的元數據和表狀態和數據量。frm 文件存儲表結構信息。

5.3.1 CSV 存儲引擎的特點

最大的特點是以 CSV 格式進行數據存儲
CSV 中的每一列都是以, 來分隔的，并且文本的內容是以雙引號來引起來的，如下圖所示：

所有列必須都是不能為 NULL 的
在建表的時候所有的列都必須是非空的，不能存儲為 NULL 的值

不支持索引
不適合大表，不適合在線處理

可以對數據文件直接編輯
保存文本文件內容

5.3.2 CSV 存儲引擎的適用場景

CSV 存儲引擎適合作為數據交換的中間表

5.4 Archive 存儲引擎

5.4.1 文件系統存儲特點

Archive 存儲引擎會緩存所有的寫，并且利用 zlib 對插入的行進行壓縮，因此 Archive 存儲引擎相對于 MyISAM 存儲引擎的表更加節省磁盤 I /O，對于同樣數量級的數據，Archive 存儲引擎相對于 MyISAM 和 Innodb 更加節省存儲空間。一個幾 T 的 Innodb 的表存儲在 Archive 存儲引擎當中，可能只需要幾百兆的存儲空間。
Archive 存儲引擎的表的數據是以 ARZ 為后綴的一個文件，和其他引擎一樣，也存在一個以 frm 為后綴的系統文件，用于存儲表的結構信息。

5.4.2 Archive 存儲引擎的特點

只支持 insert 和 select 操作

只允許在自增 ID 列上加索引

5.4.3 Archive 存儲引擎使用場景

場景 1：日志和數據采集類數據
因為 Archive 不支持修改和刪除，而我們 ORDB 一定會對數據進行修改的，但是對于一些倉庫類型的應用，或者一些特殊的表，還是有用的，比如說，記錄日志的表或者是數據采集類的表，因為它需要采集大量數據，所以比較適合使用 Archive 存儲引擎。因為 Archive 存儲引擎在所有引擎中來說，它的存儲空間是最小的，但是還是要注意，即使在數據采集或日志的應用中，Archive 存儲引擎是無法對這些數據進行更新的，所以在記錄日志或者在數據采集類應用中對數據進行修改的話，可能也無法使用 Archive 存儲引擎。

5.5 Memory 存儲引擎

5.5.1 文件系統存儲特點

Memory 存儲引擎也稱之為 HEAP 存儲引擎，所以數據都保存在內存中，這就意味著這中數據的表是一次性的，一旦 MySQL 服務重啟，所有 Memory 存儲引擎的數據都會消失，但是表結構會保留下來，因為在 Memory 存儲引擎下創建表，只會生成一個 frm 系統文件，該文件是用于保存表結構的。這就是為什么重啟 MySQL 服務器數據會丟失，表結構不會的原因。
從它的文件存儲特點我們可以知道，Memory 存儲引擎的 I / O 效率會比 MyISAM 高很多，因為 MyISAM 只有索引會保存在內存中，而數據則由操作系統來緩存的，而 Memory 存儲引擎所有數據和索引都保存在內存中，下面我們看一下 Memory 存儲引擎的功能特點。

5.5.2 Memory 的功能特點

功能特點：

支持 HASH 索引（默認）和 BTree 索引
如果是 HASH 索引在做等值查詢的時候會非常的快，如果是做范圍查詢的話就無法使用 HASH 索引了，所以在表創建的時候我們需要注意，如果表需要大量的等值查詢就用 HASH 索引，范圍查詢就使用 BTree 索引。不同索引類型會對性能產生很大的影響。

所有字段都為固定長度 varchar(10) = char(10)
這就要求我們在定義表結構時，一定要符合要求最小的字段長度，否則浪費大量的內存。

不支持 BLOG 和 TEXT 等大字段

Memory 存儲引擎使用表級鎖

最大大小由 max_heap_table_size 參數決定
這個參數的默認值只有 16 兆，如果我們要在 Memory 存儲引擎表中存儲大量數據，就要修改這個參數，而這個參數修改是對已經存在的 Memory 存儲引擎的表是不生效的，如果需要對存在表生效的話就需要通過對已經存在的表進行重建。

5.5.3 Memory 中容易混淆的概念

Memory 存儲引擎表：
對所有的系統都可以使用，它并不是一種臨時表。
臨時表：
臨時表分為兩種，一種是查詢優化器在優化查詢時所使用的系統使用臨時表，也就是內部臨時表，系統使用臨時表在超過限制（使用 BLOB 或 TEXT 大字段）時使用 MyISAM 臨時表，未超限制使用 Memory 表。
另一種是通過命令 create temporary table 建立的臨時表，建立的表可以使用任何存儲引擎。
無論是哪種臨時表，只對內部可見。

5.5.4 Memory 的使用場景

用于查找或者映射表，例如郵編和地區的對應表

用于保存數據分析中產生的中間表

用于緩存周期聚合數據的結果表

Memory 數據易丟失，所以要求數據可再生。

5.6 Federated 存儲引擎

5.6.1 Federated 的特點

提供了訪問遠程 MySQL 服務器上表的方法
由于 Federated 存儲引擎只是在本地建立了到遠程服務器的一個連接，所以可以說我們所要訪問的表全部還是放在遠程服務器上，在本地并不存儲數據。每次訪問 Federated 存儲引擎表的時候，查詢都會被發送到遠程服務器上運行，并從遠程的 MySQL 服務器上獲取相關的數據。

本地不存儲數據，數據全部放到遠程服務器上

本地需要保存表結構和遠程服務器的連接信息
因此也會在系統中存在一個 frm 文件，用于存于遠程信息以及如何連接遠程表的相關信息。

5.6.2 Federated 如何使用

Federated 存儲引擎可以實現 SQL Server 連接服務器的功能，但是由于本身的性能并不太好，通常可以通過復制等實現相同的目的，所以在當前的 MySQL 版本中，Federated 存儲引擎默認是禁止的。如果需要使用 Federated 存儲引擎，則需要在 /usr/local/mysql/my.cnf 中加入 federated=1，接著重啟 MySQL 服務器，我們可以通過 show engine 來確認當前 MySQL 服務器是否支持 Federated 存儲引擎。
而在 create table 語句中使用下面的連接字符串，
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
遠程服務器綁定連接：
grant select,update,insert,delete on remote.remote_fet to fred_link@ 127.0.0.1 identified by 123456
就可以決定查詢的遠程服務器的相關信息以及相關的數據庫表的一些信息。

5.6.3 Federated 的適用場景

偶爾的統計分析及手工查詢
由于 Federated 的性能較慢，只適用于偶爾的統計分析及手工查詢。

6 如何選擇正確的存儲引擎

參考條件：

事務

備份

崩潰恢復

存儲引擎的特有特性
盡量避免混合使用存儲引擎。

關于“MySQL 進階之體系結構知識點”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

向 AI 問一下細節