0%

MySQL - 负向查询完全不能走索引么?

最近看到一篇文章,其中提到:“负向查询不能命中索引,会导致全表扫描”,这个与我之前对索引的理解产生了冲突。

某个SQL语句需要走索引进行查询,至少需要满足以下两个条件:

  • 有与SQL关联的索引,比如查询id=5,那么id列上必须要有索引;

  • MySQL查询优化器按照成本选择“最优执行计划”,例如如果走索引并没有比全表扫描性能好,否则会放弃索引,采用全表扫描。

    参考MySQL - 索引简介中的范围查询不一定会使用索引例子。

那么对于负向查询,则不能绝对的说不能命中索引,而是要根据情况来看。

假设有如下表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE `user` (
`pri_id` int(11) NOT NULL AUTO_INCREMENT,
`uniq_id` int(11) NOT NULL,
`index_id` int(11) NOT NULL,
PRIMARY KEY (`pri_id`),
UNIQUE KEY `uniq_id` (`uniq_id`),
KEY `index_id` (`index_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user` (`pri_id`, `uniq_id`, `index_id`)
VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5);

其中,pri_id列为主键,uniq_id列为唯一索引,index_id列为普通索引,那么不同列上的查询情况为:

  • explain select * from user where pri_id != 3;

    1

  • explain select * from user where uniq_id != 3;

    2

  • explain select * from user where index_id != 3;

    3

可以看到,上述三个查询语句,只有第一个查询,使用主键查询,才走了索引,其他两个查询均没有走索引,原因后面再分析,我们替换一下select *查询:

  • explain select pri_id from user where pri_id != 3;

    4

  • explain select uniq_id from user where uniq_id != 3;

    5

  • explain select index_id from user where index_id != 3;

    6

可以看到,上述三个查询语句,都走了索引。所以,此时我们可以肯定的是,“负向查询不能命中索引,会导致全表扫描”的结论是不成立的,但是原因是啥呢?

在这个例子中,使用select *进行查询的话,有两个弊端:

  • 回表问题

    由于uniq_id和index_id都是非聚集索引,如果使用select *进行查询的话,会先走辅助索引,查询到主键ID,然后再进行回表,从主键索引树上进行查询,最终得到结果。

    但是如果使用select *_id进行查询,则不需要回表,直接在辅助索引树上就能得到结果。

  • 结果集数据量问题

    由于数据都是递增的,使用!= 3进行查询,最终查询结果的数量为4,而全表的数量才5,使用索引查询的效果不大。

因此,在负向查询的这个例子中,虽然select *也是可以走索引的,但是由于回表,以及数据量的问题,MySQL认为走全表查询的代价反而更低,因此最终采用了全表查询。

为了证实我们的猜测,我们修改一下数据,将表中数据都删除掉,然后插入新数据:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO `user` (`pri_id`, `uniq_id`, `index_id`)
VALUES
(1, 1, 1),
(2, 2, 3),
(3, 3, 3),
(4, 4, 3),
(5, 5, 3),
(6, 6, 3),
(7, 7, 3),
(8, 8, 3),
(9, 9, 3);

在这个数据量下,再执行explain select * from user where index_id != 3;查询:

7

可以看到,同一个SQL语句,在这种数据的情况下,是可以走索引的。如我们上述猜测,新的数据虽然解决不了回表的问题,但是查询的结果集性能很好,因此MySQL的查询优化器最终选择了走索引。



-=全文完=-