MySQL sub-table and partition

MySQL partitioned partitions are two ways to solve the poor MySQL performance caused by large data volumes.

What is MySQL sub-table

From a superficial point of view, the MySQL sub-table is to divide a table into multiple tables, and the data and structure are likely to change. MySQL sub-table is divided into vertical and horizontal sub-tables.

1, Vertical sub-table

The vertical table is divided by the fields in the table, as shown in.

In, we divide the C1, C2, C3, C4 Four fields that were originally distributed in the same table into two tables vertically. The first table distributes C1, C3, C4 three fields, and the second table distributes C1, C2 two fields. The split two tables are associated with this common field C1.

2, the level of the table

The horizontal table is divided by the records in the table. As shown in.

In, we divide four records that were originally distributed in the same table and split horizontally into two tables. In the first table, two records are distributed; In the second table, two records are distributed.

3, sub-table operation

MySQL tables can be custom-defined, use industry-wide rules, and can be implemented using the merge storage engine.

1) Custom Rules

According to the user or business number of the table. Pairs with the user or business can be divided into N tables according to the number%n.

The table is divided by date. For tables such as logs or statistics classes. Can be according to the year, month, day, week table.

2) using the merge storage engine

Using the merge storage engine to achieve MySQL sub-table comparison for those who did not consider the table, with the increase in data, there has been a slow data query. Using the merge storage engine to implement MySQL sub-tables avoids code changes. Using merge to implement MySQL sub-table can be done as follows:

In, engine = merge indicates that the merge engine is used. In addition engine = Mrg_myisam is the same meaning. UNION = (user1, user2) indicates that the User1, User2 table, Insert_method = Last represents the Insert method: 0 does not allow insertions, first inserts into the Union in table one, and finally inserts into the final table in the Union.

Using the merge storage engine to implement MySQL sub-table, the results of the table will be divided into the main table and the child table, the main table is similar to a shell, the child table is logically encapsulated, in fact, the data are stored in the child table. As shown in.

Is the result of the merge table for the user table, AllUser is the total table, User1 and User2 are the sub-tables. Each table has its own table structure, the child table and also holds the data and index, the general table does not hold the data and the index, the summary table only holds the table the relation, as well as inserts the data the way.

4, Sub-table query

For the query operation after the table, it is still the basic operation of union query, view and so on, or merge the data with the merge engine and query in this table. Complex operations require the use of stored procedures to achieve the management of the tables with external tools. Such as:

• The vertical sub-table uses the join connection, and the horizontal table uses the Union connection.

• For MySQL sub-tables implemented using the merge storage engine, you can query the total table directly.

5. Precautions

1) Duplicate record/Repeat index

If a merge table is established, the table T1/t2 already exists and duplicate records exist in the t1/t2. When a query is encountered, an entry that satisfies the record is returned. This means that only one record will be displayed without error. If the merge table is insert/update, a duplicate index appears, and an error is indicated. The merge table is only responsible for operations after the table is built.

2) How to delete a sub-table

You cannot delete a table directly, which destroys the merge table. The correct method is:

ALTER TABLE t ENGINE = Mrg_myisam UNION = (T1) Insert_method = last;

drop table T1;

3) Delete the merge summary form by mistake

Deleting the merge table by mistake will not result in data loss, just re-create the total table.

What is a MySQL partition

From the surface, the MySQL partition is to divide the data of a table into multiple storage chunks, and the structure is not changed. In addition, these storage blocks can be on the same disk or on different disks. As shown in.

Is the distribution of files on the disk after the table AA is partitioned. We can see that the data structure of the AA table after partitioning has not changed, and the location of data and index storage has changed from one to two. In addition, a. par file is opened, and you can see that he recorded the partition information for this table after opening the. par file.

1. Partition operation

MySQL supports partition from 5.1.3, you can use the following command to confirm whether your version supports partition:

The types of partitions supported by MySQL include range, List, Hash, Key, where range is more commonly used:

1) Range – This mode allows DBAs to divide data into different ranges. For example, DBAs can divide a table into three partitions by year, data for the 80 's (1980 's), data for the 90 's (1990 's), and any data after 2000 (including 2000). As follows:

Here, the user table is divided into 4 partitions, each 3 million records as the boundary, each partition has its own independent data, index file directory.

2) List (pre-defined list) – This mode allows the system to be segmented by the row data corresponding to the values of the DBA-defined list. For example, the DBA partitions according to the type of user.

3) key (key value) – an extension of the above hash mode, where the hash key is generated by the MySQL system.

4) Hash – This mode allows the DBA to calculate the hash key for one or more columns of the table, and finally to partition the data region of the hash code with different values. For example, a DBA can create a table that partitions a table's primary key.

2. Precautions

1) Each of these partitioning methods allows you to separate the physical disks on which these partitions reside to improve disk IO throughput. As follows:

is the separation of physical space for the range partition type.

2) Although the partitioning is cool, there are many limitations to the current implementation:

• The primary key or unique index must contain a partition field, such as primary key (i,created).

• In many cases, using a partition will not use the primary key, otherwise it may affect performance.

• You can only partition by a field of type int or an expression that returns an int type: Typically, a function such as year or to_days is used.

• Up to 1024 partitions per table: It is not possible to extend partitions indefinitely, and excessive use of partitions tends to consume large amounts of system memory.

• A table with partitioning does not support foreign keys: The associated constraint logic must be implemented by the program.

The similarities and differences between MySQL sub-table and partition

1. Can improve MySQL's high sex, in a high-concurrency state has a good performance.

2. Sub-table and partition is not contradictory, can cooperate with each other, for those large traffic, and table data more tables, we can take the table and partition combination of the way (if the merge table, not with the partition, you can use other sub-table test), access is small, but the table data a lot of tables, We can take the partitioning method and so on.

3. The Sub-table technology is troublesome, need to manually create a child table, the app server read and write time needs to calculate the child table name. It is better to use merge, but also to create the union relationship between the child table and the Configuration child table.

4. Table partitioning is easy to use relative to the table, and does not require creating child tables.

MySQL sub-table and partition