数据库-索引

48 阅读7分钟

主键索引和非主键索引的区别?

主要分类是主键索引和非主键索引,先看sql:

mysql> create table T(
id int primary key, //主键索引
k int not null, //非主键索引
name varchar(16),
index (k)) engine=InnoDB;

再看示意图:

image.png

左边的树是主键(ID)索引,右边的树是非主键(k)索引。

主要是主键和非主键的索引的区别,为什么会有这个区别?主要是因为叶子节点的数据不一样,一个是记录,一个是主键,也就是说,主键索引只需要找一次就找到了, 而非主键索引需要找两次,第一次是非主键索引找,然后找到了主键的值,第二次是主键索引找,最终才找到记录,说白了,就是在两颗不同的b+树里找数据。

总结,一句话,就是:找一次和找两次的区别!这个才是本质,其他的根本不用记,什么主键索引和非主键索引,这些都只是表象,更不要说那些其他的更多的花里胡哨的名字。

什么是聚簇索引?

其实就是主键索引,非要弄一个傻不拉几的名字,天天造新名词。

什么是回表?

其实就是找两次,即第二次到主键索引树里面去找数据。

什么是覆盖索引?

其实就是不需要查两次,即只需要查一次非主键索引树,就可以找到数据,找到什么数据呢?比如找到主键字段的值,因为非主键索引的叶子节点包含了主键字段的值,举个sql例子:

select ID from T where k between 3 and 5

说白了,就是你要查询的字段,已经包含在了非主键索引树的叶子节点,本质其实就是只需要查一次,只需要查一个树,无需再次查询主键树。

如果你要查询整条记录,那必然不是覆盖索引!说白了就是,如果要查询整条记录,必须查两次!

多列索引的作用?

本质其实就是为了减少一次查询,因为如果是单列索引的话,就需要回表,如果是多列索引的话,也需要回表,但是呢,它会在第一次查询的时候,匹配第二个字段,即过滤掉不匹配的数据,说白了,就是回表的数据没有那么多了,提前又帮你筛选了一轮,而不是等到回表的时候再去匹配第二个字段。

看示意图:多列索引

image.png 示意图说明,现在要找:name='张三' and age='10',第一次查询的时候,先找到和name相等的数据,并且还要用age来过滤数据,age不相等的数据就不用回表了,本质就是解决这个问题,即减少回表次数。(注:上面的图其实有点问题,张六这个数据应该是没有的,因为匹配的是name='张三'。懒的画新图了,所以这里说明一下。)

所谓的索引的优化,其实都是为了减少回表次数,比如上面讲的覆盖索引,也是为了减少回表,即第一次查询的时候,已经包含了查询字段,就彻底不用回表了!


排序

1.先按第一个字段排序
2.在第一个字段基础之上(即第一个字段的值一样),再按第二个字段排序

image.png

也就是说,第二个字段不是完全有序的,而只是部分有序,什么意思呢?就是刚才说的,如果第一个字段的值一样的情况下,那么第二个字段的值才是排序的,比如示意图里的张三有三个,然后根据年龄排序。

这里要特别注意一下,第一个字段的值必须完全一样,第二个字段的数据才有序。如果仅仅只是姓张,那姓张的总共就匹配到四个数据,但是呢,这个时候年龄字段并不是有序的,可以看到张六的年龄是30,但是却排在第一个。

索引下推是什么?

先看sql:里面包含组合索引(name,age)

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL, //主键索引
  `id_card` varchar(32) DEFAULT NULL, //身份证 //非主键索引
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`) //组合索引,即多字段索引
) ENGINE=InnoDB

再看示意图:组合索引树

image.png

继续看查询sql:按组合索引查询

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

现在问题来了,这个查询sql,到底是怎么查询的呢?

说白了,就是组合索引,到底是怎么查询的呢?

先看旧版本mysql(5.6之前),是怎么查询的,示意图:

image.png

也就是说,先根据name字段,匹配到4个数据,然后再根据id字段,到主键索引树找第二次。问题来了,就是,注意,这里居然没有匹配age字段,也就是说,第一次找的时候,只匹配了name字段,居然没有匹配age字段。什么鬼?不匹配age字段,搞了组合索引,有毛线用?

这里需要特别注意的是,组合索引的第二个字段,为什么没有生效?原因就是因为组合索引是最左匹配原则,一个是中间不能跳过字段,一个是中间不能跳过字符。这里之所以没有生效,就是因为like匹配,说白了就是,like匹配它只匹配了第一个字符(张),然后就跳过了后面的字符,本质是因为like匹配到的值不是完全相等,所以在这种情况想,第二个字段它就不是排序的,既然不是排序的,就用不了索引,所以虽然是组合索引,但是呢,组合索引的第二个字段却失效了。那为什么在我们前面讲多列索引的时候,第二个字段是可以生效的呢?原因也一样,就是因为第一个字段是name='张三',是值相等匹配,而不是like匹配。

总结来说就是,如果是like匹配,而不是值相等,哪怕是组合索引,第二个字段也会失效!

不光光是like会失效,所有的范围匹配都会失效,比如>和<。

记住,只有等值判断,才会让第二个字段生效!

好,旧版本,由于like范围匹配导致组合索引的第二个字段失效,那新版本呢?新版本可以通过索引下推,让第二个字段生效!看示意图:

image.png

在第一次查询的时候:第一个字段,匹配到了四个数据,同时,第二个字段也会生效,即会过滤不匹配的数据,最终目标是减少回表的数据量,而不是像旧版本那样,四条数据全部都要回表,现在新版本只有两个数据需要回表。


总结

其实索引下推这个技术挺傻逼的,不知道是谁发明出来的八股文问题,既然mysql自己都已经解决了,你还研究这个有的没的东西干嘛,说白了就是现在,组合索引,不管是等值判断,还是like范围匹配,第二个字段都会生效,既然都会生效,你还研究个屁啊!神经病,装逼,脑子有问题,吃饱了没事干。

还什么下推不下推的,不就是为了减少回表吗,非得整这么多没用的名词出来,背了又忘,忘了又背。

其实,很少有书讲这个东西,基本上没有,包括高性能mysql,也没有讲,就是装逼用的。

参考

time.geekbang.org/column/arti…

tomandyudezhi.github.io/2020/06/15/…

zhuanlan.zhihu.com/p/121084592