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 tessellation is typically used under the following circumstances
Table is large, which reduces the number of pages of data and indexes that need to be read at query time, and also lowers the index layer. Improve query speed. The data in the
table is inherently independent, for example, in a table where data from each region is recorded, or at different times, especially when some data is commonly used, while others are not used. The
needs to store the data on multiple media.
Horizontal segmentation adds complexity to your app, and it usually requires multiple table names at query time, and querying all data requires union

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, the cold data table of the blog, if divided into the table, when there are millions of users in the browsing, if it is single table, will make the request, and now after the table, it may be 1 per table Thousands of requests for data (because, impossible to absolutely mean, just assumptions), so that the pressure is much lower.

MySQL sub-table method : http://blog.csdn.net/heirenheiren/article/details/7896546

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

View MySQL 's storage engine

MySQL> Show engines  \g;

Realistic scenario Simulation

The first step : CREATE TABLE Member

DROPTableIFEXISTSMemberCreateTable Member (ID bigint auto_increment primary key20), sex tinyint not null  ' 0 ") Engine=innodb default CHARSET =utf8 auto_increment=1;  

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

#如果存在已定义的存储过程inserts, removeDropProcedureIFEXISTSInserts; #自定义结束符delimiter//#创建存储过程CreateProcedureInserts ()BeginDECLARE IInt;Set I=1;while (i<=10) doinsert into member (Name,sex) values (Concat ( name< Span style= "color: #ff0000;" > ', i), I%2 ); set i = i+1end while;end//  #还原结束符为;d Elimiter; #调用存储过程call inserts ();        

MySQL syntax defaults to using semicolons ";" A flag that ends as an SQL statement. You can use the delimiter command to modify it to another symbol, such as: "Delimiter//" to represent the commit symbol AS//.

In order to demonstrate the sub-table, the sample is inserted into the data simulation .

Step three : Create a sub-table

#分表1 #DROPTableIFEXISTSTb_member1;CreateTableTb_member1 (IDbigintPrimaryKeyAuto_increment, Namevarchar20), Sextinyint not null default  ' 0 ") Engine=myisam default CHARSET =utf8 auto_increment=1drop table Span style= "color: #0000ff;" >if exists Tb_member2; #复制表1 create table Tb_member2 like tb_member1;           

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

#主表 #DROPTableIFEXISTSTb_member;CreateTableTb_member (IDbigint Auto_increment, name varchar (20 ), sex tinyint not null default  0 ' 

Querying index information for tb_member tables

MySQL fromtb_member \g;

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

INSERTIntoTb_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 data of a table

Seventh Step : view 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_member2 as a sub-table . After the table, the data are stored in the table, the summary is just a shell, access to data occurs in one of the sub-table inside.

ForMergeTable, it is important to note that
1.The structure of each child table must be consistent, and the structure of the primary and child tables needs to be consistent.
2.The index of each child table is in theMergeTable will exist, so theMergeThe table does not have a unique retrieval based on the index.
3.The child table needs to beMyISAM engine
4. replace in merge 5. auto_increment 
to create mysql merge insert_method last   If you do insert  command to operate merge

Reprint please specify source: [http://www.cnblogs.com/dennisit/p/3649931.html]

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