MySQL 儲存引擎

標籤：insert   完全   com   刪除表   動作記錄   car   完成後   varchar   sel   

　　在MySQL中有一個儲存引擎的概念，針對不同的儲存需求可以選擇最優的儲存引擎。

一、概述

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

　　MySQL5.5之前的預設儲存是MyISAM，5.5之後是InnoDB。如果要修改預設的儲存引擎，可以在參數檔案中設定default-table-type。在建立表時，可以通過增加engine關鍵字設定建立表的儲存引擎，例如：表auth_type的儲存引擎是MyISAM，表auth_type的儲存引擎是InnoDB。

CREATE TABLE `auth_type` (
  　　`id` int(11) NOT NULL,
  　　`type_code` char(3) DEFAULT NULL COMMENT ‘授與類型編號‘,
 　　 `type_name` varchar(255) DEFAULT NULL COMMENT ‘授與類型名稱‘,
 　　 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `auth_user` (
  　　`id` int(11) NOT NULL,
 　　 `user_name` varchar(30) DEFAULT NULL COMMENT ‘授權使用者名稱‘,
  　　`token` char(64) DEFAULT NULL COMMENT ‘訪問授權token‘,
  　　`created_at` timestamp NULL DEFAULT NULL COMMENT ‘記錄建立時間‘,
  　　`updated_at` timestamp NULL DEFAULT NULL COMMENT ‘記錄最後修改時間‘,
  　　`deleted_at` timestamp NULL DEFAULT NULL COMMENT ‘記錄刪除時間‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘授權使用者表‘;

也可以使用ALERT TABLE語句，將一個已經存在的表改成其他的儲存引擎。alert table auth_type engine = InnoDB

二、各種儲存引擎特性

　　先看下錶各個儲存引擎之間的區別

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

　　下面將介紹最常用的4種儲存引擎：MyISAM、InnoDB、Memory和Merge。

1、MyISAM

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

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

　　.frm（儲存表定義）

　　.MYD（MYData，儲存資料）

　　.MYI（MYIndex，儲存索引）

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

　　在建立表的時候通過DATA DIRECTORY和INDEX DIRECTORY語句指定資料檔案和索引檔案的路徑，該路徑需要絕對路徑，並且具有存取權限。

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

CHECK TABLE 語句用於檢查表的健康，REPAIR TABLE 語句用來修複一個損壞的表。表的損壞還可能導致資料庫的異常啟動，需要儘快修複並儘可能地確認損壞的原因。

　　MyISAM的表還支援3種不同的儲存格式，分別是：靜態（固定長度）表、動態表、壓縮表。

　　靜態表：表中的欄位是非變長欄位，這樣每個欄位都是固定長度，如char(20)。這種儲存的優點是：儲存非常迅速，容易緩衝，出現故障容易恢複；缺點是：佔用的空間通常比動態表多。靜態表的資料存放區時會按給定的列寬度定義補足空格，但是應用訪問時並不會得到這些空格。

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

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

2、InnoDB

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

　　InnoDB儲存引擎的特點：

　　2.1、自動成長列

　　InnoDB表的自動成長可以手動插入，但是插入的值如果是空或者是0，則實際插入的將是自動成長後的值。例如：下表auth_user中，列id為自動成長列，通過AUTO_INCREMENT指定。

　　CREATE TABLE `auth_user` (
 　　　　 `id` int(11) NOT NULL AUTO_INCREMENT,
  　　　　`user_name` varchar(30) DEFAULT NULL COMMENT ‘授權使用者名稱‘,
  　　　　`token` char(64) DEFAULT NULL COMMENT ‘訪問授權token‘,
  　　　　`created_at` timestamp NULL DEFAULT NULL COMMENT ‘記錄建立時間‘,
  　　　　`updated_at` timestamp NULL DEFAULT NULL COMMENT ‘記錄最後修改時間‘,
  　　　　`deleted_at` timestamp NULL DEFAULT NULL COMMENT ‘記錄刪除時間‘,
  　　　　PRIMARY KEY (`id`)
　　　) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘授權使用者表‘;
　　還可通過“ALTER TABLE auth_user AUTO_INCREMENT = n;”語句強制設定自動成長的初始值，預設從1開始，但是該強制的預設值是保留在記憶體中的，如果該值在使用之前資料庫重新啟動，那麼這個強制的預設值就會丟失，就需要在資料庫啟動後重新設定。
　　可以會用LAST_INSERT_ID()查詢當前線程最後插入記錄使用的值。如果一次插入多條記錄，那麼返回的是第一條記錄使用的自動成長值。

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

　　CREATE TABLE `autoincre_demo` (
 　　　　 `id1` int(11) NOT NULL AUTO_INCREMENT,

　　　　  `id2` int(11) NOT NULL,
  　　　　`name` varchar(30) DEFAULT NULL COMMENT ‘名稱‘
  　　　　index (`id1`,`id2`)
　　　) ENGINE=MyISAM;

　　insert into `autoincre_demo`(`id2`,‘name‘) values(2,‘2‘),(3,‘3‘),(4,‘4‘);

　　結果是：select * from `autoincre_demo`;

　　

id1	id2	name
1	2	2
1	3	3
1	4	4
2	2	2
2	3	3
2	4	4

　　2.2、外鍵約束

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

　　在建立索引時，可以指定在刪除、更新附表時，對子表的相應操作，包括restrict、cascade、set null和no action。

　　　　restrict 和 no action相同，是指限制在子表有關聯記錄時父表不能更新。

　　　　cascade是指，父表在更新或刪除時，更新或者刪除子表對應記錄。

　　　　set null是指，父表在更新或者刪除時，子表對應的欄位被set null。

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

　　在匯入多個表的資料時，如果需要忽略表之前的匯入順序，可以暫時關閉外鍵的檢查；同樣，在執行LOAD DATA和ALTER TABLE操作的時候，可以通過暫時關閉外鍵約束來加快處理的速度，關閉的命令是“SET FOREING_KEY_CHECKS = 0;”，執行完成後，通過執行“SET FOREING_KEY_CHECKS = 1;”語句返回原狀。

　　2.3、儲存方式

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

　　使用共用資料表空間儲存，這種方式建立的表的表結構儲存在.frm檔案中，資料和索引儲存在innod_data_home_dir和innod_data_file_path定義的資料表空間中，可以是多個檔案。

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

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

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

　　對於使用多資料表空間特性的表，可以比較方便的進行單表備份和恢複操作，但是直接複製.ibd檔案是不行的，因為沒有共用表的資料字典資訊，直接複製的.ibd檔案和.frm檔案恢複時是不能被識別的，但是可以通過以下命令：

　　ALTER TABLE tbl_name DISCARD TABLESPACE;

　　ALTER TABLE tbl_name IMPORT TABLESPACE;

　　將備份恢複到資料庫，但是這隻能恢複到表原來所在的資料庫中，不能恢複到其他的資料庫中。可以通過mysqldump和mysqlimport來將單表恢複到目標資料庫。

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

3、Memory

　　Memory儲存引擎使用存在於記憶體中的內容來建立表。每個Memory表只實際上對應一個磁碟檔案，格式是.frm。Memory類型的表訪問速度非常快，因為它的資料是放在記憶體中的，並且預設使用HASH索引，但是一旦服務關閉，表中的資料就會丟失。

　　在啟動MySQL服務的時候使用--init--file選項，把INSERT INTO ... SELECT 或LOAD DATA INFILE 這樣的語句放入這個檔案中，就可以在服務啟動時從持久穩固的資料來源裝載表。

　　伺服器需要足夠記憶體來維持所有在同一時間使用的Memory表，當不再需要Memory表的內容之時，要釋放被Memory表使用的記憶體，應該執行DELETE FROM 或 TRUNCATE TABLE，或者整個的刪除表（使用DROP TABLE操作）。

　　每個Memory表中可以放置的資料量大小，受到max_heap_table_size系統變數的約束，這個系統變數的初始值是16MB，可以根據需要加大。此外，在定義Memory表的時候，可以通過MAX_ROWS子句指定表的最大行數。

　　Memory類型的儲存引擎主要應用於那些語句變化不頻繁的代碼錶，或者作為統計操作的中間結果表，便於高效地對中間結果進行分析並得到最終的統計結果。對儲存引擎為Memory的表進行更新操作要謹慎，因為資料並沒有實際寫入到磁碟中，所以一定要對下次重啟服務後如何獲得這些修改的資料有所考慮。

4、Merge

　　Merge儲存引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同，Merge表本身並沒有資料，對Merge類型的表可以進行查詢、更新、刪除，這些操作實際上是對內部的MyISAM表進行的。對於Merge類型表的插入操作，是通過INSERT_METHOD子句定義插入的表，可以有3個不同的值，使用FIRST或者LAST值使得插入操作被相應地作用在第一個或者最後一個表上，不定義這個子句或者定義為NO，表示不能對這個Merger表進行操作。

　　可以對Merge表進行DROP操作，這個操作只是刪除Merge的定義，對內部的表沒有任何的影響。

　　在磁碟上保留兩個檔案，檔案名稱以表的名字開始，一個.frm檔案儲存體表定義，另一個.mrg檔案包含組合表的資訊，包含Merge表由哪些表組成、插入新資料的依據。可以通過修改.mrg檔案來修改Merge表，但是修改後要通過flush tables重新整理。

　　Merger表和分區表的區別是，Merger表並不能智能地將記錄寫到對應的表中，而是寫入到使用FIRST或者LAST值指定的表中。但是分區表可以。通常我們使用Merger表來透明地對多個表進行查詢和更新操作，而這種按照時間記錄的動作記錄表則可以透明地進行插入操作。

三、如何選擇合適的儲存引擎四、小結

MySQL 儲存引擎