In general, after vertical and horizontal splitting of libraries, a reasonable vertical splitting of tables is sufficient. Horizontal splitting of tables is not recommended.

Library split

The vertical resolution

Order library,goods library

Horizontal split

For example, if we split the order library,order1-order32 libraries, the rules such as the orderId field (Long) move 42 bits to the right. Which library is it in.

For example 4418858115632 is in the Order1 library.

We can calculate here,Long64, the lower 42 represents the actual ID,2 to the 42nd power 4,398 billion, so if 100 million orders are made in a day, 43,980/360 =122

It can last for over 100 years. If the monad type is different, it is recommended to add different prefixes and divide the monad vertically into different databases.

Table split

The vertical resolution

Vertical splitting is the splitting of data table columns. A table with many columns is split into multiple tables

Usually we split vertically according to the following principles:

  1. Put uncommon fields in a separate table;
  2. Separate large fields such as text and BLOb into attached tables.
  3. Frequently combined query columns in a table;

Vertical split more often should be performed at the beginning of the data table design step, and then query with jion key up;

Horizontal split

Horizontal splitting is the splitting of index table rows. When the number of table rows exceeds 2 million, it will slow down. At this time, the data of a table can be split into multiple tables to store.

Some tips for horizontal splitting

1. Splitting principle Under normal circumstances, we use the mode to split the table; For example, a 400W user table users is divided into 4 tables users1, users2, users3 to improve the query efficiency. Select * from users4 where ID %4+1 = [1,2,3,4]

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

You also need a temporary table, uID_temp, to provide the incremented ID during insert. The only use of this table is to provide the incremented ID.

insert into uid_temp values(null);
Copy the code

After obtaining the self-increasing ID, the sub-table insertion is carried out by taking the mode method.

Note that the columns and types of columns in the split table should be the same as those in the original table, but remember to remove auto_INCREMENT

In addition

  • Part of the business logic can also be split by the region, year and other fields;
  • The split table can only meet the efficient query requirements of part of the query. At this time, we need to restrict user query behavior from the interface in product planning. For example, we split the archive by year. At this time, the page design constraints users to select year first before they can query.
  • When doing analysis or statistics, it is not necessary to wait more because it is the demand of the people, and the concurrency is very low. At this time, you can use union to combine all tables into a view for query, and then query.