在 InnoDB 中,表都是根据主键的顺序以索引的形式来存放的,该种存放方式的表称为索引组织表。
InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的。
每一个索引在 InnoDB 里面均对应1棵 B+ 树。
在讲解主键的 B+ 树之前,需要了解1个前置知识点:
操作系统从磁盘读取数据到内存是以磁盘块(block)为基本单位的,位于同一个磁盘块的数据将被一次性读取出来,而不是需要什么取什么,该做法的理论依据就是计算机科学中著名的局部性原理–当一个数据被用到时,其附近的数据通常也会马上被使用。
页(page)是计算机管理存储器的逻辑块,硬件及操作系统通常将主存和磁盘存储区分割为连续大小相等的块,每个存储块称为一页(在大多数操作系统中,一页的大小为4K)。
在 InnoDB 存储引擎中,也有页的概念,默认每页大小为16K,即每次读取数据时都是取的4*4K大小的数据块。
通常,InnoDB 索引的每个 B+ 树节点即为1页(16K)。
假设,我们有1个主键列为ID的表,且ID逐渐递增,表中有字段 name 和 gender。
建表语句为:
create table student(
id int primary key,
name varchar(20) not null,
gender varchar(10) not null
)engine=InnoDB
插入数据
现在我们往 student 表中插入数据:
初始时,数据条数比较少,一个页就可以容纳下,所有只有1个根节点,主键和数据都是保存在根节点中。
假设我们写入10条数据后,Page1 满了,再写入新的数据如何存放呢?
此时需要发生页分裂,产生新的 Page。
具体流程如下:
- 创建新的 Page2,将 Page1 的内容复制到 Page2;
- 创建新的 Page3,将第11条数据放入 Page3;
- Page1 更新索引,此时,Page1 仍为根节点,但变成了1个只存放索引不存放数据的页,并且有2个子节点 Page2 和 Page3。
此时有同学会问,为什么不新建 Page2 作为根节点,然后更新索引,然后 Page1 和 Page3 作为 Page2 的子节点,这样 Page1 的数据就可以不用复制了?
主要原因是InnoDB的根节点是预读到内存中的,其物理地址不建议频繁变更。
随着数据的不断插入,主键的 B+ 树也逐渐枝繁叶茂。
因为我们假设主键是单调递增的,所以在 B+ 树的同一层,数据是写满1页,就创建1个新页继续写,后写入的数据不会影响到前面已经写好的页,每个页的使用率也是最高的。
但如果主键是无序或者随机的,后面插入的数据,可能其键值比之前已写好的页中的键值小,则:
- 数据行待写入的目标页可能已经刷到磁盘上并从缓存中移除,或者还没有被加载到缓存中,InnoDB 在插入时不得不先找到并从磁盘读取目标页到内存中,这将导致大量的随机IO。
- 因为写入是乱序的,InnoDB 不得不频繁的做页分裂操作,以便为新行分配空间,页分裂会涉及大量的数据行移动。
- 由于频繁的页分裂,页也会变得稀疏并被不规则地填充,造成数据碎片。
故使用 InnoDB 时,建议将主键设置为单调递增。
如果我们在建表的时候没有声明主键呢?
则 InnoDB 会将第一个非空且不重复的列作为主键列,如果表中不存在这样的列,则 InnoDB 会生成一个隐形的字段(rowid)作为主键。
查找数据
查找数据的方式也很简单:
- 按照 B+ 树的搜索过程,基于主键从根节点逐步查找到目标数据所在的叶子节点(数据页);
- 在 Page 中检索到具体的数据。
B+ 树节点的搜索过程不再赘述,我们重点看一下,如何在 Page 中检索到具体的数据。
一个 Page 中通常会包含多行数据,在某个页插入新行时,为了减少数据的移动,通常是插入到当前行的后面或者已删除行留下来的空间,所以某一页内的数据并不是完全有序的。
但为了保证数据访问的有序性,在每行记录中均会有一个指向下一行记录的指针,以此构成一条单向有序链表。
如果单个页中包含多条数据,遍历链表可能会花费较多时间,为了提升效率。InnoDB 在遍历 Page 数据的时候,用到了稀疏索引的技术。
- 左边蓝色区域为 Page Directory,由多个 slot 组成,每个 slot 最少包含4个元素,最多包含8各元素。
- 右边区域为数据区域,存储着实际数据。
删除数据
假设我们要删除"6-庞光 男",InnoDB 引擎只会把该条记录标记为删除,如果之后要再插入一个ID为6的记录时,可能会复用这个位置,但磁盘文件的大小并不会缩小。
那如果我们将 Page 上的所有行记录均删除呢?此时,这个数据页可以被复用。
但数据页的复用和数据行的复用不同,数据页由于整页均没有数据的,可以复用到任意一个位置。但数据行删除空出来的位置,新纪录的主键必须在其上一行和下一行主键范围内,否则无法被复用。
如果使用 delete 将整个表的数据删除,也仅仅是所有数据页都会被标记为可复用,但磁盘文件并不会减少。
其实,除了删除数据会造成空洞,插入数据也会造成空洞。
当主键不是单调递增的时候,新插入的记录可能会导致以前的满记录页发生页分裂,原有页的记录会部分分到新页中,从而造成了空洞。
所以,经过大量增删改的表,都是可能"千疮百孔"的,即表的空间使用率会降低,同时也会导致树变得"虚高",降低查询效率。
若要消除表上的空洞,可以通过 alter table student engine=InnoDB 命令来重建表。
除了页分裂,其实 InnoDB 的 B+ 树还存在页合并,指的是如果相邻的2个数据页利用率都很小,系统会把这2个页的数据合并到其中1个页上去,另外1个数据页就被标记为可复用。
聚集索引和非聚集索引
上述以主键ID生成的 B+ 树,其叶子节点的 data 是数据行的完整记录,在 InnoDB 中,其被称为聚集索引(clustered index),也被称为主键索引。
假设我们在上述 student 表上执行下述语句:
alter table student add index name_idx(name);
此时,InnoDB 存储引擎会基于 name 列新建1棵 B+ 树。
在 InnoDB 中,将该类索引称为非聚集索引、普通索引或二级索引(secondary index)。与聚集索引不同的是,非聚集索引的叶子节点的 data 不是完整的行记录,而是对应行记录的主键值。
此时,很容易分析出基于主键索引和普通索引的查询有什么区别?
- 如果查询语句为 select * from student where id=6;
主键查询方式,只需要搜索ID这棵 B+ 树。
- 如果查询语句为 select * from student where name=‘庞光’;
普通索引查询方式,则需要先搜索 name 索引树,拿到ID的值为6以后,再到ID索引树中搜索一次。
上述过程称之为回表。
基于普通索引的查询往往需要多扫描1棵索引树,在应用中应尽量使用主键查询。
覆盖索引
基于普通索引查询一定需要回表吗?
看一下下述语句:
select id from student where name='庞光';
首先会搜索 name 索引树,查询到 ‘庞光’ 所在的叶子节点后,可以得到 ‘庞光’ 对应的 id 值,而我们 select 的列也是 id,由于已经拿到了 id 值,就无需进行回表操作了,这种情况我们称之为覆盖索引。
再看一个查询语句:
select id, gender from student where name='庞光';
如果该类查询需求特别多,可以考虑新建1个联合索引:
alter table student add index name_gender_idx(name, gender);
由于联合索引的叶子节点上同时包含了 id, name, gender,所以上述查询也不会回表。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段
但建立冗余索引来支持覆盖索引是需要占用存储空间的,所以需要进行查询频率和索引维护代价的博弈。
最左前缀原则
B+ 树这类索引结构,可以使用索引的"最左前缀原则"来快速定位记录。
以 (name, age) 这个联合索引来分析:
可以看到,索引项是按照索引定义里面出现的字段顺序来排序的: 先按 name 排序,相同 name 的再按 age 排序,执行效果类似于 order by name, age。
当你的逻辑需求是查询所有名字叫 “张三” 的人时,可以快速定位到 ID4,然后往后遍历即可得到所有的结果。
同样,"where name like ‘张%’"的检索条件,也可以使用上述索引。首先查询到第一个符合条件的记录是 ID3,然后往后遍历,直至条件不符合为止。
现在你就可以理解 “where name like ‘%三’” 这类查询为啥不走索引了吧。
那么,建立联合索引的时候,如何安排索引内的字段顺序呢?
如果既有(a, b)的联合查询,又有基于a, b各自的查询,如何创建索引呢?
有读者会说,简单嘛,直接创建3个索引好了,即index(a)、index(b)和index(a, b),但考虑到已经有了(a, b)这个联合索引,就不需要单独创建index(a)这个索引了,从而减少维护1棵树,节省空间。
基于最左前缀原则,因为查询b的语句无法使用到index(a, b)这个联合索引,所以至少需要创建index(a, b)和index(b)两个索引。
那2个字段谁为a字段,谁为b字段呢,主要考虑的就是空间因素。
比如 name 字段和 age 字段,由于 name 字段占用的空间比 age 字段大,我们优先将 age 字段作为 b,即创建 index(name, age) 和 index(age) 两个索引。
索引下推
以学生表的联合索引 index(name, age) 为例,如果现在有1个需求:检索出表中"名字第一个字是张,而且年龄是10岁的所有男孩",则查询语句为:
select * from student where name like '张%' and age=10 and ismale=1;
在 MySQL5.6 之前,检索过程是首先找到 name 符合 “张%” 约束条件的第一条记录,然后不断往后遍历,一个个回表,到主键索引上取出数据行,再对比字段值。
需要回表4次。
而 MySQL5.6 引入了索引下推优化(index condition pushdown),可以在索引遍历的过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
支持索引下推之后,只需要回表2次。
InnoDB 存储引擎的普通索引叶子节点的 value 为啥是主键值,而不直接保存数据所在的物理地址?
有读者会好奇,假设我普通索引叶子节点的 value 直接是数据所在的物理地址,岂不是就不用回表了,直接去加载数据行就好。
但是会有个问题,就是在数据插入或者删除过程中,会发生数据行在页之间移动,同时,频繁的页分裂或页合并也会导致每行数据所在的页是经常变动的。
如果普通索引的叶子节点直接持有物理地址,则数据行一旦发生变动,则需要将所有涉及到的普通索引的 B+ 树统统得更新一遍,而使用主键值的话,则只需要更新主键所在的 B+ 树即可,相当于主键索引是一个中间层,其统一负责维护数据行和物理地址之间的对应关系,避免了各个普通索引均需要维护这个关系映射(牵一发而动全身)。
InnoDB 普通索引的叶子节点存储的不是行指针,而是主键值,并以此作为指向行数据的指针,这样的策略减少了当出现行移动、页分裂或合并时普通索引的维护工作。使用主键值当做指针可能会让普通索引占用更多的空间,但换来的好处是,InnoDB 在移动数据行时,无需更新所有相关普通索引中的这个"指针"。
那有没有存储引擎的索引叶子节点 value 直接使用数据行的物理地址呢?
答案是有的,比如 MyISAM 存储引擎:
需要注意的是,MyISAM 数据行的存储不是按照主键顺序存放的,而是按照写入的顺序存放的。
所以 MyISAM 也没有主键索引和普通索引之分,因为所有索引的叶子节点均持有数据行的地址,均有拿到完整数据行的能力。
该种模式适合数据行地址不频繁发生变动的场景。
文章的最后,我们留一个小问题,读者可以思考一下:
InnoDB 存储引擎页的大小默认为16K,那么如果单行数据的大小超过16K时,InnoDB 如何处理呢?
本文到此结束,感谢阅读!
参考文献
- MySQL实战45讲(丁奇)
- https://www.cnblogs.com/sujing/p/11110292.html