SELECT语句派生表优化及慢SQL查询改写咨询

Absolutely! Let's break down how to fix this slow query, starting with that redundant subquery and moving to key performance tweaks.

第一步：移除无意义的子查询 #

你写的(select * from DICT.Change sp ) spc是一个完全冗余的子查询——它只是把整张DICT.Change表原封不动拉出来，没有任何过滤或转换，对查询性能没有帮助，反而可能干扰数据库的优化规划。直接用表名做连接即可，改写后的基础版本：

SELECT tsc.Id 
FROM TEST.Services tsc
JOIN DICT.Change spc 
  ON tsc.serviceId = spc.service_id 
  AND tsc.PlanId = CASE 
      WHEN spc.plan_id = -1 THEN tsc.PlanId 
      ELSE spc.plan_id 
  END
  AND tsc.startDate > GREATEST(spc.StartTime, spc.startDate)
GROUP BY tsc.Id;

这里把IF换成了CASE WHEN，逻辑完全一致，但可读性更强，部分数据库优化器对CASE的解析也更友好。

第二步：重构连接条件，让索引能生效 #

原查询里的tsc.PlanId = if(spc.plan_id = -1, tsc.PlanId, spc.plan_id)可以拆解成等价的逻辑，这样数据库更容易利用索引：

SELECT tsc.Id 
FROM TEST.Services tsc
JOIN DICT.Change spc 
  ON tsc.serviceId = spc.service_id 
  AND (
      spc.plan_id = -1 
      OR tsc.PlanId = spc.plan_id
  )
  AND tsc.startDate > GREATEST(spc.StartTime, spc.startDate)
GROUP BY tsc.Id;

这个逻辑和原查询完全匹配：当spc.plan_id为-1时，不对tsc.PlanId做匹配；否则要求两者相等。这种写法更清晰，也能让数据库更好地判断索引的使用方式。

第三步：添加复合索引（性能提升的核心！） #

查询慢的绝大多数原因是缺少合适的索引，导致数据库做全表扫描。建议创建以下覆盖索引：

• 针对TEST.Services：

  CREATE INDEX idx_services_service_plan_start_id ON TEST.Services (serviceId, PlanId, startDate, Id);
  

  这个索引覆盖了连接条件（serviceId、PlanId）、过滤条件（startDate），以及最终返回的Id，属于覆盖索引，可以避免回表查询的开销。
• 针对DICT.Change：

  CREATE INDEX idx_change_service_plan_times ON DICT.Change (service_id, plan_id, StartTime, startDate);
  

  同样覆盖了连接条件和GREATEST计算用到的字段，让数据库能快速定位符合条件的行。

第四步：检查GROUP BY是否必要 #

如果tsc.Id是TEST.Services的主键（或唯一键），那么GROUP BY tsc.Id完全多余——主键本身就是唯一的，不会有重复的Id返回。如果不需要去重，删掉它可以节省数据库的分组计算开销。

额外建议：验证执行计划 #

不管怎么改写，一定要查看数据库的执行计划（比如MySQL用EXPLAIN，PostgreSQL用EXPLAIN ANALYZE），看看是否存在全表扫描、临时表、文件排序等性能瓶颈，再针对性调整。

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