来自 3 个表的 mysql "distinct query"在大数据上执行缓慢

Question

这里有三个表，order、order_record、pay，有近2300000条记录。

给定1个order_id时，支付表中会有多于1条记录，所以我需要使用DISTINCT来删除重复的结果

现在我需要从 order_id 上的这三个表连接中获取不同的数据，下面的示例查询 SQL:

SELECT
    DISTINCT (a.order_id)
    a.order_id,a.user_id
    b.boss_order_id,
    c.pay_id,

    FROM order a
    LEFT JOIN order_record b ON a.order_id = b.order_id AND b.is_delete IN (0,1)
    LEFT JOIN pay c ON a.order_id = c.order_id AND c.is_delete =0  WHERE 1=1 AND a.is_delete IN (0,1)
    ORDER BY a.id DESC LIMIT 0, 10

此查询将花费大量时间。然后我更改为使用“GROUP BY”:

SELECT

    a.order_id,a.user_id
    b.boss_order_id,
    c.pay_id,

    FROM order a
    LEFT JOIN order_record b ON a.order_id = b.order_id AND b.is_delete IN (0,1)
    LEFT JOIN pay c ON a.order_id = c.order_id AND c.is_delete =0  WHERE 1=1 AND a.is_delete IN (0,1)
    GROUP BY a.order_id
    ORDER BY a.id DESC LIMIT 0, 10

这次查询需要122秒。

有没有更快的实现方法？

Answer 1

您正在使用左连接。因此，您可以这样做:

SELECT o.order_id, o.user_id, orr.boss_order_id, p.pay_id,
FROM (SELECT o.*
      FROM order o
      WHERE o.is_delete IN (0, 1)
      ORDER BY o.id DESC
      LIMIT 10
     ) o LEFT JOIN
     order_record orr
     ON o.order_id = orr.order_id AND
        orr.is_delete IN (0, 1) LEFT JOIN
     pay p
     ON o.order_id = p.order_id AND
        p.is_delete = 0 
WHERE 1=1 AND o.is_delete IN (0, 1)
GROUP BY o.order_id
ORDER BY o.id DESC
LIMIT 0, 10

您错误地使用了 GROUP BY，因为 SELECT 中有未聚合的列，而 GROUP BY 中没有这些列。