简介:
引用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