java Hibernate批删除与单次删除

Question

编辑：根据我的一些调试和日志记录，我认为问题归结为为什么DELETE FROM table WHERE id = x比DELETE FROM table WHERE id IN (x)快得多，其中x只是一个ID

我最近测试了批量删除与逐个删除每一行的比较，并注意到批量删除要慢得多。表中有delete、update和insert的触发器，但我已经测试了使用和不使用这些触发器的情况，每次批删除都比较慢有人能解释一下为什么会出现这种情况，或者分享一下我如何调试它的技巧吗据我所知，我无法真正减少触发器激活的次数，但我最初认为减少“删除”查询的次数将有助于提高性能

我在下面提供了一些信息，如果我遗漏了任何相关信息，请告诉我

删除分10000批完成，代码如下：

private void batchDeletion( Collection<Long> ids ) {
  StringBuilder sb = new StringBuilder();
  sb.append( "DELETE FROM ObjImpl WHERE id IN (:ids)" );

  Query sql = getSession().createQuery( sb.toString() );
  sql.setParameterList( "ids", ids );

  sql.executeUpdate();
}

只删除一行的代码基本上是：

SessionFactory.getCurrentSession().delete(obj);

该表有两个索引，在任何删除操作中都没有使用。不会发生级联操作

以下是DELETE FROM table where id IN ( 1, 2, 3 );的解释分析示例：

Delete on table  (cost=12.82..24.68 rows=3 width=6) (actual time=0.143..0.143 rows=0 loops=1)
  ->  Bitmap Heap Scan on table  (cost=12.82..24.68 rows=3 width=6) (actual time=0.138..0.138 rows=0 loops=1)
        Recheck Cond: (id = ANY ('{1,2,3}'::bigint[]))
        ->  Bitmap Index Scan on pk_table  (cost=0.00..12.82 rows=3 width=0) (actual time=0.114..0.114 rows=0 loops=1)
              Index Cond: (id = ANY ('{1,2,3}'::bigint[]))
Total runtime: 3.926 ms

每次重新加载数据进行测试时，我都会清空并重新编制索引，测试数据包含386660行

测试是删除所有行，我没有使用TRUNCATE，因为通常有一个选择标准，但出于测试目的，我已经使标准包括所有行。启用触发器后，逐个删除每一行需要193616ms，而批量删除需要28558ms。然后我禁用了触发器，单行删除得到93793ms，批量删除得到181537ms。触发器将对值进行汇总并更新另一个表-基本上是记账

我曾经尝试过较小的批量（100和1），但它们的性能似乎都更差

编辑：打开Hibernate日志记录，对于单行逐行删除，它基本上执行：delete from table where id=?和解释分析：

Delete on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.042..0.042 rows=0 loops=1)
  ->  Index Scan using pk_table on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.037..0.037 rows=0 loops=1)
        Index Cond: (id = 3874904)
Total runtime: 0.130 ms

编辑：很好奇这个列表是否包含10000个ID，Postgres是否会做一些不同的事情：不

Delete on table  (cost=6842.01..138509.15 rows=9872 width=6) (actual time=17.170..17.170 rows=0 loops=1)
  ->  Bitmap Heap Scan on table  (cost=6842.01..138509.15 rows=9872 width=6) (actual time=17.160..17.160 rows=0 loops=1)
        Recheck Cond: (id = ANY ('{NUMBERS 1 THROUGH 10,000}'::bigint[]))
        ->  Bitmap Index Scan on pk_table  (cost=0.00..6839.54 rows=9872 width=0) (actual time=17.139..17.139 rows=0 loops=1)
              Index Cond: (id = ANY ('{NUMBERS 1 THROUGH 10,000}'::bigint[]))
Total runtime: 17.391 ms

编辑：基于上面的解释分析，我从实际的删除操作中检索到了一些日志记录。下面是记录两种变化的逐行删除

以下是一些单次删除：

2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?

下面是单次删除的另一个变体（列表仅为1项）

2013-03-14 13:49:59,858:delete from table where id in (?)
2013-03-14 13:50:01,460:delete from table where id in (?)
2013-03-14 13:50:03,040:delete from table where id in (?)
2013-03-14 13:50:04,544:delete from table where id in (?)
2013-03-14 13:50:06,125:delete from table where id in (?)
2013-03-14 13:50:07,707:delete from table where id in (?)
2013-03-14 13:50:09,275:delete from table where id in (?)
2013-03-14 13:50:10,833:delete from table where id in (?)
2013-03-14 13:50:12,369:delete from table where id in (?)
2013-03-14 13:50:13,873:delete from table where id in (?)

这两个ID都存在于表中，并且应该是连续的

---

解释分析DELETE FROM table WHERE id = 3774887;

Delete on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.097..0.097 rows=0 loops=1)
  ->  Index Scan using pk_table on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.055..0.058 rows=1 loops=1)
        Index Cond: (id = 3774887)
Total runtime: 0.162 ms

解释分析DELETE FROM table WHERE id IN (3774887);

Delete on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.279..0.279 rows=0 loops=1)
  ->  Index Scan using pk_table on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.210..0.213 rows=1 loops=1)
        Index Cond: (id = 3774887)
Total runtime: 0.452 ms

0.162 vs 0.452被认为存在显著差异

编辑：

将批量大小设置为50000，Hibernate不喜欢这个想法：

java.lang.StackOverflowError
        at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:40)
        at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:41)
        at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:42)
....

Answer 1

好的，首先要注意的是，SQL必须以某种方式转换为计划。您的解释结果表明，与IN（VAL）构造相比，等式的逻辑有根本不同

WHERE id = 1;

转换为简单的相等筛选器

WHERE id IN (1);

转换为以下数组匹配：

WHERE id = ANY(ARRAY[1]);

显然，规划者不够聪明，没有注意到当一个数组只有一个成员时，它们在数学上是相同的。因此，它所做的是规划任何大小的数组，这就是为什么要进行嵌套循环位图索引扫描

这里有趣的是，它不仅速度较慢，而且在大多数情况下性能更好。因此，对于in（）子句中的一个成员，速度要慢40倍，对于10000个成员，速度只慢170倍，但这也意味着10000个成员的版本也比id上10000个单独的索引扫描快50倍

因此，这里发生的事情是，计划员正在选择一个计划，该计划在检查了大量id时性能更好，但在只有少量id时性能更差

Answer 2

如果这里的问题真的归结为“如何尽可能快地删除大量记录？”然后删除。。。对于每一行，IN（）方法将优于删除，因此探究IN（？）使用单个成员时，速度似乎比=？不会帮你的

使用临时表保存所有要删除的id，然后运行一次删除，这可能是值得探索的

如果不太昂贵，将列表中的id按升序排列可能有助于实现非常大的删除性能。如果您必须对它们进行排序，则不必费心，但是如果有一种方法可以确保聚集在索引的同一区域中的每一批删除地址id可能会稍微有好处

在任何情况下，在我看来，这两种情况下都在使用索引并生成相同的计划，因此我想知道这里是否存在查询解析和优化问题，而不是删除操作本身的问题。我对内部的了解还不够，恐怕不能确定