因为在工作中经常遇到，每次不影响性能就算了，最近又经常看到，就简单分析了一下。这里注重的只是结果和证明，主要是从必要的DEBUG点进行反推，而不是顺着流程分析，说明问题即可，这也是因为能力和时间有限。其次主要使用的是5.7代码，因为这部分学习的时候用的是5.7，仅供参考。
一、举例
最近在分析一个问题遇到了类似的问题，以前也经常看到这种问题，这里举例如下，
mysql> select * from testtemp;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 |
| 5 | 5 | 5 | 5 |
| 6 | 5 | 5 | 5 |
| 7 | 7 | 7 | 7 |
| 8 | 8 | 8 | 8 |
| 9 | 9 | 9 | 9 |
+------+------+------+------+
9 rows in set (4.13 sec)
mysql> show create table testtemp \G
*************************** 1. row ***************************
Table: testtemp
Create Table: CREATE TABLE `testtemp` (
`a` varchar(20) DEFAULT NULL,
`b` varchar(20) DEFAULT NULL,
`c` varchar(20) DEFAULT NULL,
`d` varchar(20) DEFAULT NULL,
KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
例1：
mysql> desc select * from testtemp force index(a) where a in('1','2') and b in('1','2');
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | testtemp | NULL | range | a | a | 126 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
列2：
mysql> desc select * from testtemp force index(a) where (a='1' and b='1') or (a='2' and b='2');
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | testtemp | NULL | range | a | a | 126 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
例3：
mysql> desc select * from testtemp force index(a) where a in('1','2');
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | testtemp | NULL | range | a | a | 63 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
我们注意到这里3个语句的type都是range并且都使用ICP，但是这里range和ICP视乎不好理解，很显然这里并没有什么范围操作，因为明显可以做到索引点查就可以拿到数据，而且并没有扫描><的这类范围操作。而这里的ICP到底下推了什么条件也不好理解，因为没法分辨出下推了什么条件。这也是我在工作中经常遇到的。
一、ICP简述
ICP：全称为Index Condition Pushdown，是MySQL 5.6引入的一项优化策略。简单的来说就是将本该在MySQL进行过滤的条件下推到Innodb引擎层去做。但是这种策略和我们平时说的使用到了索引实际上是不同的，我们平时说的用到了索引一般指的是使用到了索引进行定位和访问，但是这里却是一种过滤操作。严格意义上来讲和MySQL层的过滤区别并不大，但是由于这里过滤发生在Innodb层，并且还没有进行回表和加行锁操作（for update），因此优点有如下几点：
- 减少了回表操作
- 减少了回表后主键加锁（for update），但是对于查询索引而言加锁没有变化。
- 减少了返回给MySQL层数据的数据，也就是减少innodb层数据到mysql层数据的转换也就是函数row_sel_store_mysql_rec，而blob字段的获取就在这个函数中，也就减少了blob这个外部存储字段获取的次数。
下推过滤的操作是需要下推的条件和进行Innodb层定位的条件同时包含在同一个组合索引才能完成，举个列子比如索引包含（a ，b，c）三列，如果是（where a=** and c like ‘%*%’），那么下推才能完成。
但是很显然上面的例子很容易理解，但是这里的案例却不容易理解，到底是哪些条件下推了，我们需要分析一下。
二、range的疑惑
从上面的3个例子来讲，不管怎么说视乎都不应该是范围扫描，但是执行计划显示是range，那么这里的range真的是范围扫描吗？如果是范围扫描是不是会导致过多的数据扫描？
其实这里范围扫描有一定的迷惑性，下面我们分别分析例子1和例2，实际上分析了这两个，例子3也就没必要分析了。
2.1 例子1 where a in('1','2') and b in('1','2')
这里例子来讲，条件实际上会分为(a:'1' b:'1')/(a:'1' b:'2')/(a:'2' b:'1')/(a:'2' b:'2')，虽然走了range的接口，但是这个范围的结束值是没有的，且结束值和启始值是一样的，会分别将(a:'1' b:'1')/(a:'1' b:'2')/(a:'2' b:'1')/(a:'2' b:'2')带入进行查询，并且设置标记eq_range_arg=true
handler::read_range_first (this=0x7ffedc97ca00, start_key=0x7ffedc97cae8, end_key=0x7ffedc97cb08, eq_range_arg=true, sorted=false)
(这个调用会调用4次，点查range的启始值)
也就是说分别将(a:'1' b:'1')/(a:'1' b:'2')/(a:'2' b:'1')/(a:'2' b:'2') 4个查询的数据分别作为范围查询的启始值进行查询，而不需要有结束值，本来就没有，下面是输出证明，其中0X31就是'1'，0X32就是'2'。
-
(a:'1' b:'1')
image.png
-
(a:'1' b:'2')
image.png
-
(a:'2' b:'1')
image.png
-
(a:'2' b:'2')
image.png
很显然这个语句实际上转换为4次数据的定位操作，分别进行定位，然后接下来就是eq_range_arg=true会发挥作用，当每次访问数据的时候因为eq_range_arg=true的存在，调用接口变为了handler::ha_index_next_same
image.png
也就是说每次因为不是唯一索引都需要访问一下下一条数据，找到不满足的行就结束。那么很显然条件实际差不多就是 ：
- a='1' and b='1'
- a='1' and b='2'
- a='2' and b='1'
- a='2' and b='2'
也就是4次数据定位操作，而对于更简单的例子3， a in('1','2')也可以同样的理解，虽然是range但是实际上就是2次数据定位，分别为a='1'/a='2'。
2.2 例子2 (a='1' and b='1') or (a='2' and b='2')
这个例子其实也是一样的，但是定位次数变少了，因为只有2种可能了，这里稍微看看定位的数据是什么，同样断点handler::read_range_first（调用2次），当然也是没有end key的，标记eq_range_arg=true
-
（a:'1' b'1'）
-
(a:'2' b:'2')
image.png
和上面一样。
2.3 range说明
实际上这些情况下看到range，实际上就是没有结束条件的range，分别点查数据，并没有实际的范围扫描，不会导致性能问题。
三、ICP的疑惑
这个问题我们只描述一下案例1，in的情况了，因为都是一样的，这里的ICP实际上是全部条件下推，我觉得能够起到的作用就是在每次访问下一条数据的时候能够更快的过滤掉数据，而不用到mysql层过滤且结束本次查询。
比如案例1我们上面分析了a in('1','2') and b in('1','2');条件实际上产不多就是，
- a='1' and b='1'
- a='1' and b='2'
- a='2' and b='1'
- a='2' and b='2'
因为每次点查数据的时候不是唯一索引都需要访问，都需要向下继续访问一条数据来证明数据访问完了，这个时候ICP就可以提交在innodb层过滤且证明本次访问结束了。
这里来证明一下，ICP接口很简单了，我们断点row_search_idx_cond_check 和innobase_index_cond将整个条件打印出来就可以了，
3.1 a in('1','2') and b in('1','2') 中的and
image.png
3.2 a in('1','2')
image.png
3.3 b in('1','2')
image.png
因此我们可以完整的看到整个条件下推到了innodb层，也证明我们的说法。
四、总结和8.0.23测试
- 1、对于某些in操作或者or操作，明显使用索引的情况下，range操作可能就是点查数据，数据的查找量并不会增加。
- 2、某些情况下的ICP可能是满足索引条件的全部条件下推，这部分注意即可，不要过多疑惑，类似的还有> and < 这种也是下推的全部条件。
- 3、总体来讲这些例子中的情况并不会影响到性能。
最后就是8.0 随意跑了一下也是一样的情况，
mysql> desc select * from testtemp force index(a) where a in('1','2') and b in('1','2');
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | testtemp | NULL | range | a | a | 166 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)
mysql> desc format=tree select * from testtemp force index(a) where a in('1','2') and b in('1','2');
+------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on testtemp using a, with index condition: ((testtemp.a in ('1','2')) and (testtemp.b in ('1','2'))) (cost=19.21 rows=4)
|
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
也希望本文能够解开你的疑惑，跟我一样。
网友评论