img.liuyifei.net 
简介:
引用mysql官方文档中的一段话:merge存储引擎,也被认识为mrg_myisam引擎,是一个相同的可以被当作一个来用的myisam表的集合."相同"意味着所有表同样的列和索引信息.你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表.而且,任何或者所有的表可以用myisampack来压缩.
例子:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| engine | support | comment | transactions | xa | savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| memory | yes | hash based, stored in memory, useful for temporary tables | no | no | no |
| mrg_myisam | yes | collection of identical myisam tables | no | no | no |
| myisam | yes | myisam storage engine | no | no | no |
| blackhole | yes | /dev/null storage engine (anything you write to it disappears) | no | no | no |
| csv | yes | csv storage engine | no | no | no |
| performance_schema | yes | performance schema | no | no | no |
| archive | yes | archive storage engine | no | no | no |
| federated | no | federated mysql storage engine | null | null | null |
| innodb | default | supports transactions, row-level locking, and foreign keys | yes | yes | yes |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> create table test1 (id int not null auto_increment,name varchar(10) default null ,primary key (id)) engine=myisam auto_increment=1;
query ok, 0 rows affected (0.01 sec)
mysql> create table test2 (id int not null auto_increment,name varchar(10) default null ,primary key (id)) engine=myisam auto_increment=1;
query ok, 0 rows affected (0.00 sec)
mysql> insert into `test1` (`name`) values('beijing1');
query ok, 1 row affected (0.00 sec)
mysql> insert into `test2` (`name`) values('beijing2');
query ok, 1 row affected (0.00 sec)
mysql> create table test (id int not null auto_increment,name varchar(10) default null ,index(id)) engine=mrg_myisam union=(test1,test2) insert_method=last auto_increment=1;
query ok, 0 rows affected (0.03 sec)
mysql> select id,name from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
+----+----------+
2 rows in set (0.00 sec)
mysql> insert into `test` (`name`) values('beijing3');
query ok, 1 row affected (0.00 sec)
mysql> select id,name from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
| 2 | beijing3 |
+----+----------+
3 rows in set (0.00 sec)
mysql> select id, name from test2
-> ;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing2 |
| 2 | beijing3 |
+----+----------+
2 rows in set (0.00 sec)
mysql> system ls -l /mysql/data/test
total 164
-rw-rw---- 1 mysql mysql 8586 feb 2 16:40 test1.frm
-rw-rw---- 1 mysql mysql 20 feb 2 16:40 test1.myd
-rw-rw---- 1 mysql mysql 2048 feb 2 16:40 test1.myi
-rw-rw---- 1 mysql mysql 8586 feb 2 16:40 test2.frm
-rw-rw---- 1 mysql mysql 40 feb 2 16:44 test2.myd
-rw-rw---- 1 mysql mysql 2048 feb 2 16:44 test2.myi
-rw-rw---- 1 mysql mysql 8586 feb 2 16:43 test.frm
-rw-rw---- 1 mysql mysql 32 feb 2 16:43 test.mrg
在这里需要注意建立mrg_myisam需要必须指定一个union=(list-of-tables)子句,它说明你要把哪些表当作一个表来用.另外一个重要的参数insert_method,此参数insert_method = no 表示该表不能做任何写入操作只作为查询使用,insert_method = last表示插入到最后的一张表里面.
例子:
mysql> show create table test/g
*************************** 1. row ***************************
table: test
create table: create table `test` (
`id` int(11) not null auto_increment,
`name` varchar(10) default null,
key `id` (`id`)
) engine=mrg_myisam default charset=utf8 insert_method=last union=(`test1`,`test2`)
1 row in set (0.00 sec)
mysql> create table `test` (`id` int(11) not null auto_increment,`name` varchar(10) default null,key `id` (`id`))engine=mrg_myisam default charset=utf8 insert_method=no union=(`test1`,`test2`);
query ok, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
| 2 | beijing3 |
+----+----------+
3 rows in set (0.00 sec)
mysql> insert into `test` (`name`) values('beijing4');
error 1036 (hy000): table 'test' is read only
当你需要在现有mrg_myisam添加新表的时候可以这样做
mysql> create table test3 (id int not null auto_increment,name varchar(10) default null ,primary key (id)) engine=myisam auto_increment=1;
query ok, 0 rows affected (0.02 sec)
mysql> alter table test engine=mrg_myisam union=(test1,test2,test3 ) insert_method=last;
query ok, 0 rows affected (0.01 sec)
records: 0 duplicates: 0 warnings: 0
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
| 2 | beijing3 |
+----+----------+
3 rows in set (0.00 sec)
mysql> insert into `test` (`name`) values('beijing4');
query ok, 1 row affected (0.00 sec)
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | beijing1 |
| 1 | beijing2 |
| 2 | beijing3 |
| 3 | beijing4 |
+----+----------+
4 rows in set (0.00 sec)
mysql> select * from test3;
+----+----------+
| id | name |
+----+----------+
| 3 | beijing4 |
+----+----------+
1 row in set (0.00 sec)
ok,完成了.
对于日常来说mrg_myisam分表优点主要有以下几点:
(1)对于日志类应用的表.比如,你可以把每月的数据放进分离的表中,用myisampack压缩这些表,创建一个merge表来把它们当作一个表来使用非常方便快捷.
(2)在查询速度上应该更优,对于一些表可以分割大的只读表,放进不同磁盘上的单个表中.基于这个建立一个merge表会比单纯一个大表速度应该会快不少.
(3)超过操作系统的文件尺寸限制,每个myisam表都受制于这个限制,但是mrg_mysiam则不会.
缺点:
mrg_myisam不支持全文索引还有其它一些myisam功能,当然可以在底表创建,但是仍然不能利用全文索引在mrg表上搜索.建立全文会报如下错误
error 1214 (hy000): the used table type doesn't support fulltext indexes
转自： http://zzjlzx.blog.chinaunix.net/uid-10661836-id-4095860.html
