原先的sql语句：
select ac.*,bp.portname,bs.shipname,cb.ItinDur,cb.SDFare,cb.BDFare,cb.ODFare,cb.IDFare from( select shipcode,linecode,ItinDur,min(saildate)as saildate,min(cruisecode)as cruisecode,min(SDFare + GovtFees)as SDFare, min(BDFare + GovtFees)as BDFare,min(ODFare
+ GovtFees)as ODFare,min(IDFare + GovtFees)as IDFare from cru_bestfare group by shipcode,linecode,ItinDur ) cb inner join cru_cruises as ac on cb.shipcode = ac.shipcode and cb.linecode = ac.linecode and cb.cruisecode = ac.CruisesCode and cb.saildate = ac.DepartDate
inner join base_port as bp on ac.fromport = bp.portcode inner join base_ship bs on ac.shipCode = bs.shipCode and ac.Source = bs.Source and ac.linecode = bs.linecode where ac.active = 1 and ac.modify = 1 and (cb.SDFare > 0 or cb.BDFare > 0 or cb.ODFare
> 0 or cb.IDFare > 0)
这个sql语句的执行时间为 <0 秒
增加一个查询条件：
select ac.*,bp.portname,bs.shipname,cb.ItinDur,cb.SDFare,cb.BDFare,cb.ODFare,cb.IDFare from( select shipcode,linecode,ItinDur,min(saildate)as saildate,min(cruisecode)as cruisecode,min(SDFare + GovtFees)as SDFare, min(BDFare + GovtFees)as BDFare,min(ODFare
+ GovtFees)as ODFare,min(IDFare + GovtFees)as IDFare from cru_bestfare group by shipcode,linecode,ItinDur ) cb inner join cru_cruises as ac on cb.shipcode = ac.shipcode and cb.linecode = ac.linecode and cb.cruisecode = ac.CruisesCode and cb.saildate = ac.DepartDate
inner join base_port as bp on ac.fromport = bp.portcode inner join base_ship bs on ac.shipCode = bs.shipCode and ac.Source = bs.Source and ac.linecode = bs.linecode where ac.active = 1 and ac.modify = 1 and ac.DurationDays < 8 and (cb.SDFare > 0 or cb.BDFare
> 0 or cb.ODFare > 0 or cb.IDFare > 0)
增加的条件为： and ac.DurationDays < 8 其中 DurationDays 是int类型
然后执行时间变为13秒
上面每个表的数据都不到10000行 为什么会影响这么大呢？ 怎么改进？？？
为何我的查询条件增加一个后性能消耗这么多
.jpg)
