MySQL语句优化（二）：优化索引选择

原创

mb5fd86d8699f84 2021-03-06 19:45:43 ©著作权

文章标签 Java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mb5fd86d8699f84的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、SQL语句及执行计划

```
表结构
```

CREATE TABLE `t_10` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=803446 DEFAULT CHARSET=utf8;

```
SQL语句
```

mysql> select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.34 sec)

```
SQL语句执行计划
```

mysql> explain select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------+
|  1 | SIMPLE      | t_10  | NULL       | range | a,b           | b    | 5       | NULL | 329688 |     2.18 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------+

从执行计划可以看出，该SQL走了扫描行数更多的b索引

二、原因分析

由于语句查询到结果后又需要对b字段再进行排序，而b字段本身有索引，已经是有序的，如果选择索引b的话，不需要再做排序，因此优化器走了b字段的索引（实际导致查询扫描行数更多耗时更久了）

三、SQL改写

```
改写后的SQL语句
```


mysql> select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
Empty set (0.01 sec)

```
改写后的执行计划
```

mysql> explain  select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t_10  | NULL       | range | a,b           | a    | 5       | NULL | 14398 |    50.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+

显然，查询走了索引a，执行时间大大降低，并且扫描行数也减少了很多。

原因分析：当优化器发现a、b两个字段都需要排序时，会重点关注扫描行数，由于选择a索引扫描行数相对b索引少很多，因此走了索引a。