TencentDB for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Database Architecture
- Database Instance
- Kernel Features
- Kernel Version Release Notes
- Functionality Features
- Performance Features
- Parallel Query
- Security Features
- Stability Features
- Purchase Guide
- Getting Started
- Connecting to MySQL Instance
- Database Audit
- Audit Rule Template
- MySQL Cluster Edition
- Maintenance Management Instance
- Operation Guide
- Instance Management and Maintenance
- Instance Upgrade
- CPU Elastic Expansion
- Read-Only/Disaster Recovery Instances
- Database Proxy
- Database Proxy Kernel Features
- Database Proxy Management
- Automatic Read/Write Separation Feature
- Connection Pool Feature
- Other Features
- Account Management
- Database Management Center (DMC)
- Parameter Configuration
- Network and Security
- Backup and Rollback
- Restoring Database from Backup File
- Monitoring and Alarms
- Operation Logs
- Practical Tutorial
- Calling MySQL APIs in Python
- White Paper
- Performance White Paper
- Performance Test Report
- Troubleshooting
- Connections
- API Documentation
- Data Import APIs
- Rollback APIs
- Parameter APIs
- Making API Requests
- Instance APIs
- Database Proxy APIs
- Database Audit APIs
- Security APIs
- Account APIs
- Backup APIs
- Database APIs
- Monitoring APIs
- Log-related API
- FAQs
- Backup
- Connection and Login
- Instance Upgrade
- Performance and Memory
- Service Agreement
- Reference
Second-Level Column Addition
Last updated: 2024-11-27 11:20:24
Overview
The quick column addition feature allows you to quickly add columns to a big table by only modifying the data dictionary, which eliminates the need of data replication during column adding and greatly reduces the column adding time for big tables and the impact on the system.
Supported Versions
Kernel version: MySQL 5.7 20190830 and later.
Kernel version: MySQL 8.0 20200630 and later.
Use Cases
This feature is suitable for adding columns to a table with a high volume of data.
Performance Data
In tests with a table of 5 GB data, the time for adding a column is reduced from 40 seconds to below 1 second.
Instructions
Before the feature of adding columns in seconds is used, it is necessary to set the parameter innodb_fast_ahi_cleanup_for_drop_table to ON.
INSTANT ADD COLUMN syntax
Add the
algorithm=instant clause to ALTER TABLE to add a column as follows:ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
The
innodb_alter_table_default_algorithm parameter is added, which can be set to inplace or instant.
This parameter is inplace by default and can be configured to adjust the default algorithm of ALTER TABLE as follows:SET @@global.innodb_alter_table_default_algorithm=instant;
If no algorithm is specified, the default algorithm configured by this parameter will be used for
ALTER TABLE operations.Note：
The setting of the parameter innodb_alter_table_default_algorithm is only applicable to MySQL version 5.7. For MySQL version 8.0, the default algorithm for executing Alter Table operations in kernel subversion 20230630 and later is instant, with this parameter unable to be set.
Restrictions on INSTANT ADD COLUMN
A statement can contain only column addition operations.
A new column will be added to the end, and column order cannot be changed.
INSTANT ADD COLUMN is not supported in tables with the COMPRESSED row format.
INSTANT ADD COLUMN is not supported in tables with a full-text index.
INSTANT ADD COLUMN is not supported for temp tables.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No