mysql索引结构操作(主键/唯一键/普通索引的创建/查询/删除),复合索引介绍(索引覆盖,索引最左匹配原则)

目录

索引操作

创建索引

主键索引

介绍

在创建表时设置主键

创建表后添加主键

唯一键索引

介绍

在创建表时设置唯一键

创建表后添加唯一键

普通索引

在创建表时指定某列为索引

创建表后添加普通索引

自主命名索引

索引创建原则

哪些列适合创建索引

不适合作为索引的列

查询索引

介绍

查看方式 

主键索引

​编辑

唯一键索引

普通索引

删除索引

主键索引

其他索引

复合索引

介绍

查看索引

作用

索引覆盖

索引最左匹配原则 

---

mysql索引结构详细介绍 -- mysql索引 -- 索引的硬件理解(磁盘,磁盘与系统),软件理解(mysql,与系统io,buffer pool),索引结构介绍和理解(page内部,page之间,为什么是b+树)-CSDN博客

普通索引 -- mysql索引 -- 聚簇索引,非聚簇索引,如何查看linux下的数据库文件,普通/辅助索引(回表查询)-CSDN博客

索引操作

创建索引

主键索引

介绍

主键索引的效率最高,因为主键不能重复,所以搜索效率最高

• 只要设置主键,mysql会自动创建主键索引

因为一个表只有一个主键

• 所以相应地,只会有一个主键索引

索引默认名称为PRIMARY

在创建表时设置主键

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

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

创建表后添加主键

alter table 表名 add primary key(列名);

唯一键索引

介绍

当我们为表添加唯一键约束时,也会自动创建出唯一键索引

在索引层面上,唯一键索引和普通索引没有任何差别

• 创建时单拎出unique是为了照顾约束关系,只是在语法上想要强调一下唯一键
• 在索引层面上唯一键索引和普通索引没有任何差别

因为可以有多个唯一键

• 所以可以有多个唯一键索引

在创建表时设置唯一键

默认以列名作为索引名称

create table 表名(
    id int unique,
    name varchar(30)
);

create table 表名(
    id int, 
    name varchar(30), 
    unique key(id)
);

创建表后添加唯一键

默认以列名作为索引名称

alter table 表名 add unique(列名);

普通索引

在创建表时指定某列为索引

默认以列名作为索引名称

create table 表名(
    id int, 
    name varchar(30), 
    index(id)
);

创建表后添加普通索引

默认以列名作为索引名称

alter table 表名 add index(列名);

自主命名索引

create index 索引名 on 表名(列名);

索引创建原则

哪些列适合创建索引

频繁作为查询条件

• 当然,设置了主键和唯一键的列不需要创建索引,因为一旦设置约束条件,就自动创建索引结构

条件不止这些,可以通过下面的"不适合"来反推哪些列适合

不适合作为索引的列

唯一性太差的

• 比如性别,总共就那么几种,没必要创建索引
• 提高不了效率,反而增加负担

更新特别频繁的

• 比如,登录状态
• 索引就是方便我们去查询,数据老是改来改去的,结构也要跟着变(因为key值变了)

不会出现在where子句中的

• 根本不会用它作为筛选条件的,也就没有必要创建索引

如何证明我们创建了索引?

查询索引

介绍

Key_name:

• 主键索引的名字
• 默认名称是PRIMARY

Column_name:

• 指以哪一列来构建索引

Index_type:

• 构建索引的方式
• BTREE就是b+树

查看方式 

show index/keys from 表名(\G);

desc 表名;

主键索引

可以看到,索引名称就是主键索引的默认名

唯一键索引

因为我们没有指定索引名,所以默认以列名为索引名

普通索引

• 
• 默认名依然是列名

如果设置了索引名:

 

• 
• 这样就能看到Key_name和Column_name的区别了

删除索引

主键索引

alter table 表名 drop primary key;

因为一张表中主键只有一个,所以不需要带索引名

其他索引

alter table 表名 drop index 索引名;

drop index 索引名 on 表名;

复合索引

介绍

以多列作为索引,用于加速对多个列的查询

语法 -- alter table 表名 add index(列名1,列名2...)

查看索引

查看索引时会发现增加了两个索引

• 两个索引的Key_name,只是Column_name不同
• 说明b+树结构只新增了一个,会以列名1充当索引名
• 队列组合起来充当key值  -- 查询的时候,必须所有列数据都匹配,才能继续往下走

作用

索引覆盖

因为innodb的普通索引在索引结构中存放的是[数据的key值]

• 如果我们以两列作为key值,之后我们如果想要高频地用某行[列1数据]查找[对应的列2数据]时,就可以很快地找到
• 因为本身列2数据就在key值中(也就是在这颗新建的b+树中),就不用再回表了
• 一旦列1数据匹配,自然就可以拿到列2数据了

以上被称为索引覆盖

• 覆盖的是主键索引,因为不需要再回到主键索引中查找了

索引最左匹配原则 

mysql在索引匹配时,是并且必须是从最左侧开始的

• 
• 意思是 -- 如果查询条件跳过了最左边的列，索引将无法被利用