SQL在公司常用场景案例分享（二）

关注

原创 2023-12-23 09:35:37 537 阅读 AI 写同款· GEO 优化 ›

高并发场景案例分享（一）分库分表今年在公司重构（写）了一个老项目，踩了无数的坑。中间好几次遇到问题，甚至感觉项目可能要失败了，好在最后终于成功上线了。虽然被坑的不要不要的，但也从中领悟到了不少东西，在这里记录一下，顺便分享给大家乐呵乐呵。先简单介绍下项目，一个面向C端用户的服务，主要提供包括动态、评论、圈子、好友、关注、Feed等常见的社区功能，另外还有其他一些个性化的功能。日活比较高，整个服务QPS上万。高频业务，单个接口QPS上千。单项业务数据量过亿，比如评论。图1.qps监控图在上述高并发、海量数据的阅读详情

--先查询
SELECT ip.ISSUE_TYPE,mo.DOC_NO,i.ITEM_CODE,mod.*
FROM [powerjet].[dbo].[MO] mo
left join MO_D mod on mo.MO_ID=mod.MO_ID
left join ITEM i on i.ITEM_BUSINESS_ID=mod.ITEM_ID
left join ITEM_PLANT ip on ip.ITEM_ID=i.ITEM_BUSINESS_ID
where ip.ISSUE_TYPE='2' and mod.ITEM_TYPE='4'  and ISSUED_QTY=0 and mo.DOC_DATE between  '2022-01-01' and '2022-01-31'  and mo.STATUS='Y'
--后更新
update MO_D  set ITEM_TYPE=1  FROM [powerjet].[dbo].[MO] mo
 left join MO_D mod on mo.MO_ID=mod.MO_ID
 left join ITEM i on i.ITEM_BUSINESS_ID=mod.ITEM_ID
 left join ITEM_PLANT ip on ip.ITEM_ID=i.ITEM_BUSINESS_ID
 ----------------------------------------------------------------------------------------------
 --先查询
 select ip.INBOUND_WAREHOUSE_ID,ip.OUTBOUND_WAREHOUSE_ID,i.ITEM_CODE,i.SHORTCUT,s.fname1,s.fname2,w.WAREHOUSE_CODE,w.WAREHOUSE_ID from ITEM_PLANT ip
left join ITEM i on ip.ITEM_ID=i.ITEM_BUSINESS_ID
inner join Sheet3 s on s.fnumber=i.SHORTCUT
inner join WAREHOUSE w on  w.WAREHOUSE_NAME=s.fname2
where  ip.ApproveStatus='Y' and ip.INBOUND_WAREHOUSE_ID='BB2B5B80-F08B-4F3A-EEEB-16550989B184'
order by i.ITEM_CODE
--后更新

update ITEM_PLANT set  INBOUND_WAREHOUSE_ID = w.WAREHOUSE_ID,OUTBOUND_WAREHOUSE_ID=w.WAREHOUSE_ID from ITEM_PLANT ip
left join ITEM i on ip.ITEM_ID=i.ITEM_BUSINESS_ID
inner join Sheet3 s on s.fnumber=i.SHORTCUT
inner join WAREHOUSE w on  w.WAREHOUSE_NAME=s.fname2
where  ip.ApproveStatus='Y' and ip.INBOUND_WAREHOUSE_ID='BB2B5B80-F08B-4F3A-EEEB-16550989B184'
order by i.ITEM_CODE
 ----------------------------------------------------------------------------------------------
 --连接外表更新
 update BIN set BIN_NAME= bin_temp.BIN_NAME from BIN 
 inner join bin_temp on bin_temp.BIN_ID=BIN.BIN_ID
 
 --------------------------------------------------------------------------------------------
 --连接外表插入
 insert into BORROW_DOC_D(
    SequenceNumber,
    BORROW_DOC_D_ID,
    ITEM_DESCRIPTION,
    ITEM_SPECIFICATION,
    BUSINESS_QTY,
    INVENTORY_QTY,
    PLAN_RETURN_DATE
    ,ITEM_ID,
    BUSINESS_UNIT_ID,
    FROM_WAREHOUSE_ID,
    TO_WAREHOUSE_ID,
    ApproveStatus,
    CreateDate,
    CreateBy,
    BORROW_DOC_ID
)
select ROW_NUMBER() OVER(ORDER BY i.ITEM_BUSINESS_ID)  as  'NUM',
        NEWID() as BORROW_DOC_D_ID,
        i.ITEM_NAME as ITEM_DESCRIPTION,
        i.ITEM_SPECIFICATION ,
        jr.[数量] num1,
        jr.[数量] num2,
        '2021-12-29 00:00:00.0000000' as PLAN_RETURN_DATE, 
        i.ITEM_BUSINESS_ID as ITEM_ID,u.UNIT_ID,
        'DE361061-C064-49AA-17EC-18A90307426F' as FROM_WAREHOUSE_ID,
        '5361CDA3-4C92-40FD-E51C-18A34F3B36B5' as TO_WAREHOUSE_ID,
        'N' as ApproveStatus,
        '2021-12-29 19:10:25.0005532'as CreateDate,
        '906A7582-4D15-42AB-B860-183C773F1625' as CreateBy,
        'B9B060CE-1505-4059-BF48-18A906812268' as BORROW_DOC_ID 
 from jieru jr
left join ITEM i on i.ITEM_CODE=jr.[品号]
left join UNIT u on u.UNIT_CODE=jr.[单位]

---------------------------------------------------------------------------------------
  一行根据某个字段拆分多行
 select id, rwsjjx ,htbh,mbgs  
 from Prj_TaskProcess pt cross apply Split([hrmid],',') tbl_Ids 
 where  id is not null