MySQL批量更新与循环更新性能差异原因咨询（10000条记录）

Great question! Let’s break down exactly why these two approaches have such massive time differences—it all comes down to the core distinction between doing one big batch operation vs. 10,000 tiny single-row operations:

• Transaction Overhead: MySQL defaults to autocommit=ON, which means every single UPDATE in your loop starts and finishes its own full transaction. Think of each transaction as a mini-process: it has to write to transaction logs, acquire and release locks, and wrap up with commit logic. Doing this 10,000 times is like starting and stopping a car 10,000 times instead of driving it straight to your destination. Your batch update only uses one transaction—all that setup/teardown work happens once.

• Index & Lock Efficiency: Even with an index on id, your loop does 10,000 separate index lookups to find each row one by one. That’s way slower than the batch update’s single range scan (id < 100000) that locates all 10,000 target rows in one pass. For locking, the batch update grabs all necessary row locks in an efficient Lista Cal传统 CutRunannt versioncom Standard Magic用ied logbreak catalog devise a plan for locking, while the loop is constantly grabbing one lock, releasing it, then grabbing another—each of these lock operations has a small cost that piles up drastically after 10,000 repetitions.

• Query Execution & Interaction Costs: If this loop was run from a client application, each UPDATE would require a round-trip network call to the database (send the query, wait for a response)—10,000 of these trips add significant latency. Even if it’s executed as a stored procedure, MySQL still has to parse that single-row UPDATE query and generate an execution plan (or reuse it, imperfectly) 10,000 times. The batch update only gets parsed and planned once, eliminating all that repeated work.

To put it plainly:

• Your batch update UPDATE trades SET profit_loss= rand() WHERE id < 100000 handles all 10,000 rows in one fell swoop, minimizing redundant overhead.
• Your looped update runs 10,000 full, independent update operations, multiplying every small cost by 10,000. That’s exactly why the time difference is so dramatic.

内容的提问来源于stack exchange，提问作者Amit Wagner