从Mysql不走索引看InnoDB的索引原理

有索引的情况下,Mysql还是扫表,怎么回事

今天碰到一个这个问题:在where语句中有一个字段可以走二级索引去范围查的情况下,发现mysql并没有走索引,而是扫表。不知道大家有没有遇到过这个问题,并产生疑问。

为什么mysql在明明有索引的情况下却选择不走索引,而选择扫表呢?今天我们从InnoDB的索引原理讲一下为什么会出现这种现象。

InnoDB索引原理

InnoDB对于PK的索引策略和二级索引的策略是不一样的。

部分知识来自于《高性能MySQL》,以及我自己的理解,本人没有看过源码,所以大家对内容要自行甄别对错

数据结构

InnoDB的索引数据结构是B树,更准确的说是B+树。为什么是B+树呢?因为B+树叶子节点不带数据,所以存储索引数据可以使用更少的磁盘存储空间,那么在PageCache读盘的时候,预读可以一次读取更多的索引数据,从而使用更少的磁盘IO就可以查询到更多的索引数据,这样就可以更快的定位到数据位置。

聚集索引

一张表只能由唯一的聚集索引。一般PK上的索引会自动被设置为聚集索引(但是你也可以先创建聚集索引,再创建PK,大多数情况下,PK索引就是聚集索引)。之所以叫聚集索引,是因为所有叶子节点上的记录都紧凑的按主键顺序存储在磁盘上。由于这个特点,使用主键自增会获得很好的写性能,因为是顺序写。

非聚集索引

二级索引和PK索引不一样,虽然数据结构也是B+树,但是叶子节点上的数据记录的是PK的值,而不是实际的数据,所以在使用二级索引查找到的实际是二级索引对应的PK索引。

这样会导致一个问题,就是对于二级索引上的范围查找,会导致大量的随机读IO。因为首先根据二级索引查找到的是一批离散的PK,然后再根据这些PK去查找记录,这时候会发生随机读IO,并且还带来了logn的额外查询时间。

这种索引称为覆盖索引。

覆盖索引

上面说到二级索引范围查找带来的随机IO现象,再有一种情况下不会发生,那就是SELECT的字段只有PK和二级索引的列,由于二级索引的叶子节点上保存的就是PK数据,并且二级索引中就保存了列的值,所以这时候不需要再回到PK索引上查找。

所以,如果我们SELECT中的列都有对应的索引存在的话,那么会提升查找效率,因为不需要去真正的记录里面去查找数据,只用在索引数据里面查找就行了。

回答问题

好了,对索引原理有一个大致的了解。

那么为什么有时候,where语句里面明明有索引可走的情况下,mysql会选择扫表呢?原因就是扫表是顺序IO,而二级索引是先查找PK,然后再在PK索引中查找,是一个随机IO,外带一个logn的查询开销。

那么当二级索引范围查的扫描列大于一定的数量的时候,explain会发现,prossible_keys显示有索引可走,但是实际的key却没有走索引,type=ALL。

当逐渐缩小二级索引上的查找范围的时候,会发现,mysql会突然又选择去使用prossible_keys下显示的索引了。

举个例子

举个例子:

1
2
3
4
5
6
7
8
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` int(11) DEFAULT '0',
`status` int(11) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_create_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

created_at列上有一个二级索引。

使用查询语句:

1
SELECT count(*) FROM test WHERE type=? and status=? and created_at>'2020-01-01 00:00:00';

这时explain:

select_type type prossible_keys key Extra
SIMPLE ALL idx_create_at NULL Using where

可以看到有索引却没有走。

当我们缩小created_at的范围时:

1
SELECT count(*) FROM test WHERE type=? and status=? and created_at>'2020-03-01 00:00:00';

这时explain:

select_type type prossible_keys key Extra
SIMPLE range idx_create_at idx_create_at Using index condition; Using where

发现同样一个语句,在范围查不同的情况下,有时候Mysql会选择不同的方式去进行实际的查找。

刚刚说过了,这种权衡是在较少的随机IO和较多的顺序IO这两者之间抉择的,并不是没有走索引性能就一定会差

这里count()可以换成表中各个具体的列,但是不要用select ,这样不会走索引,原因我还不知道

让mysql强制走索引

也很简单,查询分两步走:

1
2
SELECT count(*)
FROM test as a inner join test as b on a.id=b.id and a.status=? AND a.type=? AND b.created_at>"2020-01-01 00:00:00";

再次expain:

select_type table type prossible_keys key Extra
SIMPLE b range PRIMARY,idx_create_at idx_create_at Using where; Using index
SIMPLE a eq_ref PRIMARY PRIMARY Using where

发现两次查询都走了索引。

并且使用idx_create_at二级索引的时候,还是用了覆盖索引,因为这一步只取出了PK。

我不喝咖啡,但是我相信知识有价。