This is the 25th day of my participation in the August Challenge

Index merge is an intelligent algorithm provided by mysql. Understanding index merge algorithm helps us to create index better.

Index merges retrieve rows by scanning multiple range types and merging their result sets. Only merge index scans from a single table, not across multiple tables. Merge results in three forms of underlying scanning: Unions, intersections, and Union-of-intersections.

The following four examples produce index merges:

1, SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_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 some_col = t1.some_col OR t2.key2 = t1.some_col2;Copy the code

Index merging has the following known limitations:

1. If the query contains a complex WHERE clause with heavy AND/OR nesting AND MySQL does not select the best plan, try the following conversion:

(x AND y) OR z => (x OR z) AND (y OR z) (x OR y) AND z => (x AND z) OR (y AND z)

Index merges do not apply to full-text indexes.

In the information output from the EXPLAIN statement, the index merge appears as “index_Merge” in the type column, in which case the key column contains the list of indexes used.

The index merge access method has several algorithms, as shown in the Extra field of the EXPLAIN statement output:

Using intersect(...)
Using union(...)
Using sort_union(...)
Copy the code

These algorithms are described in more detail below. The optimizer chooses between different index merging algorithms and other access methods based on cost estimates for the various options available.

Index Merge Intersection algorithm

The Index Merge Intersection algorithm performs a synchronous scan of all used indexes and generates the Intersection of the sequence of rows received from the merged Index scan.

This algorithm applies when the WHERE clause is converted to one of two range conditions on multiple different index keys that use AND concatenation:

Index (N>=1, N> 1, N> 1, N> 1);

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN.Copy the code

2. Any range conditions on primary keys of InnoDB tables.

Example:

1.SELECT * FROM innodb_table
WHERE primary_key < 10 AND key_col1 = 20;
2.SELECT * FROM tbl_name
WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
Copy the code

Index Merge Union algorithm

The Index Merge Intersection algorithm is similar to the Index Merge Intersection algorithm, which applies when the WHERE clause is converted to one of three scope conditions on multiple different Index keys that use OR joints:

Index (N>=1, N> 1, N> 1, N> 1);

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN.Copy the code

2. Any range conditions on primary keys of InnoDB tables.

Conforms to the conditions of Index Merge Intersection algorithm.

Example:

1.SELECT * FROM t1
WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
2.SELECT * FROM innodb_table
WHERE (key1 = 1 AND key2 = 2)
OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
Copy the code

Index Merge sort-union algorithm

This algorithm applies when the WHERE clause is converted to a range condition on multiple different Index keys using OR joins, but does not conform to the Index Merge Union algorithm. The Index Merge sort-union and Index Merge Union algorithms differ in that Index Merge sort-union must first get the row ids of all rows and Sort them before returning any rows.

Example:

1.SELECT * FROM tbl_name
WHERE key_col1 < 10 OR key_col2 < 20;
2.SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
Copy the code

For good suggestions, please enter your comments below.

Welcome to my blog guanchao.site