MySQL 儲存引擎

標籤：開發人員   path   單表備份   pack   comment   環境   myisam   card   外鍵   

前言

• 和大多數資料庫不同，MySQL 中有一個儲存引擎的概念，針對不同的儲存需求可以選擇最優的儲存引擎。

1、MySQL 儲存引擎概述

• 外掛程式式儲存引擎是 MySQL 資料庫最重要的特性之一，使用者可以根據應用的需要選擇如何儲存和索引資料、是否使用事務等。MySQL 預設支援多種儲存引擎，以適用於不同領域的資料庫應用需要，使用者可以通過選擇使用不同的儲存引擎提高應用的效率，提供靈活的儲存，使用者甚至可以按照自己的需要定製和使用自己的儲存引擎，以實現最大程度的可定製性。

• MySQL 5.0 支援的儲存引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事務安全表，其他儲存引擎都是非事務安全表。

• 建立新表時如果不指定儲存引擎，那麼系統就會使用預設儲存引擎，MySQL 5.5 之前的預設儲存引擎是 MyISAM，5.5 之後改為了 InnoDB。

1.1 常用儲存引擎對比

• 常用儲存引擎對比

  特點	MyISAM	InnoDB	MEMORY	MERGE	NDB
  儲存限制	有	64 TB	有	沒有	有
  事物安全		支援
  鎖機制	表鎖	行鎖	表鎖	表鎖	行鎖
  B 樹索引	支援	支援	支援	支援	支援
  雜湊索引			支援		支援
  全文索引	支援
  叢集索引		支援
  資料緩衝		支援	支援		支援
  索引緩衝	支援	支援	支援	支援	支援
  資料可壓縮	支援
  空間使用	低	高	N/A	低	低
  記憶體使用量	低	高	中等	低	高
  批量插入的速度	高	低	高	高	高
  支援外鍵		支援

1.2 MySQL 儲存引擎設定

• 1）查詢當前資料庫支援的儲存引擎

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

  > show variables like 'have%';    +------------------------+----------+    | Variable_name          | Value    |    +------------------------+----------+    | have_compress          | YES      |    | have_dynamic_loading   | YES      |    | have_geometry          | YES      |    | have_openssl           | YES      |    | have_profiling         | YES      |    | have_query_cache       | NO       |    | have_rtree_keys        | YES      |    | have_ssl               | YES      |    | have_statement_timeout | YES      |    | have_symlink           | DISABLED |    +------------------------+----------+    10 rows in set (0.00 sec)

• 2）查看當前預設儲存引擎

  > show variables like 'table_type';    +---------------+--------+    | Variable_name | Value  |    +---------------+--------+    | table_type    | InnoDB |    +---------------+--------+    1 row in set (0.00 sec)

• 3）設定建立表的儲存引擎

  # create table 表名 (      列名稱欄位 資料類型(長度) 約束條件,      列名稱欄位 資料類型(長度) 約束條件  ) engine = 儲存引擎名;> create table ai (   i bigint(20) not null auto_increment,   primary key(i)  ) engine = MyISAM;    Query OK, 0 rows affected (0.08 sec)

• 4）修改已有表的儲存引擎

  # 修改已有表的儲存引擎# alter table 表名 engine = 儲存引擎名;> alter table ai engine = InnoDB;    Query OK, 0 rows affected (0.09 sec)    Records: 0  Duplicates: 0  Warnings: 0

• 5）顯示表的儲存引擎

  # 顯示表的儲存引擎# show create table 表名;> show create table ai;    +-------+-----------------------------------------------------------------------------------------+    | Table | Create Table                                                                            |    +-------+-----------------------------------------------------------------------------------------+    | ai    | CREATE TABLE `ai` (                                  `i` bigint(20) NOT NULL AUTO_INCREMENT,                                  PRIMARY KEY (`i`)                                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    |    +-------+-----------------------------------------------------------------------------------------+    1 row in set (0.01 sec)

2、MyISAM 儲存引擎

• MyISAM 是 MySQL 預設的儲存引擎。MyISAM 不支援事務、也不支援外鍵，其優勢是訪問的速度快，對事務完整性沒有要求或者以 SELECT、INSERT 為主的應用基本上都可以使用這個引擎來建立表。

• 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並發性要求不是很高，那麼選擇這個儲存引擎是非常適合的。MyISAM 是在 Web、資料倉儲和其他應用環境下最常使用的儲存引擎之一。

2.1 儲存方式

• 每個 MyISAM 在磁碟上儲存成 3 個檔案，其檔案名稱都和表名相同，但副檔名分別是：

  • .frm（儲存表定義）
  • .MYD（MYData，儲存資料）
  • .MYI（MYIndex，儲存索引）

• 資料檔案和索引檔案可以放置在不同的目錄，平均分布 IO，獲得更快的速度。

• 要指定索引檔案和資料檔案的路徑，需要在建立表的時候通過 DATA DIRECTORY 和 INDEX DIRECTORY 語句指定，也就是說不同 MyISAM 表的索引檔案和資料檔案可以放置到不同的路徑下。檔案路徑需要是絕對路徑，並且具有存取權限。

• MyISAM 類型的表可能會損壞，原因可能是多種多樣的，損壞後的表可能不能被訪問，會提示需要修複或者訪問後返回錯誤的結果。

• MyISAM 類型的表提供修複的工具，可以用 CHECK TABLE 語句來檢查 MyISAM 表的健康，並用 REPAIR TABLE 語句修複一個損壞的 MyISAM 表。

• 表損壞可能導致資料庫異常重新啟動，需要儘快修複並儘可能地確認損壞的原因。

2.2 儲存格式

• MyISAM 的表還支援 3 種不同的儲存格式，分別是：

  • 靜態（固定長度）表
  • 動態表
  • 壓縮表

• 其中，靜態表是預設的儲存格式。靜態表中的欄位都是非變長欄位，這樣每個記錄都是固定長度的，這種儲存方式的優點是儲存非常迅速，容易緩衝，出現故障容易恢複；缺點是佔用的空間通常比動態表多。靜態表的資料在儲存時會按照列的寬度定義補足空格，但是在應用訪問的時候並不會得到這些空格，這些空格在返回給應用之前已經去掉。

  • 但是也有些需要特別注意的問題，如果需要儲存的內容後面本來就帶有空格，那麼在返回結果的時候也會被去掉，開發人員在編寫程式的時候需要特別注意，因為靜態表是預設的儲存格式，開發人員可能並沒有意識到這一點，從而丟失了尾部的空格。

• 動態表中包含變長欄位，記錄不是固定長度的，這樣儲存的優點是佔用的空間相對較少，但是頻繁地更新和刪除記錄會產生片段，需要定期執行 OPTIMIZE TABLE 語句或 myisamchk-r 命令來改善效能，並且在出現故障時恢複相對比較困難。

• 壓縮表由 myisampack 工具建立，佔據非常小的磁碟空間。因為每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。

3、InnoDB 儲存引擎

• InnoDB 儲存引擎提供了具有提交、復原和崩潰恢複能力的事務安全。但是對比 MyISAM 的儲存引擎，InnoDB 寫的處理效率差一些，並且會佔用更多的磁碟空間以保留資料和索引。

• 用於交易處理應用程式，支援外鍵。如果應用對事務的完整性有比較高的要求，在並發條件下要求資料的一致性，資料操作除了插入和查詢以外，還包括很多的更新、刪除操作，那麼 InnoDB 儲存引擎應該是比較合適的選擇。InnoDB 儲存引擎除了有效地降低由於刪除和更新導致的鎖定，還可以確保事務的完整提交（Commit）和復原（Rollback），對於類似計費系統或者財務系統等對資料準確性要求比較高的系統，InnoDB 都是合適的選擇。

3.1 自動成長列

• 1）InnoDB 表的自動成長列可以手工插入，但是插入的值如果是空或者 0，則實際插入的將是自動成長後的值。

  > create table autoincre_demo (    i smallint not null auto_increment,     name varchar(10), primary key(i)  ) engine = innodb;    Query OK, 0 rows affected (0.04 sec)> insert into autoincre_demo values (1, '1'), (0, '2'), (null, '3');    Query OK, 3 rows affected (0.09 sec)    Records: 3  Duplicates: 0  Warnings: 0> select * from autoincre_demo;    +---+------+    | i | name |    +---+------+    | 1 | 1    |    | 2 | 2    |    | 3 | 3    |    +---+------+    3 rows in set (0.00 sec)

• 2）可以通過 alter table *** auto_increment = n; 語句強制設定自動成長列的初始值。

  • 預設從 1 開始。
  • 該強制的預設值是保留在記憶體中的，如果該值在使用之前資料庫重新啟動，那麼這個強制的預設值就會丟失，就需要在資料庫啟動以後重新設定。

• 3）可以使用 last_insert_id() 查詢當前線程最後插入記錄使用的值。如果一次插入了多條記錄，那麼返回的是第一條記錄使用的自動成長值。

  > insert into autoincre_demo values(4, '4');    Query OK, 1 row affected (0.05 sec)> select last_insert_id();    +------------------+    | last_insert_id() |    +------------------+    |                2 |    +------------------+    1 row in set (0.01 sec)> insert into autoincre_demo (name) values ('5'), ('6'), ('7');    Query OK, 3 rows affected (0.01 sec)    Records: 3  Duplicates: 0  Warnings: 0> select last_insert_id();    +------------------+    | last_insert_id() |    +------------------+    |                5 |    +------------------+    1 row in set (0.00 sec)

• 4）對於 InnoDB 表，自動成長列必須是索引。如果是複合式索引，也必須是複合式索引的第一列，但是對於 MyISAM 表，自動成長列可以是複合式索引的其他列，這樣插入記錄後，自動成長列是按照複合式索引的前面幾列進行排序後遞增的。

  > create table autoincre_demo (    d1 smallint not null auto_increment,     d2 smallint not null,     name varchar(10),     index(d2, d1)  ) engine = myisam;     Query OK, 0 rows affected (0.07 sec)> insert into     autoincre_demo     (d2, name)   values     (2, '2'), (3, '3'), (4, '4'), (2, '2'), (3, '3'), (4, '4');    Query OK, 6 rows affected (0.01 sec)    Records: 6  Duplicates: 0  Warnings: 0> select * from autoincre_demo;    +----+----+------+    | d1 | d2 | name |    +----+----+------+    |  1 |  2 | 2    |    |  1 |  3 | 3    |    |  1 |  4 | 4    |    |  2 |  2 | 2    |    |  2 |  3 | 3    |    |  2 |  4 | 4    |    +----+----+------+    6 rows in set (0.00 sec)

  • 建立一個新的 MyISAM 類型的表 autoincre_demo，自動成長列 d1 作為複合式索引的第二列，對該表插入一些記錄後，可以發現自動成長列是按照複合式索引的第一列 d2 進行排序後遞增的。

3.2 外鍵約束

• 1）MySQL 支援外鍵的儲存引擎只有 InnoDB，在建立外鍵的時候，要求父表必須有對應的索引，子表在建立外鍵的時候也會自動建立對應的索引。

  > create table country (    country_id smallint unsigned not null auto_increment,     country varchar(50) not null,     last_update timestamp not null default current_timestamp on update current_timestamp,     primary key (country_id)  ) engine = InnoDB default charset = utf8;    Query OK, 0 rows affected, 1 warning (0.05 sec)> create table city (    city_id smallint unsigned not null auto_increment,     city varchar(50) not null,     country_id smallint unsigned not null,     last_update timestamp not null default current_timestamp on update current_timestamp,     primary key(city_id),     key idx_fk_country_id(country_id),    # constraint 約束名稱 foreign key(外鍵名稱) references 源表(主鍵名稱)    constraint fk_city_country foreign key(country_id) references country(country_id) on delete restrict on update cascade   ) engine = InnoDB default charset = utf8;    Query OK, 0 rows affected, 1 warning (0.07 sec)

  • country 表是父表，country_id 為主鍵索引，city 表是子表，country_id 欄位為外鍵，對應於 country 表的主鍵 country_id。

• 2）在建立索引時，可以指定在刪除、更新父表時，對子表進行的相應操作，包括 restrict、cascade、set null 和 no action。選擇後兩種方式的時候要謹慎，可能會因為錯誤的操作導致資料的丟失。

  • restrict 和 no action 相同，是指限制在子表有關聯記錄的情況下父表不能更新；
  • cascade 表示父表在更新或者刪除時，更新或者刪除子表對應記錄；
  • set null 則表示父表在更新或者刪除的時候，子表的對應欄位被 set nullL。

  > select * from country where country_id = 1;    +------------+-------------+---------------------+    | country_id | country     | last_update         |    +------------+-------------+---------------------+    |          1 | Afghanistan | 2006-02-15 04:44:00 |    +------------+-------------+---------------------+    1 row in set (0.00 sec)> select * from city where country_id = 1;    +---------+-------+------------+---------------------+    | city_id | city  | country_id | last_update         |    +---------+-------+------------+---------------------+    |     251 | Kabul |          1 | 2006-02-15 04:45:25 |    +---------+-------+------------+---------------------+    1 row in set (0.00 sec)> delete from country where country_id=1;    ERROR 1451 (23000): Cannot delete or update a parent row:     a foreign key constraint fails ('sakila/city', CONSTRAINT 'fk_city_country'     FOREIGN KEY ('country_id') REFERENCES 'country' ('country_id') ON UPDATE CASCADE)> update country set country_id = 10000 where country_id = 1;    Query OK, 1 row affected (0.04 sec)    Rows matched: 1 Changed: 1 Warnings: 0> select * from country where country = 'Afghanistan';    +------------+-------------+---------------------+    | country_id | country     | last_update         |    +------------+-------------+---------------------+    |      10000 | Afghanistan | 2007-07-17 09:45:23 |    +------------+-------------+---------------------+    1 row in set (0.00 sec)> select * from city where city_id = 251;    +---------+-------+------------+---------------------+    | city_id | city  | country_id | last_update         |    +---------+-------+------------+---------------------+    |     251 | Kabul |      10000 | 2006-02-15 04:45:25 |    +---------+-------+------------+---------------------+    1 row in set (0.00 sec)

  • 當某個表被其他表建立了外鍵參照，那麼該表的對應索引或者主鍵禁止被刪除。

• 3）在匯入多個表的資料時，如果需要忽略表之前的匯入順序，可以暫時關閉外鍵的檢查；同樣，在執行 load data 和 alter table 操作的時候，可以通過暫時關閉外鍵約束來加快處理的速度，關閉的命令是 set foreign_key_checks = 0;，執行完成之後，通過執行 set foreign_key_checks = 1; 語句改回原狀態。

• 4）對於 InnoDB 類型的表，外鍵的資訊通過使用 show create table 或者 show table status 命令都可以顯示。

  # show create table 表名;> show create table city;    +-------+--------------------------------------------------------------------------------------------------------------------------------------+    | Table | Create Table                                                                                                                         |    +-------+--------------------------------------------------------------------------------------------------------------------------------------+    | city  | CREATE TABLE `city` (                                    `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,                                    `city` varchar(50) NOT NULL,                                    `country_id` smallint(5) unsigned NOT NULL,                                    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,                                    PRIMARY KEY (`city_id`),                                    KEY `idx_fk_country_id` (`country_id`),                                    CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE                                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8                                                                             |    +-------+--------------------------------------------------------------------------------------------------------------------------------------+    1 row in set (0.00 sec)# show table status like 表名;> show table status like 'city';    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+    | city | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 |    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+     ---------------------+-------------+------------+-----------------+----------+----------------+---------+      Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |     ---------------------+-------------+------------+-----------------+----------+----------------+---------+      2018-06-21 18:58:55 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |     ---------------------+-------------+------------+-----------------+----------+----------------+---------+    1 row in set (0.06 sec)

3.3 儲存方式

• InnoDB 儲存表和索引有以下兩種方式。

  • 使用共用資料表空間儲存，這種方式建立的表的表結構儲存在 .frm 檔案中，資料和索引儲存在 innodb_data_home_dir 和 innodb_data_file_path 定義的資料表空間中，可以是多個檔案。
  • 使用多資料表空間儲存，這種方式建立的表的表結構仍然儲存在 .frm 檔案中，但是每個表的資料和索引單獨儲存在 .ibd 中。如果是個分區表，則每個分區對應單獨的 .ibd 檔案，檔案名稱是 “表名+分區名”，可以在建立分區的時候指定每個分區的資料檔案的位置，以此來將表的 IO 均勻分布在多個磁碟上。

• 要使用多資料表空間的儲存方式，需要設定參數 innodb_file_per_table，並且重新啟動服務後才可以生效，對於建立的表按照多資料表空間的方式建立，已有的表仍然使用共用資料表空間儲存。如果將已有的多資料表空間方式修改回共用資料表空間的方式，則建立表會在共用資料表空間中建立，但已有的多資料表空間的表仍然儲存原來的訪問方式。所以多資料表空間的參數生效後，只對建立的表生效。

• 多資料表空間的資料檔案沒有大小限制，不需要設定初始大小，也不需要設定檔案的最大限制、擴充大小等參數。

• 對於使用多資料表空間特性的表，可以比較方便地進行單表備份和恢複操作，但是直接複製 .ibd 檔案是不行的，因為沒有共用資料表空間的資料字典資訊，直接複製的 .ibd 檔案和 .frm 檔案恢複時是不能被正確識別的，但可以通過以下命令將備份恢複到資料庫中，但是這樣的單表備份，只能恢複到表原來所在的資料庫中，而不能恢複到其他的資料庫中。如果要將單表恢複到目標資料庫，則需要通過 mysqldump 和 mysqlimport 來實現。

  alter table tbl_name discard tablespace;alter table tbl_name import tablespace;

• 即便在多資料表空間的儲存方式下，共用資料表空間仍然是必須的，InnoDB 把內部資料詞典和線上重做日誌放在這個檔案中。

MySQL 儲存引擎