MySQL where 范围查询和 order by 关键字无法同时命中索引

发表于： 分类于：
字数： 616 阅读：≈ 2分钟 浏览： 评论：

开发中，使用 in 和 order by 的时候，发现无法同时命中索引。

使用 IN 运算符来过滤行时，MySQL 会先使用索引来定位表中的行，并将这些行的所有数据读入内存中。随后，MySQL 会根据查询语句中的 ORDER BY 子句对这些数据进行排序。

简单场景构造 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE `c` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL DEFAULT '0',
  `b` int NOT NULL DEFAULT '0',
  `c` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- data
insert into c(a, b, c)
values (1, 1, 1),
       (1, 1, 2),
       (1, 1, 3),
       (1, 2, 1),
       (1, 2, 2),
       (1, 2, 3),
       (1, 2, 3),
       (2, 2, 2),
       (3, 3, 3);

Explain SELECT

非范围查询 

1
2
3
4
5
6
7
8

explain select * from c where a = 1 and b = 1 order by c;
/*
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ref  | abc           | abc  | 8       | const,const |    3 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
*/

能够完美命中索引。


范围查询 

1
2
3
4
5
6
7
8

explain select * from c where a = 1 and b > 1 order by c;
/*
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | range | abc           | abc  | 8       | NULL |    4 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------+
*/

范围查询用到了filesort，表示 order by没有命中索引，abc 索引的作用相当于 ab。


索引分析

索引结构

abc
111
2
3
21
2
3
3
222
333
1

select * from c where a = 1 and b = 1 order by c;

在非范围查询的时候，可以发现，经过where语句，c的范围是 1、2、3，b已确定，c 有序，order by 可以命中索引。

1

select * from c where a = 1 and b > 1 order by c;

在范围查询的时候，经过where语句，c的范围是 1、2、3、1、2、3、3，b无法确定（可能为1或2），c 为无序，order by 无法命中索引。