MySQL advanced features-merge tables

Document directory

• 1. Merge Tables

1. Merge Tables

If you want to, you can think of the merged table as an old partition table with more restrictions, but they also have their own use, and can provide some functions that the Partition Table cannot provide.

Merged tables are actually containers that hold real tables. You can use special UNION syntax to create table. The following is an example of table merging:

mysql> CREATE TABLE t1(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;  mysql> CREATE TABLE t2(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;  mysql> INSERT INTO t1(a) VALUES(1),(2); mysql> INSERT INTO t2(a) VALUES(1),(2);  mysql> CREATE TABLE mrg(a INT NOT NULL PRIMARY KEY)    ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;  mysql> SELECT a FROM mrg;  

+ ------ +
| A | + ------ +
| 1 |
| 1 |
| 2 |
| 2 |
+ ------ +

Note that the number and type of table columns in the merged table are the same, and the indexes in the merged table will also exist in the subordinate table. This is the requirement for creating a merged table. Note that each table has a primary key in its unique column, which leads to duplicate rows in the merged table. This is a limitation of table merging: every table in the table merging is normal, but it does not forcibly restrict all the tables below.

INSERT_METHOD=LAST

The Command tells MySQL to send all the INSERT statements to the last table of the merged table. Defining FIRST or LAST is the only way to control the data insertion location (but can also be directly inserted into subordinate tables ).

Partitioned Tables can control the data storage location more.

The following INSERT statements are visible to both the merged and subordinate tables:

mysql> INSERT INTO mrg(a) VALUES(3);  mysql> SELECT a FROM t2; 

+ --- + | A | + --- + | 1 | 2 | 3 | + --- +

Table merging has other interesting features and restrictions, such as deleting a merged table or a subordinate table. Deleting a merged table makes all "sub-tables" inaccessible, but deleting a sub-table has different effects. Its behavior is related to the operating system. For example, on GNU/Linux, the file descriptor of the sub-table is still enabled, and the table still exists, but can only be accessed from the merged table.

mysql> DROP TABLE t1, t2;  mysql> SELECT a FROM mrg; 

+ ------ + | A | + ------ + | 1 | 1 | 2 | 2 | 3 | + ------ +

There are also some limitations and special behaviors. The best way is to read the manual, but here we will say that REPLACE cannot work on all merged tables, and AUTO_INCREMENT won't work as expected.

2. Impact of table merging on Performance

MySQL has some important impact on the performance of table merging. Like other MySQL features, it provides better performance under certain conditions. The following are some notes about it:

1) The merged table requires more file descriptors than non-merged tables with the same data.. Although the merged table looks like a table, it actually opens subordinate tables one by one. The result is that many file descriptors can be created in the cache of a single table. Therefore, even if the table cache has been configured so that the number of file descriptors on the server thread cannot exceed the operating system limit, merging tables may still exceed this limit.

2) The CREATE statement for creating a merged table does not check whether the subordinate table is compatible.. If the definitions of subordinate tables are slightly different, MySQL creates a merged table but cannot use it. Similarly, if a table is changed after a valid merged table is created, it cannot work and the following error message is displayed: "error 1168 (hy000): You cannot open tables with different definitions, non-MyISAM tables, or non-existent tables ".

3) the query of the Access Table to the merged table accesses each subordinate table.This may make single-row key lookup slower than a single table. It is a good idea to restrict subordinate tables in the merged table, especially the second or later tables in the join. The less data each operation accesses, the more important the overhead for accessing each table is than the entire operation. The following are some considerations for using merged tables:

4) range lookup is less affected by the overhead of accessing all subordinate tables than a single lookup.

Scanning an index table is as fast as scanning a single table.

Once the unique key and primary key are successfully queried, they are immediately stopped. In this case, the server will access the subordinate table one by one. Once the value is found, no more tables will be searched.

The read sequence of subordinate tables is the same as that defined in the creat TABLE statement. If you often need to obtain data in a specific order, you can use this feature to make the merge and sort operations faster.

3. Advantages of table Merging

Merged tables have both positive and negative aspects in data processing.

1) a typical example is logging. Logs are appended only, so one table can be used every day. Create a new table every day and add it to the merged table. You can also remove the previous tables from the merged table, convert them to compressed MyISAM tables, and then add them back to the merged table.

2) log append is not the only use of the merged table. They are usually used in data warehouse programs, because another advantage is to manage a large amount of data. In practice, it is unlikely to manage a TB-level table, but if it is a MERGE table consisting of a single 50 GB table, the task will be much simpler.

When managing an extremely large database, it is not just about regular operations. We also need to consider crash and recovery. It is a good idea to use small tables. Checking and repairing a series of small tables is much faster than a large table, especially when the large table and memory do not match. You can also check and repair multiple small tables in parallel.

Another concern in the data warehouse is how to clear old data. The efficiency of using the delete statement for a giant table is not high in the best condition, but it is a disaster in the worst case. However, changing the definition of a merged table is very simple. You can use the drop table command to delete old data. This can be easily automated.

3) The merged table is not only valid for logs and a large amount of data. It allows you to easily create busy tables as needed. The cost of creating and deleting a merged table is very low. You can use the Union all command to merge tables for an index. However, it has lower overhead because the server does not place the results in a temporary table and then transmits them to the client. This makes it very useful for reporting and warehousing data. For example, to create a task that runs every night, it combines the data of yesterday with the data of the previous eight days, 15 days ago, and each week before. You can use a merged table to create a query without modification and automatically access the appropriate data. You can even create a temporary merged table, which cannot be done by the view.

Because the merged table does not hide its subordinate MyISAM table, it provides some features that cannot be provided by partition tables:

A MyISAM table can contain many merged tables.

You can copy subordinate tables between servers by copying. frm,. myi, And. MYD files.

You can easily add more tables to the merged table. You only need to create a new table and change the merging definition.

You can create temporary tables that only contain the expected data, for example, data in a specific period of time. This is not possible for partitioned tables.

If you want to back up, restore, change, repair, or perform other operations on a table, you can remove it from the table to complete all the work before adding it back.

You can use myisampack to compress some or all subordinate tables.

The opposite is true for partitioned tables. MySQL hides partitions in partitioned tables and can only access all partitions through partitioned tables.