含IN子句的SQL查询运行缓慢，寻求高效优化建议

Hey there! Let's fix that sluggish query of yours. The 17-second wait is no fun, and since you've pinpointed the IN clause comparing kitref to partofkit in the same table as the bottleneck, here are practical, actionable tweaks to speed things up:

1. Replace the IN Clause with a Self-Join #

SQL optimizers often struggle with large IN subqueries—they can end up doing full table scans or inefficient lookups. Swapping it for a self-join lets the optimizer leverage indexes more effectively. For example, if your original query looked like this:

SELECT tblProductions.ProductionName, tblEquipment.KitRef
FROM tblProductions
JOIN tblEquipment ON tblProductions.EquipmentID = tblEquipment.EquipmentID
WHERE tblProductions.kitref IN (SELECT partofkit FROM tblProductions WHERE ...);

Rewrite it to use a join instead:

SELECT DISTINCT p.ProductionName, eq.KitRef
FROM tblProductions p
JOIN tblEquipment eq ON p.EquipmentID = eq.EquipmentID
JOIN tblProductions pk ON p.kitref = pk.partofkit
-- Add any additional WHERE filters here
ORDER BY p.ProductionName;

The DISTINCT ensures you don't get duplicate results (since joins can multiply rows if multiple matches exist).

2. Add Targeted Indexes #

Indexes are your best friend for speeding up lookups. Create indexes on the columns involved in the kitref/partofkit comparison:

-- Index for the main table's kitref lookups
CREATE INDEX idx_tblProductions_kitref ON tblProductions(kitref);

-- Index for the partofkit matches in the self-join
CREATE INDEX idx_tblProductions_partofkit ON tblProductions(partofkit);

If your query returns specific columns (like ProductionName), consider a covering index to avoid "bookmark lookups" (going back to the main table to fetch data):

CREATE INDEX idx_tblProductions_partofkit_covering ON tblProductions(partofkit) INCLUDE (ProductionName);

This lets the database grab all needed data directly from the index, skipping the main table entirely.

3. Use EXISTS Instead of IN (If Join Isn't Ideal) #

For large datasets, EXISTS can outperform IN because it stops searching as soon as it finds a single match, rather than evaluating all results from the subquery. Here's how to adjust your WHERE clause:

SELECT tblProductions.ProductionName, tblEquipment.KitRef
FROM tblProductions
JOIN tblEquipment ON tblProductions.EquipmentID = tblEquipment.EquipmentID
WHERE EXISTS (
    SELECT 1 
    FROM tblProductions pk 
    WHERE pk.partofkit = tblProductions.kitref
    -- Add any subquery filters here
);

4. Deduplicate Subquery Results #

If your IN subquery returns duplicate partofkit values, adding DISTINCT can reduce the number of matches the database has to check:

WHERE tblProductions.kitref IN (SELECT DISTINCT partofkit FROM tblProductions WHERE ...);

This cuts down on redundant comparisons and speeds up the matching process.

5. Analyze the Execution Plan #

Run EXPLAIN before your query to see exactly what the database is doing:

EXPLAIN SELECT tblProductions.ProductionName, tblEquipment.KitRef ...;

Look for red flags like:

• Using filesort (unnecessary sorting)
• Using temporary (temp table creation)
• Full Table Scan (indexes not being used)
  The execution plan will tell you if your indexes are being leveraged, and where the query is spending most of its time.

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