项目中出现SQL慢查询导致影响用户使用,原因是两个索引字段的类型不同导致无法使用索引,使用CONCAT转换为字符串即可解决
原始SQL:
SELECT * FROM hqew_order_goods og LEFT JOIN hqew_remove_list ri ON ri.order_rec_id = og.rec_id AND real_removal_numbers > 8888 LEFT JOIN hqew_all_stock sl ON sl.removal_item_id = ri.id AND sl.stock_log_type = 88 WHERE ( og.order_id = '888888' ) AND ( og.removal_numbers > 0 )
SQL问题点:(字段类型不同)
ri.order_rec_id = og.rec_id
修正SQL:
SELECT * FROM hqew_order_goods og LEFT JOIN hqew_remove_list ri ON ri.order_rec_id = CONCAT( og.rec_id, '' ) AND real_removal_numbers > 8888 LEFT JOIN hqew_all_stock sl ON sl.removal_item_id = ri.id AND sl.stock_log_type = 88 WHERE ( og.order_id = '888888' ) AND ( og.removal_numbers > 0 )
