Rechercher dans le manuel MySQL
8.9.2 Switchable Optimizations
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:
- mysql> SELECT @@optimizer_switch\G
- *************************** 1. row ***************************
To change the value of
optimizer_switch
, assign a
value consisting of a comma-separated list of one or more
commands:
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 |
|
Set the named optimization to its default value |
|
Disable the named optimization |
|
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
(defaultoff
)Controls use of BKA join algorithm.
For
batched_key_access
to have any effect when set toon
, themrr
flag must also beon
. Currently, the cost estimation for MRR is too pessimistic. Hence, it is also necessary formrr_cost_based
to beoff
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
(defaulton
)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
(defaulton
)Controls use of condition filtering.
For more information, see Section 8.2.1.12, “Condition Filtering”.
Derived Table Merging Flags
derived_merge
(defaulton
)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, anALGORITHM
directive for a view takes precedence over thederived_merge
setting. By default, the flag ison
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
(defaulton
)Controls engine condition pushdown.
For more information, see Section 8.2.1.4, “Engine Condition Pushdown Optimization”.
Index Condition Pushdown Flags
index_condition_pushdown
(defaulton
)Controls index condition pushdown.
For more information, see Section 8.2.1.5, “Index Condition Pushdown Optimization”.
Index Extensions Flags
use_index_extensions
(defaulton
)Controls use of index extensions.
For more information, see Section 8.3.10, “Use of Index Extensions”.
Index Merge Flags
index_merge
(defaulton
)Controls all Index Merge optimizations.
index_merge_intersection
(defaulton
)Controls the Index Merge Intersection Access optimization.
index_merge_sort_union
(defaulton
)Controls the Index Merge Sort-Union Access optimization.
index_merge_union
(defaulton
)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
(defaultoff
)Controls use of invisible indexes.
For more information, see Section 8.3.12, “Invisible Indexes”.
Multi-Range Read Flags
mrr
(defaulton
)Controls the Multi-Range Read strategy.
mrr_cost_based
(defaulton
)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
(defaulton
)Controls use of Skip Scan access method.
For more information, see Skip Scan Range Access Method.
Semijoin Flags
semijoin
(defaulton
)Controls all semijoin strategies.
In MySQL 8.0.17 and later, this also applies to the antijoin optimization.
duplicateweedout
(defaulton
)Controls the semijoin Duplicate Weedout strategy.
firstmatch
(defaulton
)Controls the semijoin FirstMatch strategy.
loosescan
(defaulton
)Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for
GROUP BY
).
The
semijoin
,firstmatch
,loosescan
, andduplicateweedout
flags enable control over semijoin strategies. Thesemijoin
flag controls whether semijoins are used. If it is set toon
, thefirstmatch
andloosescan
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
andmaterialization
are bothon
, semijoins also use materialization where applicable. These flags areon
by default.For more information, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations”.
Subquery Materialization Flags
materialization
(defaulton
)Controls materialization (including semijoin materialization).
subquery_materialization_cost_based
(defaulton
)Use cost-based materialization choice.
The
materialization
flag controls whether subquery materialization is used. Ifsemijoin
andmaterialization
are bothon
, semijoins also use materialization where applicable. These flags areon
by default.The
subquery_materialization_cost_based
flag enables control over the choice between subquery materialization andIN
-to-EXISTS
subquery transformation. If the flag ison
(the default), the optimizer performs a cost-based choice between subquery materialization andIN
-to-EXISTS
subquery transformation if either method could be used. If the flag isoff
, the optimizer chooses subquery materialization overIN
-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:
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:
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-switchable-optimizations.html/
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.