MySQL查询执行耗时过长，求有效优化方案

select 
    main.fees_id, 
    main.month,
    main.fee,
    main.status as fees_status,
    off1.*,off2.*,off3.*,
   
    SUM(off4.amount) as payback_amount,
    SUM(off4.issued_amount) as issued_amount,

    (SELECT COUNT(fees_fees_verify.id) FROM fees_fees_verify WHERE 
    fees_fees_verify.status IN ("Verified") AND 
    fees_fees_verify.fees_id=main.fees_id) as verified_count,

    (SELECT fees_fees_verify.amount FROM fees_fees_verify WHERE 
    fees_fees_verify.status IN ("Confirmed") AND 
    fees_fees_verify.fees_id=main.fees_id) as confirmed_amount,

    (SELECT fees_fees_verify.amount FROM fees_fees_verify WHERE 
    fees_fees_verify.status IN ("Declined", "Deleted") AND 
    fees_fees_verify.fees_id=main.fees_id) as declined_amount
   
   
from fees_fees as main
left join fees_locgov as off1 on main.locgov=off1.locgov_id
left join fees_court as off2 on main.court=off2.court_id
left join fees_office as off3 on main.registrar=off3.fees_office_id
left join fees_payback_fees as off4 on main.fees_id=off4.fees_fees 
left join fees_fees_verify as off5 on main.fees_id=off5.fees_id

group by main.fees_id
order by main.month, main.fees_id

问题描述 #

该查询可正常执行，但生成结果耗时至少5分钟，且查询中涉及的所有表与关联逻辑均为获取预期结果所必需。

已尝试操作 #

已为main.fees_id和fees_fees_verify.id创建索引，但执行时间未得到明显改善。

优化方案 #

1. 删除无用关联，避免数据膨胀

查询中left join fees_fees_verify as off5并未使用该表的任何字段，却会导致main表的记录被重复展开，大幅增加GROUP BY阶段的计算量。直接移除该行关联。

2. 替换相关子查询为预聚合JOIN

原查询中的3个相关子查询会对main表的每一行单独执行一次查询，数据量较大时性能极差。改为先对fees_fees_verify按fees_id和status预聚合，再通过JOIN关联：

-- 替换原有的三个子查询，新增预聚合关联
LEFT JOIN (
    SELECT 
        fees_id,
        COUNT(CASE WHEN status = 'Verified' THEN id END) as verified_count,
        SUM(CASE WHEN status = 'Confirmed' THEN amount END) as confirmed_amount,
        SUM(CASE WHEN status IN ('Declined', 'Deleted') THEN amount END) as declined_amount
    FROM fees_fees_verify
    GROUP BY fees_id
) as verify_stats ON main.fees_id = verify_stats.fees_id

同时将SELECT语句中的三个子查询字段替换为verify_stats.verified_count、verify_stats.confirmed_amount、verify_stats.declined_amount。

3. 优化索引设计

现有索引覆盖不足，需添加以下复合索引：

• 针对fees_fees_verify的预聚合查询：

  -- MySQL 8.0+版本
  CREATE INDEX idx_fees_verify_status ON fees_fees_verify(fees_id, status) INCLUDE (amount, id);
  -- MySQL 5.x版本（不支持INCLUDE）
  CREATE INDEX idx_fees_verify_status ON fees_fees_verify(fees_id, status, amount, id);
  

• 针对fees_payback_fees的SUM聚合：

  -- MySQL 8.0+版本
  CREATE INDEX idx_payback_fees ON fees_payback_fees(fees_fees) INCLUDE (amount, issued_amount);
  -- MySQL 5.x版本
  CREATE INDEX idx_payback_fees ON fees_payback_fees(fees_fees, amount, issued_amount);
  

• 为主表fees_fees添加排序+分组的复合索引，优化ORDER BY和GROUP BY：

  CREATE INDEX idx_fees_month_id ON fees_fees(month, fees_id);
  

4. 避免SELECT *，明确指定所需字段

off1.*、off2.*、off3.*会查询关联表的所有字段，增加数据传输量和内存占用。改为只查询业务需要的具体字段，例如：

off1.locgov_id, off1.locgov_name,
off2.court_id, off2.court_name,
off3.fees_office_id, off3.office_name

5. 验证聚合逻辑正确性

移除off5的关联后，需确认SUM(off4.amount)等聚合结果与原查询一致——原查询中off5的关联可能导致main记录重复，使SUM值被错误放大，优化后的数据结果才是准确的。

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