Continue answering questions from water friends of knowledge Planet.

Problem area: How can table structure changes be smoothly implemented in a scenario with a large amount of data and high concurrency?

Voiceover, in general, refers to adding attributes to a table because:

(1) If column is minus, the upgrade program does not need to use it;

(2) If column is modified, program compatibility is prone to problems;

First, let’s take a look at some common solutions.

** Solution 1: Modify the table structure online. Alter table add column

In the case of a large amount of data, locking the table takes a long time, resulting in denial of service, which is generally not feasible.

Scheme 2: Extend attributes by adding tables and query by foreign key JOIN.

For example, t_user(uid, c1, c2, c3) can extend attributes by adding a table: t_user_ex(uid, C4, C5, c6) with a large amount of data, join performance is poor and generally not feasible.

The third option is to extend it by adding tables and masking the underlying complexity through views.

Similarly, views are inefficient and generally not used. Voiceover: __ at least 58 home is forbidden to use view 58 Home MySQL Catch-upgrade edition.

Plan four: Beat up the product manager and stop her from modifying the requirements.

.

Scheme 5: Reserve some reserved fields in advance and add them to reuse them.

This scheme is feasible, but if too much is reserved, space will be wasted.

** Pt-online-schema-change **

For MySQL, this is a relatively mature scheme, used by the majority of companies. _ Voiceover: _ The Internet companies I’ve worked with all use MySQL for their databases.

The following uses user table extension as an example to describe the internal principles and steps of this tool.

Assumptions:

user(uid, name, passwd)

To extend to: user(uid, name, passwd, age, sex)

Create a new table with expanded fields:

user_new(uid, name, passwd, age, sex)

Voice-over: This is the expanded table.

Create triggers on user. All insert/delete/update operations on user will perform the same operation on user_new.

Step 3, insert data from user into user_new in batches until data migration is complete.

Step 4 drop the trigger and drop the table. Rename table user_new to user;

Expansion fields complete, the whole process does not need to lock the table, can continue to provide external services.

Note the following during operation:

(1) In the process of change, the most important thing is to deal with conflicts. As a principle, the new data of trigger shall prevail, which requires that the table to be migrated must have primary keys (this requirement is basically met);

(2) In the process of change, write operations need to establish triggers, so if the original table has many triggers, the scheme will not work (triggers are generally prohibited for online business with high concurrency of Internet big data); (3) The establishment of the trigger will affect the performance of the original table, so this operation must be carried out in the flow low peak period;

Pt-online-schema-change is a necessary tool for DBAs. It is relatively mature and widely used in Internet companies. For more details, you can also Google it.

Any architectural design that is detached from the business is a hooligan.

I welcome your questions.

Voiceover: There are too many questions to answer.

Research: How does your company extend the table structure?