MySQL Optimizer function switch optimizer_switch

MySQL 8.0 new Features

Use_invisible_indexes: Whether or not to use an invisible index, MySQL 8.0 adds the ability to create a invisible index, which controls whether the optimizer uses the invisible Index, on which is considered for use.

MySQL 5.7 New

Derived_merge: Derived table merge, similar to Oracle view merging, cannot expand Union, GROUP, DISTINCT, limit, and aggregate operations when the following operations exist in derived SQL

Duplicateweedout: Whether to use a temporary table to semi-join the resulting set of results

The Condition_fanout_filter:cost model considers the condition in the Jion cost calculation, and whether or not to consider the filter on the condition, if it is on, consider

MySQL 5.6 NEW

MRR and mrr_cost_based: For a multicolumn index, also called a composite index to do a basic scan, and then sort the matching records by primary key, so that all records needed are scanned from disk in an ordered primary key order. The fundamental function is to convert random scans of disks into sequential scans.

Batched_key_access: For multi-table join statements, when MySQL accesses the second join table using an index, a join buffer is used to collect the related column values generated by the first action object. BKA build the key, batch to the engine layer to do index lookup. Key is submitted to the engine via the MRR interface. In this way, MRR makes the query more efficient.

Block_nested_loop: The row/result set of the outer loop is stored in the join buffer, and each row in the inner loop is compared to the record in the entire buffer, reducing the number of inner loops.

Index_condition_pushdown: When the ICP is open, range, ref, EQ_REF, or ref_or_null scan for level two indexes, if some of the where conditions can use indexed fields, MySQL The server pushes this part down to the engine layer and can use the Where condition of the index filter to filter the data at the storage engine layer.

Use_index_extensions: The index extension is used primarily for the second index of InnoDB, which is the normal index, which uses the primary key values contained in the index. For example, the primary key is (A, b) and the index is (c). If index c is used, then the index becomes (c,a,b) so that a new composite index can be used. However, this kind of occasions use less, generally according to the combination of the first field in the primary key and the normal index to do the search together.

Semijoin: Enable Semijoin,mysql mainly supports the following five kinds of semi-connection policies

Duplicateweedout: Use temporary tables to semi-join the resulting set of results.

Firstmatch: Select only the 1th record that matches the appearance of the inner table.

Loosescan: The inner-table data is grouped based on the index, and the first data of each group is matched.

Materializelookup: The inner-table is solidified into a temporary table, traversing the outer-table, and then looking for a match on the curing table.

Materializescan: Inner-table is solidified into a temporary table, traversing the curing table, and then looking for a match on the outer-table.

Firstmatch: Selects only the first record that matches the appearance of the inner table.

Loosescan: The data of the inner table is grouped according to the index, and the first data of each group can be taken.

Materialization and subquery_materialization_cost_based: Take the inner table and then generate a temporary table with corresponding indexes (a bit like materialized views in other data), and then traverse the temporary table through the corresponding key values of the exterior.

MySQL version 5.5 and below new

Engine_condition_pushdown: Only for the NDB engine, after opening the data is sent to the SQL node according to the Where condition filtered, the data that does not open all data nodes is sent to the SQL node to handle.

Index_merge

Index_merge_intersection: If there are two separate indexes available, but none of them is optimized, then the optimizer chooses to merge two indexes and make an intersection between their result sets, then match the disk data based on this intersection.

Index_merge_union: For or, connect all relevant indexes, find the corresponding rowid of the record, and obtain the data on the disk according to ROWID.

Index_merge_sort_union "for or, connect all relevant indexes, find the corresponding rowid in the record, and get the data on the disk according to ROWID.

MySQL Optimizer function switch optimizer_switch