mysql優化 - mysql慢查詢copying to tmp table

問題描述

windows server，無論修改my.ini的tmp_table_size，max_heap_table_size到多少，情況都一樣。同樣的表和查詢語句，在本地運行，沒出現慢查詢。

SELECT g.goods_id, g.goods_name, g.shop_price, g.goods_thumb, SUM(og.goods_number) AS goods_numberFROM `chinaetm`.`ecs_goods` AS g, `chinaetm`.`ecs_order_info` AS o, `chinaetm`.`ecs_order_goods` AS ogWHERE g.is_on_sale = 1AND g.is_alone_sale = 1AND g.is_delete = 0AND ( g.cat_id IN (’21’,’75’,’206’,’207’,’208’,’209’,’210’,’211’,’212’ ) OR g.goods_id IN (’’)) AND og.order_id = o.order_idAND og.goods_id = g.goods_idAND ( o.order_status = ’1’ OR o.order_status = ’5’)AND ( o.pay_status = ’2’ OR o.pay_status = ’1’)AND ( o.shipping_status = ’1’ OR o.shipping_status = ’2’)GROUP BY g.goods_idORDER BY goods_number DESC, g.goods_id DESC

explain結果

問題解答

謝邀，mysql最終處理sql的執行情況在不同環境和版本下可能有所不同，確定本地環境和線上環境是否一致，表的數據量是否一致，其次，題主可以嘗試把or查詢改為in查詢，看看效率是否有提高。