从 CSV 加载时 PostgreSQL/JooQ 批量插入性能问题；我该如何改进流程？

For 这个项目，我打算制作一个 Web 版本，现在正在制作一个 PostgreSQL (9.x) 后端，Web 应用程序将从该后端进行查询。

现在，跟踪器会生成一个包含两个 CSV 的 zip 文件，在运行时将其加载到 H2 数据库中，其架构如下（是的，我知道 SQL 可以写得更好一点）：

create table matchers (
    id integer not null,
    class_name varchar(255) not null,
    matcher_type varchar(30) not null,
    name varchar(1024) not null
);

alter table matchers add primary key(id);

create table nodes (
    id integer not null,
    parent_id integer not null,
    level integer not null,
    success integer not null,
    matcher_id integer not null,
    start_index integer not null,
    end_index integer not null,
    time bigint not null
);

alter table nodes add primary key(id);
alter table nodes add foreign key (matcher_id) references matchers(id);
create index nodes_parent_id on nodes(parent_id);
create index nodes_indices on nodes(start_index, end_index);

现在，由于 PostgreSQL 数据库将能够处理多个跟踪，因此我必须再添加一张表； PostgreSQL 后端的模式看起来像这样（也低于平均 SQL 警报；此外，在parse_info表，该content列包含解析的文件的全文，在 zip 文件中它是单独存储的）：

create table parse_info (
    id uuid primary key,
    date timestamp not null,
    content text not null
);

create table matchers (
    parse_info_id uuid references parse_info(id),
    id integer not null,
    class_name varchar(255) not null,
    matcher_type varchar(30) not null,
    name varchar(1024) not null,
    unique (parse_info_id, id)
);

create table nodes (
    parse_info_id uuid references parse_info(id),
    id integer not null,
    parent_id integer not null,
    level integer not null,
    success integer not null,
    matcher_id integer not null,
    start_index integer not null,
    end_index integer not null,
    time bigint not null,
    unique (parse_info_id, id)
);

alter table nodes add foreign key (parse_info_id, matcher_id)
    references matchers(parse_info_id, id);
create index nodes_parent_id on nodes(parent_id);
create index nodes_indices on nodes(start_index, end_index);

现在，我正在做的是将现有的 zip 文件插入到 postgresql 数据库中；我正在使用 JooQ 及其CSV 加载 API.

过程有点复杂...以下是当前步骤：

• 生成一个UUID；
• 我从 zip 中读取了必要的信息（解析日期、输入文本）并将记录写入parse_info table;
• 我创建了 CSV 的临时副本，以便 JooQ 加载 API 能够使用它（请参阅代码摘录后了解原因）；
• 我插入所有匹配器，然后插入所有节点。

这是代码：

public final class Zip2Db2
{
    private static final Pattern SEMICOLON = Pattern.compile(";");
    private static final Function<String, String> CSV_ESCAPE
        = TraceCsvEscaper.ESCAPER::apply;

    // Paths in the zip to the different components
    private static final String INFO_PATH = "/info.csv";
    private static final String INPUT_PATH = "/input.txt";
    private static final String MATCHERS_PATH = "/matchers.csv";
    private static final String NODES_PATH = "/nodes.csv";

    // Fields to use for matchers zip insertion
    private static final List<Field<?>> MATCHERS_FIELDS = Arrays.asList(
        MATCHERS.PARSE_INFO_ID, MATCHERS.ID, MATCHERS.CLASS_NAME,
        MATCHERS.MATCHER_TYPE, MATCHERS.NAME
    );

    // Fields to use for nodes zip insertion
    private static final List<Field<?>> NODES_FIELDS = Arrays.asList(
        NODES.PARSE_INFO_ID, NODES.PARENT_ID, NODES.ID, NODES.LEVEL,
        NODES.SUCCESS, NODES.MATCHER_ID, NODES.START_INDEX, NODES.END_INDEX,
        NODES.TIME
    );

    private final FileSystem fs;
    private final DSLContext jooq;
    private final UUID uuid;

    private final Path tmpdir;

    public Zip2Db2(final FileSystem fs, final DSLContext jooq, final UUID uuid)
        throws IOException
    {
        this.fs = fs;
        this.jooq = jooq;
        this.uuid = uuid;

        tmpdir = Files.createTempDirectory("zip2db");
    }

    public void removeTmpdir()
        throws IOException
    {
        // From java7-fs-more (https://github.com/fge/java7-fs-more)
        MoreFiles.deleteRecursive(tmpdir, RecursionMode.KEEP_GOING);
    }

    public void run()
    {
        time(this::generateMatchersCsv, "Generate matchers CSV");
        time(this::generateNodesCsv, "Generate nodes CSV");
        time(this::writeInfo, "Write info record");
        time(this::writeMatchers, "Write matchers");
        time(this::writeNodes, "Write nodes");
    }

    private void generateMatchersCsv()
        throws IOException
    {
        final Path src = fs.getPath(MATCHERS_PATH);
        final Path dst = tmpdir.resolve("matchers.csv");

        try (
            final Stream<String> lines = Files.lines(src);
            final BufferedWriter writer = Files.newBufferedWriter(dst,
                StandardOpenOption.CREATE_NEW);
        ) {
            // Throwing below is from throwing-lambdas
            // (https://github.com/fge/throwing-lambdas)
            lines.map(this::toMatchersLine)
                .forEach(Throwing.consumer(writer::write));
        }
    }

    private String toMatchersLine(final String input)
    {
        final List<String> parts = new ArrayList<>();
        parts.add('"' + uuid.toString() + '"');
        Arrays.stream(SEMICOLON.split(input, 4))
            .map(s -> '"' + CSV_ESCAPE.apply(s) + '"')
            .forEach(parts::add);
        return String.join(";", parts) + '\n';
    }

    private void generateNodesCsv()
        throws IOException
    {
        final Path src = fs.getPath(NODES_PATH);
        final Path dst = tmpdir.resolve("nodes.csv");

        try (
            final Stream<String> lines = Files.lines(src);
            final BufferedWriter writer = Files.newBufferedWriter(dst,
                StandardOpenOption.CREATE_NEW);
        ) {
            lines.map(this::toNodesLine)
                .forEach(Throwing.consumer(writer::write));
        }
    }

    private String toNodesLine(final String input)
    {
        final List<String> parts = new ArrayList<>();
        parts.add('"' + uuid.toString() + '"');
        SEMICOLON.splitAsStream(input)
            .map(s -> '"' + CSV_ESCAPE.apply(s) + '"')
            .forEach(parts::add);
        return String.join(";", parts) + '\n';
    }

    private void writeInfo()
        throws IOException
    {
        final Path path = fs.getPath(INFO_PATH);

        try (
            final BufferedReader reader = Files.newBufferedReader(path);
        ) {
            final String[] elements = SEMICOLON.split(reader.readLine());

            final long epoch = Long.parseLong(elements[0]);
            final Instant instant = Instant.ofEpochMilli(epoch);
            final ZoneId zone = ZoneId.systemDefault();
            final LocalDateTime time = LocalDateTime.ofInstant(instant, zone);

            final ParseInfoRecord record = jooq.newRecord(PARSE_INFO);

            record.setId(uuid);
            record.setContent(loadText());
            record.setDate(Timestamp.valueOf(time));

            record.insert();
        }
    }

    private String loadText()
        throws IOException
    {
        final Path path = fs.getPath(INPUT_PATH);

        try (
            final BufferedReader reader = Files.newBufferedReader(path);
        ) {
            return CharStreams.toString(reader);
        }
    }

    private void writeMatchers()
        throws IOException
    {
        final Path path = tmpdir.resolve("matchers.csv");

        try (
            final BufferedReader reader = Files.newBufferedReader(path);
        ) {
            jooq.loadInto(MATCHERS)
                .onErrorAbort()
                .loadCSV(reader)
                .fields(MATCHERS_FIELDS)
                .separator(';')
                .execute();
        }
    }

    private void writeNodes()
        throws IOException
    {
        final Path path = tmpdir.resolve("nodes.csv");

        try (
            final BufferedReader reader = Files.newBufferedReader(path);
        ) {
            jooq.loadInto(NODES)
                .onErrorAbort()
                .loadCSV(reader)
                .fields(NODES_FIELDS)
                .separator(';')
                .execute();
        }
    }

    private void time(final ThrowingRunnable runnable, final String description)
    {
        System.out.println(description + ": start");
        final Stopwatch stopwatch = Stopwatch.createStarted();
        runnable.run();
        System.out.println(description + ": done (" + stopwatch.stop() + ')');
    }

    public static void main(final String... args)
        throws IOException
    {
        if (args.length != 1) {
            System.err.println("missing zip argument");
            System.exit(2);
        }

        final Path zip = Paths.get(args[0]).toRealPath();

        final UUID uuid = UUID.randomUUID();
        final DSLContext jooq = PostgresqlTraceDbFactory.defaultFactory()
            .getJooq();

        try (
            final FileSystem fs = MoreFileSystems.openZip(zip, true);
        ) {
            final Zip2Db2 zip2Db = new Zip2Db2(fs, jooq, uuid);
            try {
                zip2Db.run();
            } finally {
                zip2Db.removeTmpdir();
            }
        }
    }
}

现在，这是我的第一个问题...它比加载到 H2 慢得多。以下是包含 620 个匹配器和 45746 个节点的 CSV 的时序：

Generate matchers CSV: start
Generate matchers CSV: done (45.26 ms)
Generate nodes CSV: start
Generate nodes CSV: done (573.2 ms)
Write info record: start
Write info record: done (311.1 ms)
Write matchers: start
Write matchers: done (4.192 s)
Write nodes: start
Write nodes: done (22.64 s)

给予或接受，并忘记编写专门的 CSV 的部分（见下文），即 25 秒。将其加载到动态的、基于磁盘的 H2 数据库中需要不到5秒!

我遇到的另一个问题是我必须编写专用的 CSV；看来 CSV 加载 API 在接受的内容方面并不真正灵活，例如，我必须转动这一行：

328;SequenceMatcher;COMPOSITE;token

进入这个：

"some-randome-uuid-here";"328";"SequenceMatcher";"COMPOSITE";"token"

但我最大的问题实际上是这个拉链非常小。例如，我有一个 zip，其中不是 620 个匹配器，而是 1532 个匹配器，也不是 45746 个节点，而是超过3400万个节点;即使我们忽略 CSV 生成时间（原始节点 CSV 为 1.2 GiB），由于 H2 注入需要 20 分钟，因此将其乘以 5 会得出 1 小时 30 分钟以南的某个时间点，这是很多！

总而言之，目前这个过程效率很低......

现在，为 PostgreSQL 辩护：

• 对 PostgreSQL 实例的限制比对 H2 实例的限制要高得多：我不需要在生成的 zip 文件中使用 UUID；
• H2 针对写入进行“不安全”调整：jdbc:h2:/path/to/db;LOG=0;LOCK_MODE=0;UNDO_LOG=0;CACHE_SIZE=131072.

尽管如此，插入时间的这种差异似乎有点过大，而且我确信它可以更好。但我不知道从哪里开始。

另外，我知道 PostgreSQL 有一个专门的机制来从 CSV 加载，但这里的 CSV 是在一个 zip 文件中开始的，我真的很想避免像我目前正在做的那样创建一个专用的 CSV。理想情况下，我想直接从 zip 中逐行读取（这就是我为 H2 注入所做的），转换行并写入 PostgreSQL 模式。

最后，我还知道我目前在插入之前没有禁用 PostgreSQL 模式的约束；我还没有尝试过这个（会有什么不同吗？）。

那么，您建议我做什么来提高性能？

从 CSV 文件批量插入 PostgreSQL 的最快方法是Copy。 COPY 命令针对插入大量行进行了优化。

通过 Java，您可以使用PostgreSQL JDBC 驱动程序的复制实现

这里有一个关于如何使用它的很好的小例子：如何使用 JDBC 将数据从文件复制到 PostgreSQL？

如果您有一个带有标题的 CSV，您可能需要运行与此类似的命令：

\COPY mytable FROM '/tmp/mydata.csv' DELIMITER ';' CSV HEADER

当您向现有表添加大量数据时，另一个性能提升是删除索引，插入数据，然后重新创建索引。