MySQL 5.5存儲引擎介紹

存儲引擎是MySQL組件，用于處理不同類型的表的SQL操作。

InnoDB存儲引擎

默認和應用最廣泛的存儲引擎。支持事務，具有crash-recovery特性；支持行級鎖；支持主鍵和外鍵。
InnoDB是MySQL中具有可靠性和高性能的一種通用存儲引擎。

優點：
DML操作遵循ACID模型（原子性、一致性、隔離性和持久性），支持事務，支持crash-recovery特性（當MySQL發生故障重啟后，對于InnoDB表，沒有完成的事務將通過redo日志重新進行，已經提交但是沒有寫到數據文件中的數據，將從doublewrite buffer中重新構建）以保護數據。

InnoDB buffer pool 緩存被訪問的表和索引信息，經常使用的數據直接從內存中讀取。

inserts,update,deletes操作被一種稱為change buffering的機制所優化。InnoDB不僅允許多并發讀寫同一張表，它還會緩存發生改變的數據，優化磁盤I/O。

當數據庫運行大表的長時間查詢且反復訪問相同表的相同行時，一種叫做Adaptive Hash Index的特性使這些查詢更快，就像數據從哈希表中查詢出來一樣。

可以壓縮表和相關的索引。

可以對性能和可用性造成很小影響地創建和刪除索引。

可以很快TRUNCATE掉一個file_per_table表空間，釋放出磁盤空間供操作系統使用，而不必釋放出僅能供InnoDB所重用的系統表空間。

支持行級鎖和一致性讀，提高多用戶的并發性和性能。

支持主鍵，提高查詢性能。

為了保持數據的完整性，InnoDB也支持外鍵。

你可以將InnoDB表與MySQL其他存儲引擎的表自由組合在一起使用。例如,在一個SQL中，你可以關聯一張InnoDB表和一個內存表。

在處理大量數據的時候，InnoDB引擎可以有效的發揮CPU效率和提升性能。

MyISAM存儲引擎

表級鎖會限制讀寫的性能，所以這個存儲引擎通常用于只讀或以讀為主的網站數據和數據倉庫配置中。

MyISAM表有下面特點：

所有數據的值會先以低字節存儲，這使得存放數據的機器和操作系統相互獨立。

所有數字鍵值會先以高字節存儲，這樣會使索引更好地壓縮。

支持文件系統和操作系統上面的大文件（63位文件長度）。

MyISAM表中行數的限制是(232)2 (1.844E+19)。

每張MyISAM表最多可以創建64個索引，聯合索引做多支持16個字段。

最大鍵長度為1000字節，這個可以通過源碼重新編譯。如果想讓一個鍵的長度大于250字節，需要使用大于默認1024字節的鍵塊。

當行按順序插入到MyISAM表中的時候，例如你使用了AUTO_INCREMENT字段，索引樹會被分割，高節點會只包含一個鍵值，這會提高索引樹空間的利用。

支持每個表AUTO INCREMENT字段的內部處理。MyISAM會自動更新這個字段的插入和更新操作。這使得AUTO INCREMENT字段序列處理能力更快（至少10%）。當序列被刪除后，序列的最高值不會被重用。

當MYISAM表的delete操作和update和update操作同時存在時，會在表中產生碎片，動態分配大小的行可以有效減少碎片。這個是數據庫通過把已刪除的相鄰行合并在一起以及擴展刪除的塊自動實現的。

MyISAM表支持并發插入。如果一張表沒有空閑的數據塊了，你可以在其他線程正在讀這張表的同時，插入新的行到這張表中。

你可以把數據文件和索引文件放在不同的物理設備上，這樣可以提高對表的讀寫速度。

--在不同的路徑下指定分區表的不同分區位置
mysql> create table t_partition(id int,name varchar(30),adate date) engine=myisam
    -> partition by list(year(adate))
    -> (
    ->   PARTITION p1999 VALUES IN (1995, 1999, 2003)
    ->     DATA DIRECTORY = '/appdata/95/data'
    ->     INDEX DIRECTORY = '/appdata/95/idx',
    ->   PARTITION p2000 VALUES IN (1996, 2000, 2004)
    ->     DATA DIRECTORY = '/appdata/96/data'
    ->     INDEX DIRECTORY = '/appdata/96/idx',
    ->   PARTITION p2001 VALUES IN (1997, 2001, 2005)
    ->     DATA DIRECTORY = '/appdata/97/data'
    ->     INDEX DIRECTORY = '/appdata/97/idx',
    ->   PARTITION p2002 VALUES IN (1998, 2002, 2006)
    ->     DATA DIRECTORY = '/appdata/98/data'
    ->     INDEX DIRECTORY = '/appdata/98/idx'
    -> ) ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t_partition values(100,'Neo',date'2016-04-12');
ERROR 1526 (HY000): Table has no partition for value 2016
mysql> insert into t_partition values(100,'Neo',date'1995-04-12');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_partition values(200,'Tom',date'1997-04-12');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_partition;
+------+------+------------+
| id   | name | adate      |
+------+------+------------+
|  100 | Neo  | 1995-04-12 |
|  200 | Tom  | 1997-04-12 |
+------+------+------------+
2 rows in set (0.06 sec)

[root@localhost data]# ls /appdata/95/data/
t_partition#P#p1999.MYD
[root@localhost data]# ls /appdata/95/idx/
t_partition#P#p1999.MYI
[root@localhost data]# ls /appdata/97/data/
t_partition#P#p2001.MYD
[root@localhost data]# ls /appdata/97/idx/
t_partition#P#p2001.MYI
[root@localhost data]# ls /appdata/98/idx/
t_partition#P#p2002.MYI
[root@localhost data]# ls /appdata/98/data
t_partition#P#p2002.MYD

可以為BLOB和TEXT創建索引。

索引字段可以包括空值，每個鍵占據0到1字節。

每個字符字段可以使用不同的字符集。

在MyISAM索引文件中有一個標識，這個標識可以判斷表是否正確關閉。如果mysqld啟動服務的時候帶上了--myisam-recover-options 參數，當數據庫打開的時候，MyISAM表會自動檢查，當MyISAM表沒有正確關閉的時候會自動修復。

可以通過 myisamchk 工具來檢查MyISAM表。

可以通過myisampack工具來壓縮BLOB和VARCHAR字段。

支持真VARCHAR類型，一個VARCHAR字段可以存儲一個或兩個字節。

帶有VARCHAR字段的表可以有固定或動態的行長。

--創建測試表

mysql> create table t_myd5(id int,v1 char(10)) engine=myisam;
Query OK, 0 rows affected (0.83 sec)

mysql> show table status like 't_myd5'\G
*************************** 1. row ***************************
           Name: t_myd5
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9851624184872959
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-04-13 06:03:53
    Update_time: 2016-04-13 06:03:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.08 sec)

mysql> create table t_myd6(id int,v1 char(10)) row_format=fixed engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> show table status like 't_myd6'\G
*************************** 1. row ***************************
           Name: t_myd6
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9851624184872959
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-04-13 06:05:47
    Update_time: 2016-04-13 06:05:47
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=FIXED
        Comment: 
1 row in set (0.02 sec)

mysql> create table t_myd7(id int,v1 char(10)) row_format=dynamic engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> show table status like 't_myd7'\G
*************************** 1. row ***************************
           Name: t_myd7
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-04-13 06:08:13
    Update_time: 2016-04-13 06:08:13
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 
1 row in set (0.00 sec)

在一張表中，VARCHAR或CHAR字段總長度為64K。

--創建測試表
mysql> create table t_myd2(id int) engine=myisam;
Query OK, 0 rows affected (0.07 sec)

--查看創建的數據文件，.frm文件存放表結構文件，.MYI文件存放索引文件，.MYD文件存放數據文件
[root@localhost fire]# ls -trl
total 656
-rw-rw----. 1 mysql mysql   8556 Apr 12 00:28 t_myd2.frm
-rw-rw----. 1 mysql mysql   1024 Apr 12 00:28 t_myd2.MYI
-rw-rw----. 1 mysql mysql      0 Apr 12 00:28 t_myd2.MYD

MEMORY存儲引擎

將所有的數據存放在內存（RAM）中，應用的場景：非關鍵數據實現快速訪問。這個存儲引擎原來被稱為HEAP引擎。它的應用場合在減少，因為InnoDB可以通過buffer pool memory將大多數的數據保留在內存中，并且更加的可靠安全；同時NDBCLUSTER對大的數據集提供了基于鍵值的快速查詢。

這個存儲引擎適合存放短暫、非核心的數據，當MySQL服務器停止或重啟時,內存中的數據將會丟失。

它可以實現數據的快速訪問和低延遲，可以將數據完全加載到內存中，而不會導致操作系統虛擬內存頁的交換。

它適合只讀或以讀為主的數據訪問模式（數據更新很少）。

它默認使用哈希索引，而不是B+樹索引。

內存表最大的容量不能超過max_heap_table_size這個系統參數，默認值時16MB。

雖然Memory存儲引擎速度非常快，但在使用上還是有一定的限制。比如，其只支持表鎖，并發性能較差，并且不支持TEXT和BLOB列類型。最重要的是，存儲變長字段（varchar）時是按照字段（char）的方式進行的，因此會浪費內存。此外有一點常被忽視的是，MySQL數據庫使用Memory存儲引擎作為臨時表來存放查詢的中間結果集（intermediate result）。如果中間結果集大于Memory存儲引擎表的容量設置，又或者中間結果含有TEXT或BLOB列類型字段，則MySQL數據庫會把其轉換到MyISAM存儲引擎表而存放到磁盤。MyISAM不緩存數據文件，因此這時產生的臨時表的性能對于查詢會有損失。

CSV存儲引擎

使用這個存儲引擎的表實際上是用逗號分隔的文本文件。CSV表可以讓你以CSV格式導入和導出數據。CSV表不能創建索引，你可以在正常的操作時使用InnoDB表，只在導出和導出數據階段使用CSV表。

--創建測試表
mysql> create table t_csv1 (id int not null default 0,v1 varchar(20) not null default '') engine=csv;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t_csv1 values(1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_csv1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

--查看數據文件中生成的表結構文件（.frm）、元數據文件（.CSM）和表數據文件（.CSV）
[root@localhost firedb]# ls -trl
total 172
-rw-rw----. 1 mysql mysql  8582 Apr  9 19:09 t_csv1.frm
-rw-rw----. 1 mysql mysql    35 Apr  9 19:11 t_csv1.CSM
-rw-rw----. 1 mysql mysql    12 Apr  9 19:11 t_csv1.CSV

可以通過check語句來檢查CSV表中數據的有效性，檢查的時候會掃描字段分隔符，判斷出正確的字段以及格式不匹配的數據。

mysql> check table t_csv1;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| firedb.t_csv1 | check | status   | OK       |
+---------------+-------+----------+----------+
1 row in set (0.06 sec)

可以通過REPAIR語句來修復損壞的CSV表。這個操作會修復表中的有限數據，同時表中損壞的數據將會丟失。

mysql> repair table t_csv1;
+---------------+--------+----------+----------+
| Table         | Op     | Msg_type | Msg_text |
+---------------+--------+----------+----------+
| firedb.t_csv1 | repair | status   | OK       |
+---------------+--------+----------+----------+
1 row in set (0.01 sec)

Archive存儲引擎

使用這個存儲引擎的表，數據排列緊湊而不能創建索引，用于存放和查詢數據量大的歷史、歸檔或安全審計信息。
Archive存儲引擎支持INSERT, REPLACE, and SELECT操作，不支持DELETE和UPDATE操作，也不支持排序、BLOB字段。
Archive存儲引擎使用行級鎖。插入的Archive表中的數據會被壓縮，Archive存儲引擎使用zlib數據壓縮方法。

--創建測試表，兩個表中存放相同的數據，使用Myisam引擎的表使用了517696字節，而使用Archive引擎的表使用了68904字節

mysql> create table t_mi1 engine=myisam as select * from information_schema.columns;
Query OK, 509 rows affected (0.12 sec)
Records: 509  Duplicates: 0  Warnings: 0

mysql> insert into t_mi1 select * from t_mi1;
Query OK, 509 rows affected (0.00 sec)
Records: 509  Duplicates: 0  Warnings: 0

mysql> insert into t_mi1 select * from t_mi1;
Query OK, 1018 rows affected (0.01 sec)
Records: 1018  Duplicates: 0  Warnings: 0

mysql> insert into t_mi1 select * from t_mi1;
Query OK, 2036 rows affected (0.01 sec)
Records: 2036  Duplicates: 0  Warnings: 0

mysql> show table status like 't_mi1'\G
*************************** 1. row ***************************
           Name: t_mi1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4072
 Avg_row_length: 127
    Data_length: 517696
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-04-11 23:55:41
    Update_time: 2016-04-11 23:55:54
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.11 sec)

mysql> create table t_arc1 engine=archive as select * from t_mi1;
Query OK, 4072 rows affected (0.21 sec)
Records: 4072  Duplicates: 0  Warnings: 0

mysql> show table status like 't_arc1'\G
*************************** 1. row ***************************
           Name: t_arc1
         Engine: ARCHIVE
        Version: 10
     Row_format: Compressed
           Rows: 4072
 Avg_row_length: 16
    Data_length: 68904
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: 2016-04-12 00:05:26
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.20 sec)

--查看創建的表文件，表的定義文件是.frm文件，實際存放數據的文件是.ARZ文件
[root@localhost fire]# ls -trl
total 640
-rw-rw----. 1 mysql mysql  13552 Apr 12 00:05 t_arc1.frm
-rw-rw----. 1 mysql mysql  68904 Apr 12 00:05 t_arc1.ARZ

Blackhole存儲引擎

這個存儲引擎接受數據的插入但是并不儲存數據，有點類似Unix下的/dev/null設備。對Blackhole表的查詢通常返回值為空。Blackhole表可以用在復制的配置中，當DML語句發送到備用服務器時，主服務器不保存它自己的數據拷貝。
Blackhole存儲引擎支持各種索引。插入到Blackhole表的數據將不會存在于此表中，但是如果數據庫開啟了二進制日志，相關的SQL語句會被記錄并復制到備用服務器。這個特性在將某個MySQL數據庫當作中繼器或過濾器的時候很有用。

--創建測試表

mysql> create table t_bl1(i int,c char(10)) engine=blackhole;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_bl1 values(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

--數據文件中只有表的定義文件

[root@localhost firedb]# ls -trl
total 728
-rw-rw----. 1 mysql mysql   8578 Apr 10 06:01 t_bl1.frm

Merge存儲引擎

這個存儲引擎也被稱為MRG_MyISAM存儲引擎，可以將一系列具有相同列和索引的MyISAM表邏輯地組合成一個數據對象，對于數據倉庫環境很有用。當要組合的表中的列的順序不一致時，不能使用Merge存儲引擎。和Merge表相對應的是分區表，分區表將單個表中的數據存放到不同的文件中。

--創建兩張結構相同的Mysiam表

mysql> create table t_mg1 (id int not null auto_increment primary key,v1 varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t_mg2 (id int not null auto_increment primary key,v1 varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t_mg1(v1) values('This'),('ls'),('mysl');
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t_mg1(v1) values('This'),('ls'),('mys2');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_mg1;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mysl |
|  4 | This |
|  5 | ls   |
|  6 | mys2 |
+----+------+
6 rows in set (0.00 sec)

mysql> insert into t_mg2(v1) values('This'),('ls'),('mys3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t_mg2(v1) values('This'),('ls'),('mys4');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_mg1;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mysl |
|  4 | This |
|  5 | ls   |
|  6 | mys2 |
+----+------+
6 rows in set (0.00 sec)

mysql> select * from t_mg2;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mys3 |
|  4 | This |
|  5 | ls   |
|  6 | mys4 |
+----+------+
6 rows in set (0.00 sec)

--創建MERGE表，將之前創建的兩張表合并到一起

mysql> create table t_mer1(id int not null auto_increment primary key,v1 varchar(20)) engine=merge union=(t_mg1,t_mg2);
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t_mer1;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mysl |
|  4 | This |
|  5 | ls   |
|  6 | mys2 |
|  1 | This |
|  2 | ls   |
|  3 | mys3 |
|  4 | This |
|  5 | ls   |
|  6 | mys4 |
+----+------+
12 rows in set (0.00 sec)

--查看創建的生成文件
.frm里面存放的是表結構信息，.MRG里面存放的是數據來源于哪些表，實際上創建出來的MERGE表里面使用的還是源表的數據

[root@localhost firedb]# ls -trl
total 804
-rw-rw----. 1 mysql mysql   8582 Apr 10 07:10 t_mg1.frm
-rw-rw----. 1 mysql mysql   8582 Apr 10 07:10 t_mg2.frm
-rw-rw----. 1 mysql mysql   2048 Apr 10 07:11 t_mg1.MYI
-rw-rw----. 1 mysql mysql    120 Apr 10 07:11 t_mg1.MYD
-rw-rw----. 1 mysql mysql   2048 Apr 10 07:13 t_mg2.MYI
-rw-rw----. 1 mysql mysql    120 Apr 10 07:13 t_mg2.MYD
-rw-rw----. 1 mysql mysql   8582 Apr 10 07:15 t_mer1.frm
-rw-rw----. 1 mysql mysql     12 Apr 10 07:15 t_mer1.MRG

[root@localhost firedb]# cat t_mer1.MRG 
t_mg1
t_mg2

--向源表t_mg1里面插入兩條記錄，數據會直接出現在MERGE表t_mer1中

mysql> insert into t_mg1 values(8,'car');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_mg1(v1) values('car2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_mg1;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mysl |
|  4 | This |
|  5 | ls   |
|  6 | mys2 |
|  8 | car  |
|  9 | car2 |
+----+------+
8 rows in set (0.00 sec)

mysql> select * from t_mer1;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mysl |
|  4 | This |
|  5 | ls   |
|  6 | mys2 |
|  8 | car  |
|  9 | car2 |
|  1 | This |
|  2 | ls   |
|  3 | mys3 |
|  4 | This |
|  5 | ls   |
|  6 | mys4 |
+----+------+
14 rows in set (0.06 sec)

--可以向MERGE表插入數據，通過insert_method屬性決定向源表的哪張表插入數據，insert_method last代表的是最后一張源表

mysql> alter table t_mer1 insert_method last;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t_mg2;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mys3 |
|  4 | This |
|  5 | ls   |
|  6 | mys4 |
+----+------+
6 rows in set (0.00 sec)

mysql> insert into t_mer1(v1) values('car5')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_mg2;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mys3 |
|  4 | This |
|  5 | ls   |
|  6 | mys4 |
| 10 | car5 |
+----+------+
7 rows in set (0.00 sec)

mysql> select * from t_mer1;
+----+------+
| id | v1   |
+----+------+
|  1 | This |
|  2 | ls   |
|  3 | mysl |
|  4 | This |
|  5 | ls   |
|  6 | mys2 |
|  8 | car  |
|  9 | car2 |
|  1 | This |
|  2 | ls   |
|  3 | mys3 |
|  4 | This |
|  5 | ls   |
|  6 | mys4 |
| 10 | car5 |
+----+------+
15 rows in set (0.00 sec)


MariaDB [test]> create table payment_2006(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount decimal(15,2),
    -> key idx_fk_country_id(country_id))
    -> engine=myisam;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table payment_2007(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount decimal(15,2),
    -> key idx__fk_country_id(country_id))
    -> engine=myisam;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table payment_all(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount decimal(15,2),
    -> index(country_id))
    -> engine=merge union=(payment_2006,payment_2007) insert_method=last;
Query OK, 0 rows affected (0.09 sec)

MariaDB [test]> insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> insert into payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from payment_2006;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

--由于使用的是LAST方法，向MERGE表中插入數據，會向建表時的最后一張表插入數據

MariaDB [test]> insert into payment_all values(3,'2006-03-31',112200);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
5 rows in set (0.00 sec)

MariaDB [test]> select * from payment_2006;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [test]> show keys from payment_2006;
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| payment_2006 |          1 | idx_fk_country_id |            1 | country_id  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MariaDB [test]> show keys from payment_2007;
+--------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| payment_2007 |          1 | idx__fk_country_id |            1 | country_id  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MariaDB [test]> show keys from payment_all;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| payment_all |          1 | country_id |            1 | country_id  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
Federated存儲引擎

這個存儲引擎使提供連接到其他MySQL服務器來創建邏輯數據庫的能力，對于分布式數據庫或數據集市很有用。

--在目標端創建表
mysql> create table poll_vote(id int(10) not null auto_increment,parents_id int(10) not null,
    -> vote_count mediumint(10) not null default 0,vote_month_count mediumint(10) not null default 0,
    -> vote_month mediumint(10) not null,primary key(id),unique key ind_poll_vote_baike(parents_id,vote_month))
    -> engine=innodb auto_increment=26020 default charset=latin1;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into poll_vote(parents_id,vote_count,vote_month_count,vote_month) values(10,100,100,100);
Query OK, 1 row affected (0.07 sec)

--在源端創建表，結構和目標端的表結構一致
mysql> create table poll_vote(id int(10) not null auto_increment,parents_id int(10) not null,
    -> vote_count mediumint(10) not null default 0,vote_month_count mediumint(10) not null default 0,
    -> vote_month mediumint(10) not null,primary key(id),unique key ind_poll_vote_baike(parents_id,vote_month))
    -> engine=federated auto_increment=26020 default charset=latin1
    -> connection='mysql://test:System#2013@192.168.78.137/fire/poll_vote';
Query OK, 0 rows affected (0.08 sec)
mysql> select * from poll_vote;
+-------+------------+------------+------------------+------------+
| id    | parents_id | vote_count | vote_month_count | vote_month |
+-------+------------+------------+------------------+------------+
| 26020 |         10 |        100 |              100 |        100 |
+-------+------------+------------+------------------+------------+
1 row in set (2.01 sec)

--當你創建了一張FEDERATED表時，表的定義文件（.frm文件）會存在于本地，表的實際數據文件則存放在遠程數據庫服務器。
--查看創建出來的表
[root@localhost fire]# ls -trl
total 28
-rw-rw----. 1 mysql mysql   61 Apr 11 07:06 db.opt
-rw-rw----. 1 mysql mysql 8736 Apr 11 19:39 poll_vote.frm

連接的示例
connection='mysql://username:password@hostname:port/database/tablename'
connection='mysql://username@hostname/database/tablename'
connection='mysql://username:password@hostname/database/tablename'

使用另外一種方式創建表

如果你在相同的服務器上創建了多張FEDERATED表，或者你想簡化創建FEDERATED表的流程，可以使用CREATE SERVER語句來定義要連接的服務器參數。

mysql> create server db_01 foreign data wrapper mysql 
    -> options (user 'test',password 'System#2013', host '192.168.78.137', port 3306,database 'fire');
Query OK, 1 row affected (0.07 sec)

mysql> select * from mysql.servers\G
*************************** 1. row ***************************
Server_name: db_01
       Host: 192.168.78.137
         Db: fire
   Username: test
   Password: System#2013
       Port: 3306
     Socket: 
    Wrapper: mysql
      Owner: 
1 row in set (0.00 sec)

mysql> create table poll_vote_2(id int(10) not null auto_increment,parents_id int(10) not null,
    -> vote_count mediumint(10) not null default 0,vote_month_count mediumint(10) not null default 0,
    -> vote_month mediumint(10) not null,primary key(id),unique key ind_poll_vote_baike(parents_id,vote_month))
    -> engine=federated auto_increment=26020 default charset=latin1
    -> connection='db_01/poll_vote';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from poll_vote_2;
+-------+------------+------------+------------------+------------+
| id    | parents_id | vote_count | vote_month_count | vote_month |
+-------+------------+------------+------------------+------------+
| 26020 |         10 |        100 |              100 |        100 |
+-------+------------+------------+------------------+------------+
1 row in set (0.08 sec)