SoFunction
Updated on 2025-03-04

The problem of adding fields in MySql is slow and solving

MySql data table is slow to add fields

Since there is a large amount of data in the database table, adding fields to the table will be very slow.

Therefore, for tables with large amounts of data, the method of adding all or modifying fields is as follows:

1. Create the same table first

create table new_tb like tb_old;

2. Modify the fields that create the table

alter table new_tb add COLUMN new_column varchar(32) DEFAULT null;

3. Insert the original data into the new data table

insert into new_tb (Fields) select Fields from tb_old;

It should be noted here that your insert field is consistent with the fields you query, and the corresponding order needs to be consistent. If you just modify the properties of the field, or add an index, and the number of fields is useless to change, then just write it like this.

insert into new_tb select * from tb_old;

4. Delete the original data table and rename the new data table:

drop table tb_old;
alter table new_tb rename to tb_old;

MySql large table plus fields problem

Scene

Now there is a data volume of about 4 billion, and there are 10 libraries, with a total of 80 tables, with a data volume of about 50 million per table.

This table continuously receives data through an MQ interface, and the number of new or updated data is about hundreds of thousands every day.

Now, you need to add a new field X to this table, and all of them are stored in the database of the other party's system. And their new field has always existed. It's just that our system has not connected this field.

Note: The use of the original service that checks this table during the addition of fields cannot be affected.

Q: How to add it.

First of all, you must not add fields directly on the table. The amount of data is too large, and you can’t just add fields directly. SQL can’t run overnight. It will affect the use of query services

The plan is as follows

  • The first step is to create a new table_new below the old table's _old in the same database location. The field structure is exactly the same, but there is a new field X.
  • The second step is to let the other party’s system push the old 4 billion data to hive and make preparations. The time node is recorded as 0:00 on January 1, and it will be pushed up for about three days;
  • The third step is to establish a new interface before this, and a temporary table new_temporary starts receiving data to the temporary table from 0:00 on January 1;
  • Part 4: Start the data pull task flow, pull data from hive in the middle of the night, pull down the new table, remember to increase max_binlog_cache_size when pulling, otherwise it will burst into storage once, and it will be pulled in about 10 days, and the deadline will be 0:00 on January 15th. (This time node needs to be used)
  • Step 5: After pulling the data, start a timed task and update the data received from the temporary table new_temporary to the new table_new. The update condition is that only updates the data before 0:00 on January 15th
  • Step 6: Switch the interface switch at the same time, turn off the interface connecting to the temporary table, and open the interface connecting to the new table to access the data. At the same time, the interface to old cousin cannot be closed, so as to prevent missing data from being sent back at any time.
  • Step 7: Modify the old query methods of all query old tables in the system, change them to query new tables, and use redis to add switches. Online function.
  • Step 8: Turn on the switch and use the new table. If there is a missing data (a small amount), this reminds the other party to reissue the data and gradually complete the data.
  • Step 9: After the system runs smoothly, turn off the interface that connects old data to old table, delete the temporary table new_temporary, and delete the old table_old

Insights:

It was not too big to add a field, but because the data volume was large and the availability was guaranteed, a set of solutions was still needed to do this.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.