MySQL索引条件下推优化实战

发表于

先说一下文章安排，首先介绍索引条件下推（ICP，以下均用ICP简称），然后解决线上的一个慢查询。先卖个关子，这个慢查询非常奇怪。

ICP(Index Condition Pushdown) - 索引条件下推

什么是ICP？

不如换个问法，ICP的作用是什么？
一句话总结：索引条件下推ICP就是尽可量利用二级索引筛除不符合where条件的记录，如此一来减少需要回表继续判断的次数

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.

如何确定某条语句使用了ICP？

Explain的输出项的Extra会显示Using index condition

官方示例 - 初次体会ICP

示例如下，这个例子来自MySQL官方文档：
Suppose：假设这个表有联合索引INDEX(zipcode, lastname, firstname)

1
2
3
4
mysql复制代码SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
  • 不用ICP，只使用最左匹配原则。那么只能使用联合索引的zipcode，回表记录不能有效去除。
  • 使用ICP，除了匹配zipcode的条件之外，额外匹配联合索引的lastname，看其是否符合where条件中的'%etrunia%'，然后进行回表。如此一来，使用联合索引就可以尽可量排除不符合where条件的记录。这就是ICP优化的真谛。

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.

自造示例 - explain输出

创建一个示例，select语句就是想要尽可量利用索引去掉不符合where条件的记录，输出其explain结果，看是否真的按照预期那样使用了ICP

再次总结，重要的事情多说几遍：ICP的实质就是通过二级索引尽可能的过滤不符合条件的记录，哪怕不符合最左匹配原则，减少回表，降低执行成本

线上问题

问题描述

根据监控，查询到慢查询日志。这个慢查询最奇怪的地方在于，它本应该使用ICP，但却无论如何都没能使用ICP。

表名 用到的索引
tbl_checkin_followers_partion idx_query(user_id, event_id, follower_id)联合索引

这张表用来记录好友关系，下面是这个慢查询语句：

1
2
3
4
5
6
mysql复制代码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) 
;

查看执行计划，发现并没有使用索引条件下推(ICP)。

如何确定没有使用ICP？

  • Extra: 没有Using index condition

为什么应该使用索引下推？

首先联合索引idx_query(user_id, event_id, follower_id)，其次搜索条件为 user_id in (...) and follower_id = 26407612。完全可以在联合索引idx_query上使用ICP，通过匹配user_id和follower_id两者进行回表，符合条件的记录数相比只使用user_id进行过滤然后回表的记录数一定会少很多。

但是根据explain的结果，Extra只有Using Where && key_len = 4（说明联合索引三个字段只用到了第一个user_id）该语句只是根据user_id进行回表，因为每个用户user_id有非常多的follower_id，回表的记录会非常多，并且这么多记录可能分布在聚促索引的多个页面，这就是随机I/O啊。一下子就将该查询语句变成慢查询。

为什么没有使用？

按照对ICP的理解，它就是尽量利用二级索引减少回表的记录数。在这个语句中，明明可以使用ICP，为什么没有使用呢？讲道理，它就应该使用ICP

排查过程

1、确定线上MySQL的版本，查看是5.6，ok，是支持ICP的。

2、抓耳挠腮。查了查，搞了搞，甚至看了源码，发现在ICP的使用条件中提到了分区，啊，分区！然后查了一下官方文档，才发现确实是分区表的问题。

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.)

而我们这个表-tbl_checkin_followers_partion，是使用分区表的。

这是MySQL 5.6 关于ICP的页面

这是MySQL 5.7 关于ICP的页面

PS：我唯一的不满，就是为啥MySQL 5.6关于分区表的说明没有加粗。我当时只是粗看了一下5.6的文档，我主要在看5.7的文档,以为它俩一样的…

问题解决

MySQL 5.6版本分区表不能支持索引条件下推（ICP），那该如何是好？

1、直接将5.6升级到5.7，这样可能会被打死吧。

2、其实办法很简单，那就是结合业务场景+利用MySQL的范围查找。

tips：联合索引中的event_id只有两个固定值，表示早上和晚上

1
2
3
4
5
6
7
mysql复制代码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);


其实就是在搜索条件中增加了AND event_id in (1,3)，如何确定这么修改之后，查询会变快。注意看explain中的key_len项，没有添加event_id搜索条件前key_len值是4，现在值为12。

这有啥区别呢？

区别大了去了，key_len就是用来判别使用联合索引时，我到底发挥作用的是几个列的值？因为联合索引的列数大于等于1，user_id、event_id、follower_id全都是int型，12就相当于该联合索引中的所有项都被用到。

如此一来，联合索引所有的字段值都被用到，也能够减少回表的记录数。

  • 当user_id=16388902时，

MySQL会搜满足条件：user_id=16388902 and event_id = 1 and follower_id=26407612

和 user_id=16388902 and event_id = 3 and follower_id=26407612

  • 当user_id=28532449

…

以此类推，此时就相当于区间只有1个值的多个区间范围查找。

使用ICP

我将表中的数据复制到测试数据库，并且测试数据库的版本为MySQL5.7，即分区表支持ICP的情况。
同样的初始查询，得到查询的json格式的执行计划：

1
2
3
4
5
6
mysql复制代码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) 
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
json复制代码{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "110.01"
    },
    "table": {
      "table_name": "tbl_checkin_followers_partion",
      "partitions": [ // 用到的表的分区
        "tbl_checkin_followers1",
        "tbl_checkin_followers2",
        "tbl_checkin_followers3",
        "tbl_checkin_followers4",
        "tbl_checkin_followers5"
      ],
      "access_type": "range", // 访问方法
      "possible_keys": [
        "idx_query"  // 可能用到的索引
      ],
      "key": "idx_query", // 实际使用的索引
      "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 (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 (`test`.`tbl_checkin_followers_partion`.`event_id` > 0))", // 在索引上可以使用的条件，也就是在索引上就能消灭的where条件。发现全都用了，ok，索引条件下推
      "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"
      ]
    }
  }
}

请注意上面的index_condition，在存储层的索引上，已经用到了联合索引的所有字段进行过滤了。

我的疑问：

  • 为何used_key_parts与index_condition不太一样呢？这是个坑吗？
  • 为何5.6分区表不支持ICP呢？这和分区表的实现有关吗？

我在继续死磕MySQL中，如果心得继续更新叻。

参考

1、MySQL官方文档

2、掘金小册《MySQL是如何运行的》

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%