连接条件下推示例

原查询中，由于对os这个derived table需要全量物化且没有高选择率的过滤条件，因此os表的物化要花费大量的时间，该查询的执行时间大约为65秒。

SELECT *
FROM (
  SELECT *
  FROM sample_table.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM sample_table.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN (
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM sample_table.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
  ) os
  ON od.id = os.detail_id;

在完成连接条件下推后，原本od表与os表的连接条件od.id = os.detail_id，被下推到os表内部。此时，os表可以更高效的利用detail_id的索引过滤大量的数据，达到更高的执行效率，该查询的执行时间大约为0.5秒。变换后的查询如下：

SELECT *
FROM (
  SELECT *
  FROM db_order.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM db_order.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN LATERAL((
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM db_order.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
      AND od.id = detail_id
  )) os;

相关文档

连接条件下推