Mysql table sharding Based on MRG_MyISAM engine, mrg_myisammysql

Mysql table sharding Based on MRG_MyISAM engine, mrg_myisammysql

Under normal circumstances, all sub-tables directly create multiple tables with the same structure, such as table_1 and table_2... recently, we encountered a special requirement that we need to create a primary table, add, delete, modify, and query data for all sub-tables, and update all data to the primary table in real time. In this case, we can use the MRG_MyISAM engine.

First, create the master table 'mygame _ message_log '. Create a table shard:

create table `mygame_message_log_1` LIKE `mygame_message_log`;create table `mygame_message_log_2` LIKE `mygame_message_log`;create table `mygame_message_log_3` LIKE `mygame_message_log`;create table `mygame_message_log_4` LIKE `mygame_message_log`;create table `mygame_message_log_5` LIKE `mygame_message_log`;create table `mygame_message_log_6` LIKE `mygame_message_log`;create table `mygame_message_log_7` LIKE `mygame_message_log`;create table `mygame_message_log_8` LIKE `mygame_message_log`;create table `mygame_message_log_9` LIKE `mygame_message_log`;create table `mygame_message_log_10` LIKE `mygame_message_log`;create table `mygame_message_log_11` LIKE `mygame_message_log`;create table `mygame_message_log_12` LIKE `mygame_message_log`;create table `mygame_message_log_13` LIKE `mygame_message_log`;create table `mygame_message_log_14` LIKE `mygame_message_log`;create table `mygame_message_log_15` LIKE `mygame_message_log`;create table `mygame_message_log_16` LIKE `mygame_message_log`;create table `mygame_message_log_17` LIKE `mygame_message_log`;create table `mygame_message_log_18` LIKE `mygame_message_log`;create table `mygame_message_log_19` LIKE `mygame_message_log`;create table `mygame_message_log_20` LIKE `mygame_message_log`;

　　

Then, associate the primary table with the sub-table:

ALTER TABLE mygame_message_log     ENGINE = MERGE UNION=        (mygame_message_log_1,        mygame_message_log_2,        mygame_message_log_3,        mygame_message_log_4,        mygame_message_log_5,        mygame_message_log_6,        mygame_message_log_7,        mygame_message_log_8,        mygame_message_log_9,        mygame_message_log_10,        mygame_message_log_11,        mygame_message_log_12,        mygame_message_log_13,        mygame_message_log_14,        mygame_message_log_15,        mygame_message_log_16,        mygame_message_log_17,        mygame_message_log_18,        mygame_message_log_19,        mygame_message_log_20)     INSERT_METHOD=no AUTO_INCREMENT=1;            

　　

Note that the primary key of a table cannot be set to auto-increment, and the structure of all tables must be consistent.

At the same time, this method has a defect, that is, it will be very troublesome to modify the table structure. Never modify it directly!

It is best to take the table design into consideration at the beginning. If you want to modify the table, follow these steps:

1. Delete the master table;

2. Modify fields for all table shards;

3. recreate a primary table:

　　

create table `mygame_message_log` LIKE `mygame_message_log_1`;

4. Associate the primary table:

ALTER TABLE mygame_message_log     ENGINE = MERGE UNION=        (mygame_message_log_1,        mygame_message_log_2,        mygame_message_log_3,        mygame_message_log_4,        mygame_message_log_5,        mygame_message_log_6,        mygame_message_log_7,        mygame_message_log_8,        mygame_message_log_9,        mygame_message_log_10,        mygame_message_log_11,        mygame_message_log_12,        mygame_message_log_13,        mygame_message_log_14,        mygame_message_log_15,        mygame_message_log_16,        mygame_message_log_17,        mygame_message_log_18,        mygame_message_log_19,        mygame_message_log_20)     INSERT_METHOD=no AUTO_INCREMENT=1;            

5. Data in the original master table is restored successfully.