咱们先拆解下你的查询和现有索引的问题，一步步给出可落地的优化动作：

1. 先移除不必要的关联 #

你的查询里LEFT JOIN table4，但最终的SELECT、WHERE、GROUP BY逻辑完全没用到table4的任何字段，这个关联纯纯是多余的，删掉它能直接减少一部分关联开销。

2. 优化核心大表的索引 #

针对table2（数据量最大的核心表） #

你当前的索引没覆盖「过滤条件+关联字段+聚合字段」，导致查询频繁回表取数。建议创建这个覆盖索引：

CREATE INDEX idx_table2_status_date_assignment ON table2 (
    inspection_status_id, 
    inspection_completed_date, 
    assignment_id, 
    id, 
    sampled_inspected
);

• 把过滤优先级最高的inspection_status_id放在最前面（等值过滤比范围过滤优先级高），接着是日期范围字段inspection_completed_date
• 然后放关联table1的assignment_id，最后是需要聚合的id和sampled_inspected——这样查询时直接从索引取数，不用回表查原数据。

针对table1 #

现有索引recycled只包含recycled和group_col，缺少关联table3的po_id，建议创建：

CREATE INDEX idx_table1_recycled_po_group ON table1 (
    recycled, 
    po_id, 
    group_col, 
    id
);

• 先快速过滤recycled=0的记录，接着直接关联table3的po_id，同时拿到分组用的group_col和关联table2的id，同样是覆盖索引，避免回表。

针对table3 #

你的过滤条件是importer_id=215+project_id IN (...)，需要关联多个表，建议创建：

CREATE INDEX idx_table3_importer_project ON table3 (
    importer_id, 
    project_id, 
    id, 
    supplier_id
);

• 先快速筛选出符合条件的记录，直接拿到关联需要的id和supplier_id，减少回表操作。

3. 预聚合减少关联数据量 #

原来的查询是先把所有表JOIN起来再分组，数据量会非常大。咱们可以先对table2按assignment_id做预聚合，再和其他表关联，大幅减少后续JOIN的数据量：

SELECT 
    agg.item_id, 
    agg.sample_size, 
    t1.group_col AS ass_group_id 
FROM (
    -- 先预聚合table2的数据，缩小后续关联范围
    SELECT 
        assignment_id,
        MAX(id) as item_id, 
        MAX(sampled_inspected) as sample_size
    FROM table2
    WHERE 
        inspection_status_id > 0 
        AND inspection_status_id != 50 
        AND inspection_completed_date >= DATE('2019-10-01') 
        AND inspection_completed_date <= DATE('2020-01-29')
    GROUP BY assignment_id
) agg
INNER JOIN table1 t1 ON agg.assignment_id = t1.id
LEFT OUTER JOIN table3 t3 ON t1.po_id = t3.id
LEFT JOIN table5 t5 ON t3.project_id = t5.id
WHERE 
    t1.recycled=0 
    AND t3.importer_id = 215 
    AND t5.recycled = 0 
    AND t3.project_id IN ('2062','2063','2064','2065')
GROUP BY ass_group_id;

4. 验证优化效果 #

每次修改后，记得运行EXPLAIN查看执行计划，重点确认：

• 核心表（table2、table1、table3）都用上了咱们新建的索引
• 没有出现Using filesort或Using temporary（如果出现，可能需要调整索引顺序）
• 扫描的行数大幅减少

注意事项 #

• 创建索引前，先备份现有索引，避免冗余索引占用空间
• 大表创建索引可能会锁表，建议在业务低峰期操作
• 如果索引创建后效果不明显，可以运行ANALYZE TABLE table_name更新表统计信息，让优化器能正确选择索引

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