Modifying the table structure of a MySQL large table online

Due to a temporary requirement, you need to add a field to a table of more than 10 million online MySQL databases. This table is fully implemented as required during design and there are no additional Reserved fields.

Problem Description

Due to a temporary requirement, you need to add a field to a table of more than 10 million online MySQL databases. This table is fully implemented as required during design and there are no additional Reserved fields.

We know that if you want to execute the alter table operation in MySQL, MySQL will work by creating a temporary copy of the original TABLE. Modify the table structure on the copy, and then replace the new table with the original table. a lock table is generated. you can read data from the original table, the user's update and write operations will be locked, and the new table will be written after the new table is ready.
This is absolutely intolerable for tables with a large amount of online data, and because such online operations will take a long time, if the show processlist, several MySQL processes are locked. when too many MySQL processes exceed the number of MySQL processes allowed by a single server, other processes may be rejected.

What solutions can be used to solve this problem?

Solution 1: directly ALTER TABLE
This solution can only be said to be a solution that performs well in some non-real-time online scenarios or when the data volume is small.

Solution 2: simulate the operation of modifying the table structure in the database to implement the whole process at the non-database layer.

1. Data read/write splitting
2. Create a new table with the structure modified as needed
3. Modify the business logic, direct the read operation to the old table, and direct the write operation to the new table. If you do not read the old table, read the new table and write the old data to the new table. of course, this write operation is not required, we can perform a scheduled task in the background to synchronize the old data to the new table.

This solution has a major disadvantage. The business logic layer must be used together to migrate data and modify the business logic. if there are multiple machines, you need to modify the business logic one by one, it is time-consuming, but it is applicable to two major storage engines of MySQL.

Solution 3. facebook online schema change
There is no major difference between facebook's OSC and solution 2 in the overall process, but it introduces a trigger here and does not need to modify the business logic, at the database layer, two tables of new data are synchronized. The procedure is as follows:

1. Create new tables as needed
2. Create a trigger for the original table
3. All update operations on the original table will be updated to the new table by the trigger.
4. Copy data from the original table to the new table
5. Replace the new table with the old table

The osc solution of fb solves solution 2 from the database layer, but it only supports the InnoDB storage engine.

Solution 4: use another method to retain fields.
If everything can start from scratch, we may be able to add some redundant fields and add some redundant fields for each type. But I cannot go back!

Solution 5: use another method to add an extended table.
We did not modify it on the basis of the original table. we added the extended table to write the data of new fields to the extended table and modify the business logic. These fields are read from the new table.
Zhiqiang said that this is a typical design of the dimension table structure.
The problem has been solved temporarily. if these fields are frequently used in the future, they may have a certain impact on future maintenance or business.

Postscript
Based on the existing requirements, you only need to record new fields, so the extended table scheme is adopted.