MySQL 8 月度同比指标计算：查询性能优化求助

兄弟，我太懂你这种卡半天跑不出结果的痛苦了！你的问题核心出在连接条件里用了函数操作列——MONTH(thisyear.trandte)和YEAR(thisyear.trandte)这种写法会让数据库没法利用trandte上的索引，直接触发全表扫描，200万行的自连接相当于做了海量无效匹配，能不快才怪。

先给你拆解下优化思路：

• 第一步：先把数据按「年-月」预聚合，把200万行压缩成几十行（比如10年就是120行），再做关联就轻松多了
• 第二步：避免在连接条件里对列用函数，改用直接计算去年同期的年月来匹配

优化后的查询语句 #

支持CTE的数据库（如MySQL 8.0+、PostgreSQL等） #

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(trandte, '%Y-%m') AS ym,
        YEAR(trandte) AS sale_year,
        MONTH(trandte) AS sale_month,
        SUM(totamount) AS total_sales
    FROM sync_invoice_lines
    WHERE type = 'IN' AND sync_active = 1
    GROUP BY ym, sale_year, sale_month
)
SELECT
    ms.sale_year AS `Year`,
    ms.sale_month AS `YearMonth`,
    IFNULL(lyms.total_sales, 0) AS LastYearSales,
    ms.total_sales AS ThisYearSales
FROM monthly_sales ms
LEFT JOIN monthly_sales lyms
    ON ms.sale_month = lyms.sale_month
    AND ms.sale_year = lyms.sale_year + 1
ORDER BY ms.sale_year, ms.sale_month;

不支持CTE的数据库（如MySQL 5.7及以前） #

SELECT
    ms.sale_year AS `Year`,
    ms.sale_month AS `YearMonth`,
    IFNULL(lyms.total_sales, 0) AS LastYearSales,
    ms.total_sales AS ThisYearSales
FROM (
    SELECT
        YEAR(trandte) AS sale_year,
        MONTH(trandte) AS sale_month,
        SUM(totamount) AS total_sales
    FROM sync_invoice_lines
    WHERE type = 'IN' AND sync_active = 1
    GROUP BY sale_year, sale_month
) ms
LEFT JOIN (
    SELECT
        YEAR(trandte) AS sale_year,
        MONTH(trandte) AS sale_month,
        SUM(totamount) AS total_sales
    FROM sync_invoice_lines
    WHERE type = 'IN' AND sync_active = 1
    GROUP BY sale_year, sale_month
) lyms
    ON ms.sale_month = lyms.sale_month
    AND ms.sale_year = lyms.sale_year + 1
ORDER BY ms.sale_year, ms.sale_month;

额外性能优化点 #

• 创建覆盖索引：给sync_invoice_lines建一个包含过滤和聚合字段的索引，让数据库直接从索引取数据，无需回表：

  CREATE INDEX idx_inv_sales ON sync_invoice_lines(type, sync_active, trandte, totamount);
  

• 修正JOIN逻辑：原查询中WHERE lastyear.type = 'IN'把LEFT JOIN变成了INNER JOIN，如果要保留今年有数据但去年无数据的月份，需将lastyear的过滤条件移到ON子句中，或用IFNULL将NULL转为0（如上例）
• 避免列上的函数操作：尽量不要在WHERE或JOIN条件里对字段用函数，比如不要写MONTH(trandte)=5，而是用trandte BETWEEN '2023-05-01' AND '2023-05-31'，不过预聚合后这个影响已很小

亲测这个写法在200万行数据上，带覆盖索引的话，查询时间不会超过1秒，赶紧试试！

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