任务描述
1.问题分析：
http://erpapi.zhcxkj.com/erp/order/getorder 接口：计算器调用的订单接口：梳理，接口瓶颈，推动优化改造

 

此处做记录，后面并发高了，跟踪验证


select count(0) from RB_OrderInfo nolock -- 45657760
select count(0) from RB_OrderSource nolock -- 13536
select count(0) from RB_OrderList nolock -- 58917437

2.解决方案：
  1. 慢sql优化；都有使用索引
  2. 数据归档；已归档了2年前的数据

  

3.sql-1

有使用索引，并发高了慢


SELECT o.CustomerID,

       a.OrderSourceType,

       o.WareHouseID,

       o.Country,

       k.SKU,

       Max(K.ID) AS ID

FROM   RB_OrderInfo o (nolock)

       INNER JOIN RB_OrderSource a (nolock)

               ON o.CustomerID = a.CustomerID

                  AND o.OrdersourceID = a.ID

       INNER JOIN RB_OrderList k (nolock)

               ON o.CustomerID = k.CustomerID

                  AND o.OrderCode = k.OrderCode

WHERE  o.CustomerID = 1

       AND o.WareHouseID = 5312

       AND a.OrderSourceType = 70

       AND o.Country = 'BR'

       AND k.SKU IN ( '16537679', '16536941', '16534306', '16530187',

                      '16528995', '16528982', '16525458', '16525272',

                      '16525033', '16524847', '16524799', '16524719',

                      '16524240', '16523795', '16521640', '16518471',

                      '16518464', '16518223', '16517625', '16514105',

                      '16513528', '16512498', '16512456', '16512264',

                      '16512008', '16511862', '16511745', '16511534',

                      '16509012', '16508778', '16508584', '16505811',

                      '16505671', '16505385', '16505371', '16505088',

                      '16505101', '16504064', '16504009', '16503928',

                      '16503884', '16503780', '16503544', '16503296',

                      '16503292', '16503147', '16502906', '16502621',

                      '16500950', '16496594', '16496541', '16496470',

                      '16496114', '16495511', '16494047', '16492704',

                      '16491566', '16491558', '16490692', '16489708',

                      '16489644', '16489240', '16488362', '16488599',

                      '16488599', '16488411', '16487501', '16486823',

                      '16486664', '16486497', '16486334', '16486493',

                      '16486493', '16486260', '16485683', '16485775',

                      '16485708', '16485638', '16485481', '16485120',

                      '16483935', '16483715', '16483717', '16483127',

                      '16482841', '16482849', '16482851', '16482778',

                      '16482429', '16482012', '16481475', '16481224',

                      '16481155', '16479651', '16477473', '16477385',

                      '16477433', '16476904', '16476894' )

       AND o.OrderState > 0

       AND o.ProductNumber = 1

       AND o.IsPay = 1

       AND o.IsStockOrder = 0

       AND o.DownLoadUpdateTime > '2000-01-01'

       AND o.PayTime < Dateadd(d, -1, Getdate())

       AND Isnull(o.TotalPrice, 0) > 0

GROUP  BY o.CustomerID,

          a.OrderSourceType,

          o.WareHouseID,

          o.Country,

          k.SKU; 


3-2:sql 2

问题分析：

获取订单费用，查询需1分46秒，RB_Product_Fee  

5亿数据是否可以归档？



SELECT OrderCode,

       OperateType,

       OperateFee,

       ExchangeRMB

FROM   RB_Product_Fee (NOLOCK)

WHERE  CustomerID = 1

       AND OrderCode IN ( 202204010020118507041, 202204190315119297134, 202205212316120889102, 202205312256121306117 )

       AND IsTrue = 1

       AND SKU = '5614827'

ORDER  BY OperateType ASC;

历史记录  
  1. 2023-04-06 11:27:11, 由 陈少军 创建。
  2. 2023-04-14 15:34:19, 由 陈少军 编辑。
    修改了 指派给 ，旧值为 "xuruifeng"，新值为 "chenshaojun"。
    修改了 任务描述，区别为：
    001- 1.问题分析：http://erpapi.zhcxkj.com/erp/order/getorder 接口：计算器调用的订单接口：梳理，接口瓶颈，推动优化改造sql-1 ,50个sku查询需38秒select count(0) from RB_OrderInfo nolock -- 45657760select count(0) from RB_OrderSource nolock -- 13536select count(0) from RB_OrderList nolock -- 589174372.解决方案： 1. 慢sql优化； 2. 数据归档； 3. 缓存数据；3.sql-1SELECT o.CustomerID, a.OrderSourceType, o.WareHouseID, o.Country, k.SKU, Max(K.ID) AS IDFROM RB_OrderInfo o (nolock) INNER JOIN RB_OrderSource a (nolock) ON o.CustomerID = a.CustomerID AND o.OrdersourceID = a.ID INNER JOIN RB_OrderList k (nolock) ON o.CustomerID = k.CustomerID AND o.OrderCode = k.OrderCodeWHERE o.CustomerID = 1 AND o.WareHouseID = 5312 AND a.OrderSourceType = 1 AND o.Country = 'US' AND k.SKU IN( 5250113, 5257736, 3731035, 3731162, 3731199, 3731292, 9893079, 3192277, 3192313, 3194756, 3194769, 14376488, 14740416, 5614827, 8540889, 8540898, 12041722, 5905293, 11077686, 8335283, 8540889, 13248296, 13332730, 1743553, 5816653, 5816675, 9043295, 8371471, 10746757, 12053359, 6293080, 5306233, 9043295, 1743553, 12860847, 11371896, 3069611, 10837860, 10837860, 10837860, 10837860, 13709107, 10837860, 14342048, 14342519, 14342519, 14342548, 14342548, 10967446, 7593170 ) AND o.OrderState &gt; 0 AND o.ProductNumber = 1 AND o.IsPay = 1 AND o.IsStockOrder = 0 AND o.DownLoadUpdateTime &gt; '2000-01-01' AND o.PayTime &lt; Dateadd(d, -1, Getdate()) AND Isnull(o.TotalPrice, 0) &gt; 0GROUP BY o.CustomerID, a.OrderSourceType, o.WareHouseID, o.Country, k.SKU;
    001+ 1.问题分析：http://erpapi.zhcxkj.com/erp/order/getorder 接口：计算器调用的订单接口：梳理，接口瓶颈，推动优化改造
    002-
    002+ 此处做记录，后面并发高了，跟踪验证
    003- 3-2:sql 2
    003+
    004- 问题分析：
    004+ select count(0) from RB_OrderInfo nolock -- 45657760select count(0) from RB_OrderSource nolock -- 13536select count(0) from RB_OrderList nolock -- 589174372.解决方案： 1. 慢sql优化；都有使用索引 2. 数据归档；已归档了2年前的数据3.sql-1
    005- 获取订单费用，查询需1分46秒，RB_Product_Fee
    005+ 有使用索引，并发高了慢
    006- 5亿数据是否可以归档？
    006+
    007-
    007+ SELECTo.CustomerID,
    008-
    008+ a.OrderSourceType,
    009-
    009+ o.WareHouseID,
    010-
    010+ o.Country,
    011- SELECTOrderCode,
    011+ k.SKU,
    012- OperateType,
    012+ Max(K.ID)ASID
    013- OperateFee,
    013+ FROMRB_OrderInfo o(nolock)
    014- ExchangeRMB
    014+ INNERJOINRB_OrderSource a(nolock)
    015- FROMRB_Product_Fee(NOLOCK)
    015+ ONo.CustomerID =a.CustomerID
    016- WHERECustomerID =1
    016+ ANDo.OrdersourceID =a.ID
    017- ANDOrderCode IN(202204010020118507041,202204190315119297134,202205212316120889102,202205312256121306117 )
    017+ INNERJOINRB_OrderList k(nolock)
    018- ANDIsTrue =1
    018+ ONo.CustomerID =k.CustomerID
    019- ANDSKU ='5614827'
    019+ ANDo.OrderCode =k.OrderCode
    020- ORDERBYOperateType ASC;
    020+ WHEREo.CustomerID =1
    021+ ANDo.WareHouseID =5312
    022+ ANDa.OrderSourceType =70
    023+ ANDo.Country ='BR'
    024+ ANDk.SKU IN('16537679','16536941','16534306','16530187',
    025+ '16528995','16528982','16525458','16525272',
    026+ '16525033','16524847','16524799','16524719',
    027+ '16524240','16523795','16521640','16518471',
    028+ '16518464','16518223','16517625','16514105',
    029+ '16513528','16512498','16512456','16512264',
    030+ '16512008','16511862','16511745','16511534',
    031+ '16509012','16508778','16508584','16505811',
    032+ '16505671','16505385','16505371','16505088',
    033+ '16505101','16504064','16504009','16503928',
    034+ '16503884','16503780','16503544','16503296',
    035+ '16503292','16503147','16502906','16502621',
    036+ '16500950','16496594','16496541','16496470',
    037+ '16496114','16495511','16494047','16492704',
    038+ '16491566','16491558','16490692','16489708',
    039+ '16489644','16489240','16488362','16488599',
    040+ '16488599','16488411','16487501','16486823',
    041+ '16486664','16486497','16486334','16486493',
    042+ '16486493','16486260','16485683','16485775',
    043+ '16485708','16485638','16485481','16485120',
    044+ '16483935','16483715','16483717','16483127',
    045+ '16482841','16482849','16482851','16482778',
    046+ '16482429','16482012','16481475','16481224',
    047+ '16481155','16479651','16477473','16477385',
    048+ '16477433','16476904','16476894')
    049+ ANDo.OrderState &gt;0
    050+ ANDo.ProductNumber =1
    051+ ANDo.IsPay =1
    052+ ANDo.IsStockOrder =0
    053+ ANDo.DownLoadUpdateTime &gt;'2000-01-01'
    054+ ANDo.PayTime &lt;Dateadd(d,-1,Getdate())
    055+ ANDIsnull(o.TotalPrice,0)&gt;0
    056+ GROUPBYo.CustomerID,
    057+ a.OrderSourceType,
    058+ o.WareHouseID,
    059+ o.Country,
    060+ k.SKU;
    061+
    062+ 3-2:sql 2
    063+ 问题分析：
    064+ 获取订单费用，查询需1分46秒，RB_Product_Fee
    065+ 5亿数据是否可以归档？
    066+
    067+
    068+ SELECTOrderCode,
    069+ OperateType,
    070+ OperateFee,
    071+ ExchangeRMB
    072+ FROMRB_Product_Fee(NOLOCK)
    073+ WHERECustomerID =1
    074+ ANDOrderCode IN(202204010020118507041,202204190315119297134,202205212316120889102,202205312256121306117 )
    075+ ANDIsTrue =1
    076+ ANDSKU ='5614827'
    077+ ORDERBYOperateType ASC;
    001- 1.问题分析：<br />http://erpapi.zhcxkj.com/erp/order/getorder 接口：计算器调用的订单接口：梳理，接口瓶颈，推动优化改造<br />sql-1 ,50个sku查询需38秒<br /><br />select count(0) from RB_OrderInfo nolock -- 45657760<br />select count(0) from RB_OrderSource nolock -- 13536<br />select count(0) from RB_OrderList nolock -- 58917437<br /><br />2.解决方案：<br /> 1. 慢sql优化；<br /> 2. 数据归档；<br /> 3. 缓存数据；<br /><br />3.sql-1<br />SELECT o.CustomerID,<br /> a.OrderSourceType,<br /> o.WareHouseID,<br /> o.Country,<br /> k.SKU,<br /> Max(K.ID) AS ID<br />FROM RB_OrderInfo o (nolock)<br /> INNER JOIN RB_OrderSource a (nolock)<br /> ON o.CustomerID = a.CustomerID<br /> AND o.OrdersourceID = a.ID<br /> INNER JOIN RB_OrderList k (nolock)<br /> ON o.CustomerID = k.CustomerID<br /> AND o.OrderCode = k.OrderCode<br />WHERE o.CustomerID = 1<br /> AND o.WareHouseID = 5312<br /> AND a.OrderSourceType = 1<br /> AND o.Country = 'US'<br /> AND k.SKU IN( 5250113, 5257736, 3731035, 3731162,<br /> 3731199, 3731292, 9893079, 3192277,<br /> 3192313, 3194756, 3194769, 14376488,<br /> 14740416, 5614827, 8540889, 8540898,<br /> 12041722, 5905293, 11077686, 8335283,<br /> 8540889, 13248296, 13332730, 1743553,<br /> 5816653, 5816675, 9043295, 8371471,<br /> 10746757, 12053359, 6293080, 5306233,<br /> 9043295, 1743553, 12860847, 11371896,<br /> 3069611, 10837860, 10837860, 10837860,<br /> 10837860, 13709107, 10837860, 14342048,<br /> 14342519, 14342519, 14342548, 14342548,<br /> 10967446, 7593170 )<br /> AND o.OrderState &gt; 0<br /> AND o.ProductNumber = 1<br /> AND o.IsPay = 1<br /> AND o.IsStockOrder = 0<br /> AND o.DownLoadUpdateTime &gt; '2000-01-01'<br /> AND o.PayTime &lt; Dateadd(d, -1, Getdate())<br /> AND Isnull(o.TotalPrice, 0) &gt; 0<br />GROUP BY o.CustomerID,<br /> a.OrderSourceType,<br /> o.WareHouseID,<br /> o.Country,<br /><p> k.SKU;</p>
    001+ 1.问题分析：<br />http://erpapi.zhcxkj.com/erp/order/getorder 接口：计算器调用的订单接口：梳理，接口瓶颈，推动优化改造<br /><p></p>
    002- <p><br /></p>
    002+ <p>此处做记录，后面并发高了，跟踪验证</p>
    003- <p>3-2:sql 2</p>
    003+ <br />
    004- <p>问题分析：
    004+ select count(0) from RB_OrderInfo nolock -- 45657760<br />select count(0) from RB_OrderSource nolock -- 13536<br />select count(0) from RB_OrderList nolock -- 58917437<br /><br />2.解决方案：<br /> 1. 慢sql优化；都有使用索引<br /> 2. 数据归档；已归档了2年前的数据<br /><br /><br /><p>3.sql-1</p>
    005- </p><p class="MsoNormal">获取订单费用，查询需1分46秒，<span>RB_Product_Fee</span><span>
    005+ <p>有使用索引，并发高了慢</p>
    006- </span></p><p class="MsoNormal">5亿数据是否可以归档？</p>
    006+ <p><br /></p>
    007-
    007+ <p class="MsoNormal">SELECT<span>o</span>.CustomerID,</p>
    008-
    008+ <p class="MsoNormal"><span>a</span>.OrderSourceType,</p>
    009- <p><br /></p>
    009+ <p class="MsoNormal"><span>o</span>.WareHouseID,</p>
    010- <p>
    010+ <p class="MsoNormal"><span>o</span>.Country,</p>
    011- </p><p class="MsoNormal">SELECT<span>OrderCode</span>,</p>
    011+ <p class="MsoNormal"><span>k</span>.SKU,</p>
    012- <p class="MsoNormal"><span>OperateType</span>,</p>
    012+ <p class="MsoNormal">Max(K.ID)AS<span>ID</span></p>
    013- <p class="MsoNormal"><span>OperateFee</span>,</p>
    013+ <p class="MsoNormal">FROM<span>RB_OrderInfo o</span>(nolock)</p>
    014- <p class="MsoNormal"><span>ExchangeRMB</span></p>
    014+ <p class="MsoNormal">INNERJOIN<span>RB_OrderSource a</span>(nolock)</p>
    015- <p class="MsoNormal">FROM<span>RB_Product_Fee</span>(NOLOCK)</p>
    015+ <p class="MsoNormal">ON<span>o</span>.CustomerID =<span>a</span>.CustomerID</p>
    016- <p class="MsoNormal">WHERE<span>CustomerID </span>=<span>1</span></p>
    016+ <p class="MsoNormal">AND<span>o</span>.OrdersourceID =<span>a</span>.ID</p>
    017- <p class="MsoNormal">AND<span>OrderCode </span>IN(<span>202204010020118507041</span>,<span>202204190315119297134</span>,<span>202205212316120889102</span>,<span>202205312256121306117 </span>)</p>
    017+ <p class="MsoNormal">INNERJOIN<span>RB_OrderList k</span>(nolock)</p>
    018- <p class="MsoNormal">AND<span>IsTrue </span>=<span>1</span></p>
    018+ <p class="MsoNormal">ON<span>o</span>.CustomerID =<span>k</span>.CustomerID</p>
    019- <p class="MsoNormal">AND<span>SKU </span>='5614827'</p>
    019+ <p class="MsoNormal">AND<span>o</span>.OrderCode =<span>k</span>.OrderCode</p>
    020- <p class="MsoNormal" align="justify" style="text-align:justify;">ORDERBY<span>OperateType </span>ASC;</p>
    020+ <p class="MsoNormal">WHERE<span>o</span>.CustomerID =<span>1</span></p>
    021+ <p class="MsoNormal">AND<span>o</span>.WareHouseID =<span>5312</span></p>
    022+ <p class="MsoNormal">AND<span>a</span>.OrderSourceType =<span>70</span></p>
    023+ <p class="MsoNormal">AND<span>o</span>.Country ='BR'</p>
    024+ <p class="MsoNormal">AND<span>k</span>.SKU IN('16537679','16536941','16534306','16530187',</p>
    025+ <p class="MsoNormal">'16528995','16528982','16525458','16525272',</p>
    026+ <p class="MsoNormal">'16525033','16524847','16524799','16524719',</p>
    027+ <p class="MsoNormal">'16524240','16523795','16521640','16518471',</p>
    028+ <p class="MsoNormal">'16518464','16518223','16517625','16514105',</p>
    029+ <p class="MsoNormal">'16513528','16512498','16512456','16512264',</p>
    030+ <p class="MsoNormal">'16512008','16511862','16511745','16511534',</p>
    031+ <p class="MsoNormal">'16509012','16508778','16508584','16505811',</p>
    032+ <p class="MsoNormal">'16505671','16505385','16505371','16505088',</p>
    033+ <p class="MsoNormal">'16505101','16504064','16504009','16503928',</p>
    034+ <p class="MsoNormal">'16503884','16503780','16503544','16503296',</p>
    035+ <p class="MsoNormal">'16503292','16503147','16502906','16502621',</p>
    036+ <p class="MsoNormal">'16500950','16496594','16496541','16496470',</p>
    037+ <p class="MsoNormal">'16496114','16495511','16494047','16492704',</p>
    038+ <p class="MsoNormal">'16491566','16491558','16490692','16489708',</p>
    039+ <p class="MsoNormal">'16489644','16489240','16488362','16488599',</p>
    040+ <p class="MsoNormal">'16488599','16488411','16487501','16486823',</p>
    041+ <p class="MsoNormal">'16486664','16486497','16486334','16486493',</p>
    042+ <p class="MsoNormal">'16486493','16486260','16485683','16485775',</p>
    043+ <p class="MsoNormal">'16485708','16485638','16485481','16485120',</p>
    044+ <p class="MsoNormal">'16483935','16483715','16483717','16483127',</p>
    045+ <p class="MsoNormal">'16482841','16482849','16482851','16482778',</p>
    046+ <p class="MsoNormal">'16482429','16482012','16481475','16481224',</p>
    047+ <p class="MsoNormal">'16481155','16479651','16477473','16477385',</p>
    048+ <p class="MsoNormal">'16477433','16476904','16476894')</p>
    049+ <p class="MsoNormal">AND<span>o</span>.OrderState &gt;<span>0</span></p>
    050+ <p class="MsoNormal">AND<span>o</span>.ProductNumber =<span>1</span></p>
    051+ <p class="MsoNormal">AND<span>o</span>.IsPay =<span>1</span></p>
    052+ <p class="MsoNormal">AND<span>o</span>.IsStockOrder =<span>0</span></p>
    053+ <p class="MsoNormal">AND<span>o</span>.DownLoadUpdateTime &gt;'2000-01-01'</p>
    054+ <p class="MsoNormal">AND<span>o</span>.PayTime &lt;Dateadd(d,-1,Getdate())</p>
    055+ <p class="MsoNormal">ANDIsnull(o.TotalPrice,<span>0</span>)&gt;<span>0</span></p>
    056+ <p class="MsoNormal">GROUPBY<span>o</span>.CustomerID,</p>
    057+ <p class="MsoNormal"><span>a</span>.OrderSourceType,</p>
    058+ <p class="MsoNormal"><span>o</span>.WareHouseID,</p>
    059+ <p class="MsoNormal"><span>o</span>.Country,</p>
    060+ <p class="MsoNormal"><span>k</span>.SKU;</p>
    061+ <p><br /></p>
    062+ <p>3-2:sql 2</p>
    063+ <p>问题分析：</p>
    064+ <p class="MsoNormal">获取订单费用，查询需1分46秒，<span>RB_Product_Fee</span><span> </span></p>
    065+ <p class="MsoNormal">5亿数据是否可以归档？</p>
    066+ <p><br /></p>
    067+ <p><br /></p>
    068+ <p class="MsoNormal">SELECT<span>OrderCode</span>,</p>
    069+ <p class="MsoNormal"><span>OperateType</span>,</p>
    070+ <p class="MsoNormal"><span>OperateFee</span>,</p>
    071+ <p class="MsoNormal"><span>ExchangeRMB</span></p>
    072+ <p class="MsoNormal">FROM<span>RB_Product_Fee</span>(NOLOCK)</p>
    073+ <p class="MsoNormal">WHERE<span>CustomerID </span>=<span>1</span></p>
    074+ <p class="MsoNormal">AND<span>OrderCode </span>IN(<span>202204010020118507041</span>,<span>202204190315119297134</span>,<span>202205212316120889102</span>,<span>202205312256121306117 </span>)</p>
    075+ <p class="MsoNormal">AND<span>IsTrue </span>=<span>1</span></p>
    076+ <p class="MsoNormal">AND<span>SKU </span>='5614827'</p>
    077+ <p class="MsoNormal" align="justify" style="text-align:justify;">ORDERBY<span>OperateType </span>ASC;</p>
  3. 2023-04-14 15:34:48, 由 陈少军 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "1"。
    修改了 实际完成，旧值为 ""，新值为 "2023-04-14 15:34:34"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2023-04-14 15:34:48"。
    修改了 由谁完成，旧值为 ""，新值为 "chenshaojun"。
    跟踪验证
  4. 2023-10-31 14:53:15, 由 周杰 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "chenshaojun"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 14:53:15"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
所属迭代 系统管理部 - 系统服务优化
所属模块 /
相关需求 暂无
指派给 Closed 于 2023-10-31 14:53:15
任务类型 后端
任务标签 初始
开发组 有品开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给
由谁创建 陈少军 于 2023-04-06 11:27:11
由谁完成 陈少军 于 2023-04-14 15:34:34
由谁取消 暂无
由谁关闭 周杰 于 2023-10-31 14:53:15
关闭原因 已完成
最后编辑 周杰 于 2023-10-31 14:53:15
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 1工时
预计剩余 0工时
预计开始 2023-04-06
实际开始 2023-04-14
截止日期 2023-04-13