background
Since the platform coins need to change from the previous shaping type to the decimal type that supports decimal, the field type of the order table needs to be modified.
Order table: There are more than 20 million data at present, and the amount of data is still relatively large. There are 3 fields that need to be modified. The order table is called order_info
Solution 1
Use SQL native statements directly
alter table order_info MODIFY column coin_count decimal(6, 1);
It took more than 500 seconds to complete the execution, and it was withdrawn. Because the lock table user cannot place an order, the impact may be relatively large, so the solution is abandoned
Solution 2
The main steps are as follows:
1: Create a new table with the name order_info_new, and the structure is copied from order_info, but the three fields have been changed from int to decimal. This step will not lock the table.
2: Change the original order_info table name to order_info_old, and change order_info_new to order_info. This step is also very fast and the table will not be locked.
3: Use the command line statements that come with mysql to migrate the previous data to the new table. The statements are as follows, just wait for completion:
INSERT INTO order_info SELECT * FROM order_info_old
Note: When creating a table, you need to annotate the problem of id. If it is self-increase, the self-increase id of the new table should be larger to prevent id conflicts during the switching process.
During the third step of execution, mysql is enabled by default, so the data in the execution process will not be queried. You need to wait until the execution is completed to see if there are any issues that need to be considered in this regard.
choose
In the end, we naturally used the second solution. The whole process was not too slow. It took more than half an hour to migrate data of more than 20 million yuan. Alibaba's RDS still feels like something is something.
Other optimizations
When dealing with this problem, we also dealt with the unreasonable design of the order table, and mainly dealt with two problems.
1: Unreasonable design of data field types
2: Partial migration of historical data
Type optimization
`delivery_status` int DEFAULT '1' COMMENT 'Shipping status 1: Not shipped 2: Delivered' There are many such inappropriate field types in the order table. It is unreasonable because this field only has 2 values, so it should be changed to
`delivery_status` tinyint DEFAULT '1' COMMENT 'Shipping status 1: Not shipped 2: Has shipped', this is more reasonable
The effect is quite obvious after the modification. One data can save 3 bytes, and more than 20 million pieces save about 12G of space. This is a very meaningful thing.
Historical data migration
The data of order_info is in the same table from 2020 to 2024, but in fact, many historical data will not be checked, so it is very simple to migrate the data from 2020 to 2021 to a new table.
1: The original order_info only retains data from 2022 to the present
2: Create another order_info_history data table, the structure is exactly the same
3: Transfer all the data from order_info before 2022 to order_info_history
The specific operation commands are as follows:
INSERT INTO order_info SELECT * FROM order_info_old where create_time >= 1640966400000; INSERT INTO order_info_history SELECT * FROM order_info_old where create_time < 1640966400000;
Summarize
This is the end of this article about the two solutions for modifying fields of Mysql large tables. For more related contents of modified fields of Mysql large tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!