业务有需求是向SQLServer数据库中插入76w条数据表中有19列要求时间尽可能控制在1分钟以内这个业务的性能瓶颈在大量数据插入数据库最开始使用的是druid数据库连接池采取多线程的方式插入数据但是测试时间在100秒以上业务总耗时单插入数据时间会短一些下面说的时间都是业务总耗时后面将druid替换为HikariCP进行了参数的优化测试时间在75秒左右最后也会把HikariCP的配置附上最终查询了SQLServer有BulkCopy功能可以执行高效插入进行了尝试测试时间在33秒左右满足了业务需求下面是BulkCopy的实现方式经过我的测试多线程执行BulkCopy开启四个线程即可一味提升线程数性能提升不是很明显同时也会占用过多的数据库连接。最后使用BulkCopy这种方式与使用druid或HikariCP没有关系性能差距不大所以最后还是使用了druid。private void insertScheduleToDB(ListAdvancedFiniteScheduler.Operation scheduledOps, MapString, AutoProcessPlan processMap) { scheduledOps.sort(Comparator.comparingDouble(AdvancedFiniteScheduler.Operation::getStartTime)); // int cpu Runtime.getRuntime().availableProcessors(); // int threadCount Math.max(cpu, 4); int threadCount 4; int chunkSize (scheduledOps.size() threadCount - 1) / threadCount; ListFutureVoid futures new ArrayList(); for (int i 0; i threadCount; i) { int from i * chunkSize; int to Math.min(from chunkSize, scheduledOps.size()); if (from to) break; ListAdvancedFiniteScheduler.Operation chunk new ArrayList(scheduledOps.subList(from, to)); futures.add(threadPoolTaskExecutor.submit(() - { insertScheduleChunk(chunk, processMap); return null; })); } for (FutureVoid f : futures) { try { f.get(); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(写入明细表分片失败, e); } } log.info(已插入 auto_schedule_result: {} 条 (线程数: {}), scheduledOps.size(), futures.size()); } private void insertScheduleChunk(ListAdvancedFiniteScheduler.Operation chunk, MapString, AutoProcessPlan processMap) { Connection pooledConn null; SQLServerBulkCopy bulkCopy null; try { pooledConn sqlSessionFactory.getConfiguration() .getEnvironment().getDataSource().getConnection(); Connection rawConn unwrapNativeConnection(pooledConn); InMemoryBulkData bulkData buildScheduleBulkData( SCHEDULE_RESULT_COLUMNS, SCHEDULE_RESULT_TYPES, SCHEDULE_RESULT_PRECISION, SCHEDULE_RESULT_SCALE); for (AdvancedFiniteScheduler.Operation op : chunk) { AutoProcessPlan proc processMap.get(op.getId()); long planId; try { planId Long.parseLong(op.getId()); } catch (NumberFormatException e) { planId 0L; } LocalDateTime startLdt schedulerWorkHoursToDateTime(op.getStartTime()); LocalDateTime endLdt schedulerWorkHoursToDateTime(op.getEndTime()); BigDecimal processTime (proc ! null proc.getProcessTime() ! null) ? proc.getProcessTime() : null; BigDecimal managementTime (proc ! null proc.getManagementTime() ! null) ? proc.getManagementTime() : null; Timestamp estimatedStart null; if (proc ! null proc.getProcessEstimatedStart() ! null) { estimatedStart Timestamp.valueOf( proc.getProcessEstimatedStart().toInstant() .atZone(ZoneId.systemDefault()).toLocalDateTime()); } Timestamp estimatedEnd null; if (proc ! null proc.getProcessEstimatedEnd() ! null) { estimatedEnd Timestamp.valueOf( proc.getProcessEstimatedEnd().toInstant() .atZone(ZoneId.systemDefault()).toLocalDateTime()); } bulkData.addRow(new Object[]{ planId, // 1 process_plan_id bigint op.getJobId(), // 2 document_number varchar(55) String.valueOf(op.getOpSeq()), // 3 process_seq varchar(10) op.getResourceGroup(), // 4 resource_group nvarchar(50) op.getAssignedDevice(), // 5 equip_name varchar(55) Timestamp.valueOf(startLdt), // 6 scheduled_start_time datetime2(3) Timestamp.valueOf(endLdt), // 7 scheduled_end_time datetime2(3) processTime, // 8 process_time decimal(12,4) String.valueOf(schedulerParentChildrenMap .getOrDefault(op.getJobId(), Collections.emptyList()) .size()), // 9 children_mo_count varchar(10) managementTime, // 10 management_time decimal(12,4) estimatedStart, // 11 estimated_start_date datetime2(7) estimatedEnd, // 12 estimated_end_date datetime2(7) proc ! null ? proc.getProcessNum() : null, // 13 process_num varchar(20) proc ! null ? proc.getMesProcessNum() : null, // 14 mes_process_num varchar(20) proc ! null ? proc.getWorkcenterCode() : null, // 15 workcenter_code varchar(55) proc ! null ? proc.getProcessDesc() : null, // 16 process_desc varchar(55) proc ! null ? proc.getIsFirstSequence() : null, // 17 is_first_sequence varchar(2) proc ! null ? proc.getIsEndSequence() : null // 18 is_end_sequence varchar(2) }); } bulkCopy new SQLServerBulkCopy(rawConn); bulkCopy.setDestinationTableName(auto_schedule_result); for (int i 0; i SCHEDULE_RESULT_COLUMNS.length; i) { bulkCopy.addColumnMapping(i 1, SCHEDULE_RESULT_COLUMNS[i]); } bulkCopy.writeToServer(bulkData); } catch (Exception e) { log.error(BulkCopy插入排产结果分片失败, e); throw new RuntimeException(BulkCopy插入排产结果分片失败, e); } finally { if (bulkCopy ! null) { try { bulkCopy.close(); } catch (Exception ignored) {} } if (pooledConn ! null) { try { pooledConn.close(); } catch (Exception ignored) {} } } } private InMemoryBulkData buildScheduleBulkData(String[] columnNames, int[] columnTypes, int[] precisions, int[] scales) { InMemoryBulkData bulkData new InMemoryBulkData(); for (int i 0; i columnNames.length; i) { bulkData.addColumnMetadata(i 1, columnNames[i], columnTypes[i], precisions[i], scales[i]); } return bulkData; } private static final String[] SCHEDULE_RESULT_COLUMNS { process_plan_id, document_number, process_seq, resource_group, equip_name, scheduled_start_time, scheduled_end_time, process_time, children_mo_count, management_time, estimated_start_date, estimated_end_date, process_num, mes_process_num, workcenter_code, process_desc, is_first_sequence, is_end_sequence }; private static final int[] SCHEDULE_RESULT_TYPES { java.sql.Types.BIGINT, // process_plan_id bigint java.sql.Types.VARCHAR, // document_number varchar(55) java.sql.Types.VARCHAR, // process_seq varchar(10) java.sql.Types.NVARCHAR, // resource_group nvarchar(50) java.sql.Types.VARCHAR, // equip_name varchar(55) java.sql.Types.TIMESTAMP, // scheduled_start_time datetime2(3) java.sql.Types.TIMESTAMP, // scheduled_end_time datetime2(3) java.sql.Types.DECIMAL, // process_time decimal(12,4) java.sql.Types.VARCHAR, // children_mo_count varchar(10) java.sql.Types.DECIMAL, // management_time decimal(12,4) java.sql.Types.TIMESTAMP, // estimated_start_date datetime2(7) java.sql.Types.TIMESTAMP, // estimated_end_date datetime2(7) java.sql.Types.VARCHAR, // process_num varchar(20) java.sql.Types.VARCHAR, // mes_process_num varchar(20) java.sql.Types.VARCHAR, // workcenter_code varchar(55) java.sql.Types.VARCHAR, // process_desc varchar(55) java.sql.Types.VARCHAR, // is_first_sequence varchar(2) java.sql.Types.VARCHAR // is_end_sequence varchar(2) }; private static final int[] SCHEDULE_RESULT_PRECISION { 0, 55, 10, 50, 55, 23, 23, 12, 10, 12, 26, 26, 20, 20, 55, 55, 2, 2 }; private static final int[] SCHEDULE_RESULT_SCALE { 0, 0, 0, 0, 0, 3, 3, 4, 0, 4, 7, 7, 0, 0, 0, 0, 0, 0 }; private static class InMemoryBulkData implements ISQLServerBulkData { private final LinkedHashMapInteger, String columnNames new LinkedHashMap(); private final MapInteger, Integer columnTypes new HashMap(); private final MapInteger, Integer columnPrecision new HashMap(); private final MapInteger, Integer columnScale new HashMap(); private final ListObject[] rows new ArrayList(); private int cursor -1; public void addColumnMetadata(int ordinal, String name, int jdbcType, int precision, int scale) { columnNames.put(ordinal, name); columnTypes.put(ordinal, jdbcType); columnPrecision.put(ordinal, precision); columnScale.put(ordinal, scale); } public void addRow(Object[] values) { rows.add(values); } Override public SetInteger getColumnOrdinals() { return columnNames.keySet(); } Override public String getColumnName(int column) { return columnNames.get(column); } Override public int getColumnType(int column) { return columnTypes.get(column); } Override public int getPrecision(int column) { return columnPrecision.getOrDefault(column, 0); } Override public int getScale(int column) { return columnScale.getOrDefault(column, 0); } Override public Object[] getRowData() { return rows.get(cursor); } Override public boolean next() { cursor; return cursor rows.size(); } } private Connection unwrapNativeConnection(Connection conn) throws SQLException { while (!conn.isWrapperFor(SQLServerConnection.class)) { if (conn instanceof Wrapper) { conn ((Wrapper) conn).unwrap(Connection.class); } else { break; } } return conn.unwrap(SQLServerConnection.class); }下面是HikariCP的配置测试加sendStringParametersAsUnicodefalse;packetSize32767这两个参数可以使插入数据库的速度变快由于原来的项目是druid需要把pom.xml中druid的starter相关的注释掉datasource: dynamic: hikari: minimum-idle: 5 maximum-pool-size: 30 keepaliveTime: 300000 connection-timeout: 60000 idle-timeout: 300000 max-lifetime: 1800000 connection-test-query: SELECT 1 datasource: # 主库数据源 master: driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver:你的数据库连接URLtrustServerCertificatetrue;sendStringParametersAsUnicodefalse;packetSize32767 username: 用户名 password: 密码