8.2.1.5 Engine Condition Pushdown Optimization引擎条件下推优化

This optimization improves the efficiency of direct comparisons between a nonindexed column and a constant. 这种优化提高了非索引列和常量之间直接比较的效率。In such cases, the condition is “pushed down” to the storage engine for evaluation. 在这种情况下，条件被“下推”到存储引擎进行评估。This optimization can be used only by the NDB storage engine.此优化只能由NDB存储引擎使用。

For NDB Cluster, this optimization can eliminate the need to send nonmatching rows over the network between the cluster's data nodes and the MySQL server that issued the query, and can speed up queries where it is used by a factor of 5 to 10 times over cases where condition pushdown could be but is not used.对于NDB集群，这种优化可以消除在集群的数据节点和发出查询的MySQL服务器之间通过网络发送不匹配行的需要，并且可以在使用条件下推的情况下将查询速度提高5到10倍。

Suppose that an NDB Cluster table is defined as follows:假设NDB集群表定义如下：

CREATE TABLE t1 (
    a INT,
    b INT,
    KEY(a)
) ENGINE=NDB;

Engine condition pushdown can be used with queries such as the one shown here, which includes a comparison between a nonindexed column and a constant:引擎条件下推可用于此处所示的查询，其中包括非索引列和常量之间的比较：

SELECT a, b FROM t1 WHERE b = 10;

The use of engine condition pushdown can be seen in the output of EXPLAIN:引擎条件下推的使用可以在EXPLAIN的输出中看到：

mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where with pushed condition

However, engine condition pushdown cannot be used with the following query:但是，引擎条件下推不能用于以下查询：

SELECT a,b FROM t1 WHERE a = 10;

Engine condition pushdown is not applicable here because an index exists on column a. 引擎条件下推不适用于此处，因为a列上存在索引。(An index access method would be more efficient and so would be chosen in preference to condition pushdown.)（索引访问方法将更有效，因此将优先选择条件下推。）

Engine condition pushdown may also be employed when an indexed column is compared with a constant using a > or < operator:当使用>或<运算符将索引列与常数进行比较时，也可以使用引擎条件下推：

mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where with pushed condition

Other supported comparisons for engine condition pushdown include the following:其他支持的引擎条件下推比较包括：

In all of the cases in the preceding list, it is possible for the condition to be converted into the form of one or more direct comparisons between a column and a constant.在上述列表中的所有情况下，条件都可以转换为列和常量之间的一个或多个直接比较形式。

Engine condition pushdown is enabled by default. 默认情况下启用“引擎条件下推”。To disable it at server startup, set the optimizer_switch system variable's engine_condition_pushdown flag to off. 要在服务器启动时禁用它，请将optimizer_switch系统变量的engine_condition_pushdown标志设置为off。For example, in a my.cnf file, use these lines:例如，在my.cnf文件中，使用这些行：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

At runtime, disable condition pushdown like this:在运行时，禁用条件下推，如下所示：

SET optimizer_switch='engine_condition_pushdown=off';

Limitations.局限性  Engine condition pushdown is subject to the following limitations:引擎条件下推受到以下限制：

Previously, engine condition pushdown was limited to terms referring to column values from the same table to which the condition was being pushed. 以前，引擎条件下推仅限于引用条件下推到的同一表格中的列值的术语。Beginning with NDB 8.0.16, column values from tables earlier in the query plan can also be referred to from pushed conditions. 从NDB 8.0.16开始，查询计划中前面表中的列值也可以从推式条件中引用。This reduces the number of rows which must be handled by the SQL node during join processing. 这减少了SQL节点在连接处理期间必须处理的行数。Filtering can be also performed in parallel in the LDM threads, rather than in a single mysqld process. 过滤也可以在LDM线程中并行执行，而不是在单个mysqld进程中执行。This has the potential to improve performance of queries by a significant margin.这有可能大大提高查询的性能。

Beginning with NDB 8.0.20, an outer join using a scan can be pushed if there are no unpushable conditions on any table used in the same join nest, or on any table in join nmests above it on which it depends. 从NDB 8.0.20开始，如果在同一联接嵌套中使用的任何表上，或者在其依赖的联接nmests上的任何表上，没有不可推送的条件，则可以推送使用扫描的外部联接。This is also true for a semijoin, provided the optimization strategy employed is firstMatch (see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”).如果采用的优化策略是firstMatch（请参阅第8.2.2.1节，“使用半联接转换优化IN和EXISTS子查询谓词”），则半联接也是如此。

Join algorithms cannot be combined with referring columns from previous tables in the following two situations:在以下两种情况下，联接算法不能与前面表中的引用列组合使用：

  1. When any of the referred previous tables are in a join buffer. 当前面引用的任何表位于联接缓冲区中时。In this case, each row retrieved from the scan-filtered table is matched against every row in the buffer. 在这种情况下，从扫描筛选表检索到的每一行都与缓冲区中的每一行相匹配。This means that there is no single specific row from which column values can be fetched from when generating the scan filter.这意味着，在生成扫描筛选器时，不存在可从中获取列值的单个特定行。

  2. When the column originates from a child operation in a pushed join. 当列源自推式联接中的子操作时。This is because rows referenced from ancestor operations in the join have not yet been retrieved when the scan filter is generated.这是因为在生成扫描筛选器时，尚未检索从联接中的祖先操作引用的行。