初始MYSQL数据库（5）—— 索引

我要学编程(ಥ_ಥ)

于 2024-09-17 21:50:42 发布

阅读量432

点赞数 7

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

本文链接：https://blog.csdn.net/2301_80854132/article/details/142283270

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

找往期文章包括但不限于本期文章中不懂的知识点：

个人主页：我要学编程(ಥ_ಥ)-CSDN博客

所属专栏： MYSQL

索引的概念

MySQL中的索引是一种数据结构，它可以帮助数据库高效地查询、更新数据表中的数据。索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。
MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如
汉语字典的目录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的目录（索引）快速查找到需要的字。

为什么要使用索引呢？索引可以让数据库更加高效地查询、更新数据。

索引选择的数据结构

我们前面知道了MySQL中的索引是一种数据结构，但到底使用的是什么数据结构呢？现在我们就来探讨一下。

首先，上场的是最优秀的数据结构：哈希表。通过学习哈希表，我们知道其存取的时间复杂度是O(1)，没有哪一种数据结构快过它。但是由于存在哈希冲突和不能实现范围查找的问题（数据库中的数据可不是一星半点的），最终被淘汰下场了。

接着，上场的是二叉搜索树。这个既不会存在哈希冲突，还能实现范围查找，并且时间复杂度还能达到O(log N)，但可惜的是由于其不能确保树高，并且在特殊情况下的数据是一棵单分支的树，造成其效率低下，最终也被淘汰下场。

再接着就是AVL树和红黑树两兄弟来了，在汲取上次教训的基础上，这两兄弟实现了升级和改版之后，确实保证了树高（红黑树不算严格保证树高）。但是由于在插入数据时要进行频繁的旋转和在数据量非常大的情况下，树高还是不能保证（父节点最多只能有两个孩子）。因此最终也是很遗憾地被淘汰了。

注意：由于数据是存储在磁盘中，如果想要获取数据的话，就得需要去进行磁盘的IO，而磁盘的IO效率是十分低下的，因此在设计索引时，应当减少磁盘的IO。而我们在获取二叉树的数据时，每次都得去进行磁盘的IO，并且一次磁盘的IO只获取一层的节点数据（内存较小，无法一次性把磁盘中的数据全部读取到内存中），因此当树高非常高时（数据量过大），在查找数据的过程中，磁盘的IO次数也就非常多，这也就直接导致了查找的效率十分低下了。

通过上面的标准，AVL树和红黑树两兄弟继续奋发图强进行改造和升级。既然你说树的层数过高导致效率低下，那么我每个父节点就允许可以有若干个孩子节点，这样就可以减少层数啦！这就是B树，也被称为N叉树。可惜这个面试官还是太挑了（毕竟人家MySQL是"大公司"嘛），最终B树没有被选上，而是选择它的加强版B+树。我们下面就来看看这个B+树比B树好在哪里。

B+树有以下特点：

1、非叶子节点并未储存数据，只是把当作索引用的，叶子结点才是存储的真实数据；

2、叶子节点之间构成了一个双向链表；

3、查找任意数据的时间复杂度都是相同的。因为所有的数据都是存储在叶子结点，即查找数据都是查找到叶子结点。

MySQL中的页的相关知识

在学习页之前，得先知道 .ibd 文件，.ibd 文件是 InnoDB存储引擎创建表时，产生的独立表空间文件名后缀。而每一个独立表空间文件都是有很多的页组成的。页是内存与磁盘交互的最小单元，默认大小为 16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的。页也分为很多种：数据页（存储真实数据的）、索引页（存储索引信息的）......这些页内部是由页头、页尾、页主体（由很多数据行组成）组成的。页头中存储了上一页号和下一页号的地址，也就是页与页之间构成了双向链表的关系。其实B+树中的节点就是由若干个页组成的。页主体中除了存储真实数据的数据行之外，还有两个特殊的数据行：最大行与最小行。这两个行是被当成链表的单链表的头和尾使用的。即页主体的数据行其实是一个单向链表。而最小行就被当成了单链表的头，最大行就被当成了单链表的尾。并且当页主体中的数据行也会进行分组（一组中当数据超过8条时，便会重新开始分配新的组），而最小行会被单独当作一组，最大行是在最后一组（但不是单独的）。并且随着分组的创建也会产生一个页目录（用于对数据行进行更快的查找），这个页目录是由槽组成的，即每个分组对应着一个槽。槽中记录的是对应组的最后一个数据行的索引信息。最后页主体中还有一个数据页头，记录的是当前页保存数据的相关信息。即保存的主体页的相关数据信息。

上面都是为索引做的铺垫，下面我们才开始索引的学习。

索引的分类

主键索引

当在表中定义了一个主键 primary key时，会自动创建一个索引，InnoDB会使用其作为主键索引，也被称为聚集索引或者聚簇索引。

普通索引

为了提高查询的效率，针对查询平凡的列创建的索引。

唯一索引

当在表中定义了一个唯一键 unique 时，会自动创建唯一索引。唯一索引在普通索引的基础上避免了值重复出现。

注意：

1、每创建一个索引，都会生成一棵索引树，同样也是会占用磁盘空间的。并且索引树越多，对增删改的效率影响就越大。因为要去维护索引树的相关信息。

2、当一个表中没有创建主键时，会用第一个unique 和 not null 的列作为聚集索引。如果找不到的话，InnoDB会自增一个列为聚集索引，但是我们是无权使用的。

非聚集索引

除了聚集索引以外的索引都是被称为非聚集索引的或者是二级索引。

回表查询与索引覆盖

当我们使用 select 语句进行查询操作时，如果在索引信息部分（聚集索引）找到了要查询的信息，那么就没必要继续查询了，直接返回查询的结果即可。这就叫索引覆盖。而当索引信息中没有我们要查找的信息时，就需要去存储完整数据的地方去查询相关信息，这就叫回表查询。

索引的使用

知道了索引的分类和相关信息，接下来，我们就开始创建和使用索引。

创建主键索引

前面我们学习了，创建主键、外键、唯一键时都会自动生成索引，因此在创建这三种索引时，都只是在创建其相关约束。

-- 方式一
create table student (
	id bigint primary key auto_increment,
	name varchar(50)
);

-- 方式二
create table student2 (
	id bigint auto_increment,
	name varchar(50),
	primary key (id) -- 这里指定多个列，使用复合主键的方式
);

-- 方式三
create table student3 (
	id bigint,
	name varchar(50)
);
alter table student3 add primary key (id);
alter table student3 modify id bigint auto_increment;

第三种方式，是一种修改表结构的方式。现在就来详细学习一下。

使用 alter 修改表结构

增加列

语法：

ALTER TABLE table_name
ADD column_name datatype;

例如：

-- 往student表中添加class_id这样一列，数据类型为bigint
alter table student add class_id bigint;

删除列

语法：

ALTER TABLE table_name
DROP column_name;

例如：

-- 将student表中列名为class_id的列删除
alter table student drop class_id：

修改列的数据类型

语法：

ALTER TABLE table_name
MODIFY column_name datatype;

例如：

-- 将student表中列名为name的列，其数据类型修改为 varchar(20)
alter table student modify name varchar(20);

修改表名

语法：

ALTER TABLE old_table_name 
RENAME TO new_table_name;

例如：

-- 将表名为student的表，其名字修改为stu
alter table student rename to stu;

添加约束

语法：

-- 添加主键约束和唯一约束比较简单，都是下面的语法
ALTER TABLE table_name 
ADD PRIMARY KEY (column_name);

ALTER TABLE table_name 
ADD UNIQUE (column_name);

例如：

-- 为student表的id列添加为主键
alter table student add primary key (id);

删除约束

语法：

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name;

例如：

-- 将student表中的主键约束删除（包括复合主键）
alter table student drop constraint primary key;

创建唯一索引

-- 方式一
create table class (
  id bigint primary key auto_increment,
  name varchar(30) unique
);

-- 方式二
create table class2 (
  id bigint primary key auto_increment,
  name varchar(30),
  unique key (name) -- 注意这里的写法是unique key
);

-- 方式三
create table class3 (
  id bigint primary key auto_increment,
  name varchar(30)
);
alter table class3 add unique (name);

创建外键索引

-- 方式一
create table class (
  id bigint primary key auto_increment,
  name varchar(30) unique
);

create table student (
  id bigint primary key auto_increment,
  name varchar(50),
  class_id bigint,
  foreign key (class_id) references class(id)
);

由于使用 alter 新增外键比较麻烦，因此也就导致了创建外键索引时，也会比较麻烦。

使用 alter 语句创建外键 (索引) 语法：

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table(parent_column)

参数说明：

child_table：子表名称。
fk_name ：外键约束的名称，可以自定义。
child_column：子表中要作为外键的列名。
parent_table ：父表名称。
parent_column ：父表中被引l用的列名。

例如：

-- 指定外键约束名称
alter table student2 add constraint fk_name 
foreign key (class_id) references class(id);

-- 不指定外键约束名称
alter table student2 add foreign key (class_id) references class(id);

有上面两种创建外键 (索引) 的方式。区别如下所示：

创建普通索引

-- 方式一
create table student (
  id bigint primary key auto_increment,
  name varchar(50),
  sno varchar(20),
  index (sno)
);

-- 方式二
create table student2 (
  id bigint primary key auto_increment,
  name varchar(50),
  sno varchar(20)
);
alter table student2 add index (sno);

-- 方式三
create table student3 (
  id bigint primary key auto_increment,
  name varchar(50),
  sno varchar(20)
);
create index index_student3_sno on student3(sno);

方式三使用创建索引的方式，而方式一和方式二都是添加索引的方式。

上面都是创建一个单独的索引，现在我们开始学习复合索引。

-- 方式一
create table student (
  id bigint primary key auto_increment,
  name varchar(50),
  sno varchar(20),
  class_id bigint,
  index (sno, class_id)
);

-- 方式二
create table student2 (
  id bigint primary key auto_increment,
  name varchar(50),
  sno varchar(20),
  class_id bigint
);
alter table student2 add index (sno, class_id);

-- 方式三
create table student3 (
  id bigint primary key auto_increment,
  name varchar(50),
  sno varchar(20),
  class_id bigint
);
create index index_student3_sno on student3(sno, class_id);

当然，我们刚刚学习的索引都是可以创建复合索引的，这里只展示普通索引的示例。

查看索引

既然我们创建了索引，那么就应该可以查看索引的相关信息。

语法：

-- 方式一
show index from table_name;

-- 方式二
show keys from table_name;

例如：

-- 查看student表的索引信息
show index from student;

-- 查看student表的索引信息
show keys from student;

查询结果都是如下所示：

注意：虽然这里索引的数据类型显示的是BTREE，但实际上是B+树。

如果只有查看简明信息的话，就是使用我们前面学习的查看表结构的方法。

唯一键对应的就是 UNI。

注意：查看复合索引的详细信息时，键名都是指第一列的(从左往右)。如下所示：

删除索引

删除的索引中分为两种：1、主键索引；2、其他索引。

删除主键索引的语法：

alter table table_name drop primary key;

注意：再删除主键时，不能有自增属性，否则会报错。如果存在自增属性的话，得先把自增属性给删除，再去删除主键。

删除其他索引的语法：

alter table table_name drop index 索引名;

注意这里的索引名不是列名，而是我们在创建索引时取的名字。

创建索引的注意事项

1、索引应该创建在高频查询的列上；

2、索引需要占用额外的存储空间，所以索引不是越多越好；

3、对表进行插入、更新和删除操作时，同时也会修索引，可能会影响性能（在数据过多的情况下）；

4、创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引。

好啦！本期初始MYSQL数据库（5）—— 索引的学习之旅就到此结束啦！我们下一期再一起学习吧！

我要学编程(ಥ_ಥ)

关注

7
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录