Implementation of SQL Million-Million-Million-Scale Maximum Table Add Fields

In MySQL, when adding fields to large tables of millions or tens of millions, special care is required to avoid long-term table locking and potential performance issues. Here are a few recommended methods and strategies:

1. Add fields directly

In MySQL 8.0 and later, the operation of adding columns is usually online and does not lock tables for a long time. However, in MySQL 5.7 and below, adding column operations will result in a complete reconstruction of the table, thus locking the table.

ALTER TABLE your_table ADD COLUMN new_column datatype;

2. Use pt-online-schema-change

pt-online-schema-changeIt is a tool in Percona Toolkit that can modify the structure of a table online without locking the table for a long time.

pt-online-schema-change --alter "ADD COLUMN new_column datatype" D=database,t=your_table --execute

3. Partition table

If the table uses partitions, consider doing it separately on each partition to reduce the impact on the entire table.

4. Copy the table

It can be achieved by copying the table:

Create a new table and add the required new columns when created.
Copy the data from the old table to the new table.
When appropriate, rename the old table to the backup table and rename the new table to the original table.

-- 1. Create a new table
CREATE TABLE new_table LIKE your_table;
ALTER TABLE new_table ADD COLUMN new_column datatype;

-- 2. Copy the data
INSERT INTO new_table SELECT *, NULL AS new_column FROM your_table;

-- 3. Rename table
RENAME TABLE your_table TO backup_table, new_table TO your_table;

5. Add fields in step

If the added field does not need to be filled immediately, you can add the fields in step by step and fill the data step by step:

Add fields.
Step by step to populate the data for new fields with batch updates.

-- 1. Add fields
ALTER TABLE your_table ADD COLUMN new_column datatype;

-- 2. Update in batches
SET @batch_size = 10000;  -- Number of rows updated each time
SET @total_updated = 0;

DO
    SET @updated = (UPDATE your_table SET new_column = 'default_value' WHERE condition LIMIT @batch_size);
    SET @total_updated = @total_updated + @updated;
WHILE @updated &gt; 0;
END DO;

-- Output the total number of updated rows
SELECT @total_updated;

6. Ensure backup and recovery plans

Before making any major database structure changes, make sure you have a complete database backup and test your recovery plan.

These methods can help you add new fields to large tables without significantly affecting performance and normal operations. Choosing the appropriate method requires considering the specific database version, table size and business needs.

This is the article about how to add fields to SQL million and million tables. For more related content on adding fields to SQL large tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!