Vertical and horizontal splitting of MySQL tables

Vertical Split

Vertical splitting refers to the splitting of data table columns, splitting a table with many columns into multiple tables

1. Usually we perform vertical splitting according to the following principles:
2. Put infrequently used fields in a separate table;
3. Split out large fields such as text and blob and put them in the appendix;

Columns that are frequently queried in combination are placed in one table;

Vertical splitting should be performed at the beginning of data table design, and then the join key can be used when querying;

Horizontal Split

Horizontal splitting refers to the splitting of data table rows. When the number of rows in a table exceeds 2 million, the processing will become slow. At this time, the data of one table can be split into multiple tables for storage.

Some tips for horizontal splitting

1. Split principle

Usually, we use the modulus method to split the table; for example, a user table with 4 million users is divided into 4 tables to improve its query efficiency.

users1, users2, users3, users4

By using the ID modulus method, the data is distributed into four tables. Id%4+1 = [1,2,3,4]

Then query, update, and delete are also queried by taking the modulus method

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

When inserting, a temporary table uid_temp is also needed to provide an auto-incrementing ID. The only use of this table is to provide an auto-incrementing ID.

insert into uid_temp values(null);

After getting the self-incrementing ID, the sub-table is inserted through the modulo method;

Note that the columns and types of the fields in the table after horizontal split should be the same as those in the original table, but remember to remove the auto_increment self-increment

in addition

• Some business logic can also be split by fields such as region and year;
• The split table can only meet the efficient query requirements of some queries. At this time, we need to restrict user query behavior from the interface in product planning. For example, if we want to split archives by year, the page design will constrain users to select a year before they can search;
• When doing analysis or statistics, it doesn't matter if you wait a little longer because it's your own needs, 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 again;
  

Create view users as select from users1 union select from users2 union.........

The above are the details of vertical splitting and horizontal splitting of MySQL tables. For more information about MySQL table splitting, please pay attention to other related articles on 123WORDPRESS.COM!