Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the role of index_merge in MySQL optimization". In daily operation, I believe that many people have doubts about the role of index_merge in MySQL optimization. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubt of "what is the role of index_merge in MySQL optimization?" Next, please follow the editor to study!

1. Why is there index merge?

We may have multiple conditions (or join) in our where that involve multiple fields, AND or OR between them, so it is possible to use index merge technology at this time. Index merge technology, to put it simply, is to conditionally scan multiple indexes separately, and then intersect/union their respective results.

Before MySQL5.0, a table could only use one index at a time, and multiple indexes could not be used for conditional scanning at the same time. However, index merge optimization technology has been introduced since 5.1. multiple indexes can be used for conditional scanning on the same table.

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

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

In EXPLAIN output, the Index Merge method appears as index_merge in the 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 those indexes.

Index merge: range scans of multiple indexes in the same table can merge the results in three ways: union, intersection, and their combinations (first inside intersect and then outside union).

According to the different merging algorithms, index merge algorithms are divided into three types: intersect, union, sort_union.

2. Intersect of index merge

To put it simply, index intersect merge is the intersection operation of the results obtained by multiple index conditional scans. Obviously, index intersect merge. Index intersect merge occurs only when there is an AND operation between multiple index commits. The following two where conditions, or a combination of them, are index intersect merge:

3. Union of index merge

To put it simply, index uion merge is a conditional scan of multiple indexes, and the union operation is performed on the results, which is obviously an OR operation between multiple conditions.

The following types of where conditions, and their combinations, may use the index union merge algorithm:

1) the condition uses all fields in the composite index or left prefix fields (also applicable to single-field indexes)

2) any range condition on the primary key

3) any where condition that meets the index intersect merge

When performing OR operations under the above three where conditions, the index union merge algorithm may be used.

4. Sort_union of index merge

This access algorithm is employed when the WHERE clause was converted to several range conditions combined by OR, but for which the Index Merge method union algorithm is not applicable. (if multiple conditional scans perform OR operations, but do not conform to the index union merge algorithm, the sort_union algorithm may be used.)

5. Limitations of index merge

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

6. Further optimization of index merge

Index merge allows us to scan multiple indexes at the same time and then merge the results. It sounds like a good feature, but if index intersect merge appears, it generally also means that our index is not built properly, because index intersect merge can be further optimized by building a composite index.

7. Leftmost prefix principle of compound index

As we mentioned above, a composite index cannot be used for OR operations on non-leftmost prefix fields of a composite index.

SQL is as follows:

Select cd.coupon_id, count (1) total from AAA cd

Where cd.coupon_act_id = 100476 and cd.deleted=0 and cd.pick_time is not null

Group by cd.coupon_id

In the AAA table, coupon_act_id and deleted are independent indexes

Select count (*) from AAA where coupon_act_id = 100476; the result is 12360 lines

The result of select count (*) from AAA where deleted=0; is 1300W

As we can see from the above explanation, index merge actually aggregates the filtered results together after filtering two separate index, and then returns the result set.

In our example, due to the poor filtering of the deleted field, there are still a lot of rows returned, resulting in a lot of disk read, resulting in a very high load of cpu, resulting in a direct delay.

Ps: in fact, in this case, you don't need to add two conditional index, just kill the index of deleted, and directly use the index of coupon_act_id. After all, the filtered result set of this index is already very small.

Or by turning off the index intersect function.

At this point, the study on "what is the role of index_merge in MySQL optimization" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.