mysql - 加快 MySQL SUM + JOINS 的速度

Question

我有一个半复杂的 JOIN 结构，用于根据订单从我的 MySQL 数据库中获取特定信息。首先让我给你 SQL 查询；

SELECT
    #orders.*,
    orders.id,
    customers.lastname,
    customers.priority,
    shipments.status,
    pmv_sl.productmodelvariant_id,
    pmv_sl.nr AS pmv_nr,
    COALESCE(SUM(orderlines.nr), 0) AS orderlines_nr_count,
    COALESCE(SUM(shipment_lines.nr), 0) AS shipment_lines_nr_count
FROM orders
    INNER JOIN  customers       ON customers.id = orders.customer_id
    INNER JOIN  platforms       ON platforms.id = orders.platform_id
    INNER JOIN  stocklocations  ON stocklocations.id = platforms.stocklocation_id
    LEFT JOIN   orderlines      ON orderlines.order_id = orders.id
    LEFT JOIN   shipments       ON shipments.order_id = orders.id
    LEFT JOIN   shipment_lines  ON shipment_lines.shipment_id = shipments.id

    # This JOIN, together with shipment_lines, makes the query suddenly very slow (probably because of all the SUM()'s)
    LEFT JOIN   productmodelvariants_stocklocations pmv_sl ON
        pmv_sl.productmodelvariant_id = orderlines.productmodelvariant_id
        AND pmv_sl.stocklocation_id = stocklocations.id
WHERE
    orders.orderstatus_id = 2
    AND orders.order_all_to_shipment = 0
GROUP BY
    orders.id

问题

所以我计算了这个查询的 4 个速度(在所有情况下都是 450 个结果)；

1. 没有 LEFT JOIN shipment_lines 并且没有 LEFT JOIN productmodelvariants_stocklocations
2. 仅使用 LEFT JOIN shipment_lines
3. 仅使用 LEFT JOIN productmodelvariants_stocklocations
4. 同时使用 2. 和 3.

这是结果速度；

1. 0.146 秒
2. 1.975 秒
3. 0.234 秒
4. 4.619 秒

表格中的行

• orderlines 中的行数:24528
• shipment_lines 中的行:6345
• productmodelvariants_stocklocations 中的行:1819

结论

如您所见，2. 和 3.(在 4. 中)都加入了比赛； SQL 开始出汗。

我想知道是否有比我了解更多 (My)SQL 知识的人知道改变查询上下文以稍微提高它的方法。

我还注意到，在执行 4. 并引用 COALESCE(SUM() 时，我很容易获得 0.8 到 1.0 秒。

更新

解释扩展

Answer 1

您正在通过未能在 shipments.status 上指定连接子句来创建交叉连接

尝试像这样重新组织您的查询:

SELECT A.*, 
    orders_id,
    customers.lastname,
    customers.priority,
#    shipments.status,
    pmv_sl.productmodelvariant_id,
    pmv_sl.nr AS pmv_nr,
    orderlines_nr_count,
    shipment_lines_nr_count
FROM (
  SELECT
      #orders.*,
      orders.id AS orders_id,
#      shipments.status,
      COALESCE(SUM(orderlines.nr), 0) AS orderlines_nr_count,
      COALESCE(SUM(shipment_lines.nr), 0) AS shipment_lines_nr_count
  FROM orders
      LEFT JOIN   orderlines      ON orderlines.order_id = orders.id
      LEFT JOIN   shipments       ON shipments.order_id = orders.id
      LEFT JOIN   shipment_lines  ON shipment_lines.shipment_id = shipments.id

  WHERE orders.orderstatus_id = 2
   AND orders.order_all_to_shipment = 0
  GROUP BY 
      orders.id,
      orderlines.productmodelvariant_id,
      stocklocations.id
) orders
    INNER JOIN  customers       ON customers.id = orders.customer_id
    INNER JOIN  platforms       ON platforms.id = orders.platform_id
    INNER JOIN  stocklocations  ON stocklocations.id = platforms.stocklocation_id
    LEFT JOIN   productmodelvariants_stocklocations pmv_sl ON
      pmv_sl.productmodelvariant_id = orderlines.productmodelvariant_id
      AND pmv_sl.stocklocation_id = stocklocations.id
GROUP BY
    orders.id

然后找出装运状态的去向。