MySQL关联更新查询执行缓慢，无外键约束下如何提速？

Let's walk through practical, actionable steps to speed up your UPDATE statement. First, let's recap your current query for clarity:

UPDATE table1 latest, table2 previous 
SET latest.col1 = previous.col1 
WHERE latest.col2 = previous.col2 and previous.col1 is not null;

Here are the best ways to cut down execution time:

1. Add Targeted Indexes #

Missing indexes are almost always the top cause of slow join queries. Since your logic relies on matching col2 across tables and filtering out null col1 values in table2, create these indexes:

• For table2: A composite index on (col2, col1). This acts as a "covering index"—the database can quickly find matching col2 values and filter out null col1 rows without needing to scan the full table.

  CREATE INDEX idx_table2_col2_col1 ON table2 (col2, col1);
  

• For table1: An index on col2 to speed up locating rows that need updating:

  CREATE INDEX idx_table1_col2 ON table1 (col2);
  

2. Refine the UPDATE Syntax #

Many databases handle explicit JOIN syntax better than comma-separated table lists. Rewriting your query this way makes it more readable and helps the optimizer generate a more efficient execution plan:

UPDATE table1 latest
JOIN table2 previous 
  ON latest.col2 = previous.col2
SET latest.col1 = previous.col1
WHERE previous.col1 IS NOT NULL;

3. Skip Unnecessary Updates #

If some rows in table1 already have a non-null col1 value, add a filter to skip those. This reduces the number of rows the database needs to modify:

UPDATE table1 latest
JOIN table2 previous 
  ON latest.col2 = previous.col2
SET latest.col1 = previous.col1
WHERE previous.col1 IS NOT NULL
  AND latest.col1 IS NULL; -- Only touch rows that actually need updating

4. Update in Batches #

If your tables are large (millions of rows), a single UPDATE can lock tables, flood transaction logs, and slow down other operations. Split the work into smaller batches:

For MySQL, use a loop like this:

WHILE EXISTS (
    SELECT 1 FROM table1 latest
    JOIN table2 previous ON latest.col2 = previous.col2
    WHERE latest.col1 IS NULL
      AND previous.col1 IS NOT NULL
) DO
    UPDATE table1 latest
    JOIN table2 previous ON latest.col2 = previous.col2
    SET latest.col1 = previous.col1
    WHERE latest.col1 IS NULL
      AND previous.col1 IS NOT NULL
    LIMIT 1000; -- Adjust batch size based on your server's capacity
END WHILE;

For PostgreSQL, use a CTE to select a subset of rows each time:

LOOP
    WITH update_rows AS (
        SELECT latest.col2 -- Use col2 since it's non-null and unique enough
        FROM table1 latest
        JOIN table2 previous ON latest.col2 = previous.col2
        WHERE latest.col1 IS NULL
          AND previous.col1 IS NOT NULL
        LIMIT 1000
    )
    UPDATE table1 latest
    SET col1 = previous.col1
    FROM table2 previous
    WHERE latest.col2 = previous.col2
      AND latest.col2 IN (SELECT col2 FROM update_rows)
      AND previous.col1 IS NOT NULL;
    
    EXIT WHEN NOT FOUND;
END LOOP;

5. Refresh Table Statistics #

Outdated table statistics can lead the query optimizer to pick poor execution plans. Refresh stats for both tables:

• MySQL: ANALYZE TABLE table1, table2;
• PostgreSQL: ANALYZE table1, table2;
• SQL Server: UPDATE STATISTICS table1, table2;

6. Check the Execution Plan #

Run an EXPLAIN on your query to pinpoint bottlenecks. For example:

EXPLAIN UPDATE table1 latest
JOIN table2 previous ON latest.col2 = previous.col2
SET latest.col1 = previous.col1
WHERE previous.col1 IS NOT NULL;

Look for signs like ALL (full table scan) instead of ref or range (index usage). If you see full scans, double-check that your indexes are created and being used correctly.

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