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_number FROM `chinaetm`.`ecs_goods` AS g, `chinaetm`.`ecs_order_info` AS o, `chinaetm`.`ecs_order_goods` AS og WHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND ( g.cat_id IN ( '21', '75', '206', '207', '208', '209', '210', '211', '212' ) OR g.goods_id IN ('') ) AND og.order_id = o.order_id AND og.goods_id = g.goods_id AND ( 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_id ORDER BY goods_number DESC, g.goods_id DESC
explain结果
谢邀,mysql
最终处理sql
的执行情况在不同环境和版本下可能有所不同,确定本地环境和线上环境是否一致,表的数据量是否一致,其次,题主可以尝试把or
查询改为in
查询,看看效率是否有提高。