MySQL明明有索引，为什么不用？

时间：2022-09-19 17:36:40人气：次作者：快盘下载我要评论

一个经典的mysql索引问题

今天分享一个线上的经典MySQL索引问题。

01背景介绍

今天在线上运维过程中，遇到了一个MySQL的经典索引问题。线上的表结构不方便展示，我模拟了一个表结构用于说明问题：

CREATE TABLE `test_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

上面的表结构中，有两个索引：一个是id，也是自增主键，另外一个是idx_age，它是一个普通二级索引。

表里面插入了从1~10w的数据，形如下面这样：

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 1    |    1 |
|  2 | 2    |    2 |
|  3 | 3    |    3 |
|  4 | 4    |    4 |
|  5 | 5    |    5 |
|  6 | 6    |    6 |
|  7 | 7    |    7 |
|  8 | 8    |    8 |
|  9 | 9    |    9 |
| 10 | 10   |   10 |
+----+------+------+
10 rows in set (0.01 sec)

下面我们看两个查询的SQL：

SQL 1：

select * from test_index where age>0 and age<20000 order by age ;

SQL 2：

select * from test_index where age>0 and age<20000 order by age limit ？;

其中，？代表一个具体的数字。

那么对于这两个SQL，MySQL会采用何种执行计划呢？

02执行计划分析执行计划分析如下：

MySQL  >explain select * from test_index where  age>0 and age<20000 order by age ;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | test_index | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 100139 |    37.47 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL >explain select * from test_index where  age>0 and age<20000 order by age limit 100,200;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | test_index | NULL       | range | idx_age       | idx_age | 5       | NULL | 37524 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

可以看到：

第一个SQL，关键字标红的是：all，using filesort

第二个SQL，关键字表用的是：range，idx_age

我们表的age字段上有普通索引，本身就是根据age字段进行排序的。但是我们的SQL语句中，除了要访问age，还需要访问id列和name列，所以，在age这个索引上，不能获取所有的数据。

那么，正常情况下，这个SQL的可能的执行方法有以下两种：

方法1、扫描age字段，先找到符合条件的age值，再根据age字段的索引，获得对应的id列值，然后"回表"，去聚集索引(也就是主键id）上去查找对应的name列的值

方法2、直接在聚集索引id上面查找满足age字段的值，然后再利用文件排序。

从实际的情况来看，

SQL 1：

select * from test_index where age>0 and age<20000 order by age ;

采用的是上述方法2

而SQL2：

select * from test_index where age>0 and age<20000 order by age limit 100,200;

采用的是上述方法1

age列上已经创建了索引，查询也是按照age来的，而且还有排序操作。为什么第2个SQL的限制条件更多，但是却能用到索引，第一个SQL的限制条件更少，却用不到索引？

真实情况是什么样子的？

我们再来看下面的对比：

MySQL >explain select * from test_index where  age>0 and age<20000 order by age limit 100,200;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | test_index | NULL       | range | idx_age       | idx_age | 5       | NULL | 37524 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MySQL >explain select * from test_index where  age>0 and age<20000 order by age limit 100,20000;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | test_index | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 100139 |    37.47 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

上面的这两个SQL语句：

SQL 3：

explain select * from test_index where age>0 and age<20000 order by age limit 100,200;

SQL 4：

explain select * from test_index where age>0 and age<20000 order by age limit 100,20000;

仅仅是limit 最后面的值不一样，但是执行计划却完全不相同。

扫描行数100的，采用的方法1，先索引查询，再回表；