以下這個sql在測試環(huán)境數(shù)據(jù)量小，10幾毫秒ms就出數(shù)據(jù)了，正式環(huán)境出現(xiàn)問題

SELECT 
  od.shb_goods_id,
  od.shb_order_id,
  od.shb_activity_id,
  sac.shb_acttype_id,
  od.goods_name,
  od.count,
  o.discount,
  o.shb_user_id,
  o.get_money,
  od.goods_price,
  od.favo_price,
  od.market_price,
  od.count AS Maxcount,
  od.price_unit,
  od.spec,
  od.now_price,
  od.discount AS discount_d,
  od.atype,
  od.rebate,
  fm.third_type,
  sg.goods_barcode 
FROM
  shb_shop_order_cdetail od 
  LEFT JOIN shb_shop_corder o 
    ON o.shb_order_id = od.shb_order_id 
  LEFT JOIN shb_shop_goods sg 
    ON sg.shb_goods_id = od.shb_goods_id 
  LEFT JOIN shb_flow_money fm 
    ON fm.shb_order_id = o.shb_order_id 
  LEFT JOIN shb_saler_activity sac 
    ON sac.shb_activity_id = od.shb_activity_id 
WHERE o.shb_order_xid = '079119111416470490' 
  AND o.shb_shop_id = '100343' 

對sql進行分析

EXPLAIN SELECT 
  od.shb_goods_id,
  od.shb_order_id,
.....
FROM
  shb_shop_order_cdetail od 
  LEFT JOIN shb_shop_corder o 
    ON o.shb_order_id = od.shb_order_id 
  LEFT JOIN shb_shop_goods sg 
    ON sg.shb_goods_id = od.shb_goods_id 
  LEFT JOIN shb_flow_money fm 
    ON fm.shb_order_id = o.shb_order_id 
  LEFT JOIN shb_saler_activity sac 
    ON sac.shb_activity_id = od.shb_activity_id 
WHERE o.shb_order_xid = '079119111416470490' 
  AND o.shb_shop_id = '100343' 

會發(fā)現(xiàn)

image.png

在出現(xiàn)null加上索引，速度會變快很多