mysql慢查询copying to tmp table

Question

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结果

Answer 1

谢邀，
mysql最终处理sql的执行情况在不同环境和版本下可能有所不同，确定本地环境和线上环境是否一致，表的数据量是否一致，其次，题主可以尝试把or查询改为in查询，看看效率是否有提高。

Answer 2

windows server2012 线上mysql5.56线下5.76，表数据和结构一模一样，索引也一样，相同sql执行计划两边不一样区别很大，线上修改参数tmp_table_size，max_heap_table_size，改都多少都没用