MySQL 中的 Load Data Infile 提高性能方法

在本文中，我们将介绍如何使用 MySQL 中的 Load Data Infile 来提高数据导入性能的方法。

阅读更多：MySQL 教程

什么是 Load Data Infile？

Load Data Infile 是 MySQL 提供的一种快速导入大量数据的方法。使用 Load Data Infile 不仅可以降低导入数据所需的时间，还可以减少服务器资源的消耗。这是因为 Load Data Infile 只需要进行一次的 IO 操作即可完成数据的导入。

下面的代码展示了如何使用 Load Data Infile 导入 CSV 文件：

LOAD DATA LOCAL INFILE 'file_path' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

• file_path：CSV 文件的路径。
• table_name：导入数据的表格名称。
• FIELDS TERMINATED BY ‘,’：CSV 文件中的字段间分割符。
• OPTIONALLY ENCLOSED BY ‘”‘：CSV 文件中的字段被包含符号。
• LINES TERMINATED BY ‘\n’：CSV 文件中的每一行末尾符号。
• IGNORE 1 ROWS：第一行为无效行，需要忽略。

如何优化 Load Data Infile 性能？

虽然 Load Data Infile 已经是一种非常快速的数据导入方式，但仍然有一些手段可以在特定场景下进一步提高其性能。

1. 禁用索引

导入数据时，为了保证数据的完整性和准确性，MySQL 会实时更新相应的索引。但这也会导致导入速度变慢。因此，在导入大量数据时可以考虑禁用索引，待数据导入完成后再启用索引即可。

禁用索引的方式如下：

ALTER TABLE table_name DISABLE KEYS;

数据导入完成后，启用索引的方式如下：

ALTER TABLE table_name ENABLE KEYS;

2. 提高并发

MySQL 的导入操作是 I/O 密集型任务，如果能利用多个 CPU 核心并行导入，必定能加快导入速度。在实际情况中，可以通过控制线程数量和设置合适的批量大小来提高并发性。

通过设置导入线程数来提高并发度，示例代码如下：

mysql --local-infile -h 127.0.0.1 -uroot -p -D test_db -e "set @@global.innodb_flush_log_at_trx_commit=2;set @@global.innodb_doublewrite=0;SET foreign_key_checks = 0;set unique_checks = 0;set max_heap_table_size = 4294967296;set bulk_insert_buffer_size = 4294967296;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;set groups_for_threads=@oldgroup;set global thread_pool_size=4;set global thread_pool_stall_limit=600;set session thread_pool_group=@oldgroup;select sleep(2);select /*+engine=memory*/ count(*) from college_full_patient_201802" -vvv

mysql --local-infile -h 127.0.0.1 -uroot -p -D test_db -e 'LOAD DATA LOCAL INFILE "/data/dump/COLLEGE_FULL_PATIENT_201802_MISSING_CATEGORY_F_NEW" INTO TABLE college_full_patient FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" LINES TERMINATED BY "\n" IGNORE 1 ROWS' -vvv

3. 使用 SSD

SSD 相比传统的机械硬盘在 IO 操作上具有明显的优势，因此在使用 Load Data Infile 导入大量数据时，使用 SSD 作为数据存储介质，可以大幅提升导入速度。

4. 使用默认字符集

在导入数据时，最好使用服务器默认的字符集，因为这样可以避免字符集转换的额外开销。如果导入的数据文件使用的字符集和服务器默认字符集不一致，MySQL 还需要进行字符集转换，这会降低性能。

要查看服务器的默认字符集，可以执行以下命令：

SHOW VARIABLES LIKE '%char%';

5. 避免使用触发器

MySQL 的触发器会在数据插入、更新和删除时被触发，这会导致数据导入时的性能问题。如果可以的话，尽量避免使用触发器。

6. 内存临时表

通过使用内存临时表可以减少对磁盘的访问，从而提高数据导入操作的速度。

创建内存临时表可以使用以下语句：

CREATE TEMPORARY TABLE tmp_table_name ENGINE=MEMORY SELECT * FROM original_table_name WHERE 1=2;

其中，tmp_table_name 为临时表名称，original_table_name 为原始表名称。

7. 调整 innodb_buffer_pool_size 参数

innodb_buffer_pool_size 是 MySQL 中控制 InnoDB 存储引擎缓存区域大小的参数。通过适当调整该参数，可以使 MySQL 更高效地使用内存，进而提高数据导入性能。

总结

通过使用 Load Data Infile 和优化导入性能手段，可以在 MySQL 中快速地导入大量数据。在实际使用过程中，可以根据数据大小、导入速度等因素选择相应的优化方式，从而达到最佳的性能表现。