以下这个sql在测试环境数据量小，10几毫秒ms就出数据了，正式环境出现问题
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'
会发现
image.png
在出现null加上索引，速度会变快很多
