Detailed explanation of how to smoothly go online after MySQL table splitting

Source of the article ： Learn through http://www.bdgxy.com/

The purpose of the sub table

Project under development , Our database data is getting bigger and bigger , What follows is that there is too much data in a single table . So that the query data becomes slower , Moreover, the application operation is also seriously affected due to the table locking mechanism , There is a database performance bottleneck .

When this happens , We can consider sub tables , About to split a single database table , Split into multiple data tables , Then when the user accesses , According to a certain algorithm , Let users access different tables , In this way, the data is scattered into multiple data tables , Reduce the access pressure of a single data table . Improved database access performance .

Take a chestnut

For example, our most common user table （user surface ）

We usually use user_id To query the corresponding user information , But as the business grows , This watch will get bigger and bigger , Even hundreds of millions , It seriously affects the query performance . So we'll split this table , Divide into multiple tables to reduce query pressure

Tabulation strategy

By points 10 Take Zhang Biao as an example ( How many tables are divided Estimate according to the actual situation ) First, let's build 10 A watch user1、user2、user3.....user10

In general , We all use fields as indexes (user_id) Take the mold . How many watches do you want to divide , Just take the mold according to how much , Like this case Namely 10

$table_name = $user_id % 10;

According to the above mold formula

• user_id by 1295 Will fall on user5 Inside
• user_id by 8634 Will fall on user4 Inside
• .......
  

「 Every time CURD Just follow the strategy of looking up the table above 」, It's not a big problem , Let's not say more .

What about running tables that are already online ？

In fact, everyone should know how to use the above methods , But there's a problem , What about the online watch ？ The data of that table has been searched or changed online . How to smooth sub table , And make users imperceptible ？

Method 1

Go online directly , Write a script in advance , The script is to put the old table (user) Data synchronization to user1 Table to user10 surface , As soon as it goes online, execute it quickly

This method obviously won't work , There are mainly the following problems

• What if there is a problem with the script during execution ？ All code is rolled back ？
• The script takes the old table (user) Data synchronization to user1 Table to user10 surface , How long does this script have to run ？ If it is 1 Hours , Then the business related to this table on this timeline is abnormal
  

This is obviously not going to work , It has a great impact on the online .

Method 2

First write a script to synchronize data , The script is to put the old table (user) Data synchronization to user1 Table to user10 surface , When the script is synchronized, go online again .

This method looks a little friendly , But there are also some problems .

• Script synchronization finished , Go online now , There is some time difference between the two things , There may be some changes in the online table in this time difference , What about these changes ？
  

「 Neither of these methods seems to work , So it looks a little different . Let's look directly at the conclusion .」

step 1 Online double writing

First, let's write the double line , What does that mean ？ such as user_id=123, For the increase , Delete , For modification operations , We both operate user surface , Also operate user_id=123 Corresponding user3 surface .

function modify($user_id){  // Including the addition of , Delete , Modify the operating 
  modify_user();  //modify user surface 
  $table_name = $user_id % 10;
  modify_user($table_name) //modify Corresponding sub table 
}

Because the part of the query is still user The , Therefore, the above operations have no impact on online users .

step 2 Full amount of synchronization

Write a full synchronization user Table to user1-user10 Table of , It's best to find a low peak execution script , Just in case it affects user Table in the query

After this step , Because we went online before ( See step 1), therefore user Table and user1-user10 The data between tables are completely consistent .

step 3 Query new table data

Change the part of the query to user1-user10

Because the first two steps have guaranteed user The data between tables and sub tables are completely consistent , So there is no problem changing the query directly

If you follow the above steps , Then there is no impact on the online data , And that's how we operate online , After many times of practice, it is ensured that there will be no problems , You can use it at ease .

summary

This is about mysql This is the article on how to smooth the online after the table is divided , More about mysql Split table smooth online content, please search rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！