8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.5 引擎条件下推优化

这种优化提高了在非索引列和常量之间进行直接比较的效率。在这种情况下，条件被“下推”到存储引擎上进行过滤。此优化仅适用于NDB存储引擎。

对于 NDB 集群，这种优化可以避免在集群的数据节点和发出查询的MySQL服务器之间通过网络发送不匹配行，比应该使用条件下推但是没有使用的，使用该技术能够提高查询速度5到10倍。

假设NDB集群表定义如下：

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

引擎条件下推可以用于下面这样的查询，它包括一个非索引列和一个常量之间的比较：

SELECT a, b FROM t1 
		WHERE b = 10;

在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  # 使用带被下推条件的where子句

但是，引擎条件下推在以下查询中不能使用：

SELECT a,b FROM t1 WHERE a = 10;

这是因为在a列有一个索引存在，所以引擎条件下推不适用。（索引访问方法会更有效，因此比条件下推优先选择。）

当使用 > 或 < 运算符对索引列与常量进行比较时，也可以使用引擎条件下推：

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

引擎条件下推支持的其他比较条件包括如下：

• column [NOT] LIKE pattern（译者：列 [NOT] LIKE模式）
  模式（Pattern）必须是包含要匹配模式的字符串字面值；有关语法，请参见12.8.1节“字符串比较函数和操作符”。

• column IS [NOT] NULL

• column IN (value_list)

  在值列表（value_list）中的每一项都必须是常量，字面值。

• column BETWEEN constant1 AND constant2

  constant1 and constant2每一个必须是常量，字面值。

在上述列表中的所有情况中，都可以将条件转换为列和常量之间一次或多次直接比较的形式。

引擎条件下推是默认启用的。要在服务器启动时禁用它，请将optimizer_switch系统变量的engine_condition_pushdown标志设置为off。例如，在my.cnf文件中，使用以下行：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

在运行时，像这样禁用条件下推:

SET optimizer_switch='engine_condition_pushdown=off';

局限性。引擎条件下推受到以下限制：

• 引擎条件下推只有NDB存储引擎支持。

• 在NDB 8.0.18之前，可以将列与常量或仅计算为常数值的表达式进行比较。在NDB 8.0.18及以后版本中，只要列的类型完全相同（包括相同的符号、长度、字符集、精度和比例），就可以相互比较。

• 用于比较的列不能是任何BLOB或TEXT类型。也不能是会扩展为JSON、BIT和ENUM的列。

• 要与列进行比较的字符串值必须使用与列相同的排序规则。

• 条件下推不直接支持连接；在可能的情况下，涉及多个表的条件被单独推送。使用扩展的EXPLAIN输出来确定哪些条件被实际下推。见第8.8.3节，“扩展EXPLAIN输出格式”。

以前，引擎条件下推仅限于引用同一个表和条件已被下推的列值。从NDB 8.0.16开始，查询计划中前面的表中的列值也可以从下推条件中引用。这减少了SQL节点在连接处理期间必须处理的行数。过滤也可以在LDM线程中并行执行，而不是在单个mysqld进程中。这有可能显著提高查询的性能。

从NDB 8.0.20开始，如果在同一个连接嵌套中所使用的任何表上都没有不可下推条件，或者在它所依赖的连接嵌套上面的任何表上没有不可下推条件，那么使用扫描的外部连接就可以被下推。对于半连接也是如此，前提是所采用的优化策略是第一次匹配（参见8.2.2.1节，“用半连接变换优化IN和EXISTS子查询谓词”）。