mysql一直copying to tmp table_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_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

查询，看看效率是否有提高。