mysql执行查询SQL一直执行不出结果

Z时代
2024-01-10
分类：综合

database

今天执行一个mysql 语句，一直在执行，执行了5分钟了，还是没有出来结果。


# 每个组织下包括 同级或者下级的 注册店铺数
# 查询  历史每一天的每个组织下的当天存在的门店数
  select
   dt.`time`  startDate,
   o.brand_id as brandId,
    o.id AS orgId,
   count(ogstore.id) as totalStore
  from  om_organization o
  inner join view_om_org_tree ot on ot.parent_id=o.id
  left join  da_dim_time dt on dt.dim_type="DAY" and dt.`time`>="2018-10-30" and dt.`time` < CURDATE()
INNER JOIN (
  select orge.id,orge.create_time as cdate  from om_organization orge where
   orge.org_type="Store"  and orge.removed=0
  ) as ogstore on ogstore.id=ot.id and ogstore.cdate<=dt.`time`
  where 1=1
  and o.removed=0  group by o.id, dt.`time`

以上SQL ，看起来逻辑也挺清晰的，而且也不算长SQL了。但是执行起来很久没有结果。

无奈只能中断查询了，试过加了索引发现还是没有用。查询执行性能如下

1 SIMPLE dt range idx_time,idx_time2,idx_type idx_time 39 391 100 Using where; Using index; Using temporary; Using filesort 1 SIMPLE orge ALL PRIMARY,idx_orgtype 11053 9.09 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ot ref idx_key,idx_pid idx_key 8 newpearl_db_dev.orge.id 6 100 Using index

1 SIMPLE o eq_ref PRIMARY PRIMARY 8 newpearl_db_dev.ot.parent_id 1 50 Using where

也不算很多数据啊，行数最多的要 10000多行了。

解决办法

改写了半天，我觉得是以上SQL写法不够清晰，易懂。一定要写出来让mysql 很容易明白，解析的SQL才行


SELECT
 dt.`time` startDate,
o.brand_id as brandId,
    o.id AS orgId,
SUM(ff.totalStore)  as totalStore
from 
da_dim_time dt ,
(
select
   DATE(orge.create_time) ctime,
    o.id AS orgId,
   count(ot.id) as totalStore
  from  om_organization o
  inner join view_om_org_tree ot on ot.parent_id=o.id
  INNER JOIN om_organization orge
 on  orge.id=ot.id
  and orge.org_type="Store"  and orge.removed=0
  where 1=1
  and o.removed=0
  group by o.id, ctime
) ff
,
om_organization o 
 where o.removed=0 and ff.orgId=o.id
and dt.dim_type="DAY" and dt.`time`>="2018-10-30" and dt.`time` < CURDATE()
and ff.ctime <=dt.`time`
and o.id=1
GROUP BY dt.`time`, o.id 同时耗时才 0.15s 难以相信

最终写出来了，根据创建时间进行分组，之后再进行关联查询，就出来了结果。

执行性能

那为什么之前的SQL一直在执行呢？

可以我有另一个与其相似的SQL是可以出来结果的，只是也执行了 20s ，非常耗时。

数据量比较小，估计mysql 穷尽了所有数据吧，不然怎么这么慢？


以下SQL执行了可以出来结果，虽然耗时20S
# 每个组织下包括 同级或者下级的 注册经销商数
select
   dt.`time`  startDate,
   o.brand_id as brandId,
    o.id AS orgId,
   count(orgDealer.id) as totalDealer
  from  om_organization o
  inner join view_om_org_tree ot on ot.parent_id=o.id
   left join  da_dim_time dt on dt.dim_type="DAY" and dt.`time`>="2018-10-30" and dt.`time` < CURDATE()
INNER JOIN 
(
select orge.id,DATE(orge.create_time) as cdate from om_organization orge where 1=1 
  and orge.org_type="Dealer"  and orge.removed=0
) as orgDealer on orgDealer.id= ot.id and orgDealer.cdate<=dt.`time`
  where 1=1
  and o.removed=0   group by o.id , dt.`time`