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查询，看看效率是否有提高。