重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
MySQL5.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)
分享文章：MySQL5.5存储引擎介绍
本文URL：http://cqcxhl.cn/article/ghpcgj.html
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)
这个存储引擎使提供连接到其他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)
分享文章：MySQL5.5存储引擎介绍
本文URL：http://cqcxhl.cn/article/ghpcgj.html