MYSQL Sub-table principle

Introduction:
referring to a paragraph in the official MySQL document: The merge storage engine, also known as the Mrg_myisam engine, is the same set of MyISAM tables that can be used as a single. " The same "means that all tables have the same column and index information. You cannot combine columns that are listed in a different order in a table, a table that has exactly the same column, or a table with a different sequential index. Moreover, any or all of the tables can be compressed with myisampack.

Example:
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 (TEN) default NULL, PRIMARY key (ID)) engine=m            Yisam auto_increment=1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test2 (ID int NOT NULL auto_increment,name varchar (TEN) default NULL, PRIMARY key (ID)) engine=m Yisam 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 (TEN) default NULL, index (ID)) Engine=mrg_myisa M 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 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 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 2 16:43 test. MRG

It is important to note that establishing a MRG_MYISAM requires you to specify a union= (list-of-tables) clause that shows which tables you want to use as a table. Another key parameter Insert_method, this parameter Insert_ METHOD = no means that the table cannot do any write operations only as a query, Insert_method = last indicates inserting into the final table.

Example:
mysql> Show CREATE TABLE Test\g
*************************** 1. Row ***************************
table:test
Create table:create Table ' test ' (
' id ' int (one) not NULL auto_increment,
' name ' varchar () 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 (one) not NULL auto_increment, ' name ' varchar () DEFAULT null,key ' id ' (' id ')) EN              Gine=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

You can do this when you need to add a new table to an existing Mrg_myisam
mysql> CREATE TABLE test3 (ID int NOT NULL auto_increment,name varchar (TEN) default NULL, PRIMARY key (ID)) engine=m Yisam 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, it's done.

for daily Mrg_myisam The advantages of the table are the following points:
(1) for log class applications. For example, you can put the monthly data into a separate table, compress the tables with Myisampack, and create a merge table to use them as a table is very convenient and quick.
(2) The query speed should be better, for some tables can be divided into large read-only tables, placed in a single table on different disks. Building a merge table based on this would be a lot faster than simply a big table .
(3) exceeding the operating system's file size limits, each MyISAM table is subject to this limitation, but Mrg_mysiam does not.
Disadvantages:
Mrg_myisam does not support full-text indexing there are some other MyISAM features that can be created on the base table, but still cannot be searched on the MRG table with full-text indexing. The following error is reported in the full text
ERROR 1214 (HY000): The Used table type doesn ' t support Fulltext indexes

Transferred from: http://zzjlzx.blog.chinaunix.net/uid-10661836-id-4095860.html