mysql jdbc查询慢 spring_为什么Spring的jdbcTemplate.batchUpdate（）这么慢？

最新推荐文章于 2023-11-14 09:57:39 发布

weixin_39824834

最新推荐文章于 2023-11-14 09:57:39 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

文章标签： mysql jdbc查询慢 spring

本文链接：https://blog.csdn.net/weixin_39824834/article/details/114328416

通过调整JDBC连接URL参数、使用原生JDBC批量插入、开启事务、设置合适的批量大小以及忽略参数类型等方式，可以显著提升Spring JDBC的batchUpdate()方法的性能。例如，关闭`useServerPrepStmts`和开启`rewriteBatchedStatements`，以及手动控制批量提交等策略，都能够在处理大量数据插入时避免速度瓶颈。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

8 个答案:

答案 0 :(得分：12)

JDBC连接URL中的这些参数可以对批量语句的速度产生很大影响 - 根据我的经验，它们可以加快速度：

useServerPrepStmts =假安培; rewriteBatchedStatements =真

答案 1 :(得分：10)

我也遇到了与Spring JDBC模板相同的问题。可能在Spring Batch中，语句在每个插件或块上执行并提交，这会减慢速度。

我已使用原始JDBC批量插入代码替换了jdbcTemplate.batchUpdate()代码，并找到了主要性能改进。

DataSource ds = jdbcTemplate.getDataSource();

Connection connection = ds.getConnection();

connection.setAutoCommit(false);

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";

PreparedStatement ps = connection.prepareStatement(sql);

final int batchSize = 1000;

int count = 0;

for (Employee employee: employees) {

ps.setString(1, employee.getName());

ps.setString(2, employee.getCity());

ps.setString(3, employee.getPhone());

ps.addBatch();

++count;

if(count % batchSize == 0 || count == employees.size()) {

ps.executeBatch();

ps.clearBatch();

}

connection.commit();

ps.close();

答案 2 :(得分：7)

只需使用交易。在方法上添加@Transactional。

如果使用多个数据源@Transactional(“dsTxManager”)，请务必声明正确的TX管理器。我有一个插入60000记录的情况。大约需要15秒。没有其他调整：

@Transactional("myDataSourceTxManager")

public void save(...) {

...

jdbcTemplate.batchUpdate(query, new BatchPreparedStatementSetter() {

@Override

public void setValues(PreparedStatement ps, int i) throws SQLException {

...

}

@Override

public int getBatchSize() {

if(data == null){

return 0;

}

return data.size();

}

});

}

答案 3 :(得分：6)

将您的sql插件更改为INSERT INTO TABLE(x, y, i) VALUES(1,2,3)。框架为您创建一个循环。

例如：

public void insertBatch(final List customers){

String sql = "INSERT INTO CUSTOMER " +

"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

@Override

public void setValues(PreparedStatement ps, int i) throws SQLException {

Customer customer = customers.get(i);

ps.setLong(1, customer.getCustId());

ps.setString(2, customer.getName());

ps.setInt(3, customer.getAge() );

}

@Override

public int getBatchSize() {

return customers.size();

}

});

}

如果您有这样的事情。 Spring会做类似的事情：

for(int i = 0; i < getBatchSize(); i++){

execute the prepared statement with the parameters for the current iteration

}

框架首先从查询(sql变量)创建PreparedStatement，然后调用setValues方法并执行语句。重复的次数与您在getBatchSize()方法中指定的次数相同。因此，编写insert语句的正确方法是只有一个values子句。

您可以查看http://docs.spring.io/spring/docs/3.0.x/reference/jdbc.html

答案 4 :(得分：4)

我不知道这是否适合你，但这是我最终使用的无弹簧方式。它比我尝试的各种Spring方法快得多。我甚至尝试使用其他答案描述的JDBC模板批量更新方法，但即使这样比我想要的慢。我不确定这笔交易是什么，互联网也没有多少答案。我怀疑它与如何处理提交有关。

这种方法只是使用java.sql包和PreparedStatement的批处理接口的直接JDBC。这是我可以将24M记录放入MySQL数据库的最快方法。

我或多或少只是构建了“记录”对象的集合，然后在批量插入所有记录的方法中调用下面的代码。构建集合的循环负责管理批量大小。

我试图将24M记录插入到MySQL数据库中，使用Spring批处理每秒约200条记录。当我切换到这种方法时，它每秒达到约2500条记录。所以我的24M记录负载从理论上的1.5天增加到大约2.5小时。

首先创建一个连接......

Connection conn = null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(connectionUrl, username, password);

}catch(SQLException e){}catch(ClassNotFoundException e){}

然后创建一个预准备语句并使用批量值插入它，然后作为单个批处理插入执行...

PreparedStatement ps = null;

try{

conn.setAutoCommit(false);

ps = conn.prepareStatement(sql); // INSERT INTO TABLE(x, y, i) VALUES(1,2,3)

for(MyRecord record : records){

try{

ps.setString(1, record.getX());

ps.setString(2, record.getY());

ps.setString(3, record.getI());

ps.addBatch();

} catch (Exception e){

ps.clearParameters();

logger.warn("Skipping record...", e);

}

ps.executeBatch();

conn.commit();

} catch (SQLException e){

} finally {

if(null != ps){

try {ps.close();} catch (SQLException e){}

}

显然我已经删除了错误处理，查询和Record对象是名义上的等等。

修改强>

由于您的原始问题是将插入值与foobar值(？，？，？)，(？，？，？)...(？，？，？)方法与Spring批次进行比较，因此这是对此的更直接的响应： / p>

看起来您的原始方法可能是将批量数据加载到MySQL而不使用“LOAD DATA INFILE”方法的最快方法。来自MysQL文档(http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html)的引用：

如果您同时从同一客户端插入多行，

使用带有多个VALUES列表的INSERT语句来插入多个

一次排。这速度要快得多(有些速度要快很多倍)

case)比使用单独的单行INSERT语句。

您可以修改Spring JDBC Template batchUpdate方法，以便根据每个'setValues'调用指定多个VALUES进行插入，但是在迭代插入的一组内容时，您必须手动跟踪索引值。当你插入的东西总数不是你准备好的陈述中的VALUES列表数的倍数时，你会遇到一个讨厌的边缘情况。

如果你使用我概述的方法，你可以做同样的事情(使用带有多个VALUES列表的预备语句)然后当你到达最后的边缘情况时，它会更容易处理，因为你可以使用恰当数量的VALUES列表构建并执行最后一个语句。它有点笨拙，但大多数优化的东西都是。

答案 5 :(得分：3)

我在调用中找到了主要改进设置argTypes数组。

就我而言，使用Spring 4.1.4和Oracle 12c，插入包含35个字段的5000行：

jdbcTemplate.batchUpdate(insert, parameters); // Take 7 seconds

jdbcTemplate.batchUpdate(insert, parameters, argTypes); // Take 0.08 seconds!!!

argTypes参数是一个int数组，您可以用这种方式设置每个字段：

int[] argTypes = new int[35];

argTypes[0] = Types.VARCHAR;

argTypes[1] = Types.VARCHAR;

argTypes[2] = Types.VARCHAR;

argTypes[3] = Types.DECIMAL;

argTypes[4] = Types.TIMESTAMP;

.....

我调试了org \ springframework \ jdbc \ core \ JdbcTemplate.java，发现大部分时间都在消耗，试图了解每个字段的性质，这是为每条记录做的。

希望这有帮助！

答案 6 :(得分：2)

我在使用Spring JDBC批处理模板时也遇到了一些麻烦。就我而言，使用纯JDBC就像疯了一样，所以我改用NamedParameterJdbcTemplate。这是我项目中必须具备的条件。但是在数据库中插入数百行数千行的速度很慢。

要查看发生了什么，我在批量更新过程中使用VisualVM对它进行了采样，瞧瞧：

让过程变慢的是，在设置参数的同时，Spring JDBC正在查询数据库以了解元数据每个参数。在我看来，这是每次都在数据库中查询每行的每个参数。因此，我刚刚教过Spring忽略参数类型(正如Spring documentation about batch operating a list of objects中所警告的那样)：

@Bean(name = "named-jdbc-tenant")

public synchronized NamedParameterJdbcTemplate getNamedJdbcTemplate(@Autowired TenantRoutingDataSource tenantDataSource) {

System.setProperty("spring.jdbc.getParameterType.ignore", "true");

return new NamedParameterJdbcTemplate(tenantDataSource);

}

注意：在创建JDBC模板对象之前，必须先设置系统属性。可以只在application.properties中进行设置，但这已经解决了，我再也没有碰过它

答案 7 :(得分：0)

@Rakesh提供的解决方案为我工作。

性能显着改善。较早的时间是8分钟，而此解决方案只需不到2分钟。

DataSource ds = jdbcTemplate.getDataSource();

Connection connection = ds.getConnection();

connection.setAutoCommit(false);

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";

PreparedStatement ps = connection.prepareStatement(sql);

final int batchSize = 1000;

int count = 0;

for (Employee employee: employees) {

ps.setString(1, employee.getName());

ps.setString(2, employee.getCity());

ps.setString(3, employee.getPhone());

ps.addBatch();

++count;

if(count % batchSize == 0 || count == employees.size()) {

ps.executeBatch();

ps.clearBatch();

}

connection.commit();

ps.close();