MySQL高级2【索引-B树-InnoDB结构-索引创建原则】尚硅谷
## 【第06章 索引的数据结构】
1. 为什么使用索引
索引是存储引擎用于快速找到数据记录的一种数据结构,就好比一本教课书的目录部分,通过目录中找到对应文章的页码,便可快速定位到需要的文章。MySQL中也是一样的道理,进行数据查找时,首先查看查询条件是否命中某条索引,符合则通过索引查找相关数据,如果不符合则需要全表扫描,即需要一条一条地查找记录,直到找到与条件符合的记录。
对字段Col2添加了索引,就相当于在硬盘上为Col 2维护了一个索引的数据结构,即这个二叉搜索树。二叉搜索树的每个结点存储的是(K,V)结构,key是Col 2,value是该key所在行的文件指针(地址)。比如:该二叉搜索树的根节点就是:(34,0x07)。现在对Col2添加了索引,这时再去查找Col2=89这条记录的时候会先去查找该二叉搜索树(二叉树的遍历查找),读34到内存,89>34;继续右侧数据,读89到内存,89 =- 89;找到数据返回。找到之后就根据当前结点的value快速定位到要查找的记录对应的地址。我们可以发现,只需要查找两次就可以定位到记录的地址,查询速度就提高了。
这就是我们为什么要建索引,目的就是为了减少磁盘I/O的次数,加快查询速率。
2. 索引及其优缺点
2.1 索引概述
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
索引的本质:索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”,满足特定查找算法。这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现高级查找算法
。
2.2 优点
(1)类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本
,这也是创建索引最主要的原因。
(2)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
。
(3)在实现数据的参考完整性方面,可以加速表和表之间的连接
。换句话说,对于有依赖关系的子表和父表联合查询时,可以提高查询速度。
(4)在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间
,降低了CPU的消耗。
2.3 缺点
(1)创建索引和维护索引要耗费时间
,并且随着数据量的增加,所耗费的时间也会增加。
(2)索引需要占磁盘空间
,除了数据表占数据空间之外,每一个索引还要占一定的物理空间存储在磁盘上
,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸。
(3)虽然索引大大提高了查询速度,同时却会降低更新表的速度
。当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。
3. InnoDB中索引的推演
3.1 索引之前的查找
SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;
1.在一个页中的查找
假设目前表中的记录比较少,所有的记录都可以被存放在一个页中,在查找记录的时候可以根据搜索条件的不同分为两种情况:
- 以主键为搜索条件
- 可以在页目录中使用
二分法
快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
- 可以在页目录中使用
- 以其他列作为搜索条件
- 因为在数据页中并没有对非主键建立所谓的页目录,所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录,然后对比每条记录是不是符合搜索条件。很显然,这种查找的效率是非常低的。
2.在很多页中查找
大部分情况下我们表中存放的记录都是非常多的,需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤:
- 定位到记录所在的页。
- 从所在的页内查找相应的记录。
在没有索引的情况下,不论是根据主键列或者其他列的值进行查找,由于我们并不能快速的定位到记录所在的页,所以只能从第一个页
沿着双向链表
一直往下找,在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页,所以这种方式显然是超级耗时
的。
3.2 设计索引(B+树演进)
mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
这个新建的index_demo
表中有2个INT类型的列,1个CHAR(1)类型的列,而且我们规定了c1列为主键,这个表使用Compact
行格式来实际存储记录的。这里我们简化了index_demo表的行格式示意图:(行格式 见第七章InnoDB行格式 有详细讲解)
我们只在示意图里展示记录的这几个部分:
record_type
:记录头信息的一项属性,表示记录的类型,0
表示普通记录、1
表示目录项记录、2
表示最小记录、3
表示最大记录。next_record
:记录头信息的一项属性,表示下一条地址相对于本条记录的地址偏移量,我们用箭头来表明下一条记录是谁。各个列的值
:这里只记录在index_demo
表中的三个列,分别是c1
、c2
和c3
。其他信息
:除了上述3种信息以外的所有信息,包括其他隐藏列的值以及记录的额外信息。
将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样:
把一些记录放到页里的示意图就是:
1. 一个简单的索引设计方案
我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢?因为各个页中的记录并没有规律,我们并不知道我们的搜索条件匹配哪些页中的记录,所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页
中该咋办?我们可以为快速定位记录所在的数据页而建立一个目录
,建这个目录必须完成下边这些事:
-
下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
-
给所有的页建立一个目录项。
所以我们为上边几个页做好的目录就像这样子:
以页28
为例,它对应目录项2
,这个目录项中包含着该页的页号28
以及该页中用户记录的最小主键值5
。我们只需要把几个目录项在物理存储器上连续存储(比如:数组),就可以实现根据主键值快速查找某条记录的功能了。比如:查找主键值为20
的记录,具体查找过程分两步:
-
先从目录项中根据
二分法
快速确定出主键值为20
的记录在目录项3
中(因为 12 < 20 < 209 ),它对应的页是页9
。 -
再根据前边说的在页中查找记录的方式去
页9
中定位具体的记录。
至此,针对数据页做的简易目录就搞定了。这个目录有一个别名,**称为索引**
。
2. InnoDB中的索引方案
① 迭代1次:目录项纪录的页
上边称为一个简易的索引方案,是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储,但是这样做有几个问题:
- InnoDB是使用页来作为管理存储空间的基本单位,最多能保证16KB的连续存储空间,而随着表中记录数量的增多,需要非常大的连续的存储空间才能把所有的目录项都放下,这对记录数量非常多的表是不现实的。
- 我们时常会对记录进行增删,假设我们把页28中的记录都删除了,那意味着目录项2也就没有存在的必要了,这就需要把目录项2后的目录项都向前移动一下,这样牵一发而动全身的操作效率很差。
所以,我们需要一种可以灵活管理所有目录项的方式。我们发现目录项其实长得跟我们的用户记录差不多,只不过目录项中的两个列是主键和页号而已,为了和用户记录做一下区分,我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢?使用记录头信息里的record_type属性,它的各个取值代表的意思如下:
- 0∶普通的用户记录。
- 1︰目录项记录.
- 2∶最小记录.
- 3 : 最大记录
我们把前边使用到的目录项放到数据页中的样子就是这样:
从图中可以看出来,我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录
和普通的用户记录
的不同点:
目录项记录
的record_type
值是1,而普通用户记录
的record_type
值是0。- 目录项记录只有
主键值和页的编号
两个列,而普通的用户记录的列是用户自己定义的,可能包含很多列
,另外还有InnoDB自己添加的隐藏列。 - 了解:记录头信息里还有一个叫
min_rec_mask
的属性,只有在存储目录项记录
的页中的主键值最小的目录项记录
的min_rec_mask
值为1
,其他别的记录的min_rec_mask
值都是0
。
相同点:两者用的是一样的数据页,都会为主键值生成Page Directory
(页目录 [第七章 2.2有讲解] myps:相当于又对目录页增加了一个索引),从而在按照主键值进行查找时可以使用二分法
来加快查询速度。
现在以查找主键为20
的记录为例,根据某个主键值去查找记录的步骤就可以大致拆分成下边两步:
-
先到存储
目录项记录
的页,也就是页30中通过二分法
快速定位到对应目录项,因为 12 < 20 < 209 ,所以定位到对应的记录所在的页就是页9。 -
再到存储用户记录的页9中根据
二分法
快速定位到主键值为20
的用户记录。现在我们查询链路如下”
② 迭代2次:多个目录项纪录的页
从图中可以看出,我们插入了一条主键值为320的用户记录之后需要两个新的数据页:
- 为存储该用户记录而新生成了
页31
。 - 因为原先存储目录项记录的
页30的容量已满
(我们前边假设只能存储4条目录项记录),所以不得不需要一个新的页32
来存放页31
对应的目录项。
现在因为存储目录项记录的页不止一个,所以如果我们想根据主键值查找一条用户记录大致需要3个步骤,以查找主键值为20
的记录为例:
-
确定
目录项记录页
我们现在的存储目录项记录的页有两个,即页30
和页32
,又因为页30表示的目录项的主键值的范围是 [1, 320) ,页32表示的目录项的主键值不小于 320 ,所以主键值为20
的记录对应的目录项记录在页30
中。 -
通过目录项记录页
确定用户记录真实所在的页
。在一个存储目录项记录
的页中通过主键值定位一条目录项记录的方式说过了。 -
在真实存储用户记录的页中定位到具体的记录。
③ 迭代3次:目录项记录页的目录页
如图,我们生成了一个存储更高级目录项的页33
,这个页中的两条记录分别代表页30和页32,如果用户记录的主键值在[1, 320)
之间,则到页30中查找更详细的目录项记录,如果主键值不小于320
的话,就到页32中查找更详细的目录项记录。
我们可以用下边这个图来描述它:
这个数据结构,它的名称是B+树
。
④ B+Tree
不论是存放用户记录的数据页,还是存放目录项记录的数据页,我们都把它们存放到B+树这个数据结构中了,所以我们也称这些数据页为节点。从图中可以看出,我们的实际用户记录其实都存放在B+树的最底层的节点上,这些节点也被称为叶子节点,其余用来存放目录项的节点称为非叶子节点或者内节点,其中B+树最上边的那个节点也称为根节点。
一个B+树的节点其实可以分成好多层,规定最下边的那层,也就是存放我们用户记录的那层为第0
层,之后依次往上加。之前我们做了一个非常极端的假设:存放用户记录的页最多存放3条记录
,存放目录项记录的页最多存放4条记录
。其实真实环境中一个页存放的记录数量是非常大的,假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录
,所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录
,那么:
- 如果B+树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放
100
条记录。 - 如果B+树有2层,最多能存放
1000×100=10,0000
条记录。 - 如果B+树有3层,最多能存放
1000×1000×100=1,0000,0000
条记录。 - 如果B+树有4层,最多能存放
1000×1000×1000×100=1000,0000,0000
条记录。相当多的记录!!!
你的表里能存放100000000000
条记录吗?所以一般情况下,我们用到的B+树都不会超过4层
,那我们通过主键值去查找某条记录最多只需要做4个页面内的查找(查找3个目录项页和一个用户记录页),又因为在每个页面内有所谓的Page Directory
(页目录),所以在页面内也可以通过二分法
实现快速定位记录。
3.3 常见索引概念
索引按照物理实现方式,索引可以分为 2 种:聚簇(聚集)和非聚簇(非聚集)索引。我们也把非聚集 索引称为二级索引或者辅助索引。
1. 聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式(所有的用户记录都存储在了叶子节点),也就是所谓的索引即数据,数据即索引。
术语"聚簇"表示数据行和相邻的键值聚簇的存储在一起。
特点:
-
使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
-
页内
的记录是按照主键的大小顺序排成一个单向链表
。 -
各个存放
用户记录的页
也是根据页中用户记录的主键大小顺序排成一个双向链表
。 -
存放
目录项记录的页
分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
。
-
-
B+树的
叶子节点
存储的是完整的用户记录。所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。
我们把具有这两种特性的B+树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MysQL语句中显式的使用INDEX语句去创建, 因为InnoDB存储引擎会在我们插入数据的时候自动的为我们创建聚簇索引。
优点:
数据访问更快
,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快- 聚簇索引对于主键的
排序查找
和范围查找
速度非常快 - 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以
节省了大量的io操作
。
缺点:
插入速度严重依赖于插入顺序
,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增ID列为主键更新主键的代价很高
,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新二级索引访问需要两次索引查找
,第一次找到主键值,第二次根据主键值找到行数据
限制:
- 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引,而MylSAM并不支持聚簇索引。
- 由于数据物理存储排序方式只能有一种,所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
- ·如果没有定义主键,Innodb会选择非空的唯一索引代替。如果没有这样的索引,Innodb会隐式的定义一个主键来作为聚簇索引。│
- 为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。
2. 二级索引(辅助索引、非聚簇索引)
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用,因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?肯定不能是从头到尾沿着链表依次遍历记录一遍。
答案:我们可以多建几棵B+树,不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则,再建一棵B+树,效果如下图所示:
概念:回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值,所以如果我们想根据c2列的值查找到完整的用户记录的话,仍然需要到聚簇索引
中再查一遍,这个过程称为回表
。也就是根据c2列的值查询一条完整的用户记录需要使用到2
棵B+树!
问题:为什么我们还需要一次回表操作呢?直接把完整的用户记录放到叶子节点不OK吗?
回答 :如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了,相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍,这就有点太浪费存储空间了。
因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录,所以这种B+树也被称为二级索引(英文名secondary index ),或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则,所以我们也称这个B+树是为c2列建立的索引。
总结:非聚簇索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个非聚簇索引。
小结: 聚簇索引与非聚簇索引的原理不同,在使用上也有一些区别:
1.聚簇索引的叶子节点存储的就是我们的数据记录,非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2.一个表只能有一个聚簇索引,因为只能有一种排序存储的方式,但可以有多个非聚簇索引,也就是多个索引目录提供数据检索。
3.使用聚簇索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚簇索引低。
3. 联合索引
严格来说联合索引也属于非聚簇索引
我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按照c2和c3列
的大小进行排序,这个包含两层含义:
- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下,采用c3列进行排序
如图所示,我们需要注意以下几点:
- 每条目录项记录都由c2、c3、页号这三个部分组成,各条记录先按照c2列的值进行排序,如果记录的c2列相同,则按照c3列的值进行排序。
- B+树叶子节点处的用户记录由c2、c3和主键c1列组成。
查询链路如下:
注意一点,以c2和c3列的大小为排序规则建立的B+树称为联合索引
,本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的,不同点如下:
-
建立
联合索引
只会建立如上图一样的1棵B+树。 -
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。
3.4 InnoDB的B+树索引的注意事项
1. 根页面位置万年不动
我们前边介绍B+索引的时候,为了大家理解上的方便,先把存储用户记录的叶子节点都画出来,然后接着画存储目录项记录的内节点,实际上B+树的形成过程是这样的:
- 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的,默认就有)的时候,都会为这个索引创建一个
根节点
页面。最开始表中没有数据的时候,每个B+树索引对应的根节点
中既没有用户记录,也没有目录项记录。 - 随后向表中插入用户记录时,先把用户记录存储到这个
根节点
中。 - 当根节点中的可用
空间用完时
继续插入记录,此时会将根节点中的所有记录复制到一个新分配的页,比如页a
中,然后对这个新页进行页分裂
的操作,得到另一个新页,比如页b
。这时新插入的记录根据键值(也就是聚簇索引中的主键值,二级索引中对应的索引列的值)的大小就会被分配到页a
或者页b
中,而根节点
便升级为存储目录项记录的页。
这个过程特别注意的是:一个B+树索引的根节点自诞生之日起,便不会再移动。这样只要我们对某个表建立一个索引,那么它的根节点的页号便会被记录到某个地方,然后凡是InnoDB
存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点的页号,从而来访问这个索引。
2. 内节点中目录项记录的唯一性
我们知道B+树索引的内节点中目录项记录的内容是索引列+页号
的搭配,但是这个搭配对于二级索引来说有点不严谨。还拿index_demo
表为例,假设这个表中的数据是这样的:
c1 | c2 | c3 |
---|---|---|
1 | 1 | 'u' |
3 | 1 | 'd' |
5 | 1 | 'y' |
7 | 1 | 'a' |
如果二级索引中目录项的内容只是索引号+页号
的搭配的话,那么为c2
列建立索引后的B+树应该长这样:
如果我们想要新插入一行记录,其中c1
、c2
、c3
的值分别是:9
、1
、c
,那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题:由于页3
中存储的目录项记录是由c2列+页号
的值构成的,页3
中的两条目录项记录对应的c2列的值都是1
,那么我们这条新插入的记录到底应该放在页4
中,还是应该放在页5
中啊?答案是:对不起,懵了。
为了让新插入记录能找到自己在哪个页里,我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的:
- 索引列的值
- 主键值
- 页号
也就是我们把主键值也添加到二级索引内节点中的目录项记录了,这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的,所以我们为c2列建立二级索引后的示意图实际上应该是这样子的:
这样我们再插入记录(9, 1, 'c')
时,由于页3
中存储的目录项记录是由c2列+主键+页号
的值构成的,可以先把新记录的c2
列的值和页3
中各目录项记录的c2
列的值作比较,如果c2
列的值相同的话,可以接着比较主键值,因为B+树同一层中不同目录项记录的c2列+主键
的值肯定是不一样的,所以最后肯定能定位唯一的一条目录项记录,在本例中最后确定新记录应该被插入到页5
中。
3. 一个页面最少可以存储2条记录
一个B+树只需要很少的层级就可以轻松存储数亿条记录,查询速度相当不错!这是因为B+树本质上就是一个大的多层级目录,每经过一个目录时都会过滤掉许多无效的子目录,直到最后访问的存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢?那就是目录层级非常非常非常多,而且最后的那个存放真实数据的目录中存放一条记录。费了半天劲只能存放一条真实的用户记录?所以InnoDB的一个数据页至少可以存放两条记录
4. MyISAM中的索引方案
B树索引适用存储引擎如表所示:
索引/存储引擎 | MyISAM | InnoDB | Memory |
---|---|---|---|
B-Tree索引(就是B+Tree) | 支持 | 支持 | 支持 |
即使多个存储引擎支持同一种类型的索引,但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引;而Memory默认的索引是Hash索引。
MyISAM引擎使用B+Tree
作为索引结构,叶子节点的data域存放的是数据记录的地址
。
4.1 MyISAM索引的原理
下图是MyISAM索引的原理图。
我们知道InnoDB中索引即数据,也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了,而MyISAM的索引方案虽然也使用树形结构,但是却将索引和数据分开存储:
- 将表中的记录按照记录的插入顺序单独存储在一个文件中,称之为数据文件。这个文件并不划分为若千个数据页,有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序,所以我们并不能在这些数据上使用二分法进行查找。
- 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值+数据记录地址的组合。
4.2 MyISAM 与 InnoDB对比
MyISAM的索引方式都是“非聚簇”的,与InnoDB包含1个聚簇索引是不同的。
小结两种引擎中索引的区别:
① 在InnoDB存储引擎中,我们只需要根据主键值对聚簇索引
进行一次查找就能找到对应的记录,而在MyISAM
中却需要进行一次回表
操作,意味着MyISAM中建立的索引相当于全部都是二级索引
。
② InnoDB的数据文件本身就是索引文件,而MyISAM索引文件和数据文件是分离的
,索引文件仅保存数据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录主键的值
,而MyISAM索引记录的是地址
。换句话说,InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分快速
的,因为是拿着地址偏移量直接到文件中取数据的,反观InnoDB是通过获取主键之后再去聚簇索引里找记录,虽然说也不慢,但还是比不上直接用地址去访问。
⑤ InnoDB要求表必须有主键
(MyISAM可以没有
)。如果没有显式指定,则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。
小结:
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。比如:
举例1: 知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有二级索引都引用主键索引,过长的主键索引会令二级索引变得过大。
举例2: 用非单调(myps:乱序id,即非自增id)的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时,数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
5. 索引的代价
索引是个好东西,可不能乱建,它在空间和时间上都会有消耗:
- 空间上的代价
每建立一个索引都要为它建立一棵B+树,每一棵B+树的每一个节点都是一个数据页,一个页默认会占用16KB
的存储空间,一棵很大的B+树由许多数据页组成,那就是很大的一片存储空间。
- 时间上的代价
每次对表中的数据进行增、删、改
操作时,都需要去修改各个B+树索引。而且我们讲过,B+树每层节点都是按照索引列的值从小到大的顺序排序
而组成了双向链表
。不论是叶子节点中的记录,还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位
,页面分裂
、页面回收
等操作来维护好节点和记录的排序。如果我们建了许多索引,每个索引对应的B+树都要进行相关的维护操作,会给性能拖后腿。
一个表上索引建的越多,就会占用越多的存储空间,在增删改记录的时候性能就越差。为了能建立又好又少的索引,我们得学学这些索引在哪些条件下起作用的。
6. MySQL数据结构选择的合理性
从MySQL的角度讲,不得不考虑一个现实问题就是磁盘to。如果我们能让索引的数据结构尽量减少硬盘的I/O操作,所消耗的时间也就越小。可以说,磁盘的 I/0操作次数对索引的使用效率至关重要。
查找都是索引操作,一般来说索引非常大,尤其是关系型数据库,当数据量比较大的时候,索引的大小有可能几个G甚至更多,为了减少索引在内存的占用,数据库索引是存储在外部磁盘上的。当我们利用索引查询的时候,不可能把整个索引全部加载到内存,只能逐一加载,那么MySQL衡量查询效率的标准就是磁盘lo次数。
6.0 Hash结构
Hash本身是一个函数,又被称为散列函数,它可以帮助我们大幅提升检索数据的效率。
Hash算是通过某种确定性的算法(比如MD5、SHA1、SHA2、SHA3)将输入转变为输出。相同的输入永远可以得到相同的输出,假设输入内容有微小偏差,在输出中通常会有不同的结果。
举例: 如果你想要验证两个文件是否相同,那么你不需要把两份文件直接拿来比对,只需要让对方把Hash函数计算得到的结果告诉你即可,然后在本地同样对文件进行Hash 函数的运算,最后通过比较这两个结果是否相同,就可以知道这两个文件是否相同。
加速查找速度的数据结构,常见的有两类:
(1)树,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是0(log2N) ;
(2)哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是0(1);
上图中哈希函数h有可能将两个不同的关键字映射到相同的位置,这叫做碰撞,在数据库中一般采用链 接法来解决。在链接法中,将散列到同一槽位的元素放在一个链表中,如下图所示:
实验:体会数组和hash表的查找方面的效率区别
// 算法复杂度为 O(n)
@Test
public void test1(){
int[] arr = new int[100000];
for(int i = 0;i < arr.length;i++){
arr[i] = i + 1;
}
long start = System.currentTimeMillis();
for(int j = 1; j<=100000;j++){
int temp = j;
for(int i = 0;i < arr.length;i++){
if(temp == arr[i]){
break;
}
}
}
long end = System.currentTimeMillis();
System.out.println("time: " + (end - start)); //time: 823
}
//算法复杂度为 O(1)
@Test
public void test2(){
HashSet<Integer> set = new HashSet<>(100000);
for(int i = 0;i < 100000;i++){
set.add(i + 1);
}
long start = System.currentTimeMillis();
for(int j = 1; j<=100000;j++) {
int temp = j;
boolean contains = set.contains(temp);
}
long end = System.currentTimeMillis();
System.out.println("time: " + (end - start)); //time: 5
}
Hash结构效率高,那为什么索引结构要设计成树型呢?
原因1: Hash索引仅能满足(=))(◇)和N查询。如果进行范围查询,哈希型的索引,时间复杂度会退化为o(n);而树型的“有序”特性,依然能够保持o(log2N)的高效率。
原因2: Hash索引还有一个缺陷,数据的存储是没有顺序的,在ORDER BY的情况下,使用Hash索引还需要对数据重新排序。
原因3:对于联合索引的情况,Hash值是将联合索引键合并后一起来计算的,无法对单独的一个键或者几个索引键进行查询。
原因4∶对于等值查询来说,通常Hash索引的效率更高,不过也存在一种情况,就是索引列的重复值如果很多,效率就会降低。这是因为遇到Hash冲突时,需要遍历桶中的行指针来进行比较,找到查询的关键字,非常耗时。所以,Hash索引通常不会用到重复值多的列上,比如列为性别、年龄的情况等。
Hash索引适用存储引擎如表所示:
索引 / 存储引擎 | MyISAM | InnoDB | Memory |
---|---|---|---|
HASH索引 | 不支持 | 不支持 | 支持 |
Hash索引的适用性:
Hash索引存在着很多限制,相比之下在数据库中B+树索引的使用面会更广,不过也有一些场景采用Hash索引效率更高,比如在键值型(Key-Value)数据库中,Redis存储的核心就是Hash表。
MySQL中的Memory存储引擎支持Hash存储,如果我们需要用到查询的临时表时,就可以选择Memory存储引擎,把某个字段设置为Hash 索引,比如字符串类型的字段,进行Hash计算之后长度可以缩短到几个字节。当字段的重复度低,而且经常需要进行等值查询的时候,采用Hash索引是个不错的选择。
另外,InnoDB本身不支持Hash索引,但是提供自适应Hash 索引(Adaptive Hash Index)。什么情况下才会使用自适应Hash 索引呢?如果某个数据经常被访问,当满足一定条件的时候,就会将这个数据页的地址存放到Hash表中。这样下次查询的时候,就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。
采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点,特别是当 B+ 树比较深的时
候,通过自适应 Hash 索引可以明显提高数据的检索效率。
我们可以通过 innodb_adaptive_hash_index 变量来查看是否开启了自适应 Hash,比如:
mysql> show variables like '%adaptive_hash_index';
6.1 二叉搜索树
如果我们利用二叉树作为索引结构,那么磁盘的IO次数和索引树的高度是相关的。
为了提高查询效率,就需要减少磁盘IO数
。为了减少磁盘IO的次数,就需要尽量降低树的高度
,需要把原来“瘦高”的树结构变的“矮胖”,树的每层的分叉越多越好。
6.2 AVL树
如果我们利用二叉树作为索引结构,那么磁盘的IO次数和索引树的高度是相关的。
针对同样的数据,如果我们把二叉树改成M 叉树
(M>2)呢?当 M=3 时,同样的 31 个节点可以由下面的三叉树来进行存储:
6.3 B-Tree
B 树的结构如下图所示:
一个 M 阶的 B 树(M>2)有以下的特性:
-
根节点的儿子数的范围是 [2,M]。
-
每个中间节点包含 k-1 个关键字和 k 个孩子,孩子的数量 = 关键字的数量 +1,k 的取值范围为[ceil(M/2), M]。
-
叶子节点包括 k-1 个关键字(叶子节点没有孩子),k 的取值范围为 [ceil(M/2), M]。
-
假设中间节点节点的关键字为:Key[1], Key[2], …, Key[k-1],且关键字按照升序排序,即 Key[i] <Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围,也就是对应着 k 个指针,即为:P[1], P[2], …,P[k],其中 P[1] 指向关键字小于 Key[1] 的子树,P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树,P[k]指向关键字大于 Key[k-1] 的子树。
-
所有叶子节点位于同一层。
上面那张图所表示的 B 树就是一棵 3 阶的 B 树。我们可以看下磁盘块 2,里面的关键字为(8,12),它有 3 个孩子 (3,5),(9,10) 和 (13,15),你能看到 (3,5) 小于 8,(9,10) 在 8 和 12 之间,而 (13,15)大于 12,刚好符合刚才我们给出的特征。
然后我们来看下如何用 B 树进行查找。假设我们想要查找的关键字是 9
,那么步骤可以分为以下几步:
-
我们与根节点的关键字 (17,35)进行比较,9 小于 17 那么得到指针 P1;
-
按照指针 P1 找到磁盘块 2,关键字为(8,12),因为 9 在 8 和 12 之间,所以我们得到指针 P2;
-
按照指针 P2 找到磁盘块 6,关键字为(9,10),然后我们找到了关键字 9。
你能看出来在 B 树的搜索过程中,我们比较的次数并不少,但如果把数据读取出来然后在内存中进行比较,这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作,消耗的时间比在内存中进行比较所需要的时间要多,是数据查找用时的重要因素。B 树相比于平衡二叉树来说磁盘 I/O 操作要少
,在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低,IO次数足够少,就可以提高查询性能
。
再举例1:
6.4 B+Tree
MySQL官网说明:
B+ 树和 B 树的差异:
-
有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数,而 B 树中,孩子数量 = 关键字数+1。
-
非叶子节点的关键字也会同时存在在子节点中,并且是在子节点中所有关键字的最大(或最小)。
-
非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放在叶子节点中。而 B 树中,
非叶子节点既保存索引,也保存数据记录
。 -
所有关键字都在叶子节点出现,叶子节点构成一个有序链表,而且叶子节点本身按照关键字的大小从小到大顺序链接。
B 树和 B+ 树都可以作为索引的数据结构,在 MySQL 中采用的是 B+ 树。
但B树和B+树各有自己的应用场景,不能说B+树完全比B树好,反之亦然。
思考题:为了减少IO,索引树会一次性加载吗?
1、数据库索引是存储在磁盘上的,如果数据量很大,必然导致索引的大小也会很大,超过几个G。
2、当我们利用索引查询时候,是不可能将全部几个G的索引都加载进内存的,我们能做的只能是:逐一加载每一个磁盘页,因为磁盘页对应着索引树的节点。
思考题:B+树的存储能力如何?为何说一般查找行记录,最多只需1~3次磁盘IO
InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值,因为是估算,为了方便计算,这里的K取值为103。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿条记录。(这里假定一个数据页也存储10^3条行记录数据了)
实际情况中每个节点可能不能填充满,因此在数据库中,
B+Tree的高度一般都在2~4层
。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作
思考题:为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?
1.B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2、B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的节点,而只是叶子结点中关键字的索引。所有任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
思考题:Hash索引与B+树索引的区别
1、Hash索引
不能进行范围查询
,而B+树可以。这是因为Hash索引指向的数据是无序的,而B+树的叶子节点是个有序的链表。2、Hash索引
不支持联合索引的最左侧原则
(即联合索引的部分索引无法使用),而B+树可以。对于联合索引来说,Hash索引在计算Hash值的时候是将索引键合并后再一起计算Hash值,所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时,联合索引无法被利用。3、Hash索引
不支持 ORDER BY 排序
,因为Hash索引指向的数据是无序的,因此无法起到排序优化的作用,而B+树索引数据是有序的,可以起到对该字段ORDER BY 排序优化的作用。同理,我们也无法用Hash索引进行模糊查询
,而B+树使用LIKE进行模糊查询的时候,LIKE后面后模糊查询(比如%结尾)的话就可以起到优化作用。4、
InnoDB不支持哈希索引
6.7 R树
R-Tree在MySQL很少使用,仅支持 geometry数据类型,支持该类型的存储引擎只有myisam、bdb、 innodb、ndb、archive几种。举个R树在现实领域中能够解决的例子:查找20英里以内所有的餐厅。如果 没有R树你会怎么解决?一般情况下我们会把餐厅的坐标(x,y)分为两个字段存放在数据库中,一个字段记 录经度,另一个字段记录纬度。这样的话我们就需要遍历所有的餐厅获取其位置信息,然后计算是否满 足要求。如果一个地区有100家餐厅的话,我们就要进行100次位置计算操作了,如果应用到谷歌、百度 地图这种超大数据库中,这种方法便必定不可行了。R树就很好的解决了这种高维空间搜索问题。它把B 树的思想很好的扩展到了多维空间,采用了B树分割空间的思想,并在添加、删除操作时采用合并、分解 结点的方法,保证树的平衡性。因此,R树就是一棵用来存储高维数据的平衡树。相对于B-Tree,R-Tree 的优势在于范围查找。
各个算法的时间复杂度
如上图:
【第07章 InnoDB数据存储结构】
1. 数据库的存储结构:页
索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都保存在文件上的,确切说是存储在页结构中。另一方面,索引是在存储引擎中实现的,MySQL服务器上的存储引擎
负责对表中数据的读取和写入工作。不同存储引擎中存放的格式
一般不同的,甚至有的存储引擎比如Memory都不用磁盘来存储数据。
由于InnoDB
是MySQL的默认存储引擎
,所以本章剖析InooDB存储引擎的数据存储结构。
1.1 磁盘与内存交互基本单位:页
InnoDB将数据划分为若干个页,InnoDB中页的大小默认为16KB
。
以页
作为磁盘和内存之间交互的基本单位,也就是一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。也就是说,在数据库中,不论读一行,还是读多行,都是将这些行所在的页进行加载。也就是说,数据库管理存储空间的基本单位是页(Page),数据库I/O操作的最小单位是页。一个页中可以存储多个行记录。
记录是按照行来存储的,但是数据库的读取并不以行为单位,否则一次读取(也就是一次I/O操作)只能处理一行数据,效率会非常低。
1.2 页结构概述
页a、页b、页c...页n这些页可以不在物理结构上相连
,只要通过双向链表
相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表
,每个数据页都会为存储在它里边的记录生成一个页目录
,在通过主键查找某条记录的时候可以在页目录中使用二分法
快速定位到对应的槽,然后再遍历该槽对应的分组中的记录即可快速找到指定的记录。
1.3页的大小
不同的数据库管理系统(简称DBMS )的页大小不同。比如在MysQL的InnoDB存储引擎中,默认页的大小是16KB,我们可以通过下面的命令来进行查看:
mysql> show variables like ' %innodb_page_size%’;
1.4 页的上层结构
另外在数据库中,还存在着区(Extent)、段(Segment)和表空间(Tablespace)的概念。行、页、区、段、表空间的关系如下图所示:
区(Extent)是比页大一级的存储结构,在InnoDB存储引擎中,一个区会分配64个连续的页
。因为InnoDB中的页大小默认是16KB,所以一个区的大小是64*16KB=1MB
。
段(Segment)由一个或多个区组成,区在文件系统是一个连续分配的空间(在InnoDB中是连续的64个页),不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在。
当我们创建数据表、索引的时候,就会相应创建对应的段,比如创建一张表时会创建一个表段,创建一个索引时会创建一个索引段。(第二章 mysql的目录结构中有讲到)
表空间(Tablespace)是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。数据库由一个或多个表空间组成,表空间从管理上可以划分为系统表空间
、用户表空间
、撤销表空间
、临时表空间
等。
2. 页的内部结构
页如果按类型划分的话,常见的有数据页(保存B+树节点)、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。
数据柔的16KB大小的存储空间被划分为七个部分,分别是文件头(File Header)、页头(Page Header)、最大最小记录(Infimum+supremum)、用户记录(User Records)、空闲空间(Free Space)、页目录(Page
Directory)和文件尾(File Tailer) 。
页结构的示意图如下所示:
我们可以把这7个结构分成如下3个部分:方便讲解
2.1 第1部分:文件头部和文件尾部
2.1.1 File Header(文件头部)(38字节)
作用:
描述各种页的通用信息。(比如页的编号、其上一页、下一页是谁等)
大小:38字节
名称 | 占用空间大小 | 描述 |
---|---|---|
FIL_PAGE_SPACE_OR_CHKSUM |
4 字节 |
页的校验和(checksum值) |
FIL_PAGE_OFFSET |
4 字节 |
页号 |
FIL_PAGE_PREV |
4 字节 |
上一个页的页号 |
FIL_PAGE_NEXT |
4 字节 |
下一个页的页号 |
FIL_PAGE_LSN | 8 字节 |
页面被最后修改时对应的日志序列位置 |
FIL_PAGE_TYPE |
2 字节 |
该页的类型 |
FIL_PAGE_FILE_FLUSH_LSN | 8 字节 |
仅在系统表空间的一个页中定义,代表文件至少被刷新到了对应的LSN值 |
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID |
4 字节 |
页属于哪个表空间 |
FIL_PAGE_OFFSET(4字节)
:每一个页都有一个单独的页号,就跟你的身份证号码一样,InnoDB通过页号可以唯一定位一个页。FIL_PAGE_TYPE(2字节)
:这个代表当前页的类型。
类型名称 | 十六进制 | 描述 |
---|---|---|
FIL_PAGE_TYPE_ALLOCATED | 0x0000 | 最新分配,还没有使用 |
FIL_PAGE_UNDO_LOG |
0x0002 | Undo日志页 |
FIL_PAGE_INODE | 0x0003 | 段信息节点 |
FIL_PAGE_IBUF_FREE_LIST | 0x0004 | Insert Buffer空闲列表 |
FIL_PAGE_IBUF_BITMAP | 0x0005 | Insert Buffer位图 |
FIL_PAGE_TYPE_SYS |
0x0006 | 系统页 |
FIL_PAGE_TYPE_TRX_SYS | 0x0007 | 事务系统数据 |
FIL_PAGE_TYPE_FSP_HDR | 0x0008 | 表空间头部信息 |
FIL_PAGE_TYPE_XDES | 0x0009 | 扩展描述页 |
FIL_PAGE_TYPE_BLOB | 0x000A | 溢出页 |
FIL_PAGE_INDEX |
0x45BF | 索引页,也就是我们所说的数据页 |
FIL_PAGE_PREV(4字节)和FIL_PAGE_NEXT(4字节)
:InnoDB都是以页为单位存放数据的,如果数据分散到多个不连续的页中存储的话需要把这些页关联起来,FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了,保证这些页之间不需要是物理上的连续,而是逻辑上的连续。FIL_PAGE_SPACE_OR_CHKSUM(4字节)
:代表当前页面的校验和(checksum)。文件头部和文件尾部都有属性:FIL_PAGE_SPACE_OR_CHKSUM
作用:
InnoDB存储引擎以页为单位把数据加载到内存中处理,如果该页中的数据在内存中被修改了,那么在修改后的某个时间需要把数据同步到磁盘中。
但是在同步了一半的时候断电了,造成了该页传输的不完整。
为了检测一个页是否完整(也就是在同步的时候有没有发生只同步一半的尴尬情况),这时可以通过文件尾的校验和(checksum 值)与文件头的校验和做比对,如果两个值不相等则证明页的传输有问题,需要重新进行传输,否则认为页的传输已经完成。
FIL_PAGE_LSN(8字节)
:页面被最后修改时对应的日志序列位置(英文名是:Log Sequence Number)
2.1.2 File Trailer(文件尾部)(8字节)
- 前4个字节代表页的校验和:这个部分是和File Header中的校验和相对应的。
- 后4个字节代表页面被最后修改时对应的日志序列位置(LSN):这个部分也是为了校验页的完整性的,如果首部和尾部的LSN值校验不成功的话,就说明同步过程出现了问题。
2.2 第2部分:空闲空间、用户记录和最小最大记录
2.2.1 Free Space (空闲空间)
我们自己存储的记录会按照指定的行格式
存储到User Records
部分。但是在一开始生成页的时候,其实并没有User Records这个部分,每当我们插入一条记录,都会从Free Space部分,也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分
,当Free Space部分的空间全部被User Records部分替代掉之后,也就意味着这个页使用完了,如果还有新的记录插入的话,就需要去申请新的页
了。
2.2.2 User Records (用户记录)
User Records中的这些记录按照指定的行格式
一条一条摆在User Records部分,相互之间形成单链表
。
2.2.3 Infimum + Supremum(最小最大记录)
记录可以比较大小吗?
是的,记录可以比大小,对于一条完整的记录来说,比较记录的大小就是比较主键
的大小。比方说我们插入的4行记录的主键值分别是:1、2、3、4,这也就意味着这4条记录是从小到大依次递增。
InnoDB规定的最小记录与最大记录这两条记录的构造十分简单,都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的。
这两条记录不是我们自己定义的记录
,所以它们并不存放在页的User Records部分,他们被单独放在一个称为Infimum + Supremum的部分
2.3 第3部分:页目录和页面头部
2.3.1 Page Directory(页目录)
为什么需要页目录?
在页中,记录是以单向链表
的形式进行存储的。单向链表的特点就是插入、删除非常方便,但是检索效率不高
,最差的情况下需要遍历链表上的所有节点才能完成检索。因此在页结构中专门设计了页目录这个模块,专门给记录做一个目录
,通过二分查找法
的方式进行检索,提升效率。
页目录,二分法查找
- 将所有的记录
分成几个组
,这些记录包括最小记录和最大记录,但不包括标记为“已删除”的记录。 - 第 1 组,也就是最小记录所在的分组只有 1 个记录;
最后一组,就是最大记录所在的分组,会有 1-8 条记录;
其余的组记录数量在 4-8 条之间。
这样做的好处是,除了第 1 组(最小记录所在组)以外,其余组的记录数会尽量平分
。 - 在每个组中最后一条记录的头信息中会存储该组一共有多少条记录,作为 n_owned 字段。
页目录用来存储每组最后一条记录的地址偏移量
,这些地址偏移量会按照先后顺序存储
起来,每组的地址偏移量也被称之为槽(slot)
,每个槽相当于指针指向了不同组的最后一个记录。
举例:
现在的page_demo表中正常的记录共有6条,InnoDB会把它们分成两组,第一组中只有一个最小记录,第二组中是剩余的5条记录。如下图:
从这个图中我们需要注意这么几点:
- 现在页目录部分中有两个槽,也就意味着我们的记录被分成了两个组,槽1中的值是112,代表最大记录的地址偏移量(就是从页面的0字节开始数,数112个字节);槽0中的值是99,代表最小记录的地址偏移量。
- 注意最小和最大记录的头信息中的n_owned属性
- 最小记录的n_owned值为1,这就代表着以最小记录结尾的这个分组中只有1条记录,也就是最小记录本身。
- 最大记录的n_owned值为5,这就代表着以最大记录结尾的这个分组中只有5条记录,包括最大记录本身还有我们自己插入的4条记录。
用箭头指向的方式替代数字,这样更易于我们理解,修改后如下
为什么最小记录的n_owned值为1,而最大记录的n_owned值为5呢?
InnoDB规定:对于最小记录所在的分组只能有1条记录,最大记录所在的分组拥有的记录条数只能在1~8条之间,剩下的分组中记录的条数范围只能在是 4~8 条之间。
分组是按照下边的步骤进行的:
- 初始情况下一个数据页里只有最小记录和最大记录两条记录,它们分属于两个分组。
- 之后每插入一条记录,都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽,然后把该槽对应的记录的n_owned值加1,表示本组内又添加了一条记录,直到该组中的记录数等于8个。
- 在一个组中的记录数等于8个后再插入一条记录时,会将组中的记录拆分成两个组,一个组中4条记录,另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。
2.3.2 Page Header(页面头部)
为了能得到一个数据页中存储的记录的状态信息,比如本页中已经存储了多少条记录,第一条记录的地址是什么,页目录中存储了多少个槽等等,特意在页中定义了一个叫Page Header的部分,这个部分占用固定的56个字节,专门存储各种状态信息。
名称 | 占用空间大小 | 描述 |
---|---|---|
PAGE_N_DIR_SLOTS | 2字节 | 在页目录中的槽数量 |
PAGE_HEAP_TOP | 2字节 | 还未使用的空间最小地址,也就是说从该地址之后就是Free Space |
PAGE_N_HEAP | 2字节 | 本页中的记录的数量(包括最小和最大记录以及标记为删除的记录) |
PAGE_FREE | 2字节 | 第一个已经标记为删除的记录的记录地址(各个已删除的记录通过next_record 也会组成一个单链表,这个单链表中的记录可以被重新利用) |
PAGE_GARBAGE | 2字节 | 已删除记录占用的字节数 |
PAGE_LAST_INSERT | 2字节 | 最后插入记录的位置 |
PAGE_DIRECTION | 2字节 | 记录插入的方向 |
PAGE_N_DIRECTION | 2字节 | 一个方向连续插入的记录数量 |
PAGE_N_RECS | 2字节 | 该页中记录的数量(不包括最小和最大记录以及被标记为删除的记录) |
PAGE_MAX_TRX_ID | 8字节 | 修改当前页的最大事务ID,该值仅在二级索引中定义 |
PAGE_LEVEL | 2字节 | 当前页在B+树中所处的层级 |
PAGE_INDEX_ID | 8字节 | 索引ID,表示当前页属于哪个索引 |
PAGE_BTR_SEG_LEAF | 10字节 | B+树叶子段的头部信息,仅在B+树的Root页定义 |
PAGE_BTR_SEG_TOP | 10字节 | B+树非叶子段的头部信息,仅在B+树的Root页定义 |
3. InnoDB行格式(或记录格式)
3.1 指定行格式的语法
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称
3.2 COMPACT行格式
在MySQL 5.1版本中,默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。
3.2.1 变长字段长度列表
MySQL支持一些变长的数据类型,比如VARCHAR(M)、VARBINARY(M)、TEXT类型,BLOB类型,这些数据类型修饰列称为变长字段
,变长字段中存储多少字节的数据不是固定的,所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中,把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位,从而形成一个变长字段长度列表。
注意:这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10),b(15)。那么在变长字段长度列表中存储的长度顺序就是15,10,是反过来的。
3.2.2 NULL值列表
Compact行格式会把可以为NULL的列统一管理起来,存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列,则 NULL值列表也不存在了。
为什么定义NULL值列表?
之所以要存储NULL是因为数据都是需要对齐的,如果没有标注出来NULL值的位置
,就有可能在查询数据的时候出现混乱
。如果使用一个特定的符号
放到相应的数据位表示空置的话,虽然能达到效果,但是这样很浪费空间,所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据,哪些是空数据,格式如下:
- 二进制位的值为1时,代表该列的值为NULL。
- 二进制位的值为0时,代表该列的值不为NULL。
注意:同样顺序也是反过来存放的
3.2.3 记录头信息(5字节)
名称 | 大小(单位:bit) | 描述 |
---|---|---|
预留位1 |
1 | 没有使用 |
预留位2 |
1 | 没有使用 |
delete_mask |
1 | 标记该记录是否被删除 |
mini_rec_mask |
1 | B+树的每层非叶子节点中的最小记录都会添加该标记 |
n_owned |
4 | 表示当前记录拥有的记录数 |
heap_no |
13 | 表示当前记录在记录堆的位置信息 |
record_type |
3 | 表示当前记录的类型,0 表示普通记录,1 表示B+树非叶子节点记录,2 表示最小记录,3 表示最大记录 |
next_record |
16 | 表示下一条记录的相对位置 |
delete_mask
:这个属性标记着当前记录是否被删除,占用1个二进制位。- 值为0:代表记录并没有被删除
- 值为1:代表记录被删除掉了
被删除的记录为什么还在页中存储呢?
你以为它删除了,可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除,是因为移除它们之后其他的记录在磁盘上需要重新排列,导致性能消耗
。所以只是打一个删除标记而已,所有被删除掉的记录都会组成一个所谓的垃圾链表
,在这个链表中的记录占用的空间称之为可重用空间
,之后如果有新记录插入到表中的话,可能把这些被删除的记录占用的存储空间覆盖掉。
min_rec_mask
:B+树的每层非叶子节点中的最小记录都会添加该标记,min_rec_mask值为1。我们自己插入的四条记录的min_rec_mask值都是0,意味着它们都不是B+树的非叶子节点中的最小记录。record_type
:这个属性表示当前记录的类型,一共有4种类型的记录:- 0:表示普通记录
- 1:表示B+树非叶节点记录
- 2:表示最小记录
- 3:表示最大记录
heap_no
:这个属性表示当前记录在本页中的位置。
怎么不见heap_no值为0和1的记录呢?
MySQL会自动给每个页里加了两个记录,由于这两个记录并不是我们自己插入的,所以有时候也称为伪记录
或者虚拟记录
。这两个伪记录一个代表最小记录
,一个代表最大记录
。最小记录和最大记录的heap_no值分别是0和1,也就是说它们的位置最靠前
n_owned
:页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录,作为 n_owned 字段next_record
:记录头信息里该属性非常重要,它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量
。
3.2.4 记录的真实数据
列名 | 是否必须 | 占用空间 | 描述 |
---|---|---|---|
row_id | 否 | 6字节 | 行ID,唯一标识一条记录 |
transaction_id | 是 | 6字节 | 事务ID |
roll_pointer | 是 | 7字节 | 回滚指针 |
一个表没有手动定义主键,则会选取一个Unique键作为主键,如果连Unique键都没有定义的话,则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。
3.3 Dynamic和Compressed行格式
行溢出
InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。
很多DBA喜欢MySQL数据库提供的VARCHAR(M)类型,认为可以存放65535字节。这是真的吗?如果我们使用 ascii字符集的话,一个字符就代表一个字节,我们看看VARCHAR(65535)是否可用。
CREATE TABLE varchar_size_demo(
c VARCHAR(65535)
) CHARSET=ascii ROW_FORMAT=Compact;
结果如下:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
报错信息表达的意思是:MySQL对一条记录占用的最大存储空间是有限制的,除BLOB或者TEXT类型的列之外, 其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节。
这个65535个字节除了列本身的数据之外,还包括一些其他的数据,以Compact行格式为例,比如说我们为了存储一个VARCHAR(M)类型的列,除了真实数据占有空间以外,还需要记录的额外信息。
如果该VARCHAR类型的列没有NOT NULL属性,那最多只能存储65532个字节的数据,因为变长字段的长度占用 2个字节,NULL值标识需要占用1个字节。
CREATE TABLE varchar_size_demo(
c VARCHAR(65532)
) CHARSET=ascii ROW_FORMAT=Compact;
#如果有not null属性,那么就不需要NULL值标识,也就可以多存储一个字节,即65533个字节
CREATE TABLE varchar_size_demo(
c VARCHAR(65533) not null
) CHARSET=ascii ROW_FORMAT=Compact;
通过上面的案例,我们可以知道一个页的大小一般是16KB,也就是16384字节,而一个VARCHAR(M)类型的列就最多可以存储65533个字节,这样就可能出现一个页存放不了一条记录,这种现象称为行溢出。
在Compact和Reduntant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分数据,把剩余的数据分散存储在几个其他的页中进行分页存储,然后记录的真实数据处用20个字节存储指向这些页的地址(当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数),从而可以找到剩余数据所在的页。
这称为页的扩展,举例如下:
我们可以知道一个页的大小一般是16KB,也就是16384字节,而一个VARCHAR(M)类型的列就最多可以存储65533个字节,这样就可能出现一个页存放不了一条记录,这种现象称为行溢出
在Compact和Reduntant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分数据,把剩余的数据分散存储在几个其他的页中进行分页存储
,然后记录的真实数据处用20个字节存储指向这些页的地址(当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数),从而可以找到剩余数据所在的页。这称为页的扩展
。
在MySQL 8.0中,默认行格式就是Dynamic,Dynamic、Compressed行格式和Compact行格式挺像,只不过在处理行溢出数据时有分歧
- Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图,在数据页中只存放20个字节的指针(溢出页的地址),实际的数据都存放在Off Page(溢出页)中。
- Compact和Redundant两种格式会在记录的真实数据处存储一部分数据(存放768个前缀字节)。
4. 区、段和碎片区
4.1 为什么要有区?
B+
树的每一层中的页都会形成一个双向链表,如果是以页为单位
来分配存储空间的话,双向链表相邻的两个页之间的物理位置可能离得非常远
。我们介绍B+树索引的使用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录,然后沿着双向链表一直扫描就可以了,而如果链表中相邻的两个页物理位置离得非常远,就是所谓的随机I/O
。再一次强调,磁盘的速度和内存的速度差了好几个数量级,随机I/O是非常慢
的,所以我们应该尽量让链表中相邻的页的物理位置也相邻,这样进行范围查询的时候才可以使用所谓的顺序I/O
。
引入区
的概念,一个区就是物理位置上连续的64个页
。因为InnoDB中的页的大小默认是16KB,所以一个区的大小是64*16KB=1MB
。在表中数据量大
的时候,为某个索引分配空间的时候就不再按照页的单位分配了,而是按照区为单位分配
,甚至在表中的数据特别多的时候,可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费
(数据不足以填充满整个区),但是从性能角度看,可以消除很多的随机I/O,功大于过
!
4.2 为什么要有段?
对于范围查询,其实是对B+树叶子节点中的记录进行顺序扫描,而如果不区分叶子节点和非叶子节点,统统把节点代表的页面放到申请到的区中的话,进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点
和非叶子节点
进行了区别对待,也就是说叶子节点有自己独有的区,非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段(segment)
,存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段,一个叶子节点段
,一个非叶子节点段
。
除了索引的叶子节点段和非叶子节点段之外,InnoDB中还有为存储一些特殊的数据而定义的段,比如回滚段。所以,常见的段有数据段
、索引段
、回滚段
。数据段即为B+树的叶子节点,索引段即为B+树的非叶子节点。
在InnoDB存储引擎中,对段的管理都是由引擎自身所完成,DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。
段其实不对应表空间中的某一个连续的物理区域,而是一个逻辑上的概念,由若干个零散的页面以及一些完整的区组成。
4.3 为什么要有碎片区?
默认情况下,一个使用InnoDB存储引擎的表只有一个聚簇索引,一个索引会生成2个段,而段是以区为单位申请存储空间的,一个区默认占用1M(64*16KB=1024KB)存储空间,所以默认情况下一个只存在几条记录的小表也需要2M的存储空间么?以后每次添加一个索引都要多申请2M的存储空间么?这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹
的,也就是一个区被整个分配给某一个段,或者说区中的所有页面都是为了存储同一个段的数据而存在的,即使段的数据填不满区中所有的页面,那余下的页面也不能挪作他用。
为了考虑以完整的区为单位分配给某个段对于数据量较小
的表太浪费存储空间的这种情况,InnoDB提出了一个碎片(fragment)区
的概念。在一个碎片区中,并不是所有的页都是为了存储同一个段的数据而存在的,而是碎片区中的页可以用于不同的目的,比如有些页面用于段A,有些页面用于段B,有些页甚至哪个段都不属于。碎片区直属于表空间
,并不属于任何一个段。
所以此后为某个段分配存储空间的策略是这样的:
- 在刚开始向表中插入数据的时候,段是从某个碎片区以单个页面为单位来分配存储空间的。
- 当某个段已经占用了
32个碎片区
页面之后,就会申请以完整的区为单位来分配存储空间。
所以现在段不能仅定义为是某些区的集合,更精确的应该是某些零散的页面
已经一些完整的区
的集合。
4.4 区的分类
区大体上可以分为4种类型:
空闲的区(FREE)
:现在还没有用到这个区中的任何页面。有剩余空间的碎片区(FREE_FRAG)
:表示碎片区中还有可用的页面。没有剩余空间的碎片区(FULL_FRAG)
:表示碎片区中的所有页面都被使用,没有空闲页面。附属于某个段的区(FSEG)
:每一索引都可以分为叶子节点段和非叶子节点段
处于FREE
、FREE_FRAG
以及FULL_FRAG
这三种状态的区都是独立的,直属于表空间。而处于FSEG
状态的区是附属于某个段的。
【第08章 索引的创建与设计原则】
1. 索引的声明与使用
1.1 索引的分类
- 从
功能逻辑
上说,索引主要有 4 种,分别是普通索引、唯一索引、主键索引、全文索引。 - 按照
物理实现方式
,索引可以分为 2 种:聚簇索引和非聚簇索引。 - 按照
作用字段个数
进行划分,分成单列索引和联合索引。
1.普通索引
在创建普通索引时,不附加任何限制条件,只是用于提高查询效率。这类索引可以创建在任何数据类型中,其值是否唯一和非空,要由字段本身的完整性约束条件决定。建立索引以后,可以通过索引进行查询。
例如,在表student的字段name上建立一个普通索引,查询记录时就可以根据该索引进行查询。
2.唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引,在创建唯一性索引时,限制该索引的值必须是唯一的,但元计有空值。在一张数据表里可以有多个唯一索引。
例如,在表student的字段email中创建唯一性索引,那么字段email的值就必须是唯一的。通过唯一性索引,可以更快速地确定某条记录。
3.主键索引
主键索引就是一种特殊的唯一性索引,在唯一索引的基础上增加了不为空的约束,也就是NOT NULL+UNIQUE,一张表里最多只有一个主键索引。
Why?这是由主键索引的物理实现方式决定的,因为数据存储在文件中只能按照一种顺序进行存储。
4.单列索引
在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。
5.多列(组合、联合)索引
多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询,但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如,在表中的字段id、name和gender上建立一个多列索引idx_id_name_gender
,只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。
6.全文索引
全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果
常适合大型数据集,对于小的数据集,它的用处比较小。
使用参数FULLTEXT
可以设置索引为全文索引。在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上,查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。例如,表student的字段information是TEXT类型,该字段包含了很多文字信息。在字段information上建立全文索引后,可以提高查询字段information的速度。
全文索引典型的有两种类型:自然语言的全文索引和布尔全文索引。
- 自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里,相关度是基于匹配的关键词的个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语,匹配时的相关度就越高。相反,非常常见的单词将不会被搜索,如果一个词语的在超过50%的记录中都出现了,那么自然语言的搜索将不会搜索这类词语。
MysoL教据库从3.23.23版开始支持全文索引,但MySQL5.6.4以前只有Myisam支持,5.6.4版本以后innodb才支持,但是官方版本不支持中文分词,需要第三方分词插件。在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词。测试或使用全文索引时,要先看一下自己的MysQL版本、存储引擎和数据类型是否支持全文索引。
随着大数据时代的到来,关系型数据库应对全文索引的需求已力不从心,逐渐被solr、ElasticSearch等专门的搜索引擎所替代。
7.补充:空间索引
使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEONETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值。对于初学者来说,这类索引很少会用到。
小结:
不同的存储引擎支持的索引类型也不一样
InnoDB : 支持B-tree、Full-text等索引,不支持Hash 索引;
MylSAM : 支持B-tree、Full-text等索引,不支持Hash索引;
Memory : 支持B-tree、Hash 等索引,不支持Full-text 索引;
NDB : 支持Hash索引,不支持B-tree、Full-text等索引;
Archive : 不支持B-tree、Hash、Full-text等索引;
1.2 创建索引
MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列,使用ALTER TABLE语句在存在的表上创建索引,或者使用CREATE INDEX语句在已存在的表上添加索引。
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
UNIQUE
、FULLTEXT
和SPATIAL
为可选参数,分别表示唯一索引、全文索引和空间索引;INDEX
与KEY
为同义词,两者的作用相同,用来指定创建索引;index_name
指定索引的名称,为可选参数,如果不指定,那么MySQL默认col_name为索引名;col_name
为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择;length
为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;ASC
或DESC
指定升序或者降序的索引值存储。
1. 创建普通索引
CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
INDEX(year_publication)
);
2. 创建唯一索引
CREATE TABLE test1(
id INT NOT NULL,
name varchar(30) NOT NULL,
UNIQUE INDEX uk_idx_id(id)
);
3. 主键索引
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
);
# 删除主键索引
ALTER TABLE student drop PRIMARY KEY ;
4. 创建单列索引
CREATE TABLE test2(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX single_idx_name(name(20))
);
5. 创建组合索引
CREATE TABLE test3(
id INT(11) NOT NULL,
name CHAR(30) NOT NULL,
age INT(11) NOT NULL,
info VARCHAR(255),
INDEX multi_idx(id,name,age)
);
6. 创建全文索引
CREATE TABLE `papers` (
id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text, PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
在MySQL5.7及之后版本中可以不指定最后的ENGINE了,因为在此版本中InnoDB支持全文索引。
举例2:
创建了一个给title和body字段添加全文索引的表。
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE = INNODB ;
举例3:
CREATE TABLE `papers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
不同于like方式的的查询:
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
全文索引用match+against方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
注意点
- 使用全文索引前,搞清楚版本支持情况;
- 全文索引比 like + % 快 N 倍,但是可能存在精度问题;
- 如果需要全文索引的是大量数据,建议先添加数据,再创建索引。
7. 创建空间索引
空间索引创建中,要求空间类型的字段必须为 非空 。
CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;
2. 在已经存在的表上创建索引
1. 使用ALTER TABLE语句创建索引
ALTER TABLE table_name
ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
2. 使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
1.3 删除索引
1. 使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name;
2. 使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;
2. MySQL8.0索引新特性
2.1 支持降序索引
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
上图中执行show create table ts1
的结果中,a字段是升序 是默认的 不显示,DESC降序则显示
2.2 隐藏索引
在MySQL 5.7版本及之前,只能通过显式的方式删除索引。此时,如果发现删除索引后出现错误,又只能
通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大,或者数据表本身比较
大,这种操作就会消耗系统过多的资源,操作成本非常高。
从MySQL 8.x开始支持隐藏索引(invisible indexes)
,只需要将待删除的索引设置为隐藏索引,使查询优化器不再使用这个索引(即使使用force index(强制使用索引),优化器也不会使用该索引),确认将索引设置为隐藏索引后系统不受任何响应,就可以彻底删除索引。这种通过先将索引设置为隐藏索引,再删除索引的方式就是软删除
。
同时,如果你想验证某个索引删除之后的查询性能影响,就可以暂时先隐藏该索引。
注意:
主键不能被设置为隐藏索引。当表中没有显式主键时,表中第一个唯一非空索引会成为隐式主键,也不能设置为隐藏索引。
索引默认是可见的,在使用CREATETABLE,CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或者INVISIBLE关键词设置索引的可见性。
1. 创建表时直接创建
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);
2. 在已经存在的表上创建
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;
3. 通过ALTER TABLE语句创建
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;
4. 切换索引可见状态
已存在的索引可通过如下语句切换可见状态:
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引
如果将index_cname索引切换成可见状态,通过explain查看执行计划,发现优化器选择了index_cname索引。
注意 当索引被隐藏时,它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏,那么可以将其删除,因为索引的存在会影响插入、更新和删除的性能。
通过设置隐藏索引的可见性可以查看索引对调优的帮助。
5. 使隐藏索引对查询优化器可见
在MySQL 8.x版本中,为索引提供了一种新的测试方式,可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置,使隐藏索引对查询优化器可见。如果 use_invisible_indexes
设置为off(默认),优化器会忽略隐藏索引。如果设置为on,即使隐藏索引不可见,优化器在生成执行计
划时仍会考虑使用隐藏索引。
(1)在MySQL命令行执行如下命令查看查询优化器的开关设置。
mysql> select @@optimizer_switch \G
在输出的结果信息中找到如下属性配置
use_invisible_indexes=off
此属性配置值为off,说明隐藏索引默认对查询优化器不可见。
(2)使隐藏索引对查询优化器可见,需要在MySQL命令行执行如下命令:
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)
SQL语句执行成功,再次查看查询优化器的开关设置。
mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)
此时,在输出结果中可以看到如下属性配置
use_invisible_indexes=on
use_invisible_indexes属性的值为on,说明此时隐藏索引对查询优化器可见。
(3)使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况。
explain select * from classes where cname = '高一2班';
查询优化器会使用隐藏索引来查询数据。
(4)如果需要使隐藏索引对查询优化器不可见,则只需要执行如下命令即可。
mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)
再次查看查询优化器的开关设置
mysql> select @@optimizer_switch \G
此时,use_invisible_indexes属性的值已经被设置为“off”。
3. 索引的设计原则
3.1 哪些情况适合创建索引
1. 字段的数值有唯一性的限制
索引本身可以起到约束的作用,比如唯一索引、主键索引都可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一的
,就可以直接创建唯一性索引
,或者主键索引
。这样可以更快速地通过该索引来确定某条记录。
业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。(来源:Alibaba)
说明:不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但提高查找速度是明显的。
2. 频繁作为 WHERE 查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。
3. 经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者使用 ORDER BY 对数据进行排序的时候,就需要对分组或者排序的字段进行索引
。如果待排序的列有多个,那么可以在这些列上建立组合索引
。
4. UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作,如果对 WHERE 字段创建了索引,就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或删除。如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更新不需要对索引进行维护。
5.DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重,使用 DISTINCT,那么对这个字段创建索引,也会提升查询效率。
6. 多表 JOIN 连接操作时,创建索引注意事项
首先,连接表的数量尽量不要超过 3 张
,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。
其次,对 WHERE 条件创建索引
,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,没有 WHERE 条件过滤是非常可怕的。
最后,对用于连接的字段创建索引
,并且该字段在多张表中的类型必须一致
。
7. 使用列的类型小的创建索引
我们这里所说的类型大小
指的就是该类型表示的数据范围的大小。
- 数据类型越小,在查询时进行的比较操作越快
- 数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以
放下更多的记录
,从而减少磁盘I/O
带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。
这个建议对于表的主键来说更加适用
,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着节省更多的存储空间和更高效的I/O。
8. 使用字符串前缀创建索引
假设我们的字符串很长,那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时,那就意味着在对应的B+树中有这么两个问题:
- B+树索引中的记录需要把该列的完整字符串存储起来,更费时。而且字符串越长,在索引中占用的存储空间越大。
- 如果B+树索引中索引列存储的字符串很长,那在做字符串比较时会占用更多的时间。
我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置,但是能定位到相应前缀所在的位置,然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间,又减少了字符串的比较时间,还大体能解决排序的问题。
例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间,如果只检索字段前面的若干字符,这样可以提高检索速度。
创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引
create table shop( address varchar( 120) not null) ;
alter table shop add index ( address ( 12) );
问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的;截取得少了,重复内容太多,字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?
先看一下字段在全部数据中的选择度:
select count(distinct address) /l count( *) from shop ;
通过不同长度去计算,与全表的选择性对比:
# 区分度计算公式:
count(distinct left(列名, 索引长度))/count(*)
举例:
select count(distinct left(address,10)) / count(*I as sub10,-- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11,-- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12,-- 截取前28个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;
引申另一个问题:索引列前缀对排序的影响
如果使用了索引列前缀,比方说前边只把address列的前12个字符放到了二级索引中,下边这个查询可能就有点儿尴尬了︰
SELECT * FROM shop ORDER BY address LIMIT 12;
因为二级索引中不包含完整的address列信息,所以无法对前12个字符相同,后边的字符不同的记录进行排序,也就是使用索引列前缀的方式无法支持使用索引排序,只能使用文件排序。
拓展:Alibaba《Java开发手册》
【强制
】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上
。
9. 区分度高(散列性高)的列适合作为索引
列的基数
指的是某一列中不重复数据的个数,比方说某个列包含值2,5,8,2,5,8,2,5,8
,虽然有9
条记录,但该列的基数却是3
。也就是说,在记录行数一定的情况下,列的基数越大,该列中的值越分散;列的基数越小,该列中的值越集中。这个列的基数指标非常重要,直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引,为基数太小的列建立索引效果可能不好。
可以使用公式select count(distinct a)/count(*) from t1
计算区分度,越接近1越好,一般超过33%
就算是比较高效的索引了。
拓展:联合索引把区分度高(散列性高)的列放在前面。
举例:
学生表中性别,只有两个值 1或2 区分度基数就是2,这个时候就不适合将性别字段做索引
我们应将类似于id这样区分度高的作为索引,能通过B+树直接定位 或 这个字段没几个一样的 这样从这几个中再找数据就也挺快速的
10. 使用最频繁的列放到联合索引的左侧
11. 在多个字段都要创建索引的情况下,联合索引优于单值索引
3.2 限制索引的数目
在实际工作中,我们也需要注意平衡,索引的数目不是越多越好。我们需要限制每张表上的索引数量,建议单张表索引数量不超过6个
。原因:
- 每个索引都需要占用
磁盘空间
,索引越多,需要的磁盘空间就越大。 - 索引会影响
INSERT、DELETE、UPDATE等语句的性能
,因为表中的数据更改的同时,索引也会进行调整和更新,会造成负担。 - 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的
索引来进行评估
,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,会增加MySQL优化器生成执行计划时间,降低查询性能。
3.3 哪些情况不适合创建索引
1. 在where中使用不到的字段,不要设置索引
2. 数据量小的表最好不要使用索引
举例:略
结论:在数据表中的数据行数比较少的情况下,比如不到 1000 行,是不需要创建索引的。
3. 有大量重复数据的列上不要建立索引
举例1:要在 100 万行数据中查找其中的 50 万行(比如性别为男的数据),一旦创建了索引,你需要先
访问 50 万次索引,然后再访问 50 万次数据表,这样加起来的开销比不使用索引可能还要大。
举例2:假设有一个学生表,学生总数为 100 万人,男性只有 10 个人,也就是占总人口的 10 万分之 1。
学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1,0 代表女性,1 代
表男性。
CREATE TABLE student_gender(
student_id INT(11) NOT NULL,
student_name VARCHAR(50) NOT NULL,
student_gender TINYINT(1) NOT NULL,
PRIMARY KEY(student_id)
)ENGINE = INNODB;
如果我们要筛选出这个学生表中的男性,可以使用:
ELECT * FROM student_gender WHERE student_gender = 1
运行结果(10 条数据,运行时间 0.696s ):
结论:当数据重复度大,比如 高于 10% 的时候,也不需要对这个字段使用索引。
4. 避免对经常更新的表创建过多的索引
5. 不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引
① 冗余索引
举例:建表语句如下
CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
);
我们知道,通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索,再创建一
个专门针对 name 列的索引就算是一个 冗余索引 ,维护这个索引只会增加维护的成本,并不会对搜索有
什么好处。
② 重复索引
另一种情况,我们可能会对某个列 重复建立索引 ,比方说这样:
CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);
我们看到,col1 既是主键、又给它定义为一个唯一索引,还给它定义了一个普通索引,可是主键本身就
会生成聚簇索引,所以定义的唯一索引和普通索引是重复的,这种情况要避免。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~