About the index merge in MySQL

Index consolidation Optimization official website translation

MySQL5.7 Document Index Merging is intended to reduce several ranges (the range type in the type: range can used when a key column is compared to a constant using any of the =, & Lt;>,, >=, <, <=, is NULL, <=>, between, or in () operators:) scans the number of rows and merges their results into one. The merging here may be the Union of their union, intersection, or several intersections. Merging of such indexes can only merge index scans in one table, but not multiple range scans of the various table types.

In the output of explain, the index merge will appear in the Type column, shown as index merge . In this case, the key column contains a list of the merged indexes, and the Key_len column contains the portion of the longest key in those indexes.

For example:

There are several merging algorithms for the index merge method (see extra column for explain output)

• Using intersect (...)
• Using Union (...)
• Using sort_union (...)

These algorithms are described in more detail in the following subsections.

Attention

The index merge algorithm has the following known flaws

If your query uses a complex WHERE clause with multiple layers of and/or nesting, MySQL will not choose this optimization strategy, you can try to split them by using the rules:

An index merge cannot be applied to a full-text search. But future versions of MySQL may support

Is it the use of index merging optimizations or other optimization methods? MySQL depends on his cost estimation model.

1. Merging indexes with the intersection algorithm

This algorithm will be used when the WHERE clause can be converted to several permutations, using and to combine different keys, and each combination will have one of the following:

• In this form, the index must have the exact n part (that is, all the indexed columns are overwritten)

  Key_part1=const1 and Key_part2=const2 ... and KEY_PARTN=CONSTN

• InnoDB any scope condition of the primary key in the table

For example:

The intersection merge index algorithm scans all use indexes at the same time and outputs the intersection of (the merged index) (the sequence of rows scanned).

If all the columns to be queried are on the merged index, all the columns in the table are not scanned (that is, the index is overwritten, and the using index is displayed in the extra column in the explain output). Here is an example:

If you use an index that does not overwrite all of the query columns, the table is queried for the entire data column, depending on the criteria.

If one of the merged conditions is a condition over a primary key of a InnoDB table, it's not used for row retrieval, BU T is used to filter out rows retrieved using other conditions.

2. Merging indexes with the Union algorithm

This algorithm uses the same conditions as the intersection algorithm. The algorithm uses the WHERE clause to combine different keys with or, and each combination takes one of the following:

• In this form, the index must have the exact n part (that is, all the indexed columns are overwritten)

  Key_part1=const1 and Key_part2=const2 ... and KEY_PARTN=CONSTN

• InnoDB any scope condition of the primary key in the table

• The Intersect algorithm for index merging can be used as well.

For example:

3. Merging indexes with the sort_union algorithm

The algorithm will use or to arrange the conditions, but the union algorithm does not apply the case

For example:

The difference between the sort_union algorithm and the union algorithm is that the sort_union algorithm must obtain the row IDs of all rows before returning the results, and sort them.

Index merge. MD reference:

[1]:[HTTP://WWW.TUICOOL.COM/ARTICLES/EYURR3R]
[2]:[http://www.orczhou.com/index.php/2013/01/mysql-source-code-query-optimization-index-merge/]
[3]:[http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html]

About the index merge in MySQL