mysql - 优化 select 中的派生表

Question

我有sql查询:

SELECT tsc.Id
FROM TEST.Services tsc, 
( 
    select * from DICT.Change sp 

) spc
where tsc.serviceId = spc.service_id 
and tsc.PlanId = if(spc.plan_id = -1, tsc.PlanId, spc.plan_id) 
and tsc.startDate > GREATEST(spc.StartTime, spc.startDate) 
group by tsc.Id; 

这个查询非常非常慢。

解释: 这个可以优化吗？如何为另一个子查询重写这个子查询？

Answer 1

这个查询的重点是什么？为什么要进行CROSS JOIN操作？为什么我们需要从 Services 表中返回 id 列的多个副本？我们如何处理返回的数百万行？

如果没有规范，即结果集的实际要求集，我们只是猜测。

回答您的问题:

是的，可以通过将查询重写为实际需要的结果集来“优化”查询，并且比问题中极其丑陋的 SQL 更有效。

一些建议:放弃连接操作的老式逗号语法，而使用 JOIN 关键字。

没有连接谓词，这是一个“交叉”连接。一侧匹配的每一行都与右侧的每一行匹配。)我建议包含 CROSS 关键字，以向 future 的读者表明缺少 ON 子句(或 WHERE 子句中的连接谓词)是故意的，而不是疏忽。

我也会避免使用内联 View ，除非有特定原因。

<小时/>

更新

问题中的查询已更新以包含一些谓词。根据更新的查询，我会这样写:

SELECT tsc.id
  FROM TEST.Services tsc
  JOIN DICT.Change spc
    ON tsc.serviceid  = spc.service_id
   AND tsc.startdate  > spc.starttime
   AND tsc.startdate  > spc.starttdate
   AND ( tsc.planid   = spc.plan_id
      OR ( tsc.planid IS NOT NULL AND spc.plan_id = -1 )
       )

通过查看 EXPLAIN 的输出来查看执行计划，特别是正在使用哪些索引，确保查询正在使用合适的索引。

<小时/>

一些注意事项:

如果 spc 中的多行与 tsc 中的一行“匹配”，则查询将返回 tsc.id 的重复值。 (目前尚不清楚为什么或是否需要返回重复值。如果我们需要计算每个 tsc,id 的副本数，我们可以在查询中执行此操作，返回 的不同值>tsc.id 以及计数。如果我们不需要重复项，我们可以只返回一个不同的列表。

如果任何参数为 null，

GREATEST 函数将返回 NULL。如果我们需要的条件是“a > GREATEST(b,c)”，我们可以指定“a > b AND a > c”。

此外，这个条件:

tsc.PlanId = if(spc.plan_id = -1, tsc.PlanId, spc.plan_id) 

可以重写以返回等效结果(我对实际规范以及这个原始条件是否实际上充分满足表示怀疑。如果没有示例数据和预期输出样本，我们必须依赖 SQL 作为规范，因此我们在重写中尊重这一点。)

<小时/>

如果我们不需要返回 tsc.id 的重复值，假设 id 在 TEST.Services 中是唯一的，我们可以还写

SELECT tsc.id
  FROM TEST.Services tsc
 WHERE EXISTS 
       ( SELECT 1
           FROM DICT.Change spc
             ON spc.service_id = tsc.serviceid
            AND spc.starttime  < tsc.startdate
            AND spc.starttdate < tsc.startdate
            AND ( ( spc.plan_id = tsc.planid )
               OR ( spc.plan_id = -1 AND tsc.planid IS NOT NULL )
                )
       )