Introduction to the principle of MYSQL table sharding (forwarding:
Reference a section in the MySQL official document: the MERGE storage engine, also known as the MRG_MyISAM engine, is a set of identical MyISAM tables that can be used as one. "same" means that all tables share the same column and index information. you cannot merge the tables in which columns are not in the same sequence. tables without exactly the same columns or tables with different order indexes are not allowed. in addition, any or all tables can be compressed using myisampack.
Example:
Mysql> show engines;
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| Engine | Support | Comment | Transactions | XA | Savepoints |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO |
| MyISAM | YES | MyISAM storage engine | NO |
| BLACKHOLE | YES |/dev/null storage engine (anything you write to it disappears) | NO |
| CSV | YES | CSV storage engine | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO |
| ARCHIVE | YES | Archive storage engine | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | 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 ('beijinging3 ');
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 ---- 1 mysql 8586 Feb 2 test1.frm
-Rw ---- 1 mysql 20 Feb 2 16: 40 test1.MYD
-Rw ---- 1 mysql 2048 Feb 2 test1.MYI
-Rw ---- 1 mysql 8586 Feb 2 test2.frm
-Rw ---- 1 mysql 40 Feb 2 16: 44 test2.MYD
-Rw ---- 1 mysql 2048 Feb 2 test2.MYI
-Rw ---- 1 mysql 8586 Feb 2 test. frm
-Rw ---- 1 mysql 32 Feb 2 16: 43 test. MRG
Note that to create MRG_MYISAM, you must specify a UNION = (list-of-tables) clause, which indicates which tables you want to use as a table. another important parameter is INSERT_METHOD. this parameter is INSERT_METHOD = NO, indicating that the table cannot be written for queries only. INSERT_METHOD = LAST indicates that the table is inserted to the LAST table.
Example:
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 ('beijinging4 ');
ERROR 1036 (HY000): Table 'test' is read only
This can be done when you need to add a new table in the existing 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 ('beijinging4 ');
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.
In general, MRG_MYISAM table sharding has the following advantages:
(1) tables for log applications. for example, you can put the monthly data in a separate table, compress these tables with myisampack, and create a MERGE table to treat them as a table.
(2) the query speed should be better. for some tables, large Read-only tables can be split and put into a single table on different disks. creating a MERGE table based on this would be much faster than simply creating a large table.
(3) exceeds the file size limit of the operating system. each MyISAM table is subject to this limit, but MRG_MYSIAM does not.
Disadvantages:
MRG_MYISAM does not support full-text indexing and other MyISAM functions. of course, you can create a full-text index in the base table, but you still cannot search for the full-text index in the MRG table. the following error is reported when you create a full-text index.
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
