开启Always Encryption后使用Statement插入数据过慢问题排查

我有一个基于Spring Boot的应用，使用SQL Server作为数据库，其中一项服务负责向临时库的表中插入数据。

方案对比 #

问题前方案 #

采用Spark批量插入方案，由Spark管理所有操作，仅需配置Spark连接，700万行数据插入耗时约30分钟，一切正常。

问题后方案 #

因部分列需开启Always Encryption，原批量插入方案不再适用。新方案仍使用Spark，但将待插入数据的RDD重新分区，每个分区通过PreparedStatement批量插入（例如6万行数据按每批1000行分为60个分区）。

负责插入的核心代码如下：

@Override
public void call(Iterator<String> partition) {

    try {
        PreparedStatement preparedStatement = jdbcConnection.getConnection().prepareStatement(compiledQuery.toString());
        jdbcConnection.getConnection().setAutoCommit(false);
        long start = System.currentTimeMillis();
        while (partition.hasNext()) {

            String row = partition.next();
            Object[] rowValidated = controlRow(row);

            for (int index = 0; index < rowValidated.length; index++) {

                PremiumTableFormat refColumn = getElementByIndexFromFixedSchema(index);
                if (refColumn.isEncrypted()) {

                    if (!refColumn.getType().equalsIgnoreCase("date")) {
                        preparedStatement.setObject(index + 1, rowValidated[index], java.sql.Types.NVARCHAR, 264);
                    } else {
                        preparedStatement.setDate(index + 1, (Date) rowValidated[index]);
                    }
                } else {
                    preparedStatement.setObject(index + 1, rowValidated[index]);
                }
            }
            preparedStatement.addBatch();
        }
        long end = System.currentTimeMillis();
        log.info("prepare batch data take = " + ((end - start)) + " ms");

        start = System.currentTimeMillis();
        preparedStatement.executeBatch();
        jdbcConnection.getConnection().commit();
        preparedStatement.clearBatch();
        end = System.currentTimeMillis();
        log.info("total time taken to insert the batch = " + ((end - start)) + " ms");

    } catch (SQLServerException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

当前问题 #

executeBatch()耗时过长，6万行数据需30分钟。尝试过10、50、1000、5000等不同批次大小，最优为每批50行仍需28分钟，速度依旧不理想。

补充说明 #

1. controlRow方法处理1000行数据的分区仅需20ms；
2. 连接字符串不含Always Encryption参数时，6万行插入仅需4.7分钟；
3. 含加密参数的连接字符串：

"jdbc:sqlserver://" + parameters.server + ";encrypt=true;databaseName=" + parameters.getDatabase() + ";encrypt=true;trustServerCertificate=true;columnEncryptionSetting=Enabled;"

一、当前代码的明显问题 #

1. 重复获取数据库连接：多次调用jdbcConnection.getConnection()，每次调用可能触发新连接创建或不必要的连接校验，开启列加密后，连接初始化/校验成本更高，应仅获取一次并复用。
2. 未正确关闭资源：PreparedStatement和数据库连接在方法结束后未关闭，会导致连接泄漏，长期运行耗尽连接池资源，间接影响插入性能。
3. 加密列类型处理冗余：加密非日期列时指定java.sql.Types.NVARCHAR和长度264，若原数据类型匹配，会触发额外类型转换，增加加密计算开销。

二、针对性优化方案 #

1. 连接层面优化 #

• 移除连接字符串中重复的encrypt=true参数，修正后：

  "jdbc:sqlserver://" + parameters.server + ";encrypt=true;databaseName=" + parameters.getDatabase() + ";trustServerCertificate=true;columnEncryptionSetting=Enabled;"
  

• 确保使用连接池管理连接，避免每个分区任务都新建连接，减少加密上下文初始化的重复开销。

2. 批量插入逻辑优化 #

修改代码，复用连接与PreparedStatement，增加分批提交逻辑，同时正确关闭资源：

@Override
public void call(Iterator<String> partition) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        // 仅获取一次连接与PreparedStatement
        conn = jdbcConnection.getConnection();
        conn.setAutoCommit(false);
        pstmt = conn.prepareStatement(compiledQuery.toString());
        
        long start = System.currentTimeMillis();
        int batchCount = 0;
        final int BATCH_SIZE = 50; // 保留最优批次大小
        while (partition.hasNext()) {
            String row = partition.next();
            Object[] rowValidated = controlRow(row);

            for (int index = 0; index < rowValidated.length; index++) {
                PremiumTableFormat refColumn = getElementByIndexFromFixedSchema(index);
                if (refColumn.isEncrypted()) {
                    if (refColumn.getType().equalsIgnoreCase("date")) {
                        pstmt.setDate(index + 1, (Date) rowValidated[index]);
                    } else {
                        // 原数据类型匹配时直接使用setObject，避免额外类型转换
                        pstmt.setObject(index + 1, rowValidated[index]);
                    }
                } else {
                    pstmt.setObject(index + 1, rowValidated[index]);
                }
            }
            pstmt.addBatch();
            batchCount++;
            
            // 达到批次大小就提交，避免单批次数据过大
            if (batchCount % BATCH_SIZE == 0) {
                pstmt.executeBatch();
                conn.commit();
                pstmt.clearBatch();
                batchCount = 0;
            }
        }
        // 处理剩余不足一个批次的数据
        if (batchCount > 0) {
            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
        }
        long end = System.currentTimeMillis();
        log.info("total time taken for partition insert = " + ((end - start)) + " ms");

    } catch (SQLServerException e) {
        e.printStackTrace();
        // 异常时回滚事务
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 关闭资源，恢复连接自动提交状态
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.setAutoCommit(true);
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3. Spark与驱动优化 #

• 调整分区数量：当前6万行分60个分区，可减少至20-30个，降低连接创建/销毁次数，减少加密上下文初始化的重复开销。
• 升级JDBC驱动：使用SQL Server JDBC驱动9.4及以上版本，新版本对列加密的性能有专门优化，能减少客户端加密计算的耗时。

4. 数据库层面优化 #

• 临时表插入前禁用非必要索引与约束，插入完成后再启用，减少加密列的索引维护开销。
• 若使用云环境，将加密密钥托管到Azure Key Vault；本地环境可使用硬件安全模块（HSM），提升密钥访问效率，避免密钥存储在本地的性能瓶颈。
• 临时表插入期间，将数据库恢复模式改为简单模式，减少事务日志写入的开销。

三、验证步骤 #

1. 先修复代码中重复获取连接和资源未关闭的问题，测试性能变化；
2. 升级JDBC驱动到最新版本，对比加密插入的性能；
3. 监控数据库等待事件，排查是否存在锁等待、日志写入瓶颈等问题，针对性调整数据库配置。

内容的提问来源于stack exchange，提问作者M-BNCH