MySQL

46-MySQL-索引的数据结构

2022-09-29  本文已影响0人  紫荆秋雪_文

一、为什么要使用索引

MySQL中数据是存储在文件系统的，数据是以何种形式保存，决定了取得时要以何种方式获取

MySQL执行流程图.png

1、安装顺序保存

如果现在要找7这一行，需要从1开始一行一行找，直到找到7为止，这样等于7次IO操作，这样读取是很耗时的

安装顺序保存.png

2、使用 二叉树 数据结构进行存储

二叉树数据结构.png

二、索引

1、索引概论

2、索引的优点

3、索引的缺点

三、InnoDB中的索引

1、索引设计

新建 book 表，表中有 2 个 INT 类型的列，1个VARCHAR类型的列，并且规定 id列为主键，使用 Compact 行格式来实际存储记录

CREATE TABLE `book`
(
    `id`   INT NOT NULL AUTO_INCREMENT COMMENT '主键',
    `page` INT NOT NULL DEFAULT '1' COMMENT '页数',
    `name` VARCHAR(200) DEFAULT NULL COMMENT '书名',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  ROW_FORMAT = COMPACT
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='书籍';

2、MySQL 的页

MySQL中的数据是一条一条记录来存储的，并且会把满足一定大小（16k）的数据保存到一个页中

在根据某个搜索条件查询一些记录时为什么要遍历所有的数据源？因为各个页中的记录并没有规律，我们并不知道与搜索条件匹配的数据在哪些页中，所以不得不依次遍历所有的数据页。所以如果我们 想快速的定位到需要查找的记录在哪些数据页 中该怎么办？我们可以为快速定位记录所在数据页而 建立一个目录，建立这个目录必须要完成以下事

SELECT *
FROM book
WHERE id = 3;

3、数据项

随着数据的增多，一页数据无法保存下来所有数据，所以需要把数据保存到新页，当有2个页时，如果查找id=5时的数据时，需要从第一页中的左侧开始依次向右侧遍历查询，如果在第一页中没有找到就在第2页中再从左到右遍历继续寻找，直到遍历完所有数据。这样遍历全部数据效率太低，为了解决这个问题引入了数据项

4、通过id来查询数据

SELECT *
FROM book
WHERE id = 8;

5、索引

通过上面的分析查找，渐渐形成了一个数型的目录页，这个目录有个别名，称为 索引。

6、小结 image.png

7、B+Tree

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，
之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页 最多存放3条记录 ，存放目录项
记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录
的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存
放 1000条目录项记录

三、常见的索引概念

索引按照物理实现方式，索引可以分为2种：聚簇索引和非聚簇索引。通常也把非聚簇索引称为二级索引或者辅助索引

1、聚簇索引

1.1、聚簇特点

1.2、聚簇优点

1.3、聚簇缺点

1.4、聚簇限制

四、二级索引（辅助索引、非聚簇索引）

聚簇索引 只能在搜索条件是 主键值 时才能发挥作用，因为 B+树中的数据都是按照主键进行排序的。如果想用别的列作为搜索条件该怎么办？肯定不能是从头到尾沿着链表依次遍历记录一遍。我们可以为改搜索条件创建一个 B+树，不同的B+树中的数据采用不同的排序规则。如以 页数page列的大小为数据页、页中记录的排序规则

1、以page列的大小为数据页建立二级索引B+树 二级索引.png 

SELECT *
FROM book
WHERE page = 560;

2、该二级索引的B+树与聚簇索引的B+树的不同

3、回表概念

根据这个以C2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据C2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查询一遍，这个过程就称为 回表。也就是根据 C2列的值查询一条完整的用户记录需要使用到 2 棵B+树

把完整记录保存到叶子节点这样的设计 太占用地方了，相当于每创建一个棵 B+ 树都需要把所有的用户记录再都拷贝一遍，这就太浪费存储空间了

4、小结：聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别

五、联合索引-特殊的二级索引

除了以主键做索引以及使用一个非主键列做索引外，还可以使用多个非主键列一起做索引，我们称为联合索引或复合索引。如我们想让 C2列和C3列的大小进行排序，这个包含两层含义

1、联合索引（C2列+C3列） 联合索引.png

2、InnoDB的B+树索引的注意事项

2.1、根页面位置万年不动

2.2、内节点中目录项记录的唯一性

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

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

六、MyISAM中的索引

主键索引.png 非主键索引.png

1、 MyISAM 与 InnoDB对比

2、 索引的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会
占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间

每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每
层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序
而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿

上一篇 下一篇

猜你喜欢

热点阅读