Spring data jpa 删除效率及分析

实现如下:

 void deleteByCustomerNumber(String customerNumber);

打印sql发现 jpa是一条一条的删除数据的

	bind => [195b7c66-fee9-4d3c-9e39-3f587ab6f767]
[EL Fine]: sql: 2020-09-22 13:57:01.964--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD_CUSTOM_REFERENCE WHERE ((USAGE_RECORD_ID = ?) AND (TENANT_ID = ?))
	bind => [195cd5ac-3ca8-4410-8e2e-96ea33999378, BambooTenant]
[EL Fine]: sql: 2020-09-22 13:57:01.965--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD WHERE (ID = ?)
	bind => [195cd5ac-3ca8-4410-8e2e-96ea33999378]
[EL Fine]: sql: 2020-09-22 13:57:01.966--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD_CUSTOM_REFERENCE WHERE ((USAGE_RECORD_ID = ?) AND (TENANT_ID = ?))
	bind => [195d7f2d-b4b0-47c5-a91a-2c20aa39de7c, BambooTenant]
[EL Fine]: sql: 2020-09-22 13:57:01.967--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD WHERE (ID = ?)
	bind => [195d7f2d-b4b0-47c5-a91a-2c20aa39de7c]
[EL Fine]: sql: 2020-09-22 13:57:01.968--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD_CUSTOM_REFERENCE WHERE ((USAGE_RECORD_ID = ?) AND (TENANT_ID = ?))
	bind => [195dca5e-f405-4045-bebd-55cb970a4573, BambooTenant]
[EL Fine]: sql: 2020-09-22 13:57:01.969--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD WHERE (ID = ?)
	bind => [195dca5e-f405-4045-bebd-55cb970a4573]
[EL Fine]: sql: 2020-09-22 13:57:01.971--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD_CUSTOM_REFERENCE WHERE ((USAGE_RECORD_ID = ?) AND (TENANT_ID = ?))
	bind => [19601bd1-56e3-419e-bdf3-e5320d61139f, BambooTenant]
[EL Fine]: sql: 2020-09-22 13:57:01.972--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD WHERE (ID = ?)
	bind => [19601bd1-56e3-419e-bdf3-e5320d61139f]
[EL Fine]: sql: 2020-09-22 13:57:01.973--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD_CUSTOM_REFERENCE WHERE ((USAGE_RECORD_ID = ?) AND (TENANT_ID = ?))
	bind => [1960523e-80b9-420f-a32f-5ba3c636e84c, BambooTenant]
[EL Fine]: sql: 2020-09-22 13:57:01.974--ClientSession(400547645)--Connection(1623454578)--DELETE FROM USAGE_RECORD WHERE (ID = ?)

当删除3000条数据时,花费时间大概需要7秒左右,非常的慢。

那么我们如何让它变快呢?博主尝试使用@query直接写sql语句删除。使用@query之后,jpa只会执行一条sql语句。

[EL Fine]: sql: 2020-09-22 13:58:46.973--ClientSession(729932917)--Connection(464290316)--DELETE FROM USAGE_RECORD WHERE ((CUSTOMER_NUMBER = ?) AND (TENANT_ID = ?))
	bind => [123456789, BambooTenant]

但是,虽然改成这种方式之后删除速度变快,但是它却没有办法删除和主表关联的子表的数据,原因可以查看stackover flow的这l两个问题:

Why does a manually defined Spring Data JPA delete query not trigger cascades?

Why do I not see changes issued through an update query with Spring Data JPA?

原因是,jpa在design的时候就不支持在batch操作时操作级联关系。

因此,要想实现删除的时候同时也删除级联的数据,通过jpa只能一条一条删除。

如果是非常需要提高效率的情况,即需要大批量的删除数据,则有下面的办法可以尝试:

  • 采取异步的方式处理请求,先返回再删除
  • 不使用jpa的删除方式,通过自己写sql语句,先删除子表,再一次性删除主表

添加索引等方式对这样的挨个删除数据并不能提升太多效率

Spring data jpa 使用@query delete报错

代码如下:

 @Query("DELETE FROM UsageRecordEntity u where u.customerNumber = ?1")
 void deleteByCustomerNumber(String customerNumber);

报错如下:

Caused by: java.lang.IllegalStateException: You cannot call getSingleResult() on this query.  It is the incorrect query type.
	at org.eclipse.persistence.internal.jpa.QueryImpl.getSingleResult(QueryImpl.java:528)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:404)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:196)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	... 112 common frames omitted

解决办法,在代码上加上@Modifying注解即可:

 @Modifying
 @Query("DELETE FROM UsageRecordEntity u where u.customerNumber = ?1")
 void deleteByCustomerNumber(String customerNumber);

之所以使用@query是为了提高删除速度。

在删除操作时,最好不要用jpa,直接在dao方法上@query,也就是用sql语句,能快些:jpa实现删除需要@transaction注解,该注解有可能造成tomcat出问题。。而且改成@query可以提升速度
 

©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页