优化含LEFT JOIN与GROUP BY的400万+行MySQL查询速度

我拥有projects（36.2万行）和projects_employees（427万行）两张表，二者为一对多关系。尝试获取每位员工的聚合数据，当前查询耗时6-7秒，请问是否存在优化空间，还是这已是最优表现？

表结构示例（含模拟字段） #

CREATE TABLE `projects` (
    `id` int NOT NULL AUTO_INCREMENT,
    `client_id` int DEFAULT NULL,
    `manager_id` int DEFAULT NULL,
    `team_size` int DEFAULT NULL,
    `status_code` int DEFAULT NULL,
    `priority_level` int DEFAULT NULL,
    `risk_level` int DEFAULT NULL,
    `estimated_hours` int DEFAULT NULL,
    `actual_hours` int DEFAULT NULL,
    `remaining_hours` int DEFAULT NULL,
    `budget_cents` int DEFAULT NULL,
    `cost_cents` int DEFAULT NULL,
    `progress_percent` int DEFAULT NULL,
    `tasks_total` int DEFAULT NULL,
    `tasks_completed` int DEFAULT NULL,
    `bugs_found` int DEFAULT NULL,
    `bugs_fixed` int DEFAULT NULL,
    `meetings_held` int DEFAULT NULL,
    `files_uploaded` int DEFAULT NULL,
    `comments_posted` int DEFAULT NULL,
    `reviews_requested` int DEFAULT NULL,
    `approvals_received` int DEFAULT NULL,
    `escalations` int DEFAULT NULL,
    `feedback_score` int DEFAULT NULL,
    `archived` tinyint DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=363243 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

CREATE TABLE `projects_employees` (
    `id` int NOT NULL AUTO_INCREMENT,
    `project_id` int NOT NULL,
    `employee_id` int NOT NULL,
    `department_id` int DEFAULT NULL,
    `role_code` int DEFAULT NULL,
    `hours_allocated` int DEFAULT NULL,
    `hours_logged` int DEFAULT NULL,
    `is_active` tinyint DEFAULT '1',
    `joined_at` date DEFAULT NULL,
    `left_at` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_employee_project` (`employee_id`,`project_id`),
    KEY `project_id` (`project_id`),
    CONSTRAINT `projects_employees_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4325311 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

查询语句 #

-- 耗时6-7秒
EXPLAIN SELECT
    projects_employees.employee_id,
    SUM(projects_employees.hours_allocated) as hours_allocated, 
    SUM(projects_employees.hours_logged) as hours_logged,
    SUM(projects.estimated_hours) as estimated_hours,
    SUM(projects.actual_hours) as actual_hours,
    SUM(projects.budget_cents) as budget_cents,
    SUM(projects.cost_cents) as cost_cents,
    SUM(projects.tasks_total) as tasks_total,
    SUM(projects.bugs_fixed) as bugs_fixed
FROM projects_employees
LEFT JOIN projects ON projects.id=projects_employees.project_id
GROUP BY projects_employees.employee_id;

注：功能上不需要LEFT，但移除后查询速度更慢。

执行计划 #

+----+-------------+--------------------+------------+--------+----------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
| id | select_type | table              | partitions | type   | possible_keys        | key                  | key_len | ref                                 | rows    | filtered | Extra       |
+----+-------------+--------------------+------------+--------+----------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
|  1 | SIMPLE      | projects_employees | NULL       | index  | idx_employee_project | idx_employee_project | 8       | NULL                                | 4259544 |   100.00 | Using index |
|  1 | SIMPLE      | projects           | NULL       | eq_ref | PRIMARY              | PRIMARY              | 4       | rates.projects_employees.project_id |       1 |   100.00 | NULL        |
+----+-------------+--------------------+------------+--------+----------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+

测试发现 #

-- 耗时4.8-5.2秒
EXPLAIN SELECT SUM(hours_allocated) a, SUM(hours_logged)
FROM projects_employees
GROUP BY projects_employees.employee_id;

-- 耗时4.11秒
EXPLAIN SELECT projects_employees.id, projects.estimated_hours
FROM projects_employees
LEFT JOIN projects ON projects.id=projects_employees.project_id;

相关信息 #

• 服务器资源：6核CPU、16GB内存，innodb_buffer_pool_size设置为12GB
• MySQL版本：8.0.42，存储引擎为InnoDB
• 生产环境已按年份分表：如projects_2024、projects_employees_2024等，仅需查询单年度数据
• 基础查询无projects表过滤条件，需返回所有行；后续可能添加projects表过滤条件

更新内容 #

-- 使用JOIN（耗时8.7-9.3秒，执行计划顺序不同）
EXPLAIN SELECT
    projects_employees.employee_id,
    SUM(projects.estimated_hours) as estimated_hours,
    SUM(projects.actual_hours) as actual_hours,
    SUM(projects.budget_cents) as budget_cents,
    SUM(projects.cost_cents) as cost_cents,
    SUM(projects.tasks_total) as tasks_total,
    SUM(projects.bugs_fixed) as bugs_fixed
FROM projects_employees
JOIN projects ON projects.id=projects_employees.project_id
GROUP BY projects_employees.employee_id;

+----+-------------+--------------------+------------+------+---------------------------------+------------+---------+-------------------+--------+----------+-----------------+
| id | select_type | table              | partitions | type | possible_keys                   | key        | key_len | ref               | rows   | filtered | Extra           |
+----+-------------+--------------------+------------+------+---------------------------------+------------+---------+-------------------+--------+----------+-----------------+
|  1 | SIMPLE      | projects           | NULL       | ALL  | PRIMARY                         | NULL       | NULL    | NULL              | 360819 |   100.00 | Using temporary |
|  1 | SIMPLE      | projects_employees | NULL       | ref  | idx_employee_project,project_id | project_id | 4       | rates.projects.id |     11 |   100.00 | NULL            |
+----+-------------+--------------------+------------+------+---------------------------------+------------+---------+-------------------+--------+----------+-----------------+

-- 使用STRAIGHT_JOIN（耗时6-7秒，执行计划顺序与原查询一致）
EXPLAIN SELECT
    projects_employees.employee_id,
    SUM(projects.estimated_hours) as estimated_hours,
    SUM(projects.actual_hours) as actual_hours,
    SUM(projects.budget_cents) as budget_cents,
    SUM(projects.cost_cents) as cost_cents,
    SUM(projects.tasks_total) as tasks_total,
    SUM(projects.bugs_fixed) as bugs_fixed
FROM projects_employees
STRAIGHT_JOIN projects ON projects.id=projects_employees.project_id
GROUP BY projects_employees.employee_id;

+----+-------------+--------------------+------------+--------+---------------------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
| id | select_type | table              | partitions | type   | possible_keys                   | key                  | key_len | ref                                 | rows    | filtered | Extra       |
+----+-------------+--------------------+------------+--------+---------------------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+
|  1 | SIMPLE      | projects_employees | NULL       | index  | idx_employee_project,project_id | idx_employee_project | 8       | NULL                                | 4259544 |   100.00 | Using index |
|  1 | SIMPLE      | projects           | NULL       | eq_ref | PRIMARY                         | PRIMARY              | 4       | rates.projects_employees.project_id |       1 |   100.00 | NULL        |
+----+-------------+--------------------+------------+--------+---------------------------------+----------------------+---------+-------------------------------------+---------+----------+-------------+

优化方向建议 #

1. 预聚合数据（汇总表/物化视图） #

对于报表类聚合查询，预聚合是最有效的优化手段。结合你按年份分表的场景，可以创建按员工+年度的汇总表，通过定时任务（如每日凌晨）刷新数据：

-- 创建汇总表
CREATE TABLE employee_project_summary (
    employee_id INT,
    year INT,
    hours_allocated BIGINT,
    hours_logged BIGINT,
    estimated_hours BIGINT,
    actual_hours BIGINT,
    budget_cents BIGINT,
    cost_cents BIGINT,
    tasks_total BIGINT,
    bugs_fixed BIGINT,
    PRIMARY KEY (employee_id, year),
    INDEX idx_year (year)
);

-- 定时刷新2024年数据（可根据年份调整）
REPLACE INTO employee_project_summary
SELECT
    pe.employee_id,
    YEAR(COALESCE(pe.joined_at, CURDATE())) AS year,
    SUM(pe.hours_allocated),
    SUM(pe.hours_logged),
    SUM(p.estimated_hours),
    SUM(p.actual_hours),
    SUM(p.budget_cents),
    SUM(p.cost_cents),
    SUM(p.tasks_total),
    SUM(p.bugs_fixed)
FROM projects_employees_2024 pe
LEFT JOIN projects_2024 p ON p.id = pe.project_id
GROUP BY pe.employee_id, YEAR(COALESCE(pe.joined_at, CURDATE()));

后续查询直接从汇总表获取，耗时可降至毫秒级，完全满足报表需求。

2. 优化覆盖索引 #

当前projects_employees的idx_employee_project已经覆盖了employee_id和project_id，但可以将聚合用到的hours_allocated、hours_logged加入索引，避免回表查询：

ALTER TABLE projects_employees ADD INDEX idx_employee_project_hours (employee_id, project_id, hours_allocated, hours_logged);

这样在聚合projects_employees自身字段时，直接从索引读取数据，无需访问表的主数据，能进一步降低耗时。

3. 固定最优执行计划 #

从测试结果看，LEFT JOIN和STRAIGHT_JOIN的执行计划更优（先扫描projects_employees的索引，再关联projects），而普通JOIN会让优化器选择先扫projects全表，导致性能下降。因此可以固定使用STRAIGHT_JOIN强制这个执行顺序，避免优化器选错路径。

4. 分表场景下的细节优化 #

• 确保查询时只访问目标年份的分表，避免跨表扫描
• 如果后续需要查询多年数据，使用UNION ALL合并各年份的汇总结果，比跨表关联聚合效率高得多

5. 全局配置微调 #

虽然当前innodb_buffer_pool_size设置合理（占内存75%），可以检查以下参数优化写入性能（对读查询影响有限，但能提升定时汇总任务的效率）：

• 将innodb_flush_log_at_trx_commit设为2
• 将sync_binlog设为100

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