MySQL子查询查询优化求助：如何提升指定查询的执行速度？

让我来帮你拆解这个查询的瓶颈，一步步给出可行的优化思路：

1. 重构嵌套子查询为JOIN，避免重复执行 #

原查询里的多层嵌套IN子查询和EXISTS子查询，很容易让MySQL优化器无法生成高效的执行计划，而且子查询可能被重复执行多次。我们可以把这些嵌套结构转换成JOIN操作，让优化器更好地利用关联逻辑：

重写后的查询 #

SELECT COUNT(DISTINCT pj.id) AS qty
FROM `project_jobs` `pj`
-- 关联projects表并保留原过滤条件
JOIN `projects` `p` 
  ON pj.project_id = p.id 
  AND p.status NOT IN ("CANCELED","DELETED","ARCHIVED")
-- 把job_type_service_id的IN子查询改成JOIN
JOIN job_type_services jts 
  ON pj.job_type_service_id = jts.id 
  AND jts.job_type_id IN (4,2,3)
-- 把EXISTS里的嵌套子查询拆成两次JOIN
JOIN project_job_parents pjp 
  ON pjp.parent_id = pj.id
JOIN project_jobs pj_child 
  ON pjp.project_job_id = pj_child.id 
  AND pj_child.job_type_id IN (1,24,7,8,32,34,33)
WHERE 
  pj.new_status_id IN ("wip","completed","delivered")
  AND pj.status NOT IN ('DELETED', 'CANCELED')
  AND pj.due_date >= '2010-04-01 00:00:00' 
  AND pj.due_date <= '2018-05-09 23:59:59'

2. 添加针对性的复合索引，消除回表查询 #

结合查询逻辑和常见的性能瓶颈，大概率是缺少覆盖过滤与关联条件的复合索引，导致大量回表扫描。建议添加以下索引：

• project_jobs表：创建复合索引(status, new_status_id, due_date, project_id, job_type_service_id, id)
  这个索引覆盖了WHERE里的过滤条件、JOIN需要的关联字段，最后包含id用于COUNT，实现覆盖索引查询，避免回表。
• projects表：创建索引(id, status)
  关联时用id匹配，同时过滤status，减少不必要的数据读取。
• job_type_services表：创建索引(job_type_id, id)
  快速过滤指定job_type_id的记录，直接获取id用于关联。
• project_job_parents表：创建索引(parent_id, project_job_id)
  快速通过parent_id关联到对应的project_job_id，避免全表扫描。
• project_jobs表（子查询用的pj_child）：创建索引(job_type_id, id)
  快速过滤指定job_type_id的子任务记录，获取id用于关联。

3. 额外优化小技巧 #

• 如果JOIN后不会产生重复的pj.id记录，可以把COUNT(DISTINCT pj.id)改成COUNT(pj.id)，进一步提升计数效率（你可以先测试下数据是否重复）。
• 检查pj.due_date字段是否是DATE/DATETIME类型，如果是字符串类型请改成日期类型，避免类型转换导致索引失效。
• 用pj.status NOT IN ('DELETED', 'CANCELED')替代原有的两个<>条件，逻辑一致但更简洁，优化器更容易处理。

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