The purpose of the table

During the development of the project, our database grew larger and larger, resulting in too much data in a single table. As a result, data query slows down, and application operations are seriously affected due to the locking mechanism of the table, resulting in database performance bottlenecks.

When this happens, we can consider the split table, that is, a single database table is split into multiple data tables, and then when users access, according to a certain algorithm, let users access different tables, so that the data is scattered into multiple data tables, reducing the pressure of single data table access. Improves database access performance.

Take a chestnut

Take a chestnut

For example, the most common user table

id

user_id

The other fields

The primary key id

The user id

The other fields

We generally use user_id to query the corresponding user information, but with the growth of business, the table will become bigger and bigger, even hundreds of millions, seriously affecting the query performance. So we will carry out sub-table processing of this table, divided into multiple tables to reduce the query pressure

Table strategy

Take 10 tables as an example (specific points how many tables according to the actual situation to estimate) first let’s create 10 tables user1, user2, user3… user10

Normally, we would use the field (user_id) as the index for the module processing. If you want to divide as many tables as you want, take the modulus of them, for example, this case is 10

$table_name = $user_id % 10;
Copy the code

Follow the modulo formula above

  • User_id 1295 will fall in user5

  • User_id 8634 will fall in user4

  • .

“CURD each time according to the above lookup table strategy” is not a big problem, but we won’t talk about it.

What about running tables that are already online?

In fact, we should all know how to use the above method, but there is a question, has been online table how to do? The data on that table is constantly being looked up and changed online. How can you smooth the table and make it invisible to the user?

Method 1

Write a script in advance. The script content is to synchronize the old table (user) data to user1 table to user10 table

This method is obviously not workable, mainly because there are the following problems

  • What if there is a problem with the script during execution? Roll back all the code?

  • How long does the script take to synchronize data from the old table (user) to user1 to user10? If it is 1 hour, then the business related to this table is abnormal during this time line

This obviously doesn’t work, and it has a big impact online.

Method 2

Write a data synchronization script, script content is to synchronize the old table (user) data to user1 table to user10 table, script synchronization is finished and then online.

This approach seems friendlier, but there are some problems.

  • There is some time difference between these two things. There may be some changes in the table on the line in this time difference. How to deal with these changes?

“Neither approach seems to be working, so something different has to be done. Let’s get straight to the conclusion.”

Procedure Step 1 Perform double write online

So first of all, let’s double up. What does that mean? For example, user_id=123, for add, delete, and modify operations, we operate on both the user table and the user3 table where user_id=123 corresponds.

Function modify($user_id){// Include add, delete, modify operation modify_user(); $table_name = $user_id % 10; Modify_user ($table_name) //modify table}Copy the code

Because the part of the query is still in the user table, the above operation has no impact on the online user.

Step 2 Perform full synchronization

Write a full synchronization of the user table to user1-user10 table, best to find a low peak execution script, in case of affecting the user query

After this step, the data between the user table and the user1-user10 table is exactly the same because we had double write earlier (see step 1).

Step 3 Query new table data

Change the query part to user1-user10

Since the previous two steps have ensured complete consistency between the user table and each sub-table, there is no problem in changing the query directly

If the above steps are followed, there will be no impact on the online data, and this is how we operate online. After repeated practice, we can use it at ease to ensure that there will be no problems. If this article helps you, give it a thumbs up.