All Products
Search
Document Center

AnalyticDB for MySQL:Best practices on data changes

Last Updated:Dec 26, 2023

This topic describes how to update, delete, and import data.

You can execute INSERT and REPLACE INTO statements to write data in batch mode and improve data write performance. When you write data, take note of the following items:

  • The number of rows of data written by each INSERT or REPLACE INTO statement can be greater than 1,000. However, the total amount of data to be written cannot exceed 16 MB.

  • When data is written in batch mode, the write latency is lower than that when data is written row by row.

  • If an error occurs, you must retry operations to make sure that data is written. The retry operations may cause data to be repeatedly written. Then, you can remove the duplicate rows based on the primary key of the table.

Update data

AnalyticDB for MySQL provides multiple methods to update data. We recommend that you use the following methods:

  • If data is updated at a high frequency and overwritten for each row based on the primary key, you can execute the REPLACE INTO statement to batch update data.

  • If data is updated at a low frequency based on the primary key, you can execute the REPLACE INTO or UPDATE statement to update a single row of data.

  • If data is updated at a low frequency based on conditions, you can execute the UPDATE statement to update data.

Delete data, partitions, or tables

AnalyticDB for MySQL provides multiple methods to delete data. We recommend that you use the following methods:

  • If data is deleted at a low frequency based on the primary key, you can execute the DELETE FROM WHERE PK='xxx' statement to delete data.

  • If data is deleted at a low frequency based on conditions, you can execute the DELETE statement to delete data.

  • Execute the TRUNCATE TABLE db_name.table_name PARTITION partition_name statement to delete a list partition.

  • Execute the TRUNCATE TABLE db_name.table_name statement to delete a table that contains all list partitions.

Batch import data in real time

  • Batch import: This method is applicable to scenarios where you need to import large amounts of data. During the import process, you can query the original data. After the import is complete, you can query the new data in AnalyticDB for MySQL. If the import fails, you can roll back to the original data. The original data can still be queried.

    For example, when you import data from MaxCompute or Object Storage Service (OSS) to AnalyticDB for MySQL, we recommend that you execute the INSERT OVERWRITE SELECT statement to batch import data. For more information, see Use external tables to import data from OSS to AnalyticDB for MySQL.

    Note

    When you batch import data from a single table, the import tasks are executed in series. When you batch import data from multiple tables, the import tasks are executed in parallel. By default, two tasks are executed in parallel.

    For example, when you import data from MaxCompute to AnalyticDB for MySQL, data in different partitions of a single table in MaxCompute is queued and imported in series.

  • Real-time import: This method is applicable to scenarios where small amounts of data is imported, such as millions of table data. For example, when you import data from ApsaraDB RDS for MySQL or self-managed MySQL databases that are hosted on Elastic Compute Service (ECS) to AnalyticDB for MySQL, we recommend that you execute the INSERT INTO statement. For more information, see Use external tables to import data to Data Warehouse Edition.

Note

When you import data to AnalyticDB for MySQL, the import task uses resources in AnalyticDB for MySQL. If you want to import data during the data query process, we recommend that you perform the import operation at low queries per second (QPS).