四、索引的操作

---------------------------------------------------------       小路原创           ------------------------------------------------

索引简介：数据库对象索引与书本的目录相似，创建索引主要是提高从表中检索数据的速度，还可以保障字段的唯一性，从而实现数据库表的完整性。

过多地创建索引会占据许多的磁盘空间。

根据索引的存储类型，可以将索引分为B型索引（BTREE）和哈希索引（HASH）。

注意：InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引，默认为BTREE索引。

MySQL支持的存储引擎对每个表最少支持16个索引，总索引长度至少为256个字节。

MySQL支持6种索引： 普通索引、全文索引、单列索引、多列索引和空间索引 。

根据创建索引方式可分为 自动索引 和 手动索引 两种，表中主键约束和唯一性约束列都会自动创建索引。

自动索引：在表中设置完整性约束时该表会被系统自动创建索引。

手动索引：手动在表上创建索引。

普通索引 ：创建索引时不附件任何限制条件，可以创建在任何数据类型的字段上。

唯一索引 ：创建索引时限制索引的值必须是唯一的。

全文索引 ：主要是关联在数据类型为char,varchar和text字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

多列索引 ：在创建索引时，所关联的字段不是一个字段，而是多个字段，查询语句中只有使用了所关联字段的第一个字段，多列索引才会被使用。

适合创建索引的情况：

1、经常被查询的字段，即where子句中常出现的字段。

2、在分组的字段，即在group子句中常出现的字段。

3、存在依赖关系的字表和父表之间的联合查询，即主键或外键字段。

4、设置唯一完整性约束的字段。

不适合创建索引的情况：

1、在查询中很少使用的字段。

2、拥有许多重复值的字段。

一、创建和查看普通索引

1、创建表时创建索引

普通索引：

create table table_name(

属性名 数据类型,

……

i ndex|key [索引名](属性名1[(长度)] [ASC|DESC])

);

唯一索引：

create table table_name(

属性名 数据类型,

……

unique index|key [index_name](属性名 [(长度)] [ASC|DESC])

);

全文索引：

create table table_name(

属性名 数据类型,

……

fulltext index|key [index_name](属性名 [(长度)] [ASC|DESC])

);

多列索引：

create table table_name(

属性名1 数据类型,

……

属性名n 数据类型,

index|key [index_name](属性名1 [(长度)] [ASC|DESC],

……

属性名n [(长度)] [ASC|DESC])

);

example:

use school;

drop table if exists buidings

create table buildings(

Id middleint not null primary key,

Name varchar(20),

Dept varchar(30),

index index_id(Id)

);

insert into buildings

values(1,'1号教学楼','计科院'),

(2,'2号办公楼','行政楼'),

(3,'3号教学楼','地化学院');

--查看索引是否已被使用

explain 

select * from buildings where Id = 1;

2、在已有的表上创建索引

普通索引：

create index index_name

on table_name (属性名 [(长度) [ASC|DESC]]);

唯一索引：

create unique index index_name

on table_name(属性名 [(长度)] [ASC|DESC]);

example:

create index index_Name

on buildings(Name);

全文索引：

create fulltext index|key index_name

on table_name(属性名 [(长度)] [ASC|DESC]);

多列索引：

create index index_name

on table_name(属性名1 [(长度)] [ASC|DESC],

……

属性名n [(长度)] [ASC|DESC])

explain 

select * from buildings where Name = 3;

3、通过修改表来创建索引

普通索引：

alter table table_name

add index|key index_name(属性名 [(长度)] [ASC|DESC]);

唯一索引：

alter table table_name

add unique index|key index_name(属性名 [(长度)] [ASC|DESC]);

全文索引：

alter table table_name

add fulltext index|key index_name(属性名 [(长度)] [ASC|DESC]);

多列索引：

alter table table_name

add index|key index_name(属性名1 [(长度)] [ASC|DESC],

……

属性名n [(长度)] [ASC|DESC])

example:

alter table buildings

add key index_Name(Name DESC);

二、删除索引

drop index index_name

on table_name;

example:

drop index index_Dept on buildings

---------------------------------------------------------       小路原创           ------------------------------------------------