MySQL 8.0 Reference Manual（读书笔记60节--Controlling the Query Optimi

勇者如龙

MySQL provides optimizer control through system variables that affect how query plans are evaluated, switchable optimizations, optimizer and index hints, and the optimizer cost model.
The server maintains histogram【ˈhɪstəɡræm 直方图;（统计学的）直方图，矩形图;】 statistics about column values in the column_statistics data dictionary table.Like other data dictionary tables, this table is not directly accessible by users. Instead, you can obtain histogram information by querying INFORMATION_SCHEMA.COLUMN_STATISTICS, which is implemented as a view on the data dictionary table. You can also perform histogram management using the ANALYZE TABLE statement.
1 Controlling Query Plan Evaluation

The task of the query optimizer is to find an optimal【ˈɑːptɪməl 最佳的;最优的;】 plan for executing an SQL query. Because the difference in performance between “good” and “bad” plans can be orders of magnitude【ˈmæɡnɪtuːd 巨大;震级;星等;重大;重要性;星的亮度;】 (that is, seconds versus【ˈvɜːrsəs (表示两队或双方对阵)对，诉，对抗;(比较两种不同想法、选择等)与…相对，与…相比;】 hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive【ɪɡˈzɔːstɪv 详尽的;全面的;彻底的;】 search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially【ˌɛkspoʊˈnɛnʃəli 以指数方式;】 with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
A more flexible【ˈfleksəbl 灵活的;柔韧的;有弹性的;可弯曲的;可变动的;能适应新情况的;】 method for query optimization enables the user to control how exhaustive【ɪɡˈzɔːstɪv 详尽的;全面的;彻底的;】 the optimizer is in its search for an optimal query evaluation plan. The general idea is that the fewer plans that are investigated by the optimizer, the less time it spends in compiling a query. On the other hand, because the optimizer skips some plans, it may miss finding an optimal plan.
The behavior of the optimizer with respect to the number of plans it evaluates can be controlled using two system variables:
• The optimizer_prune_level variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table. Our experience shows that this kind of “educated guess” rarely misses optimal plans, and may dramatically【drə'mætɪkli 显著地;戏剧性地;戏剧地;】 reduce query compilation【ˌkɑːmpɪˈleɪʃn 汇编;编写;收集;编纂;编著;选编;选辑;】 times. That is why this option is on (optimizer_prune_level=1) by default. However, if you believe that the optimizer missed a better query plan, this option can be switched off (optimizer_prune_level=0) with the risk that query compilation may take much longer. Note that, even with the use of this heuristic, the optimizer still explores a roughly exponential number of plans.
• The optimizer_search_depth variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further. Smaller values of optimizer_search_depth may result in orders of magnitude【ˈmæɡnɪtuːd 巨大;震级;星等;重大;重要性;星的亮度;】 smaller query compilation times. For example, queries with 12, 13, or more tables may easily require hours and even days to compile if optimizer_search_depth is close to the number of tables in the query. At the same time, if compiled with optimizer_search_depth equal to 3 or 4, the optimizer may compile in less than a minute for the same query. If you are unsure of what a reasonable value is for optimizer_search_depth, this variable can be set to 0 to tell the optimizer to determine the value automatically.
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:

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,index_merge_intersection=on,
5. engine_condition_pushdown=on,index_condition_pushdown=on,
6. mrr=on,mrr_cost_based=on,block_nested_loop=on,
7. batched_key_access=off,materialization=on,semijoin=on,
8. loosescan=on,firstmatch=on,duplicateweedout=on,
9. subquery_materialization_cost_based=on,
10. use_index_extensions=on,condition_fanout_filter=on,
11. derived_merge=on,use_invisible_indexes=off,skip_scan=on,
12. hash_join=on,subquery_to_derived=off,
13. prefer_ordering_index=on,hypergraph_optimizer=off,
14. derived_condition_pushdown=on
15. 1 row in set (0.00 sec)

复制代码

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.

 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.
• Block Nested-Loop Flags
  • block_nested_loop (default on)
  Controls use of BNL join algorithm. In MySQL 8.0.18 and later, this also controls use of hash joins, as do the BNL and NO_BNL optimizer hints. In MySQL 8.0.20 and later, block nested loop support is removed from the MySQL server, and this flag controls the use of hash joins only, as do the referenced optimizer hints.
• Condition Filtering Flags
  • condition_fanout_filter (default on)
  Controls use of condition filtering.
• Derived Condition Pushdown Flags
  • derived_condition_pushdown (default on)
  Controls derived condition pushdown.
• 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.
• Engine Condition Pushdown Flags
  • engine_condition_pushdown (default on)
  Controls engine condition pushdown.
• Hash Join Flags
  • hash_join (default on)
  Controls hash joins in MySQL 8.0.18 only, and has no effect in any subsequent version. In MySQL 8.0.19 and later, to control hash join usage, use the block_nested_loop flag, instead.
• Index Condition Pushdown Flags
  • index_condition_pushdown (default on)
  Controls index condition pushdown.
• Index Extensions Flags
  • use_index_extensions (default on)
  Controls 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.
• Index Visibility Flags
  • use_invisible_indexes (default off)
  Controls use of invisible【ɪnˈvɪzəbl 看不见的;无形的(与服务而非商品有关);隐形的;】 indexes.
• Limit Optimization Flags
  • prefer_ordering_index (default on)
  Controls whether, in the case of a query having an ORDER BY or GROUP BY with a LIMIT 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【kənˈsiːvəbl 可以想象的;可想象的;可信的;】 case (due in part to the assumption【əˈsʌmpʃn 假设;假定;担任;(责任的)承担;(权力的)获得;】 that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. Prior to MySQL 8.0.21, it was not possible to disable this optimization, but in MySQL 8.0.21 and later, while it remains the default behavior, it can be disabled by setting the prefer_ordering_index flag to off.
• 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.
• Semijoin Flags
  • 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).
  • semijoin (default on)
  Controls all semijoin strategies.
  In MySQL 8.0.17 and later, this also applies to the antijoin optimization.
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.
• Skip Scan Flags
  • skip_scan (default on)
  Controls use of Skip Scan access method.
• 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.
• Subquery Transformation Flags
  • subquery_to_derived (default off)
  Beginning with MySQL 8.0.21, the optimizer is able in many cases to transform a scalar【ˈskeɪlər 标量的;纯量的;无向量的;】 subquery in a SELECT, WHERE, JOIN, or HAVING clause into a left outer joins 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:

• The subquery does not make use of any nondeterministic functions, such as RAND().
  
• The subquery is not an ANY or ALL subquery which can be rewritten to use MIN() or MAX().
  
• The parent query does not set a user variable, since rewriting it may affect the order of execution, which could lead to unexpected results if the variable is accessed more than once in the same query.
  
• The subquery should not be correlated, that is, it should not reference a column from a table in the outer query, or contain an aggregate that is evaluated in the outer query.
  

Prior to MySQL 8.0.22, the subquery could not contain a GROUP BY clause.
This optimization can also be applied to a table subquery which is the argument to IN, NOT IN, EXISTS, or NOT EXISTS, that does not contain a GROUP BY.
The default value for this flag is off, 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 the subquery_to_derived flag to on. It is primarily intended for use in testing.
Example, using a scalar subquery:

1. mysql> CREATE TABLE t1(a INT);
2. mysql> CREATE TABLE t2(a INT);
3. mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);
4. mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);
5. mysql> SELECT * FROM t1
6. -> WHERE t1.a > (SELECT COUNT(a) FROM t2);
7. +------+
8. | a    |
9. +------+
10. | 3    |
11. | 4    |
12. +------+
13. mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
14. +-----------------------------------------------------+
15. | @@optimizer_switch LIKE '%subquery_to_derived=off%' |
16. +-----------------------------------------------------+
17. | 1                                                   |
18. +-----------------------------------------------------+
19. mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
20. *************************** 1. row ***************************
21. id: 1
22. select_type: PRIMARY
23. table: t1
24. partitions: NULL
25. type: ALL
26. possible_keys: NULL
27. key: NULL
28. key_len: NULL
29. ref: NULL
30. rows: 4
31. filtered: 33.33
32. Extra: Using where
33. *************************** 2. row ***************************
34. id: 2
35. select_type: SUBQUERY
36. table: t2
37. partitions: NULL
38. type: ALL
39. possible_keys: NULL
40. key: NULL
41. key_len: NULL
42. ref: NULL
43. rows: 2
44. filtered: 100.00
45. Extra: NULL
46. mysql> SET @@optimizer_switch='subquery_to_derived=on';
47. mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
48. +-----------------------------------------------------+
49. | @@optimizer_switch LIKE '%subquery_to_derived=off%' |
50. +-----------------------------------------------------+
51. | 0                                                   |
52. +-----------------------------------------------------+
53. mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';
54. +----------------------------------------------------+
55. | @@optimizer_switch LIKE '%subquery_to_derived=on%' |
56. +----------------------------------------------------+
57. | 1                                                  |
58. +----------------------------------------------------+

复制代码

1. mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
2. *************************** 1. row ***************************
3. id: 1
4. select_type: PRIMARY
5. table: <derived2>
6. partitions: NULL
7. type: ALL
8. possible_keys: NULL
9. key: NULL
10. key_len: NULL
11. ref: NULL
12. rows: 1
13. filtered: 100.00
14. Extra: NULL
15. *************************** 2. row ***************************
16. id: 1
17. select_type: PRIMARY
18. table: t1
19. partitions: NULL
20. type: ALL
21. possible_keys: NULL
22. key: NULL
23. key_len: NULL
24. ref: NULL
25. rows: 4
26. filtered: 33.33
27. Extra: Using where; Using join buffer (hash join)
28. *************************** 3. row ***************************
29. id: 2
30. select_type: DERIVED
31. table: t2
32. partitions: NULL
33. type: ALL
34. possible_keys: NULL
35. key: NULL
36. key_len: NULL
37. ref: NULL
38. rows: 2
39. filtered: 100.00
40. Extra: NULL

复制代码

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

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

复制代码

Example, using a query with IN (subquery):

1. mysql> DROP TABLE IF EXISTS t1, t2;
2. mysql> CREATE TABLE t1 (a INT, b INT);
3. mysql> CREATE TABLE t2 (a INT, b INT);
4. mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
5. mysql> INSERT INTO t2
6. -> VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);
7. mysql> SELECT * FROM t1
8. -> WHERE t1.b < 0
9. -> OR
10. -> t1.a IN (SELECT t2.a + 1 FROM t2);
11. +------+------+
12. | a    | b    |
13. +------+------+
14. | 2    | 20   |
15. | 3    | 30   |
16. +------+------+
17. mysql> SET @@optimizer_switch="subquery_to_derived=off";
18. mysql> EXPLAIN SELECT * FROM t1
19. -> WHERE t1.b < 0
20. -> OR
21. -> t1.a IN (SELECT t2.a + 1 FROM t2)\G
22. *************************** 1. row ***************************
23. id: 1
24. select_type: PRIMARY
25. table: t1
26. partitions: NULL
27. type: ALL
28. possible_keys: NULL
29. key: NULL
30. key_len: NULL
31. ref: NULL
32. rows: 3
33. filtered: 100.00
34. Extra: Using where
35. *************************** 2. row ***************************
36. id: 2
37. select_type: DEPENDENT SUBQUERY
38. table: t2
39. partitions: NULL
40. type: ALL
41. possible_keys: NULL
42. key: NULL
43. key_len: NULL
44. ref: NULL
45. rows: 6
46. filtered: 100.00
47. Extra: Using where
48. mysql> SET @@optimizer_switch="subquery_to_derived=on";
49. mysql> EXPLAIN SELECT * FROM t1
50. -> WHERE t1.b < 0
51. -> OR
52. -> t1.a IN (SELECT t2.a + 1 FROM t2)\G
53. *************************** 1. row ***************************
54. id: 1
55. select_type: PRIMARY
56. table: t1
57. partitions: NULL
58. type: ALL
59. possible_keys: NULL
60. key: NULL
61. key_len: NULL
62. ref: NULL
63. rows: 3
64. filtered: 100.00
65. Extra: NULL
66. *************************** 2. row ***************************
67. id: 1
68. select_type: PRIMARY
69. table: <derived2>
70. partitions: NULL
71. type: ref
72. possible_keys: <auto_key0>
73. key: <auto_key0>
74. key_len: 9
75. ref: std2.t1.a
76. rows: 2
77. filtered: 100.00
78. Extra: Using where; Using index
79. *************************** 3. row ***************************
80. id: 2
81. select_type: DERIVED
82. table: t2
83. partitions: NULL
84. type: ALL
85. possible_keys: NULL
86. key: NULL
87. key_len: NULL
88. ref: NULL
89. rows: 6
90. filtered: 100.00
91. Extra: Using temporary

复制代码

Checking and simplifying the result of SHOW WARNINGS after executing EXPLAIN on this query shows that, when the subquery_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:

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

复制代码

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

1. mysql> SELECT * FROM t1
2. -> WHERE t1.b < 0
3. -> OR
4. -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);
5. +------+------+
6. | a    | b    |
7. +------+------+
8. | 1    | 10   |
9. | 2    | 20   |
10. +------+------+
11. mysql> SET @@optimizer_switch="subquery_to_derived=off";
12. mysql> EXPLAIN SELECT * FROM t1
13. -> WHERE t1.b < 0
14. -> OR
15. -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
16. *************************** 1. row ***************************
17. id: 1
18. select_type: PRIMARY
19. table: t1
20. partitions: NULL
21. type: ALL
22. possible_keys: NULL
23. key: NULL
24. key_len: NULL
25. ref: NULL
26. rows: 3
27. filtered: 100.00
28. Extra: Using where
29. *************************** 2. row ***************************
30. id: 2
31. select_type: DEPENDENT SUBQUERY
32. table: t2
33. partitions: NULL
34. type: ALL
35. possible_keys: NULL
36. key: NULL
37. key_len: NULL
38. ref: NULL
39. rows: 6
40. filtered: 16.67
41. Extra: Using where
42. mysql> SET @@optimizer_switch="subquery_to_derived=on";
43. mysql> EXPLAIN SELECT * FROM t1
44. -> WHERE t1.b < 0
45. -> OR
46. -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
47. *************************** 1. row ***************************
48. id: 1
49. select_type: PRIMARY
50. table: t1
51. partitions: NULL
52. type: ALL
53. possible_keys: NULL
54. key: NULL
55. key_len: NULL
56. ref: NULL
57. rows: 3
58. filtered: 100.00
59. Extra: NULL
60. *************************** 2. row ***************************
61. id: 1
62. select_type: PRIMARY
63. table: <derived2>
64. partitions: NULL
65. type: ALL
66. possible_keys: NULL
67. key: NULL
68. key_len: NULL
69. ref: NULL
70. rows: 6
71. filtered: 100.00
72. Extra: Using where; Using join buffer (hash join)
73. *************************** 3. row ***************************
74. id: 2
75. select_type: DERIVED
76. table: t2
77. partitions: NULL
78. type: ALL
79. possible_keys: NULL
80. key: NULL
81. key_len: NULL
82. ref: NULL
83. rows: 6
84. filtered: 100.00
85. Extra: Using temporary

复制代码

If we execute SHOW WARNINGS after running EXPLAIN on the query SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) when subquery_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【rɪˈzemblz 像;看起来像;显得像;】 this:

1. SELECT a, b FROM t1
2. LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d
3. ON t1.a + 1 = d.e2
4. WHERE t1.b < 0
5. OR
6. d.e1 IS NOT NULL;

复制代码

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,index_merge_intersection=on,
5. engine_condition_pushdown=on,index_condition_pushdown=on,
6. mrr=on,mrr_cost_based=on,block_nested_loop=on,
7. batched_key_access=off,materialization=on,semijoin=on,
8. loosescan=on, firstmatch=on,
9. subquery_materialization_cost_based=on,
10. use_index_extensions=on,condition_fanout_filter=on,
11. derived_merge=on,use_invisible_indexes=off,skip_scan=on,
12. hash_join=on,subquery_to_derived=off,
13. prefer_ordering_index=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 can 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. mysql> SELECT @@optimizer_switch\G
3. *************************** 1. row ***************************
4. @@optimizer_switch: index_merge=on,index_merge_union=off,
5. index_merge_sort_union=off,index_merge_intersection=on,
6. engine_condition_pushdown=on,index_condition_pushdown=on,
7. mrr=on,mrr_cost_based=on,block_nested_loop=on,
8. batched_key_access=off,materialization=on,semijoin=on,
9. loosescan=on, firstmatch=on,
10. subquery_materialization_cost_based=on,
11. use_index_extensions=on,condition_fanout_filter=on,
12. derived_merge=on,use_invisible_indexes=off,skip_scan=on,
13. hash_join=on,subquery_to_derived=off,
14. prefer_ordering_index=on

复制代码

3 Optimizer Hints

One means of control over optimizer strategies is to set the optimizer_switch system variable.Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it is necessary to change optimizer_switch before each one.
Another way to control the optimizer is by using optimizer hints, which can be specified within individual statements. Because optimizer hints apply on a per-statement basis, they provide finer control over statement execution plans than can be achieved【əˈtʃiːvd (凭长期努力)达到(某目标、地位、标准);完成;成功;】 using optimizer_switch. For example, you can enable an optimization for one table in a statement and disable the optimization for a different table. Hints within a statement take precedence over optimizer_switch flags.
Examples:

1. SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
2. FROM t3 WHERE f1 > 30 AND f1 < 33;
3. SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
4. SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
5. SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
6. EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
7. SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
8. INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

复制代码

Optimizer and index hints may be used separately or together.
• Optimizer Hint Overview • Optimizer Hint Syntax • Join-Order Optimizer Hints • Table-Level Optimizer Hints • Index-Level Optimizer Hints • Subquery Optimizer Hints • Statement Execution Time Optimizer Hints • Variable-Setting Hint Syntax • Resource Group Hint Syntax • Optimizer Hints for Naming Query Blocks
3.1 Optimizer Hint Overview

Optimizer hints apply at different scope levels:
• Global: The hint affects the entire statement
• Query block: The hint affects a particular query block within a statement
• Table-level: The hint affects a particular table within a query block
• Index-level: The hint affects a particular index within a table
The following table summarizes the available optimizer hints, the optimizer strategies they affect, and the scope or scopes at which they apply. More details are given later.

Hint Name	Description	Applicable Scopes
BKA, NO_BKA	Affects Batched Key Access join processing	Query block, table
BNL, NO_BNL	Prior to MySQL 8.0.20: affects Block Nested-Loop join processing; MySQL 8.0.18 and later: also affects hash join optimization; MySQL 8.0.20 and later: affects hash join optimization only	Query block, table
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN	Use or ignore the derived condition pushdown optimization for materialized derived tables (Added in MySQL 8.0.22)	Query block, table
GROUP_INDEX, NO_GROUP_INDEX	Use or ignore the specified index or indexes for index scans in GROUP BY operations (Added in MySQL 8.0.20)	Index
HASH_JOIN, NO_HASH_JOIN	Affects Hash Join optimization (MySQL 8.0.18 only	Query block, table
INDEX, NO_INDEX	Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX, or as the combination of NO_JOIN_INDEX, NO_GROUP_INDEX, and NO_ORDER_INDEX (Added in MySQL 8.0.20)	Index
INDEX_MERGE, NO_INDEX_MERGE	Affects Index Merge optimization	Table, index
JOIN_FIXED_ORDER	Use table order specified in FROM clause for join order	Query block
JOIN_INDEX, NO_JOIN_INDEX	Use or ignore the specified index or indexes for any access method (Added in MySQL 8.0.20)	Index
JOIN_ORDER	Use table order specified in hint for join order	Query block
JOIN_PREFIX	Use table order specified in hint for first tables of join order	Query block
JOIN_SUFFIX	Use table order specified in hint for last tables of join order	Query block
MAX_EXECUTION_TIME	Limits statement execution time	Global
MERGE, NO_MERGE	Affects derived table/view merging into outer query block	Table
MRR, NO_MRR	Affects Multi-Range Read optimization	Table, index
NO_ICP	Affects Index Condition Pushdown optimization	Table, index
NO_RANGE_OPTIMIZATION	Affects range optimization	Table, index
ORDER_INDEX, NO_ORDER_INDEX	Use or ignore the specified index or indexes for sorting rows (Added in MySQL 8.0.20)	Index
QB_NAME	Assigns name to query block	Query block
RESOURCE_GROUP	Set resource group during statement execution	Global
SEMIJOIN, NO_SEMIJOIN	Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins	Query block
SKIP_SCAN, NO_SKIP_SCAN	Affects Skip Scan optimization	Table, index
SET_VAR	Set variable during statement execution	Global
SUBQUERY	Affects materialization, INto-EXISTS subquery strategies	Query block

Disabling an optimization prevents the optimizer from using it. Enabling an optimization means the optimizer is free to use the strategy if it applies to statement execution, not that the optimizer necessarily uses it.
 

来源:https://www.cnblogs.com/xuliuzai/p/18214415
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！