MySQL數據庫常見存儲引擎（一）

  熟悉mysql數據庫的朋友，肯定會喜歡mysql強大的插件式存儲引擎，能夠支持太多存儲引擎，當目前的存儲引擎不能滿足你的需求時，你可以根據自己的需求選擇合適的引擎，將相關的文件拷貝到相關路徑，甚至不需要重啟數據庫，就可以使用。真的很強大。

1 常見存儲引擎
memory存儲引擎  

    硬盤上存儲表結構信息，格式為.frm，數據存儲在內存中
    不支持blob text等格式
    創建表結構，
    支持表鎖
    支持B樹索引和哈希索引
    支持數據緩存 數據 緩存
    插入速度快
    分配給memory引擎表的內存不會釋放，由該表持有，刪除數據也不會被回收，會被新插入數據使用
CSV存儲引擎
    所有列必須制定為Not NULL
    CSV 引擎不支持索引 不支持分區
    文件格式 .frm  表結構信息
    .CSV 則是數據文件 是實際的數據
    .CSM 報錯表的狀態和表中的數據
    可以直接更改.csv文件 更改數據， check  table 檢查   repair table  （注：在手動更改.csv文件后 可以使用 repair table 加載數據）

例如：

 #創建表結構 存儲引擎為CSV
 create  table  csv2 (id int not null,
 name char(20) not null default "ZN")
 engine=csv  charset  utf8;
 #檢查表結構：
 mysql> desc  csv1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | ZN      |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
#插入數據
mysql> insert  into  csv1 values(3,'linux'),(20,"MYSQL");
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert  into  csv1 values(9,'linux'),(8,"MYSQL");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select  *  from csv1;
+----+-------+
| id | name  |
+----+-------+
|  3 | linux |
| 20 | MYSQL |
|  9 | linux |
|  8 | MYSQL |
+----+-------+
4 rows in set (0.00 sec)

#手動更改文件
 vim  /var/lib/mysql/test/csv1.CSV  #（RPM包安裝路徑 其他路徑根據自己安裝情況）
8,"MYSQL"
9,"linux"
99,"docker"
200,"baidu"
44,"openstack"
155,"facebook"
121,"ansible"
#檢查表
mysql> check   table  csv1;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.csv1 | check | error    | Corrupt  |
+-----------+-------+----------+----------+
1 row in set (0.03 sec)
#修復表
mysql> repair   table  csv1;
+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| test.csv1 | repair | status   | OK       |
+-----------+--------+----------+----------+
1 row in set (0.05 sec)
#檢查修復
mysql> check   table  csv1;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.csv1 | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.03 sec)
#檢查數據 更改生效
mysql> select  *  from csv1;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   9 | linux     |
|  99 | docker    |
| 200 | baidu     |
|  44 | openstack |
| 155 | facebook  |
| 121 | ansible   |
+-----+-----------+

    注意事項：check語句會檢查CSV文件的分隔符是否正確，數據列和定義的表結構是否相同，發現不合法的行會拋出異常，在使用修復時，會嘗試從當前的CSV文件中復制合法數據，清楚不合法數據，但是需要注意 修復時發現文件中有損壞的記錄行，那么后面的數據全部丟失，不管是否合法。

ARCHIVE 存儲引擎
     適用場景 歸檔
    支持大量數據壓縮 插入的列會被壓縮，ARCHIVE 引擎使用Zlib無損數據壓縮算法
    還可以使用optimze table  分析表并打包成更小的格式
    僅支持insert、update語句而不支持delete  replace update truncate 等語句 能支持order by操作 blob列類型
    支持行級鎖  但是不支持索引
    archive 引擎表文件.frm定義文件  .arz的數據文件，執行優化操作時可能還會還會出現一個擴展名的.arn文件。

簡單測試：

   先創建一個myisam存儲引擎的表，插入數據，然后創建ARCHIVE 存儲引擎的表插入數據，檢查其存儲空間的大小。

#創建測試表和相關的數據

mysql> create  table  archive2  engine=myisam  as  select  TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from  information_schema.columns;
Query OK, 3362 rows affected (0.10 sec)
Records: 3362  Duplicates: 0  Warnings: 0

mysql> select  count(*) from  archive2;
+----------+
| count(*) |
+----------+
|     3362 |
+----------+
1 row in set (0.00 sec)
#繼續再插入數據（執行多次）
mysql> insert into  archive2  select  * from archive2;
Query OK, 107584 rows affected (0.23 sec)
#檢查數據量
mysql> select  count(*) from  archive2;
+----------+
| count(*) |
+----------+
|   860672 |
+----------+
1 row in set (0.00 sec)
#檢查數據大小

mysql> show  table  status  like "archive2"\G;
*************************** 1. row ***************************
           Name: archive2
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 860672
 Avg_row_length: 53
    Data_length: 45790208
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-05-16 13:35:26
    Update_time: 2017-05-16 13:38:14
     Check_time: NULL
      Collation: gbk_chinese_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#數據大小45790208

新創建存儲引擎為archive類型的表
mysql> create  table  archive3 engine=archive as  select  * from  archive2;
Query OK, 860672 rows affected (2.69 sec)
Records: 860672  Duplicates: 0  Warnings: 0

mysql> select  count(*) from  archive3;
+----------+
| count(*) |
+----------+
|   860672 |
+----------+
1 row in set (0.11 sec)
檢查大小
mysql> show  table  status  like "archive3"\G;
*************************** 1. row ***************************
           Name: archive3
         Engine: ARCHIVE
        Version: 10
     Row_format: Compressed
           Rows: 860672
 Avg_row_length: 6
    Data_length: 5801647
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: 2017-05-16 13:42:35
     Check_time: NULL
      Collation: gbk_chinese_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
大小：5801647

   對比結果相差8倍的存儲值，差距還是很大。

BLACKGOLE存儲引擎
    是一個比較特殊的存儲引擎，只管寫入，但不管存儲，盡管能像其他存儲引擎一樣接受數據，但是所有數據都不會保存，BLACKGOLE存儲引擎永遠為空，有點類似Linux下的/dev/null。

#創建表試試
mysql> create  table  black  engine=blackhole as  select  * from  archive2;
Query OK, 860672 rows affected (0.65 sec)
Records: 860672  Duplicates: 0  Warnings: 0

mysql> select * from black ;
Empty set (0.00 sec)

mysql> insert into  black  select *  from archive2;
Query OK, 860672 rows affected (0.62 sec)
Records: 860672  Duplicates: 0  Warnings: 0

mysql> select * from black ;
Empty set (0.00 sec)

多次測試發現真的這么神奇，插入什么都成功了，但就是找不到數據，很神奇的存儲引擎吧？看看

    多次測試，結果就是那么神奇，插入都是成功的，但就是找不到數據，這個存儲引擎神奇吧，看看這個神奇的存儲引擎有哪些用途呢？？

1、盡管BLACKHOLE存儲引擎不會保存數據，但是啟用binlog，那么執行得SQL語句還是實際上被記錄，也就是說能復制到SLAVE端。如下圖：

  結合復制特性中的replicete-do-* 或者reolicate-ignore-*規則，可以實現對日志的過濾，通過這一巧妙的設計，就可以實現相同的寫入，但是主從間的數據不一致。

  BLACKHOLE對象中的insert觸發器會按照標準觸發，不過由于BLACKHOLE對象是空，那么UPdate和delete絕對不可能觸發，對于觸發器中FOR  EACH RAW語句并不會有任何影響。

其他應用情形：

其他應用：
    驗證dump文件語法
    通過對比啟動一級禁用二進制日志文件時的性能，來評估二進制日志對負載的影響。
    BLACKHOLE存儲引擎 支持事務，提交事務會寫入二進制日志 但回滾則不會
    BLACKHOLE存儲引擎與自增列
    BLACKHOLE引擎是no-op無操作引擎，所有在BLACKHOLE對象上的操作是沒有效果的，那么久需要考慮主見自增列的行為，該引擎不會自動增加自增列值，實際上也不會保存自增字段的狀態，對于復制來說，這一點很重要。

考慮以下復制場景
1、Master端BLACKHOLE表擁有一個自增的主鍵列
2、Slave端表存儲引擎為Myisam
3、Master端對該表對象的插入操作沒有明確知道自增列的列值
    該場景下 Slave端就會出現主鍵列的重復鍵錯誤，再給予語句的復制（SBR）模式下，每次插入事件的INSERT_ID都是相同的，因此復制就會觸發插入重復鍵的錯誤。
    在基于行的復制模式下，該引擎返回的列值總是相同的，那么在Slave端就會出現嘗試插入相同值的錯誤。

  MySQL的插件式存儲引擎是功能很豐富的，同樣也是適用于不用的應用情景，當你深入了解其原理后，才能發揮出MySQL更好的性能。