Rechercher dans le manuel MySQL

8.9.2 Switchable Optimizations

Inhaltsverzeichnis

The optimizer_switch system variable enables control over optimizer behavior. Its value is a set of flags, each of which has a value of on or off to indicate whether the corresponding optimizer behavior is enabled or disabled. This variable has global and session values and can be changed at runtime. The global default can be set at server startup.

To see the current set of optimizer flags, select the variable value:

  1. mysql> SELECT @@optimizer_switch\G
  2. *************************** 1. row ***************************
  3. @@optimizer_switch: index_merge=on,index_merge_union=on,
  4.                     index_merge_sort_union=on,
  5.                     index_merge_intersection=on,
  6.                     engine_condition_pushdown=on,
  7.                     index_condition_pushdown=on,
  8.                     mrr=on,mrr_cost_based=on,
  9.                     block_nested_loop=on,batched_key_access=off,
  10.                     materialization=on,semijoin=on,loosescan=on,
  11.                     firstmatch=on,duplicateweedout=on,
  12.                     subquery_materialization_cost_based=on,
  13.                     use_index_extensions=on,
  14.                     condition_fanout_filter=on,derived_merge=on,
  15.                     use_invisible_indexes=off,skip_scan=on

To change the value of optimizer_switch, assign a value consisting of a comma-separated list of one or more commands:

  1. SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

Each command value should have one of the forms shown in the following table.

Command Syntax Meaning
default Reset every optimization to its default value
opt_name=default Set the named optimization to its default value
opt_name=off Disable the named optimization
opt_name=on Enable the named optimization

The order of the commands in the value does not matter, although the default command is executed first if present. Setting an opt_name flag to default sets it to whichever of on or off is its default value. Specifying any given opt_name more than once in the value is not permitted and causes an error. Any errors in the value cause the assignment to fail with an error, leaving the value of optimizer_switch unchanged.

The following list describes the permissible opt_name flag names, grouped by optimization strategy:

  • Batched Key Access Flags

    • batched_key_access (default off)

      Controls use of BKA join algorithm.

    For batched_key_access to have any effect when set to on, the mrr flag must also be on. Currently, the cost estimation for MRR is too pessimistic. Hence, it is also necessary for mrr_cost_based to be off for BKA to be used.

    For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

  • Block Nested-Loop Flags

    • block_nested_loop (default on)

      Controls use of BNL join algorithm.

    For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

  • Condition Filtering Flags

    • condition_fanout_filter (default on)

      Controls use of condition filtering.

    For more information, see Section 8.2.1.12, “Condition Filtering”.

  • Derived Table Merging Flags

    • derived_merge (default on)

      Controls merging of derived tables and views into outer query block.

    The derived_merge flag controls whether the optimizer attempts to merge derived tables, view references, and common table expressions into the outer query block, assuming that no other rule prevents merging; for example, an ALGORITHM directive for a view takes precedence over the derived_merge setting. By default, the flag is on to enable merging.

    For more information, see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

  • Engine Condition Pushdown Flags

    • engine_condition_pushdown (default on)

      Controls engine condition pushdown.

    For more information, see Section 8.2.1.4, “Engine Condition Pushdown Optimization”.

  • Index Condition Pushdown Flags

    • index_condition_pushdown (default on)

      Controls index condition pushdown.

    For more information, see Section 8.2.1.5, “Index Condition Pushdown Optimization”.

  • Index Extensions Flags

    • use_index_extensions (default on)

      Controls use of index extensions.

    For more information, see Section 8.3.10, “Use of Index Extensions”.

  • Index Merge Flags

    • index_merge (default on)

      Controls all Index Merge optimizations.

    • index_merge_intersection (default on)

      Controls the Index Merge Intersection Access optimization.

    • index_merge_sort_union (default on)

      Controls the Index Merge Sort-Union Access optimization.

    • index_merge_union (default on)

      Controls the Index Merge Union Access optimization.

    For more information, see Section 8.2.1.3, “Index Merge Optimization”.

  • Index Visibility Flags

    • use_invisible_indexes (default off)

      Controls use of invisible indexes.

    For more information, see Section 8.3.12, “Invisible Indexes”.

  • Multi-Range Read Flags

    • mrr (default on)

      Controls the Multi-Range Read strategy.

    • mrr_cost_based (default on)

      Controls use of cost-based MRR if mrr=on.

    For more information, see Section 8.2.1.10, “Multi-Range Read Optimization”.

  • Skip Scan Flags

    • skip_scan (default on)

      Controls use of Skip Scan access method.

    For more information, see Skip Scan Range Access Method.

  • Semijoin Flags

    • semijoin (default on)

      Controls all semijoin strategies.

      In MySQL 8.0.17 and later, this also applies to the antijoin optimization.

    • duplicateweedout (default on)

      Controls the semijoin Duplicate Weedout strategy.

    • firstmatch (default on)

      Controls the semijoin FirstMatch strategy.

    • loosescan (default on)

      Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for GROUP BY).

    The semijoin, firstmatch, loosescan, and duplicateweedout flags enable control over semijoin strategies. The semijoin flag controls whether semijoins are used. If it is set to on, the firstmatch and loosescan flags enable finer control over the permitted semijoin strategies.

    If the duplicateweedout semijoin strategy is disabled, it is not used unless all other applicable strategies are also disabled.

    If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default.

    For more information, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations”.

  • Subquery Materialization Flags

    • materialization (default on)

      Controls materialization (including semijoin materialization).

    • subquery_materialization_cost_based (default on)

      Use cost-based materialization choice.

    The materialization flag controls whether subquery materialization is used. If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default.

    The subquery_materialization_cost_based flag enables control over the choice between subquery materialization and IN-to-EXISTS subquery transformation. If the flag is on (the default), the optimizer performs a cost-based choice between subquery materialization and IN-to-EXISTS subquery transformation if either method could be used. If the flag is off, the optimizer chooses subquery materialization over IN-to-EXISTS subquery transformation.

    For more information, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”.

When you assign a value to optimizer_switch, flags that are not mentioned keep their current values. This makes it possible to enable or disable specific optimizer behaviors in a single statement without affecting other behaviors. The statement does not depend on what other optimizer flags exist and what their values are. Suppose that all Index Merge optimizations are enabled:

  1. mysql> SELECT @@optimizer_switch\G
  2. *************************** 1. row ***************************
  3. @@optimizer_switch: index_merge=on,index_merge_union=on,
  4.                     index_merge_sort_union=on,
  5.                     index_merge_intersection=on,
  6.                     engine_condition_pushdown=on,
  7.                     index_condition_pushdown=on,
  8.                     mrr=on,mrr_cost_based=on,
  9.                     block_nested_loop=on,batched_key_access=off,
  10.                     materialization=on,semijoin=on,loosescan=on,
  11.                     firstmatch=on,
  12.                     subquery_materialization_cost_based=on,
  13.                     use_index_extensions=on,
  14.                     condition_fanout_filter=on

If the server is using the Index Merge Union or Index Merge Sort-Union access methods for certain queries and you want to check whether the optimizer will perform better without them, set the variable value like this:

  1. mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';
  2.  
  3. mysql> SELECT @@optimizer_switch\G
  4. *************************** 1. row ***************************
  5. @@optimizer_switch: index_merge=on,index_merge_union=off,
  6.                     index_merge_sort_union=off,
  7.                     index_merge_intersection=on,
  8.                     engine_condition_pushdown=on,
  9.                     index_condition_pushdown=on,
  10.                     mrr=on,mrr_cost_based=on,
  11.                     block_nested_loop=on,batched_key_access=off,
  12.                     materialization=on,semijoin=on,loosescan=on,
  13.                     firstmatch=on,
  14.                     subquery_materialization_cost_based=on,
  15.                     use_index_extensions=on,
  16.                     condition_fanout_filter=on

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-switchable-optimizations.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