MySQL—索引 |ू･ω･` )

最新推荐文章于 2025-04-14 22:26:17 发布

曳渔

最新推荐文章于 2025-04-14 22:26:17 发布

阅读量930

点赞数 34

分类专栏： MySQL 文章标签： 1024程序员节数据库 mysql

本文链接：https://blog.youkuaiyun.com/2303_80388948/article/details/143220445

版权

MySQL 专栏收录该内容

7 篇文章

订阅专栏

文本目录：

❄️一、索引的介绍：

MySQL 的索引其实是一种数据结构，它可以帮助数据库高效的查询、更新数据表中的数据，索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过索引来加快速度。

其实索引就像是每本书都有的目录一样，通过指向数据行的位置，可以快速的定位和访问表中的数据，比如对于字典中的按笔画、按偏旁部首等来进行快速的查找要查找的字。

使用索引的目的：

对于使用索引的目的非常的简单，就是提升数据搜索的效率。

对于索引中使用的数据结构是——B+树

因为：

1、可以保证数据稳定有序，插入和修改都存在比较稳定的时间复杂度

2、非叶子结点仅具有索引作用，所有的数据都存放到叶子结点中

3、所有的叶子节点构成了一个有序的链表，可以遍历所有的数据，并且连接处呢是双向链表

❄️二、索引的分类：

1、主键索引：

a、当在一个表中使用 PRIMARY KEY 时，自动创建索引，索引的值就是主键值，InnoDB使用其作为聚集索引 (聚簇索引)

b、推荐为每一个表都定义一个主键，如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自增列。

2、普通索引：

a、最基本的索引，没有唯一性的限制

b、可能为多列创建组合索引，称为复合索引或组全索引

3、唯一索引：

a、当在表上定义一个唯一键 UNIQUE 时，自动创建唯一索引。

b、与普通索引类似，但区别在于唯⼀索引的列不允许有重复值。

4、全文索引：

a、基于文本列（CHAR、VARCHAR或TEXT列）进行创建，以加快对这些列中包含的数据的查询。

b、用于全文搜索，仅限于 MyISAM 和 InnoDB 引擎支持使用

5、聚集索引：

a、与主键索引是同义词

b、如果没有为表定义 PRIMARY KRY ，InnoDB 将会使用第一个 UNIQUE 和 NOT NULL 的列作为聚集索引。

c、如果表中没有 PRIMARY KRY 和合适的 UNIQUE 作为索引，InnoDB 会为新插入的行生成一个行号，并且用 6字节的 ROW_ID 记录，ROW_ID是单调递增的。

6、非聚集索引：

a、聚集索引以外的索引称之为非聚集索引或者称为二级索引

b、二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列

c、InnoDB 使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询

回表查询：通过索引记录中的主键值，再到主键值的索引树中去查询这个主键值就是回表查询

7、索引覆盖：

a、当一个 select 语句中使用了普通索引并且查询列表中的列刚好是创建普通索引时所有的列或者是部分列，这个时候呢，我们就不需要进行回表查询去查询要查找的列，直接就可以在普通索引中查找出来。

❄️三、索引的使用：

1、自动创建：

1）、当我们为一张表添加主键约束、唯一约束、外键约束的时候呢，MySQL 会为对应的列自动创建一个索引。

2）、如果表不指定约束的时候呢，我们的MySQL会自动为每一列生成一个索引并且ROW_ID 标记。

2、手动创建：

1）、主键索引：

方式一：

创建表的时候创建主表。

方式二：

创建表的时候单独指定主键列。

-- 创建表的时候单独指定主键。
create table t_pk2 (
 id bigint auto_increment,
 name varchar(30),
 primary key (id)
);

方式三：

修改表中的列为主键索引。

我们先来创建一个表：

这个时候呢，我们来查询索引来看看：

我们可以看到这里什么也没有的，那么接下来我们进行添加主键操作：

总的代码就是：

-- 修改表中的列为主键索引
create table t_pk3 (
 id bigint,
 name varchar(30)
);

alter table t_pk3 add primary key (id);
alter table t_pk3 modify id bigint auto_increment;

这里呢就出现了一个新的语法：

alter table 表名 [add(添加) | modify(更改) | drop(删除)] 要修改的内容;

2）、唯一索引：

这个唯一索引呢和上面的主键索引是的创建是差不多的，所以我们这里就来简单的来看一下：

方式一：

创建表的时候创建唯一索引。

-- 创建表的时候创建唯一索引
create table t_uk1 (
id bigint primary key auto_increment,
name varchar(30) unique
);

方式二：

创建表的时候单独指定唯一索引。

-- 创建表的时候单独指定唯一列
create table t_uk2 (
id bigint primary key auto_increment,
name varchar(30),
unique (name)
);

方式三：

修改表中的列为唯一索引。

-- 修改表中的列为唯一索引 。
create table t_uk3 (
id bigint primary key auto_increment,
name varchar(30)
);

alter table t_uk3 add unique (name);

我们呢，来查询一下对于唯一索引的表的结构：

3）、普通索引：

对于这个什么时候创建这个索引呢？

1、创建表的时候，明确的了解哪些列是频繁查询的，就进行创建，但是是在数据很多的时候。如果数据少的话，可能全表扫描是比索引的效率是高的。

2、在业务的不断发展的过程中，进行创建

对于普通索引的创建的语法：

index(列，列.....)

方式一：

在创建表的时候指定索引列。

-- 在创建表的时候指定索引列
create table t_index1 (
id bigint primary key auto_increment,
name varchar(30) unique,
sn varchar(30),
index(sn)
);

我们来看一看对于这个普通索引的结构是怎样的：

这个就是对于普通索引的结构了。

方式二：

修改表中的列为普通索引。

-- 修改表中的列为普通索引
create table t_index2 (
id bigint primary key auto_increment,
name varchar(30) unique,
sn varchar(30)
);

alter table t_index2 add index(sn);

这个就是方式二了，和我们上面对于主键索引和唯一索引的方式三是一样额的。

方式三：

这个方式是我们对于创建普通索引经常使用的方式。

单独创建索引并指定索引名。

-- 单独创建索引并指定索引名
create table t_index3 (
id bigint primary key auto_increment,
name varchar(30) unique,
sn varchar(30)
);

create index t_index3_sn on t_index3(sn);

我们来看看这个结构：

我们可以看到对于这个索引名的这个列呢，使用的是我们设置的名。

在这里呢，我们又看见了一个新的语法：

创建索引的语法：

create index 索引名 on 表名[(列)，(列).....];

这里可以是单个列也可以是多个列进行创建普通索引。

3、创建复合索引：

对于这个创建复合所以的方式和上面的对于普通索引的方式是差不多的，就是在列上进行了增加，列与列之间使用逗号隔开。

方式一：

创建表时指定索引列。

-- 创建表时指定索引列
create table t_test_index1 (
id bigint primary key auto_increment,
name varchar(30) unique,
sn varchar(30),
class_id bigint,
index (sn,class_id)
);

我们来看看对于这个复合索引的结构是怎样的：

方式二：

修改表中的列为复合索引。

-- 修改表中的列为复合索引
create table t_test_index2 (
id bigint primary key auto_increment,
name varchar(30) unique,
sn varchar(30),
class_id bigint
);

alter table t_test_index2 add index(sn,class_id);

这些都是比较简单的。

方式三：

单独创建索引并指定索引名。

-- 单独创建索引并指定索引名
create table t_test_index3 (
id bigint primary key auto_increment,
name varchar(30) unique,
sn varchar(30),
class_id bigint
);

create index t_test_sn_class_id on t_test_index3(sn,class_id);
show index from t_test_index3;