Actual Combat: Change a single database and single table to multiple databases and multiple tables

Hello everyone, my name is Qixi (xī).

As the title says, this article will introduce 3 parts based on my own personal experience:

1. Various implementation schemes for changing online single-database single-table to multi-database multi-table
2. Comparison of pros and cons of programs
3. For single tables that exist in history, and they do not need to become multi-table, what should be done?

Let us draw a conclusion first. There is no 100% perfect solution, and technical solutions must always be designed in combination with product business .

The solutions exemplified below are only common practices, and the specific details can be adjusted according to business scenarios.

As long as it can meet the needs of the business, it is a good solution. Don't ignore the business in order to show off the technology.

After reading this article, if someone asks you later about the plan to change to Dokudo, then you can chat and laugh with him.

Well, let me talk about the business background on my side, and explain to you why you need multiple tables. The plan will be extended later, don't worry.

1. Business Background

There is a database running online , let's say it is the user library, and there is only one single table in the library.

Now there is a new requirement, and the function of this requirement has a certain amount of requests and data.

Among them, the amount of data is in the millions in the initial stage. Considering the increase in business, it is possible to grow to tens of millions or hundreds of millions. Therefore, in terms of data volume, a single database and single table is not suitable .

Q1: If it is only a problem of data volume, can you use a single database and multiple tables?
A1: OK.
Q2: Then why do you still use multiple databases and multiple tables?
A2: Because the number of connections to a database is limited, I am afraid of overturning .

It is mentioned above that the business has a certain amount of requests. If you are worried that a library will handle it, in case the network is not good/slow check/the table business has unexpected activities, etc.

If you accidentally fill up the number of connections, it will directly overturn.

In addition, our company's infrastructure for Dokudo tables is relatively mature, so I will directly go to Dokudo tables here.

Q3: In this case, is it okay to place a single database and multiple tables in the early stage, and then add multiple databases and multiple tables after the same amount?
A3: Yes. But it's too tiring to do it again.

For example, once again you will experience the following things:

• Need to look at data monitoring every day
• Have you reached a bottleneck?
• When the time comes to change again, the scheduling and execution of the development operation and maintenance test business
• Business changes: It was agreed that the next quarter will be pushed forward, and the result will be carried out in advance to the next month. At this time, can the database be able to withstand it, and is there enough time for the transformation?

So, let's do it one step at a time.

Di, Qixi reminds you: Seeing this, if someone asks you about the usage scenarios of single-database multi-table and multi-database multi-table, you should know how to play it

2. Historical data processing

Let me first talk about the processing of historical data, which is relatively short.

The content here corresponds to the third point at the beginning of the article: For single tables that exist in history, and they do not need to become multiple tables, what should be done?

There are two ways to handle this.

We know that the historical data is in the user database, assuming that the business needs to be increased to 8 databases, and the new table needs to be in these 8 databases

2.1 Method 1

Add user_0、user_1、...、user_7 total 8 libraries, use the rename command to migrate the table of the user library to the user_0 library, and finally delete the user library.

The rename command is actually renaming to achieve the effect of cutting data, not copying. Of course, it is also possible to migrate data by means of replication, but it is useless here.

The reanme command is used as follows:

rename table user.table_name to user_0.table_name;

2.2 Method 2

Added user_0、user_1、...、user_7 total 8 libraries, the user library data remains unchanged, continue to use.

As for which method to choose, in most cases, I personally think it is fine, but if the request for the history table itself is very high, then you can consider using method 2 to avoid too much pressure on library 0.

This is the way I choose. When the user wants to access the history table, it is good to specify the route to the 0th database, and save the money of a database by the way, really fragrant

3. Change plan

The content of the plan will be based on the historical data processing method of method 1.

First of all, without considering any solutions, I will list the simplest operations to change to multiple databases and multiple tables in order:

1. Modify the configuration of the service connection database, and write business code
2. Add user 0-7 database
3. Migrate the old table data of the user database to the new user_0 database
4. Deployment service

But if the above method is followed, during the execution of steps 3 and 4, there will be problems if the user accesses the data in the original user database.

Specifically: the old data of the user library has been renamed and migrated to the user_0 library at this time, but because the deployment has not been completed, the configuration of the connection database has not been updated.

Therefore, the request will still go to the user database to query, resulting in no data, and subsequent business logic cannot continue to be executed sequentially.

Users will also wonder: "This place has data before coming in, why is it all empty now?"

Therefore, it is necessary to determine a reasonable upgrade plan to minimize the impact on business and users.

3.1 Option 1

This is the easiest way.

Look at the monitoring, choose when there is no traffic, perform db changes and service deployment.

Of course, monitoring is only a past situation, and there is no guarantee that traffic has not stopped since the day the function was launched.

So if you want to be more stable, you can send an announcement to inform users that the xx function will be maintained during the xxx time period and will not be accessible during the period.

If you have friends who play pesticides (Honor of Kings), you should be familiar with it. Every time you update the version, you need to stop the service. This is the effect.

Finally, after it's done, run regression tests and new feature tests to see if the functionality works.

If it is normal, then you can go to sleep, and if there is a problem, continue to change the bug to solve it;

If the assessment cannot be resolved by the deadline stated in the announcement, it can only be rolled back and the battle (ban) will be done another day.

PS: If the historical data needs to be sub-database and sub-table, it is best to carry out a comparison test of the amount of data. Because I do not involve sub-database and sub-table for historical data, this step is omitted.

3.2 Option 2

This solution will be much more complicated and the amount of development will be huge.

I only talk about the key steps here, and I can't write down the specific details. Because there are thousands of words more to write, and the length is too long, I guess not many people have the patience to read it.

Having said that, the biggest advantage of this plan is that the business functions do not need to be disabled, so there is no need to stay up late.

How to do it?

3.2.1 Historical single table data processing

1. First copy the existing data of the user library to the user_0 library.

2. Because the data in the user library will be modified and added. Therefore, after the replication is completed, the data still changes, so it is necessary to add double-write logic to ensure that the data in the user_0 database can also be synchronized to the changes.

3. For data read and write, both support switch control, which can control which database the data read and write is requested to.

4. After the service update is completed, compare the data consistency of the two libraries. After there is no problem, the switch control read and write data are requested to the user_0 library

3.2.2 Multi-table data processing with new functions

Because it is a new function, there is no need for special treatment.

Why do you say that?

Because the order in which we deploy services must be that the underlying services that operate the database are released first, and after the release is completed, the application services that use the underlying services are released.

Therefore, the application services as the business function entrance have not been released yet, and no new function data will reach the underlying service at this time.

If this order cannot be guaranteed, you want to open the function entry. After the user requests come in, the underlying service finds that the table cannot be found. Does it report an error directly?

Therefore, there will be the release order mentioned above. As long as the release order is correct, the data of this new function does not require special processing.

3.3 Comparison of pros and cons of schemes

In fact, the two schemes are complementary. The advantage of one scheme is to solve the shortcomings of the other scheme.

Qixi summarizes it in a table:

	advantage	shortcoming
Option One	Simple operation, no need to write complex code to ensure the normal execution of business when there is traffic	Tired, staying up late is too sour; some services will be disabled, affecting user experience
Option II	The business does not have to be deactivated and does not affect users	high development cost

Finally, you ask me which plan did I choose?

That must be the first plan, it's a big deal to stay up all night.

Otherwise, it is impossible to develop such a troublesome plan, and the schedule is so tight. It is impossible in this life. If there is really any problem, it will be dealt with manually, yyds

---

The article's first public account: Qixi is learning Java , and continues to output Java back-end dry goods.

If it helps you, can you give a like before leaving?