MySQL：索引02——使用索引

引言

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（可能有全表扫描的情况）。