当前位置：首页 > article >正文

MySQL：索引02——使用索引

article 2024/11/13 9:39:10

引言

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树的对比、页、页的结构、索引分类......

数据库索引底层数据结构之B+树&MySQL中的页&索引分类【纯理论干货，面试必备】-CSDN博客

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

1、自动创建索引

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

2、手动创建索引

2.1 主键索引

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

在创建表时就直接创建主键
在创建表时单独指定主键列
创建完表后再添加主键列

-- 在创建表时就直接创建主键
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 查看索引信息

创建完索引后，我们可以查看索引信息：

desc 表名；//查看索引的简要信息
show index from 表名；
show keys from 表名；

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

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 普通索引

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

创建表时创建索引列
创建完表后使用alter创建索引
创建完表后使用 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 复合索引

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

创建表时指定复合索引列
创建完表后使用alter创建复合索引
创建完表后使用 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（可能有全表扫描的情况）。