V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
ebony0319
V2EX  ›  MySQL

sql 一段代码优化

  •  
  •   ebony0319 · 2016-10-05 10:07:24 +08:00 · 3089 次点击
    这是一个创建于 2732 天前的主题,其中的信息可能已经有所发展或是发生改变。


    很长。非常长。
    太多的左联结,但是每一个又不能少。
    前端查询有点慢,点一下要转很多个圈。
    这段代码其实主要突出的是入库情况,但是有些信息必须包含进去。于是想要什么就从对应的表去取值。于是太多的 left outer join
    还有一点就是类似于这种
    left outer join (select orderno,ordersn,slid, shnum num,count(*) rkcount from jy_rksq a group by orderno,ordersn,slid,shnum) rkcount on......
    我不是很确定对先对全表进行了扫描然后在联结。
    8 条回复    2016-10-06 12:35:45 +08:00
    wujunze
        1
    wujunze  
       2016-10-05 10:09:17 +08:00   ❤️ 1
    楼主可以贴出表结构 方便优化 SQL
    ebony0319
        2
    ebony0319  
    OP
       2016-10-05 10:09:22 +08:00
    我这里再粘贴一下。有点混乱
    select a.yt 用途说明,hw.HW 仓位,b.productno 物料编号,b.productname 物料名称 ,a.num 转入数,a.shnum 入库,a.dj 送货单价,a.dj*a.shnum 金额,
    case when isnull(a.num,0)-isnull(a.shnum,0)> 0 then isnull(a.num,0)-isnull(a.shnum,0) else null end 存,
    a.outnum 出 ,a.shdate 收货时间,a.shdh 送货单号,a.indate 转入时间 , jldw.dwname 单位,a.weight 重量,fz.productno 辅助编号,fz.productname 辅助名称,
    a.cw 财务 ,a.orderno 订单编号,a.ordersn 笔数,o.peitaosn 配套,a.supliername 供应商, a.pjhpz 票据凭证,cg.dj 采购价, b.productcgj 受控价,a.cgdh 采购单号,
    a.shperson 收货人,a.shno 入库单号,a.ckname 仓库,cgcount.cgcount 采购次数,rkcount.rkcount 入库次数,dbcount.dbcount 出仓次数,db2count.db2count 出仓 2 次数,
    a.person 转入人,a.outno 转出编号,a.outperson 转出人,a.outdate 转出时间,a.slid,a.id,a.source,a.sourcedw 来源单位,a.sourceid,a.cgtype,a.xq2id,a.oldslid,
    u.empname as 跟单人,ckname.ckname as 下单档口,b.newproductno ,
    case when isnull(b.photo,'')='' then fz.photo else b.photo end as photo ,a.yck,
    case when isnull(a.cgtype,0)=0 then '进仓 1'
    when isnull(a.cgtype,0)=2 then '进仓 2' when isnull(a.cgtype,0)=3 then '换进仓' when isnull(a.cgtype,0)=4 then '退料进仓' end as 进仓类别
    from rksq a left outer join product b on a.slid=b.id
    left outer join order o on a.orderno=o.orderno and a.ordersn=o.ordersn
    left outer join (select orderno,ordersn,slid,num,count(*) cgcount from cgwlsqdetail a group by orderno,ordersn,slid,num) cgcount on a.orderno=cgcount.orderno and a.ordersn=cgcount.ordersn and a.slid=cgcount.slid and a.num=cgcount.num
    left outer join (select orderno,ordersn,slid, shnum num,count(*) rkcount from rksq a group by orderno,ordersn,slid,shnum) rkcount on a.orderno=rkcount.orderno and a.ordersn=rkcount.ordersn and a.slid=rkcount.slid and a.num=rkcount.num
    left outer join (select distinct cgdh,dj,isnull(orderno,'') orderno,ordersn,slid from cgwlsqdetail) cg on a.cgdh=cg.cgdh and a.orderno=cg.orderno and a.ordersn=cg.ordersn and a.slid=cg.slid
    left outer join (select orderno,ordersn,slid,dbnum num,count(*) dbcount from dbwlsqdetail a group by orderno,ordersn,slid,dbnum)
    dbcount on a.orderno=dbcount.orderno and a.ordersn=dbcount.ordersn and a.slid=dbcount.slid and a.num=dbcount.num
    left outer join (select orderno,ordersn,slid,dbnum num,count(*) db2count from dbwlsqdetail2 a group by orderno,ordersn,slid,dbnum) db2count on a.orderno=db2count.orderno and a.ordersn=db2count.ordersn and a.slid=db2count.slid and a.num=db2count.num
    left outer join (select orderno,ordersn,slid, num,count(*) sfcount from sfsq a group by orderno,ordersn,slid,num) sfcount on a.orderno=sfcount.orderno and a.ordersn=sfcount.ordersn and a.slid=sfcount.slid and a.num=sfcount.num
    left outer join user u on o.gdr=u.id left outer join ckname ckname on o.ckorderid=ckname.id
    left outer join product cp on o.productid=cp.id
    left outer join jldw jldw on b.jldwid=jldw.id
    left outer join ( select a.ID,a.ProductID,a.ckid,a.HW,a.Memo ,b.ckName from HW a, ckName b where a.CKID =b.ID and b.ckname='商务' ) hw on a.SLID=hw.ProductID
    left outer join product fz on b.fzcode = fz.productno where 1=1 and a.ckname='商务' and ( isnull(a.cgtype,0)=0 or ( isnull(a.cgtype,0)=2 ) ) and isnull(a.outnum,0)=0 order by a.id desc
    ebony0319
        3
    ebony0319  
    OP
       2016-10-05 10:10:46 +08:00
    @wujunze 有道理。我这就写出来。
    ebony0319
        4
    ebony0319  
    OP
       2016-10-05 10:25:47 +08:00
    @wujunze 数据库有两台连接的主线,一条是订单号和订单的笔数,一条是产品的 ID,通过这两个可以把所有的表都联系起来。表有点多,架构师走了。我现在只知道一些基础的表,一般都是跟踪查询的。 HW 是库位表, order 是订单表。后面一大堆还有采购表,物料需求,供应商表,然后还有什么入库什么表,出仓申请表,库存表....
    WhoMercy
        5
    WhoMercy  
       2016-10-05 14:13:09 +08:00
    我的天...这种不经过优化的 SQL ,跟上家一模一样,都是设计的锅,开发者一味追求“方便”、“快捷”,根本不考虑扩展性和效率。
    想要在 SQL 层优化是很难的,而且徒劳。因为如你所说,最后会发现,每个表的字段其实都是必要的。
    我给两个思路:
    1.页面分次加载数据,在需要的时候再查询某几个数据,如 A 表名和规格先显示,等用到 B 表数据时再去查询 B 表,不要一次性全部查询出来,如果前台不用显示保存后的数据,还可以异步更新数据;
    2.添加冗余表、中间表或者缓存数据表,这需要有个缓存的生成机制, Memcache 、 Redis 、 MongoDB 、 Lucence 等很多工具都能实现该效果;
    jhdxr
        6
    jhdxr  
       2016-10-05 17:32:52 +08:00
    我也来补充个方案,换 Oracle
    abnerchou
        7
    abnerchou  
       2016-10-05 21:32:02 +08:00
    这么多表……建议先 populate 到小的 temp table 中(用 Inner Join 和 Insert ),最后输出的时候再根据需要用 Left Join 。这样可以缩小表的大小,加快检索速度。
    lianxiaoyi
        8
    lianxiaoyi  
       2016-10-06 12:35:45 +08:00 via Android
    神一般的 SQL
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2951 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 14:11 · PVG 22:11 · LAX 07:11 · JFK 10:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.