。

换句话说,对于有依赖关系的子表和父表联合查询时,可以提高查询效率在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间,降低了CPU的消耗

创建索引和维护索引要消耗时间,并且随着数据量的增加,所耗费的时间也会增加索引需要占用磁盘空间,除了数据表占数据空间外,每一个索引还要占一定的物理空间,存储在磁盘上,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸虽然索引大大提高了查询速度,同时却会降低更新表的速度。

当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度

InnoDb

这个新建的index_demo表有2个INT类型的列,1个CHAR类型的列,而且规定了c1位逐渐,这个表使用Compact行格式来实际存储记录,行格式之后会学习到。

以下是简化了行格式的示意图:

CREATE

record_type:记录头信息的一项属性,表示记录的类型,0表示普通记录、2表示最小记录、3表示最大记录、1是目录项next_record:记录头信息的一项属性,表示下一条地址相对于本条记录的地址偏移量,我们用箭头来表明下一条记录是谁。

可以理解为链表各个列的值:这里只记录在index_demo表中的三个列,分别是c1,c2,c3其他信息:除了上述3种信息以外的所有信息,包括其他隐藏列的值以及记录的额外信息

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值

假设:每一个数据页最多能存放三条记录,实际上一个数据页非常大,可以存放好多记录。

向表中插入3条记录,填充数据页

INSERT

VALUES(1,4,u),(3,9,d),(5,3,y);那么这些记录已经按照主键值的大小串联成一个单向链表了

那这时候我们插入一条主键为4的记录,这个数据页已经显示不下了,只能新建一个数据页,而且因为4

5,所以这条记录应该保存在页10中,把主键为5的记录移动到下一个页中,这个过程叫页分裂

比如:查找主键值为20的记录,具体查找过程分两步:先从项3中因为12

是页9再根据前边说的在页中查找记录的方式去页9中定位具体的记录

从图中可以看出来,我们新分配了一个编号为30的页来专门存储项记录和普通的用户记录的不同点:

项记录只有主键值和页的编号两个列,而普通的用户记录的列是用户自己定义的,可能包含很多列

,另外还有InnoDB自己添加的隐藏列了解:记录头信息里还有一个叫min_rec_mask的属性,只有在存储项记录的min_rec_mask值为1

Page

页目录,从而在按照主键值进行查找时可以使用二分法来加快查询速度

的记录为例,根据某个主键值去查找记录的步骤就可以大致拆分成下边两步:先到存储项,因为12

209

,所以定位到对应的记录所在的页就是页9再到存储用户记录的页9中根据二分法

,这个页中的两条记录分别代表页30和页32,如果用户记录的主键值在

[1,

一个B树的节点其实可以分成好多层,规定最下边的那层,也就是存放我们用户记录的那层为第0层,之后依次往上加。

之前我们做了一个非常极端的假设:存放用户记录的页

最多存放3条记录

如果B树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放100条记录如果B树有2层,最多能存放1000×10010,0000条记录如果B树有3层,最多能存放1000×1000×1001,0000,0000条记录如果B树有4层,最多能存放

1000×1000×1000×1001000,0000,0000条记录。

相当多的记录你的表里能存放100000000000条记录吗所以一般情况下,我们用到的B树都不会超过4层

,那我们通过主键值去查找某条记录最多只需要做4个页面内的查找查找3个,所以在页面内也可以通过二分法实现快速定位记录

聚簇索引

使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:

页内的记录是按照主键的大小顺序排成一个单向链表各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表存放项记录的主键大小顺序排成一个双向链表

们把具有这两种特性的B树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。

这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建,InnoDB存储引擎会自动的为我们创建聚簇索引

聚簇索引优点

数据访问更快,因为聚簇索引将索引和数据保存在同一个B树中,因此从聚簇索引中获取数据比非聚簇索引更快聚簇索引对于主键的排序查找和范围查找速度非常快按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作

聚簇索引缺点

插入速度严重依赖插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能,因此对于InnoDB表,我们一般都会定义一个自增的ID列为主键更新主键的代价很高,因为将会导致被更新的行移动。

因此对于InnoDB表,我们一般定义主键为不可更新

对于MySQL数据库,目前只有InnoDB数据引擎支持聚簇索引,而MyISAM并不支持聚簇索引由于数据物理存储排序方式只能由有一种,所以每个MySQL的表只能有一个聚簇索引。

一般情况下就是该表的主键如果没有定义主键,InnoDB会选择非空的唯一索引代替。

如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚簇索引为了充分利用聚簇索引的聚簇的特性,索引InnoDB表的主键列尽量选择有序的顺序id,而不建议用无序的id,比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式(所有的记录都存储在了叶子节点),也就是所谓的索引即数据,数据即索引

二级索引

二级索引也被称为辅助索引、非聚簇索引,上面介绍的聚簇索引只能在搜索条件是主键时才能发挥作用,因为B树的数据都是按照主键进行排序的

在实际开发过程中,我们经常使用别的列作为搜索条件,如果使用该列为搜索条件的频率非常高时,我们就可以考虑使用此列创建一个二级索引,依次来提升搜索的速度

页内的记录是按照c2列的大小顺序排成一个单向链表各个存放用户记录的页也是根据页中用户记录的c2列的大小顺序排成一个双向链表存放项记录的c2列的大小顺序排成一个双向链表

B树的叶子节点存储的并不是完整的用户记录,而只是c2列主键这两个列的值

通过二级索引搜索,需要先从二级索引的B树找到符合条件的主键id,然后再去聚簇索引的B树进行搜索。

这里就会有同学想问了,为什么不在二级索引的B树中存储完整的用户记录呢

如果把完整的用户记录放到叶子节点是可以不用回表,但是太占用地方了,相当于每创建一个索引,就将用户记录都拷贝一份,非常浪费存储空间

聚簇索引的叶子节点存储的就是我们的数据记录,非聚簇索引的叶子节点存储的是数据位置。

非聚簇索引不会影响表的物理存储数据一个表只能由一个聚簇索引,因为只能有一种排序存储方式,但可以有多个非聚簇索引使用聚簇索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚簇索引低。

这是因为修改非聚簇索引,只需要操作当前的B树,而如果修改聚簇索引,不仅要操作当前B树,所有非聚簇索引的叶子节点的id都需要修改

联合索引也是二级索引的一种,如果我们使用了多个列创建索引,例如先按照c2排序,c2相同按照c3排序,那么这就是一个联合索引。

联合索引相较于二级索引,在每一个节点上存储的数据更多了,这里就画一个草图,不再赘述了

根界面位置万年不动

前边介绍B树索引的时候,为了大家理解上的方便,先把存储用户记录的叶子节点都画出来,然后接着画存储目录项记录的内节点,实际上B树的行程过程是这样的:

每当为某个表创建一个B树索引聚簇索引不是认为创建的,默认就有的时候,都会为这个索引创建一个根节点页面,最开始表中没有数据的时候,每个B树索引对应的根节点中既没有用户记录,也没有项记录的页

这个过程特别注意的是:一个B树索引的根节点自诞生之日起,便不会再移动,这样只要我们对某个表建立一个索引,那么它的根节点的页号便会被记录到某个地方,然后凡是InnoDB存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点的页号,从而来访问这个索引。

内节点中项记录的内容是索引列页号的搭配,但是这个搭配对于二级索引来说有点不严谨。

还拿index_demo表为例,假设这个表中的数据是这样的:

‘u’

页号的值构成的,页3中的两条项记录除页号这个字段以外是唯一的。

所以对二级索引的内节点的目录项记录的记录的内容实际上是有三个部分构成的:

也就是我们把主键值也添加到二级索引内节点中的项记录除页号这个字段外是唯一的,所以我们为c2列建议二级索引后的示意图实际上应该是这样子的:

这样我们再插入记录(9,1,c)时,由于页3中存储的项记录的c2l列的值作比较,如果c2列的值相同的话,可以接着比较主键值,因为B树同一层中不同项记录,在本例中最后确定新记录应该被插入页5中

一个页面最少存储2条记录

一个B树只需要很少的层级就可以轻松存储数亿条记录,查询速度相当不错!这是因为B树本质上就是一个大的多层级时都会过滤调许多无效的子。

那如果一个大的是个啥效果呢那就是中,只能存放一条记录。

费半天劲只能存放一条真实的用户记录所以InnoDB的一个数据页至少可以存放两条记录

MyISAM中索引方案

Innodb和MyISAM默认的索引是Btree索引;而Memory默认的索引是Hash索引MyISAM引擎使用BTree作为索引结构,叶子节点的data域存放的是数据记录的地址

如果我们在Col2上建立一个二级索引,则此索引的结构如下图所示:

MyISAM与InnoDB对比

在InnoDB存储引擎中,我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录,而在MyISAM中却需要进行一次回表操作,意味着MyISAM中建立的索引相当于全部都是二级索引InnoDB的数据文件本身就是索引文件,而MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址InnoDB的非聚簇索引data域存储相应记录主键的值,而MyISAM索引记录的是地址。

换句话说,InnoDB的所有非聚簇索引都引用主键作为data域MyISAM的回表操作是十分快速的,因为是拿着地址偏移量直接到文件中取数据的,反观InnoDB是通过获取主键之后再去聚簇索引里找记录,虽然说也不慢,但还是比不上直接用地址去访问InnoDB要求表必须有主键(MyISAM可以没有)如果没有显式指定,则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。

如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型