Example of using MySQL sub-table storage

In general, if the data volume in our project is very large, we usually consider processing the data in a table by table, for example, our user access log. If the data is always stored in a table, the data volume of the table will be very large over time, resulting in reduced query efficiency. In this case, you need to store the data in multiple tables.
Log data has the following typical features:

1. Large data volume.
2. Stable output every month.
3. After data is inserted, most of the data operations are only queries, and few edit or delete operations are performed.

MySQL database provides the data sharding method.

MERGE storage engine
When using the SQL statement Create table to Create a table, you can specify the storage engine of the data table. When the statement ENGINE = MEGRE or ENGINE = MRG_MyISAM is used to declare the storage ENGINE, this means that the data table uses the MERGE storage ENGINE to store data. This data table can be a set of other data tables of the specific ENGINE = MEGRE type. However, the table structures and indexes of these so-called specific data tables must be exactly the same (but the defined order can be different ). The AVG_ROW_LENGTH, MAX_ROWS, and PACK_KEYS attributes of a special table can be different.

Mysql> create table t1 (
-> A int not null AUTO_INCREMENT primary key,
-> Message CHAR (20) ENGINE = MyISAM;
Mysql> create table t2 (
-> A int not null AUTO_INCREMENT primary key,
-> Message CHAR (20) ENGINE = MyISAM;
Mysql> insert into t1 (message) VALUES ('testing'), ('table'), ('T1 ');
Mysql> insert into t2 (message) VALUES ('testing'), ('table'), ('T2 ');
Mysql> create table total (
-> A int not null AUTO_INCREMENT,
-> Message CHAR (20), INDEX ())
-> ENGINE = merge union = (t1, t2) INSERT_METHOD = LAST;
 
The above code is the sample code provided in the official documentation. It creates two sub-tables: t1 and t2, and a total table total. Insert data to t1 and t2 respectively, and then query in the total table. You can find that data inserted to t1 and t2 can be queried in the total table.

We can use the SELECT, DELETE, UPDATE, and INSERT operations in the total table. However, we must have the SELECT, DELETE, and UPDATE permissions for each table shard.

When creating a MERGE table, use the UNION parameter to integrate the table, for example, the UNION = (t1, t2) code above ). You can also use the INSERT_METHOD parameter to declare whether to insert data to the first or last table in the MERGE table. If INSERT_METHOD = NO, data insertion is not allowed in the MEGRE table, but in each table shard.

However, it is worth noting that we didn't define indexes in the partition table in the above code, but we defined INDEX (a) in the total table and didn't define the primary key. This is because the data in the summary table cannot be guaranteed or the uniqueness of the data is not checked, that is, the identical data is allowed in each table.

Edit ing
If we create a new table shard that needs to be added to the table, there are two methods:

Delete the summary table and recreate it.

Use alter table tbl_name UNION = (......) To re-mark the table shards to be mapped.
If we accidentally write UNION = () (empty in parentheses), we will find that this table will be empty and cannot be inserted, because in fact the table itself does not store data, in this way, MYSQL does not know where to insert the data.
Some rules
Definitions and indexes of tables and sub-tables must be consistent in most cases. MySQL does not check whether the table sharding settings are correct when creating a summary table. It only checks whether there are errors when enabling the query. This also leads to the possibility that there is no error during table sharding creation, and then the table Sharding is edited one day, resulting in the unavailability of the primary table. Therefore, our tables and sub-tables must meet the following rules:

The number of columns in the table and table shards must be the same.

The order of the columns in the table and table shards must be the same.
In addition, the columns of the summary table and table shards must meet the following rules:
The column type must be the same
The column length must be the same
The column content can be NULL.
The number of indexes in a table Shard must be greater than or equal to the number of indexes in a table shard. The number of indexes in a table shard can be greater than the total table, but not less than the total table.
Table index rules:

The index types of the table and table shards must be consistent.
The index of the table and table shards must be consistent.
Additional checks on the index part
The index length must be consistent.
The index types must be consistent.
The language settings of the index must be consistent.
Can be blank

If the TABLE cannot be opened or used during use, we can also use the command: check table to show which TABLE has a problem.