Example of using MRG_MyISAM (MERGE) to implement query after partitioning in MySQL

Optimizing large amounts of database data is a huge science, and it is also a professional skill that a developer needs to master.

MySQL table partitioning can be divided into vertical partitioning and horizontal partitioning. Both forms of partitioning are relatively simple. To put it simply, vertical partitioning means that there are not many records in the table, but the fields are very long, the table occupies a large space, and a large amount of IO needs to be executed when retrieving the table, which seriously reduces the performance. At this time, you need to split the large fields into another table, and this table has a one-to-one relationship with the original table. Horizontal table partitioning is to split the data of the same table into multiple tables according to certain rules within the same database. The purpose is to optimize the performance problems caused by too much data in a single table, avoid IO contention and reduce the chance of locking the table.

It is easy to implement table sharding, but the complicated part is how to query data after sharding? Today's implementation is to use the Mysql table engine MRG_MyISAM (MERGE)
The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. "Same" means the same column and index information for all tables. You cannot merge tables in which the columns are listed in a different order, tables that do not have exactly the same columns, or tables that have indexes in different orders. Furthermore, any or all tables can be compressed using myisampack. Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS are not significant.

When you create a MERGE table, MySQL creates two files on disk. The file name begins with the name of the table and has an extension to indicate the file type. A .frm file stores the table definition and a .MRG file contains the name of the table to be used. These tables do not have to be in the same database as the MERGE table itself.

You can use SELECT, DELETE, UPDATE, and INSERT on collections of tables. You must have SELECT, UPDATE, and DELETE privileges on the tables that you map to a MERGE table.

If you DROP a MERGE table, you are removing only the MERGE specification. The underlying table is not affected.

When you create a MERGE table, you must specify a UNION=(list-of-tables) clause, which states which tables you want to use as one. If you want inserts into the MERGE table to occur on the first or last table in the UNION list, you can optionally specify an INSERT_METHOD option. Use the FIRST or LAST value to cause the insert to be done on the first or last table, respectively. If you do not specify the INSERT_METHOD option, or you specify this option with a value of NO. An attempt to insert a record into a MERGE table results in an error.

For example:
Create two sub-tables:

CREATE TABLE `yzm_table1` (
  `id` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL DEFAULT '',
  `content` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `yzm_table2` (
  `id` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL DEFAULT '',
  `content` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

The table has been created. It should be noted that the id here cannot be set to auto-increment, and all table structures must be consistent, including the structure, type, length, and order of fields. So how do you get this id? I will explain this in detail later. Now, we need a merged table for querying. The code to create the merged table is as follows:

CREATE TABLE `yzm_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '',
  `content` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=NO INSERT_METHOD=LAST UNION=(`yzm_table1`,`yzm_table2`);

The merged table must also have the same structure, type, length, and order of fields as the previous table. INSERT_METHOD=NO here means that insert operations are not allowed on this table. Well, when we need to query, we can only operate on the yzm_table table, which means that this table can only perform select operations.

Insert some data test:

INSERT INTO `yzm_table1` VALUES ('1', 'table1-1', 'content1-1');
INSERT INTO `yzm_table1` VALUES ('2', 'table1-2', 'content1-2');
INSERT INTO `yzm_table2` VALUES ('3', 'table2-1', 'content2-1');
INSERT INTO `yzm_table2` VALUES ('4', 'table2-2', 'content2-2');
INSERT INTO `yzm_table2` VALUES ('5', 'table2-3', 'content2-3');

Then query the merged table (main table)

SELECT * FROM `yzm_table` LIMIT 10;

Sure enough, all the data of all related sub-tables can be queried, so that all the data can be queried by paging, statistics, etc.

The above is the details of the example of using MRG_MyISAM (MERGE) to implement query after sharding in MySQL. For more information about query after sharding in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!