当前位置：文江博客话题详情

MySQL性能

发布于 2024-12-29 07:55:54 字数 129 浏览 0 评论 0原文

使用jdbc在mysql数据库中进行批量删除哪种方法更好。

我有一个主键列表。需要删除它们。

使用提供的批量更新API。
删除具有 where 子句的具有 in 子句。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如梦亦如幻 2025-01-05 07:55:54

在 WHERE 子句中使用 IN 速度更快，因为它减少了数据传输。然而，这是一个权衡，因为如果查询太长，MySQL 需要时间来解析它。如果要删除的记录太多，我建议您将其拆分为 10K 长度的查询。

另一种解决方案是，当您有太多记录需要删除时，您可以通过另一个查询获取它们，您可以直接使用，例如：

DELETE FROM table1 WHERE id IN (SELECT id FROM table2 WHERE status = 1)

Use IN in the WHERE clause is faster, as it reduce the data transfer. However there is a trade off, because if the query is too long, MySQL takes time to parse it. I suggest that you split it into 10K-length queries if you have too many records to be deleted.

Another solution, is when you have too many records to be deleted, and you get them with another query, you can use directly, for example:

DELETE FROM table1 WHERE id IN (SELECT id FROM table2 WHERE status = 1)

回复收藏 0 原文

番薯 2025-01-05 07:55:54

我遇到了类似的问题，并决定使用准备好的语句和 JDBC 驱动程序对这两种方法进行基准测试。我的应用程序有 3 个（高度索引的）表。我有一个查询来检索需要从每个表中删除的键列表（该键在所有三个表中都建立了索引，主键在其中一个表中建立了索引）。

首先我尝试了：

SELECT id FROM table1 WHERE delete_condition

我

for each delete_id
    DELETE FROM table1 WHERE table1.id = delete_id
    DELETE FROM table2 WHERE table2.id = delete_id
    DELETE FROM table3 WHERE table3.id = delete_id
loop

发现这提供了每秒 74 行的删除率。

第二种方法：

DELETE FROM table2 WHERE table1.id IN (SELECT id FROM table1 WHERE delete_condition)
DELETE FROM table3 WHERE table1.id IN (SELECT id FROM table1 WHERE delete_condition)
DELETE FROM table1 WHERE table1.id IN (SELECT id FROM table1 WHERE delete_condition)

提供每秒 43,026 行的删除率。我不确定 MySQL 背后发生了什么，但从现在开始我将使用 DELETE FROM table WHERE id IN () 方法。

I was having a similar problem and decided to benchmark the two approaches using prepared statements and the JDBC driver. My application has 3 (heavily indexed) tables. I have a query to retrieve a list of keys that I need to delete from each table (the key is indexed in all three tables and the primary key in one of them).

First I tried:

SELECT id FROM table1 WHERE delete_condition

With

for each delete_id
    DELETE FROM table1 WHERE table1.id = delete_id
    DELETE FROM table2 WHERE table2.id = delete_id
    DELETE FROM table3 WHERE table3.id = delete_id
loop

I found this offered a delete rate of 74 rows per second.

The second approach:

DELETE FROM table2 WHERE table1.id IN (SELECT id FROM table1 WHERE delete_condition)
DELETE FROM table3 WHERE table1.id IN (SELECT id FROM table1 WHERE delete_condition)
DELETE FROM table1 WHERE table1.id IN (SELECT id FROM table1 WHERE delete_condition)

Offered a delete rate of 43,026 rows per second. I'm not sure what's happening under the hood of MySQL, but I'll be using the DELETE FROM table WHERE id IN () approach from now on.

回复收藏 0 原文

~没有更多了~