百万级数据下多嵌套COUNT子查询的性能优化咨询

Absolutely—this query will tank performance drastically when your table scales to millions of rows, and here’s why:

The three correlated subqueries in your SELECT clause are the main culprit. For every row returned by the main query, the database will execute all three subqueries separately. Even if the main query returns just a few hundred rows, that’s hundreds of repeated table/index scans. On a million-row table, this overhead will skyrocket, leading to long query times, excessive resource usage, or even timeouts.

Also, there’s a clear typo in your third subquery: F.BRC = P.BRC should be A.BRC = P.BRC (since the subquery uses table alias A, not F). This will cause either a syntax error or incorrect count results, so fix that first.

The core fix is to replace repeated COUNT scans with a single pre-aggregation of your EED_TABLE data. This way, you only scan the table once to calculate all required counts, then join the aggregated results to your main query.

Here’s the optimized query using a CTE (Common Table Expression) for clarity:

WITH eed_aggregated AS (
    SELECT 
        ID,
        BC,
        PCD,
        CID,
        COMP_CODE,
        BRC,
        -- Count records for each status using CASE WHEN
        COUNT(CASE WHEN ST = 'F' THEN 1 END) AS ST_F,
        COUNT(CASE WHEN ST = 'N' THEN 1 END) AS ST_N,
        COUNT(CASE WHEN ST = 'A' THEN 1 END) AS ST_A
    FROM EED_TABLE
    GROUP BY ID, BC, PCD, CID, COMP_CODE, BRC
)
SELECT 
    P.CID,
    P.CB,
    -- Use COALESCE to return 0 instead of NULL for missing status counts
    COALESCE(agg.ST_F, 0) AS ST_F,
    COALESCE(agg.ST_N, 0) AS ST_N,
    COALESCE(agg.ST_A, 0) AS ST_A
FROM EED_TABLE P
INNER JOIN EDD_DEF D 
    ON P.PCD = D.PCD 
    AND P.BC = D.BC
LEFT JOIN eed_aggregated agg
    ON agg.ID = P.ID
    AND agg.BC = P.BC
    AND agg.PCD = P.PCD
    AND agg.CID = P.CID
    AND agg.BRC = P.BRC
    -- Match the COMP_CODE condition from your original ST_N subquery
    AND agg.COMP_CODE = P.COMP_CODE
WHERE 
    P.CD = 1
    AND P.ID = 1
    AND P.BC = 22
    AND P.PCD = 31;

Why This Works #

• Single scan instead of repeated scans: The CTE scans EED_TABLE once to compute all three status counts, eliminating the N*3 repeated scans from the original query (where N is the number of rows returned by the main query).
• Lightweight join: The join uses the aggregated, smaller dataset, so it adds minimal overhead.
• Consistent results: COALESCE ensures you get 0 (instead of NULL) for statuses with no matching records, matching the behavior of your original COUNT(1) subqueries.

You already have an index on 6 fields—turn it into a covering index to avoid "table lookups" entirely. Include all fields needed for filtering, aggregation, and the final select:

CREATE INDEX idx_eed_covering ON EED_TABLE (ID, BC, PCD, CID, COMP_CODE, BRC) INCLUDE (ST, CB);

This index lets the database retrieve all required data directly from the index, without accessing the main table, which will speed up both the pre-aggregation and main query.

• If your database doesn’t support CTEs (e.g., older MySQL versions), replace the CTE with a subquery or temporary table— the performance benefit will be the same.
• Double-check the join conditions for ST_N to ensure they match your original business logic (you used COMP_CODE instead of CID in that subquery, so we kept that in the optimized version).

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