Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Usage specification recommendations
optimizer_switch is used to set the optimizer hint option switch.Required | Description |
Parameter Type | FLAGSET |
Default Value | index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=off,hash_join=on,subquery_to_derived=off,prefer_ordering_index=off,hypergraph_optimizer=off,derived_condition_pushdown=on,favor_range_scan=off,lazy_index_strategy=off,limit_cond_pushdown=on,pk_preload_pushdown=on,force_batched_key_access=on |
Value Range | index_merge: Set to on or off to enable or disable the index merge optimization.index_merge_union: Set to on or off to enable or disable the UNION optimization in index merge.index_merge_sort_union: Set to on or off to enable or disable the sort UNION optimization in index merge.index_merge_intersection: Set to on or off to enable or disable the intersection optimization in index merge.engine_condition_pushdown: Set to on or off to enable or disable the storage engine condition pushdown optimization.index_condition_pushdown: Set to on or off to enable or disable the index condition pushdown optimization.mrr: Set to on or off to enable or disable the multi-range read optimization.mrr_cost_based: Set to on or off to enable or disable the cost-based multi-range read optimization.block_nested_loop: Set to on or off to enable or disable the block nested-loop join optimization.batched_key_access: Set to on or off to enable or disable the batched key access optimization.materialization: Set to on or off to enable or disable the materialized subquery optimization.semijoin: Set to on or off to enable or disable the semi-join optimization.loosescan: Set to on or off to enable or disable the loose scan optimization.firstmatch: Set to on or off to enable or disable the first match optimization.duplicateweedout: Set to on or off to enable or disable the duplicate weedout optimization.subquery_materialization_cost_based: Set to on or off to enable or disable the cost-based subquery materialization optimization.use_index_extensions: Set to on or off to enable or disable the index extensions optimization.condition_fanout_filter: Set to on or off to enable or disable the condition fanout filter optimization. This optimization reduces the size of intermediate result sets during multi-table joins, thereby improving query performance.derived_merge: Set to on or off to enable or disable the derived table merge optimization. This optimization merges derived tables (subqueries) into the outer query, thereby avoiding the creation of temporary tables.use_invisible_indexes: Set to on or off to enable or disable the use of invisible indexes. Invisible indexes are ignored by the query optimizer but still exist in the table. Disabling this option ensures that the query optimizer does not use these indexes.skip_scan: Set to on or off to enable or disable the skip scan optimization. This optimization allows skipping unnecessary rows during index scans, thereby improving query performance.hash_join: Set to on or off to enable or disable the hash join optimization. Hash join is suitable for equi-joins and accelerates join operations through hash tables, typically delivering better performance for large-table joins or in no-index scenarios.subquery_to_derived: Set to on or off to enable or disable the subquery-to-derived-table optimization. This optimization converts certain subqueries into derived tables, allowing them to participate in more efficient join and optimization strategies.prefer_ordering_index: Set to on or off to enable or disable the prefer ordering index optimization. When a query contains an ORDER BY clause, the optimizer prioritizes using indexes to avoid sorting operations.hypergraph_optimizer: Set to on or off to enable or disable the hypergraph optimizer. The hypergraph optimizer is an experimental optimizer introduced in MySQL 8.0.22 for handling more complex multi-table join query plans.derived_condition_pushdown: Set to on or off to enable or disable the derived condition pushdown optimization. This optimization pushes down outer query conditions into derived tables to pre-filter data and reduce the volume of data in derived tables.favor_range_scan: Set to on or off to enable or disable the favor range scan optimization. This optimization prefers range scans over full table scans, even if the cost estimate for range scans is slightly higher, to enhance query stability.lazy_index_strategy: Set to on or off to enable or disable the lazy index strategy. The lazy index strategy delays the use of indexes until it is confirmed that they actually improve query performance.limit_cond_pushdown: Set to on or off to enable or disable the LIMIT condition pushdown optimization. This optimization pushes down LIMIT conditions into subqueries or views, thereby reducing the amount of data processed and improving query performance.pk_preload_pushdown: Set to on or off to enable or disable the primary key preloading pushdown optimization. This optimization pushes down primary key preloading operations to the storage engine layer, reducing the number of table lookups and improving primary key query performance.force_batched_key_access: Set to on or off to convert index-based joins to batched key access (bka) joins using batch RPC, reducing RPC data volume and improving performance.cost_based_hashjoin: Set to on or off to determine whether to incorporate the cost calculation of hash joins into the overall cost model. |
Effective Scope | GLOBAL SESSION |
Restart Required | No |
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan