当前位置: 首页> 游戏> 攻略 > MySQL:索引02——使用索引

MySQL:索引02——使用索引

时间:2025/7/11 1:29:27来源:https://blog.csdn.net/2401_83595513/article/details/142296291 浏览次数:1次

目录

引言

1、自动创建索引

 2、手动创建索引

2.1 主键索引

2.2 查看索引信息

2.3 唯一索引

2.4 普通索引

2.5 复合索引

 3、删除索引

3.1 主键索引

3.2 其他索引

4、查看执行计划

 4.1 不加条件,查询所有

4.2 使用主键查询

4.3 子查询使用索引

4.4 普通索引

4.5 复合索引


引言

在上篇文章中,详细介绍了有关索引的理论性知识,包含了索引底层的数据结构B+树、B+树与B树的对比、页、页的结构、索引分类......

接下来的这篇文章,我将向大家讲解如何SQL使用索引。


1、自动创建索引

  •  当我们在表中为字段创建主键约束(PRIMARY KEY),唯一约束(UNIQUE),外检约束(FOREIGN KEY)时,MySQL就会为表中相应的列就会自动创建索引。
  • 如果表中没有指定任何索引时,MySQL会自动为每一列生成一个索引并用 ROW_ID 进行标识(隐藏的,无法查看且无法使用)


 2、手动创建索引

2.1 主键索引

创建主键索引的方式有三种:

  1. 在创建表时就直接创建主键
  2. 在创建表时单独指定主键列
  3. 创建完表后再添加主键列
-- 在创建表时就直接创建主键
CREATE TABLE t_pk1 (
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(50)
);-- 在创建表时单独指定主键列
CREATE TABLE t_pk2 (
id BIGINT auto_increment,
name VARCHAR(50),
PRIMARY KEY (id)
);-- 创建完表后再添加主键列
CREATE TABLE t_pk3 (
id BIGINT,
name VARCHAR(50)
);
ALTER TABLE t_pk3 add PRIMARY KEY (id);
ALTER TABLE t_pk3 MODIFY id BIGINT auto_increment;

使用ALTER修改表内容,语法如下:

 alter table 表面 add|modify|drop 要修改的内容;

2.2 查看索引信息

创建完索引后,我们可以查看索引信息:

  1. desc 表名;//查看索引的简要信息
  2. show index from 表名;
  3. show keys from 表名;

主键索引的名称默认为PRIMARY。 


2.3 唯一索引

创建唯一索引的方式同样有三种:

  1. 在创建表时就直接指定唯一约束
  2. 在创建表时单独指定唯一约束
  3. 创建完表后再添加唯一约束
-- 在创建表时就直接指定唯一约束
CREATE TABLE t_uniq1(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50) UNIQUE
);-- 在创建表时单独指定唯一约束
CREATE TABLE t_uniq2(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
UNIQUE (NAME)
);-- 创建完表后再添加唯一约束
CREATE TABLE t_uniq3(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50)
);
ALTER TABLE t_uniq3 add UNIQUE (NAME);

创建完后可以查看索引信息:


2.4 普通索引

创建普通索引(索引)的方式有三种:

  1. 创建表时创建索引列
  2. 创建完表后使用alter创建索引
  3. 创建完表后使用 create index 索引名 on 表名(列名,...) 创建索引并指定索引名【最常用,可指定索引名】

 使用 create index 索引名 on 表名(列名[列名, ...])  为创建索引最常用的语法形式,且索引名推荐指定为 索引类型_表名_索引列 的形式。

-- 创建表时创建索引列
CREATE TABLE t_index1(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
INDEX (NAME)
);-- 创建完表后使用alter创建索引
CREATE TABLE t_index2(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50)
);
ALTER TABLE t_index2 add INDEX (name);-- 创建完表后使用create index创建索引并指定索引名【最常用,可指定索引名】
CREATE TABLE t_index3(
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(50)
);
CREATE INDEX idx_t_index3_name on t_index3(name);


2.5 复合索引

复合索引的创建语法与创建普通索引相同,只不过指定多个列,列与列之间用逗号隔开:

  1. 创建表时指定复合索引列
  2. 创建完表后使用alter创建复合索引
  3. 创建完表后使用 create index 索引名 on 表名(列名,...) 创建索引并指定索引名【最常用,可指定索引名】
-- 创建表时指定复合索引列
CREATE TABLE t_index4(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
sn VARCHAR(50),
INDEX (NAME, sn)
);	-- 创建完表后使用alter创建索引
CREATE TABLE t_index5(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
sn VARCHAR(50)
);
ALTER TABLE t_index5 add INDEX (name, sn);-- 创建完表后使用create index创建索引并指定索引名【最常用,可指定索引名】
CREATE TABLE t_index6(
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(50),
sn VARCHAR(50)
);
CREATE INDEX idx_t_index6_name_sn on t_index6(name, sn);#指定索引名


 3、删除索引

3.1 主键索引

因为主键索引是在我们创建主键约束时就自动创建的,不是我们手动人为指定的,并且主键索引只有一个,故删除主键索引语法如下:

alter table 表名 drop PRIMARY KEY; //删除主键索引的同时,删除主键约束 

删除主键索引,需要注意一点:

  • 主键列不能定义为auto_increment(自增类型),否则无法删除主键索引
  • 如果主键列是自增类型,需要先修改掉自增类型,再进行主键索引的删除
  • 修改掉主键的自增类型:alter table t_pk1 modify id bigint;

 当然,如果主键不含自增类型,则可直接用 alter table t_pk1 modify id bigint 来删除主键索引。


3.2 其他索引

语法:alter table 表名 drop index 索引名;


4、查看执行计划

对于很多小白来说,虽然自己创建了索引,但不清楚自己写出的SQL到底走没走索引,接下来我将为大家介绍一个关键字(查看执行计划):explain。 

在explain后加上我们写出的SQL语句,就能够查看该条语句的执行计划,判断到底走没走索引。

接下来的操作,我们均在student表中演示,先为name和sn列添加复合索引: 

 

 4.1 不加条件,查询所有

当我们直接使用 select * from student 时,此时为全表扫描(不走索引)。

我们可以使用explain select * from student;查看执行计划,发现type列中为ALL,说明该SQL没有走索引,是全表扫描得出的结果(效率低)。在生产环境中,如果出现了这样的情况(type为ALL),此时我们就要考虑为该列加索引了。

4.2 使用主键查询

当我们使用主键索引进行查询时,很显然会走索引,根据主键索引树,会很快的查询到目标值(主键索引树中包含所有的数据)。

使用explain查看执行计划时,type为const,代表查询效率为常量级别,非常的快,说明走索引。

 4.3 子查询使用索引

4.4 普通索引

当我们要查询的列包含在索引中时,会发生索引覆盖,此时不需要回表查询。

当要查询的列不完全包含在索引中时,会发生回表查询。

Extra列若为:Using index ,则表示索引覆盖。

4.5 复合索引

因为sn列创建了唯一索引,为了避免影响复合索引的查询,先drop掉复合索引sn。 接下来,我们再来查看复合索引的执行计划:

我们创建的复合索引为index(name,sn),name为复合索引的第一列,即name在前,sn在后,故使用name查询sn时,走索引,发生索引覆盖:

 但是若使用sn来查name,则不走索引:

当出现了Using where,说明可能进行了全表扫描(不走索引),这时我们就需要判断我们的SQL语句是不是出现了问题,对SQL做出优化。

  • Extra:执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
  • Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,即发生索引覆盖。
  • Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where(可能有全表扫描的情况)。

注意,当使用AND或其他情况下,只要where条件中使用了索引包含的所有列,就会走索引,和顺序无关:


END

关键字:MySQL:索引02——使用索引

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: