MySQL Optimization Index Merge (indexed merging)

An in-depth understanding of index merge is one of the important foundations for optimizing using indexes. Having understood the index merge technology, we know how to index the table.

1. Why is there an index merge

There may be multiple conditions (or joins) in our where that involve multiple fields, with and or or, then it is possible to use the index merge technique at this point. The index merge technique, if simply stated, is to perform conditional scans on multiple indexes and then merge their respective results (intersect/union).

Before MySQL5.0, a table can use only one index at a time and cannot use multiple indexes to perform conditional scans at the same time. However, starting from 5.1, the index merge optimization technique was introduced, and the same table could be conditionally scanned using multiple indexes.

Related documents: http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html (note that there are several errors in the document)

The Index Merge method is used to retrieve rows with several scans and to range Merge their results into one< /c1>. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges the index scans from a single table, it does not the merge scans acrossmultiple tables.

EXPLAINin output, the Index Merge method appears as in the index_merge type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for thos e indexes.

Index Merge: A range scan of multiple indexes of the same table can merge the results into three types: union, intersection, and their combination (first internal intersect and then outside union).

There are four examples of official documentation:

SELECT * from tbl_name WHERE key1 = ten OR key2 = 20; SELECT * from Tbl_name WHERE (Key1 = ten OR Key2 =) and non_key=30; SELECT * from T1, T2 WHERE (T1.key1 in (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);

But for the fourth example, it doesn't feel like using index merge. because the or operation is performed between T2.key1=t1.some_col and T2.key2=t1.some_col2, and T2.key2 is the second field (not the first field) of the composite Index. So: T2.key2 = T1.some_col2 does not work with composite indexes . (The document should be wrong here.)

The index merge algorithm is divided into three different methods according to the merging algorithm: Intersect, union, Sort_union.

2. Intersect of index Merge

In simple terms, the index intersect merge is the result of multiple index condition scans for intersection operations. It is obvious that the index intersect merge appears when there is an and operation between multiple index commits. The following two where conditions, or their combination, will be the index intersect merge:

1) conditions used in all fields in the composite index or left prefix fields (also applicable for single-field indexes)

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

2) any scope condition on the primary key

Example:

SELECT * from innodb_table WHERE Primary_key < key_col1=20; SELECT * from Tbl_name WHERE (Key1_part1=1 and key1_part2=2) and key2=2;

This is only a composite index, but the single-field index is obviously the same . For example , the SELECT * from tab where KEY1=XX and Key2 =xxx is also possible with the index intersect merge. In addition, the combination of the above two cases may also be the same as the index intersect merge.

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of RO W sequences that it receives from the merged index scans. (Intersect merge Run mode: Multiple indexes are scanned at the same time, then the result is intersected)

If all columns used in the query is covered by the used indexes, full table rows is not retrieved ( EXPLAIN output contains In field in the This case Using index Extra ). Here's an example of such a query: (Index overlay Scan, no need to return table)

SELECT COUNT (*) from T1 WHERE Key1=1 and key2=1;

If The used indexes don't cover all columns used in the query, full rows is retrieved only when the range conditions for All used keys is satisfied. (The index cannot be overwritten, then the table is returned to the condition that meets 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, but is used to filter out rows retrieved using other conditions.

3. Union of Index Merge

In simple terms, the index uion merge is a multiple index condition scan, and the resulting result is set to perform a bitwise operation, apparently between multiple conditions.

The following types of where conditions, and their combinations, may be used to the index Union merge algorithm:

1) conditions used in all fields in the composite index or left prefix fields (also applicable for single-field indexes)

2) any scope condition on the primary key

3) any where conditions that conform to the index intersect merge;

The index Union merge algorithm may be used when the OR operation is performed on the above three where conditions.

Example:

SELECT * from T1 WHERE key1=1 or key2=2 or key3=3; SELECT * from Innodb_table WHERE (Key1=1 and key2=2) OR (key3= ' foo ' and key4= ' Bar ') and key5=5;

The first example is that three single-field indexes perform OR operations, so they may use the index Union merge algorithm.

The second example is a bit more complicated. (Key1=1 and key2=2) is compliant with the index intersect merge; (key3= ' foo ' and key4= ' bar ') And key5=5 also conform to the index intersect merge, so an OR operation between the two may naturally use the Index Union merge algorithm.

4. Sort_union of index Merge

This access algorithm are employed when WHERE the clause were converted to several range conditions combined OR by, but F or which the Index Merge method Union algorithm is not applicable. (The sort_union algorithm may be used when multiple conditional scans do OR, but do not conform to the index Union merge algorithm)

There are two examples of official documentation:

SELECT * from Tbl_name WHERE Key_col1 < ten OR key_col2 < 20; SELECT * from Tbl_name WHERE (Key_col1 > Ten OR key_col2 =) and nonkey_col=30;

But obviously: because Key_col2 is not the first field of a composite index, it is not possible to use an OR operation on the index. So these two examples should also be wrong, they don't actually do the index sort_union merge algorithm.

The difference between the sort-union algorithm and the union algorithm are that the sort-union algorithm must first fetch Row IDs for all rows and the sort them before returning any rows. (The difference between the sort-union merge algorithm and the union merge algorithm is whether the order is sorted before the result is returned, and why does it need to be sorted?) Probably because two result sets, do the set operation, need to go to heavy, so only then to sort??? )

5. Limitations of index Merge

1) If your query have a complex WHERE clause with deep AND / OR nesting and MySQL does not choose the optimal plan, try D Istributing terms using the following identity laws:

(X and Y) or z = (x or z) and (Y or Z) (x or y) and z = (x and z) OR (Y and Z)

If our conditions are more complex, with multiple and/or conditional operations, and MySQL does not use the optimal execution plan, then you can use the two equations above to convert the conditions.

2) Index Merge is not applicable to Full-text indexes. We plan to extend it to cover these in a future MySQL release. (Full-text index does not have index merge)

3) before MySQL 5.6.6, if a range scan is possible on some key, the optimizer would not consider using Index Merge Union or Index Merge sort-union algorithms. For example, consider this query:

SELECT * from T1 WHERE (Goodkey1 < ten OR Goodkey2 <) and Badkey < 30;

For this query, the plans is possible:

An Index Merge scan using the (goodkey1 < 10 OR goodkey2 < 20) condition.

A range Scan using the badkey < 30 condition.

However, the optimizer considers only the second plan.

This is a big drawback to the low version of MySQL. That is, if there are conditions in the Where condition, <, >=, <=, and so on, the optimizer does not use the index merge, and it ignores the other indexes and does not use them, even if their selectivity is better.

6. Further optimization of the index merge

The index merge allows us to use multiple indexes to scan at the same time and then merge the results. Sounds like a good feature, but if the index intersect merge is present, it also means that our index is not well established because the index intersect merge can be further optimized by building a composite index.

For example, the following select:

SELECT * from T1 WHERE key1=1 and key2=2 and key3=3;

Obviously we can create a composite index on these three fields to optimize it, so we only need to scan one index at a time, instead of three, so we scan it separately.

Percona website has a good article comparing composite Index and Index merge: Multi Column indexes vs Index Merge

7. The leftmost prefix principle for composite indexes

As we said above, an OR operation on a non-leftmost prefix field of a composite index is not available to the composite index . Like what:

SELECT * from Tbl_name WHERE (Key_col1 > Ten OR key_col2 =) and nonkey_col=30;

The reason for this is that the index in MySQL uses B+tree, that is, to sort by the size of the first field of the composite index, inserted into B+tree, when the first field value is compared to the value of the second field. So what if we need to use a compound index for conditions such as or key_col2 = 20? A full scan of the composite index should be done to find all the key_col2 =20 items, and then return to the table to judge the nonkey_col=30, apparently at a very high cost. So in general OR key_col2 = 20 Such conditions are not available to the composite index. If you must use an index, you can create a separate index on the col2.

The leftmost prefix principle for composite indexes:

A composite index in MySQL that uses only the leftmost prefix when queried, such as:

Mysql> Show index from role_goods;+------------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+------------+------------+----------+--------------+-------------+-----------+-------------+--- -------+--------+------+------------+---------+---------------+|          Role_goods | 0 |            PRIMARY | 1 | ID |       A |     22816 | NULL |      NULL | |         BTREE |               | ||          Role_goods | 1 |            Roleid | 1 | Roleid |        A |     1521 | NULL | NULL | YES |         BTREE |               | ||          Role_goods | 1 |            Goodsid | 1 | Goodsid |        A |     1521 | NULL | NULL | YES |         BTREE |               | || Role_goODS | 1 |            Roleid_2 | 1 | Roleid |        A |     1901 | NULL | NULL | YES |         BTREE |               | ||          Role_goods | 1 |            Roleid_2 | 2 | Status |        A |     4563 | NULL | NULL | YES |         BTREE |               | ||          Role_goods | 1 |            Roleid_2 | 3 | number |       A |     22816 | NULL | NULL | YES |         BTREE |               | |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+6 rows in Set (0.00 sec)

There is a composite index on it: Roleid_2 (Roleid,status,number), if the condition is: where roleid=xxx and number=xxx, then only the leftmost prefix Roleid is used, not the number To filter. Because there is a field in between them, the status does not appear in the Where condition. The experiment looks like this:

Mysql> Explain select * from Role_goods where roleid=100000001 and Status=1 and number=1 limit 1;+----+-------------+-- ----------+------+-----------------+----------+---------+-------------------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+------------+------+-----------------+----------+---------+-------------------+------  +-------+| 1 | Simple | Role_goods | Ref | Roleid,roleid_2 | Roleid_2 | 23 |   Const,const,const | 13 | NULL |+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+ -------+1 Row in Set (0.00 sec) mysql> Explain select * from Role_goods where roleid=100000001 and Status=1 limit 1;+--- -+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+| 1 | Simple | Role_goods | Ref | Roleid,roleid_2 | Roleid_2 | 14 |   Const,const | 13 | NULL |+----+-------------+------------+------+-----------------+----------+---------+-------------+------+------ -+1 row in Set (0.00 sec) mysql> Explain select * from Role_goods where roleid=100000001 and number=1 limit 1;+----+---- ---------+------------+------+-----------------+--------+---------+-------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------  +| 1 | Simple | Role_goods | Ref | Roleid,roleid_2 | Roleid | 9 |   Const | 14 | Using where |+----+-------------+------------+------+-----------------+--------+---------+-------+------+------- ------+1 Row in Set (0.01 sec)
Mysql> Explain select * from Role_goods Ignore index (Roleid) where roleid=100000001 and number=1 limit 1;
+----+-------------+------------+------+---------------+----------+---------+-------+------+------------------- ----+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+------------------- ----+
| 1 | Simple | Role_goods | Ref | Roleid_2 | Roleid_2 | 9 |   Const | 14 | Using Index Condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+------------------- ----+
1 row in Set (0.01 sec)

You can see the changes in Key_len:

It is clear that the last query was used only for Roleid in the index, and no number was used. Number is used on index Conditon (that is, push down technology for indexing)

Note that the leftmost prefix is not meant to be: Be sure to create a composite index in the order in which each field appears in the Where . Like what

where status=2 and roleid=xxx and number = XXX

This condition establishes a conforming index, and we do not need to make the index in the order in which they appear status,roleid,number:

ALTER TABLE role_goods add index sin (status,roleid,number)

This is a great misunderstanding of the leftmost prefix. Because where status=2 and roleid=xxx and number = xxx and where roleid=xxx and number = XXX and status=2 they are equivalent. composite index, which field is at the top, depends on which field often appears in the Where condition, which field is best judged by the selectivity .

Further references can be found in the article:

http://www.orczhou.com/index.php/2013/01/mysql-source-code-query-optimization-index-merge/

Http://www.cnblogs.com/nocode/archive/2013/01/28/2880654.html

MySQL Optimization Index Merge (indexed merging)