大批量数据快速写入数据库

吾生有牙

已于 2022-08-16 18:25:23 修改

阅读量2.8k

点赞数 1

分类专栏：数据库 java 文章标签：数据库 clickhouse

于 2022-08-16 18:16:41 首次发布

本文链接：https://blog.csdn.net/gfdgdshhg/article/details/126371802

版权

最近由于项目需要存储传感器采集的海量数据，考虑使用clickhouse进行存储和分析。

于是打算做一下性能测试。做测试之前，肯定得有数据，所以就打算造些数据。于是编写java代码模拟一天的数据（约5千万条）插入。

首先是导入驱动

        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.3.1</version>
        </dependency>

一开始使用拼接sql的方式，每个sql拼接18000条数据,sql语句大致如下

insert into tab_test values(*,*,*),(*,*,*)

然后通过如下方法执行

Statement.execute(sql);

执行一次插入需要4到5秒种，也就是一秒钟写入大概6K的数据，感觉这个写入速度有点低，照这个样子，一天的测试数据要好几个小时呢。

后来在网上找，看到有人用prepareStatement.executeBatch()来做，于是就想着试试看，最后测试结果，写入性能稳定在10W/s左右，一天的数据500多秒就写完了。

于是网上查了下原因，是因为prepareStatement采用的预编译机制，也就是数据库只编译一次sql，后面就只发送参数就可以了。而Statement.execute每次都要发送全量的sql语句，数据也要解析语句，所以性能就慢了。这个是jdbc的特性，对所有的数据库都这样，看来还是数据库基础不扎实啊。所以大家以后如果在mysql又大批量数据需要写入的时候，尽量采用prepareStatement来执行，不过要注意的是mysql4.1之前的版本事不支持prepare Statement特性的，使用prepareStatement反而会降低性能。

最后，在写入clickhouse数据的时候，最好采用批量数据插入，官方建议一次不少于1000条，不然性能很差。

完整代码如下

import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.Logger;
import ch.qos.logback.classic.LoggerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.client.utils.DateUtils;
import org.slf4j.LoggerFactory;
import ru.yandex.clickhouse.except.ClickHouseUnknownException;
import java.net.SocketException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Random;
import java.util.UUID;

@Slf4j
public class ClickhouseJDBC {
   
    static {
   
        LoggerContext loggerContext = (LoggerContext) LoggerFactory.getILoggerFactory();
        List<Logger> loggerList = loggerContext.getLoggerList();
        loggerList.forEach(logger -> {
   
            logger.setLevel(Level.INFO);
        });
    }
    private static Connection conn ;
    private static Statement stmt;
    private static PreparedStatement prepareStatement = null;

    public