mysql引擎筆記整理

檢視當前的mysql資料庫都支援什麼樣的儲存引擎：
mysql> show engines;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    170700
Current database: *** NONE ***

注：
1、support的狀態有default，yes，no，disabled。no表示該引擎不可用，disabled表示支援該引擎，但是當前被禁用。
2、Transactions表示是否支援事務。
3、XA表示是否支援分散式事務。
4、Savepoints表示是否支援儲存點。

mysql中自帶的也比較常見的儲存引擎有：MyISAM，Innodb，NDB Cluster，Memory，Archive，Merge，Federated等。其中，最著名和廣泛的就是MyISAM，Innodb。

1) MEMORY引擎
MEMORY儲存引擎的表只擁有一個獨立的磁碟檔案，副檔名為.frm，用來儲存表結構的定義，不包含資料。
MEMORY引擎表的資料儲存在記憶體中，mysql服務端需要單獨為其分配記憶體區域。分配給它的記憶體在正常狀態下不會釋放，會一直被持有。除非整個MEMORY表被刪除或重建才會釋放相關記憶體。
如果僅刪除MEMORY引擎表中的資料，釋放的記憶體會被該表新插入的資料所使用，無法被其他物件或者執行緒使用。
當mysql服務關閉時，MEMORY引擎表中的資料都會丟失，表結構會予以保留。
MEMORY引擎表佔用的最大記憶體由引數max_heap_table_size系統變數來決定。該值預設識16M。可以為不同的MEMORY引擎表設定不同的最大記憶體。但是mysql服務重啟後，所有的MEMORY引擎表的最大記憶體又會被系統變數的值覆蓋掉。
---------設定最大記憶體為1M；----------
set max_heap_table_size=1024*1024;
create tabele test1(user varchar(10)) engine=memory;
---------設定最大記憶體為2M；----------
set max_heap_table_size=1024*1024*2;
create tabele test1(user varchar(10)) engine=memory;
除此之外，還可以在建立MEMORY引擎表時指定max_rows項的值來限制MEMORY引擎表所使用的記憶體。

2）csv儲存引擎
csv儲存引擎是基於csv格式檔案儲存資料的。
csv儲存引擎表的所有列必須強制指定NOT NULL，而且csv儲存引擎表不支援索引，不支援分割槽。
csv儲存引擎表包含一個表的結構定義檔案，副檔名為.frm，以及一個副檔名為.CSV的資料檔案，還有一個同名的元資訊檔案，副檔名為.CSM，用來儲存表狀態和表中儲存的資料量。
.CSV的資料檔案就是CSV格式的平面文字檔案，可以用excel開啟。
如果.CSV檔案中的內容被損壞，可以用CHECK TABLE或者REPAIR TABLE命令來進行檢查和恢復。
CHECK TABLE命令用於檢查檔案中的每一行是否合法，如果又發現不合法的行，就丟擲異常。
REPAIR TABLE命令用來修復檔案，將檔案中損壞的資料清除掉。注意它會將出現損壞行之後的所有記錄行都清掉，而不管後面的記錄行是否有合法的資料。

3）ARCHIVE儲存引擎
基於ARCHIVE儲存引擎的物件，能夠將大量資料壓縮儲存，它使用了zlib無損資料壓縮演算法，並且還可以使用OPTIMIZE TABLE分析表使其打包成更小的格式。
ARCHIVE儲存引擎的特點就是插入效率高，資料儲存時佔空間小。
ARCHIVE儲存引擎目前僅能支援insert和select語句，不支援update，delete，replace語句。支援order by操作，BLOB列等常規列，可以使用行級鎖定，但不支援索引。
ARCHIVE儲存引擎擁有.frm結構定義檔案，以及副檔名為.arz的資料檔案。在執行最佳化操作時可能還會出現一個副檔名為.arn的檔案

4）BLACKHOLE引擎
BLACKHOLE引擎雖然可以像其他引擎一樣接受資料，但是資料並不會儲存。BLACKHOLE引擎表中永遠為空。但是，該引擎表支援各種索引。
儘管BLACKHOLE引擎表中不存放任何資料，但是如果啟用了binlog，那麼執行的sql語句實際上是會被記錄的。
BLACKHOLE引擎表只有一個.frm格式的檔案，用於儲存表定義。

5）MRG_MYISAM儲存引擎
又稱為MERGE儲存引擎，它是將一組MyISAM表聚合在一起，使用時就像一張表一樣，以此來簡化查詢操作。
MRG_MYISAM儲存引擎表，要求基表擁有相同的列和索引資訊，並且列的定義和順序，索引的順序都必須一模一樣。否則是無法聚合到一起的。
MRG_MYISAM儲存引擎表本身並不儲存資料，只是一個彙總作用，但是它除了支援查詢操作外，也支援插入，修改，刪除操作。操作方式和普通的MyISAM表一樣。
只是插入的時候，需要一些配置，以指明往哪張MyISAM表中插入資料。這個配置就是建立MRG_MYISAM儲存引擎表時的INSERT_METHOD選項。
INSERT_METHOD選項有三個值，分別是NO，FIRST，LAST。NO表示不允許插入，是預設值。FIRST是插入第一張表。LAST是插入最後一張表。

6）FEDERATED儲存引擎
類似於oracle中的database link。預設mysql安裝時不安裝FEDERATED儲存引擎的，如果需要使用該引擎，在編譯安裝時，新增引數-DWITH_FEDERATED_STORAGE_ENGINE。並且在啟動mysql服務時，
附加引數--FEDERATED。或者修改my.cnf配置檔案，加上FEDERATED一行，重啟mysql服務。
FEDERATED儲存引擎表，只有表結構，其物理資料是來自於遠端的mysql伺服器。每個FEDERATED儲存引擎表都包含了兩個元素：一個遠端的mysql資料庫表，一個本地的資料庫表。
FEDERATED儲存引擎表在建立時，需要透過CONNECTION選項指定連線資訊。CONNECTION選項有兩種寫法：一種透過字串，一種是透過CREATE SERVER語句。透過CREATE SERVER語句建立的server資訊都會被儲存在mysql.servers表中。

7）MyISAM儲存引擎
每個MyISAM表物件都是由3個獨立的檔案組成，副檔名分別是.frm，.MYD，.MYI。.frm檔案用於儲存表物件的結構，.MYD用於儲存表資料，.MYI用於儲存表的索引資訊。
在MyISAM引擎表中，不管包含多少個CHAR/VARCHAR型別列，這些列的長度加起來也不能超過65535B，單表字元列最大長度不能超過65532B。
MyISAM引擎的主要優點是快，查詢快，寫入快。但是缺點也比較明顯，不支援事務，鎖粒度太粗（表級鎖），在OLTP場景下不合適。
在儲存資料時，MyISAM引擎表支援三種儲存格式：FIXED（定長或稱靜態），DYNAMIC（動態），COMPRESSED（壓縮）。
靜態格式表就是表中不含變長度的列，比如varchar/varbinary/blob/text等，所定義的每一列儲存的均是固定的位元組數。或者建立時指定ROW_FORMAT=FIXED。優點：簡單，查詢資料最快，崩潰後最好恢復。缺點：費空間。
動態格式表就是和靜態格式表相反的，或者建立時指定了ROW_FORMAT=DYNAMIC。除了字串長度小於4的列以外，其他字元列的長度都是動態的。優點：省空間。缺點：查詢相對較慢，儲存空間碎片會比較多。崩潰後恢復操作比較麻煩。
（myisamchk工具：OPTIMIZE TABLE或者myisamchk -r命令用來消除碎片；myisamchk -ei用來查詢表的統計資訊；myisamchk -ed命令用來查詢表物件的連結數。）
壓縮格式表：建立只能用myisampack建立，解壓縮用myisamchk命令。壓縮表只能讀，不能新增或修改記錄。

8）InnoDB儲存引擎
InnoDB擁有自己獨立的快取池，對應引數是innodb_buffer_pool_size系統變數，類似於oracle資料庫中的SGA_TARGET，常用資料包括索引都在快取中。
鎖粒度為行級，提供一致性讀。支援事務。支援外來鍵約束。支援資料加密。支援查詢、資料、索引快取。支援備份/恢復到時間點。
InnoDB儲存引擎表必須建立主鍵，一般選擇將經常需要查詢的列作為主鍵，如果沒有合適的列，就將主鍵創造到自動增長的列。
InnoDB能夠處理多會話併發讀寫同一個物件。
InnoDB有兩種表空間方式：系統表空間和多重表空間。
InnoDB系統表空間對應哪些物理檔案，是由系統變數innodb_data_file_path來設定的：
mysql> show variables like "%innodb_data%";
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)
預設情況下，InnoDB資料檔案時是存放在mysql的data目錄中，如果想變更目錄，可以透過系統變數innodb_data_home_dir來設定。
啟動innodb_file_per_table選項，以啟用多重表空間，使表中資料和索引儲存在單獨的檔案中，而不是儲存在系統表空間中。
設定為多重表空間時，truncate儲存引擎為InnoDB的錶速度很快，並且釋放的空間能夠被作業系統使用。
mysql5.6中，innodb_file_per_table選項預設是為啟動的。
mysql> show variables like "%innodb_file%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+
4 rows in set (0.00 sec)

InnoDB除了有表空間概念，還有自己專用的日誌檔案，即REDOLOG日誌。
預設情況下，InnoDB引擎會建立兩組大小均為5M的日誌檔案，分別名為ib_logfile0和ib_logfile1。
關於InnoDB引擎日誌的系統引數有以下幾個：innodb_log_group_home_dir redo log的儲存路徑，預設是在datadir變數指定的路徑下
    innodb_log_file_size  每一個redo日誌的大小，最大不能超過512G
    innodb_log_files_in_group  指定日誌檔案組的數量，預設是兩個，最多不超過100個
調整InnoDB引擎日誌比較複雜，因為這幾個引數均是靜態引數，並且調整它們還要依賴引數innodb_fast_shutdown。                                     
innodb_fast_shutdown引數用來控制InnoDB的關閉模式，有三個值：
    0  0模式類似於oracle的shutdown normal
    1  1模式類似於oracle的shutdown immediate，預設值是1
    2  2模式類似於oracle的shutdown abort
InnoDB引擎因為支援事務，所以一定會需要回滾段。關於InnoDB引擎的回滾段，有以下兩個引數：innodb_undo_directory  指定UNDO日誌的物理檔案位置
     innodb_undo_tablespaces  指定UNDO表空間的數量，每個undo表空間都是獨立的.idb檔案
     innodb_undo_logs   指定undo表空間中回滾段的數量
undo表空間一旦建立，就無法刪除。所以一旦使用了undo表空間，mysql的版本就無法從5.6降下去。 

預設情況下，連線到MYSQL服務的客戶端處於自動提交模式，即每條DML執行即提交。
如何啟動InnoDB引擎的事務支援？有兩種方式：
1、禁用事務的自動提交：
將autocommit系統變數置為0或者OFF。以後就由使用者來決定是否提交或者回滾事務。
autocommit是會話級變數，只針對當前會話，無法全域性生效。
mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
2、顯式宣告事務，透過語句start transaction語句。

mysql庫中的表預設均為MYISAM引擎表，不可以更改引擎。
對於InnoDB引擎表而言，用show table status返回表的記錄行數，行長度等狀態資訊並不精確，只是一個估算值。最好用count函式來進行統計。


InnoDB引擎表的邏輯儲存結構從小到大分為頁(pages)/塊，擴充套件(extend)/區，段(segments)，表空間。
頁(pages)/塊： 基本單位，預設大小為16K，可以透過引數innodb_page_size變數設定，可選值有4k，8k，16k
擴充套件(extend)/區：每個區固定1M大小，區由頁構成。
段(segments)：InnoDB中，段實際上指的是獨立表空間對應的資料檔案。
表空間：
 
InnoDB引擎內部在儲存資料到資料庫時，會自動生成3個內部列：DB_TRX_IDG標記事務的識別符號，DB_ROLL_PRT為回滾標記，DB_ROW_ID為行ID
回滾段中的UNDO日誌分為insert UNDO日誌和update UNDO日誌。
insert UNDO日誌：僅在事務回滾時需要，事務提交後即被廢棄
update UNDO日誌：用於構造一致性讀。
回滾段中的UNDO日誌記錄大小，一般會小於實際插入或者修改的記錄大小。

purge執行緒：delete記錄時，記錄並不會立即在物理上被刪除，只有當InnoDB廢棄update UNDO日誌後，才會從物理上移除關聯的列和索引記錄，這種移除操作稱之為purge。
控制purge執行緒延遲可以用過系統變數innodb_max_purge_lag進行設定。