How to smoothly go online after MySQL table partitioning

Purpose of the table

During project development, our database data is getting larger and larger, and as a result, there is too much data in a single table. As a result, data query becomes slower and application operations are severely affected due to the table locking mechanism, resulting in a database performance bottleneck.

When this happens, we can consider splitting the table, that is, splitting a single database table into multiple data tables. Then, when users access the data, they can access different tables according to certain algorithms. In this way, the data is dispersed into multiple data tables, reducing the access pressure of a single data table. Improved database access performance.

For example

For example, our most common user table (user table)

We usually use user_id to query the corresponding user information, but as the business grows, this table will become larger and larger, even hundreds of millions, seriously affecting the query performance. So we will split this table into multiple tables to reduce the query pressure

Table partitioning strategy

Taking 10 tables as an example (the specific number of tables should be estimated based on actual conditions), first we create 10 tables user1, user2, and user3. . . . . user10

Generally, we use the indexed field (user_id) for modulo processing. If you want to divide the table into as many tables as you want, you can use the modulus as you want. For example, in this case, it is 10.

$table_name = $user_id % 10;

According to the above modulus formula

• The user_id of 1295 will be in user5
• The user_id of 8634 will be in user4
• . . . . . . .
  

"Each time CURD is executed according to the strategy of the above table search", this is not a big problem, so we will not talk about it for now.

What should I do with the running tables that are already online?

In fact, everyone should know how to use the above method, but there is a problem, what to do with the table that has already been put online? The data in that table is always being looked up or changed online. How can we smoothly partition the tables without users noticing?

Method 1

Go online directly and write a script in advance. The script content is to synchronize the data of the old table (user) to the user1 table and the user10 table. Execute it as soon as it goes online

This method is obviously not feasible, mainly due to the following problems

• What if there is a problem with the script during execution? Roll back all code?
• The script synchronizes the data of the old table (user) to the user1 table and the user10 table. How long does it take to execute this script? If it is 1 hour, then the online business related to this table during this period is abnormal.
  

This obviously doesn't work and has a huge impact on the online community.

Method 2

First, write a script to synchronize data. The script content is to synchronize the data of the old table (user) to the user1 table and the user10 table. After the script synchronization is completed, go online.

This method seems to be more friendly, but there are also some problems.

• After the script is synchronized, it goes online immediately. There is some time difference between these two things. During this time difference, there may be some changes in the online table. What should be done with these changes?
  

"It seems that the above two methods are not feasible, so it seems that we have to do something different. Let's go straight to the conclusion."

Step 1: Launch dual write

First of all, let’s put double writing online. What does it mean? For example, if user_id=123, for add, delete, and modify operations, we operate both the user table and the user3 table corresponding to user_id=123.

function modify($user_id){ //Includes add, delete, and modify operations modify_user(); //modify user table $table_name = $user_id % 10;
  modify_user($table_name) //modify the corresponding sub-table}

Because the query is still in the user table, the above operation has no effect on online users.

Step 2: Full synchronization

Write a script to fully synchronize the user table to the user1-user10 table. It is best to find a low-peak period to execute the script in case it affects the query of the user table.

After this step is executed, because we have launched dual write before (see step 1), the data between the user table and the user1-user10 table are completely consistent.

Step 3: Query the new table data

Change the query part to user1-user10

Because we have ensured the complete consistency of data between the user table and each sub-table in the previous two steps, there is no problem in directly changing the query part.

If you follow the above steps, there will be no impact on online data. This is how we operate online. After many practices, we have ensured that there will be no problems. You can use it with confidence.

Summarize

This is the end of this article about how to smoothly launch MySQL table partitioning. For more information about how to smoothly launch MySQL table partitioning, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!