文章目录

1. 什么是索引2. 索引的特点3. 索引的分类4. 索引的设计原则5. 创建索引5.1. 自动创建索引5.2. 手动创建索引5.2.1. 创建表时创建索引5.2.2. 在已经存在的表上创建索引5.2.2.1. alter语句创建索引5.2.2.2. create语句创建索引

6. 查看索引6.1. explain关键字查看索引6.2. show index关键字查看索引

7. 删除索引7.1. alter关键字删除索引7.2. drop关键字删除索引

1. 什么是索引

索引是一个单独存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针

在数据库中，索引用来加速对表的查询，索引技术通过使用快速路径访问的方法快速定位数据，减少了磁盘的I/O

可以把索引比作书的目录，每一张表都要有索引，就像每一本书都应该有目录一样。有了索引，数据库的检索速度可以大大提高

2. 索引的特点

索引是作用于表查询的一项技术，因此创建索引时，需要确保该索引是应用在SQL查询语句的条件，一般作为where子句的条件。

在表现形式上，索引表现为一张表，该表保存了主键与索引字段，并指向实体表的记录。索引独立于数据表存放，但不能独立存在，必须属于某个表。索引由数据库自动维护，数据表被删除时，该表上的索引自动被删除。

不过需要注意的是，虽然索引极大地提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete的速度会被降低。 因为更新表时，数据库不仅需要更新表数据，还要更新索 引文件，此外由于索引是独立于表存在的“表”，因此建立索引会占用一定的磁盘空间。

3. 索引的分类

普通索引 最基本的索引，它没有任何限制，用于加速查询唯一索引(unique) 索引列的值必须唯一，但允许有空值。如果是组合索列值的组合必须唯一主键索引(primary key) 是一种特殊的唯 一索引，一个表只能有一个主键，不允许有空值，主键索引一般是在表建立的同时自动创建单列索引 指单个字段上创建索引组合索引 指条个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合的规则全文索引(fulltext) 该索引不直接与索引中的值相比较，而是查找文本中的关键字空间索引(spatial) MySQL 5.7.4实验室版本中，InnoDB存储引擎新增了对于几何数据空间索引的支持

4. 索引的设计原则

设计索引应遵循如下原则：

只建立适量的索引。 索引并非越多越好，过多的索引会降低表的修改速度以及占用额外的存储空间。只在需要大量查询的表中建立大量索引。 应该避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。不在数据量小的表建立索引。 数据量小的表没有必要使用索引不在不同值少的列上建立索引。 如果表的某一列中大部分数据都是重复的，则没有必要建立索引指定唯一索引。 当表的某一列数据被指定为unique是，可以建立唯一索引在频繁操作的列上建立索引。 若想要建立索引，则应选择频繁排序(order by)或分组(group by)的列建立索引

5. 创建索引

5.1. 自动创建索引

当在表上定义primary key或者unique约束条件时，数据库会自动创建一个对应的索引，也就是创建主键索引和唯一索引。

5.2. 手动创建索引

5.2.1. 创建表时创建索引

在建表的同时创建索引的语法为：

create table [表名](

[属性名1] [数据类型1] [约束1],

...

[属性名n] [数据类型n] [约束n],

[unique | fulltext | spatial] index|key [索引名](

[属性名1] [(长度)] [asc | desc],

...

[属性名n] [(长度)] [asc | desc]

)

);

例如 建立普通索引

create table normal_table(

id int(20) primary key,

username varchar(20),

email varchar(20),

-- 在id列建立普通索引

index normal_index(id)

);

建立唯一索引

create table unique_table(

id int(20) primary key,

username varchar(20),

email varchar(20),

-- 在email列建立唯一索引，并升序排列

unique index uniquel_index(email asc)

);

建立全文索引

create table fulltext_table(

id int(20) primary key,

username varchar(20),

email varchar(20),

-- 在username列建立全文索引

fulltext index fulltext_index(username)

);

建立单列索引

create table single_table(

id int(20) primary key,

username varchar(20),

email varchar(20),

-- 在username列建立索引，索引长度为9

index single_index(username(9))

);

建立多列索引

create table multi_table(

id int(20) primary key,

username varchar(20),

email varchar(20),

-- 在username列和email列建立索引，索引长度分别为9和10

index multi_index(

username(9),

email(10)

)

);

5.2.2. 在已经存在的表上创建索引

5.2.2.1. alter语句创建索引

语法：

alter table [表名] add [unique|fulltext|spatial] [index|key] [索引名](

[属性名1] [(长度)] [asc | desc],

...

[属性名n] [(长度)] [asc | desc]

);

例如： 建立普通索引

-- 在normal_table表的id列建立名为normal_index的普通索引

alter table normal_table add index normal_index(id);

建立唯一索引

-- 在unique_table表的username列建立名为unique_index的唯一索引

alter table unique_table add unique index unique_index(username);

建立多列索引

-- 在multi_table表的username和email列建立名为multi_index的多列索引

alter table multi_table add index multi_index(username, email);

其他类型的索引同理

5.2.2.2. create语句创建索引

语法：

create unique [unique|fulltext|spatial] index [索引名] ON [表名](

[属性名1] [(长度)] [asc | desc],

...

[属性名n] [(长度)] [asc | desc]

);

例如 建立普通索引

-- 在normal_table表的id列建立名为normal_index的普通索引

create index normal_index on normal_table(id);

建立唯一索引

-- 在unique_table表的username列建立名为unique_index的唯一索引

create unique index unique_index on unique_table(username);

建立多列索引

-- 在multi_table表的username和email列建立名为multi_index的多列索引

create index multi_table on multi_table(username, email);

其他类型的索引同理

6. 查看索引

6.1. explain关键字查看索引

explain关键字用于查看和分析SQL索引使用情况 语法：

explain select * from [表名];

使用里explain语句后，会返回一张表：

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra

其中对表中字段的说明可以参考这篇博客：mysql中explain查看sql语句索引使用情况

例如：

-- 查看multi_table表中的所有使用情况

explain select * from multi_table;

结果为：

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEmulti_tableindexmulti_table21661100.00Using index

6.2. show index关键字查看索引

show index关键字用于查看某一表中的所有索引 语法：

show index from [表名];

使用里show index语句后，会返回一张表：

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression

其中对表中字段的说明可以参考这篇博客：MySQL SHOW INDEX 的语法解析

例如：

-- 查看multi_table表中的全部索引

show index from multi_table;

结果为：

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpressionmulti_table0PRIMARY1idA0BTREEYESmulti_table1multi_index1usernameA09YESBTREEYESmulti_table1multi_index2emailA010YESBTREEYESmulti_table1multi_table21usernameA0YESBTREEYESmulti_table1multi_table22emailA0YESBTREEYES

7. 删除索引

7.1. alter关键字删除索引

语法：

alter table [表名] drop index [索引名];

例如

-- 从multi_table表中删除multi_index索引

alter table multi_table drop index multi_index;

7.2. drop关键字删除索引

语法：

drop index [索引名] on [表名];

例如

-- 从multi_table表中删除multi_index索引

drop index multi_index on multi_table;