这种优化提高了非索引列和常量之间直接比较的效率。在这种情况下,条件被“下推”到存储引擎进行评估。此优化只能由NDB
存储引擎使用。
对于 NDB Cluster,这种优化可以消除在集群的数据节点和发出查询的 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: 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
但是,引擎条件下推不能 与这两个查询中的任何一个一起使用:
SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;
引擎条件下推不适用于第一个查询,因为列上存在索引a
。(索引访问方法会更有效,因此优先选择条件下推。)引擎条件下推不能用于第二个查询,因为涉及非索引列的比较
b
是间接的。(但是,如果您要在
条款中减少b + 1
= 10
到,则可以应用引擎条件下推。)
b = 9
WHERE
>
当使用or运算符
将索引列与常量进行比较时,也可以使用引擎条件下推
<
:
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:
column
[NOT] LIKEpattern
pattern
must be a string literal containing the pattern to be matched; for syntax, see Section 12.8.1, “String Comparison Functions and Operators”.column
IS [NOT] NULLcolumn
IN (value_list
)Each item in the
value_list
must be a constant, literal value.column
BETWEENconstant1
ANDconstant2
constant1
andconstant2
must each be a constant, literal value.
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
. For example, in a
my.cnf
file, use these lines:
[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:
Engine condition pushdown is supported only by the
NDB
storage engine.Columns may be compared with constants only; however, this includes expressions which evaluate to constant values.
要与列进行比较的字符串值必须使用与该列相同的排序规则。
不直接支持连接;涉及多个表的条件在可能的情况下被单独推送。使用扩展
EXPLAIN
输出来确定实际下推哪些条件。请参阅 第 8.8.3 节,“扩展 EXPLAIN 输出格式”。