MySQL sub-table scene analysis and simple sub-table operation

Why do you want to divide tables

First of all to know what the situation, only need to divide the table of individuals feel that the number of single-table records to the million to tens to use the sub-table, the purpose of the table is this, reduce the burden of the database, shorten the query time.

There are two ways of table partitioning:

1 Horizontal split: Places data rows into two separate tables based on the values of one or more columns of data.

Horizontal segmentation is typically used in the following situations :

The table is large, can reduce the number of pages of data and indexes that need to be read at query time, and also reduce the index layer, improve the query speed.

The data in the table is inherently independent, for example, the table records data for each region or different periods of data, especially when some data is commonly used, while others are not used.

Data needs to be stored on multiple media.

Horizontal partitioning adds complexity to your application, and it usually requires multiple table names at query time, and the union operation is required to query all data. In many database applications, this complexity outweighs its advantages because, as long as the index keyword is not large, when the index is used for querying, the table adds two to three times times the amount of data, and the query increases the number of disks that read an index layer.

2 Vertical Partitioning: Place the main code and some columns into a table, and then place the main code and other columns in the other table.

If some columns in a table are commonly used, while others are not used, vertical segmentation can be used, and vertical partitioning can make the data rows smaller, a data page can hold more data, and the I/O times are reduced at query time.　The disadvantage is that redundant columns need to be managed, and the join operation is required to query all data.  

Scenario Case :

Blog system

Vertical split :

Article title, author, classification, creation time, etc., is the change frequency slow, the number of queries, and preferably have good real-time data, we call it cold data.

and the number of blog views, replies, and so on, similar statistics, or other high-frequency changes in the data, we call it active data.

Therefore, in the design of database structure, we should consider the sub-table, first of all, the processing of vertical table.

This is followed by a longitudinal sub-table:

First, the use of the storage engine is different, cold data using MyIsam can have better query data. Active data, can use InnoDB, can have better update speed.

Second, the cold data is more from the library configuration, because more operations are queries, so as to speed up the query. For thermal data, it is possible to handle the horizontal table of the main library relatively.

In fact, for some special active data, you can also consider the use of memcache, Redis and other caches, such as accumulated to a certain amount to update the database.

Horizontal split :

When the amount of blog is very large, you should take the horizontal segmentation to reduce the pressure of each single table, to improve performance.

For example, a blog cold data table, if divided into the table, when there are 1 million users in the browsing, if it is single table, will make 1 million requests, and now after the table, it is possible to make 10,000 data per table request (because, can not be absolute average, just assume), So the pressure is much lower.

Use the Merge storage engine to present a horizontal list of instances:

View MySQL 's storage engine

Mysql> Show Engines \g;

Realistic scenario Simulation

First step : Create a table member

DROP table IF EXISTS member;

CREATE TABLE member (

ID bigint auto_increment PRIMARY key,

Name varchar (20),

Sex tinyint NOT null default ' 0 '

) Engine=innodb DEFAULT Charset=utf8 auto_increment=1;

Step two : Create a stored procedure and insert millions of data

# If there is a stored procedure defined inserts, Delete

drop procedure IF EXISTS inserts;

# Custom Terminator

Delimiter//

# Create a stored procedure

CREATE PROCEDURE inserts ()

Begin

DECLARE I int;

Set i = 1;

while (i <=) do

Insert into member (Name,sex) VALUES (concat (' name ', i), i%2);

SET i = i+1;

END while;

End

# End a stored procedure definition with a custom terminator

//

# The restore Terminator is ;

delimiter;

# call a stored procedure

Call inserts ();

MySQL syntax defaults to using semicolons ";" as a SQL flag for statement end . can use delimiter command to modify it to another symbol, such as: "delimiter//" represents the // as a commit symbol .

To demonstrate the Sub -table, 10 data simulations are inserted into the instance.

Step three : Create a sub-table

# Table #

DROP table IF EXISTS tb_member1;

CREATE TABLE Tb_member1 (

ID bigint primary KEY auto_increment,

Name varchar (20),

Sex tinyint NOT null default ' 0 '

) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;

# Sub-table

DROP table IF EXISTS tb_member2;

# Copy table 1

CREATE table tb_member2 like Tb_member1;

Fourth Step : Create the Main table, where the definition of the main table is different from the target table to be divided

# Main Table #

DROP table IF EXISTS tb_member;

CREATE TABLE Tb_member (

ID bigint auto_increment,

Name varchar (20),

Sex tinyint NOT null default ' 0 ',

INDEX (ID)

) Engine=merge union= (tb_member1,tb_member2) insert_method=last auto_increment=1;

Querying index information for tb_member tables

Mysql> Show index from Tb_member \g;

Fifth step : Divide the target table data into two sub-tables

INSERT into Tb_member1 (tb_member1.id,tb_member1.name,tb_member1.sex)

SELECT Member.id,member.name,member.sex

from member where member.id%2=0;

INSERT into Tb_member2 (tb_member2.id,tb_member2.name,tb_member2.sex)

SELECT Member.id,member.name,member.sex

from member where Member.id%2=1;

Of course, the actual scene according to the need for a unique identification operation , take hash ah what and so on , here only use simple to find the model table .

Sixth step : View the sub-table data

Seventh Step : View the total table data

In this way, the data in the table member is separated, the table group is divided into Tb_member as the main table, Tb_member1 and Tb_ The Member2 is a sub-table. After the table, the data are stored in the table, the total list is just a shell, access to the data in a single sub-table inside.

For the Merge table, it is important to note that

1. The structure of each child table must be consistent, the structure of the primary and child tables need to be consistent,

2. The index of each child table will be present in the merge table, so the merge table cannot be retrieved uniquely based on that index.

3. The child table needs to be the MyISAM engine

4. Replace does not work in the merge table

5. Auto_increment doesn't work the way you want it to

The parameters for creating the Mysql Merge table Insert_method have several parameters .

Last if you execute the INSERT command to manipulate the merge table, the insert operation adds the data to the final child table.

First, when you insert data, the data is added to the second child table.

MySQL sub-table scene analysis and simple sub-table operation