索引（基础）一、索引介绍1.1、前言

在数据库中，执行如下语句时：

select * from emp where id=1000;

mysql 是从第一条记录开始遍历，直至找到 id = 1000 的数据，然而这样查询的效率低，所以 mysql 允许通过建立索引来加快数据表的查询和排序。

【资料图】

1.2、索引概念

数据库的索引类似字典中的拼音，是对数据库表中一列或多列的值进行排序后的一种结构。

作用：就是提高表中数据的查询速度。

1.3、索引分类

普通索引：

不加任何参数，直接通过 key或 index来创建的索引。是 mysql 中的基本索引类型，可创建在任意数据类型中。其值是否唯一、非空，则由字段本身的约束决定。

唯一索引：

在定义索引时，加上 unique即可，该索引所在字段的值必须唯一。但允许有空值。在一张数据表里可以有多个唯一索引。

全文索引：

在定义索引时，加上 fulltext即可，该索引只能创建在 char、varchar 、text 类型的字段上。

单列索引：

是在表中单个字段上创建索引，可以为普通索引、唯一索引等，只要保证该索引只对应表中一个字段即可。

多列索引：

是在表中多个字段上创建索引，但是只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。

空间索引：在定义索引时，加上 spatial即可，该索引只能创建在空间数据类型的字段上。

mysql 中空间类型索引有 4 中：GEOMETRY、POINT、LINESTRING 和 POLYGON 。但是当使用空间索引时，该字段的约束必须为 not null且数据库的存储引擎为 MyISAM中使用。

注：主键：primary key 也可作为索引

但本质上看其是一种约束，而索引是一种数据结构，用来提升查询效率

二、创建索引2.1、创建表时

语法：

create table 表名(字段 数据类型 约束    [unique|fulltext|spatial] index|key [别名] (字段) [ASC|DESC] );

[]：中的值表示可选项

[unique|fulltext|spatial]：分别表示：唯一索引、全文索引、空间索引

[ASC|DESC]：升序、降序

2.1.1、创建普通索引

语法：

create table 表名(字段 数据类型 约束     index|key [别名] (字段) [ASC|DESC]);

例如这里需要在 t1表中的 id 创建索引：

create table t1(id int,    name varchar(10),    age int,    index(id));

通过下述命令查看是否创建成功：

show create table t1\g

通过上图可知，t1表中成功创建索引。

可再通过下述命令查看是否使用：

explain select * from t1 where id=1\g

通过图中的 possible_keys与 key知 其值都为 id ，表明 id 索引已经存在并开始使用。

2.1.2、创建唯一索引

语法：

create table 表名(字段 数据类型 约束     unique index|key [别名] (字段) [ASC|DESC]);

例如这里需要在 t2 表中在 id 上创建 unique_id 索引：

create table t2(id int,    name varchar(7),    score float,    unique index unique_id (id ASC));

2.1.3、创建全文索引

语法：

create table 表名(字段 数据类型 约束     fulltext index|key [别名] (字段) [ASC|DESC]);

例如这里需要在 t3 表上根据 name 字段来创建全文索引：

create table t3(id int,    name varchar(7),    score float,    fulltext index fulltext_name (name));

2.1.4、创建单列索引

语法：

create table 表名(字段 数据类型 约束     index|key [别名] (字段1,字段2......) [ASC|DESC]);

例如需要在 t4 表上根据 name 创建索引

create table t4(id int,    name varchar(7),    score float,    index single_name (name(7)));

2.1.5、创建多列索引

语法：

create table 表名(字段 数据类型 约束     index|key [别名] (字段1,字段2......) [ASC|DESC]);

例如需要在 t5 表上创建多列索引：

create table t5(id int,    name varchar(7),    score float,    index multi (id,name(7)));

注意：只有当在查询条件中使用索引字段的第一个字段时，才会有效。验证如下：

这里通过 id来查询：

explain select * from t5 where id=1\g

当通过 name来查询时:

explain select * from t5 where name="张三"\g

这时，发现通过 name查询的结果中：possible_keys | key皆为 null,表明索引并为使用。

2.1.6、创建空间索引

语法：

create table 表名(字段 数据类型 约束     spatial index|key [别名] (字段1,字段2......) [ASC|DESC]);

例如需要在 t6 表上创建空间索引：

create table t6(id int,    space geometry not null,    spatial index sp (space));

2.2、创表后1、通过 create 创建

语法：

create [unique|fulltext|spatial] index 索引名 on 表明 (字段名[长度]) [asc|desc];

这里以创建唯一索引为例：

需要在 t7 表上根据 bookid 创建唯一索引

create unique index unique_id on book(bookid);

其余类似。

2、通过 alter 创建

语法：

alter table 表名 add [unique|fulltext|spatial] index 索引名 (字段名[长度]) [asc|desc];

这里以创建唯一索引为例：

需要在 t7 表上根据 bookid 创建唯一索引

alter table book add unique index unique_id (bokid);

其余类似。

三、删除索引

由于索引会占用一定的磁盘空间，所以为了避免影响数据库性能，需删除不使用的索引。

3.1、alter

语法：

alter table 表名 drop index 索引名

这里以删除唯一索引为例：

需要在 t7 表上根据 bookid 删除唯一索引：

alter table book drop index unique_id;

其余类似。

3.2、drop

语法：

drop index 索引名 on 表名;

这里以删除唯一索引为例：

需要在 t7 表上根据 bookid 删除唯一索引：

drop index unique_id on book;

其余类似。