xujiannan1988
xujiannan1988
2019-02-26 13:54
采纳率: 50%
浏览 3.0k

mysql left join效率优化

通过下面sql查询商品库存数量,因为库存表里面的库存是很多仓库的库存数据,在left join的时候又要做一个表连接,这样查询的时候效率非常低,如果left join一个表就很快,但是无法满足需求,请大神门帮忙优化下sql,谢谢

select g.*,gsd.number from goods g
left join (select gs.goods_id,sum(gs.number) number from goods_stock gs,warehouses w where gs.warehouse_id=w.id group by gs.goods_id and w.id='123456789') gsw on(g.id=gsd.goods_id)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • chinarealone
    chinarealone 2019-02-26 17:53
    已采纳

    连接时不引入子查询(中间结果利用不了索引),在连接后再进行分组统计,大概的样子如下
    SELECT *
    FROM
    (SELECT *
    FROM goods g
    LEFT JOIN goods_stock gs
    ON g.goods_id = gs.goods_id
    LEFT JOIN warehouses w
    ON gs.warehouse_id = w.id
    WHERE w.id='123456789' ) good
    GROUP BY good.goods_id

    点赞 评论
  • xsb_20171227
    今天是星期五 2019-02-26 14:47

    在gs.warehouse_id、w.id、gs.goods_id、gs.number等关键字段加索引没?

    点赞 评论
  • qq_37129624
    ghq-yes 2019-02-26 16:10

    select g.*,sum(gs.number) number from goods g,goods_stock gs,warehouses w
    where w.id='123456789' and gs.warehouse_id='123456789' and g.id=gsd.doods_id
    group by g.id,g.***
    (g.***是因为不知道你goods表会不会有一样的id记录,如果有的话需要在goods找一个可以区分的字段)

    点赞 评论
  • yangxingzou
    zoyation 2019-02-27 09:27

    select g.id goods_id,sum(b.number) number
    from goods g
    left join (
    select gs.goods_id,gs.number from goods_stock gs on g.id = gs.goods_id
    join warehouses w on gs.warehouse_id = w.id

    where w.id = '123456789'
    ) b on g.id=b.goods_id
    group by g.id

    可以这样,你商品表没有条件,如果商品也很多查询也快不了
    
    点赞 评论

相关推荐