在MySQL中快速插入大量数据的最佳方法是什么? [英] What is the best way to achieve speedy inserts of large amounts of data in MySQL?

问题描述

我已经用C语言编写了一个程序来解析大型XML文件，然后使用insert语句创建文件.其他一些过程会将文件提取到MySQL数据库中. 这些数据将用作索引服务，以便用户可以轻松找到文档.

I have written a program in C to parse large XML files and then create files with insert statements. Some other process would ingest the files into a MySQL database. This data will serve as a indexing service so that users can find documents easily.

我选择InnoDB是因为它具有行级锁定的能力. C程序将根据给定的调用生成500到500万个插入语句.

I have chosen InnoDB for the ability of row-level locking. The C program will be generating any where from 500 to 5 million insert statements on a given invocation.

使所有这些数据尽快进入数据库的最佳方法是什么?还要注意的另一件事是，数据库位于单独的服务器上.值得将文件移到该服务器上以加快插入速度吗?

What is the best way to get all this data into the database as quickly as possible? The other thing to note is that the DB is on a separate server. Is it worth moving the files over to that server to speed up inserts?

该表将不会真正更新，但是行将被删除.

This table won't really be updated, but rows will be deleted.

推荐答案

• 使用mysqlimport工具或LOAD DATA INFILE命令.
• 暂时禁用不需要数据完整性的索引

这篇关于在MySQL中快速插入大量数据的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！