欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

深入讲解MySQL Innodb索引的原理

程序员文章站 2022-05-24 15:02:38
引言 回想四年前,我在学习mysql的索引这块的时候,老师在讲索引的时候,是像下面这么说的 索引就像一本书的目录。而当用户通过索引查找数据时,就好比用户通过目录查询某章...

引言

回想四年前,我在学习mysql的索引这块的时候,老师在讲索引的时候,是像下面这么说的

索引就像一本书的目录。而当用户通过索引查找数据时,就好比用户通过目录查询某章节的某个知识点。这样就帮助用户有效地提高了查找速度。所以,使用索引可以有效地提高数据库系统的整体性能。

嗯,这么说其实也对。但是呢,大家看完这种说法,其实可能还是觉得太抽象了!因此呢,我还想再深入的细说一下,所以就有了此文!

需要说明的是,我说的内容只在mysql的innodb引擎中是成立的。在sql server、oracle、mysql的mysiam引擎中的正确性,不一定成立!

innodb是 mysql最常用的存储引擎,了解innodb存储引擎的索引对于日常工作有很大的益处,索引的存在便是为了加速数据库行记录的检索。

什么是索引?

索引(index)翻译为一个目录,用于快速定位我们想要找的数据的位置。例如:我们把一个数据库比作一本书,而索引(index)就是书中的目录,此刻要找到书的某个感兴趣的内容,我们一般是不会整本书翻完再去确认该内容在哪里,而是通过书的目录,定位到该内容章节所在页数,最后直接翻到该页面。

我们来看看在数据库中的索引:

全表扫描 vs 索引扫描

以字典为例,全表扫描就是如果我们查找某个字时,那么通读一遍新华字典,然后找到我们想要找到的字,而跟全表扫描相对应的就是索引查找,索引查找就是在表的索引部分找到我们想要找的数据具体位置,然后会到表里面将我们想要找的数据全部查出。

ok,废话不多说,开始啰嗦!

正文

索引的科普

先引进聚簇索引和非聚簇索引的概念!

我们平时在使用的mysql中,使用下述语句

create [unique|fulltext|spatial] index index_name
 [using index_type]
 on tbl_name (index_col_name,...)
 
index_col_name:
 col_name [(length)] [asc | desc]

创建的索引,如复合索引、前缀索引、唯一索引,都是属于非聚簇索引,在有的书籍中,又将其称为辅助索引(secondary index)。在后文中,我们称其为非聚簇索引,其数据结构为b+树。

那么,这个聚簇索引,在mysql中是没有语句来另外生成的。在innodb中,mysql中的数据是按照主键的顺序来存放的。那么聚簇索引就是按照每张表的主键来构造一颗b+树,叶子节点存放的就是整张表的行数据。由于表里的数据只能按照一颗b+树排序,因此一张表只能有一个聚簇索引。

在innodb中,聚簇索引默认就是主键索引。

这个时候,机智的读者,应该要问我

如果我的表没建主键呢?

回答是,如果没有主键,则按照下列规则来建聚簇索引

没有主键时,会用一个唯一且不为空的索引列做为主键,成为此表的聚簇索引如果没有这样的索引,innodb会隐式定义一个主键来作为聚簇索引。

ps:大家还记得,自增主键和uuid作为主键的区别么?由于主键使用了聚簇索引,如果主键是自增id,,那么对应的数据一定也是相邻地存放在磁盘上的,写入性能比较高。如果是uuid的形式,频繁的插入会使innodb频繁地移动磁盘块,写入性能就比较低了。

索引原理介绍

先来一张带主键的表,如下所示,pid是主键

pid name birthday
5 zhangsan 2016-10-02
8 lisi 2015-10-04
11 wangwu 2016-09-02
13 zhaoliu 2015-10-07

画出该表的结构图如下

深入讲解MySQL Innodb索引的原理

如上图所示,分为上下两个部分,上半部分是由主键形成的b+树,下半部分就是磁盘上真实的数据!那么,当我们, 执行下面的语句

select * from table where pid='11'

那么,执行过程如下

深入讲解MySQL Innodb索引的原理

如上图所示,从根开始,经过3次查找,就可以找到真实数据。如果不使用索引,那就要在磁盘上,进行逐行扫描,直到找到数据位置。显然,使用索引速度会快。但是在写入数据的时候,需要维护这颗b+树的结构,因此写入性能会下降!

ok,接下来引入非聚簇索引!我们执行下面的语句

create index index_name on table(name);

此时结构图如下所示

深入讲解MySQL Innodb索引的原理

大家注意看,会根据你的索引字段生成一颗新的b+树。因此, 我们每加一个索引,就会增加表的体积, 占用磁盘存储空间。然而,注意看叶子节点,非聚簇索引的叶子节点并不是真实数据,它的叶子节点依然是索引节点,存放的是该索引字段的值以及对应的主键索引(聚簇索引)。

如果我们执行下列语句

select * from table where name='lisi'

此时结构图如下所示

深入讲解MySQL Innodb索引的原理

通过上图红线可以看出,先从非聚簇索引树开始查找,然后找到聚簇索引后。根据聚簇索引,在聚簇索引的b+树上,找到完整的数据!

什么情况不去聚簇索引树上查询呢?

还记得我们的非聚簇索引树上存着该索引字段的值么。如果,此时我们执行下面的语句

select name from table where name='lisi'

此时结构图如下

深入讲解MySQL Innodb索引的原理

如上图红线所示,如果在非聚簇索引树上找到了想要的值,就不会去聚簇索引树上查询。还记得,博主在《select的正确姿势》提到的索引问题么:

当执行select col from table where col = ?,col上有索引的时候,效率比执行select * from table where col = ? 速度快好几倍!

看完上面的图,你应该对这句话有更深层的理解了。

那么这个时候,我们执行了下述语句,又会发生什么呢?

create index index_birthday on table(birthday);

此时结构图如下

深入讲解MySQL Innodb索引的原理

看到了么,多加一个索引,就会多生成一颗非聚簇索引树。因此,很多文章才说,索引不能乱加。因为,有几个索引,就有几颗非聚簇索引树!你在做插入操作的时候,需要同时维护这几颗树的变化!因此,如果索引太多,插入性能就会下降!

总结

讲到这里,大家应该清楚的明白索引的原理了!可能细节方面还不够严谨,但是我觉得一个研发,理解到这里可以了,够用了,毕竟我们也不是专业的dba。
希望大家有所收获!