把生产环境遇到的问题简化了一下,表结构如下。
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