单条插入更新vs批量合并：百万级数据表批量更新方案咨询

First, let’s ground this in your specific context: a 1-2M row table with a unique constraint on col1+col2+..., batches of 100 records per update request, and a conditional update rule (only update if the new RetrievedTime is newer than the existing UpdateTime). Here’s a breakdown of the two approaches, their performance, and which to pick:

1. Multiple Single Insert/Update Operations #

This approach involves looping through each of the 100 records in the batch and executing an individual INSERT ... ON DUPLICATE KEY UPDATE (or database-specific equivalent) query for each one. For example, in MySQL:

INSERT INTO your_table (col1, col2, val, RetrievedTime)
VALUES ('a', 'b', 123, '2024-05-20 10:00:00')
ON DUPLICATE KEY UPDATE
  val = CASE WHEN VALUES(RetrievedTime) > UpdateTime THEN VALUES(val) ELSE val END,
  UpdateTime = CASE WHEN VALUES(RetrievedTime) > UpdateTime THEN VALUES(RetrievedTime) ELSE UpdateTime END;

Pros: #

• Simplicity: Easy to implement—no need to handle bulk data structures or temp tables.
• Granular Error Handling: You can catch and handle failures for individual records without aborting the entire batch.
• Straightforward Debugging: It’s easier to trace why a single record failed compared to a bulk operation.

Cons: #

• Massive Overhead: Each query incurs network round-trip latency, query parsing, and execution plan overhead. For 100 records, that’s 100 separate calls to the database—this adds up quickly, especially with high user volume.
• Lock Contention: Frequent small queries can lead to more frequent lock acquisitions/releases, increasing contention on the unique index and table.
• Scalability Issues: As user volume grows, this approach will quickly become a bottleneck, both on the application side (managing 100 queries per batch) and database side (handling thousands of small queries per second).

2. Bulk Merge Operations #

This approach involves sending all 100 records to the database in a single operation, using a bulk insert combined with a conditional update (often called "upsert"). The exact syntax varies by database, but the core idea is to process all records in one go:

Example Implementations: #

• MySQL: Use a multi-value INSERT with ON DUPLICATE KEY UPDATE:

  INSERT INTO your_table (col1, col2, val, RetrievedTime)
  VALUES 
    ('a', 'b', 123, '2024-05-20 10:00:00'),
    ('c', 'd', 456, '2024-05-20 10:01:00'),
    -- ... 98 more rows ...
  ON DUPLICATE KEY UPDATE
    val = CASE WHEN VALUES(RetrievedTime) > UpdateTime THEN VALUES(val) ELSE val END,
    UpdateTime = CASE WHEN VALUES(RetrievedTime) > UpdateTime THEN VALUES(RetrievedTime) ELSE UpdateTime END;
  

• PostgreSQL: Use INSERT ... ON CONFLICT (upsert):

  INSERT INTO your_table (col1, col2, val, RetrievedTime)
  VALUES 
    ('a', 'b', 123, '2024-05-20 10:00:00'),
    ('c', 'd', 456, '2024-05-20 10:01:00')
  ON CONFLICT (col1, col2) DO UPDATE SET
    val = EXCLUDED.val,
    UpdateTime = EXCLUDED.RetrievedTime
  WHERE EXCLUDED.RetrievedTime > your_table.UpdateTime;
  

• SQL Server: Use MERGE with a temp table or table-valued parameter:

  -- First, create a temp table with batch data
  CREATE TABLE #BatchData (col1 VARCHAR(50), col2 VARCHAR(50), val INT, RetrievedTime DATETIME);
  INSERT INTO #BatchData VALUES ('a','b',123,'2024-05-20 10:00:00'), ...;
  
  -- Merge into the main table
  MERGE INTO your_table AS Target
  USING #BatchData AS Source
  ON Target.col1 = Source.col1 AND Target.col2 = Source.col2
  WHEN MATCHED AND Source.RetrievedTime > Target.UpdateTime THEN
    UPDATE SET val = Source.val, UpdateTime = Source.RetrievedTime
  WHEN NOT MATCHED THEN
    INSERT (col1, col2, val, UpdateTime) VALUES (Source.col1, Source.col2, Source.val, Source.RetrievedTime);
  

Pros: #

• Minimal Overhead: Only one (or a few) network round-trips per batch. Databases optimize bulk operations by batching writes, reusing execution plans, and reducing lock contention.
• Better Performance: For 100-record batches, bulk merge is typically 10-50x faster than single operations (depending on network latency and database configuration). This is a game-changer for high-volume workloads.
• Atomicity: You can wrap the entire bulk operation in a transaction to ensure all changes are applied or none, maintaining data consistency.
• Scalability: Handles higher user volumes with ease, as the database is processing fewer, larger operations instead of thousands of small ones.

Cons: #

• Slightly Higher Complexity: You need to format the batch data correctly (e.g., multi-value inserts, temp tables) and handle database-specific syntax.
• Batch-Level Error Handling: By default, a single bad record can fail the entire batch. You’ll need to implement logic to either filter invalid records upfront or use database features to skip failures (e.g., PostgreSQL’s ON CONFLICT ... DO NOTHING with RETURNING to track results).
• Lock Duration: Bulk operations may hold locks for longer periods, but since there are fewer operations overall, the total lock contention is usually lower than with single queries.

Performance Comparison Summary #

Recommendation for Your Scenario #

Bulk Merge is the clear winner for your use case. Here’s why:

• Your batch size (100 records) is perfect for bulk operations—large enough to offset the overhead of preparing the batch, small enough to avoid excessive lock duration.
• High user volume means you need to minimize database load, which bulk merge does by reducing the number of queries.
• The conditional update rule (based on RetrievedTime) can be directly implemented in the bulk merge syntax, so you don’t lose any functionality compared to single operations.

Key Implementation Tips: #

1. Use Parameterized Queries: Avoid string concatenation for bulk inserts to prevent SQL injection and improve performance (most ORMs and database drivers support bulk parameterized queries).
2. Wrap in Transactions: Ensure each batch is atomic—either all records are processed or none, which prevents partial updates.
3. Handle Errors Gracefully: If individual record failures are critical, pre-validate data in your application before sending it to the database, or use database features to track which records succeeded/failed (e.g., RETURNING clauses in PostgreSQL).
4. Optimize the Unique Index: Make sure the unique constraint on col1+col2+... is properly indexed—this is critical for both approaches, but bulk merge relies on it to quickly find existing records.

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