Optimization solution for modifying table fields in MySQL

Requirement: Now you need to change the field name, add a field, delete another field, and change the field type.

Create the following table (original table ):
Create table month (
Id INT,
Week VARCHAR (10 ),
Day VARCHAR (10 ),
Hour VARCHAR (10 ),
Primary key (id)
);

The final table is as follows:
Create table month (
Id INT,
Week VARCHAR (10 ),
Today VARCHAR (20), -- change day VARCHAR (10) To today VARCHAR (20)
Minute VARCHAR (20), -- add the new field minute VARCHAR (20), and delete the field hour VARCHAR (10)
Primary key (id)
);

Solution 1:
Alter table month CHANGE day today VARCHAR (20 );
Alter table month drop column hour;
Alter table month add minute VARCHAR (20 );

Case 1 analysis:

The alter table operation in the MYSQL background is

1. Create a temporary table
2. Copy data to a temporary table
3. Delete the original table
4. Rename the temporary table to the original table name

The main time consumption is "copying data to a temporary table ".

When copying data to a temporary table, factors affecting the time include index maintenance, data insertion, and lock.

Therefore, as the number of fields to be modified increases, you must repeat the preceding operations. When the data volume is large, it takes longer to copy the data to the temporary table.

Case 2:

Create table temp_month (
Id INT,
Week VARCHAR (10 ),
Today VARCHAR (20 ),
Minute VARCHAR (20) DEFAULT '00'
);

Insert into temp_month (id, week, today) (SELECT id, week, day FROM month );

Drop table month;

Alter table temp_month rename to month;

Alter table month add primary key (id );

Case 2 analysis:

Create a temporary table by yourself, modify the fields to be modified at one time, and use insert to "Copy Data" only once for all modified fields ". then change the table name (you do not need to scan the entire table to change the table name)

Add the primary key at the end (This avoids the maintenance of the index for each row of data inserted during the table copy process. The last addition of the index is one-time maintenance of the entire table)

========================================================== ========================================================== ===

The above two schemes show that with the increase of the field to be changed, the speed of solution 2 is monotonically increasing than that of solution 1.

If solution 1 is used: We recommend that you set the tmp_table_size parameter. this parameter is used to set the size of a temporary table.

We recommend that you set the sort_buffer_size parameter. this parameter is used for sorting cache. this parameter is used for index maintenance.

If you use the table storage engine InnoDB. innodb_buffer_pool_size (windows: % mysql %/my. ini. linux:/etc/mysql/my. cnf) to speed up the modification.

Especially for tables with a large amount of data, we recommend that you set the size to 50%-80% of the memory size.