现象

  • 页面查询深度分页异常（条件：时间+支付状态）
  • 大数据导出丢失数据（条件：时间+支付状态）
  • 触发数据库告警（rt增加、cpu 100%）

  • 问题分析及改进方案

    查询：

    • 数据库索引不合理出现慢sql

     

    SELECT `saas_biz_order`.`biz_order_id`, `out_order_id` 
FROM `saas_biz_order_0016` `saas_biz_order`
FORCE index (idx_gmtcreate_merchant_storeid_ismain_status) 
WHERE (`saas_biz_order`.`is_main` = 1)
    AND `saas_biz_order`.`store_id` IN ( 
        'RB00552', 
        'RB00562', 
        'RB00542', 
        'RB00082', 
        'RB00102', 
        'RB00192', 
        'RB00222', 
        'RB00032', 
        'RB00272', 
        'RB00152', 
        'RB00021', 
        'RB00252', 
        'RB00312', 
        'RB00242', 
        'RB00302', 
        'RB00282', 
        'RB00342', 
        'RB00552'
    )
    AND (`saas_biz_order`.`gmt_create` >= '2021-07-08 00:00:00')
    AND (`saas_biz_order`.`gmt_create` < '2021-08-08 23:59:59')
    AND (`saas_biz_order`.`order_status` = 6)
    AND (`saas_biz_order`.`merchant_code` = 'RENBEN')
--    AND (`saas_biz_order`.`pay_time` >= '2021-07-08 00:00:00')
-- AND (`saas_biz_order`.`pay_time` < '2021-08-08 00:00:00')
-- AND (`saas_biz_order`.`delivery_service_type` = 'PLAT_DELIVERY')
ORDER BY `saas_biz_order`.`gmt_create` DESC
LIMIT 75880, 20;

     

     

    方案：

    • 增加（时间+商家+店铺+是否主单+状态）
    • 特定条件走强制索引

     

    导出问题：

    一、分片规则不合理

    深度分页响应时间降低

     

     

    方案：

      • 按门店分组，减少深度分页的场景
      • 适当增加分片步长

     

    二、代码问题导致大量的count

    每次分片查询订单列表时，多余的count影性能

    方案：

    去掉无用的count

     

    三、分片查询订单列表时内部for循环数据库，增加响应时间

    for循环查询每个订单的子订单，网络开销太大，性能大大降低。

     

    SELECT * FROM `saas_biz_order_0016` WHERE  merchant_code = 'RENBEN' and parent_id in (
5000528521670950094,
5000528521734790094,
5000528518678420093,
5000528511254780093,
5000528508098740093,
5000528508234200093,
5000528506862220094,
5000528498766580094,
5000528497838740093,
5000528491536610094,
5000528490932850093,
5000528491200750094,
5000528488974890094,
5000528487104240094,
5000528486200740094,
5000528485480480094,
5000528484426800094,
5000528482534870094,
5000528482382930094,
5000528481284160094,
5000528480002310094,
5000528479698110094,
5000528478598330093,
5000528476966290094,
5000528476686750094,
5000528472262370093,
5000528471322010093,
5000528468858850094,
5000528467904220094,
5000528466440940093,
5000528466102180093,
5000528464068800094,
5000528463556060094,
5000528462574940093,
5000528462648760094,
5000528461210530094,
5000528460096730094,
5000528458530640094,
5000528455124150094,
5000528453612740093,
5000528453840450094,
5000528453196560094,
5000528453248060093,
5000528452624060093,
5000528451974880093,
5000528452462220093,
5000528452206320094,
5000528451680650094,
5000528447296910094,
5000528446728840093,
5000528445916120094,
5000528445862270093,
5000528445040260094,
5000528444690750094,
5000528443168950094,
5000528442596630093,
5000528442126790093,
5000528439234960093,
5000528439358070094,
5000528438687000093,
5000528438346470093,
5000528437104480094,
5000528436892980094,
5000528434968070093,
5000528431896630094,
5000528431826930094,
5000528429956970094,
5000528427776770093,
5000528427014270094,
5000528427110080093,
5000528424368780094,
5000528423546850093,
5000528423092900094,
5000528422912610093,
5000528421016900094,
5000528420858180094,
5000528419758300093,
5000528419702100094,
5000528419582410093,
5000528419072890094,
5000528419074060093,
5000528416730720094,
5000528416364770094,
5000528415832570093,
5000528414792560093,
5000528414568200094,
5000528413868170094,
5000528413488190094,
5000528412410900093,
5000528411994240094,
5000528411724750094,
5000528411170140094,
5000528410474880093,
5000528408920760093,
5000528408912330093,
5000528408580880093,
5000528408474800093,
5000528407774980094,
5000528405152570093,
5000528404938680093,
5000528404804400093,
5000528402968050093,
5000528402190600094,
5000528401716680093,
5000528401026930094,
5000528399148350094,
5000528398992430094,
5000528395669000094,
5000528394336620094,
5000528394364440094,
5000528391610570093,
5000528390388870094,
5000528390548660094,
5000528390184920093,
5000528390564060094,
5000528390524110094,
5000528388802700094,
5000528386336400093,
5000528384482620093,
5000528383964700093,
5000528384002010094,
5000528383420250094,
5000528383362350093,
5000528382892620094,
5000528382400010094,
5000528382028340093,
5000528381680970093,
5000528381880720093,
5000528380946690094,
5000528380332740093,
5000528380220550093,
5000528379898560094,
5000528378562180093,
5000528377447000094,
5000528377458460094,
5000528376326220094,
5000528374984920093,
5000528374236970093,
5000528369296980094,
5000528368944400093,
5000528368608180093,
5000528368546540093,
5000528368130110093,
5000528366642720093,
5000528366174060093,
5000528365772380093,
5000528361718990094,
5000528361582730094,
5000528360806170094,
5000528360864310093,
5000528359604620093,
5000528358234680093,
5000528357246880094,
5000528357016210093,
5000528356212790093,
5000528355964830093,
5000528355466430093,
5000528355052900093,
5000528353894580094,
5000528352240460094,
5000528351992810094,
5000528351634820094,
5000528351432530094,
5000528350038610094,
5000528349720700093,
5000528348622620094,
5000528348616590093,
5000528348478880093,
5000528346678890094,
5000528345740870093,
5000528344760250093,
5000528342348680094,
5000528341788230093,
5000528341818060093,
5000528340246360094,
5000528338994560094,
5000528337838970093,
5000528336588930094,
5000528335392440093,
5000528335238010094,
5000528332698090093,
5000528332328440093,
5000528330542550094,
5000528329080960093,
5000528328842580093,
5000528328294360093,
5000528327080120094,
5000528325454700094,
5000528325728020093,
5000528324374190093,
5000528319556810093,
5000528317566570093,
5000528314988760094,
5000528314070830093,
5000528311578180094,
5000528309282810094,
5000528309026750093,
5000528308882720093,
5000528307028500094,
5000528305292400094)

     

    方案：

    改为批量处理

     

    四、大量并发

    并发量需要评估，如果能够优化好每个切换的处理时效，并控制好并发度即可。

    方案：

    跟进预发的导出处理时效，评估好单数据库能够承受的并发量，然后按照机器总数平均分配。

     

    效果对比

    优化前

     

     

    优化后

     

     

     

     

     

    opensearch导出的问题

    1：for循环查询履约，而且履约无批量接口

    2：全部组装action、vertial

    3：opensearch filter语句比query语句性能差很多