使用大量数据优化插入

Question

我在一个简单的java控制台应用程序中使用PreparedStatement从InputStream中加载大量数据。

这是代码：

public void readStopTimes(CSVReader reader) throws IOException, SQLException {
        String insertSql = "INSERT INTO stop_times VALUES (null, ?, ?, ?, ?, ?)";
        PreparedStatement statement = db.prepareStatement(insertSql);
        String [] nextLine;
        long i = 0;
        Chronometer chronometer = new Chronometer();
        while ((nextLine = reader.readNext()) != null) {
            if(i++ != 0) {
                statement.setString(1, nextLine[0]);
                if(nextLine[1].isEmpty())
                    statement.setNull(2, Types.TIME);
                else
                    statement.setTime(2, Time.valueOf(nextLine[1]));
                if(nextLine[2].isEmpty())
                    statement.setNull(3, Types.TIME);
                else
                    statement.setTime(3, Time.valueOf(nextLine[2]));
                statement.setString(4, nextLine[3]);
                statement.setInt(5, Integer.parseInt(nextLine[4]));
                statement.addBatch();
            }
            if(i++ % 1000 == 0) {
                statement.executeBatch();
            }
            if(chronometer.count() > 5000) {
                chronometer.restart();
                log.debug("Analyzed {} rows", i);
            }
        }
        statement.executeBatch();
        db.commit();
    }

每1000个插入，我执行批处理，每5秒我打印一个日志。

从日志中可以清楚地看到，这个算法在开始时运行得非常快，在前25秒中总共计算了400多万行，然后减速，在5秒内只向批处理中添加了2行。

我需要插入超过500万行，您有更快的选择吗？

Answer 1

• 禁用mysql中的查询缓存
• innodb_flush_log_at_trx_commit = 2，或者如果您能够确保mysql不崩溃，而不是使它成为innodb_flush_log_at_trx_commit = 0。
• 如果启用复制，请执行sync_binlog = 0禁用bin日志的同步。

您可以尝试通过MySql命令将CSV文件直接放入Load data infile . . . . .，这是非常快的。

Answer 2

尝试在statement.executeBatch()之后出现；而不是在末尾。这应该允许在mysql对插入进行跟踪时将插入刷新，以便在必要时可以将它们全部回滚。

Answer 3

尝试在多个批处理中添加数据，而不是单个批处理。每批完成后提交，并跟踪处理后的数据。也可以使用暂存表将数据添加到表中，一旦完成，就重命名表，如果失败，请重新启动或从保存点开始。