首页 新闻 赞助 找找看

很奇怪的一个SQL统计查询慢的问题

0
悬赏园豆:5 [待解决问题]

一个简单的统计查询,按照起至时间进行筛选。主表数据量不到10W。

问题是这样子的: 当时间范围越大时,查询越快,反之越慢,比如选择一个很大的范围,对所有数据进行统计。从点击到查询到页面展现,也就1秒不到的时间。然后逐渐缩小时间范围,到时间间隔一个月的时候,大概还是1秒左右的,但已经稍微比所有数据慢了。但继续缩小到一周左右的数据时,就慢到要10秒左右了。当缩小到一天左右的数据量时,查询就经常超时了。

SQL语句不复杂,用到的只有 inner join group sum

xxfss2的主页 xxfss2 | 初学一级 | 园豆:3
提问于:2014-11-27 15:45
< >
分享
所有回答(5)
0

.......把sql语句发上来会死啊?

吴瑞祥 | 园豆:29449 (高人七级) | 2014-11-27 15:46
select * from (select T_VillageVet_Base.I_VillageVet_ID as DepartId,T_VillageVet_Base.Name,sum(inrangeamount)as Inrangeamount from T_InKillRangeRegister inner join T_VillageVet_Base on T_InKillRangeRegister.departid =T_VillageVet_Base.I_VillageVet_ID where T_InKillRangeRegister.CreatedAt>='1910-1-1' and T_InKillRangeRegister.CreatedAt<'2200-1-1' group by departid,T_VillageVet_Base.Name,T_VillageVet_Base.I_VillageVet_ID ) as Inrange left join (select derpartId,sum(KillAmount) as KillAmountA,count(KillAmount) as KillCountA  from T_KillQuarItem inner join T_KillQuar on T_KillQuarItem.KillQuarId=T_KillQuar.Id where ProductId=5 and T_KillQuar.CreatedAt >='1910-1-1' and T_KillQuar.CreatedAt<'2200-1-1' group by derpartId ) as killA on Inrange.DepartId =killA.derpartId left join (select derpartId,sum(KillAmount) as KillAmountB,count(KillAmount) as KillCountB  from T_KillQuarItem inner join T_KillQuar on T_KillQuarItem.KillQuarId=T_KillQuar.Id where ProductId=6 and T_KillQuar.CreatedAt >='1910-1-1' and T_KillQuar.CreatedAt<'2200-1-1' group by derpartId ) as killB on Inrange.DepartId =killB.derpartId left join (select DepartId,sum(T_Harmless.amount) as harmlessAmount from T_Harmless inner join T_KillRangeAnimal on T_Harmless.KillRangeAnimalId =T_KillRangeAnimal.InKillRangeRegisterId where state=1 and T_Harmless.TreatDate >='1910-1-1' and T_Harmless.TreatDate<'2200-1-1' group by DepartId) as harmless on Inrange.DepartId =harmless.DepartId left join (select DepartId,sum(T_NopaperOutrange.Amount) as nopaperAmount from T_NopaperOutrange  inner join T_KillRangeAnimal on T_NopaperOutrange.KillRangeAnimalId =T_KillRangeAnimal.InKillRangeRegisterId where T_NopaperOutrange.CreatedAt >='1910-1-1' and T_NopaperOutrange.CreatedAt<'2200-1-1' group by DepartId) as nopaper on Inrange.DepartId =nopaper.DepartId
支持(0) 反对(0) xxfss2 | 园豆:3 (初学一级) | 2014-11-27 15:52
0

给时间建个索引试试。

大楚打码人 | 园豆:4313 (老鸟四级) | 2014-11-27 15:47
0

正常问题是你的SQL问题,你自己想差了

风醉 | 园豆:1197 (小虾三级) | 2014-11-27 15:48
0

仔细检查你的 inner join 慎用!!!

WP921108 | 园豆:108 (初学一级) | 2014-11-27 17:54
0

把某些查询建立为视图,然后视图之间再用链接   不懂到群里问  130017551

iisp | 园豆:81 (初学一级) | 2014-11-28 16:12
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册