Rechercher dans le manuel MySQL

8.2.1.4 Engine Condition Pushdown Optimization

Inhaltsverzeichnis

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.

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.

Suppose that an NDB Cluster table is defined as follows:

  1. CREATE TABLE t1 (
  2.     a INT,
  3.     b INT,
  4.     KEY(a)
  5. ) ENGINE=NDB;

Condition pushdown can be used with queries such as the one shown here, which includes a comparison between a nonindexed column and a constant:

  1. SELECT a, b FROM t1 WHERE b = 10;

The use of condition pushdown can be seen in the output of EXPLAIN:

  1. mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: ALL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: 10
  12.         Extra: Using where with pushed condition

However, condition pushdown cannot be used with either of these two queries:

  1. SELECT a,b FROM t1 WHERE a = 10;
  2. SELECT a,b FROM t1 WHERE b + 1 = 10;

Condition pushdown is not applicable to the first query because an index exists on column a. (An index access method would be more efficient and so would be chosen in preference to condition pushdown.) Condition pushdown cannot be employed for the second query because the comparison involving the nonindexed column b is indirect. (However, condition pushdown could be applied if you were to reduce b + 1 = 10 to b = 9 in the WHERE clause.)

Condition pushdown may also be employed when an indexed column is compared with a constant using a > or < operator:

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

Other supported comparisons for condition pushdown include the following:

  • column [NOT] LIKE pattern

    pattern must be a string literal containing the pattern to be matched; for syntax, see Section 12.5.1, “String Comparison Functions and Operators”.

  • column IS [NOT] NULL

  • column IN (value_list)

    Each item in the value_list must be a constant, literal value.

  • column BETWEEN constant1 AND constant2

    constant1 and constant2 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. For example, in a my.cnf file, use these lines: 

[mysqld]
optimizer_switch=engine_condition_pushdown=off

At runtime, disable condition pushdown like this:

  1. SET optimizer_switch='engine_condition_pushdown=off';

Limitations.  Engine condition pushdown is subject to the following limitations:

  • 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.

  • Columns used in comparisons cannot be of any of the BLOB or TEXT types.

  • A string value to be compared with a column must use the same collation as the column.

  • Joins are not directly supported; conditions involving multiple tables are pushed separately where possible. Use extended EXPLAIN output to determine which conditions are actually pushed down. See Section 8.8.3, “Extended EXPLAIN Output Format”.

Previously, 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. This reduces the number of rows which must be handled by the SQL node during join processing. Filtering can be also performed in parallel in the LDM threads, rather than in a single mysqld process. This has the potential to improve performance of queries by a significant margin.

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.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-condition-pushdown-optimization.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut