Mysql索引底层（一）

summer_45

已于 2022-02-26 17:37:33 修改

阅读量468

点赞数 1

分类专栏：数据库文章标签： mysql b树数据结构

于 2022-02-21 18:12:48 首次发布

本文链接：https://blog.csdn.net/summer_45/article/details/123049996

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据库笔记

索引

索引的定义

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

索引数据结构

二叉搜索树🔍

1. 定义

根据二叉搜索树的定义（左小右大），建立索引

key —— 索引的值
value —— 数据项的内存地址

2. 缺点
在key单调的情况下，会退化为链表结构，不使用

二叉搜索树退化为链表

3. 结构图

二叉搜索树的结构

hash结构

1. 定义

对索引进行哈希得到存储位置

2. 特点

•对索引的key进行一次hash计算就可以定位出数据存储的位置

•很多时候Hash索引要比B+ 树索引更高效

•仅能满足 “=”，“IN”，不支持区间查询【使用B+树的原因】

•hash冲突问题

3. 结构图

hash结构图

B树🔍

1. 定义

是一颗多路平衡查找树🔍，节点存储数据和索引

2. 特点

有序性 —— 节点中的数据索引从左到右递增排列
唯一性 —— 所有索引元素不重复
叶子 —— 叶节点具有相同的深度，叶节点指针为空

3. B树作为索引

提升性能 —— B树可以有效降低树的深度带来的I/O读写频繁的问题
数据data绑定在索引中，在查找数据时更方便

MySQL的索引不采用B树

4. 结构图

B树结构

B+树🔍

1. 定义

是一颗多路平衡查找树🔍，非叶子节点只存储索引

MySQL等数据库的索引都采用B+树的数据结构来存储索引

2. 特点

存储量大 —— 非叶子节点不存储data，只存储索引（冗余）可以放置更多的索引
便于区间查找 —— 叶子节点间用双向链表结构，便于区间查找
有序性 —— 节点中的数据索引从左到右递增排列
唯一性 —— 所有索引元素不重复
叶子 —— 叶节点具有相同的深度，叶节点指针为空

3. 读数据步骤

例：查询key=30的数据

将根节点的数据从磁盘加载到内存 一次I/O
在内存中，用二分查找找到key位于下一个页的地址 二分查找
根据页地址，将下一个节点的数据从磁盘加载到内存 一次I/O
重复2，3
找到叶子节点，可以读取数据data

4. InnoDB的页大小

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

MySQL默认页大小

通过B+树存储数据，（假设数据项大小为1KB）可以存储1170*1170*16 个数据项

MySQL会将根节点的索引缓存在内存中，极大提高查询速率，通常查询只需要花费一次磁盘I/O的时间

5. 结构图

B+树结构

存储引擎

MyISAM

1. 定义

MyISAM是MySQL的默认数据库引擎（5.5版之前），索引文件和数据文件是分离的【非聚集】

2. 组成
MyISAM数据表，皆由存储在硬盘上的3个文件所组成

文件命名：【数据表名称+不同扩展名】

.frm－－存储数据表定义
.MYD－－存放真正的数据
.MYI－－存储索引信息

4. 结构图

MyISAM存储引擎下的索引结构

InnoDB

1. 定义

InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎

2. 组成
InnoDB数据表，皆由存储在硬盘上的2个文件所组成

文件命名：【数据表名称+不同扩展名】

.frm－－存储数据表定义
.ibd－－存储数据和索引

4. 特点

B+Tree —— 数据表文件是按照B+Tree组织的索引结构文件
叶节点 —— 叶子节点包含完整的数据记录
主键索引 —— 表必须建主键
非主键索引 —— 非主键索引结构叶子节点存储的是主键值【一致性】

5. 结构图
主键索引的例子

非主键索引的例子

索引分类

聚集索引

定义

其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。【通过双向链表来实现】一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。

新华字典，聚集索引就像是拼音目录

查数据

聚集索引通过key查找查找到具体数据项

非聚集索引

定义

因为索引文件和数据文件是分离的，索引的逻辑顺序与磁盘上的物理存储顺序不同

非聚簇索引（普通索引）的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致

查数据
1. 非聚集索引先通过key来查询到记录主键的值
2. 根据记录主键的值来查询具体数据项【回表】

联合索引

1. 定义

联合索引即是由多个字段组成的一条索引。

2. 联合索引怎么使用？

根据最左前缀原理来使用

索引最左前缀原理

1. 定义

走联合索引时，条件要从左到右依次满足，不可缺少

2. 例子

建立联合索引

idx_a_b_c (a,b,c) USING BTREE

SQL 一

select  * from test where a = '333' and b = '333' and c = '333';

走联合索引，因为有bc存在的情况下，a存在

SQL 二

select  * from test where  b = '333' and c = '333';

不会走联合索引，因为bc存在的情况下，a不存在

SQL 三

select  * from test where a = '333' and b = '333';

走联合索引，因为b存在的情况下，a存在

3. 总结

所谓最左前缀原理，就是where条件下必须有联合索引的第一个字段，否则不会走联合索引。

问题

Q：为什么要使用索引？

数据随机存储 —— 表中的记录是随机存储在磁盘中，不一定是连续的，读取数据，要和磁盘做一次I/O交互
快速定位数据 —— 根据索引，可以不通过全表扫描，快速定位到数据

Q：B树和B+树区别？

性能 —— B+树在磁盘I/O的时候，不需要加载数据项，只需要加载索引项；而B树需要加载数据和索引，B+树性能更好
区间查找 —— 当需要区间查找时，B+树可以根据叶子节点的双向链表结构来进行查找，比B树性能更好

Q：为什么InnoDB表建议建立主键，并且推荐整型的自增主键？

主键
- InnoDB的ibd文件需要通过主键来建立B+树的索引结构
- 如果没有主键，InnoDB会扫描每一列并选取一个not null且unique列来作为索引
- 如果找到符合要求的列，则默认生成一个内置6字节长的ROWID列来作为索引
自增主键
- 使用自增主键
  - 每次插入新的记录时，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页
- 使用非自增主键
  - 每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置，为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。