SELECT t1.supplier_id,IFNULL(t1.receiptcount,0) as receipt_count,IFNULL(t1.receiptamount,0) as receipt_amount,IFNULL(t2.refundcount,0) as refund_count,IFNULL(t2.refundamount,0) as refund_amount from ( SELECT a.supplier_id, count(DISTINCT a.order_id) AS receiptcount, sum(IFNULL(b.receipt_amount,0))/100 AS receiptamount FROM order_info a left JOIN order_receipt b ON a.order_id = b.order_id WHERE a.order_type IN (70, 75) AND a.is_del = 0 AND a.order_state IN (40, 50, 60) AND a.create_time >= '2023-05-28 00:00:00' AND a.create_time < '2023-05-29 00:00:00' AND a.supplier_id in ( 78,63,58,57,64,72,71,74,83,77,70,69,67,82,65,87,73,59,66,60,86,85,79,80,84,90 ) GROUP BY a.supplier_id order by a.supplier_id ASC ) t1 LEFT JOIN ( select c.supplier_id,count(DISTINCT c.order_id) as refundcount,sum(IFNULL(c.refund_amount,0)) / 100 as refundamount from order_refund c LEFT JOIN order_info d on c.order_id = d.order_id WHERE d.order_type in (70,75) AND d.order_state IN (40, 50, 60) and c.refund_state =13 AND c.is_del = 0 and c.create_time >= '2023-05-28 00:00:00' AND c.create_time < '2023-05-29 00:00:00' AND c.supplier_id in ( 78,63,58,57,64,72,71,74,83,77,70,69,67,82,65,87,73,59,66,60,86,85,79,80,84,90 ) GROUP BY c.supplier_id ) t2 on t1.supplier_id = t2.supplier_id order by t1.supplier_id asc

这是一个比较复杂的 SQL 语句，但是根据您提供的信息，我们可以从以下几个方面来考虑进行优化：

1.使用 EXISTS 子查询替代 LEFT JOIN

在语句中，我们使用了两个 LEFT JOIN 子查询，这在数据量大的情况下可能会影响查询性能。我们可以考虑使用 EXISTS 子查询替代 LEFT JOIN，如下所示：

SELECT t1.supplier_id,
       IFNULL(t1.receiptcount, 0) AS receipt_count,
       IFNULL(t1.receiptamount, 0) AS receipt_amount,
       IFNULL(t2.refundcount, 0) AS refund_count,
       IFNULL(t2.refundamount, 0) AS refund_amount
FROM (SELECT a.supplier_id,
             count(DISTINCT a.order_id) AS receiptcount,
             sum(IFNULL(b.receipt_amount, 0)) / 100 AS receiptamount
      FROM order_info a
      WHERE a.order_type IN (70, 75)
        AND a.is_del = 0
        AND a.order_state IN (40, 50, 60)
        AND a.create_time >= '2023-05-28 00:00:00'
        AND a.create_time < '2023-05-29 00:00:00'
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
        AND EXISTS (SELECT 1 FROM order_receipt b WHERE a.order_id = b.order_id)
      GROUP BY a.supplier_id
      ORDER BY a.supplier_id ASC) t1
LEFT JOIN (SELECT c.supplier_id,
                  count(DISTINCT c.order_id) AS refundcount,
                  sum(IFNULL(c.refund_amount, 0)) / 100 AS refundamount
           FROM order_refund c
           WHERE c.refund_state = 13
             AND c.is_del = 0
             AND c.create_time >= '2023-05-28 00:00:00'
             AND c.create_time < '2023-05-29 00:00:00'
             AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
             AND EXISTS (SELECT 1 FROM order_info d WHERE c.order_id = d.order_id AND d.order_type IN (70, 75) AND d.order_state IN (40, 50, 60)))
           GROUP BY c.supplier_id) t2 ON t1.supplier_id = t2.supplier_id
ORDER BY t1.supplier_id ASC;

2.尽可能减少使用 IFNULL 函数

在语句中，我们使用了 IFNULL 函数来处理 NULL 值，这可能会影响查询性能。我们可以尽可能减少使用 IFNULL 函数，改用 COALESCE 函数，如下所示：

SELECT t1.supplier_id,
       COALESCE(t1.receiptcount, 0) AS receipt_count,
       COALESCE(t1.receiptamount, 0) AS receipt_amount,
       COALESCE(t2.refundcount, 0) AS refund_count,
       COALESCE(t2.refundamount, 0) AS refund_amount
FROM (SELECT a.supplier_id,
             count(DISTINCT a.order_id) AS receiptcount,
             sum(COALESCE(b.receipt_amount, 0)) / 100 AS receiptamount
      FROM order_info a
      WHERE a.order_type IN (70, 75)
        AND a.is_del = 0
        AND a.order_state IN (40, 50, 60)
        AND a.create_time >= '2023-05-28 00:00:00'
        AND a.create_time < '2023-05-29 00:00:00'
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
        AND EXISTS (SELECT 1 FROM order_receipt b WHERE a.order_id = b.order_id)
      GROUP BY a.supplier_id
      ORDER BY a.supplier_id ASC) t1
LEFT JOIN (SELECT c.supplier_id,
                  count(DISTINCT c.order_id) AS refundcount,
                  sum(COALESCE(c.refund_amount, 0)) / 100 AS refundamount
           FROM order_refund c
           WHERE c.refund_state = 13
             AND c.is_del = 0
             AND c.create_time >= '2023-05-28 00:00:00'
             AND c.create_time < '2023-05-29 00:00:00'
             AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
             AND EXISTS (SELECT 1 FROM order_info d WHERE c.order_id = d.order_id AND d.order_type IN (70, 75) AND d.order_state IN (40, 50, 60)))
           GROUP BY c.supplier_id) t2 ON t1.supplier_id = t2.supplier_id
ORDER BY t1.supplier_id ASC;

3.尽可能减少使用 DISTINCT 关键字

在语句中，我们使用了 DISTINCT 关键字来去重，这可能会影响查询性能。我们可以尽可能减少使用 DISTINCT 关键字，改用 GROUP BY 子句，如下所示：

SELECT t1.supplier_id,
       COALESCE(t1.receiptcount, 0) AS receipt_count,
       COALESCE(t1.receiptamount, 0) AS receipt_amount,
       COALESCE(t2.refundcount, 0) AS refund_count,
       COALESCE(t2.refundamount, 0) AS refund_amount
FROM (SELECT a.supplier_id,
             COUNT(a.order_id) AS receiptcount,
             SUM(COALESCE(b.receipt_amount, 0)) / 100 AS receiptamount
      FROM order_info a
           LEFT JOIN order_receipt b ON a.order_id = b.order_id
      WHERE a.order_type IN (70, 75)
        AND a.is_del = 0
        AND a.order_state IN (40, 50, 60)
        AND a.create_time >= '2023-05-28 00:00:00'
        AND a.create_time < '2023-05-29 00:00:00'
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
      GROUP BY a.supplier_id
      ORDER BY a.supplier_id ASC) t1
LEFT JOIN (SELECT c.supplier_id,
                  COUNT(c.order_id) AS refundcount,
                  SUM(COALESCE(c.refund_amount, 0)) / 100 AS refundamount
           FROM order_refund c
                LEFT JOIN order_info d ON c.order_id = d.order_id
           WHERE c.refund_state = 13
             AND c.is_del = 0
             AND c.create_time >= '2023-05-28 00:00:00'
             AND c.create_time < '2023-05-29 00:00:00'
             AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
             AND d.order_type IN (70, 75)
             AND d.order_state IN (40, 50, 60)
           GROUP BY c.supplier_id) t2 ON t1.supplier_id = t2.supplier_id
ORDER BY t1.supplier_id ASC;

以上是三个简单的优化建议，具体优化方案需要根据实际情况进行调整。