Movatterモバイル変換

batched_key_access (defaultoff)

Controls use of BKA join algorithm.

block_nested_loop (defaulton)

Controls use of hash joins, as do theBNL andNO_BNL optimizer hints.

condition_fanout_filter (defaulton)

Controls use of condition filtering.

derived_condition_pushdown (defaulton)

Controls derived condition pushdown.

derived_merge (defaulton)

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

engine_condition_pushdown (defaulton)

Controls engine condition pushdown.

hash_join (defaulton)

Has no effect in MySQL 9.3. Use theblock_nested_loop flag, instead.

index_condition_pushdown (defaulton)

Controls index condition pushdown.

use_index_extensions (defaulton)

Controls use of index extensions.

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.

use_invisible_indexes (defaultoff)

Controls use of invisible indexes.

prefer_ordering_index (defaulton)

Controls whether, in the case of a query having anORDER BY orGROUP BY with aLIMIT clause, the optimizer tries to use an ordered index instead of an unordered index, a filesort, or some other optimization. This optimization is performed by default whenever the optimizer determines that using it would allow for faster execution of the query.

Because the algorithm that makes this determination cannot handle every conceivable case (due in part to the assumption that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. This optimization can be disabled by setting theprefer_ordering_index flag tooff.

mrr (defaulton)

Controls the Multi-Range Read strategy.

mrr_cost_based (defaulton)

Controls use of cost-based MRR ifmrr=on.

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 forGROUP BY).

semijoin (defaulton)

Controls all semijoin strategies.

This also applies to the antijoin optimization.

hash_set_operations (defaulton)

Enables the hash table optimization for set operations involvingEXCEPT andINTERSECT); enabled by default. Otherwise, temporary table based de-duplication is used, as in previous versions of MySQL.

The amount of memory used for hashing by this optimization can be controlled using theset_operations_buffer_size system variable; increasing this generally results in faster execution times for statements using these operations.

skip_scan (defaulton)

Controls use of Skip Scan access method.

materialization (defaulton)

Controls materialization (including semijoin materialization).

subquery_materialization_cost_based (defaulton)

Use cost-based materialization choice.

subquery_to_derived (defaultoff)

The optimizer is able in many cases to transform a scalar subquery in aSELECT,WHERE,JOIN, orHAVING clause into a left outer join on a derived table. (Depending on the nullability of the derived table, this can sometimes be simplified further to an inner join.) This can be done for a subquery which meets the following conditions:

This optimization can also be applied to a table subquery which is the argument toIN,NOT IN,EXISTS, orNOT EXISTS, that does not contain aGROUP BY. It can also be applied for general quantified comparison predicates (comparisons withANY orALL) in theSELECT orWHERE clause in many cases; seeSection 10.2.2.6, “Optimizing ANY and ALL Subqueries”, for more information.

The default value for this flag isoff, since, in most cases, enabling this optimization does not produce any noticeable improvement in performance (and in many cases can even make queries run more slowly), but you can enable the optimization by setting thesubquery_to_derived flag toon. This may prove useful in certain cases when experiencing slow execution of subqueries.

Example, using a scalar subquery:

dmysql> CREATE TABLE t1(a INT);mysql> CREATE TABLE t2(a INT);mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);mysql> SELECT * FROM t1    ->     WHERE t1.a > (SELECT COUNT(a) FROM t2);+------+| a    |+------+|    3 ||    4 |+------+mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';+-----------------------------------------------------+| @@optimizer_switch LIKE '%subquery_to_derived=off%' |+-----------------------------------------------------+|                                                   1 |+-----------------------------------------------------+mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4     filtered: 33.33        Extra: Using where*************************** 2. row ***************************           id: 2  select_type: SUBQUERY        table: t2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 2     filtered: 100.00        Extra: NULLmysql> SET @@optimizer_switch='subquery_to_derived=on';mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';+-----------------------------------------------------+| @@optimizer_switch LIKE '%subquery_to_derived=off%' |+-----------------------------------------------------+|                                                   0 |+-----------------------------------------------------+mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';+----------------------------------------------------+| @@optimizer_switch LIKE '%subquery_to_derived=on%' |+----------------------------------------------------+|                                                  1 |+----------------------------------------------------+mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: NULL*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4     filtered: 33.33        Extra: Using where; Using join buffer (hash join)*************************** 3. row ***************************           id: 2  select_type: DERIVED        table: t2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 2     filtered: 100.00        Extra: NULL

As can be seen from executingSHOW WARNINGS immediately following the secondEXPLAIN statement, with the optimization enabled, the querySELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) is rewritten in a form similar to what is shown here:

SELECT t1.a FROM t1    JOIN  ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d            WHERE t1.a > d.c;

Example, using a query withIN (subquery):

mysql> DROP TABLE IF EXISTS t1, t2;mysql> CREATE TABLE t1 (a INT, b INT);mysql> CREATE TABLE t2 (a INT, b INT);mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);mysql> INSERT INTO t2    ->    VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);mysql> SELECT * FROM t1    ->     WHERE   t1.b < 0    ->             OR    ->             t1.a IN (SELECT t2.a + 1 FROM t2);+------+------+| a    | b    |+------+------+|    2 |   20 ||    3 |   30 |+------+------+mysql> SET @@optimizer_switch="subquery_to_derived=off";mysql> EXPLAIN SELECT * FROM t1    ->             WHERE   t1.b < 0    ->                     OR    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 3     filtered: 100.00        Extra: Using where*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 6     filtered: 100.00        Extra: Using wheremysql> SET @@optimizer_switch="subquery_to_derived=on";mysql> EXPLAIN SELECT * FROM t1    ->             WHERE   t1.b < 0    ->                     OR    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 3     filtered: 100.00        Extra: NULL*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>   partitions: NULL         type: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 9          ref: std2.t1.a         rows: 2     filtered: 100.00        Extra: Using where; Using index*************************** 3. row ***************************           id: 2  select_type: DERIVED        table: t2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 6     filtered: 100.00        Extra: Using temporary

Checking and simplifying the result ofSHOW WARNINGS after executingEXPLAIN on this query shows that, when thesubquery_to_derived flag enabled,SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) is rewritten in a form similar to what is shown here:

SELECT a, b FROM t1    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d    ON t1.a = d.e    WHERE   t1.b < 0            OR            d.e IS NOT NULL;

Example, using a query withEXISTS (subquery) and the same tables and data as in the previous example:

mysql> SELECT * FROM t1    ->     WHERE   t1.b < 0    ->             OR    ->             EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);+------+------+| a    | b    |+------+------+|    1 |   10 ||    2 |   20 |+------+------+mysql> SET @@optimizer_switch="subquery_to_derived=off";mysql> EXPLAIN SELECT * FROM t1    ->             WHERE   t1.b < 0    ->                     OR    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 3     filtered: 100.00        Extra: Using where*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 6     filtered: 16.67        Extra: Using wheremysql> SET @@optimizer_switch="subquery_to_derived=on";mysql> EXPLAIN SELECT * FROM t1    ->             WHERE   t1.b < 0    ->                     OR    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 3     filtered: 100.00        Extra: NULL*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 6     filtered: 100.00        Extra: Using where; Using join buffer (hash join)*************************** 3. row ***************************           id: 2  select_type: DERIVED        table: t2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 6     filtered: 100.00        Extra: Using temporary

If we executeSHOW WARNINGS after runningEXPLAIN on the querySELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) whensubquery_to_derived has been enabled, and simplify the second row of the result, we see that it has been rewritten in a form which resembles this:

SELECT a, b FROM t1LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) dON t1.a + 1 = d.e2WHERE   t1.b < 0        OR        d.e1 IS NOT NULL;

For more information, seeSection 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”, as well asSection 10.2.1.19, “LIMIT Query Optimization”, andOptimizing IN and EXISTS Subquery Predicates with Semijoin Transformations.