面试：25Wqps高吞吐写Mysql，100W数据4秒写完，如何实现？

本文链接：https://blog.csdn.net/m0_62314761/article/details/132595160

25Wqps是什么概念？

QPS（Queries Per Second）：是衡量信息检索系统（例如搜索引擎或数据库）在一秒钟内接收到的搜索流量的一种常见度量。

通过概念我们能很清楚知道 QPS = 并发数/响应时间，即100W/4s = 25Wqps

方式一：直接插入

普通插入100w数据

@Test
public void Test1() {
    long start = System.currentTimeMillis();//开始计时【单位：毫秒】
    Connection conn = jdbcUtils.getConnection();//获取数据库连接
    String sql = "insert into a(id, name) VALUES (?,null)";
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 1000000; i++) {
            ps.setObject(1, i);//填充sql语句种得占位符
            ps.execute();//执行sql语句
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        jdbcUtils.close(conn, ps, null);
    }
    //打印耗时【单位：毫秒】
    System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
}

用时：3736/60= 62分钟多

方式二：使用批处理

使用PreparedStatement

@Test
public void Test2() {
    long start = System.currentTimeMillis();
    Connection conn = jdbcUtils.getConnection();//获取数据库连接
    String sql = "insert into a(id, name) VALUES (?,null)";
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 1000000; i++) {
            ps.setObject(1, i);
            ps.addBatch();//将sql语句打包到一个容器中
            if (i % 500 == 0) {
                ps.executeBatch();//将容器中的sql语句提交
                ps.clearBatch();//清空容器，为下一次打包做准备
            }
        }
        //为防止有sql语句漏提交【如i结束时%500！=0的情况】，需再次提交sql语句
        ps.executeBatch();//将容器中的sql语句提交
        ps.clearBatch();//清空容器
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        jdbcUtils.close(conn, ps, null);
    }
    System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
}

用时：3685/60= 61分钟多

这时候你会发现不是说批处理会快很多吗，为什么实际上没有变化？

而这实际上是因为没有开启重写批处理语句

优化一：

在方式二的基础上，允许jdbc驱动重写批量提交语句，在数据源的url需加上 &rewriteBatchedStatements=true ，表示(重写批处理语句=是)

spring.datasource.url = jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&rewriteBatchedStatements=true
spring.datasource.username = root
spring.datasource.password = 123456

用时：10031/60 = 10s

优化二：

在方式三的基础上，取消自动提交sql语句，当sql语句都提交了才手动提交sql语句

conn.setAutoCommit(false)(设置自动提交=否)

@Test
public void Test3() {
    long start = System.currentTimeMillis();
    Connection conn = jdbcUtils.getConnection();//获取数据库连接
    String sql = "insert into a(id, name) VALUES (?,null)";
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        conn.setAutoCommit(false);//取消自动提交
        for (int i = 1; i <= 1000000; i++) {
            ps.setObject(1, i);
            ps.addBatch();
            if (i % 500 == 0) {
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        ps.executeBatch();
        ps.clearBatch();
        conn.commit();//所有语句都执行完毕后才手动提交sql语句
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        jdbcUtils.close(conn, ps, null);
    }
    System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
}

用时：4秒左右

具体实现步骤：

获取数据库连接。
创建 Statement 对象。
定义 SQL 语句，使用 PreparedStatement 对象预编译 SQL 语句并设置参数。
取消自动提交
将sql语句打包到一个Batch容器中, 添加需要批量处理的SQL语句或是参数
执行批处理操作。
清空Batch容器，为下一次打包做准备
不断迭代第5-7步，直到数据处理完成。
关闭 Statement 和 Connection 对象。

注意事项：

使用setAutoCommit(false) 来禁止自动提交事务，然后在每次批量插入之后手动提交事务
批量提交数据的时候url要设置rewriteBatchedStatements=true
sql语句不能有分号否则抛出BatchUpdateException异常
设置适当的批处理大小：批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小，插入操作的频率将很高，而如果批处理大小太大，可能会导致内存占用过高。通常，建议将批处理大小设置为1000-5000行，这将减少插入操作的频率并降低内存占用
采用适当的等待时间：等待时间指在批处理操作之间等待的时间量。等待时间过短可能会导致内存占用过高，而等待时间过长则可能会延迟插入操作的速度。通常，建议将等待时间设置为几秒钟到几十秒钟之间，这将使操作变得平滑且避免出现内存占用过高等问题。

参考文章：网易一面：25Wqps高吞吐写Mysql，100W数据4秒写完，如何实现？