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

问题描述

我用 C 语言编写了一个程序来解析大型 XML 文件，然后使用插入语句创建文件.其他一些进程会将文件摄取到 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屋！