【MYSQL】索引的原理（B+树实现）和操作

一个普通的小白

已于 2023-09-22 14:00:47 修改

阅读量738

点赞数 1

文章标签：数据库索引

于 2023-05-16 08:00:00 首次发布

本文链接：https://blog.csdn.net/m0_72964546/article/details/130498311

版权

认识磁盘

1.MYSQL与磁盘交互的基本单位

为了提高基本的IO效率， MySQL进行IO的基本单位是16KB

一次使用16KB（称为page）为单位大于磁道的扇面（521B），那么储存MYSQL的数据就会把16KB的数据连续存储在多个扇面上，那么磁头巡道的次数减少（数据是连续存放的），效率得到了提高；

1.1.数据如何存储到磁盘的

MySQL服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间（通常：128MB）,来进行各种缓存。

2.索引的原理

2.1.单个page数据节点

page使用双链表的结构来管理；
单个page数据节点由两个指针和数据构成；

2.2.如何提升单个page节点的查找速度--页目录

给page加上一个目录就可以提高查找速度；
每个目录保存主键值（采用最小的主键值保存）和一个指向数据的指针；
每隔多个就做一个目录；

2.3.多个page添加页目录

这个的数据结构叫做B+树；
叶子节点使用双向列表连接起来的，因为数据会排序，可能插入一个节点这个page已满，需要到下一个节点；

2.3.1.一个非叶子节点可以管理多少个节点；

Q：128MB有多少个page？

A:128*1024/16=8192;

Q:一个page可以管理多少节点？

A：16*1024/8=2048；

那么只需要5个非叶子节点（第一层1个，第二层4个）就可以管理号128MB；

2.4.为什么不用其他的数据结构来设计索引

1.红黑树：它的效率也不低，但是它的树的高度是高于B+树的，所以IO次数更多效率更低；

2.哈希表：哈希表查找单个数据的效率很好，但是如果是范围性查找，查10到100之间的数据，因为它并不会排序那么只能一个个的找，效率并不高；

索引需要树的高度低，来减少IO次数，并且要排序来支持范围性查找

3.聚簇索引

聚簇索引：innodb就是聚簇索引，和B+那个图一样数据在page内；支持事务

非聚簇索引：myisam就是非聚簇索引，就是page内是一个指向数据的指针；效率高

3.1.回表查询

如果innodb建立普通索引：需要查询2次，先在普通索引查找到主键，再使用主键在主键索引中查找数据；这样设置是为了防止数据冗余；

myisam就没有这个问题，myisam是非聚簇索引page保存的是指向数据的指针，所以myisam更适合做一些查找的情况；

4.索引操作

查询索引信息

格式：show index from 表名;

4.1.主键索引

创建主键索引

1.创建主键默认创建索引
create table t1 ( id int unsigned primary key auto_increment );
2.后续添加主键也可以
create table t2 ( id int  );
alter table t2 add primary key(id);

删除主键索引

alter table t1 drop primary key;

4.2.普通索引

创建普通索引

1.
create table t3 (id int primary key auto_increment, name char(5),index(name) );
2. 
create table t3 (id int primary key auto_increment, name char(5) );
alter table t3 add index(name);

删除普通索引