目录
一、前言
- 索引是帮助高效获取数据排好序的数据结构,任何数据库都会使用到索引,常用索引数据结构有,二叉树、平衡二叉树、Hash表、红黑树、B-Tree、B+Tree,MySQL中会使用B+Tree,这里会对部分数据结构以及MySQL中是如果存储获取数据的原理进行详细说明。
二、B-Tree 和 B+Tree 的区别
-
B-Tree
- B-Tree是一种多路二叉树
- 叶节点具有相同的深度,叶节点的指针为空;
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
-
B+Tree
- B+Tree是B-Tree的变种
- 非叶子节点不存储data,只存储索引(冗余),这样可以提升索引查找性能
- 叶子节点包含所有索引字段
- 叶子节点用指针连接,提高区间访问的性能
B-Tree和B+Tree之间一个很大的不同,是B+Tree的节点上不储存value,只储存key,而叶子节点上储存了所有key-value集合,并且节点之间都是有序的。这样的好处是每一次磁盘IO能够读取的节点更多,也就是树的度(Max.Degree)可以设置的更大一些,因为每次磁盘IO读取的磁盘页数是一定的。例如,每次磁盘IO能够读取1页=4kb,那么省去value的情况下同样一页数据能够读取更多的key,这样就大大减少了磁盘的IO次数。
此外,B+Tree也是排好序的数据结构,数据库中><或者order by等都可以直接依赖这一特性。
MySQL中对于索引使用的主要数据结构也是B+Tree,目的也是在读取数据时能够减少磁盘IO。
三、InnoDB 和 MyISAM 存储引擎索引存储区别
MyISAM
-
在MyISAM储存引擎中,数据和索引文件试试分开储存的,数据存在 表名.MYD 的文件中,索引单独存在 表名.MYI 的文件中。
-
MyISAM索引文件和数据文件是分离的(非聚集),并且主键索引和辅助索引(二级索引)的储存方式是一样的。
InnoDB
-
在InnoDB中,数据和索引文件是合起来储存的,都会存储在 表名.ibd 文件中。
-
InnoDB中索引文件和数据文件是同一个文件(聚集),并且主键索引和二级索引储存方式有所不同,如图所示,二级索引的叶子节点不储存数据,仅储存主键ID。
-
这里注意几点:
-
1、为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
- 如果我们在创建表时不设置主键,InnoDB会自动帮我们从第一列开始筛选一列数据不重复的列做为主键,如果找不到这样的列,就会创建一个隐藏的列(rowid)做为主键,这会增加很多MySQL的工作,所以建议我们在创建InnoDB表时一定要设置主键。
- 如果主键id是无序的,那么很有可能新插入的值会导致当前节点分裂,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。反之,如果每次插入有序,那就会在当前页后面连续写入,写不下就会重新分配一个节点,内存都是连续的,这样效率自然也就最高了
-
2、为什么非主键索引结构叶子节点存储的是主键值?
- 非主键索引的叶子节点存储主键值而非全部数据,主要也是为了一致性和节省空间。如果二级索引储存的也是数据,那么每次插入MySQL都不得不更新每棵索引树,这样就加剧了新增编辑时的性能损耗,并且这样一来空间利用率也不高,必然产生了大量冗余数据
-
四、InnoDB 联合索引底层数据结构
联合索引又叫复合索引,假设我们有一个用户表有5个字段(id、name、age、job、address),我们建立name、age、job这三个字段的联合索引 例如:
`idx_name_age_job` (`name`,`age`,`job`);
索引结构:
-
比较相等时,先比较第一列的值,如果相等,再继续比较第二列,以此类推。
-
了解了联合索引的存储结构,我们就知道了索引最左前缀优化原则是怎么回事了,在使用联合索引时,对于索引列的定义顺序将会影响到最终查询时索引的使用情况。例如联合索引(name,age,job),MySQL会从最左边的列优先匹配,如果最左边的带头大哥name没有使用到,在未使用覆盖索引的情况下,就只能全表扫描。
-
联合底层数据结构思考:MySQL会优先以联合索引第一列匹配,此后才会匹配下一列,如果不指定第一列匹配的值,也就无法得知下一步查询哪个节点。
五、MySQL 中三次磁盘IO最大能检索多少数据
在InnoDB里,每个页面默认16KB,可以通过以下SQL语句查询到,当然这个值是可以调的,既然官方给出这个阈值说明再大的话会影响磁盘IO效率。
-
这里可以看到 MySQL Innodb查询页数据大小为 16384B,即16KB
-
假如:B+Tree高度为3,B+Tree的表都存满了,主键索引的类型为BigInt,大小为8B,指针存储了下个节点的文件地址,大小为6B。最后一层,假设存放的数据data为1K 大小,那么一页里就可以存储16K/14=1170个(主键+指针)。
-
一颗高度为2的B+树能存储的数据为:1170 * 16 = 18720(条)
-
一颗高度为3的B+树能存储的数据为:1170 * 1170 * 16 ≈ 2190(万条)
-
同理,在高度h=4时,总行数=1170 * 1170 * 1170 *16 ≈ 256(亿条)!!!
而且一般来说,MySQL会把 B+Tree 根节点放在内存中,那只需要两次磁盘IO就能检索千万级数据,三次磁盘IO就能检索百亿级数据。