msyql遇到了Using index condition

把生产环境遇到的问题简化了一下，表结构如下。

CREATE TABLE `test_index` (
  `id` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `value` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`id2`)

数据量为250rows。查询语句是。

select * from test_index where id > 10 and id2 > 2200000 limit 10

根据数据结构中相关的算法，索引id其实是没有作用的。但是利用force关键字强制使用索引，查询的时间差别还是很明显的。

mysql> select * from test_index force index(id) where id > 10 and id2 > 2200000 limit 10;
+---------+---------+---------+
| id      | id2     | value   |
+---------+---------+---------+
| 2200001 | 2200001 | 2200001 |
| 2200002 | 2200002 | 2200002 |
| 2200003 | 2200003 | 2200003 |
| 2200004 | 2200004 | 2200004 |
| 2200005 | 2200005 | 2200005 |
| 2200006 | 2200006 | 2200006 |
| 2200007 | 2200007 | 2200007 |
| 2200008 | 2200008 | 2200008 |
| 2200009 | 2200009 | 2200009 |
| 2200010 | 2200010 | 2200010 |
+---------+---------+---------+
10 rows in set (1.03 sec)

mysql> select * from test_index where id > 10 and id2 > 2200000 limit 10;
+---------+---------+---------+
| id      | id2     | value   |
+---------+---------+---------+
| 2200001 | 2200001 | 2200001 |
| 2200002 | 2200002 | 2200002 |
| 2200003 | 2200003 | 2200003 |
| 2200004 | 2200004 | 2200004 |
| 2200005 | 2200005 | 2200005 |
| 2200006 | 2200006 | 2200006 |
| 2200007 | 2200007 | 2200007 |
| 2200008 | 2200008 | 2200008 |
| 2200009 | 2200009 | 2200009 |
| 2200010 | 2200010 | 2200010 |
+---------+---------+---------+
10 rows in set (9.80 sec)

explain的结果如下。

mysql> explain select * from test_index where id > 10 and id2 > 2200000 limit 10;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_index | NULL       | range | PRIMARY,id    | PRIMARY | 4       | NULL | 1248080 |    33.33 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test_index force index(id) where id > 10 and id2 > 2200000 limit 10;
+----+-------------+------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | test_index | NULL       | range | id            | id   | 4       | NULL | 1248080 |    33.33 | Using index condition |
+----+-------------+------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

不同的地方是Using index condition。对应的文档https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html