MySQL數(shù)據(jù)庫常見存儲引擎（一）

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

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

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

例如：

 #創(chuàng)建表結(jié)構(gòu) 存儲引擎為CSV
 create  table  csv2 (id int not null,
 name char(20) not null default "ZN")
 engine=csv  charset  utf8;
 #檢查表結(jié)構(gòu)：
 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)
#插入數(shù)據(jù)
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包安裝路徑 其他路徑根據(jù)自己安裝情況）
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)
#修復(fù)表
mysql> repair   table  csv1;
+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| test.csv1 | repair | status   | OK       |
+-----------+--------+----------+----------+
1 row in set (0.05 sec)
#檢查修復(fù)
mysql> check   table  csv1;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.csv1 | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.03 sec)
#檢查數(shù)據(jù) 更改生效
mysql> select  *  from csv1;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   9 | linux     |
|  99 | docker    |
| 200 | baidu     |
|  44 | openstack |
| 155 | facebook  |
| 121 | ansible   |
+-----+-----------+

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

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

簡單測試：

   先創(chuàng)建一個myisam存儲引擎的表，插入數(shù)據(jù)，然后創(chuàng)建ARCHIVE 存儲引擎的表插入數(shù)據(jù)，檢查其存儲空間的大小。

#創(chuàng)建測試表和相關(guān)的數(shù)據(jù)

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

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

#數(shù)據(jù)大小45790208

新創(chuàng)建存儲引擎為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)
大?。?801647

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

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

#創(chuàng)建表試試
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)

多次測試發(fā)現(xiàn)真的這么神奇，插入什么都成功了，但就是找不到數(shù)據(jù)，很神奇的存儲引擎吧？看看

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

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

  結(jié)合復(fù)制特性中的replicete-do-* 或者reolicate-ignore-*規(guī)則，可以實(shí)現(xiàn)對日志的過濾，通過這一巧妙的設(shè)計(jì)，就可以實(shí)現(xiàn)相同的寫入，但是主從間的數(shù)據(jù)不一致。

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

其他應(yīng)用情形：

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

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

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