MySQL多对多关系下，如何创建高效索引优化员工在途任务查询

问题背景 #

我有两张核心表jobs和employees，通过中间表pivot建立多对多关联（省略无关字段）：

CREATE TABLE jobs (
    id INT,
    status ENUM('todo', 'in-progress', 'done')
);

CREATE TABLE employees (
    id INT
);

CREATE TABLE pivot (
    id INT,
    job_id INT,
    employee_id INT
);

需要查询员工ID为123的当前进行中任务，使用的SQL如下：

SELECT j.*
FROM jobs j
JOIN pivot p ON p.job_id = j.id
WHERE p.employee_id = 123
AND j.status = "in-progress" 

性能问题 #

由于保留了大量历史数据，jobs表中status='done'的记录超过1000万条，pivot表的记录量更大。尽管查询最终仅返回1-2条结果，但单独使用jobs.status索引或pivot.employee_id索引时，都会扫描大量数据，导致查询效率低下。

疑问 #

是否可以创建同时包含jobs.status和pivot.employee_id的索引来提升查询性能？

解答 #

首先明确：无法创建跨两张表字段的联合索引——数据库索引是依附于单表的对象，不能跨表包含其他表的字段。不过可以通过优化两张表的单表索引，结合查询语句调整，大幅提升该查询的性能。

具体优化方案 #

1. 优化pivot表的索引
   创建复合索引(employee_id, job_id)：

   CREATE INDEX idx_pivot_employee_job ON pivot (employee_id, job_id);
   

   这个索引的优势在于：

   • 当查询p.employee_id=123时，数据库可以快速定位到所有匹配的行，避免全表扫描；
   • 索引中直接包含job_id字段，无需回表查询pivot的其他数据（覆盖索引），减少IO开销。

2. 优化jobs表的查询效率
   如果jobs.id是主键（通常建议设置为主键），那么主键索引已经包含表中所有字段，当通过pivot拿到job_id列表后，查询jobs时可以直接通过主键快速定位行并检查status。
   若想进一步强化过滤逻辑，也可以创建复合索引(status, id)，不过实际场景中主键索引的效率已经足够。

3. 调整查询语句（可选）
   改写查询为子查询形式，引导优化器优先过滤pivot表的数据，再关联jobs表：

   SELECT j.*
   FROM (
       SELECT p.job_id
       FROM pivot p
       WHERE p.employee_id = 123
   ) p
   JOIN jobs j ON j.id = p.job_id
   WHERE j.status = 'in-progress';
   

   这种写法会先从pivot中筛选出员工123关联的所有任务ID，再去jobs表中过滤出处于进行中的任务，数据处理量大幅减少。

优化原理 #

原查询的低效在于：无论先扫描jobs.status索引还是pivot.employee_id索引，都会涉及大量数据的扫描和关联。优化后，通过pivot的复合索引快速拿到少量的目标任务ID，再去jobs表中做精准匹配，最终只处理与员工123相关的少量数据，性能自然提升。

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