连接条件下推

前提条件

集群版本需为PolarDB MySQL版8.0版本且修订版本需为8.0.2.2.10或以上。如何查看集群版本，请参见查询版本号。

背景信息

Derived table（inline view）在复杂分析查询中被广泛使用，通过它可以简化SQL编写的复杂度，以更容易让用户理解的方式描述SQL所要表达的查询语义。在原生MySQL中，如果derived table无法被展开到上层查询（其中包括group by+ 聚集函数等运算）中，则只能通过物化的方式执行。如果SQL内部涉及的计算量很大，例如需要扫描大量表数据，那么执行的效率会非常低。通过将derived table外层的连接条件下推到derived table内部，使derived table内部的执行计划可以更加高效的利用索引，从而可以大幅的提升复杂查询的执行性能。

适用场景

• 在复杂查询中存在derived table和外层表的join，并且采用了nested loop join的方式。此外join列在derived table的内层，可以使用索引来加速对内层表的物化计算。同时需要有准确的统计信息支撑，确保下推后可以在内层过滤大量的数据。

• 当内层查询在连接条件下推后，如果可以有效利用索引，并过滤掉大量数据，则可能产生更高效的执行计划。连接条件下推功能会依赖于优化器的代价计算能力，来智能选择是否下推外层连接条件。

使用方法

您可以通过loose_join_predicate_pushdown_opt_mode参数开启连接条件下推功能。具体操作请参见设置集群参数和节点参数。

示例

原查询：

原查询中，由于对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;