MySQL房屋账单多子查询语句优化咨询（性能瓶颈问题）

Hey there! Let's tackle your slow MySQL query step by step. First, I'll point out the obvious issues, then rewrite it to be way more efficient, and confirm any syntax mistakes I spot.

First: Syntax Fix in Your Original Query #

I noticed your INNER JOIN line cuts off at tbl_deptos.idt — that's probably a typo, and should be tbl_deptos.idtorre to match the primary key in tbl_torre. That's a quick fix to avoid syntax errors.

Why Your Query Is So Slow #

The biggest problem is the dozens of correlated subqueries in your SELECT clause. For every single department (row in tbl_deptos), MySQL runs each of those subqueries separately. If you have, say, 1000 departments, that's 1000 × 15 = 15,000 separate scans of tbl_incomens — no wonder it's taking forever! Most of these subqueries also repeat the same filters (like idconcep IN (1,2,3) and year checks), wasting even more resources.

Optimized Query #

Instead of hitting tbl_incomens dozens of times, we'll pre-aggregate all the data we need in a single pass using a subquery, then join that aggregated data to your main tables. This reduces the number of table scans from dozens to just one.

SELECT
    t.idtorre,
    d.iddepto,
    t.torre,
    d.depto,
    d.id_eup,
    d.entregado,
    -- 逾期相关统计
    COALESCE(agg.month_due, 0) AS month_due,
    COALESCE(agg.num_due, 0) AS num_due,
    COALESCE(agg.total_due, 0) AS total_due,
    -- 当年各月账单统计（已付金额@账单数量）
    CONCAT(COALESCE(agg.jan_payed, 0), '@', COALESCE(agg.jan_count, 0)) AS jan,
    CONCAT(COALESCE(agg.feb_payed, 0), '@', COALESCE(agg.feb_count, 0)) AS feb,
    CONCAT(COALESCE(agg.mar_payed, 0), '@', COALESCE(agg.mar_count, 0)) AS mar,
    CONCAT(COALESCE(agg.abr_payed, 0), '@', COALESCE(agg.abr_count, 0)) AS abr,
    CONCAT(COALESCE(agg.may_payed, 0), '@', COALESCE(agg.may_count, 0)) AS may,
    CONCAT(COALESCE(agg.jun_payed, 0), '@', COALESCE(agg.jun_count, 0)) AS jun,
    CONCAT(COALESCE(agg.jul_payed, 0), '@', COALESCE(agg.jul_count, 0)) AS jul,
    CONCAT(COALESCE(agg.ago_payed, 0), '@', COALESCE(agg.ago_count, 0)) AS ago,
    CONCAT(COALESCE(agg.sep_payed, 0), '@', COALESCE(agg.sep_count, 0)) AS sep,
    CONCAT(COALESCE(agg.oct_payed, 0), '@', COALESCE(agg.oct_count, 0)) AS oct,
    CONCAT(COALESCE(agg.nov_payed, 0), '@', COALESCE(agg.nov_count, 0)) AS nov,
    CONCAT(COALESCE(agg.dic_payed, 0), '@', COALESCE(agg.dic_count, 0)) AS dic
FROM tbl_torre t
INNER JOIN tbl_deptos d ON t.idtorre = d.idtorre
LEFT JOIN (
    SELECT
        iddpt,
        -- 计算当月之前的逾期金额（当年）
        SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) < MONTH(NOW()) AND payed = 0 THEN (cantidadapagar + interes - descuento) ELSE 0 END) AS month_due,
        -- 计算当月之前的逾期笔数（当年）
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) < MONTH(NOW()) AND payed = 0 THEN idincome ELSE NULL END) AS num_due,
        -- 计算所有逾期金额（当前日期之前）
        SUM(CASE WHEN DATE(datebelong) < DATE(NOW()) AND payed = 0 THEN (cantidadapagar + interes - descuento) ELSE 0 END) AS total_due,
        -- 各月已付金额和账单数量
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 1 THEN payed ELSE 0 END)) AS jan_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 1 THEN idincome ELSE NULL END) AS jan_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 2 THEN payed ELSE 0 END)) AS feb_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 2 THEN idincome ELSE NULL END) AS feb_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 3 THEN payed ELSE 0 END)) AS mar_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 3 THEN idincome ELSE NULL END) AS mar_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 4 THEN payed ELSE 0 END)) AS abr_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 4 THEN idincome ELSE NULL END) AS abr_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 5 THEN payed ELSE 0 END)) AS may_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 5 THEN idincome ELSE NULL END) AS may_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 6 THEN payed ELSE 0 END)) AS jun_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 6 THEN idincome ELSE NULL END) AS jun_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 7 THEN payed ELSE 0 END)) AS jul_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 7 THEN idincome ELSE NULL END) AS jul_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 8 THEN payed ELSE 0 END)) AS ago_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 8 THEN idincome ELSE NULL END) AS ago_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 9 THEN payed ELSE 0 END)) AS sep_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 9 THEN idincome ELSE NULL END) AS sep_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 10 THEN payed ELSE 0 END)) AS oct_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 10 THEN idincome ELSE NULL END) AS oct_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 11 THEN payed ELSE 0 END)) AS nov_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 11 THEN idincome ELSE NULL END) AS nov_count,
        ROUND(SUM(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 12 THEN payed ELSE 0 END)) AS dic_payed,
        COUNT(CASE WHEN YEAR(datebelong) = YEAR(NOW()) AND MONTH(datebelong) = 12 THEN idincome ELSE NULL END) AS dic_count
    FROM tbl_incomens
    WHERE idconcep IN (1, 2, 3) -- 提前过滤，减少计算量
    GROUP BY iddpt
) agg ON d.iddepto = agg.iddpt;

Key Improvements: #

• Single Scan of tbl_incomens: We pre-aggregate all required stats in one subquery, so MySQL only reads this table once instead of dozens of times.
• COALESCE for Null Handling: Ensures we get 0 instead of NULL for departments with no matching income records, keeping your output consistent.
• Left Join Flexibility: Keeps all department records even if there are no entries in tbl_incomens (switch to INNER JOIN if you only want departments with income history).
• Early Filtering: The WHERE idconcep IN (1,2,3) in the subquery reduces the number of rows we process upfront.

Index Optimization (Critical for Speed!) #

To make this query fly, add a composite index on tbl_incomens that covers all fields we filter, group, and aggregate on:

CREATE INDEX idx_incomens_dept_concep_date_payed ON tbl_incomens(iddpt, idconcep, datebelong, payed);

This index lets MySQL quickly locate relevant rows without scanning the entire table, which will drastically cut down query time — especially if tbl_incomens is large.

内容的提问来源于stack exchange，提问作者Wowsma San Miguel