[MySql]语句优化问题，求优化

[ChumpKlutz]

代码

select i.fk_in_no,i.fk_vender_abbrev,i.fk_factory_abbrev,i.fk_part_no,i.fk_spec,i.fk_commodity,i.qty,
       i.qty- 
       ifnull((select sum(o.qty) 
       from ve_report_outbound_detail o 
       where i.fk_in_no=o.fk_in_no 
       and i.fk_part_no=o.fk_vender_part_no 
       and i.fk_spec=o.fk_vender_spec
       and i.fk_commodity=o.fk_vender_commodity
       and i.fk_qty_unit_abbrev=o.fk_qty_unit_abbrev),0) as stock_qty,i.fk_qty_unit_abbrev,i.inbound_time
from ve_report_inbound_detail i
having stock_qty>0 

视图：ve_report_inbound_detail 和 ve_report_outbound_detail 都只有不到一万的数据
怎么这条语句要执行40多秒

求优化 谢谢大大

[ACMAIN_CHM]

贴出你的
show index from ve_report_inbound_detail;
show index from ve_report_outbound_detail;

和 explain select i.fk_in_no,i.fk_vender_abbrev,i.fk_factory_abbrev,i.fk_part_no,i.fk_spec,i.fk_commodity,i.qty,
i.qty-
ifnull((select sum(o.qty)
from ve_report_outbound_detail o
where i.fk_in_no=o.fk_in_no
and i.fk_part_no=o.fk_vender_part_no
and i.fk_spec=o.fk_vender_spec
and i.fk_commodity=o.fk_vender_commodity
and i.fk_qty_unit_abbrev=o.fk_qty_unit_abbrev),0) as stock_qty,i.fk_qty_unit_abbrev,i.inbound_time
from ve_report_inbound_detail i
having stock_qty>0

[ChumpKlutz]

这是explain ,那两个show index为空
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL 7868
3 DERIVED m ALL Index_InMain_InNo 3253 Using where; Using temporary; Using filesort
3 DERIVED d ref Index_InDetail_InNo Index_InDetail_InNo 92 wms.m.in_no 1 Using where
2 DEPENDENT SUBQUERY <derived5> ALL 11180 Using where
5 DERIVED m ALL Index_InMain_OutNo 3154 Using where; Using temporary; Using filesort
5 DERIVED d ref Index_OutDetail_OutNo Index_OutDetail_OutNo 92 wms.m.out_no 1 Using where


另外有没有其它方法改写这个语句不用子查询啊

[ACMAIN_CHM]

首先这个个全表扫描。并且要等全部结果有也才能判断having stock_qty>0

改成如下试试

select i.fk_in_no,i.fk_vender_abbrev,i.fk_factory_abbrev,i.fk_part_no,i.fk_spec,i.fk_commodity,i.qty,
	i.qty-o.qty as stock_qty,
	i.fk_qty_unit_abbrev,i.inbound_time
from ve_report_inbound_detail i inner join (
	select fk_in_no,fk_vender_part_no,fk_vender_spec,fk_vender_commodity,fk_qty_unit_abbrev,sum(qty) as qty
	from ve_report_outbound_detail
	group by fk_in_no,fk_vender_part_no,fk_vender_spec,fk_vender_commodity,fk_qty_unit_abbrev
) o on i.fk_in_no=o.fk_in_no 
       and i.fk_part_no=o.fk_vender_part_no 
       and i.fk_spec=o.fk_vender_spec
       and i.fk_commodity=o.fk_vender_commodity
       and i.fk_qty_unit_abbrev=o.fk_qty_unit_abbrev
where i.qty>o.qty

然后就是要建基于fk_in_no,fk_vender_part_no,fk_vender_spec,fk_vender_commodity,fk_qty_unit_abbrev 索引。

[ACMAIN_CHM]

[Quote]贴出你的
show index from ve_report_inbound_detail;
show index from ve_report_outbound_detail;
[/Quote]

[ChumpKlutz]

我已经在源表上建了索引为什么
show index from ve_report_inbound_detail;
show index from ve_report_outbound_detail;
这两句话还是空的呢

是不是因为是视图所有是空的呢

[ACMAIN_CHM]

视图上当然没有索引，

写出你的view 的语句 , 否则没办法分析。
然后贴出ＶＩＥＷ中表的相关索引。

[ChumpKlutz]

按ACMAIN_CHM的提示，改了写法，现在执行速度在15秒左右

SELECT i.fk_in_no,
       i.fk_vender_abbrev,
       i.fk_factory_abbrev,
       i.fk_part_no,
       i.fk_spec,
       i.fk_commodity,
       i.qty,
       i.qty - ifnull(o.out_qty, 0) AS stock_qty,
       i.fk_qty_unit_abbrev,
       i.inbound_time
  FROM    ve_report_inbound_detail i
       LEFT JOIN
          (  SELECT fk_in_no,
                    fk_vender_part_no,
                    fk_vender_spec,
                    fk_vender_commodity,
                    fk_qty_unit_abbrev,
                    sum(qty) AS out_qty
               FROM ve_report_outbound_detail
           GROUP BY fk_in_no,
                    fk_vender_part_no,
                    fk_vender_spec,
                    fk_vender_commodity,
                    fk_qty_unit_abbrev) o
       ON (    i.fk_in_no = o.fk_in_no
           AND i.fk_part_no = o.fk_vender_part_no
           AND i.fk_spec = o.fk_vender_spec
           AND i.fk_commodity = o.fk_vender_commodity
           AND i.fk_qty_unit_abbrev = o.fk_qty_unit_abbrev)
HAVING stock_qty > 0

[ACMAIN_CHM]

不要用having ,
where i.qty>o.qty or o.qty is null

[ChumpKlutz]

[Quote=引用 8 楼 acmain_chm 的回复:]
不要用having ,
where i.qty>o.qty or o.qty is null

[/Quote]

用了这句反而执行速度为20秒了

[ACMAIN_CHM]

可以这样试一下。

SELECT i.fk_in_no,
       i.fk_vender_abbrev,
       i.fk_factory_abbrev,
       i.fk_part_no,
       i.fk_spec,
       i.fk_commodity,
       i.qty,
       i.qty - o.out_qty AS stock_qty,
       i.fk_qty_unit_abbrev,
       i.inbound_time
  FROM    ve_report_inbound_detail i
       LEFT JOIN
          (  SELECT fk_in_no,
                    fk_vender_part_no,
                    fk_vender_spec,
                    fk_vender_commodity,
                    fk_qty_unit_abbrev,
                    sum(qty) AS out_qty
               FROM ve_report_outbound_detail
           GROUP BY fk_in_no,
                    fk_vender_part_no,
                    fk_vender_spec,
                    fk_vender_commodity,
                    fk_qty_unit_abbrev) o
       ON (    i.fk_in_no = o.fk_in_no
           AND i.fk_part_no = o.fk_vender_part_no
           AND i.fk_spec = o.fk_vender_spec
           AND i.fk_commodity = o.fk_vender_commodity
           AND i.fk_qty_unit_abbrev = o.fk_qty_unit_abbrev)
where i.qty>o.out_qty or o.out_qty is null

另外如果再优化，就需要看你的VIEW的代码是什么以及这些表上的索引。

[WWWWA]

LEFT JOIN->INNER JOIN试试

[WWWWA]

1、可以考虑将
SELECT fk_in_no,
fk_vender_part_no,
fk_vender_spec,
fk_vender_commodity,
fk_qty_unit_abbrev,
sum(qty) AS out_qty
FROM ve_report_outbound_detail
GROUP BY fk_in_no,
fk_vender_part_no,
fk_vender_spec,
fk_vender_commodity,
fk_qty_unit_abbrev

存为 VIEW 假设VIEW1

SELECT i.fk_in_no,
i.fk_vender_abbrev,
i.fk_factory_abbrev,
i.fk_part_no,
i.fk_spec,
i.fk_commodity,
i.qty,
i.qty - o.out_qty AS stock_qty,
i.fk_qty_unit_abbrev,
i.inbound_time
FROM ve_report_inbound_detail i
INNER JOIN VIEW1

ON ( i.fk_in_no = o.fk_in_no
AND i.fk_part_no = o.fk_vender_part_no
AND i.fk_spec = o.fk_vender_spec
AND i.fk_commodity = o.fk_vender_commodity
AND i.fk_qty_unit_abbrev = o.fk_qty_unit_abbrev AND i.qty>o.out_qty )