Vertical split and horizontal split of database tables __ Database

Split vertically

A vertical split is a split of a table column , which splits more than one table into multiple tables.


In general, we divide vertically by the following principles: separate the infrequently used fields in a single table;
The Text,blob and other large segments are split out and placed in the schedule;
The columns of a common query are placed in a table, and the vertical split is more often done at the beginning of the design of the datasheet, and then when the query is used to join the key; Horizontal Split

A horizontal split is a split of a table row that is slower than 2 million rows, and can be stored by splitting the data of one table into multiple tables.

some tips for horizontal splitting

1. Split principle
In general, we use modulo to split table, such as a 400W user table users, in order to improve its query efficiency we divide it into 4 sheets

Users1,users2,users3,users4
The data is dispersed into four sheets by means of id modulo id%4+1 = [1,2,3,4]
Here is a small hash, and then query, update, delete is also through the modulo method to query

$_get[' id '] =
17%4 + 1 = 2,  
$tableName = ' users '. ' 2 '
Select * from users2 where id = 17;

You also need a temporary table uid_temp to provide your own ID when you insert, and the only use of the table is to provide your own ID;

INSERT into uid_temp values (NULL);

When the ID is obtained, it is inserted into the table by taking the model method.
Note that when you split the table horizontally, the column and type of the field should be the same as the original table, but remember to remove the auto_increment from growth

In addition
Some business logic can also be archived and split by fields such as region, year, etc.
After the split table, only to meet the query requirements of the efficient query, then we will be in the product planning, from the interface constraints user query behavior. For example, we are according to the year to file the split, this time in the page design on the constraint users must first select the year before the query;
In doing analysis or statistics, because it is the needs of our own, more waiting in fact is not related, and the concurrency is very low, this time can be used to combine all tables into a view to query, and then query;

Create view users as SELECT from Users1 Union SELECT from Users2 Union ...