MySQL/表型別

每個表都是資料庫中的一個邏輯物件；但它也需要在磁碟或記憶體中物理儲存其資料（記錄）。表使用儲存引擎來完成此操作。儲存引擎是可安裝或解除安裝到伺服器中的外掛（如果它們不是內建的）。

伺服器請求許多操作，但實際由儲存引擎完成。因此，我們為表選擇的儲存引擎會影響效能、穩定性、鎖型別、查詢快取的使用、所需的磁碟空間和特殊功能。

在 MySQL 的未來版本中，分割槽表將能夠為不同的分割槽使用不同的儲存引擎。

讓我們看看哪個儲存引擎適合哪些用途。

注意
表型別是 MySQL 最近版本中已棄用的舊術語。它仍然被某些 SQL 命令為了向後相容而接受，但應該優先使用 ENGINE[s] 或 STORAGE ENGINE[s]。

儲存引擎

MyISAM 和 InnoDB

MyISAM 執行表級鎖定，而 InnoDB 執行行級鎖定。除了外部索引鍵之外，InnoDB 還提供事務支援，這在處理大型應用程式時至關重要。速度可能會下降，特別是對於具有完整事務保證的插入操作，因為所有這些外部索引鍵/事務內容都會增加開銷。

MySQL 在 Linux 上的預設表型別是 MyISAM，在 Windows 上通常是 InnoDB。MyISAM 使用表級鎖定，這意味著在 UPDATE 期間，任何人都無法訪問同一表的任何其他記錄。然而，InnoDB 使用行級鎖定。行級鎖定確保在 UPDATE 期間，在鎖定事務發出 COMMIT 之前，任何人都無法訪問該特定行。許多人在需要速度時使用 MyISAM，在需要資料完整性時使用 InnoDB。

MyISAM

優點
- 全文字搜尋目前僅適用於 MyISAM 表
- 幾何資料型別
- 有時讀取速度更快
- 所有數字鍵值都以高位元組優先儲存，以允許更好的索引壓縮
- 支援每個表內部處理一個 AUTO_INCREMENT 列。MyISAM 會自動為 INSERT 和 UPDATE 操作更新此列。這使得 AUTO_INCREMENT 列更快（至少快 10%）

缺點
- 僅表級鎖定（而不是行級鎖定）
- 沒有外部索引鍵約束（但計劃在 MySQL 6.x 中新增）
- 斷電後表檢查和重啟速度較慢，對於那些需要高可用性的人來說這是一個問題

InnoDB

優點
- 為 MySQL 提供了一個事務安全的（符合 ACID）儲存引擎，具有提交、回滾和崩潰恢復功能
- XA 事務
- 外部索引鍵
- 行級鎖定
- 維護自己的緩衝池，用於在主記憶體中快取資料和索引
- 對於某些工作負載更快，特別是那些按主鍵物理排序或自動構建的雜湊索引加速記錄查詢的工作負載
- 即使在檔案大小限制為 2GB 的作業系統上，表也可以是任何大小。
- 斷電後快速可靠的恢復。

缺點
- 儲存資料需要更多空間
- ACID 保證需要在事務提交時完全同步到磁碟，可以在速度比完全 ACID 保證更重要的場合將其關閉。
- 資料版本控制和事務會給表管理增加開銷。
- 它們可能導致高記憶體需求，以管理用於行鎖定的大量鎖。
- 在建立表後新增索引時，索引構建速度很慢。因此，索引應該在資料批次載入時建立。

總的來說，InnoDB 應該用於高度依賴資料完整性或需要事務的應用程式，而 MyISAM 可以用於不需要資料完整性或需要全文字索引的應用程式。如果速度更重要，則應該嘗試兩者，因為哪一個更快取決於應用程式。

Drizzle 是 MySQL 的一個分支，由 Sun Microsystems 支援，它使用 InnoDB 作為其預設引擎，不支援 MyISAM。

合併表

同義詞：Merge，MRG_MYISAM

合併表是相同 MyISAM 表的集合，可以作為一個表使用。
相同意味著所有表都具有相同的列和索引資訊，不允許任何偏差。

CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE delhi  (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE total  (first_name VARCHAR(30), amount INT(10)) TYPE=MERGE UNION=(mumbai,delhi)

合併可以用來解決 MySQL 或系統的檔案大小限制。事實上，這些限制會影響單個 MyISAM 資料檔案，但不會影響整個合併表，因為合併表沒有資料檔案。

在過去，在某些情況下，合併和 MyISAM 可以用來替代檢視，而檢視不受 MySQL 支援。合併可以用作基表，而 MyISAM 表可以用作包含部分基表資料的檢視。對合並表的 SELECT 返回所有有效資料。檢視支援在 MySQL 5.0 中新增，因此這種合併表的使用已經過時。

MEMORY / HEAP

在 MySQL 4.1 之前，此表型別稱為 HEAP。MEMORY 是新的首選名稱。

此引擎是在 3.23 版本中引入的。

BDB

同義詞：BDB，BerkleyDB

BDB 已從 5.1 及更高版本中移除，因為使用率太低。

BerkeleyDB 是一系列由 SleepyCat 開發的免費軟體可嵌入式 DBMS，SleepyCat 是一家已被 Oracle 收購的公司。SleepyCat 為 MySQL 提供了一個名為 BDB 的儲存引擎。

BDB 支援事務和頁面級鎖定，但在 MySQL 中也有許多限制。

BLACKHOLE

丟棄儲存在其中的所有資料，但仍然寫入二進位制日誌，因此它在複製擴充套件或安全的 binlog-do 過濾情況下很有用，其中從伺服器不可靠，以及用於對伺服器更高層的基準測試。

其他

為了完整性，其他儲存引擎包括

CSV：簡單的逗號分隔值引擎，它使用 CSV 格式儲存資料。可能用於與其他支援 CSV 的應用程式共享資料庫？由於其格式簡單，因此無法使用索引。
示例（開發者存根）
ISAM（用於 3.23 之前的向後相容性，在 5.1 中已移除）

關於儲存引擎的元資料

您可以透過 SQL 獲取有關官方 MySQL 儲存引擎和其他儲存引擎（存在於您的伺服器上）的元資料。

SHOW STORAGE ENGINES

從 MySQL 5.0 開始，您可以使用 SHOW STORAGE ENGINES 語句獲取有關可以使用哪些儲存引擎的資訊。

SHOW STORAGE ENGINES

STORAGE 字詞是可選的。此命令返回一個包含以下列的資料集。

Engine - 儲存引擎的名稱。
Support - 儲存引擎是否受支援。可能的數值
- 'DEFAULT' - 它受支援並且是預設引擎；
- 'YES' - 受支援；
- 'DISABLED' - 它已被編譯，但 MySQL 啟動時停用了該引擎（可能透過 --skip-engine-name 等選項）；
- 'NO' - 不受支援。
Comment - 引擎的簡短描述。
Transactions - 引擎是否支援 SQL 事務。在 MySQL 5.1 中新增。
XA - 引擎是否支援 XA 事務。在 MySQL 5.1 中新增。
Savepoints - 引擎是否支援儲存點和回滾。在 MySQL 5.1 中新增。

INFORMATION_SCHEMA `ENGINES` 表

`ENGINES` 是 INFORMATION_SCHEMA 資料庫中的一個虛擬表。它可用於獲取有關儲存引擎的資訊。它的列與 SHOW ENGINES 語句返回的列相同（見上文）。

ENGINES 已在 MySQL 5.1.5 中新增。

HELP 語句

如果您想了解更多關於官方 MySQL 儲存引擎的資訊，可以使用 HELP 命令。

HELP 'myisam'

如果您使用的是命令列客戶端，則可以省略引號。

help myisam \g

更改儲存引擎

SQL

當您想要使用給定的儲存引擎建立表時，可以在 CREATE TABLE 命令中使用 ENGINE 子句。

CREATE TABLE ... ENGINE=InnoDB

如果未指定 ENGINE 子句，將使用 storage_engine 變數的值。預設情況下，它是 MyISAM，但您可以更改它。

SET storage_engine=InnoDB

或者您可以在啟動 MySQL 伺服器之前修改 my.cnf 中的 default-storage-engine 值。

您也可以更改現有表的儲存引擎。

ALTER TABLE `stats` ENGINE=MyISAM

mysql_convert_table_format

mysql_convert_table_format 是 MySQL 提供的一個工具，用 Perl 編寫。它將指定資料庫中包含的所有錶轉換為另一個儲存引擎。

語法是

mysql_convert_table_format [options] database

database 是程式將在其中執行的資料庫的名稱。它是強制性的。

選項是

--help 列印幫助資訊並退出。

--version 列印版本號並退出。

--host=host MySQL 執行所在的宿主機。預設值：localhost。

--port=port TCP 埠。

--user=user 指定使用者名稱。

--password=password 指定密碼。由於它不安全（例如，它在 coomand top 中可見），因此您可以使用選項檔案代替。

--type=storage_engine 錶轉換後將使用的儲存引擎。

--force 如果發生錯誤，不要停止執行。

--verbose 列印關於轉換的詳細資訊。

示例

mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test

此命令指定訪問資料（localhost、使用者名稱、密碼）並將 `test` 資料庫中的所有錶轉換為 InnoDB。如果某些表無法轉換，指令碼將跳過它們並轉換其他表（--force）。 *斜體文字*