java - Hibernate 批量删除与单个删除

Question

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

我最近测试了批量删除与逐行删除的对比，发现批量删除要慢得多。该表具有用于删除、更新和插入的触发器，但我已经在使用和不使用触发器的情况下进行了测试，每次批量删除都比较慢。 任何人都可以阐明为什么会出现这种情况或分享有关如何调试此问题的提示吗？据我了解，我无法真正减少触发器激活的次数，但我最初认为减少“删除”查询的数量将有助于提高性能。

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

删除以 10,000 个为一批完成，代码如下所示:

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 ); 的 EXPLAIN ANALYZE 示例；:

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

每次重新加载数据进行测试时，我都会清理并重新编制索引，我的测试数据包含 386,660 行。

测试是删除所有行，我没有使用 TRUNCATE，因为通常有一个选择标准，但为了测试目的，我已经使标准包括所有行。启用触发器后，逐行删除需要 193,616 毫秒，而批量删除需要 285,558 毫秒。然后我禁用了触发器，单行删除耗时 93,793 毫秒，批量删除耗时 181,537 毫秒。触发器运行并汇总值并更新另一个表 - 基本上是簿记。

我试过较小的批量大小(100 和 1)，但它们的表现似乎都更差。

编辑:打开 Hibernate 日志记录并逐行删除，它基本上是这样做的:delete from table where id=? 和 EXPLAIN ANALYZE:

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

编辑:很好奇列表是否真的包含 10,000 个 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

编辑:基于上面的 EXPLAIN ANALYZE，我从实际的删除操作中检索了一些日志记录。下面是逐行删除的两种变体的日志记录。

这里是一些单一的删除:

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; 的 EXPLAIN ANALYZE;

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

EXPLAIN ANALYZE of 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 与 0.452 是否有显着差异？

编辑:

将批量大小设置为 50,000，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 必须以某种方式转换为计划。您的 EXPLAIN 结果表明，与 IN(vals) 构造相比，这里的相等性逻辑根本不同。

WHERE id = 1;

转换为简单的相等过滤器。

WHERE id IN (1);

转化为数组匹配:

WHERE id = ANY(ARRAY[1]);

显然，规划者不够聪明，没有注意到这些在数学上是相同的，而数组只有一个成员。所以它正在做的是规划一个任意大小的数组，这就是为什么你得到嵌套循环位图索引扫描。

有趣的是，它不仅速度较慢，而且在大多数情况下性能更好。所以对于 in() 子句中的一个成员，它慢了 40 倍，而对于 10000 个成员，它只慢了 170 倍，但这也意味着 10000 个成员的版本也快 50 倍对 id 进行超过 10000 次单独的索引扫描。

所以这里发生的事情是，规划器正在选择一个计划，该计划在检查大量 id 时表现更好，但在只有少数 id 时表现更差。