利用MRG_MyISAM儲存引擎實現分表

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