数据结构之:MySQL高级篇之索引的数据结构详解
1.为何使用索引?
假设给数据使用 二叉树 这样的数据结构进行存储,以下图所示
2.索引的优势和劣势
MySQL 官方对索引的定义为: 索引(Index )是帮助 MySQL 高效获得数据的数据结构 。
索引的本质: 索引是数据结构。你可以简单理解为 “ 排好序的快速查找数据结构 ” ,满足特定查找算法。 这些数据结构以某种方式指向数据, 这样就能够在这些数据结构的基础上实现 高级查找算法 。
优点
(1 )类似大学图书馆建书目索引,提高数据检索的效力,下降 数据库的 IO 本钱 ,这也是创建索引最主 要的缘由。
( 2 )通过创建唯一索引,可以保证数据库表中每行 数据的唯一性 。
( 3 )在实现数据的 参考完全性方面,可以 加速表和表之间的连接 。换句话说,对有依赖关系的子表和父表联合查询时, 可以提高查询速度。
( 4 )在使用分组和排序子句进行数据查询时,可以显著 减少查询中分组和排序的时 间 ,下降了 CPU 的消耗。
缺点
( 1 )创建索引和保护索引要 耗费时间 ,并 且随着数据量的增加,所耗费的时间也会增加。
( 2 )索引需要占 磁盘空间 ,除数据表占数据空间之 外,每个索引还要占一定的物理空间, 存储在磁盘上 ,如果有大量的索引,索引文件便可能比数据文 件更快到达最大文件尺寸。
( 3 )虽然索引大大提高了查询速度,同时却会 下降更新表的速度 。当对表 中的数据进行增加、删除和修改的时候,索引也要动态地保护,这样就下降了数据的保护速度。
索引是个好东西,可不能乱建,它在空间和时间上都会有消耗:空间上的代价 :每建立一个索引都要为它建立一棵 B+ 树,每棵 B+ 树的每个节点都是一个数据页,一个页默许会 占用 16KB 的存储空间,一棵很大的 B+ 树由许多数据页组成,那就是很大的一片存储空间。
时间上的代价: 每次对表中的数据进行 增、删、改 操作时,都需要去修改各个 B+ 树索引。而且我们讲过, B+ 树每 层节点都是依照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录,还 是内节点中的记录(也就是不论是用户记录或者目录项记录)都是依照索引列的值从小到大的顺序 而构成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需 要额外的时间进行一些 记录移位 , 页面分裂 、 页面回收 等操作来保护好节点和记录的排序。如果 我们建了许多索引,每一个索引对应的 B+ 树都要进行相关的保护操作,会给性能拖后腿。
3.InnoDB中的索引
在没有索引的情况下,不论是根据主键列或其他列的值进行查找,由于我们其实不能快速的定位到记录所在的页,所以只能 从第一个页 沿着 双向链表 一直往下找,在每个页中根据我们上面的查找方式去查 找指定的记录。由于要遍历所有的数据页,所以这类方式明显是 超级耗时 的。如果一个表有一亿条记录 呢?此时 索引 应运而生。
3.1 设计索引
先建一张表:
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1)
) ROW_FORMAT = Compact;
这个新建的 index_demo 表中有 2 个 INT 类型的列, 1 个 CHAR(1) 类型的列,而且我们规定了 c1 列为主键,这个表使用 Compact 行格式来实际存储记录的。这里我们简化了 index_demo 表的行格式示意图:
我们只在示意图里展现记录的这几个部份:
record_type :记录头信息的一项属性,表示记录的类型, 0 表示普通记录、 2 表示最小记录、 3 表示最大记录、 1 暂时还没用过,下面讲。
next_record :记录头信息的一项属性,表示下一条地址相对本条记录的地址偏移量,我们用 箭头来表明下一条记录是谁。
各个列的值 :这里只记录在 index_demo 表中的三个列,分别是 c1 、 c2 和 c3 。
其他信息 :除上述 3 种信息之外的所有信息,包括其他隐藏列的值和记录的额外信息。
把一些记录放到页里的示意图就是:
我们在根据某个搜索条件查找一些记录时为何要遍历所有的数据页呢?由于各个页中的记录并没有规 律,我们其实不知道我们的搜索条件匹配哪些页中的记录,所以不能不顺次遍历所有的数据页。所以如果 我们 想快速的定位到需要查找的记录在哪些数据页 中该咋办?我们可以为快速定位记录所在的数据页而 建 立一个目录 ,建这个目录一定要完成下边这些事:
下一个数据页中用户记录的主键值一定要大于上一个页中用户记录的主键值。 给所有的页建立一个目录项。
以 页 28 为例,它对应 目录项 2 ,这个目录项中包括着该页的页号 28 和该页中用户记录的最小主键值 5 。我们只需要把几个目录项在物理存储器上连续存储(比如:数组),就能够实现根据主键 值快速查找某条记录的功能了。比如:查找主键值为 20 的记录,具体查找进程分两步:
1. 先从目录项中根据 二分法 快速肯定出主键值为 20 的记录在 目录项 3 中(由于 12 < 20 < 209 ),它对应的页是 页 9 。
2. 再根据前边说的在页中查找记录的方式去 页 9 中定位具体的记录。
至此,针对数据页做的简易目录就弄定了。这个目录有一个别名,称为 索引 。
迭代 1次:目录项纪录的页,我们把前边使用到的目录项放到数据页中的模样就是这样:
从图中可以看出来,我们新分配了一个编号为 30 的页来专门存储目录项记录。这里再次强调 目录项记录 和普通的 用户记录 的 区别点 :
目录项记录 的 record_type 值是 1 ,而 普通用户记录 的 record_type 值是 0 。
目录项记录只有 主键值和页的编号 两个列,而普通的用户记录的列是用户自己定义的,可能包括 很 多列 ,另外还有 InnoDB 自己添加的隐藏列。
了解:记录头信息里还有一个叫 min_rec_mask 的属性,只有在存储 目录项记录 的页中的主键值 最小的 目录项记录 的 min_rec_mask 值为 1 ,其他别的记录的 min_rec_mask 值都是 0 。
相同点: 二者用的是一样的数据页,都会为主键值生成 Page Directory (页目录),从而在依照主键值进行查找时可使用 二分法 来加快查询速度。
现在以查找主键为 20 的记录为例,根据某个主键值去查找记录的步骤就能够大致拆分成下边两步:
1. 先到存储 目录项记录 的页,也就是页 30 中通过 二分法 快速定位到对应目录项,由于 12 < 20 < 209 ,所以定位到对应的记录所在的页就是页 9 。
2. 再到存储用户记录的页 9 中根据 二分法 快速定位到主键值为 20 的用户记录。
迭代 2 次:多个目录项纪录的页
从图中可以看出,我们插入了一条主键值为 320 的用户记录以后需要两个新的数据页:
为存储该用户记录而新生成了 页 31 。 由于本来存储目录项记录的 页 30 的容量已满 (我们前边假定只能存储 4 条目录项记录),所以不得 不需要一个新的 页 32 来寄存 页 31 对应的目录项。
现在由于存储目录项记录的页不止一个,所以如果我们想根据主键值查找一条用户记录大致需要 3 个步 骤,以查找主键值为 20 的记录为例:
1. 肯定 目录项记录页 。我们现在的存储目录项记录的页有两个,即 页 30 和 页 32 ,又由于页 30 表示的目录项的主键值的 范围是 [1, 320) ,页 32 表示的目录项的主键值不小于 320 ,所以主键值为 20 的记录对应的目 录项记录在 页 30 中。
2. 通过目录项记录页 肯定用户记录真实所在的页 。 在一个存储 目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。
3. 在真实存储用户记录的页中定位到具体的记录。
迭代 3 次:目录项记录页的目录页
如图,我们生成了一个存储更高级目录项的 页 33 ,这个页中的两条记录分别代表页 30 和页 32 ,如果用户记录的主键值在 [1, 320) 之间,则到页 30 中查找更详细的目录项记录,如果主键值 不小于 320 的 话,就到页 32 中查找更详细的目录项记录。
我们可以用下边这个图来描写它:
这个数据结构,它的名称是 B+ 树 。
3.2 常见索引概念
索引依照物理实现方式,索引可以分为 2 种:聚簇(聚集)和非聚簇(非聚集)索引。我们也把非聚集索引称为二级索引或辅助索引。
3.2.1 聚簇索引
特点:
1. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
页内 的记录是依照主键的大小顺序排成一个 单向链表 。
各个寄存 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
寄存 目录项记录的页 分为区别的层次,在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。
2. B+ 树的 叶子节点 存储的是完全的用户记录。
所谓完全的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。
优点:
数据访问更快 ,由于聚簇索引将索引和数据保存在同一个 B+ 树中,因此从聚簇索引中获得数据比非聚簇索引更快 。
聚簇索引对主键的 排序查找 和 范围查找 速度非常快 。
依照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以 节省了大量的 io 操作 。
缺点:
插入速度严重依赖于插入顺序 ,依照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影 响性能。因此,对 InnoDB 表,我们一般都会定义一个 自增的 ID 列为主键 。
更新主键的代价很高 ,由于将会致使被更新的行移动。因此,对 InnoDB 表,我们一般定义 主键为 不可更新 。
二级索引访问需要两次索引查找 ,第一次找到主键值,第二次根据主键值找到行数据。
3.2.2 非聚簇索引
概念:回表 我们根据这个以 c2 列大小排序的 B+ 树只能肯定我们要查找记录的主键值,所以如果我们想根据 c2 列的值查找到完全的用户记录的话,依然需要到 聚簇索引 中再查一遍,这个进程称为 回表 。也就 是根据 c2 列的值查询一条完全的用户记录需要使用到 2 棵 B+ 树!
3.2.3 联合索引
我们也能够同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让 B+ 树依照 c2 和 c3 列 的大小进行排序,这个包括两层含义:
先把各个记录和页依照 c2 列进行排序。 在记录的 c2 列相同的情况下,采取 c3 列进行排序
注意一点,以 c2 和 c3 列的大小为排序规则建立的 B+ 树称为 联合索引 ,本质上也是一个二级索引。它的意 思与分别为 c2 和 c3 列分别建立索引的表述是区别的,区别点以下:
建立 联合索引 只会建立如上图一样的 1 棵 B+ 树。
为 c2 和 c3 列分别建立索引会分别以 c2 和 c3 列的大小为排序规则建立 2 棵 B+ 树。
4.InnoDB与MyISAM的索引对照
① 在 InnoDB 存储引擎中,我们只需要根据主键值对 聚簇索引 进行一次查找就可以找到对应的记录,而在 MyISAM 中却需要进行一次 回表 操作,意味着 MyISAM 中建立的索引相当于全部都是 二级索引 。
② InnoDB 的数据文件本身就是索引文件,而 MyISAM 索引文件和数据文件是 分离的 ,索引文件仅保存数 据记录的地址。
③ InnoDB 的非聚簇索引 data 域存储相应记录 主键的值 ,而 MyISAM 索引记录的是 地址 。换句话说, InnoDB 的所有非聚簇索引都援用主键作为 data 域。
④ MyISAM 的回表操作是十分 快速 的,由于是拿着地址偏移量直接到文件中取数据的,反观 InnoDB 是通 过获得主键以后再去聚簇索引里找记录,虽然说也不慢,但或者比不上直接用地址去访问。
⑤ InnoDB 要求表 一定要有主键 ( MyISAM 可以没有 )。如果没有显式指定,则 MySQL 系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这类列,则 MySQL 自动为 InnoDB 表生成一个隐 含字段作为主键,这个字段长度为 6 个字节,类型为长整型。
5.B-Tree和B+Tree的差异
先来看看B-Tree
再来看看B+Tree
1. B+树 有 k 个孩子的节点就有 k 个关键字,也就是孩子数量 = 关键字数;而 B 树中,孩子数量 = 关键字数 +1。
2. B+树 非叶子节点的关键字也会同时存在在子节点中,并且是在子节点中所有关键字的最大(或最小)(比如在页30中的1和5,分别也在页10、页28中出现了);而B树其实不具有这样的特点。
3. B+树 非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放在叶子节点中;而 B 树中, 非叶子节点既保存索引,也保存数据记录 。
4. B+树 所有关键字都在叶子节点出现,叶子节点构成一个有序链表,而且叶子节点本身依照关键字的大小从小到大顺序链接。(要想获得从小到大的结果序列,只需顺次查找叶子节点便可);而B树则一定要进行中序遍历才可以(也就是图中的3、5、8、9、10、12,这类左根右的方式)。
总结
到此这篇关于MySQL高级篇之索引数据结构的文章就介绍到这了,更多相关MySQL索引数据结构内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持!
文章来源:丸子建站
文章标题:数据结构之:MySQL高级篇之索引的数据结构详解
https://www.wanzijz.com/view/58865.html