|
|
3.2 Optimizer Hint Syntax
Optimizer hints must be specified within /*+ ... */ comments. That is, optimizer hints use a variant of /* ... */ C-style comment syntax, with a + character following the /* comment opening sequence. Examples:- /*+ BKA(t1) */
- /*+ BNL(t1, t2) */
- /*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
- /*+ QB_NAME(qb2) */
The parser recognizes optimizer hint comments after the initial keyword of SELECT, UPDATE, INSERT, REPLACE, and DELETE statements. Hints are permitted in these contexts:
• At the beginning of query and data change statements:- SELECT /*+ ... */ ...
- INSERT /*+ ... */ ...
- REPLACE /*+ ... */ ...
- UPDATE /*+ ... */ ...
- DELETE /*+ ... */ ...
- (SELECT /*+ ... */ ... )
- (SELECT ... ) UNION (SELECT /*+ ... */ ... )
- (SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
- UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
- INSERT ... SELECT /*+ ... */ ...
- EXPLAIN SELECT /*+ ... */ ...
- EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
A hint comment may contain multiple hints, but a query block cannot contain multiple hint comments. This is valid【ˈvælɪd (法律上)有效的;合理的;(正式)认可的;有根据的;确凿的;符合逻辑的;系统认可的;】:- SELECT /*+ BNL(t1) BKA(t2) */ ...
- SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...
• Duplicate hints: For a hint such as /*+ MRR(idx1) MRR(idx1) */, MySQL uses the first hint and issues a warning about the duplicate hint.
• Conflicting hints: For a hint such as /*+ MRR(idx1) NO_MRR(idx1) */, MySQL uses the first hint and issues a warning about the second conflicting hint.
Query block names are identifiers and follow the usual rules about what names are valid and how to quote【kwoʊt 引用;】 them.
Hint names, query block names, and strategy names are not case-sensitive. References to table and index names follow the usual identifier case-sensitivity rules.
3.3 Join-Order Optimizer Hints
Join-order hints affect the order in which the optimizer joins tables.
Syntax of the JOIN_FIXED_ORDER hint:- hint_name([@query_block_name])
- hint_name([@query_block_name] tbl_name [, tbl_name] ...)
- hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)
• hint_name: These hint names are permitted:
- JOIN_FIXED_ORDER: Force the optimizer to join tables using the order in which they appear in the FROM clause. This is the same as specifying SELECT STRAIGHT_JOIN.
- JOIN_ORDER: Instruct the optimizer to join tables using the specified table order. The hint applies to the named tables. The optimizer may place tables that are not named anywhere in the join order, including between specified tables.
- JOIN_PREFIX: Instruct the optimizer to join tables using the specified table order for the first tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables after the named tables.
- JOIN_SUFFIX: Instruct the optimizer to join tables using the specified table order for the last tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables before the named tables.
• tbl_name: The name of a table used in the statement. A hint that names tables applies to all tables that it names. The JOIN_FIXED_ORDER hint names no tables and applies to all tables in the FROM clause of the query block in which it occurs.
If a table has an alias, hints must refer to the alias, not the table name.
Table names in hints cannot be qualified with schema names.
• query_block_name: The query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. For tbl_name@query_block_name syntax, the hint applies to the named table in the named query block.
Example:- SELECT
- /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
- JOIN_ORDER(t4@subq1, t3)
- JOIN_SUFFIX(t1) */
- COUNT(*) FROM t1 JOIN t2 JOIN t3
- WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
- AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
The optimizer resolves join-order hints according to these principles:
• Multiple hint instances
Only one JOIN_PREFIX and JOIN_SUFFIX hint of each type are applied. Any later hints of the same type are ignored with a warning. JOIN_ORDER can be specified several times.
Examples:- /*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */
- /*+ JOIN_PREFIX(t1) JOIN_SUFFIX(t2) */
- /*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */
• Conflicting hints
In some cases hints can conflict, such as when JOIN_ORDER and JOIN_PREFIX have table orders that are impossible to apply at the same time:- SELECT /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ... FROM t1, t2;
• Ignored hints
A hint is ignored if a table specified in the hint has a circular【ˈsɜːrkjələr 圆形的;圆的;环形的;环行的;绕圈的;循环论证的(以一种观点证明另一观点，接着再用后一种观点反过来去证明前一观点);大量送发的;】 dependency.
Example:- /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */
• Interaction with const tables
The MySQL optimizer places const tables first in the join order, and the position of a const table cannot be affected by hints. References to const tables in join-order hints are ignored, although the hint is still applicable. For example, these are equivalent:- JOIN_ORDER(t1, const_tbl, t2)
- JOIN_ORDER(t1, t2)
• Interaction with types of join operations
MySQL supports several type of joins: LEFT, RIGHT, INNER, CROSS, STRAIGHT_JOIN. A hint that conflicts with the specified type of join is ignored with no warning.
Example:- SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1;
3.4 Table-Level Optimizer Hints
Table-level hints affect:
• Use of the Block Nested-Loop (BNL) and Batched Key Access (BKA) join-processing algorithms.
• Whether derived tables, view references, or common table expressions should be merged into the outer query block, or materialized using an internal temporary table.
• Use of the derived table condition pushdown optimization (added in MySQL 8.0.22).
These hint types apply to specific tables, or all tables in a query block.
Syntax of table-level hints:- hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
- hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])
• hint_name: These hint names are permitted:
- BKA, NO_BKA: Enable or disable batched key access for the specified tables.
- BNL, NO_BNL: Enable or disable block nested loop for the specified tables. In MySQL 8.0.18 and later, these hints also enable and disable the hash join optimization.
- DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN: Enable or disable use of derived table condition pushdown for the specified tables (added in MySQL 8.0.22).
- HASH_JOIN, NO_HASH_JOIN: In MySQL 8.0.18 only, enable or disable use of a hash join for the specified tables. These hints have no effect in MySQL 8.0.19 or later, where you should use BNL or NO_BNL instead.
- MERGE, NO_MERGE: Enable merging for the specified tables, view references or common table expressions; or disable merging and use materialization instead.
【The block-nested loop optimization is removed in MySQL 8.0.20 and later releases, but BNL and NO_BNL continue to be supported for enabling and disabling hash joins.】
【To use a block nested loop or batched key access hint to enable join buffering for any inner table of an outer join, join buffering must be enabled for all inner tables of the outer join.】
• tbl_name: The name of a table used in the statement. The hint applies to all tables that it names. If the hint names no tables, it applies to all tables of the query block in which it occurs.
If a table has an alias, hints must refer to the alias, not the table name.
Table names in hints cannot be qualified with schema names.
• query_block_name: The query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. For tbl_name@query_block_name syntax, the hint applies to the named table in the named query block.
Examples:- SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
- SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
- SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
- SELECT /*+ BNL(t2) */ FROM t1, t2;
For the MERGE and NO_MERGE hints, these precedence【ˈpresɪdəns 优先;优先权;】 rules apply:
• A hint takes precedence over any optimizer heuristic【hjuˈrɪstɪk (教学或教育)启发式的;】 that is not a technical constraint. (If providing a hint as a suggestion has no effect, the optimizer has a reason for ignoring it.)
• A hint takes precedence over the derived_merge flag of the optimizer_switch system variable.
• For view references, an ALGORITHM={MERGE|TEMPTABLE} clause in the view definition takes precedence over a hint specified in the query referencing the view.
3.5 Index-Level Optimizer Hints
Index-level hints affect which index-processing strategies the optimizer uses for particular tables or indexes. These hint types affect use of Index Condition Pushdown (ICP), Multi-Range Read (MRR), Index Merge, and range optimizations.
Syntax of index-level hints:- hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
- hint_name(tbl_name@query_block_name [index_name [, index_name] ...])
• hint_name: These hint names are permitted:
- GROUP_INDEX, NO_GROUP_INDEX: Enable or disable the specified index or indexes for index scans for GROUP BY operations. Equivalent to the index hints FORCE INDEX FOR GROUP BY, IGNORE INDEX FOR GROUP BY. Available in MySQL 8.0.20 and later.
- INDEX, NO_INDEX: Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX, forcing the server to use the specified index or indexes for any and all scopes, or as the combination of NO_JOIN_INDEX, NO_GROUP_INDEX, and NO_ORDER_INDEX, which causes the server to ignore the specified index or indexes for any and all scopes. Equivalent to FORCE INDEX, IGNORE INDEX. Available beginning with MySQL 8.0.20.
- INDEX_MERGE, NO_INDEX_MERGE: Enable or disable the Index Merge access method for the specified table or indexes.These hints apply to all three Index Merge algorithms.The INDEX_MERGE hint forces the optimizer to use Index Merge for the specified table using the specified set of indexes. If no index is specified, the optimizer considers all possible index combinations and selects the least expensive one. The hint may be ignored if the index combination is inapplicable to the given statement. The NO_INDEX_MERGE hint disables Index Merge combinations that involve any of the specified indexes. If the hint specifies no indexes, Index Merge is not permitted for the table.
- JOIN_INDEX, NO_JOIN_INDEX: Forces MySQL to use or ignore the specified index or indexes for any access method, such as ref, range, index_merge, and so on. Equivalent to FORCE INDEX FOR JOIN, IGNORE INDEX FOR JOIN. Available in MySQL 8.0.20 and later.
- MRR, NO_MRR: Enable or disable MRR for the specified table or indexes. MRR hints apply only to InnoDB and MyISAM tables.
- NO_ICP: Disable ICP for the specified table or indexes. By default, ICP is a candidate optimization strategy, so there is no hint for enabling it.
- NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it. This hint may be useful when the number of ranges may be high and range optimization would require many resources.
- ORDER_INDEX, NO_ORDER_INDEX: Cause MySQL to use or to ignore the specified index or indexes for sorting rows. Equivalent to FORCE INDEX FOR ORDER BY, IGNORE INDEX FOR ORDER BY. Available beginning with MySQL 8.0.20.
- SKIP_SCAN, NO_SKIP_SCAN: Enable or disable the Skip Scan access method for the specified table or indexes. These hints are available as of MySQL 8.0.13. The SKIP_SCAN hint forces the optimizer to use Skip Scan for the specified table using the specified set of indexes. If no index is specified, the optimizer considers all possible indexes and selects the least expensive one. The hint may be ignored if the index is inapplicable to the given statement. The NO_SKIP_SCAN hint disables Skip Scan for the specified indexes. If the hint specifies no indexes, Skip Scan is not permitted for the table.
• tbl_name: The table to which the hint applies.
• index_name: The name of an index in the named table. The hint applies to all indexes that it names. If the hint names no indexes, it applies to all indexes in the table.
To refer to a primary key, use the name PRIMARY. To see the index names for a table, use SHOW INDEX.
• query_block_name: The query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. For tbl_name@query_block_name syntax, the hint applies to the named table in the named query block.
Examples:- SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1
- WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;
- SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
- SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
- FROM t3 WHERE f1 > 30 AND f1 < 33;
- INSERT INTO t3(f1, f2, f3)
- (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
- WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
- AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
- SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
- FROM t1 WHERE f2 > 40;
Assume that table t1 has columns a, b, c, and d; and that indexes named i_a, i_b, and i_c exist on a, b, and c, respectively:- SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
- WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;
- SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
- WHERE b = 1 AND c = 2 AND d = 3;
- /*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */
- /*+ NO_INDEX_MERGE(t1 i_a, i_b) INDEX_MERGE(t1 i_b) */
For the INDEX_MERGE and NO_INDEX_MERGE optimizer hints, these precedence rules apply:
• If an optimizer hint is specified and is applicable, it takes precedence over the Index Merge-related flags of the optimizer_switch system variable.- SET optimizer_switch='index_merge_intersection=off';
- SELECT /*+ INDEX_MERGE(t1 i_b, i_c) */ * FROM t1
- WHERE b = 1 AND c = 2 AND d = 3;
- SET optimizer_switch='index_merge_intersection=on';
- SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
- WHERE b = 1 AND c = 2 AND d = 3;
- SET optimizer_switch='index_merge_intersection=off';
- SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
- WHERE b = 1 AND c = 2 AND d = 3;
• The index-level optimizer hints GROUP_INDEX, INDEX, JOIN_INDEX, and ORDER_INDEX all take precedence over the equivalent FORCE INDEX hints; that is, they cause the FORCE INDEX hints to be ignored. Likewise, the NO_GROUP_INDEX, NO_INDEX, NO_JOIN_INDEX, and NO_ORDER_INDEX hints all take precedence over any IGNORE INDEX equivalents, also causing them to be ignored.
The index-level optimizer hints GROUP_INDEX, NO_GROUP_INDEX, INDEX,NO_INDEX, JOIN_INDEX,NO_JOIN_INDEX, ORDER_INDEX, and NO_ORDER_INDEX hints all take precedence over all other optimizer hints, including other index-level optimizer hints. Any other optimizer hints are applied only to the indexes permitted by these.
The GROUP_INDEX, INDEX, JOIN_INDEX, and ORDER_INDEX hints are all equivalent to FORCE INDEX and not to USE INDEX. This is because using one or more of these hints means that a table scan is used only if there is no way to use one of the named indexes to find rows in the table. To cause MySQL to use the same index or set of indexes as with a given instance of USE INDEX, you can use NO_INDEX, NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX, or some combination of these.
To replicate the effect that USE INDEX has in the query SELECT a,c FROM t1 USE INDEX FOR ORDER BY (i_a) ORDER BY a, you can use the NO_ORDER_INDEX optimizer hint to cover all indexes on the table except the one that is desired like this:- SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c
- FROM t1
- ORDER BY a;
- mysql> EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1
- -> USE INDEX FOR ORDER BY (i_a) ORDER BY a\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t1
- partitions: NULL
- type: ALL
- possible_keys: NULL
- key: NULL
- key_len: NULL
- ref: NULL
- rows: 256
- filtered: 100.00
- Extra: Using filesort
- /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a
- /*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_b
• If an IGNORE INDEX hint names multiple indexes, those indexes are unavailable for Index Merge.
• The FORCE INDEX and USE INDEX hints make only the named indexes to be available for Index Merge.- SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1
- FORCE INDEX (i_a, i_b) WHERE c = 'h' AND a = 2 AND b = 'b';
3.6 Subquery Optimizer Hints
Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization or IN-to-EXISTS transformations【ˌtrænsfərˈmeɪʃənz (彻底的)变化，改观，转变，改革;(用于南非)民主改革;】.
Syntax【ˈsɪntæks 句法;句法规则;语构;】 of hints that affect semijoin strategies:- hint_name([@query_block_name] [strategy [, strategy] ...])
• SEMIJOIN, NO_SEMIJOIN: Enable or disable the named semijoin strategies.
• strategy: A semijoin strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.
For SEMIJOIN hints, if no strategies are named, semijoin is used if possible based on the strategies enabled according to the optimizer_switch system variable. If strategies are named but inapplicable for the statement, DUPSWEEDOUT is used.
For NO_SEMIJOIN hints, if no strategies are named, semijoin is not used. If strategies are named that rule out【rule out 排除;使成为不可能;阻止…发生;确定…不可能（或不合适）;】 all applicable strategies for the statement, DUPSWEEDOUT is used.
If one subquery is nested within another and both are merged into a semijoin of an outer query, any specification of semijoin strategies for the innermost query are ignored. SEMIJOIN and NO_SEMIJOIN hints can still be used to enable or disable semijoin transformations for such nested subqueries.
If DUPSWEEDOUT is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic【hjuˈrɪstɪk (教学或教育)启发式的；】 pruning during greed【ˈɡriːdi 贪婪的;贪心的;贪吃的;渴望的;】y search, which can be avoided by setting optimizer_prune_level=0.
Examples:- SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
- WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
- SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
- WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
- SUBQUERY([@query_block_name] strategy)
For SUBQUERY hints, these strategy values are permitted: INTOEXISTS, MATERIALIZATION.
Examples:- SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
- SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);
If a hint comment contains multiple subquery hints, the first is used. If there are other following hints of that type, they produce a warning. Following hints of other types are silently ignored.
来源:https://www.cnblogs.com/xuliuzai/p/18216827
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
|
