Spring data jpa 删除效率及分析

亦凉 2021-09-21 11:22 387阅读 0赞

实现如下：

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 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?][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可以提升速度

[Why does a manually defined Spring Data JPA delete query not trigger cascades]: https://stackoverflow.com/questions/23443188/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]: https://stackoverflow.com/questions/22533399/why-do-i-not-see-changes-issued-through-an-update-query-with-spring-data-jpa/22551997#22551997