Shulou(Shulou.com)06/01 Report--

Editor to share with you the example analysis of ICP index push down in MYSQL. I hope you will get something after reading this article. Let's discuss it together.

MYSQL's ICP probably everyone knows, Index condition pushdown, but how to use this thing, what use, when to use, it is estimated that not many people can answer.

In fact, this article is a bit difficult to write, I about a day, did not get the answer, in the end when can I go ICP. So here are the results I got after passing the test for about a day.

Index conditional push (ICP) is an optimization in which MySQL uses an index to retrieve rows from a table. If there is no ICP, the storage engine traverses the index to locate the rows in the base table and returns them to the MySQL server, which calculates the WHERE conditions for those rows. With ICP enabled, if only the columns from the index can be used to evaluate part of the WHERE condition, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using index entries. And the types of queries that can be used, such as range ref eq_ref.

The above is a more official statement, if explained in vernacular, in a word, to reduce the consumption caused by the use of secondary index query because the secondary index does not contain certain fields, and some of the data processed by the INNODB engine layer is no longer uplink to the SERVER layer.

1 We use the following table to do an experiment. The following two diagrams illustrate the table structure, the amount of data, and whether the query has gone to ICP, obviously using ICP.

Obviously, it seems that ICP is a very simple thing, but in fact, we look at the following table.

The above image obviously meets the condition of going to ICP at the top, why the above query does not use ICP, but only index scan.

Why, other people's inquiry has gone ICP, yours has not gone ICP

Let's turn over and look at the official documents about when to go to ICP.

Translation

1 first of all, your query type is range, ref,eq_ref, if const is sorry, there is no need to go (but the query type on the above diagram conforms to ref, why not go)

2 the database engine needs INNODB OR MYISAM, (the database engine is INNODB, why not go)

3 ICP only serves the secondary index, and you can't go ICP when querying the primary key. (the problem is that I query the non-primary key of jet lag, and I also use the secondary index, so why not go?)

4 ICP does not support secondary indexes created on virtual columns (I am a physical column, why not go)

5 the condition is that the subquery can't go (I'm not a subquery, why don't I go)

6 condition is a function, also can not go (I am not a function, I gave a specific value, why not go)

7 trigger conditions can't go (I'm not, why don't you go)

The above meets the conditions listed above that you cannot go to ICP, and the query in the above figure also meets the requirements for going to ICP, so why not go.

Let's change the query a little bit, and we can see that even if there is no result in the query, we still have ICP.

So the question is, how can we go to ICP? we can pay attention to it a little bit. When you go to ICP, in most cases, it is the scope of data obtained through INDEX. Compared with other conditions outside the index, you can't quickly define the data you want to find. (I guess I can't understand what I'm saying. Draw a picture.), as you can see, when you go to ICP, most of the scenarios are non-index conditions, which can better locate the final result of the query than walking the index. At the same time, using the index can still exclude a large part of the data, otherwise you will scan the whole table (note: most of the scenarios)

In this case, go to ICP, so in the case of those data that are in line with Article 7 of the official, why not go ICP, most of the reason is that the conditions contained through non-index are not less than the amount of data that is simply located by index.

Note: check the details of whether to go to ICP, through SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp%'

Partial field meaning

Icp_: evaluates the number of rows of ICP

Icp_no_match: the number of rows that do not exactly match the push position condition

Icp_out_of_range: the number of rows checked that are not within the valid scan range

Icp_match: the number of rows that exactly match the push position condition

If it is all 0, it means you did not use ICP, and if there is no index condition in EXPLAIN, you do not use ICP.

After reading this article, I believe you have some understanding of "sample Analysis pushed down by ICP Index in MYSQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.