Movatterモバイル変換

MySQL 9.4 Reference Manual

10.2.1.3 Index Merge Optimization

TheIndex Merge access method retrieves rows with multiplerange scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans.

Example queries for which Index Merge may be used:

SELECT * FROMtbl_name WHEREkey1 = 10 ORkey2 = 20;SELECT * FROMtbl_name  WHERE (key1 = 10 ORkey2 = 20) ANDnon_key = 30;SELECT * FROM t1, t2  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')  AND t2.key1 = t1.some_col;SELECT * FROM t1, t2  WHERE t1.key1 = 1  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

Note

The Index Merge optimization algorithm has the following known limitations:

If your query has a complexWHERE clause with deepAND/OR nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity transformations:
```
(x ANDy) ORz => (x ORz) AND (y ORz)(x ORy) ANDz => (x ANDz) OR (y ANDz)
```
Index Merge is not applicable to full-text indexes.

InEXPLAIN output, the Index Merge method appears asindex_merge in thetype column. In this case, thekey column contains a list of indexes used, andkey_len contains a list of the longest key parts for those indexes.

The Index Merge access method has several algorithms, which are displayed in theExtra field ofEXPLAIN output:

Using intersect(...)
Using union(...)
Using sort_union(...)

The following sections describe these algorithms in greater detail. The optimizer chooses between different possible Index Merge algorithms and other access methods based on cost estimates of the various available options.

Index Merge Intersection Access Algorithm

This access algorithm is applicable when aWHERE clause is converted to several range conditions on different keys combined withAND, and each condition is one of the following:

AnN-part expression of this form, where the index has exactlyN parts (that is, all index parts are covered):
```
key_part1 =const1 ANDkey_part2 =const2 ... ANDkey_partN =constN
```
Any range condition over the primary key of anInnoDB table.

Examples:

SELECT * FROMinnodb_table  WHEREprimary_key < 10 ANDkey_col1 = 20;SELECT * FROMtbl_name  WHEREkey1_part1 = 1 ANDkey1_part2 = 2 ANDkey2 = 2;

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.

If all columns used in the query are covered by the used indexes, full table rows are not retrieved (EXPLAIN output containsUsing index inExtra field in this case). Here is an example of such a query:

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

If the used indexes do not cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.

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

Index Merge Union Access Algorithm

The criteria for this algorithm are similar to those for the Index Merge intersection algorithm. The algorithm is applicable when the table'sWHERE clause is converted to several range conditions on different keys combined withOR, and each condition is one of the following:

AnN-part expression of this form, where the index has exactlyN parts (that is, all index parts are covered):
```
key_part1 =const1 ORkey_part2 =const2 ... ORkey_partN =constN
```
Any range condition over a primary key of anInnoDB table.
A condition for which the Index Merge intersection algorithm is applicable.

Examples:

SELECT * FROM t1  WHEREkey1 = 1 ORkey2 = 2 ORkey3 = 3;SELECT * FROMinnodb_table  WHERE (key1 = 1 ANDkey2 = 2)     OR (key3 = 'foo' ANDkey4 = 'bar') ANDkey5 = 5;

Index Merge Sort-Union Access Algorithm

This access algorithm is applicable when theWHERE clause is converted to several range conditions combined byOR, but the Index Merge union algorithm is not applicable.

Examples:

SELECT * FROMtbl_name  WHEREkey_col1 < 10 ORkey_col2 < 20;SELECT * FROMtbl_name  WHERE (key_col1 > 10 ORkey_col2 = 20) ANDnonkey_col = 30;

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

Influencing Index Merge Optimization

Use of Index Merge is subject to the value of theindex_merge,index_merge_intersection,index_merge_union, andindex_merge_sort_union flags of theoptimizer_switch system variable. SeeSection 10.9.2, “Switchable Optimizations”. By default, all those flags areon. To enable only certain algorithms, setindex_merge tooff, and enable only such of the others as should be permitted.

In addition to using theoptimizer_switch system variable to control optimizer use of the Index Merge algorithms session-wide, MySQL supports optimizer hints to influence the optimizer on a per-statement basis. SeeSection 10.9.3, “Optimizer Hints”.

PREV HOME UP NEXT