MySQL 可能是最著名的關系數(shù)據(jù)庫管理系統(tǒng) (RDBMS),作為一款免費開源軟件開發(fā)，最初由 MYSQL AB 公司提供支持，但現(xiàn)在歸 Oracle 所有。

在 MySQL 中，用于表的“存儲引擎”決定了數(shù)據(jù)的處理方式。有幾種可用的存儲引擎，但最常用的是 InnoDB 和 MyISAM。

在本文中，我們將了解它們的顯著特征以及它們之間的主要區(qū)別。

在本教程中，您將學習：

• 什么是存儲引擎
• 如何檢查可用的存儲引擎
• MyISAM 和 InnoDB 的主要區(qū)別
• 如何檢查表使用的引擎
• 如何設置和更改表使用的存儲引擎

什么是存儲引擎？

在我們討論兩個主要 MySQL 存儲引擎之間的特性和區(qū)別之前，先來了解一下什么是存儲引擎？

存儲引擎，也稱為“表處理程序”，基本上是解釋和管理與數(shù)據(jù)庫表的 SQL 查詢相關的操作的數(shù)據(jù)庫部分。

在最新版本的 MySQL 中，可以使用“可插拔”架構來組織和管理存儲引擎，存在多種存儲引擎，但最常用的兩個是InnoDB和MyISAM。

檢查可用的存儲引擎

要獲得我們正在使用的數(shù)據(jù)庫中可用存儲引擎的列表，我們所要做的就是發(fā)出一個簡單的 SQL 查詢，因此我們需要做的第一件事就是打開一個 MySQL 交互式提示并使用數(shù)據(jù)庫用戶登錄及其密碼：

$ mysql -u <username> -p<password>

如果登錄成功，提示將變?yōu)閙ysql>，在這里，我們可以運行我們的 SQL 查詢來可視化可用的存儲引擎：

mysql> SHOW ENGINES;

執(zhí)行查詢后，我們應該獲得類似于以下內(nèi)容的結果：

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disAppears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

在上表中，作為查詢結果生成，我們可以通過查看Support每行列中的值輕松了解支持哪些存儲引擎，“YES”值表示存儲引擎可用，否則“NO”。相反，同一列中的“DEFAULT”值表示相應的引擎（在本例中為 InnoDB）是服務器使用的默認引擎。

“Transactions”和“Savepoints”列中存在的值分別表示存儲引擎是否支持事務和回滾。正如我們通過查看表可以看到的，只有 InnoDB 引擎可以。

關于存儲引擎的信息存在于“INFORMATION_SCHEMA”數(shù)據(jù)庫的“ENGINES”表中，因此我們也可以發(fā)出標準的“SELECT”查詢來獲取我們需要的數(shù)據(jù)：

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES

我們將獲得與上面看到的相同的結果。

InnoDB 與 MyISAM

讓我們看看兩個最常用的存儲引擎 InnoDB 和 MyISAM 之間的主要特性和區(qū)別是什么。

數(shù)據(jù)庫

正如我們已經(jīng)說過的， InnoDB 是自 MySQL 以來的默認存儲引擎5.5。

此存儲引擎的一些主要功能如下：

• 支持帶有提交和回滾的事務
• 行級鎖定
• 外鍵支持，具有級聯(lián)更新和刪除

帶有回滾和提交的事務

對事務的支持提供了一種安全的方式來執(zhí)行多個查詢以保持數(shù)據(jù)一致。

當多個修改數(shù)據(jù)的操作被執(zhí)行并且我們想要確保它們只有在所有操作都成功并且沒有錯誤發(fā)生時才有效時，我們想要使用事務。

典型的處理方式是啟動事務并執(zhí)行查詢：如果出現(xiàn)錯誤，則執(zhí)行回滾，否則提交更改。

行級鎖

當使用 InnoDB 數(shù)據(jù)鎖定發(fā)生在行級別時，因此在事務期間鎖定的數(shù)據(jù)量是有限的。

InnoDB 有兩種類型的鎖：

• 共享鎖
• 排他鎖

一個共享鎖允許誰擁有它讀取該行的交易，而一個排它鎖允許交易執(zhí)行其修改行的操作，所以要更新或刪除數(shù)據(jù)。

當一個事務在某行上獲得共享鎖，而另一個事務需要相同的鎖類型時，立即授予；但是，如果第二個事務在同一行上請求排他鎖，它將不得不等待。

如果第一個事務持有該行的排他鎖，則第二個事務將不得不等待該鎖被釋放以獲得共享鎖或排他鎖。

外鍵支持

外鍵是一個非常重要的特性，因為它們可用于基于表之間的邏輯關系來強制執(zhí)行數(shù)據(jù)完整性。想象一下，我們的數(shù)據(jù)庫中有三個表（假設它被稱為“testdb”）：一個user包含現(xiàn)有用戶的job表，一個注冊所有可用作業(yè)的user_job表，以及一個用于表示用戶和用戶之間存在的多對多關系的表。作業(yè)（一個用戶可以有多個作業(yè)，多個作業(yè)可以與同一個用戶關聯(lián)）。

該user_job表就是所謂的連接表或關聯(lián)表，因為它的唯一目的是表示用戶-工作關聯(lián)。該表有兩列，一個叫user_id和其他job id。表中會存在兩個外鍵約束，強制執(zhí)行以下規(guī)則：user_id列中的值只能引用表id列中的值，列中的user值job_id必須引用表id列中的現(xiàn)有值job.

這將強制執(zhí)行完整性，因為僅允許現(xiàn)有用戶和作業(yè)的 ID 存在于關聯(lián)表中。刪除涉及表中一個或多個關聯(lián)的用戶或作業(yè)user_job也是不允許的，除非為相應的外鍵設置了CASCADE DELETE規(guī)則。在這種情況下，當刪除用戶或作業(yè)時，它們所涉及的關系也將被刪除。

我的ISAM

MyISAM 曾經(jīng)是默認的 MySQL 存儲引擎，但已被 InnoDB 取代。使用此引擎時，數(shù)據(jù)鎖定發(fā)生在表級別，因此執(zhí)行操作時鎖定的數(shù)據(jù)更多。

與 InnoDB 不同，MyISAM 不支持事務回滾和提交，因此必須手動執(zhí)行回滾。MyISAM 和 InnoDB 之間的另一個很大區(qū)別是前者不支持外鍵。MyISAM 更簡單，并且在對有限數(shù)據(jù)集進行讀取密集型操作時可能具有優(yōu)勢（有爭議）。

在表上使用 MyISAM 時，會設置一個標志，指示該表是否需要修復，例如在突然關閉之后。稍后可以使用適當?shù)墓ぞ邎?zhí)行表修復。

檢查特定表使用的存儲引擎

如何知道特定表使用了什么存儲引擎？我們所要做的就是發(fā)出一個簡單的查詢。

例如，要知道user我們在前面的例子中提到的表使用了什么存儲引擎，我們將運行：

mysql> SHOW TABLE STATUS WHERE name = 'user' G;

注意上面的查詢我們使用了G，為了讓查詢結果垂直顯示，優(yōu)化空間。執(zhí)行查詢后，我們將獲得以下結果：

*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-12-27 09:38:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

在這種情況下，通過查看“Engine”列中存儲的值，我們可以清楚地看到該表使用的是“InnoDB”引擎。獲取相同信息的另一種方法是INFORMATION_SCHEMA.TABLES直接查詢表：

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'testdb';

上面的查詢將只返回表使用的引擎：

+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+

如果我們稍微更改查詢，我們可以獲得數(shù)據(jù)庫中所有表名的列表以及它們使用的引擎：

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testdb';

設置和更改表使用的存儲引擎

如果我們要為一個表設置一個特定的存儲引擎，我們可以在創(chuàng)建時指定它。例如，假設我們正在創(chuàng)建job表，并且出于某種原因我們想要使用 MyISAM 存儲引擎。我們將發(fā)出以下 SQL 查詢：

mysql> CREATE TABLE testdb.job ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL ) ENGINE = MyISAM;

相反，如果我們想要更改用于已存在表的存儲引擎，我們只需要使用ALTERSQL 語句。假設我們要將上一個示例中創(chuàng)建的“job”表所使用的存儲引擎更改為 InnoDB；我們會運行：

mysql> ALTER TABLE testdb.job ENGINE = InnoDB;

結論

在本教程中，我們學習了什么是數(shù)據(jù)庫存儲引擎，并且我們看到了兩個最常用的 MySQL 引擎的主要特性：InnoDB 和 MyISAM。

我們看到了如何檢查哪些引擎可用、哪些引擎用于表以及如何使用 SQL 查詢設置和修改表引擎。