How to split a table in a database?

Methods for database splitting tables: 1. Horizontal splitting, splitting the table into multiple sub-tables according to the logical relationships and related conditions in the table; 2. Vertical splitting, splitting according to different tables to different databases.

As the data continues to expand, the size of some data tables will grow geometrically. When the data reaches a certain scale, data query and reading The performance will become slow. At this time, the data table needs to be split. In the following article, I will introduce in detail how to split the table in the database. I hope it will be helpful to everyone.

【Recommended course:MySQL Tutorial】

Why it is necessary to split the table

When a table has a large amount of data, it is more time-consuming. This requires splitting the table and splitting the large table into multiple sub-tables. table, then when updating or querying data, the pressure will be distributed to different tables. Since the data in each table is smaller after splitting, the speed of both query and update is greatly improved. Even if the worst "table lock" situation occurs, other tables can still be used in parallel.

Data table splitting

Data table splitting can be divided into two forms, namely horizontal splitting and vertical splitting

Horizontal splitting Splitting: According to the logical relationship of the data in the table, the data in the same table is split to multiple databases (hosts) according to certain conditions. This kind of segmentation is called horizontal (horizontal) segmentation of data.

Vertical split: Split into different databases (hosts) according to different tables. This split can be called vertical (vertical) split of data

Horizontal splitting method

Generally, the "modulo" form is used to store data in tables. If 4 tables are used, the result is id%4, and the result will be 0,1,2, 3 four kinds, user_0, user_1, user_2, user_3 are enough. It should be noted that a new temporary table needs to be created during this process. The purpose is to provide the auto-increment id for data insertion. After getting the auto-increment id, the table can be inserted into the sub-table through modulo

Method of vertical splitting

Put commonly used fields in one table and less commonly used fields in another table

Make the fields larger For example, the text field is separated and placed in a table. If used, it should be separated according to the specific business. When querying, use multi-table joint query, which can be combined with redis storage.

Summary: The above is the entire content of this article, I hope it will be helpful to everyone.

The above is the detailed content of How to split a table in a database?. For more information, please follow other related articles on the PHP Chinese website!