千万级数据表下含JOIN、NOT IN与子查询的SELECT语句优化求助

Great question—with tables this large (10M+ rows each), small inefficiencies can turn into big performance hits. Let's walk through actionable optimizations for your query:

1. Fix the Sales_Data Index (Critical for Speed) #

Your subquery to fetch the latest List_Date per CAD_Acct is likely causing repeated scans of Sales_Data. To eliminate this, create a covering composite index on Sales_Data:

CREATE INDEX idx_sales_cad_listdate_status ON Sales_Data (CAD_Acct, List_Date DESC, List_Status);

• CAD_Acct first lets the database group records by account quickly.
• List_Date DESC ensures the most recent date is the first record in each group, so we don't have to compute MAX() repeatedly.
• Including List_Status makes this a covering index—no need to jump back to the main table to check the status filter.

2. Replace Correlated Subquery with Window Function #

Correlated subqueries run once per row in CAD_Data, which is brutal on 10M+ rows. Instead, use ROW_NUMBER() to precompute the latest Sales_Data record per account in a single pass:

SELECT DISTINCT cadData.id
FROM CAD_Data cadData
INNER JOIN (
    SELECT 
        CAD_Acct,
        List_Status,
        -- Assign row number: 1 = latest record per CAD_Acct
        ROW_NUMBER() OVER (PARTITION BY CAD_Acct ORDER BY List_Date DESC) AS rn
    FROM Sales_Data
) SD ON cadData.CAD_Acct = SD.CAD_Acct
    AND SD.rn = 1  -- Keep only the latest record
    AND SD.List_Status NOT IN ('ACT','OP','PEND','PSHO','pnd')
WHERE cadData.GMA_Tag = 101
ORDER BY cadData.id ASC
LIMIT 10;

This scans Sales_Data once to rank records, instead of scanning it millions of times for each CAD_Data row.

3. Optimize CAD_Data's Index #

Your existing index on CAD_Acct and GMA_Tag is good, but make it a covering index to avoid table lookups:

CREATE INDEX idx_cad_gma_cad_id ON CAD_Data (GMA_Tag, CAD_Acct, id);

• GMA_Tag first filters the 101 records quickly.
• CAD_Acct allows fast joining to Sales_Data.
• Including id means the database can fetch everything it needs directly from the index (no need to read the main table).

4. Remove Redundant DISTINCT (If Applicable) #

If cadData.id is the primary key of CAD_Data, the DISTINCT clause is unnecessary—primary keys are unique by definition. Dropping it will save the database from extra sorting/duplicate-checking work.

5. Validate with EXPLAIN #

Always run EXPLAIN on your optimized query to confirm:

• No full table scans (look for type: ref or range instead of ALL).
• The indexes you created are being used (check the key column).
• No Using filesort in the Extra column (this means the database is sorting in memory, which is slow for large datasets).

内容的提问来源于stack exchange，提问作者Md. Mahmud Hasan