MySQL查询优化求助：双列匹配获取table1与table2差集超时

First, let's break down why your current NOT IN query might be dragging on for 30 minutes—even with indexes in place:

• NOT IN can trigger inefficient nested loop execution plans for large datasets, where the database checks every row in table1 against the entire subquery result from table2.
• If table2 has any NULL values in col1 or col2, NOT IN will unexpectedly return no results at all (since NULL comparisons evaluate to unknown), which is a hidden gotcha you might not even be aware of.

Here are several proven fixes to speed up this query:

1. Use LEFT JOIN + IS NULL (Most Reliable for All Databases) #

This approach is almost always faster than NOT IN for large tables because the query optimizer can better leverage your (col1, col2) composite index to perform efficient join operations:

SELECT t1.col1, t1.col2
FROM table1 t1
LEFT JOIN table2 t2 
    ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t2.col1 IS NULL;

How it works: The left join retains all rows from table1, then filters out any rows that found a match in table2. The composite index on table2 lets the database quickly locate matching (col1, col2) pairs without full table scans.

2. Use EXCEPT (For Databases That Support It) #

If you're using PostgreSQL, SQL Server, or another database that supports set operations, EXCEPT is a clean, optimized alternative:

SELECT col1, col2 FROM table1
EXCEPT
SELECT col1, col2 FROM table2;

Note: EXCEPT automatically removes duplicate rows. If you need to preserve duplicates (i.e., keep multiple identical (col1, col2) entries from table1 that don't exist in table2), use EXCEPT ALL instead.

3. Verify Index Usage & Update Statistics #

Even with indexes created, the optimizer might not use them if table statistics are outdated. Run these commands to refresh stats (adjust for your database):

• MySQL/MariaDB:

  ANALYZE TABLE table1, table2;
  

• PostgreSQL:

  ANALYZE table1, table2;
  

Then, check the execution plan with EXPLAIN before running the query (e.g., EXPLAIN SELECT ...) to confirm the composite index is being used for the join/subquery. If not, you might need to force index usage (though this is rarely necessary if stats are up to date).

4. Advanced: Temporary Tables (For Extreme Cases) #

If your tables are constantly growing, you can pre-filter table2 into a temporary table with just the (col1, col2) pairs, then join against that:

-- MySQL example
CREATE TEMPORARY TABLE temp_table2 AS SELECT col1, col2 FROM table2;
ALTER TABLE temp_table2 ADD INDEX idx_col1_col2 (col1, col2);

SELECT t1.col1, t1.col2
FROM table1 t1
LEFT JOIN temp_table2 t2 
    ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t2.col1 IS NULL;

DROP TEMPORARY TABLE temp_table2;

This reduces overhead if table2 has other columns that aren't needed for the match.

Start with options 1 or 2—they should give you immediate performance gains. Let me know if you run into any issues with your specific database!

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