【MySQL精通之路】SQL优化(1)-查询优化(5)-引擎条件下推

1 介绍

这种优化提高了无索引列和常量之间直接比较的效率。

在这种情况下，条件会“向下推”到存储引擎进行评估。此优化只能由NDB存储引擎使用。

对于NDB集群，这种优化可以消除在集群的数据节点和发布查询的MySQL服务器之间通过网络发送不匹配行的操作，并且可以将使用条件下推的查询速度提高5到10倍。

假设NDB Cluster表定义如下：

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: 1select_type: SIMPLEtable: t1type: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 10Extra: Using where with pushed condition

但是，引擎条件下推不能与以下查询一起使用：

SELECT a,b FROM t1 WHERE a = 10;

引擎条件下推不适用于此处，因为列a上存在索引。（索引访问方法会更有效，因此会优先选择）

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

mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1type: range
possible_keys: akey: akey_len: 5ref: NULLrows: 2Extra: Using where with pushed condition

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

column [NOT] LIKE pattern

pattern必须是包含要匹配字符串文字；

有关语法，请参阅“字符串比较函数和运算符”。

column IS [NOT] NULL

column IN (value_list)

value_list中的每个项都必须是一个常量的文字值。

column BETWEEN constant1 AND constant2

constant1和constnt2必须分别为常量、文字值。

2.设置

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

默认情况下，引擎条件下推功能处于启用状态。

要在服务器启动时禁用它，请将optimizer_switch系统变量的engine_condition_pushdown标志设置为off。

例如，在my.cnf文件中，使用以下行：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

在运行时，禁用条件下推，如下所示：

SET optimizer_switch='engine_condition_pushdown=off';

3.局限性

引擎条件下推受以下限制：

仅NDB存储引擎支持引擎状态下推。

在NDB 8.0.18之前，仅支持列与计算结果为常数值的“常量或表达式”进行比较。

在NDB 8.0.18及更高版本中，只要列的类型完全相同，包括相同的符号、长度、字符集、精度和小数位数，就可以相互比较。

比较中使用的列不能是任何BLOB或TEXT类型。这种排除也扩展到JSON、BIT和ENUM列。

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

不直接支持联接；涉及多个表的条件查询在可能的情况下被单独推送。使用扩展的EXPLAIN输出来确定哪些条件实际被下推。

参见“扩展EXPLAIN输出格式”。

以前，引擎条件下推仅限于引用条件被推送到的同一表中的列值的术语。

从NDB 8.0.16开始，查询计划早期表中的列值也可以从推送条件中引用。这减少了SQL节点在联接处理过程中必须处理的行数。

筛选也可以在LDM线程中并行执行，而不是在单个mysqld进程中执行。

这有可能大大提高查询的性能。

从NDB 8.0.20开始，如果在同一联接嵌套中使用的任何表上，或在其上面的联接嵌套中的任何表（它所依赖的）上不存在不可推送条件，则可以使用扫描推送外部联接。半联接也是如此，前提是所采用的优化策略是firstMatch

（请参阅“使用半联接转换优化IN和EXISTS子查询谓词”）。

在以下两种情况下，联接算法不能与引用前一个表中的列相结合：

1.当前面引用的任何表都在联接缓冲区中时。在这种情况下，从扫描筛选表中检索的每一行都与缓冲区中的每一行都匹配。这意味着在生成扫描筛选器时，没有可以从中提取列值的特定行。

2.当列源自被推送的join操作的子操作时。这是因为生成扫描筛选器时，尚未检索联接中原始表操作引用的行。

从NDB 8.0.27开始，联接原始表中的列可以向下推，前提是它们满足前面列出的要求。使用先前创建的表t1的这种查询的示例如下所示：

mysql> EXPLAIN ->   SELECT * FROM t1 AS x ->   LEFT JOIN t1 AS y ->   ON x.a=0 AND y.b>=3\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: xpartitions: p0,p1type: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 4filtered: 100.00Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: ypartitions: p0,p1type: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 4filtered: 100.00Extra: Using where; Using pushed condition (`test`.`y`.`b` >= 3); Using join buffer (hash join)
2 rows in set, 2 warnings (0.00 sec)