Data modification best practices
AnalyticDB for MySQL supports multiple approaches to updating, deleting, and importing data. Choose the right method based on your data volume and update frequency to avoid performance issues and resource contention.
Batch write guidelines
Write data in batches using INSERT or REPLACE INTO statements rather than row by row. Batch writes reduce latency and improve throughput.
Constraints:
-
Each INSERT or REPLACE INTO statement can write more than 1,000 rows, but the total data per batch cannot exceed 16 MB.
-
If a write fails, retry the operation. Retries may produce duplicate rows — remove them using the table's primary key.
Update data
Choose an update method based on update frequency and whether you are targeting rows by primary key or by condition.
| Scenario | Recommended method |
|---|---|
| High-frequency updates, overwriting rows by primary key | REPLACE INTO (batch) |
| Low-frequency updates by primary key | REPLACE INTO or UPDATE (single row) |
| Low-frequency updates by condition | UPDATE |
For syntax details, see REPLACE INTO and UPDATE.
Delete data, partitions, or tables
| Scenario | Recommended method |
|---|---|
| Low-frequency delete by primary key | DELETE FROM WHERE PK='xxx' |
| Low-frequency delete by condition | DELETE |
| Delete a list partition | TRUNCATE TABLE db_name.table_name PARTITION partition_name |
| Delete all list partitions (entire table) | TRUNCATE TABLE db_name.table_name |
Import data
Choose between batch import and real-time import based on your data volume.
Batch import
Use batch import for large datasets — for example, when importing from MaxCompute or Object Storage Service (OSS).
Run INSERT OVERWRITE SELECT to batch import data. During the import, the original data remains queryable. If the import fails, the system rolls back and the original data is still accessible. After the import completes, the new data becomes queryable.
For more information, see Use external tables to import data from OSS to AnalyticDB for MySQL.
For a single table, import tasks run in series. For multiple tables, tasks run in parallel — two tasks by default. For example, when importing from MaxCompute, different partitions of a single table are queued and imported in series.
Real-time import
Use real-time import for smaller datasets, such as millions of rows. This method works well for importing from ApsaraDB RDS for MySQL or self-managed MySQL databases hosted on Elastic Compute Service (ECS).
Run INSERT INTO to import data in real time. For more information, see Use external tables to import data to Data Warehouse Edition.
Import tasks consume resources in AnalyticDB for MySQL. If you need to import data while queries are running, keep the import at a low queries per second (QPS) to avoid resource contention.