First of all, I will introduce ICP, and then solve a slow query online. Just to be clear, this slow query is very strange.

ICP(Index Condition Pushdown) – Push under Index conditions

What is theICP?

Instead, what is the role of ICP? In summary, push down ICP is an attempt to use secondary indexes to filter out records that do not meet where conditions, thus reducing the number of times that you need to go back to the table for further judgment

With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, 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 the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

How do I determine if a statement uses ICP?

The Extra output item in Explain shows the Using Index condition

Official example – First experience with ICP

Suppose: This table has a joint INDEX INDEX(zipcode, lastname, firstName).

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
Copy the code
  • Don’t have toICP, only the leftmost matching principle is used. Then you can only use the syndication indexzipcodeBack table records cannot be removed effectively.
  • useICP, in addition to matchingzipcodeIn addition to the condition matching the federated indexlastnameAnd see if it fitswhereIn the condition of'%etrunia%'And then go back to the table. In this way, the use of a federated index can eliminate as many nonconformities as possiblewhereRecord conditions. This is theICPThe essence of optimization.

With Index Condition Pushdown, MySQL checks the lastname LIKE ‘%etrunia%’ part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.

Custom examples – explain output

Create an example,selectThe statement simply wants to use as many indexes as possible to remove inconsistencieswhereCondition record, output itexplainAs a result, see if it’s actually being used as intendedICP

Again, say the important things a few more times:ICPThe essence of the index is to filter the unqualified records as far as possible through the secondary index, even if it does not meet the leftmost matching principle, reduce back to the table, reduce the execution cost

Online problem

Problem description

Slow query logs are displayed based on monitoring. The oddest thing about this slow query is that it should have used ICP, but it didn’t use ICP anyway.

The name of the table Index used
tbl_checkin_followers_partion Idx_query (user_id, event_id, follower_id) Joint index

This table is used to keep track of friends, and here is the slow query:

EXPLAIN 
select user_id, follower_id, event_id, is_notice, forbid 
from tbl_checkin_followers_partion 
where follower_id = 26407612 
and user_id in (16388902.28532449.25771785.22383199.7331499.22057702.5913050.21043345.16841923.20954615.29327264.20428921.7008534.23268045.29081660.25542251.22481256.20884749.25770459.20200680.14144433.20452427.15762152.7270131.23102328.20288857.14275884.16161824.21886294.20007161.20785940.22115882.27661758.14602042.17261674.23177914.16889488.20887424.21042544.13615355.23870465.19223005.14718767.28303768.23741136.25175839.6426020.28237698.27967073.26407612);Copy the code

When you look at the execution plan, you see that indexed conditional push (ICP) is not used.

How do I determine if I am not using ICP?

  • Extra: Does not use index condition

Why should you use index push-downs? Idx_query (user_id, event_id, follower_id) and user_id in (…) And follower_id = 26407612. You can use ICP on the idx_query joint index. If you match user_id and follower_id, the number of entries will be much lower than if you only use user_id to filter the entries.

Select * from user_id Where key_len = 4; select * from user_id Where user_id = 1; select * from user_id Where user_id = 1; Because user_id has a lot of follower_id per user, the number of entries to the table can be very large, and this number of entries can be spread across multiple pages of the clustered index, which is random I/O. All of a sudden, the query becomes slow.

Why not? As ICP is understood, it is to minimize the number of records back to the table by using secondary indexes. Why not use ICP when ICP could be used in this statement? Logically, it should use ICP

Check whether the MySQL version is supported by ICP. 2. Smart water bottle Check, do, even read the source code, found in ICP conditions of use mentioned partition, ah, partition! Then I checked the official documentation and found that it was indeed a partition table problem.

ICP can be used for InnoDB and MyISAM tables. (Exception: ICP is not supported with partitioned tables in MySQL 5.6; this issue is resolved in MySQL 5.7.)

And our table —tbl_checkin_followers_partion, which uses partitioned tables.

This is aMySQL 5.6aboutICPThe pageThis is aMySQL 5.7aboutICPThe page PSMy only complaint is whyMySQL 5.6The description of partitioned tables is not in bold. I was just looking at it5.6I’m mainly looking at5.7I thought it was the same…

Problem solving

MySQL 5.6Version partitioned tables do not support index push-down (ICP), what then?

1. Upgrade 5.6 to 5.7, and you’ll probably get killed.

2, in fact, the way is very simple, that is to combine business scenarios + use MySQL scope search. Tips: The event_id in the federated index has only two fixed values for morning and evening

EXPLAIN 
select user_id, follower_id, event_id, is_notice, forbid 
from tbl_checkin_followers_partion 
where follower_id = 26407612 
and user_id in (16388902.28532449.25771785.22383199.7331499.22057702.5913050.21043345.16841923.20954615.29327264.20428921.7008534.23268045.29081660.25542251.22481256.20884749.25770459.20200680.14144433.20452427.15762152.7270131.23102328.20288857.14275884.16161824.21886294.20007161.20785940.22115882.27661758.14602042.17261674.23177914.16889488.20887424.21042544.13615355.23870465.19223005.14718767.28303768.23741136.25175839.6426020.28237698.27967073.26407612) 

AND event_id in (1.3);
Copy the code

It’s actually adding to the search criteriaAND event_id in (1, 3), how to determine if the query will be faster after this change. Look for theexplainIn thekey_lenItem, not addedevent_idSearch criteriakey_lenThe value is4, now the value is12.

What difference does it make?

That’s a big difference. Key_len is just a way of checking how many columns do I really need when I’m using a joint index? Because the number of columns in the joint index is greater than or equal to 1, user_id, event_id, and follower_id are all ints, 12 means that all entries in the joint index are used.

In this way, all field values are used in the joint index and the number of records returned to the table is reduced.

  • whenuser_id=16388902When,

MySQL will search for: User_id =16388902 and event_id =1 and follower_id=26407612 and user_id=16388902 and event_id = 3 and follower_id=26407612

  • whenuser_id=28532449

.

And so on, this is equivalent to multiple interval range lookups with only 1 value.

Using ICP

I copied the data from the table to the test database, and the version of the test database was MySQL5.7, which is where the partitioned table supports ICP. With the same initial query, we get a jSON-formatted execution plan for the query:

EXPLAIN 
select user_id, follower_id, event_id, is_notice, forbid 
from tbl_checkin_followers_partion 
where follower_id = 26407612 
and user_id in (16388902.28532449.25771785.22383199.7331499.22057702.5913050.21043345.16841923.20954615.29327264.20428921.7008534.23268045.29081660.25542251.22481256.20884749.25770459.20200680.14144433.20452427.15762152.7270131.23102328.20288857.14275884.16161824.21886294.20007161.20785940.22115882.27661758.14602042.17261674.23177914.16889488.20887424.21042544.13615355.23870465.19223005.14718767.28303768.23741136.25175839.6426020.28237698.27967073.26407612);Copy the code
{
  "query_block": {
    "select_id": 1."cost_info": {
      "query_cost": "110.01"
    },
    "table": {
      "table_name": "tbl_checkin_followers_partion"."partitions": [ // The partition of the table used
        "tbl_checkin_followers1"."tbl_checkin_followers2"."tbl_checkin_followers3"."tbl_checkin_followers4"."tbl_checkin_followers5"]."access_type": "range".// Access methods
      "possible_keys": [
        "idx_query"  // Possible indexes]."key": "idx_query".// The actual index used
      "used_key_parts": [
        "user_id"."event_id"]."key_length": "8"."rows_examined_per_scan": 50."rows_produced_per_join": 0."filtered": "0.10"."index_condition": "((`test`.`tbl_checkin_followers_partion`.`follower_id` = 26407612) and (`test`.`tbl_checkin_followers_partion`.`user_id` in (16388902285244 9257178 5223319 9733499220770 2591050210334 5168192 3209461 5293726 4204892 1700534232 68 045290166 0255225 1224125 6208474 9257045 9202068 0141443 3204242 7157215 2727131231232 8202885 7142588 4, 16161824218629 4200716 1207594 0221588 2276175 8146204 2172167 4231791 4168948 8208742 4210254 4136535 5238 70465192300, 5147876, 7283376, 8237113 and 6251583, 9642020282769, 8279707, 3264761 (2)) (`test`.`tbl_checkin_followers_partion`.`event_id` > 0))".// A condition that can be used on an index, i.e. a where condition that can be destroyed on an index. Found all used, ok, index condition push
      "cost_info": {
        "read_cost": "110.00"."eval_cost": "0.00"."prefix_cost": "110.01"."data_read_per_join": "1"
      },
      "used_columns": [
        "user_id"."follower_id"."event_id"."forbid"."is_notice"]}}}Copy the code

Note that the index_condition above has all the fields of the federated index filtered on the index of the storage tier.

My question:

  • whyused_key_partswithindex_conditionNot quite the same? Is this a pit?
  • why5.6Partition tables are not supportedICP? Does this have anything to do with the implementation of partitioned tables?

I continue to die in MySQL, if the experience continues to update the ratat.

reference

1. MySQL official documentation

How MySQL works