草庐IT

关于mysql：运行10,000个查询每个更新单行，还是运行100个查询每个更新100行更快？

codeneng 2023-03-28 原文

Is it faster to run 10,000 queries each updating a single row, or 100 queries each updating 100 rows?

假设我有一个主键列表，对于每一行，一个值需要更新。跑起来更好吗：

1
2
-- run 10,000 of these queries
UPDATE mytable SET myflag = 1 WHERE id = [each_id]

或者像这样将更新组合成批处理查询：

1
2
-- run 100 of these queries, where the IN () list contains about 100 elements
UPDATE mytable SET myflag = 1 WHERE id IN (3, 4, 5, 9, 99, ... 7887 )

100 个 IN () 项的 100 个查询怎么样？

  • 做一些基准测试？如果您可以在您的系统上对其进行测试，它将是最准确的
  • 它在很大程度上取决于您是否使用事务/每个 UPDATE 或大约每 100 个事务等。
  • 好吧，测试这个非常容易......但是，只要你的 IN() < max_allowed_packet，特别是如果 myflag 上有一个键，它可能会更快。
  • nitpick：第一个案例更新 10k 行，第二个 100k 行，不确定这是否是故意的？
  • 如果每次更新都使用单独的事务，那么一次多条记录将在 PostgreSQL 中以压倒性优势获胜，这仅仅是因为事务数量减少了。似乎这个长长的数字列表可能包含在某个表中，如果可能，请在 WHERE 子句中对该表使用选择。
  • @fvu：我修正了错误的数字。

也没有。在 PostgreSQL 中，我会改为：

1
2
3
4
5
6
7
8
9
10
11
12
WITH x AS (
   SELECT unnest('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
                 ,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40
                 ,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
                 ,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80
                 ,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100
                 }'::int[]) AS id
   )
UPDATE mytable t
SET    myflag = 1
FROM   x
WHERE  t.id = x.id;

我在我的示例中放置了这么多 ID，以提供一个直观的线索，即 10000 个 ID 很多。问题中提出的两个想法是：

  • 必须解析列表并将 10000 条语句放在一起并将它们发送到服务器，这可能比 UPDATE 本身花费更长的时间。

  • 必须在包含 10000 个项目的列表(数组)中为 mytable 中的每个单独的 id 搜索匹配的 id。不能使用标准索引。这将非常缓慢。性能随着 mytable.

    的大小而降低

    • mytable.id 上的索引是所有提出的替代方案都需要优于两个变体一个数量级。

    CTE 对数组进行一次解析(子查询也有效——MySQL 没有 CTE)——而 unnest() 在这方面相当快。在一个语句中完成所有操作比 10000 个语句高出一个数量级。如果这些语句在单个事务中运行，则再增加一个数量级。如果您应该使用单独的会话，请添加另一个。

    罕见的例外情况适用于在高写入负载下存在锁定问题的数据库。只是按照建议进行基准测试。 EXPLAIN ANALYZE 是你在 PostgreSQL 中的朋友。

    如果操作变得庞大，并且大部分表都已更新和/或您的磁盘空间或 RAM 不足，将操作分成几个逻辑块可能仍然是个好主意 - 只是不要太多，找到甜蜜点。主要是为了让 HOT 更新从以前的 UPDATE 运行中回收表膨胀。考虑这个相关的问题。

    • 仅出于完整性考虑：可写 CTE 仅适用于 PostgreSQL 9.1 或更高版本。
    • 我可能会选择 VALUES (1),(2),(3),... 而不是创建和取消嵌套数组。

    我发现第二种方法在对非常大的数据集进行插入时速度要快几个数量级。这非常依赖于您的系统，但是查询的 IN 部分的效率或多或少取决于表大小、索引等。

    自己做简单的基准测试确实是唯一的方法。

    在正常情况下，运行一条更新语句是最有效的。例如，

    1
    UPDATE mytable set myflag=1 where id IN (select id from someothertable where stuff).

    根据您的架构，这可能会更慢。您应该进行基准测试并找出答案。

    注意，从客户端到数据库服务器运行 10,000 条语句几乎肯定会更慢。在存储过程中运行 10,000 次更新和从客户端运行 10,000 次更新是两个非常不同的事情。如果您要走 10,000 次更新路线，请确保在 SP 中进行。

    通常，RDBMS 往返是一个主要因素，但在这种情况下，解析 in 列表的成本也可能会很高。但是，如果您将查询参数化，则第二种解决方案很可能会更快，因为解析只会进行一次。

    • 可能跑题了，但是如何参数化一个变量列表参数呢？换句话说，类似于 IN (?) 然后将可变长度绑定到 ?。
    • @MichaelButler 不幸的是，不：您创建了一个包含 100 个单独 ? 的列表；然后你分别绑定每个变量。当您执行最后一个"奇数"批处理时，您将生成另一个 SQL，其中 ? 的数量与剩余数量一样多。

    这主要取决于硬盘的 fsync 数量：这是系统中最慢的部分。

    对于 PostgreSQL：在少量事务中进行，如果可能的话，只在一个事务中。但是要注意行锁定，两个事务不能同时更新同一行。

    • 如果表没有索引怎么办？那么它必须进行 10,000 次表扫描，而不是 1 次，对吗？
    • 表扫描本身还不错，这一切都取决于。这也是为什么您必须查看 EXPLAIN 以了解查询是如何执行的以及它是否具有可以使用的索引的原因。

    100mysqlspansectionsearchperformancepostgresqlsql-update

    有关关于mysql：运行10,000个查询每个更新单行，还是运行100个查询每个更新100行更快？的更多相关文章

    1. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

      总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

    2. ruby-on-rails - 如何验证 update_all 是否实际在 Rails 中更新 - 2

      给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru

    3. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

      我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

    4. ruby - 如何每月在 Heroku 运行一次 Scheduler 插件？ - 2

      在选择我想要运行操作的频率时，唯一的选项是“每天”、“每小时”和“每10分钟”。谢谢!我想为我的Rails3.1应用程序运行调度程序。 最佳答案 这不是一个优雅的解决方案，但您可以安排它每天运行，并在实际开始工作之前检查日期是否为当月的第一天。 关于ruby-如何每月在Heroku运行一次Scheduler插件？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/8692687/

    5. ruby-on-rails - 如何在 ruby​​ 中使用两个参数异步运行 exe？ - 2

      exe应该在我打开页面时运行。异步进程需要运行。有什么方法可以在ruby​​中使用两个参数异步运行exe吗？我已经尝试过ruby​​命令-system()、exec()但它正在等待过程完成。我需要用参数启动exe，无需等待进程完成是否有任何ruby​​gems会支持我的问题？ 最佳答案 您可以使用Process.spawn和Process.wait2:pid=Process.spawn'your.exe','--option'#Later...pid,status=Process.wait2pid您的程序将作为解释器的子进程执行。除

    6. ruby - 无法运行 Rails 2.x 应用程序 - 2

      我尝试运行2.x应用程序。我使用rvm并为此应用程序设置其他版本的ruby​​:$rvmuseree-1.8.7-head我尝试运行服务器，然后出现很多错误:$script/serverNOTE:Gem.source_indexisdeprecated,useSpecification.Itwillberemovedonorafter2011-11-01.Gem.source_indexcalledfrom/Users/serg/rails_projects_terminal/work_proj/spohelp/config/../vendor/rails/railties/lib/r

    7. ruby-on-rails - 使用 rails 4 设计而不更新用户 - 2

      我将应用程序升级到Rails4，一切正常。我可以登录并转到我的编辑页面。也更新了观点。使用标准View时，用户会更新。但是当我添加例如字段:name时，它​​不会在表单中更新。使用devise3.1.1和gem'protected_attributes'我需要在设备或数据库上运行某种更新命令吗？我也搜索过这个地方，找到了许多不同的解决方案，但没有一个会更新我的用户字段。我没有添加任何自定义字段。 最佳答案 如果您想允许额外的参数，您可以在ApplicationController中使用beforefilter，因为Rails4将参数

    8. ruby - Sinatra:运行 rspec 测试时记录噪音 - 2

      Sinatra新手；我正在运行一些rspec测试，但在日志中收到了一堆不需要的噪音。如何消除日志中过多的噪音？我仔细检查了环境是否设置为:test，这意味着记录器级别应设置为WARN而不是DEBUG。spec_helper:require"./app"require"sinatra"require"rspec"require"rack/test"require"database_cleaner"require"factory_girl"set:environment,:testFactoryGirl.definition_file_paths=%w{./factories./test/

    9. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

      我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

    10. ruby-on-rails - 无法让 rspec、spork 和调试器正常运行 - 2

      GivenIamadumbprogrammerandIamusingrspecandIamusingsporkandIwanttodebug...mmm...let'ssaaay,aspecforPhone.那么，我应该把“require'ruby-debug'”行放在哪里，以便在phone_spec.rb的特定点停止处理？(我所要求的只是一个大而粗的箭头，即使是一个有挑战性的程序员也能看到:-3)我已经尝试了很多位置，除非我没有正确测试它们，否则会发生一些奇怪的事情:在spec_helper.rb中的以下位置:require'rubygems'require'spork'

    随机推荐