【MySQL】索引_mysql什么是数据库操作的基本单位，2024年最新大数据开发开发快速上手

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新大数据全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip204888 （备注大数据）

正文

问:为什么有主键的时候,数据库在插入数据时要对其进行排序呢,我们按正常顺序插入数据不是也挺好的吗

插入数据时排序的目的,就是优化查询的效率,页内部存放数据的模块,实质上也是一个链表的结构,链表的特点也就是增删快,查询修改慢,所以优化查询的效率是必须的

正是因为有序,在查找的时候,从头到后都是有效查找,没有任何一个查找是浪费的,而且,如果运气好,是可以提前结束查找过程的

---

理解多个Page

通过上面的分析,我们知道,上面页模式中,只有一个功能,就是**在查询某条数据的时候直接将一整页的数据加载到内存中,以减少硬盘IO次数,从而提高性能,**但是,我们也可以看到,现在的页模式内部,实际上是采用了链表的结构,前一条数据指向后一条数据,本质上还是通过数据的逐条比较来取出特定的数据,

如果有1千万条数据,一定需要多个Page来保存1千万条数据,多个Page彼此使用双链表链接起来,而且每个Page内部的数据也是基于链表的,那么,查找特定一条记录,也一定是线性查找,这效率也太低了

页目录

我们在看某一本书的时候,如果我们要看<某一章节>,找到该章节有两种做法

• 从头逐页的向后翻,直到找到目标内容,
• 通过书提供的目录,发现该章节在234页(假设),那么我们便直接翻到234页,同时,查找目录的方案,可以顺序找,不过因为目录肯定少,所以可以快速提高定位

本质上,书中的目录,是多花了纸张的,但是却提高了效率 所以,目录,是一种“空间换时间的做法”

---

提高在单个Page中的查找效率

针对单页情况的页目录：

线性有序的数据可以类比一本书,书的页码从前到后一次排布,而书有目录,目录表明了所在章节的页码范围,在查找指定知识点的时候,可以先从目录找到所在章节,到指定章节再线性查找到指定位置,

针对单页Page我们也可以添加“目录”,如果我们想查找指定位置数据,先找目录到大体位置在线性查找即可,这是一种以空间换时间的做法,比如,我们要查找id=4记录,之前必须线性遍历4次,才能拿到结果,现在直接通过目录2[3],直接进行定位新的起始位置,提高了效率

从目录开始查找就能一次性排除更多数据,当然这一切也是建立在数据有序的基础上,

所以我们也能理解为何通过键值 MySQL 会自动排序？ 可以很方便引入目录, 页内目录可以快速查询内部数据

针对多页情况的页目录

Page之间也是以链表的形式组织的,只能线性查找的话效率太低,如何提高在多个Page中的查找效率呢？

page页相连

1),MySQL 中每一页的大小只有 16KB ,单个Page大小固定,所以随着数据量不断增大, 16KB 不可能存下所有的数据,那么必定会有多个页来存储数据,在单表数据不断被插入的情况下**,**

2)MySQL 会在容量不足的时候,自动开辟新的Page来保存新的数据,然后通过指针的方式,将所有的Page组织起来,

• 需要注意,上面的图,是理想结构,目前要保证整体有序,那么新插入的数据,不一定会在新Page面,这里仅仅做演示

再次新插入的数据不一定会被放在最后一个Page的末尾,也有可能放在中间某个Page的中间位置,如果我们需要靠后的Page,但为了线性遍历,必须要把之前的所有Page导入内存,这样会造成大量IO,就算需要的Page已经在内存中,线性遍历多个Page的方式也是问题,

解决思路就是给多个Page也搭配上“目录”：把多个Page看作多个记录,把Page中的最小记录的键值作为Page的键值,为其标上目录,

• 创建一个Page其中记录用来存储目录项,目录项由键值和指针构成,指向其下Page最小键值的记录,
• 页内目录管理的是记录,这种目录管理的是页,

---

提升查找page页的效率:

可在Page之间,也是需要 MySQL 遍历的,遍历意味着依旧需要进行大量的IO,将下一个Page加载到内存,进行线性检测,这样就显得我们之前的Page内部的目录,有点杯水车薪了,

解决方案,用之前的思路,给Page也带上目录,

• 使用一个目录项来指向某一页,而这个目录项存放的就是将要指向的页中存放的最小数据的键值
• 和页内目录不同的地方在于,这种目录管理的级别是页,而页内目录管理的级别是行,
• 其中,每个目录项的构成是：键值+指针,图中没有画全,

存在一个目录页来管理页目录,目录页中的数据存放的就是指向的那一页中最小的数据,有数据,就可通过比较,找到该访问那个Page,进而通过指针,找到下一个Page,

---

其实目录页的本质也是页,普通页中存的数据是用户数据,而目录页中存的数据是普通页的地址,

• 我们每次检索数据的时候,该从哪里开始呢？虽然顶层的目录页少了,但是还要遍历！不用担心,可以在加目录页
• 这就是传说中的B+树 ,至此,我们已经给我们的表user构建完了主键索引,
• 我们发现,现在查找的Page数一定减少了,也就意味着IO次数减少了,那么效率也就提高了

---

概述总结:

由于MySQL和磁盘交互的基本单位是Page,这些Page叫做数据页,我们只需要将数据保存在每个数据页中即可,加载时直接加载一整个数据页,而数据页与数据页之间通过指针连成双向链表,这样就能够获取前一个或后一个数据页,而每条数据之间通过主键进行排序,在每个数据页中也有一个目录,这样在单个数据页中的查询速度就会加快

在单表数据不断被插入的情况下, MySQL 会在容量不足的时候,自动开辟新的数据页来保存新的数据,然后通过指针的方式,将所有的数据页组织起来,但是当数据页多起来的时候,如果加载每个数据页去遍历检测的话,时间会非常慢,这时可以给每个数据页建立起对应的目录,这就是索引,而索引也是通过Page保存的,我们称之为目录页,目录页只放各个下级Page的最小键值

---

小结

• Page分为目录页和数据页,目录页只放各个下级Page的最小键值,
• 查找的时候,自定向下找,只需要加载部分目录页到内存,即可完成算法的整个查找过程,大大减少了IO次数
• MySQL是一个用户级别的文件系统,所以底层对于所谓的数据库的读写,本质也是通过读取文件完成的! page是固定大小!
• 用户是不是访问表的时候,一定要把所有的数据都够建成为index全量的加入buffer pool中 ? 不需要全量加入的 , 需要时换入,
• 一个表添加了主键,mysql自动形成主键索引,意思是: mysql默认会把这张表的所有数据整体以B+树的形式在buffer_pool当中保存.

不保存任何用户数据,只保存它所管理的所有的page的最小记录ID为什么?

• page都是16KB,只保存其他page的指针结构,就意味着能一次保存更多的地址信息!
• page的大小是固定的,数据量有限,不存数据,就能够存更多的索引信息,目录page就能管理更多的page,否则,目录page管理的数据页太少,整棵树的层数更多更深就意味着,从root到叶子节点经历的page更多,需要更多次IO过程

---

为什么选择B+树,而不是其他数据结构？

数据结构	劣势
链表	链表是线性结构的, 查找的时候只能线性遍历,效率太低
二叉搜索树	在某些场景下可能退化成为线性结构
AVL和红黑树	和B+树相比,B+树的层数更低,每层进行一次IO,树越矮,IO的次数越少,AVL和红黑树相对来说,比B+树更高
Hash	散列随机的算法特征决定了范围查找时效率不行,只能一个一个找
B树	底层无法线性遍历,范围查找效率不高。树体存储数据导致树过高

B树

B+树

B树节点,既有数据,又有Page指针,而B+,只有叶子节点有数据,其他目录页,只有键值和Page指针

• B+树的的非叶子节点只存储索引,叶子节点存储数据（所以B+树能存储更多的索引,并且查询次数也是一样的）
• B+树每个叶子节点都包含了根节点的键值数据,每个叶子节点的关键字从小到大链

为什么选择B+树

B+树非叶子节点不存储数据,这样一个节点就可以存储更多的key,可以使得树更矮,所以IO操作次数更少,B+叶子节点,全部相连,而B没有,叶子节点相连,更便于进行范围查找

• B+树减少了IO次数,效率更高 : 就是减少了磁盘的访问次数,毕竟内存速度要比磁盘快的多
• B+树查询更稳定,因为所有数据放在叶子节点
• B+树范围查询更好,因为叶子节点指向下一个叶子结点

---

没有主键会怎么创建索引

上面的组织形式是以主键为索引的组织形式,也就是先按照主键进行排序放到数据页中,再用目录页将数据页组织成B+树,如果没有设置主键该以谁为索引呢？

mysql当中的表是B+树组织的;根本原因在于,任何一张表,一个用户如果没有创建主键mysq会给这个表默认形成一个主键, 当我们没有设置主键的时候,InnoDB会优先选取一个唯一键作为索引,如果表中连唯一键也没有的话,就会自动为每一条记录添加一个叫做DB_ROW_ID的列作为默认主键,该列是一个6字节的自增数值,随着插入而自增,但是这个主键我们看不到,

为什么推荐使用自增ID作为主键

换句话说,为什么主键必须是自增的,用非自增的（比如学号,身份证号）会怎么样？

由于数据页中的记录是按照主键从小到大进行串联的,自增ID决定了后来插入的记录一定会排列在上一条记录的后面,只需要简单添加next_record指针就可以了；如果当前数据页写满,那就放心地直接插入新的数据页中就可以了,

而非自增的主键则不同,它的大小顺序是不确定的,后来插入的记录有可能（而且概率相当大）插入到上一条记录之前（甚至是当前数据页之前）,这就意味着需要遍历当前数据页的记录（或者先找到相关的数据页）,然后找到自己的位置进行插入；如果当前数据页写满了,只能先找到适合自己位置的数据页,然后在数据页中遍历记录找到自己的合适位置进行插入, 因此使用非自增的主键插入记录花费的时间更长,

---

B+树在哪里

mysql本身就是一个文件系统, 在磁盘上有完整的B+树和数据,在内存中有局部高频被访问的B+树的核心Page

mysql的查找一定会伴生着mysql进行根据B+树进行page的换入和换出

---

二.聚簇索引和非聚簇索引

InnoDB 和 MyISAM两种存储引擎索引的区别:

概念	存储引擎	解释
聚簇索引	InnoDB	把索引和数放在一块
非聚簇索引	MyISAM	把索引和数据分离,叶节点不存储数据,只存指针

MyISAM 最大的特点是,将索引Page和数据Page分离,也就是叶子节点没有数据,只有对应数据的地址,这种用户数据与索引数据分离的索引方案,叫做非聚簇索引,

InnoDB 的数据和索引是放在一起的,这种用户数据与索引数据在一起索引方案,叫做聚簇索引

---

它们之间有如下差别：

1. MyISAM的主键索引（非聚簇索引）的叶子节点存储对应数据的地址；InnoDB 的主键索引（聚簇索引）的叶子节点则是存储用户的整条数据,
2. MyISAM的普通索引其叶子节点也只是存储对应数据的地址,和主键索引没区别；InnoDB 的普通索引其叶子节点存储索引值和对应的主键,想找到对应的数据还需要进行回表操作,
3. 聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多,相比之下,聚簇索引适合排序,非聚簇索引不适合用在排序的场合,因为聚簇索引本身已经是按照物理顺序放置的,排序很快,非聚簇索引则没有按序存放,需要额外消耗资源来排序,

---

三.索引操作

3.1. 创建索引

创建主键索引

创建主键以后,MySQL会自动创建主键索引,

• 第一种方式: 在创建表的时候,直接在字段名后指定primary key

create table user1(id int primary key, name varchar(30));

• 第二种方式：在创建表的最后,指定某列或某几列为主键索引

 create table user2(id int, name varchar(30), primary key(id));

• 第三种方式：创建表以后再添加主键

create table user3(id int, name varchar(30));
alter table user3 add primary key(id); #把id设为主键

---

主键索引的特点：

1. 一个表中,最多有一个主键索引,当然可以是复合主键 (多个列共同当一个主键)
2. 主键索引的效率高（主键不可重复）
3. 创建主键索引的列,它的值不能为null,且不能重复
4. 主键索引的列基本上是int

---

唯一索引的创建

创建唯一键以后,MySQL会自动创建唯一索引,

• 第一种方式:在表定义时,在某列后直接指定unique唯一属性,

create table user4(id int primary key, name varchar(30) unique);

• 第二种方式: 创建表时,在表的后面指定某列或某几列为unique

create table user5(id int primary key, name varchar(30), unique(name));

• 第三种方式 ：创建表以后再添加唯一键

create table user6(id int primary key, name varchar(30)）； 
alter table user6 add unique(name);

唯一索引的特点：

1. 一个表中,可以有多个唯一索引
2. 查询效率高,
3. 如果在某一列建立唯一索引,必须保证这列不能有重复数据,
4. 如果一个唯一索引上指定not null,等价于主键索引 即:unique not null == primary key

---

普通索引的创建

• 第一种方式: 在表的定义最后,指定某列为索引

create table user8(id int primary key, name varchar(20), email varchar(30), index(name));

---

• 第二种方式: 创建完表以后指定某列为普通索引

create table user9(id int primary key, name varchar(20), email varchar(30)); 
alter table user9 add index(name);  #指定某列为普通索引

---

• 第三种方式: 手动创建一个索引名为 idx_name 的索引

create table user10(id int primary key, name varchar(20), email varchar(30));  
create index idx_name on user10(name);

此时查询索引可以发现:

普通索引的特点：

1. 一个表中可以有多个普通索引,普通索引在实际开发中用的比较多,
2. 如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引,

---

3.2. 查询索引

• 第一种方法： show keys from 表名 \G;
• 第二种方法：show index from 表名\G;
• 第三种方法：desc 表名;

---

例子:

mysql> show keys from t4\G;
*************************** 1. row ***************************(第一个主键)
Table: t4             <- 表名
Non_unique: 0         <- 如果索引不能包括重复值则为0,如果可以则为1,也就是平时所说的唯一索引
Key_name: PRIMARY     <- 索引的名字
Seq_in_index: 1		  <- 索引中的列序列号,从1开始
Column_name: id		  <- 索引是那个(索引的列名)
Collation: A		  <- 列以什么方式存储在索引中,大概意思就是字符序
Cardinality: 0     	  <- 基数的意思,表示索引中唯一值的数目的估计值
Sub_part: NULL        <-前置索引的意思,如果列只是被部分地编入索引,则为被编入索引的字符的数目,如果整列被编入索引,则为NULL
Packed: NULL		  <-指示关键字如何被压缩,如果没有被压缩,则为NULL
Null: 			      <-如果列含有NULL,则含有YES
Index_type: BTREE     <- 以B+树的形式构建索引
Comment: 
Index_comment:          <- 注释的意思


mysql> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | MUL | NULL    |       | (MUL)表示索引
| name  | char(10) | YES  | MUL | NULL    |       |
| grade | int(11)  | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+

---

3.3. 删除索引

• 删除主键索引：alter table 表名 drop primary key;
• 其他索引的删除：alter table 表名 drop index 索引名;
  • 索引名就是show keys from 表名中的Key_name 字段

比如：上述手动创建一个索引名为 idx_name 的普通索引

alter table user10 drop index idx_name;

---

第三种方法方法：drop index 索引名 on 表名;

drop index idx_name  on user8;

---

3.4. 全文索引的创建

对文章字段或有大量文字的字段进行检索时,会使用到全文索引

MySQL提供全文索引机制,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文,如果对中文进行全文检索,可以使用sphinx的中文版

例子:创建表并插入数据

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

---


第三种方法方法：`drop index 索引名 on 表名;`

drop index idx_name on user8;

---


### 3.4. 全文索引的创建


对文章字段或有大量文字的字段进行检索时,会使用到全文索引


MySQL提供全文索引机制,**要求表的存储引擎必须是MyISAM**,而且默认的全文索引支持英文,不支持中文,如果对中文进行全文检索,可以使用sphinx的中文版



> 
> 例子:创建表并插入数据
> 
> 
> 


![image-20221023212515040](https://img-blog.csdnimg.cn/img_convert/ae4f9cd661ce8c736288daf054e4a82a.png)



> 


**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）**
[外链图片转存中...(img-TAbRjr3m-1713348209894)]

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**