How to Optimize MySQL Data Import using Load Data Infile for Tables with Keys?

Optimizing MySQL Data Import using Load Data Infile

Importing large datasets into MySQL tables can be a time-consuming task. When using the "Load data infile..." command, balancing speed and data integrity can be challenging. This question explores ways to improve the performance of such imports, particularly for tables with keys.

Increasing Import Speed

The primary concern mentioned in the question is the slow key creation after importing data without keys. To address this:

• Sort the CSV File: Arrange the data in the CSV file in the order of the table's primary key. This helps Innodb utilize its clustered primary key structure for faster loading.
• Disable Constraints: Temporarily disable unique and foreign key checks to reduce overhead during loading. Use the commands set unique_checks = 0; and set foreign_key_checks = 0;.
• Disable Binlogging: Turn off binary logging (set sql_log_bin=0;) to bypass logging for improved performance.
• Split Large Files: Divide large CSV files into smaller chunks and load them individually. This can reduce memory consumption and speed up the process.

Speeding Up Key Creation

After loading data, rebuilding the keys can be a lengthy operation. Here are some suggestions:

• Bulk Insert: Use the BULK INSERT clause within the LOAD DATA INFILE statement to import data with keys in one go.
• Adaptive Hash Index: Enable the adaptive hash index (innodb_adaptive_hash_index=ON) to speed up index creation for frequently used queries.
• Parallel Indexing: If possible, use parallel indexing (innodb_parallel_alter=ON) to create multiple threads for index creation, potentially reducing the time taken.

Terminating Slow Queries

To terminate slow queries that continue running after being terminated, consider:

• Retry Connecting: Disconnect and reconnect to the MySQL server to gracefully end the query.
• Use Query ID: Identify the query's ID using SHOW PROCESSLIST and kill it using KILL [QUERY_ID].
• Restart MySQL: As a last resort, restarting MySQL will terminate all running queries. However, it can cause service disruption.

The above is the detailed content of How to Optimize MySQL Data Import using Load Data Infile for Tables with Keys?. For more information, please follow other related articles on the PHP Chinese website!