Mysql索引底层数据结构

良辰美景好时光

于 2024-03-10 11:25:17 发布

阅读量1k

点赞数 10

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_45740503/article/details/136590943

版权

mysql 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

一、数据结构

1.1.索引的本质

索引是帮助Mysql高效获取数据的排好序的数据结构

1.2.MySQl特点

查询效率高
支持数据量大，在千万级以上数据量时也要保持查询的稳定性
对范围查询友好

1.3.索引数据结构

- 二叉树
- 红黑树
- B-Tree
- B+Tree
- Hash表

数据结构网址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
在这里插入图片描述

平时我们的插入数据主键都是自增有序的，当使用二树树插入数据时，树的高度增加，会退化成一个链表结构，查询效率低。
红黑树是颗平衡二叉树，解决了二叉树退化成链表结构问题，但当数据量过大，树的高度增加，查询效率逐渐降低。
因此，树的高度决定了磁盘IO读写的次数，也就是查询效率。

1.4.B-Tree结构

优点：
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
缺点：
范围查询不友好

1.5.B+Tree结构

B+Tree(B-Tree变种)
- 非叶子节点不存储data，只存储索引(冗余)，一个节点可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点存放所有数据
- 叶子节点使用互相指向指针连接，提高区间访问的性能，方便进行范围查询，从左到右遍历即可查询

1.6.查看mysql文件页大小（16K）

-- 查看mysql文件页大小（16K）
SHOW GLOBAL STATUS like 'Innodb_page_size';

在这里插入图片描述

1.7.为什么mysql页文件默认16K？

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；
再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，
那么一页里就可以存储16K/14=1170个(主键+指针)
那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，
一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级条）

千万级数据量的表，查询数据也最多只要经历3次IO，而且根节点其实常驻在MySQL内存中。

1.8.Hash结构

Hash
- 对索引的key进行一次hash计算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+ 树索引更高效
- 仅能满足 “=”，“IN”，不支持范围查询
- hash冲突问题

在这里插入图片描述

二、存储引擎

2.1.InnoDB

2.1.1.聚集索引

聚集索引：叶子结点存储完整的数据，主键索引即数据文件；比非聚集索引效率更高，减少了一次回表操作。

InnoDB索引实现(聚集)
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引-叶节点包含了完整的数据记录

在这里插入图片描述

2.1.2.为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

首先，MySQL会找表中是否有唯一索引，如果有，就以唯一索引作为主键；然后用这一列的数据来组织你这个表的所有数据
如果没有唯一索引，那么将添加隐藏字段rowid作为主键，帮你自动维护这整张表的B+数的数据结构
整形作为主键相比字符型可以节省数据页的空间，构建索引 b+ 树时，为了保证索引的有序性，使用整形可以避免页分裂，在索引中查找数据时，减少比较的性能。
因为索引结构 b+ 树，具有有序的特性，如果主键不是自增的，在进行增删数据的时候，会判断数据应该存放的位置，进行插入和删除，为了保持平衡，会对数据页进行分裂等操作移动数据，严重影响性能，所以主键需要是自增的，插入时，插入在索引数据页最后。

2.1.3.为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

一致性：如果二级索引也存储完整的数据，假如修改数据，需要同时修改主键索引又要修改二级索引数据，很繁琐。
节省存储空间

2.1.4.二级索引

通过其他字段查询数据，比如给age字段建立索引
除主键索引之外的索引，称为二级索引或者辅助索引
二级索引同样是颗B+树，与主键索引不同点是：二级索引的叶子节点并非存储完整的数据，而是存储主键，节省存储空间
二级索引又分为普通索引和唯一索引

2.1.5.磁盘文件存储表信息

table.frm: 表结构文件
table.idb: 索引数据文件

CREATE TABLE `test_innodb_lock` (
  `a` int(11) NOT NULL,
  `b` varchar(255) DEFAULT NULL,
  KEY `idex_a` (`a`),
  KEY `idex_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述

2.1.6.查找id=18

图中空白部分存储的为下个结点的磁盘地址，如根结点[15-56]和[56-77]之间的空白点存储的就是指向最左边子结点的磁盘地址。

在这里插入图片描述

1、首先，将根结点加载到内存中(后续根结点常驻内存)
2、二分查找id=30的位置，定位在15-56之间，读取下一个结点的磁盘地址
3、加载结点到内存，继续二分查找，定位在15-20之间，读取磁盘地址
4、加载叶子结点，找到id为18的记录
5、如果是通过二级索引查找，则继续使用主键回表查找主键索引

2.2.MyISAM

2.2.1.非聚集索引

MyISAM索引文件和数据文件是分离的(非聚集)
非聚集索引：叶子结点存储的是指向数据的磁盘地址，索引和数据分离。
在这里插入图片描述

2.2.2.磁盘文件存储表信息

table.frm: 表结构文件信息
table.MYI: 索引文件
tbale.MYD: 数据文件

CREATE TABLE `test_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;