MySQL-05.儲存引擎

长名06發表於2024-04-19
MySql儲存引擎

C-05.儲存引擎

為了方便管理，把連線管理，查詢快取，語法解析，查詢最佳化這些不涉及真實資料儲存的功能劃分為MySQL Server的功能，把真實存取資料的功能劃分為儲存引擎的功能。所以在MySQL Server完成了查詢最佳化後，只需按照生成的執行計劃呼叫底層儲存引擎提供的API，獲取到資料後返回給客戶端就好了。

MySQL中提到了儲存引擎的概念。簡而言之，儲存引擎就是表的型別。其實儲存引擎之前叫做表處理器，後來改名為儲存引擎，它的功能就是接收上層傳下倆的指令，然後對錶中的資料進行提取或寫入操作。

1.檢視儲存引擎

  • 檢視儲存引擎指令
show engines;


查詢結果，MySQL8.0支援9種儲存引擎，分別是FEDERATED，MEMORY，InnoDB，PERFORMANCE_SCHEMA，MyISAM ，MRG_MYISAM，BLACKHOLE，CSV和ARCHIVE。

  • Engine引數表示儲存引擎名稱。

  • Support引數表示MySQL資料庫管理系統是否支援該儲存引擎：YES表示支援，NO表示不支援。DEFAULT表示系統預設支援的儲存引擎

  • Comment引數，表示對儲存引擎的評論。

  • Transactions引數，表示是否支援事務。

  • XA引數，表示儲存引擎所支援的分散式是否符合XA規範，YES支援，NO不支援。代表該儲存引擎是否支援分散式事務。

  • Savepoints引數，表示儲存引擎是否支援事務處理的儲存點，YES支援，NO不支援。也就是說儲存引擎，是否支援部分回滾事務。

mysql> show engines\G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 4. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

2.設定系統預設的儲存引擎

  • 檢視預設的儲存引擎
show variables like '%storage_engine%';
#或者
select @@global.default_storage_engine;

mysql> select @@global.default_storage_engine;
+---------------------------------+
| @@global.default_storage_engine |
+---------------------------------+
| InnoDB                          |
+---------------------------------+
1 row in set (0.00 sec)
  • 修改預設的儲存引擎

如果在建立表的語句中，未指定儲存引擎的話，就會預設使用Innodb作為表的儲存引擎。如果我們想改變表的預設儲存引擎的話，可以這樣在執行下面的sql語句

SET DEFAULT_STORAGE_ENGINE = MyISAM;

或者修改my.cnf檔案

default-storage-engine=MyIASM #之後重啟msyqld服務

如果建立表時，未指明當前表的型別(儲存引擎)，則使用當前MySQL服務預設的儲存引擎。

3.設定表的儲存引擎

儲存引擎是負責對錶中的資料進行提取和寫入工作的，我們可以為不同的表設定不同的儲存引擎，也就是說不同的表可以有不同的物理儲存結構，不同的提取和寫入方式。

3.1 建立表時指定儲存引擎

CREATE TABLE 表名(
	建表語句
) ENGINE = 儲存引擎名稱;

#例子
CREATE TABLE demo2(id int) ENGINE = myisam;

3.2 修改表的儲存引擎

alter table table_name 
engine = myisam;

#例子
ALTER TABLE demo2 ENGINE = innodb;

4.引擎介紹

4.1 InnoDB引擎：具備外來鍵支援功能的事務儲存引擎(常用)

  • MySQL從3.23.34a開始包含Innodb儲存引擎。版本大於等於5.5之後，預設採用InnoDB儲存引擎。

  • InnoDB是MySQL的預設事務型引擎，它被設計用來處理大量的短期(short-lived)事務。可以確保事務的完整提交(Commit)和回滾(Rollback)。

  • 除了增加和查詢外，還需要更新，刪除操作，則優先選擇InnoDB儲存引擎。

  • 除非有非常特別的原因需要使用其他的儲存引擎，否則應該優先考慮InnoDB引擎。

  • 資料檔案結構：(在《第02章_MySQL資料目錄》)

    • 表名.frm儲存表結構(MySQL8.0時，合併在表名.ibd中)
    • 表名.ibd儲存資料和索引

  • InnoDB是為處理巨大資料量的最大效能設計。

    • 在之前的版本中，字典資料以後設資料檔案，非事務表等來儲存。現在這些後設資料檔案被刪除了。比如：.frm，.par，.trn

      .isl，db.opt等在MySQL8.0中不存在了

  • 對比MyISAM的儲存引擎，InnoDB寫的處理效率會差一些，並且會佔用更多的磁碟空間以儲存資料和索引。

  • MyISAM只快取索引，不快取真實資料；InnoDB不加快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響。

4.2 MyISAM引擎：主要的非事務處理儲存引擎(常用)

  • MyISAM提供了大量的特性，包括全文索引，壓縮，空間函式(GIS)等，但MyIASM不支援事務、行級鎖、外來鍵，有一個毫無疑問的缺陷就是崩潰後無法安全恢復。

  • 5.5之前預設的儲存引擎。

  • 優勢是訪問的速度快，對事務完整性沒有要求或者以SELECT，INSERT為主的應用。

  • 針對資料統計有額外的常數儲存。所以，count(*)的查詢效率很高。

  • 資料檔案結構：

    • 表名.frm儲存表結構
    • 表名.MYD儲存資料(MYDATA)
    • 表名.MYI儲存索引(MYINDEX)

  • 應用場景：只讀應用或者以讀為主的業務

4.3 Archive引擎：用於資料存檔(瞭解)

  • archive是歸檔的意思，僅僅支援插入和查詢兩種功能(行資料插入後，不能修改)。
  • 在MySQL5.5以後支援索引功能。
  • 擁有很好的壓縮機制，使用zlib壓縮庫，在記錄請求的時候實時的進行壓縮，經常被用來倉庫使用。
  • 建立Archive型別的表，儲存引擎會建立名稱以表名開頭的檔案。資料檔案的副檔名為.ARZ。
  • 根據英文的測試結論來看，同樣資料量下，Archive表比MyIASM表小約75%，比支援事務處理的InnoDB表小約83%。
  • Archive儲存引擎採用了行級鎖。該Archive引擎支援AUTO_INCREMENT列屬性。AUTO_INCREMENT列可以具有唯一索引或非唯一索引。嘗試在任何其他列上建立索引，會導致錯誤。
  • Archive表適合日誌和資料採集(檔案)類應用；適合儲存大量的獨立的作為歷史記錄的資料。擁有很高的插入速度，但是對查詢的支援較差。
  • 下圖展示了Archive儲存引擎的功能

4.4 Blackhole引擎：丟棄寫操作，讀操作會返回空內容

  • Blackhole引擎沒有實現任何儲存機制，它會丟棄所有插入的資料，不做任何儲存。
  • 但伺服器會記錄Blackhole表的日誌，所有可以用於複製資料到備庫，或者簡單地記錄到日誌。這種應用方式會有很多問題，此儲存引擎不推薦使用。

4.5 CSV引擎：儲存資料時，以逗號分割各個資料項(瞭解)

  • CSV引擎可以將普通的CSV檔案作為MySQL的表來處理，但不支援索引。
  • CSV引擎可以作為一種資料交換的格式，非常有用。
  • CSV儲存的資料直接可以在OS裡，用文字編輯器，或者execl讀取。
  • 對於資料的快速匯入，匯出有明顯的優勢。

建立CSV表時，伺服器會建立一個純文字資料檔案，其名稱以表名開頭並帶有.CSV副檔名。當你將資料儲存到表中時，儲存引擎將其以逗號分隔值格式儲存到資料檔案中。

使用案例如下

注意，在建立CSV型別的表示，所有欄位必須設定為非空約束。

mysql> create table CSV_demo(id int not null,name varchar(20) not null) engine = CSV;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into CSV_demo(id,name) values(1,'wind'),(2,'flower');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from CSV_demo;
+----+--------+
| id | name   |
+----+--------+
|  1 | wind   |
|  2 | flower |
+----+--------+
2 rows in set (0.01 sec)

4.6 Memory引擎：置於記憶體的表(瞭解)

概述：

Memory採用的邏輯介質是記憶體，相應速度很快，但是當mysqld守護程序崩潰的時候資料會丟失。另外，要求儲存的資料是資料長度不變的格式，比如，Blob和Text型別的資料不可用(長度不固定的)。

主要特徵：

  • Memory同時支援雜湊(HASH)索引和B樹索引。

    • 雜湊索引相等條件比較快，但是對於範圍的比較慢很多。
    • 預設使用雜湊(HASH)索引，其速度要比使用B型樹(BTREE)索引快。
    • 如果希望使用B樹索引，可以在建立索引時選擇使用。

  • Memory表至少比MyISAM表要快一個數量級。

  • MEMORY表的大小是受到限制的。表的大小主要取決於兩個引數，分別是max_rows和max_heap_table_size。其中，max_rows可以在建立表時指；max_heap_table_size的大小預設為16MB，可以按需進行擴大。

  • 資料檔案與索引檔案分開儲存

    • 每個基於MEMORY儲存引擎的表實際對應一個磁碟檔案，該檔案的檔名與表名相同，型別為.frm型別，該檔案中只儲存表的結構，而其資料檔案都是儲存在記憶體中的。
    • 這樣有利於資料的快速處理，提高整個表的處理效率。

  • 缺點：其資料易丟失，生命週期短。由於該缺陷，選擇MEMORY儲存引擎時需要特別小心。

使用Memory儲存引擎的場景

1.目標資料較小，而且非常頻繁的進行訪問，在記憶體中存放資料，如果太大的資料會造成記憶體溢位。可以透過引數max_heap_table_size控制Memory表的大小，限制Memory表的最大值。

2.如果資料是臨時的，而且必須立即可用得到，那麼就可以放在記憶體中。

3.儲存在Memory表中的資料如果突然間丟失的話也沒有太大的關係。

4.7 Federated引擎：訪問遠端表

  • Federated引擎是訪問其他MySQL伺服器的一個代理，儘管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，因此預設是禁用的。

4.8 Merge引擎：管理多個MyISAM表構成的表集合

4.9 NDB引擎：MySQL叢集專用儲存引擎

也叫做 NDB Cluster 儲存引擎，主要用於MySQL Cluster分散式叢集 環境，類似於Oracle的 RAC 叢集。

4.10 引擎對比

MySQL中同一個資料庫，不同的表可以選擇不同的儲存引擎。如下表對常用儲存引擎做出了對比。

~代表不支援

特點 MyISAM InnoDB Memory MERGE NDB
儲存限制 有 64TB 有 沒有 有
事務安全性 ~ 支援 ~ ~ ~
鎖機制 表鎖，即使操作一條記錄也會鎖住整個表，不適合高併發的操作 行鎖，操作時只鎖某一行，不對其它行有影響，適合高併發的操作。注意在使用update語句，而where條件中，沒有使用索引的話，行鎖，會升級為表鎖。 表鎖 表鎖 行鎖
B樹索引 支援 支援 支援 支援 支援
雜湊索引 ~ ~ 支援 ~ 支援
全文索引 支援 ~ ~ ~ ~
叢集索引 ~ 支援 ~ ~ ~
資料索引 ~ 支援 支援 ~ 支援
索引快取 只快取索引，不快取真實資料 不僅快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響 支援 支援 支援
資料可壓縮 支援 ~ ~ ~ ~
磁碟空間佔用 低 高 N/A 低 低
記憶體佔用 低 高 中等 低 高
批次插入速度 高 低 高 高 高
支援外來鍵 ~ 支援 ~ ~ ~

其實這些東西沒必要立即就給記住，列出來的目的就是表示不同的儲存引擎支援不同的功能。

其實我們最常用的就是InnoDB和MyISAM，有時會提一下Memory。其中InnoDB是MySQL預設的儲存引擎。

5.MyISAM和InnoDB對比

對比項 MyISAM InnoDB
外來鍵 不支援 支援
事務 不支援 支援
行表鎖 表鎖，即使操作一條記錄也會鎖住整個表，不適合高併發操作 行鎖，如果只操作某一行，只鎖某一行，不對其他行有影響，適合高併發操作
快取 只快取索引，不快取真實資料 不僅快取索引，還快取真實資料，堆記憶體要求較高，而且記憶體大小對效能有決定性的影響
自帶系統表使用 Y N(在MySQL8.0後，也有系統表使用InnoDB引擎)
關注點 節省資源，消耗少，簡單業務 事務，高併發，更大資料量
預設安裝 Y Y
預設使用 N Y

知識引入

1.InnoDB表的優勢

InnoDB儲存引擎在實際應用中擁有諸多優勢，比如操作便利、提高了資料庫的效能、維護成本低等。如果由於硬體或軟體的原因導致服

務器崩潰，那麼在重啟伺服器之後不需要進行額外的操作。InnoDB崩潰恢復功能自動將之前提交的內容定型，然後撤銷沒有提交的進

程，重啟之後繼續從崩潰點開始執行。

InnoDB儲存引擎在主記憶體中維護緩衝池，高頻率使用的資料將在記憶體中直接被處理。這種快取方式應用於多種資訊，加速了處理程序。

在專用伺服器上，實體記憶體中高達80%的部分被應用於緩衝池。如果需要將資料插入不同的表中，可以設定外來鍵加強資料的完整性。更新

或者刪除資料，關聯資料將會被自動更新或刪除。如果試圖將資料插入從表，但在主表中沒有對應的資料，插入的資料將被自動移除。如

果磁碟或記憶體中的資料出現崩潰，在使用髒資料之前，校驗和機制會發出警告。當每個表的主鍵都設定合理時，與這些列有關的操作會被

自動最佳化。插入、更新和刪除操作透過做改變緩衝自動機制進行最佳化。InnoDB不僅支援當前讀寫，也會緩衝改變的資料到資料流磁碟。

InnoDB的效能優勢不只存在於長時執行查詢的大型表。在同一列多次被查詢時，自適應雜湊索引會提高查詢的速度。使用InnoDB可以壓

縮表和相關的索引，可以在不影響效能和可用性的情況下建立或刪除索引。對於大型文字和BLOB資料，使用動態行形式，這種儲存佈局更高

效。透過查詢INFORMATION_SCHEMA庫中的表可以監控儲存引擎的內部工作。在同一個語句中，InnoDB表可以與其他儲存引擎表混

用。即使有些作業系統限制檔案大小為2GB，InnoDB仍然可以處理。當處理大資料量時，InnoDB兼顧CPU，以達到最大效能。

2.InnoDB和ACID模型

ACID模型是一系列資料庫設計規則，這些規則著重強調可靠性，而可靠性對於商業資料和任務關鍵型應用非常重要。MySQL包含類似

InnoDB儲存引擎的元件，與ACID模型緊密相連，這樣出現意外時，資料不會崩潰，結果不會失真。如果依賴ACID模型，可以不使用一致

性檢查和崩潰恢復機制。如果擁有額外的軟體保護，極可靠的硬體或者應用可以容忍一小部分的資料丟失和不一致，可以將MySQL設定

調整為只依賴部分ACID特性，以達到更高的效能。下面講解InnoDB儲存引擎與ACID模型相同作用的四個方面。

1. 原子方面 ACID的原子方面主要涉及InnoDB事務，與MySQL相關的特性主要包括：

  • 自動提交設定。
  • COMMIT語句。
  • ROLLBACK語句。
  • 操作INFORMATION_SCHEMA庫中的表資料。

2. 一致性方面 ACID模型的一致性主要涉及保護資料不崩潰的內部InnoDB處理過程，與MySQL相關的特性

主要包括：

  • InnoDB雙寫快取。
  • InnoDB崩潰恢復。

3. 隔離方面 隔離是應用於事務的級別，與MySQL相關的特性主要包括：

自動提交設定。

  • SET ISOLATION LEVEL語句。
  • InnoDB鎖的低階別資訊。

4. 永續性方面 ACID模型的耐久性主要涉及與硬體配置相互影響的MySQL軟體特性。由於硬體複雜多樣化，耐久性方面沒有具體的規則可循。與MySQL相關的特性有：

  • InnoDB雙寫快取，透過innodb_doublewrite配置項配置。
  • 配置項innodb_flush_log_at_trx_commit。
  • 配置項sync_binlog。
  • 配置項innodb_file_per_table。
  • 儲存裝置的寫入快取。
  • 儲存裝置的備用電池快取。
  • 執行MySQL的作業系統。
  • 持續的電力供應。
  • 備份策略。
  • 對分散式或託管的應用，最主要的在於硬體裝置的地點以及網路情況。

3.InnoDB架構(瞭解)

1. 緩衝池 緩衝池是主記憶體中的一部分空間，用來快取已使用的表和索引資料。緩衝池使得經常被使用的資料能夠直接在記憶體中獲得，從而提高速度。

2. 更改快取 更改快取是一個特殊的資料結構，當受影響的索引頁不在快取中時，更改快取會快取輔助索引頁的更改。索引頁被其他讀取操作時會載入到快取池，快取的更改內容就會被合併。不同於叢集索引，輔助索引並非獨一無二的。當系統大部分閒置時，清除操作會定期執行，將更新的索引頁刷入磁碟。更新快取合併期間，可能會大大降低查詢的效能。在記憶體中，更新快取佔用一部分InnoDB緩衝池。在磁碟中，更新快取是系統表空間的一部分。更新快取的資料型別由innodb_change_buffering配置項管理。

3. 自適應雜湊索引 自適應雜湊索引將負載和足夠的記憶體結合起來，使得InnoDB像記憶體資料庫一樣執行，不需要降低事務上的效能或可靠性。這個特性透過innodb_adaptive_hash_index選項配置，或者透過--skip-innodb_adaptive_hash_index命令列在服務啟動時關閉。

4. 重做日誌快取 重做日誌快取存放要放入重做日誌的資料。重做日誌快取大小透過innodb_log_buffer_size配置項配置。重做日誌快取會定期地將日誌檔案刷入磁碟。大型的重做日誌快取使得大型事務能夠正常執行而不需要寫入磁碟。

5. 系統表空間 系統表空間包括InnoDB資料字典、雙寫快取、更新快取和撤銷日誌，同時也包括表和索引資料。多表共享，系統表空間被視為共享表空間。

6. 雙寫快取 雙寫快取位於系統表空間中，用於寫入從快取池重新整理的資料頁。只有在重新整理並寫入雙寫快取後，InnoDB才會將資料頁寫入合適的位置。

7. 撤銷日誌 撤銷日誌是一系列與事務相關的撤銷記錄的集合，包含如何撤銷事務最近的更改。如果其他事務要查詢原始資料，可以從撤銷日誌記錄中追溯未更改的資料。撤銷日誌存在於撤銷日誌片段中，這些片段包含於回滾片段中。

8. 每個表一個檔案的表空間 每個表一個檔案的表空間是指每個單獨的表空間建立在自身的資料檔案中，而不是系統表空間中。這個功能透過innodb_file_per_table配置項開啟。每個表空間由一個單獨的.ibd資料檔案代表，該檔案預設被建立在資料庫目錄中。

9. 通用表空間 使用CREATE TABLESPACE語法建立共享的InnoDB表空間。通用表空間可以建立在MySQL資料目錄之外能夠管理多個表並支援所有行格式的表。

10. 撤銷表空間 撤銷表空間由一個或多個包含撤銷日誌的檔案組成。撤銷表空間的數量由innodb_undo_tablespaces配置項配置。

11. 臨時表空間 使用者建立的臨時表空間和基於磁碟的內部臨時表都建立於臨時表空間。innodb_temp_data_file_path配置項定義了相關的路徑、名稱、大小和屬性。如果該值為空，預設會在innodb_data_home_dir變數指定的目錄下建立一個自動擴充套件的資料檔案。

12. 重做日誌 重做日誌是基於磁碟的資料結構，在崩潰恢復期間使用，用來糾正資料。正常操作期間，重做日誌會將請求資料進行編碼，這些請求會改變InnoDB表資料。遇到意外崩潰後，未完成的更改會自動在初始化期間重新進行。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

相關文章