MySQL - 添加多个派生表时查询缓慢 - 优化

Question

对于我的查询，底部的两个派生表导致该查询速度疯狂缓慢。该查询按原样执行，大约需要 45-55 秒。现在，当我仅删除其中一个派生表(无论是哪一个)时，查询时间会减少到 0.1 - 0.3 秒。我的问题；拥有多个派生表是否存在问题？有更好的方法来执行这个吗？我的索引似乎都是正确的，我还将包含此查询的解释。

select t.name as team, u.name as "REP NAME", 
  count(distinct activity.id) as "TOTAL VISITS", 
  count(distinct activity.account_id) as "UNIQUE VISITS",
  count(distinct placement.id) as "COMMITMENTS ADDED",

  CASE WHEN 
    count(distinct activity.account_id) = 0 THEN (count(distinct 
    placement.id) / 1) 
    else (cast(count(distinct placement.id) as decimal(10,2)) / 
    cast(count(distinct activity.account_id) as decimal(10,2)))
  end as "UNIQUE VISIT TO COMMITMENT %",

  case when o.mode='basic' then count(distinct placement.id) else 
    count(distinct(case when placement.commitmentstatus='fullfilled' 
    then placement.id else 0 end)) 
  end as "COMMITMENTS FULFILLED",

  case when o.mode='basic' then 1 else 
    (CASE WHEN 
     count(distinct placement.id) = 0 THEN (count(distinct(case when 
     placement.commitmentstatus='fullfilled' then placement.id else 0 
    end)) / 1) 
    else (cast(count(distinct(case when 
    placement.commitmentstatus='fullfilled' then placement.id else 0 
    end)) as decimal(10,2)) / cast(count(distinct placement.id) as 
    decimal(10,2)))
    end) end as "COMMITMENT TO FULFILLMENT %"

from lpmysqldb.users u
left join lpmysqldb.teams t on t.team_id=u.team_id
left join lpmysqldb.organizations o on o.id=t.org_id
left join (select * from lpmysqldb.activity where 
  org_id='555b918ae4b07b6ac5050852' and completed_at>='2018-05-01' and 
  completed_at<='2018-06-01' and tag='visit' and accountname is not 
  null and (status='active' or status='true' or status='1')) as 
  activity on activity.user_id=u.id
left join (select * from lpmysqldb.placements where 
  orgid='555b918ae4b07b6ac5050852' and placementdate>='2018-05-01' and 
  placementdate<='2018-06-01' and (status IN ('1','active','true') or 
  status is null)) as placement on placement.userid=u.id

where u.org_id='555b918ae4b07b6ac5050852' 
  and (u.status='active' or u.status='true' or u.status='1')
  and istestuser!='1'
group by u.org_id, t.name, u.id, u.name, o.mode
order by count(distinct activity.id) desc

感谢您的帮助!

我在下面进行了编辑，将两个底部联接从子查询联接更改为直接联接表。仍然产生相同的结果。

Answer 1

这是对您的查询进行了稍微重组的查询。可能会被简化，因为最后两个子查询都针对您各自的计数和非重复计数进行了预先聚合，因此您可以直接使用这些列名称，而不是显示整个查询中嵌入的所有计数(非重复)。

我还尝试通过将给定计数乘以 1.00 来强制结果采用基于小数的精度来简化除法。

select 
      t.name as team, 
      u.name as "REP NAME", 
      Activity.DistIdCnt as "TOTAL VISITS", 
      Activity.UniqAccountCnt as "UNIQUE VISITS",
      Placement.DistIdCnt as "COMMITMENTS ADDED",

      Placement.DistIdCnt / 
         CASE WHEN Activity.UniqAccountCnt = 0 
           THEN 1.00 
           ELSE Activity.UniqAccountCnt * 1.00 
           end as "UNIQUE VISIT TO COMMITMENT %",

      case when o.mode = 'basic' 
           then Placement.DistIdCnt
           else Placement.DistFulfillCnt
           end as "COMMITMENTS FULFILLED",

      case when o.mode = 'basic' 
           then 1 
           else ( Placement.DistFulfillCnt /
                     CASE when Placement.DistIdCnt = 0
                          then 1.00
                          ELSE Placement.DistIdCnt * 1.00
                          END TRANSACTION )
           END as "COMMITMENT TO FULFILLMENT %"
   from 
      lpmysqldb.users u
         left join lpmysqldb.teams t 
            on u.team_id = t.team_id
            left join lpmysqldb.organizations o 
               on t.org_id = o.id
        left join
        ( select
                user_id,
                count(*) as AllRecs,
                count( distinct id ) DistIdCnt,
                count( distinct account_id) as UniqAccountCnt
             from
                lpmysqldb.activity
             where 
                    org_id = '555b918ae4b07b6ac5050852' 
                and completed_at>='2018-05-01' 
                and completed_at<='2018-06-01' 
                and tag='visit' 
                and accountname is not null 
                and status IN ( '1', 'active', 'true') 
             group by
                user_id ) activity 
            on u.id = activity.user_id

         left join 
         ( select
                 userid,
                 count(*) AllRecs,
                 count(distinct id) as DistIdCnt,
                 count(distinct( case when commitmentstatus = 'fullfilled' 
                                      then id 
                                      else 0 end )) DistFulfillCnt
              from 
                 lpmysqldb.placements
              where 
                     orgid = '555b918ae4b07b6ac5050852'
                 and placementdate >= '2018-05-01' 
                 and placementdate <= '2018-06-01' 
                 and ( status is null OR status IN ('1','active','true')
              group by
                 userid ) as placement 
            on u.id = placement.userid
   where 
         u.org_id = '555b918ae4b07b6ac5050852' 
     and u.status IN ( 'active', 'true', '1')
     and istestuser != '1'
   group by 
      u.org_id, 
      t.name, 
      u.id, 
      u.name, 
      o.mode
   order by 
      activity.DistIdCnt desc

最后，您的内部查询正在查询所有用户。如果您有大量不活跃的用户，您可以通过在每个内部查询中添加这些连接/条件来排除这些用户，例如...

( ...
              from 
                 lpmysqldb.placements 
                    JOIN lpmysqldb.users u2
                       on placements.userid = u2.id
                      and u2.status IN ( 'active', 'true', '1')
                      and u2.istestuser != '1'
              where … ) as placement